The posts about RDBMS that were written for this blog can be found below. For quick reference only.
Alternative of DBCC CHECKDB Repair Allow Data Loss
DBCC CHECKDB is executed on the SQL Server database to check the physical and logical integrity of the database’s integral components. These objects include tables, indexes, keys, stored procedures, triggers, etc. DBCC CHECKDB also checks the structure of all the database tables and indexes. In case any of these checks fail while executing DBCC, you…
USE of GO in SQL Server
GO is interpreted by SQL Server utilities as a signal to deliver the current batch of Transact-SQL statements to a SQL Server instance. All statements entered since the last GO, or since the beginning of the ad hoc session or script if this is the first GO, make up the current batch of statements. To…
Can we convert TIMESTAMP to date time?
The short answer is, NO. Let’s consider the following example. USE testDB GO CREATE TABLE tbOrders ( iOrderID INT IDENTITY(1,1) PRIMARY KEY, dtOrderDate DATETIME, dcPaidAmount Decimal(9,2), TIMESTAMP ); INSERT INTO tbOrders(dtOrderDate, dcPaidAmount) VALUES (‘2023-11-01’, 2750), (‘2023-11-02’, 3950), (‘2023-11-03’, 7000); Now retrieve the data and see the results. SELECT * FROM tbOrders The timestamp values assigned…
Stored Procedure – RAISERROR
Here’s an example of how to make a user-defined error message and raise it in the event that something goes wrong. — Creating the procedure CREATE PROCEDURE uspUpdateValue AS BEGIN TRY DECLARE @ErrorMsg VARCHAR(100) DECLARE @ErrorMessage VARCHAR(100) DECLARE @Severity INT DECLARE @State INT SET @Severity = 10 SET @State = 1 SET @ErrorMessage = ‘An…
MySQLDump Backup and Restore All Databases at once
The mysqldump client utility performs logical backups, producing a set of SQL statements that can be executed to reproduce the original database object definitions and table data. It dumps one or more MySQL databases for backup or transfer to another server. The mysqldump command can also generate output in CSV, other delimited text, or XML…
Assign a serial number to result set – MySQL
Concerned parties may occasionally request that a serial number be displayed alongside the result set when a report is being generated. If the table lacks an auto-increment column or the result-set is lacking a unique identifier, you can assign one dynamically using the method outlined below. — Sample datasetCREATE TABLE tbCustomer ( CustName VARCHAR(45) NOT…
Generate SubOrder ID to Orders MySQL
To differentiate between OrderIDs that are identical, it is suggested to include a SubOrderID as an additional field with a serial number suffix. For example, in the event that the OrderID is 100123 and is repeated twice, the SubOrderID values in the additional column should be 1001231 and 1001232. — Sample dataset CREATE TABLE tbOrders(…
Anonymity with Random Values in MySQL
Organisations can use data anonymization as a strategy to abide by strict data privacy rules that require the protection of personally identifiable information (PII), such as date of births, emails, identification details, monetary information, contact information, and information from health reports. The following will assist in making the data anonymous. 1) Updating random values for…
SQL Sample Datasets and Assignments
Here is a compilation of earlier blog posts that were relevant to Sample datasets, Sample projects, and Sample assignments. For quick reference only.
Hadoop Default Ports
Computers are able to distinguish between various traffic types with the use of ports. A default open port is a port number that is preconfigured to accept internet connections and packets from specific services using protocols like the User Datagram Protocol (UDP), Stream Control Transmission Protocol (SCTP), Datagram Congestion Control Protocol (DCCP), or Transmission Control…
Metadata Information from SYS tables – SQL Server
The underlying tables which store the metadata for a certain database are called system tables. A system table called sys tables is used to keep track of data about the tables in a database. A record is created in the sys for each table or object that is added to the database. These system tables…
Get the information about the backup and restore operations
The msdb database keeps a complete record of every SQL Server backup and restore procedure performed on a server instance. The data for the SQL Server Agent jobs, job history, operators, and alarms is stored in this database. It includes information about log shipping settings, backup and restore history, and database activity. Additionally, it contains…
SQL Server Stored Procedures, Triggers and Cursors
The articles about SQL Server stored procedures, triggers, and cursors that have previously been posted on this blog are collected here. For quick reference only.
List of Scheduled Jobs – SQL Server
Scheduled administrative tasks, referred to in SQL Server as jobs, are carried out by SQL Server Agent which is a crucial component of SQL Server and is a Microsoft Windows service. Using SQL Server Agent, we can schedule and automate database and server management activities. The following query will assist in determining which jobs were…
Get the list of files and filegroups – SQL Server
One of the routine database administration tasks that a DBA performs is creating files and filegroups. The following query is quite useful for viewing, analysing, and comprehending the files and filegroups created in the database. The query will assist in obtaining data regarding files, filegroups, their overall size, and their growth. SELECT a.NAME FileName, b.NAME…
Get the size of each table from a database – SQL Server
Checking the size of the tables is one of the duties that DBAs frequently perform. In order to do capacity planning, it is imperative that a parameter be utilized to monitor database expansion. The size of each table in the database is determined by the following popular query, which is available over the internet. You…
Get the Edition and Version Information of SQL Server
As soon as you start a new job, the first thing you should do is look up the version and edition details of the database servers. And you’ll be able to know it in several ways. The version information will be visible in the object explorer once SQL Server Management. Studio has established a connection…
Cursors in SQL Server
An object called a database cursor makes it possible to browse through the rows of a result set. Unlike TSQL commands that operate on all the rows in the result set at one time, it enables you to handle a single row from the result set of a query. Sometimes, you might want to process,…
Example – Cursor in SQL Server
The following gives a SQL Server cursor example. It is useful to back-up all the databases from SQL Server. Please click here for more information about what a cursor is, how to use one, and its drawbacks. /* Variable Declaration */ DECLARE @DB VARCHAR(50) DECLARE @FolderPath VARCHAR(100) DECLARE @FileNamePath VARCHAR(100) — Make sure the specified…
mysqldump – extended insert
By default, –extended-insert, -e is enabled when using mysqldump to create backups of MySQL databases. As seen in the example below, the dump file is prepared using multiple-row syntax, and each INSERT statement has several VALUES lists. INSERT INTO `TestTable` VALUES (1,’value1′),(2,’value2′),(3,’value3′); When the file is reloaded, it reduces the size of the dump file…
Answer to – SQL Assignment-4
Below is an attachment with the assignment #4 solution. You can execute it all at once in the “dbCall” database that was built in Assignment-4 to retrieve the results, including the length and cost of each call, among other information. Happy learning!!!
SQL Assignment-4
There will be no complex stored procedures available anywhere if you look for them online since complexity arises when you have a requirement that leads to it. A sample database schema for information on mobile calls, their owners, mobile recharges, consumption, etc. is attached. The schema is created based on the following requirement. Customers exist,…
MySQLDump Backup All Databases Into Separate Files
The mysqldump client utility performs logical backups, producing a set of SQL statements that can be executed to reproduce the original database object definitions and table data. It dumps one or more MySQL databases for backup or transfer to another server. The mysqldump command can also generate output in CSV, other delimited text, or XML…
While Loop Examples – SQL Server
WHILE LOOP creates a condition for a SQL statement or statement block to be executed again. As long as the provided condition is true, the execution is repeated. With the use of the BREAK and CONTINUE keywords, the WHILE loop’s statement execution can be managed from within the loop itself. In simple words, the advantage…
SSRS – Refresh the report at a set time
I created dashboards using SSRS that include graphs, key performance indicators, and other business KPIs in my previous organizations. Wide-screen televisions throughout the call center, back office, and management office showed them during business hours. There was a requirement to refresh the data every 5 minutes. Let’s talk about it in this article. Simply entering…
SSRS Error – Something went wrong
The following error message has occasionally been reported by SSRS users who are trying to generate the report. Something went wrong. please try again later. The error message doesn’t make the issue clear. However, the common reasons and the solution could be – The account that you set up doesn’t have access to the machine’s administrative…
SSRS Error – An error occurred during local report processing
The following error message has occasionally been reported by SSRS users who are trying to generate the report. An error occurred during local report processing. An error has occurred during report processing. Cannot create a connection to data source ‘xyz’. You have specified integrated security or credentials in the connection string for the data source,…
SSRS Error – cannot open a connection to the report server
The following error message has occasionally been reported by SSRS users who browse the online portal after installing it. The report server cannot open a connection to the report server database. The error message makes the issue quite clear. It claims that the SSRS service is unable to connect to the “report server” database that…
mysqldump: Couldn’t execute ‘FLUSH TABLES’: Access denied
The following error messages are frequently encountered by EC2 instance users, particularly when they lack the privileges necessary to perform a database backup from the RDS MySQL. mysqldump –no-create-db RetailDB –single-transaction –host MyInstance.rds.amazonaws.com -u auser -pPassword > dump.sql mysqldump: Couldn’t execute ‘FLUSH TABLES’: Access denied mysqldump: Couldn’t execute ‘FLUSH TABLES WITH READ LOCK’: Access denied for user ‘auser’@’%’…
Kill multiple connections at once in AWS RDS
The quickest method for generating the kill statements that end the sleeping threads in RDS MySQL is as follows. SELECT CONCAT(‘CALL mysql.rds_kill(‘,ID,’);’) FROM INFORMATION_SCHEMA.PROCESSLIST WHERE command <> ‘Sleep’; Hope this helps.
Triggers in SQL Server
A trigger is a sort of stored procedure that is triggered when an event happens in the database server. There are 3 types of triggers as described below: DML Triggers: DML triggers are triggered when a user attempts to edit data using a data manipulation language (DML) event. These triggers are triggered anytime any eligible…
SQL Server Triggers – Enable and Disable
In this article, you’ll know how to enable and disable triggers in SQL Server. Syntax and Example: DISABLE TRIGGER trgEmpInsDelUpd ON Emp; GO ENABLE TRIGGER trgEmpInsDelUpd ON Emp; GO A disabled trigger is still present in the current database as an object, but it does not fire. Enabling a trigger does not result in its…
SQL Server Triggers – After Insert, Update, and Delete – An example
When a DML statement is issued against a table, a DML trigger is fired. It can execute before or after the DML procedure depending on the condition. In this article, you’ll know how to write a trigger that writes an entry into an audit table, whenever a row is inserted or updated, or deleted on…
SQL Server Triggers – Instead of Delete – An example
An SQL trigger that is executed “instead of” a DML statement is known as an INSTEAD OF trigger. There is absolutely no actual insert, remove, or update activity. Instead, it carries out other commands listed in the trigger. The DML triggers known as INSTEAD OF triggers are those that are triggered in place of triggering…
SSRS – Highlight cell color based on values
SSDT is a cutting-edge development tool for creating relational databases in SQL Server, databases in Azure SQL, data models in Analysis Services (AS), packages in Integration Services (IS), and reports in Reporting Services (RS). Yes, it’s one of my favorite tools, and I’ve worked on it with great pleasure for many years while producing reports…
Install MySQL in Mac
This tutorial will assist you in using Brew to install MySQL on a Mac. user@hostname ~ % brew install mysql The above command will help in installing the latest MySQL version available. It will take a few minutes to complete the process. Once installation is completed, run the following command check the mysql version. user@hostname…
Backup and Restore MySQL databases through Jump servers
The import of MySQL databases using backup files is supported by Amazon RDS. You can back up your database, save it on Amazon S3, and then use that backup file to restore your database onto a fresh Amazon RDS DB instance running MySQL. The databases can be backed up and restored using this method if…
Types of Reports in SSRS
Using SQL Server reporting services, you may build various reports, render them in various formats, plan the report-generating process, schedule the delivery of the report to the recipients, and more. In this article, you’ll learn what kinds of reports you may produce using SSRS to compile and deliver. Parameterized reports: For the purpose of processing…
SSRS Presentation (PPT)
Here is a power-point presentation on SSRS which I recently used to give a demonstration to the students. Notes Slide-1:Microsoft SQL Server Reporting Services is a server-based report-generating software system that allows administrators to share reports with users or integrate the reports into internal or web applications based on access permissions and user groups. It…
SSRS Architecture
Microsoft SQL Server Reporting Services is a server-based report-generating software system that allows administrators to share reports with users or integrate the reports into internal or web applications based on access permissions and user groups. It is used as a solution to build custom reports from a variety of data sources, such as Flat files,…
SSRS – Allow multiple values in the parameter
Users may occasionally want to choose numerous values from a list or a dropdown menu. Those many parameters should be taken into consideration while displaying the report or the page’s content. You’ll learn how to create a report with numerous values that users can choose from the provided parameters, in this post. Allow Multiple Values:…
Display Everything for an SSRS Report on a Single Page
At times, users want the entire content of dashboards or reports to be visible on a single page. The report, however, spans numerous pages if the page’s contents are greater than what can really fit on one page. Set the Height in InteractiveSize attribute of the report to zero to render the entire contents of…
SSRS – Quick Reference
The posts about SSRS that were written for this blog can be found below. For quick reference only.
SSRS Report Header – Display Report Criteria
Some reports can require the proper headers and footers to let readers know the selection criteria and the date the report was written. You can learn how to supply the parameters that have been chosen to generate the report, in this post. Once a text box has been placed, right-click it, choose expression, and then…
SSRS Dashboard Auto Refresh
The steps listed below can be used if you’ve developed a dashboard using SSRS and want the report to be automatically refreshed after a certain amount of time. Go to the report properties page. Scroll down to find the AutoRefresh property. It will have 0 as a default value which denotes that the report should…
SSRS Report Generation Date
The report generated date is one of the crucial headers or footers that must be included once the report has been designed. Once a text box has been placed, right-click it, choose expression, and then type one of the following expressions. =FORMAT(Cdate(today), “yyyy-MM-dd”) =FORMAT(Cdate(today), “MM-dd-yyyy”) =FORMAT(Cdate(today), “dd-MM-yyyy”) =Format(Now(), “dd/MM/yyyy hh:mm tt”) =”Report Generation Date: “…
PostgreSQL Quick Reference
The posts about PostgreSQL that were written for this blog can be found below. For quick reference only.
MySQL Generated Column
Virtual and stored generated columns are both available in MySQL. Every time data is read, the virtual columns are calculated instantly, but when data is updated, the stored columns are calculated and physically saved. This is equivalent to a calculated column in SQL Server that is either virtual or physically stored. An expression that is part…
MySQL Generated column from another table
No, if this is what you are trying to achieve, then it isn’t supported in any SQL product. Sometimes we may require a generated/computed column in MySQL that references other tables. However, this isn’t supported and not a practical solution to have a reference of another column. The following code will return a syntax error.…
Third Highest Salary in SQL Server
For the majority of data retrievals, there are workarounds. In SQL, sub-queries can be used, although joins are also an option in the majority of situations. Most of the time, you can achieve a goal by using built-in functions rather than inner queries. The example that follows shows how to extract the third-highest salary from…
SSRS Catalog Queries
Since it is hard to acquire a complete view of the SSRS landscape from the hierarchical web interface, the following query provides some basic information about what was deployed in SQL Server reporting services (SSRS). The report will provide you with a list of the objects in the reporting service, including folders, and reports, as…
DATE FORMAT in SQL
SQL date formatting is a common task. When creating reports or getting data, everyone tries to convert the date. In SQL Server, the date can be formatted in a variety of ways. Method-1: SELECT Ename, FORMAT(HireDate, ‘d MMM yyyy hh:mm:ss.sss’) as JoinDate FROM Emp Output: 17 Dec 1980 12:00:00.00 Method-2: SELECT Ename, CONVERT(VARCHAR(40), HireDate, 113)…
Split alpha-numeric string into multiple columns in SQL Server
To break the string into numerous columns, a certain pattern should be used. Splitting a string can be done using various delimiters such as a comma, semicolon, or tab, or it can be split using the amount of characters provided as input. Here’s an example of combining a username and a phone number into a…
BULK INSERT in SQL Server
In SQL Server, bulk insert imports a data file in a user-specified format into a database table or view. We’ll be able to import CSV, TXT, DAT, and other files into SQL Server with this command. Before inserting data into a table, it must exist. Example for TEXT file: BULK INSERT tbVehicleDetails FROM ‘C:\Users\User\Documents\VehicleDetails.txt’ WITH…
SSRS Expressions on Field Values
Usually, the Stored Procedures that were called in the SSRS report contain practically all of the formatting and conversion already. However, you can still convert or format the data while creating the report in SSDT if you are unsure of the data and when you have control over the report rather than the source code.…
Report Generation Date in SSRS
In addition to the data, there are a few more elements that should be in the report. “Report Generation Date” is among them. This will inform others when the report that was shared is being prepared. Take a text box in the report’s data section and enter the following expression. =”Report Generated Date: ” &…
Multiple Tables Row Count in Single Query in SQL Server
The following SQL Server query can be used to view multiple tables’ row counts in one single SELECT statement. CREATE TABLE Dept( EmployeeDetails VARCHAR(100)); INSERT INTO Dept VALUES (‘John|Sales Team’), (‘Mike|Sales’), (‘Smith|Development’), (‘Jim|New Sales Team’), (‘Lucy|Development’), (‘Will|Development’); CREATE TABLE tbStudents(iStudentID INT, StudentName VARCHAR(10), SubjectName VARCHAR(30)); INSERT INTO tbStudents VALUES (1, ‘John’, ‘Mathematics’), (1, ‘John’, ‘Science’),…
MATCH and AGAINST in MySQLf
The MATCH() AGAINST() syntax is used to conduct full-text searches. The columns to be searched are listed in a comma-separated list that MATCH() accepts. AGAINST requires a search string and an optional modifier to specify the kind of search to conduct. The search string needs to have a constant value during the assessment of the…
1191 – Can’t find FULLTEXT index matching the column list
If you see the following error notice while conducting a full-text search in a column of a table, it signifies that the column doesn’t have a full-text index. 1191 – Can’t find FULLTEXT index matching the column list Let’s reproduce this issue. CREATE TABLE Dept( EmployeeDetails VARCHAR(100)); INSERT INTO Dept VALUES (‘John|Sales Team’), (‘Mike|Sales’), (‘Smith|Development’),…
Multiple Tables Row Count in Single Query
The following MySQL query can be used to view multiple tables’ row counts in one single SELECT statement. CREATE TABLE Dept( EmployeeDetails VARCHAR(100)); INSERT INTO Dept VALUES (‘John|Sales Team’), (‘Mike|Sales’), (‘Smith|Development’), (‘Jim|New Sales Team’), (‘Lucy|Development’), (‘Will|Development’); CREATE TABLE tbStudents(iStudentID INT, StudentName VARCHAR(10), SubjectName VARCHAR(30)); INSERT INTO tbStudents VALUES (1, ‘John’, ‘Mathematics’), (1, ‘John’, ‘Science’), (1,…
GROUP_CONCAT in MySQL
GROUP_CONCAT() is a function that merges the data from multiple rows into one field. It is a GROUP BY function that returns a string. The comma (,) or any user-specified delimiter can be used to separate the values in the string. To know more about this function and similar functionality in various RDBMS and big…
REGEXP in MySQL
A regular expression is a powerful way of specifying a pattern for a complex search. It performs a pattern match of a string expression against a pattern. The pattern (search condition) is supplied as an argument. If the pattern finds a match in the expression, the function returns 1, else it returns 0. If either…
UPDATE with CASE in MySQL
The CASE expression checks each condition and, if the first one is true, returns a value. It will therefore stop reading and return the outcome if a condition is true. It returns the value in the ELSE clause if no conditions are met. It returns NULL if the ELSE clause is absent and none of…
RAW Data for normalization practice
Data organization in a database is done by normalization. This entails creating tables and linking those tables together in accordance with principles intended to safeguard the data and increase the database’s adaptability by removing duplication and inconsistent reliance. In other words, the practice of normalization improves data integrity in the table by removing redundant data…
Usage of TIME datatype in MySQL
When retrieving and displaying TIME data, MySQL uses the format ‘hh:mm:ss’ (or ‘hhh:mm:ss’ for huge hours values). ‘-838:59:59’ to ‘838:59:59’ are the possible TIME values. The TIME type can be used to indicate elapsed time or the duration between two occurrences, both of which may be much longer than 24 hours or even negative, in…
MySQL UPDATE with Incremental value
When you wish to UPDATE a specific column with an incremental value or UPDATE the column with a string and incremental integers, the following methods can be helpful. Assign a value to the parameter directly as shown below. There is no requirement to DECLARE it, unlike in SQL Server. UPDATE the column with an incremental…
SQL Server Conditional Statements
This is a collection of previously published articles on this site about SQL Server Conditional statements. This is for quick reference.
Answer to – SQL Assignment-2
The answer to assignment-2 is provided below. If you haven’t visited the post, please do so by clicking here. — Answer to 1st Question SELECT DName, LocationName, COUNT(*) FROM Emp JOIN DeptLocation ON DeptLocation.DeptLocationId = Emp.DeptLocationId JOIN Dept ON Dept.DeptNo = DeptLocation.DeptNo JOIN Locations ON Locations.LocationId = DeptLocation.LocationId GROUP BY DName, LocationName — Answer to…
Answer to – SQL Assignment-3
The answer to assignment-3 is provided below. If you haven’t visited the post, please do so by clicking here. Solution:1 ;WITH CTE AS (SELECT DName, EName, EmpNo, Sal = CASE WHEN DName = ‘SALES’ THEN (Sal + Sal * 0.10) WHEN DName = ‘RESEARCH’ THEN (Sal + Sal * 0.08) WHEN DName = ‘ACCOUNTING’ THEN…
SQL Assignment-3
This is a simple test or assignment to see how comfortable someone is using SQL at the beginner to advanced level. Exercises of this nature will undoubtedly aid in skill improvement. The simplest method of learning is through practice and exercise problems. This is unrelated to Microsoft examinations or their format. Simply put, I’m testing…
Named Window functions in MySQL
For queries where numerous OVER clauses would ordinarily define the same window, a WINDOW clause is helpful. Instead, you can create a single definition for the window, assign it a name, and then make reference to the name in the OVER clauses. Syntax: WINDOW window_name AS (window_spec) [, window_name AS (window_spec)] … Window_name is the…
Dataset to practice Windows functions in MySQL
As discussed in the previous post, MySQL is supporting “Window functions” beginning with version 8.0. The window functions enable you to tackle query problems in new, easier, and faster ways. The following are important considerations to remember. The following is an example dataset for practice.
Window Functions in MySQL
MySQL supports “Window functions” beginning with version 8.0. The window functions enable you to tackle query problems in new, easier, and faster ways. The following are important considerations to remember. Based on a set of data, Windows and Analytic functions compute an aggregate value. These functions are used in the majority of RDBMS programs and…
SQL Server Interview Questions All in One
This is a collection of previously published articles on this site about SQL Server interview questions and answers. This is for quick reference.
MySQLDump – Complete reference
This is a collection of articles regarding the MySQL utility mysqldump that were previously published on this blog. This is for quick reference.
SQL Server Database Administration
This is a collection of articles about SQL Server database administration that were formerly published on this blog. This is for quick reference.
Get a random number between two given numbers in MySQL
The following example shows how to generate a random number between two provided numbers. This may be useful in situations when the random number must fall between a certain minimum and maximum value. The formula for this case is as shown below: SELECT RAND()*(MaximumValue-MinimumValue)+MinimumValue; Example: SELECT FLOOR( RAND() * (1800-100) + 100) as RandomValue; Remember,…
Insert 10000 records using WHILE loop in MySQL
Based on the maximum and minimum parameters provided, the example will insert 10000 records into a table. The goal of this example is to show beginners how to – 1) construct a WHILE loop statement2) Develop a stored procedure3) Insert 10,000 random records for the practice dataset. — Drop table if existsDROP TABLE IF EXISTS…
SQL DBA – Check SQL Services status across multiple servers
Working with so many database servers necessitates checking to see if the Windows services are operating in each instance. This is especially important if you have other services installed alongside it, such as reporting services, analytical services, and so on, as well as third-party tools like Idera compliance manager, diagnostic manager, and so on. The…
LOAD XML File into a table – MySQL
This article shows how to import XML data into a MySQL table. Once the data is imported into the table, you’ll see the data in rows and columns. Dataset: Copy the following data and save it into your local file system. <CustInfo> <Customer><field name=”CustomerID”>1</field><field name=”CustomerName”>Dalillah</field> <field name=”PhoneNumber”>00971305374799</field></Customer> <Customer><field name=”CustomerID”>2</field><field name=”CustomerName”>Melanie</field> <field name=”PhoneNumber”>00971305374776</field></Customer> </CustInfo> Now go…
SQL_CALC_FOUND_ROWS and FOUND_ROWS in MySQL
A LIMIT clause can be used in a SELECT query to limit the number of rows the server returns to the client. In some circumstances, it would be useful to know how many rows the statement would have returned if the LIMIT clause had not been present but without rerunning the statement. Include the SQL_CALC_FOUND_ROWS…
MySQL Errors and Solutions
You’ll encounter errors with MySQL frequently when working with development or administration for a variety of reasons. The configuration file settings, parameter variable restrictions, poorly written code, etc. could all be contributing factors. This page serves as a quick reference for the numerous error message fixes offered earlier in this blog.
MySQL Functions
Below is a quick reference list for the MySQL Functions descriptions and usage examples that have already been posted on this site. You can search this blog using the function name to find many use cases for each of the built-in functions, as most of them are identical to those in Apache Hive, SQL Server,…
NULLIF in MySQL
When the given two expressions are equal, the NULLIF() function compares them and returns NULL. If not, the first expression is returned. Examples: SELECT NULLIF(34, 0); Returns: 34 SELECT NULLIF(34, “SQL”); Returns: 34 SELECT NULLIF(34, 34); Returns: NULL SELECT NULLIF(NULL, 34); Returns: NULL (since it is the first expression)
SQL Server ISNULL equivalent in MySQL
One of the often used functions in SQL Server is ISNULL, which aids in replacing the null values of a column with an alternative value. The syntax of the ISNULL is provided below: SELECT ISNULL(expression, alternative_value); IFNULL is an equivalent function in MySQL. The definition: If the expression is NULL, the IFNULL() function returns a…
IFNULL in MySQL
If the expression is NULL, the IFNULL() function returns a specified alternative value. This function returns the expression if it is NOT NULL. This function is typically used to return a different value in the event that a column’s value contains any NULLs. Syntax: IFNULL(expression, alt_value) Example: SELECT IFNULL(NULL, “bigdatansql.com”); Result: bigdatansql.com
SUBTIME in MySQL
The SUBTIME() function subtracts a given time from a specified time or DateTime expression and then returns the resulting time/DateTime. Syntax: SUBTIME(datetime, time_interval) Note that both parameters are required. The time interval is the one that gets subtracted from datetime and can be either a positive or a negative value. Examples: SELECT SUBTIME(“2023-04-23 10:18:18.000021”, “5.000001”);…
TIME_FORMAT in MySQL
The time_format() is a MySQL date/time function. The purpose of it is to format the time using a defined format_mask. Format_mask refers to the timely application of the format. The choices are listed below. %f Microseconds (000000 to 999999) %H Hour (00 to 23 generally, but can be higher) %h Hour (00 to 12) %I…
TIMEDIFF Function in MySQL
In order to analyze trends or systemic patterns throughout time, organizations might use time series analysis. Business users can explore the reasons why such patterns happen at certain times by using data visualizations to identify seasonal trends. TIMEDIFF is a MySQL function that calculates the difference between two “time” expressions. In data analysis or reporting, this…
sys.objects “type” abbreviations and their meanings – SQL Server
A system table or catalog table that stores “System Objects” is called sys.objects. Every user-defined, schema-scoped object that is created in a database is represented by a row, including any natively built scalar user-defined functions. The columns such as “type” has abbreviated information about the type of the object. Their meaning has been given below.…
Sys.Procedures in SQL Server
The subset of sys.objects with the object types = P, X, RF, and PC can be found in the object catalog view sys.procedures. P = SQL Stored Procedure X = Extended stored procedure RF = Replication-filter-procedure PC = Assembly (CLR) stored-procedure In other words, the columns of this view (sys.procedures) inherit from sys.objects. By executing the…
List of indexes with Index Type – SQL Server
The system catalog views sys.indexes and sys.index_columns make it easy to get a list of all indexes and index columns in a database. You may get all the indexes for tables, views, and table valued functions by using the sys.indexes function. The following query is used to acquire a list of a specific database’s indexes…
List of indexes from a specific table – SQL Server
An earlier post described that the system catalog views sys.indexes and sys.index_columns make it easy to get a list of all indexes and index columns in a database. You may get all the indexes for tables, views, and table valued functions by using the sys.indexes. However, you can do so by executing the following statement…
MySQL Error – The target table xxx of the INSERT is not insertable into
While transferring data from one database to another database, you may receive the following error. ERROR 1471 (HY000): The target table xxx of the INSERT is not insertable into. Find out the reason by executing the following command in both source and target servers. SELECT * FROM information_schema.TABLES WHERE TABLE_NAME = ‘xxx’; The target table,…
Replace NULL value in datetime column to an empty string – SQL Server
We were stuck for a moment today in class when trying to replace the NULL values in a date column with an empty string since the ISNULL and COALESCE methods return the default date 1900-01-01. Here are the initial queries. SELECT C.iCustomerID, vcCustomerName, ISNULL(OH.iOrderID,0) OrderID, COALESCE(OrderedDate, NULL, ”) OrderedDate, COALESCE(vcDeliveryStatus, NULL, ‘N/A’) AS DeliveryStatus FROM…
List of indexes – SQL Server
The system catalog views sys.indexes and sys.index_columns make it easy to get a list of all indexes and index columns in a database. You may get all the indexes for tables, views, and table valued functions by using the sys.indexes function. The following query is used to acquire a list of a specific database’s indexes.…
Add a CHECK constraint to the existing table
In this article, you’ll know how to add a CHECK constraint to the existing table. CREATE TABLE ActiveCustomersSummary ( SummaryID INT IDENTITY(1,1) PRIMARY KEY, CustomerID INT NOT NULL, CustomerName VARCHAR(50), OrdersCount INT, StartEffectiveDate DATE, EndEffectiveDate DATE ); Now, let’s ALTER the table and add a CHECK constraint. ALTER TABLE ActiveCustomersSummary ADD CONSTRAINT ck_ActiveStatus_ActiveCustomersSummary CHECK (…
Complex CHECK Constraints in SQL Server
Depending on your needs, SQL Server allows you to use a single CHECK constraint across multiple columns or numerous CHECK Constraints across a single column. The following examples show how it can be done. Example-1 CREATE TABLE tbProducts( ProductID INT IDENTITY PRIMARY KEY, ProductName VARCHAR(255) NOT NULL, PurchasedPrice DECIMAL(10,2), SalablePrice DECIMAL(10,2), DiscountedPrice DECIMAL(10,2), CONSTRAINT Ck_DiscountedPrice_tbProducts1…
Column CHECK constraint for column ‘col1’ references another column, table ‘table1’.
Despite the fact that SQL Server permits the addition of multiple check constraints on a single column that is referenced by other columns in the same table, attempts to write the syntax in the following way frequently result in errors. CREATE TABLE tbProducts( ProductID INT IDENTITY PRIMARY KEY, ProductName VARCHAR(255) NOT NULL, PurchasedPrice DECIMAL(10,2), SalablePrice…
ENUM vs SET in MySQL
Have you ever used an application or filled out a web form with the radio buttons and checkboxes depicted below? Source: Google Image. Typically, the values for radio buttons are fixed, and you can only select one from the available options. In the database, the column is restricted to that set of items so that…
Big Data & SQL 