Category: sql

SQL – Quick Guide ”; Previous Next SQL – Overview SQL is a language to operate databases; it includes database creation, deletion, fetching rows, modifying rows, etc. SQL is an ANSI (American National Standards Institute) standard language, but there are many different versions of the SQL language. What is SQL? SQL is Structured Query Language, which is a computer language for storing, manipulating and retrieving data stored in a relational database. SQL is the standard language for Relational Database System. All the Relational Database Management Systems (RDMS) like MySQL, MS Access, Oracle, Sybase, Informix, Postgres and SQL Server use SQL as their standard database language. Also, they are using different dialects, such as − MS SQL Server using T-SQL, Oracle using PL/SQL, MS Access version of SQL is called JET SQL (native format) etc. Why SQL? SQL is widely popular because it offers the following advantages − Allows users to access data in the relational database management systems. Allows users to describe the data. Allows users to define the data in a database and manipulate that data. Allows to embed within other languages using SQL modules, libraries & pre-compilers. Allows users to create and drop databases and tables. Allows users to create view, stored procedure, functions in a database. Allows users to set permissions on tables, procedures and views. A Brief History of SQL 1970 − Dr. Edgar F. “Ted” Codd of IBM is known as the father of relational databases. He described a relational model for databases. 1974 − Structured Query Language appeared. 1978 − IBM worked to develop Codd”s ideas and released a product named System/R. 1986 − IBM developed the first prototype of relational database and standardized by ANSI. The first relational database was released by Relational Software which later came to be known as Oracle. SQL Process When you are executing an SQL command for any RDBMS, the system determines the best way to carry out your request and SQL engine figures out how to interpret the task. There are various components included in this process. These components are − Query Dispatcher Optimization Engines Classic Query Engine SQL Query Engine, etc. A classic query engine handles all the non-SQL queries, but a SQL query engine won”t handle logical files. Following is a simple diagram showing the SQL Architecture − SQL Commands The standard SQL commands to interact with relational databases are CREATE, SELECT, INSERT, UPDATE, DELETE and DROP. These commands can be classified into the following groups based on their nature − DDL – Data Definition Language Sr.No. Command & Description 1 CREATE Creates a new table, a view of a table, or other object in the database. 2 ALTER Modifies an existing database object, such as a table. 3 DROP Deletes an entire table, a view of a table or other objects in the database. DML – Data Manipulation Language Sr.No. Command & Description 1 SELECT Retrieves certain records from one or more tables. 2 INSERT Creates a record. 3 UPDATE Modifies records. 4 DELETE Deletes records. DCL – Data Control Language Sr.No. Command & Description 1 GRANT Gives a privilege to user. 2 REVOKE Takes back privileges granted from user. SQL – RDBMS Concepts What is RDBMS? RDBMS stands for Relational Database Management System. RDBMS is the basis for SQL, and for all modern database systems like MS SQL Server, IBM DB2, Oracle, MySQL, and Microsoft Access. A Relational database management system (RDBMS) is a database management system (DBMS) that is based on the relational model as introduced by E. F. Codd. What is a table? The data in an RDBMS is stored in database objects which are called as tables. This table is basically a collection of related data entries and it consists of numerous columns and rows. Remember, a table is the most common and simplest form of data storage in a relational database. The following program is an example of a CUSTOMERS table − +—-+———-+—–+———–+———-+ | ID | NAME | AGE | ADDRESS | SALARY | +—-+———-+—–+———–+———-+ | 1 | Ramesh | 32 | Ahmedabad | 2000.00 | | 2 | Khilan | 25 | Delhi | 1500.00 | | 3 | kaushik | 23 | Kota | 2000.00 | | 4 | Chaitali | 25 | Mumbai | 6500.00 | | 5 | Hardik | 27 | Bhopal | 8500.00 | | 6 | Komal | 22 | MP | 4500.00 | | 7 | Muffy | 24 | Indore | 10000.00 | +—-+———-+—–+———–+———-+ What is a field? Every table is broken up into smaller entities called fields. The fields in the CUSTOMERS table consist of ID, NAME, AGE, ADDRESS and SALARY. A field is a column in a table that is designed to maintain specific information about every record in the table. What is a Record or a Row? A record is also called as a row of data is each individual entry that exists in a table. For example, there are 7 records in the above CUSTOMERS table. Following is a single row of data or record in the CUSTOMERS table − +—-+———-+—–+———–+———-+ | 1 | Ramesh | 32 | Ahmedabad | 2000.00 | +—-+———-+—–+———–+———-+ A record is a horizontal entity in a table. What is a column? A column is a vertical entity in a table that contains all information associated with a specific field in a table. For example, a column in the CUSTOMERS table is ADDRESS, which represents location description and would be as shown below − +———–+ | ADDRESS | +———–+ | Ahmedabad | | Delhi | | Kota | | Mumbai | | Bhopal | | MP | | Indore | +—-+——+ What is a NULL value? A NULL value in a table is a value in a field that appears to be blank, which means a field with a NULL value is a field with no value. It is very important to understand that a NULL value is different than a zero value or a field that contains

SQL – Statistical Functions ”; Previous Next SQL server Statistical functions work on SQL server tables and views and exploit all database parallelism, scalability, user privileges and security schemes. Hence the SQL statistical functions can be included and exposed within SQL queries − Sr.No. Function & Description 1 @@CONNECTIONS() Returns number of attempted connections. 2 @@CPU_BUSY() Returns amount of time, SQL server spent on an active operation. 3 @@IDLE() returns the total idle time of this SQL server. 4 @@IO_BUSY() Returns the total spent time by this SQL server on performing the input and output operations. 5 @@PACK_RECEIVED() Returns the number of input packets. 6 @@PACK_SENT() Returns the number of output packets. 7 @@PACKET_ERRORS() Returns the number of network packet errors. 8 @@TIMETICKS() Returns the number of microseconds per tick. 9 @@TOTAL_ERRORS() Returns the number of disk errors. 10 @@TOTAL_READ() Returns the number of disk reads. 11 @@TOTOAL_WRITE() Returns the number of disk writes. Print Page Previous Next Advertisements ”;

SQL – Date Functions ”; Previous Next The Date & time functions are built-in functions in the SQL server that allows you to manipulate and perform operations on date and time values in the SQL server. These functions can be used in SQL Server queries to perform various date and time operations, such as filtering records based on dates, calculating date differences, and formatting dates for display purposes. Sr.No. Function & Description 1 @@DATEFIRST This function is used to retrieve the first day of the week which is set by the SET DATEFIRST function. 2 CURRENT_TIMESTAMP Is used to retrieve the current date and time. 3 CURRENT_TIMEZONE() This function is used to retrieve the current time zone offset from Coordinated Universal Time (UTC). 4 CURRENT_TIMEZONE_ID() Is used to retrieve the current time zone ID observed by a server or an instance. 5 DATE_BUCKET() Is used to group data into groups that correspond to fixed periods of time. 6 DATEADD() Is used to add a specific number of intervals to a given date or time value. 7 DATEDIFF() Is used to calculate the difference between two date values and returns in a int data type. 8 DATEDIFF_BIG() Is used to calculate the difference between two dates values and return in a bigint data type. 9 DATEFROMPARTS() This function is used to retrieve a date from individual segments such as year, month, and day. 10 DATENAME() This function is used to retrieve a specified part of a date or time value as string. 11 DATEPART() Is used to return a specified part of a date or time value as integer. 12 DATETIME2FROMPARTS() Is used to construct a datetime2 value from an individual date and time segments. 13 DATETIMEFROMPARTS() Is used to construct a datetime value from an individual date and time segments. 14 DATETIMEOFFSETFROMPARTS() Is used to extract a datetimeoffset value from each of a date”s component parts. 15 DATETRUNC() Is used to truncate a date or time value to a specified datepart (such as year, month, day, etc.). 16 DAY() This function is used to get the day of the month for a specified date. 17 EOMONTH() This function is used to get the last day of the month for a specified date value. 18 GETDATE() This function is used to get the current database system date and time. 19 GETUTCDATE() This function is used to get the current database system UTC date and time. 20 ISDATE() Is used to determine whether a value is a valid date or not. 21 MONTH() This function is used to get the month part for a specified date (value can be from 1 to 12). 22 SMALLDATETIMEFROMPARTS() Is used to construct a new datetime value from individual segments (such as year, month, etc.). 23 SWITCHOFFSET() This function is used to get datetimeoffset values that has been changed from the stored time zone offset to a given new time zone offset. 24 SYSDATETIME() This function is used to get the date and time of the SQL Server. 25 SYSDATETIMEOFFSET() This function is used to get a value of DATETIMEOFFSET(7) that provides the current system date and time and also timezone of the SQL server. 26 SYSUTCDATETIME() This function is used to get date and time of the computer on which the instance of SQL Server is running. 27 TIMEFROMPARTS() Is used to construct a time value from individual segments. 28 TODATETIMEOFFSET() This function is used to get a datetimeoffset value that is translated from a datetime2 expression. 29 YEAR() This function is used to get an integer number that represents the year of the given date. Print Page Previous Next Advertisements ”;

SQL – Indexes Table of content The SQL Indexes The CREATE INDEX Statement Types of Indexes The DROP INDEX Statement When should indexes be avoided? ”; Previous Next The SQL Indexes SQL Indexes are special lookup tables that are used to speed up the process of data retrieval. They hold pointers that refer to the data stored in a database, which makes it easier to locate the required data records in a database table. SQL Indexes work similar to the index of a book or a journal. While an index speeds up the performance of data retrieval queries (SELECT statement), it slows down the performance of data input queries (UPDATE and INSERT statements). However, these indexes do not have any effect on the data. SQL Indexes need their own storage space within the database. Despite that, the users cannot view them physically as they are just performance tools. The CREATE INDEX Statement An index in SQL can be created using the CREATE INDEX statement. This statement allows you to name the index, to specify the table and which column or columns to index, and to indicate whether the index is in an ascending or descending order. Preferably, an index must be created on column(s) of a large table that are frequently queried for data retrieval. Syntax The basic syntax of a CREATE INDEX is as follows − CREATE INDEX index_name ON table_name; Types of Indexes There are various types of indexes that can be created using the CREATE INDEX statement. They are: Unique Index Single-Column Index Composite Index Implicit Index Unique Indexes Unique indexes are used not only for performance, but also for data integrity. A unique index does not allow any duplicate values to be inserted into the table. It is automatically created by PRIMARY and UNIQUE constraints when they are applied on a database table, in order to prevent the user from inserting duplicate values into the indexed table column(s). The basic syntax is as follows. CREATE UNIQUE INDEX index_name on table_name (column_name); Single-Column Indexes A single-column index is created only on one table column. The syntax is as follows. CREATE INDEX index_name ON table_name (column_name); Composite Indexes A composite index is an index that can be created on two or more columns of a table. Its basic syntax is as follows. CREATE INDEX index_name on table_name (column1, column2); Implicit Indexes Implicit indexes are indexes that are automatically created by the database server when an object is created. For example, indexes are automatically created when primary key and unique constraints are created on a table in MySQL database. The DROP INDEX Statement An index can be dropped using SQL DROP command. Dropping an index can effect the query performance in a database. Thus, an index needs to be dropped only when it is absolutely necessary. The basic syntax is as follows − DROP INDEX index_name; When should indexes be avoided? Although indexes are intended to enhance a database”s performance, there are times when they should be avoided. The following guidelines indicate when the use of an index should be reconsidered. Indexes should not be used on small tables. They should not be used on tables that have frequent, large batch updates or insert operations. Indexes should not be used on columns that contain a high number of NULL values. Columns that are frequently manipulated should not be indexed. Print Page Previous Next Advertisements ”;

SQL – Text & Image functions ”; Previous Next SQL Text and Image functions operate on text and image data. The text and image functions are − Sr.No. Function & Description 1 TEXTPTR() Returns the pointer value of the text or images. 2 TEXTVALID() Returns an integer value(1 for valid, 0 for non-valid). Print Page Previous Next Advertisements ”;

SQL – IN vs EXISTS Table of content The SQL IN Operator The SQL EXISTS Operator IN vs EXISTS ”; Previous Next In SQL, we use the IN operator to simplify queries and reduce the need for multiple OR conditions. It allows us to match a value against a list of values. On the other hand, the EXISTS operator checks whether one or more rows exist in a subquery and returns either true or false based on this condition. If the subquery finds at least one row, the EXISTS operator returns true; otherwise, it returns false. The SQL IN Operator The IN operator in SQL is used to check if a particular value matches any within a given set. This set of values can be specified individually or obtained from a subquery. We can use the IN operator with the WHERE clause to simplify queries and reduce the use of multiple OR conditions. Suppose we have a table named CUSTOMERS and we want to retrieve customer details based on their IDs. In this scenario, we can use the IN operator with the WHERE clause to fetch the details of these specific IDs. Syntax Following is the syntax of the SQL IN operator − SELECT column_name FROM table_name WHERE column_name IN (value1, value2, valueN); In the above syntax, the column_name matches every value (value1, value2, … valueN). If the matches occur, The IN operators returns true; otherwise, false. Example First of all, let us create a table named CUSTOMERS using the following query − CREATE TABLE CUSTOMERS ( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25), SALARY DECIMAL (18, 2), PRIMARY KEY (ID) ); Now, add records into the above created table using the INSERT INTO statement as shown below − INSERT INTO CUSTOMERS VALUES (1, ”Ramesh”, 32, ”Ahmedabad”, 2000.00), (2, ”Khilan”, 25, ”Delhi”, 1500.00), (3, ”Kaushik”, 23, ”Kota”, 2000.00), (4, ”Chaitali”, 25, ”Mumbai”, 6500.00), (5, ”Hardik”, 27, ”Bhopal”, 8500.00), (6, ”Komal”, 22, ”Hyderabad”, 4500.00), (7, ”Muffy”, 24, ”Indore”, 10000.00); The table will be created as follows − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 2 Khilan 25 Delhi 1500.00 3 kaushik 23 Kota 2000.00 4 Chaitali 25 Mumbai 6500.00 5 Hardik 27 Bhopal 8500.00 6 Komal 22 Hyderabad 4500.00 7 Muffy 24 Indore 10000.00 The following query retrieves the NAME and SALARY columns from the CUSTOMERS table for rows where the ID is 1, 2, or 3. SELECT NAME, SALARY FROM CUSTOMERS WHERE ID IN(1, 2, 3); Output On executing the query above, the output is displayed as − Name Salary Ramesh 2000.00 Khilan 1500.00 Kaushik 2000.00 The SQL EXISTS Operator The EXISTS operator is used to look for the existence of a row in a given table that satisfies a set of criteria. It is a Boolean operator that compares the result of the subquery to an existing record and returns true or false. The returned value is true, if the subquery fetches single or multiple records; and false, if no record is matched. EXISTS operator follows the querys efficiency features, i.e. when the first true event is detected, it will automatically stop processing further. We can use the EXISTS operator with the SELECT, UPDATE, INSERT and DELETE queries. Syntax Following is the basic syntax of SQL EXISTS operator − SELECT column_name FROM table_name WHERE EXISTS ( SELECT column_name FROM table_name WHERE condition ); Example First of all, consider the CUSTOMERS table, and create another table named EMPLOYEES using the following query − CREATE TABLE EMPLOYEES ( EID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, CITY CHAR (25), CONTACT INT, PRIMARY KEY (EID) ); Now, let us insert some records into the EMPLOYEES table using the INSERT INTO statement as shown below − INSERT INTO EMPLOYEES VALUES (1, ”Varun”, 32, ”Ahmedabad”, 12345), (2, ”Mahesh”, 22, ”Kashmir”, 34235 ), (3, ”Suresh”, 43, ”Kerala”, 12355 ); The table will be created as follows − EID NAME AGE CITY CONTACT 1 Varun 32 Ahmedabad 12345 2 Mahesh 22 Kashmir 34235 3 Suresh 43 Kerala 12355 In the following query, we are using the EXISTS operator to fetch the names and ages of CUSTOMERS whose AGE is same as the AGE in the EMPLOYEES table. SELECT NAME, AGE FROM CUSTOMERS WHERE EXISTS( SELECT * FROM EMPLOYEES WHERE CUSTOMERS.AGE = EMPLOYEES.AGE ); Output Following is the output of the above query − NAME AGE Ramesh 32 Komal 22 IN vs EXISTS Following table summarizes all the differences between IN and EXISTS − S.No. IN EXISTS 1 It is applied to the SQL query to remove the multiple OR conditions. It is used to find whether the data in the subquery truly exist. 2 It executes all values contained within the IN block. If the value is matched, displays the details of the given value. It will terminate the further process if the condition is met. 3 It can be used for the comparison of a null value because it returns true, false, and a null value. It cannot be used for the comparison of a null value because it returns only true and false values. 4 It can be used with subqueries as well as with values. It can be used only with subqueries. 5 It executes faster when the subquery is smaller. It executes faster when the subquery is larger. Because it is more efficient than IN and returns only a Boolean value. Print Page Previous Next Advertisements ”;

SQL – Expressions Table of content What is SQL Expression? SQL Boolean Expressions SQL Numeric Expressions SQL Date Expressions ”; Previous Next What is SQL Expression? An SQL expression is a combination of one or more values, operators and SQL functions that are all evaluated to a value. These SQL EXPRESSION(s) are like formulae and they are written in query language. You can also use them to query the database for a specific set of data. Expressions are used in WHERE clause of an SQL query. As you might have already known, a WHERE clause specifies a condition that needs to be satisfied for the purpose of filtering records from a database table. This condition is comprised of either single or multiple expressions. These expressions are further classified into three types − Boolean Expressions Numeric Expressions Date and time Expressions Let us discuss each of these expressions in detail further in this chapter. Syntax Consider the basic syntax of the SELECT statement containing some expressions as follows − SELECT column1, column2, columnN FROM table_name WHERE [CONDITION|EXPRESSION]; SQL Boolean Expressions SQL Boolean Expressions are SQL expressions that return only Boolean Datatype as a result. These expressions can be of two types − Boolean Expressions that check for equality of two values using SQL comparison operators. Here, equality of these values is a condition. Boolean Expressions can also contain one value paired with an SQL logical operator. In this case, the logic specified acts like a condition. They return either TRUE, FALSE or UNKNOWN as the result. If the condition is met, these expressions return TRUE; and FALSE otherwise. UNKNOWN is returned when either of the operands in the expression is a NULL value. Syntax Following is the syntax of Boolean Expression − SELECT column1, column2, columnN FROM table_name WHERE BOOLEAN EXPRESSION; Example Consider the CUSTOMERS table having the following records − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 2 Khilan 25 Delhi 1500.00 3 kaushik 23 Kota 2000.00 4 Chaitali 25 Mumbai 6500.00 5 Hardik 27 Bhopal 8500.00 6 Komal 22 Hyderabad 4500.00 7 Muffy 24 Indore 10000.00 The following query is a simple example showing the usage of an SQL Boolean Expression − SELECT * FROM CUSTOMERS WHERE SALARY = 10000; Output The output will be displayed as − ID NAME AGE ADDRESS SALARY 7 Muffy 24 Indore 10000.00 SQL Numeric Expressions SQL Numeric expressions are comprised of two operands and an SQL Arithmetic Operator. These expressions are used to perform any mathematical operation in any query. Hence, the operands must always be numerals and the return value will always be a number as well. Syntax Following is the syntax − SELECT numerical_expression as OPERATION_NAME FROM table_name WHERE NUMERICAL EXPRESSION ; Here, the numerical_expression is used for a mathematical expression or any formula. Example Following is a simple example showing the usage of SQL Numeric Expressions − SELECT 15 + 6; Output The output table is retrieved as − 21 Example There are several built-in functions like avg(), sum(), count(), etc., to perform what is known as the aggregate data calculations against a table or a specific table column. SELECT COUNT(*) FROM CUSTOMERS; Output The output is displayed as follows − 7 SQL Date Expressions SQL Date Expressions are used to compare date related values with current system date and time values. For instance, in a manufacturing company, items manufactured per year can be segregated by using date expressions in a WHERE clause. Counting from the first day of an year to the last day, the count of each item will be retrieved; once the required information is gathered, the company can use this information for their own purposes. Syntax Following is the syntax − SELECT column_name(s) FROM table_name WHERE DATE EXPRESSION ; Example In this example we are trying to simply retrieve the current timestamp of the system using CURRENT_TIMESTAMP. SELECT CURRENT_TIMESTAMP; Output The output table is displayed as − Current_Timestamp 2009-11-12 06:40:23 Example Consider the following Customer Order records in an ORDERS table: ORDER_ID CUSTOMER_ID DATE ORDER_AMOUNT ITEM_COUNT 102 3 2009-10-08 00:00:00 3000 4 100 3 2009-10-08 00:00:00 1500 2 101 2 2009-11-20 00:00:00 1560 7 103 4 2008-05-20 00:00:00 2060 3 Now let”s retrieve the records before 1st June, 2008: SELECT * FROM ORDERS WHERE DATE < ”2008/06/01”; Output The output table is displayed as − ORDER_ID CUSTOMER_ID DATE ORDER_AMOUNT ITEM_COUNT 103 4 2008-05-20 00:00:00 2060 3 Print Page Previous Next Advertisements ”;

SQL – Injection Table of content SQL Injection Preventing SQL Injection ”; Previous Next If you take a user input through a webpage and insert it into an SQL database, there is a chance that you have left yourself wide open for a security issue known as the SQL Injection. This chapter will teach you how to help prevent this from happening and help you secure your scripts and SQL statements in your server side scripts such as a PERL Script. SQL Injection SQL Injection is a type of security attack that exploits a vulnerability in a database by executing malicious queries. This will allow attackers to access sensitive data, tamper it and also delete it permanently. Injection usually occurs when you ask a user for input, like their name and instead of a name they give you a SQL statement that you will unknowingly run on your database. Never trust user provided data, process this data only after validation; as a rule, this is done by Pattern Matching. Example In the example below, the name is restricted to the alphanumerical characters plus underscore and to a length between 8 and 20 characters (you can modify these rules as needed). if (preg_match(“/^w{8,20}$/”, $_GET[”username”], $matches)) { $result = mysqli_query(“SELECT * FROM CUSTOMERS WHERE name = $matches[0]”); } else { echo “user name not accepted”; } To demonstrate the problem, consider this excerpt − // supposed input $name = “Qadir”; DELETE FROM CUSTOMERS;”; mysqli_query(“SELECT * FROM CUSTOMSRS WHERE name=”{$name}””); The function call is supposed to retrieve a record from the CUSTOMERS table where the name column matches the name specified by the user. Under normal circumstances, $name would only contain alphanumeric characters and perhaps spaces. But here, by appending an entirely new query to $name, the call to the database turns into disaster; the injected DELETE query removes all records from the CUSTOMERS table. Fortunately, if you use MySQL, the mysqli_query() function does not permit query stacking or executing multiple SQL queries in a single function call. If you try to stack queries, the call fails. However, other PHP database extensions, such as SQLite and PostgreSQL happily perform stacked queries, executing all the queries provided in one string and creating a serious security problem. Preventing SQL Injection You can handle all escape characters smartly in scripting languages like PERL and PHP. The MySQL extension for PHP provides the function mysql_real_escape_string() to escape input characters that are special to MySQL. if (get_magic_quotes_gpc()) { $name = stripslashes($name); } $name = mysql_real_escape_string($name); mysqli_query(“SELECT * FROM CUSTOMERS WHERE name=”{$name}””); The LIKE Quandary To address the LIKE quandary, a custom escaping mechanism must convert user-supplied ”%” and ”_” characters to literals. Use addcslashes(), a function that lets you specify a character range to escape. $sub = addcslashes(mysql_real_escape_string(“%str”), “%_”); // $sub == %str_ mysqli_query(“SELECT * FROM messages WHERE subject LIKE ”{$sub}%””); Print Page Previous Next Advertisements ”;

SQL – Common Table Expression (CTE) Table of content The SQL Common Table Expression The MySQL WITH Clause CTE from Multiple Tables Recursive CTE Advantages of CTE Disadvantages of CTE ”; Previous Next A Common Table Expression (CTE) can make it easier to manage and write complex queries by making them more readable and simple, like database views and derived tables. We can reuse or rewrite the query by breaking down the complex queries into simple blocks. The SQL Common Table Expression The WITH clause in MySQL is used to specify a Common Table Expression. A Common Table Expression (CTE) in SQL is a one-time result set, i.e. it is a temporary table that exists only during the execution of a single query. It allows us to work with data specifically within that query, such as using it in SELECT, UPDATE, INSERT, DELETE, CREATE, VIEW, OR MERGE statements. CTE is temporary because it cannot be stored anywhere for later use; once the query is executed, it is lost. The MySQL WITH Clause To specify common table expressions, we use WITH clause that consists of one or more comma-separated subclauses. Within each subclause, we can present a subquery that produces a result set and assigns a name to this subquery. You cannot use the WITH clause in MySQL versions before 8.0. Syntax Following is the syntax to create a CTE using WITH clause − WITH CTE_NAME (column_name) AS (query) SELECT * FROM CTE_NAME; Where, CTE_NAME − It is the name assigned to the CTE. column_name − It is the column names for the CTE, which can be useful for improving query readability. query − It defines the CTE and it can be any valid SQL query. After defining the CTE, you can reference it in subsequent queries within the same session. Example Assume we have created a table named CUSTOMERS in MySQL database using CREATE TABLE statement as shown below − CREATE TABLE CUSTOMERS ( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25), SALARY DECIMAL (18, 2), PRIMARY KEY (ID) ); Now, we are inserting some records into the above created table − INSERT INTO CUSTOMERS VALUES (1, ”Ramesh”, 32, ”Ahmedabad”, 2000.00 ), (2, ”Khilan”, 25, ”Delhi”, 1500.00 ), (3, ”Kaushik”, 23, ”Kota”, 2000.00 ), (4, ”Chaitali”, 25, ”Mumbai”, 6500.00 ), (5, ”Hardik”, 27, ”Bhopal”, 8500.00 ), (6, ”Komal”, 22, ”Hyderabad”, 4500.00 ), (7, ”Muffy”, 24, ”Indore”, 10000.00 ); The table created is as shown below − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 2 Khilan 25 Delhi 1500.00 3 Kaushik 23 Kota 2000.00 4 Chaitali 25 Mumbai 6500.00 5 Hardik 27 Bhopal 8500.00 6 Komal 22 Hyderabad 4500.00 7 Muffy 24 Indore 10000.00 Here, we are creating a Common Table Expression (CTE) named CUSTOMER_AGE that selects all customers with an age of 23. We are then retrieving the ID, NAME, and AGE of these customers from the CTE. WITH CUSTOMER_AGE AS ( SELECT * FROM customers WHERE AGE = 23) SELECT ID, NAME, AGE FROM CUSTOMER_AGE; Output Following is the output of the above query − ID NAME AGE 3 Kaushik 23 CTE from Multiple Tables We can also create a Common Table Expression (CTE) that combines data from multiple tables by using JOIN operations within the CTE”s subquery. To do this, we need to use the comma operator to separate each CTE definition, effectively merging them into a single statement. Syntax Following is the basic syntax for multiple Common Table Expression (CTE) − WITH CTE_NAME1 (column_name) AS (query), CTE_NAME2 (column_name) AS (query) SELECT * FROM CTE_NAME1 UNION ALL SELECT * FROM CTE_NAME2; We can use multiple Common Table Expressions (CTEs) with various SQL operations, such as UNION, UNION ALL, JOIN, INTERSECT, or EXCEPT. Example In here, we are defining two CTEs namely ”CUSTOMERS_IN_DELHI” and ”CUSTOMERS_IN_MUMBAI” to segregate customers based on their addresses in Delhi and Mumbai. Then, we are using the UNION ALL operator to combine the results from both CTEs into a single result set, retrieving customer information from both cities. WITH CUSTOMERS_IN_DELHI AS ( SELECT * FROM CUSTOMERS WHERE ADDRESS = ”Delhi”), CUSTOMERS_IN_MUMBAI AS ( SELECT * FROM CUSTOMERS WHERE ADDRESS = ”Mumbai”) SELECT ID, NAME, ADDRESS FROM CUSTOMERS_IN_DELHI UNION ALL SELECT ID, NAME, ADDRESS FROM CUSTOMERS_IN_MUMBAI; Output Output of the above query is as shown below − ID NAME ADDRESS 2 Khilan Delhi 4 Chaitali Mumbai Recursive CTE A common table expression is a query that keeps referring back to its own result in a loop repeatedly until it returns an empty result. A recursive query continually iterates across a subset of the data during its execution, and defines itself in a self-referencing manner. This self-referencing mechanism allows it to repeatedly process and expand its results until a stopping condition is met. To make a CTE recursive, it must include a UNION ALL statement and provide a second definition of the query that utilizes the CTE itself. This allows the CTE to repeatedly reference to its own results, creating a recursive behaviour in the query.

SQL – Using Sequences Table of content Sequences in MySQL Starting a Sequence at a Particular Value in MySQL Sequences in SQL Server ”; Previous Next Sequences in SQL are database objects that generate a sequence of unique integer values. They are frequently used in databases because many applications require that each row in a table must contain unique values and sequences provide an easy way to generate them. Sequences are a feature of many SQL database management systems, such as Oracle, PostgreSQL, SQL server, and IBM DB2. MySQL does not support the CREATE SEQUENCE statement to create sequences for table rows or columns. Instead, we can use AUTO_INCREMENT attribute. Sequences in MySQL In MySQL, we use the AUTO_INCREMENT attribute to generate unique integer values (sequences) for a column. By default, the sequence starts with an initial value of 1 and increments by 1 for each new row. Syntax Following is the syntax of AUTO_INCREMENT attribute in MySQL − CREATE TABLE table_name ( column1 datatype AUTO_INCREMENT, column2 datatype, column3 datatype, … columnN datatype ); Example In the following example, we are creating a table named CUSTOMERS. In addition to that, we are defining AUTO_INCREMENT on ID column of the table. CREATE TABLE CUSTOMERS ( ID INT AUTO_INCREMENT, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25), SALARY DECIMAL (18, 2), PRIMARY KEY (ID) ); Here, we are adding some records into the above created table − INSERT INTO CUSTOMERS VALUES (NULL, ”Ramesh”, 32, ”Ahmedabad”, 2000.00), (NULL, ”Khilan”, 25, ”Delhi”, 1500.00), (NULL, ”Kaushik”, 23, ”Kota”, 2000.00), (NULL, ”Chaitali”, 25, ”Mumbai”, 6500.00), (NULL, ”Hardik”, 27, ”Bhopal”, 8500.00), (NULL, ”Komal”, 22, ”Hyderabad”, 4500.00), (NULL, ”Muffy”, 24, ”Indore”, 10000.00); The table will be created as follows − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 2 Khilan 25 Delhi 1500.00 3 Kaushik 23 Kota 2000.00 4 Chaitali 25 Mumbai 6500.00 5 Hardik 27 Bhopal 8500.00 6 Komal 22 Hyderabad 4500.00 7 Muffy 24 Indore 10000.00 As we can see in the above table, the values in the ID column are auto incremented. Starting a Sequence at a Particular Value in MySQL By default, MySQL sequences start from 1. To start a sequence with a different value, we use the AUTO_INCREMENT in combination with the ALTER statement. Syntax Following is the syntax to start the sequence with different value − ALTER TABLE table_name AUTO_INCREMENT = value; In the following query, we are creating a table named BUYERS with AUTO_INCREMENT defined on the ID column. CREATE TABLE BUYERS ( ID INT AUTO_INCREMENT, NAME VARCHAR(20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25), SALARY DECIMAL (18, 2), PRIMARY KEY (ID) ); Here, we are making the sequence start with 100 using the ALTER Statement as shown below − ALTER TABLE BUYERS AUTO_INCREMENT=100; Now, we are adding records into the BUYERS table using the INSERT INTO statement − INSERT INTO BUYERS VALUES (”Ramesh”, 32, ”Ahmedabad”, 2000.00), (”Khilan”, 25, ”Delhi”, 1500.00), (”Kaushik”, 23, ”Kota”, 2000.00), (”Chaitali”, 25, ”Mumbai”, 6500.00), (”Hardik”, 27, ”Bhopal”, 8500.00), (”Komal”, 22, ”Hyderabad”, 4500.00), (”Muffy”, 24, ”Indore”, 10000.00); The table will be created as − ID NAME AGE ADDRESS SALARY 100 Ramesh 32 Ahmedabad 2000.00 101 Khilan 25 Delhi 1500.00 102 Kaushik 23 Kota 2000.00 103 Chaitali 25 Mumbai 6500.00 104 Hardik 27 Bhopal 8500.00 105 Komal 22 Hyderabad 4500.00 106 Muffy 24 Indore 10000.00 As observed in the table above, the values in the “ID” column begin with 100 instead of 1. Sequences in SQL Server In SQL server, a sequence can be created using the CREATE SEQUENCE statement. The statement specifies the name of the sequence, the starting value, the increment, and other properties of the sequence. Syntax Following is the syntax to create a sequence in SQL − CREATE SEQUENCE Sequence_Name START WITH Initial_Value INCREMENT BY Increment_Value MINVALUE Minimum_Value MAXVALUE Maximum_Value CYCLE|NOCYCLE; Here, Sequence_Name − This specifies the name of the sequence. Initial_Value − This specifies the starting value from where the sequence should start. Increment_Value − This specifies the value by which the sequence will increment by itself. This can be valued positively or negatively. Minimum_Value − This specifies the minimum value of the sequence. Maximum_Value − This specifies the maximum value of the sequence. Cycle − When the sequence reaches its Maximum_Value, it starts again from the beginning. Nocycle − An exception will be thrown if the sequence exceeds the Maximum_Value. Example First of all, let us create a table named CUSTOMERS using the following query − CREATE TABLE CUSTOMERS ( ID INT, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25), SALARY DECIMAL (18, 2), ); We are inserting some records in the above-created table using INSERT INTO statement as shown in the query below − INSERT INTO CUSTOMERS VALUES (NULL, ”Ramesh”, 32, ”Ahmedabad”, 2000.00), (NULL, ”Khilan”, 25, ”Delhi”, 1500.00), (NULL, ”Kaushik”, 23, ”Kota”, 2000.00), (NULL, ”Chaitali”, 25, ”Mumbai”, 6500.00), (NULL, ”Hardik”, 27, ”Bhopal”, 8500.00), (NULL, ”Komal”, 22, ”Hyderabad”, 4500.00), (NULL, ”Muffy”, 24, ”Indore”, 10000.00 ); The table is successfully created in the SQL database. ID NAME AGE ADDRESS SALARY NULL Ramesh 32 Ahmedabad 2000.00 NULL Khilan 25 Delhi 1500.00 NULL Kaushik 23 Kota 2000.00 NULL Chaitali 25 Mumbai 6500.00 NULL Hardik 27 Bhopal 8500.00 NULL Komal 22 Hyderabad 4500.00 NULL Muffy 24 Indore 10000.00 Now, create a sequence using the following query − CREATE SEQUENCE My_Sequence AS INT START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 7 CYCLE; In the above query, the sequence is named My_Sequence and it starts with the value 1 and increments by 1 each time a value is generated. The sequence has a maximum value of 5 and cycles back to the starting value when it reaches the maximum value. Once the sequence is created, it can be used to generate unique integer values. Now, let us update the data in the ID column of the CUSTOMERS table using the following query − UPDATE CUSTOMERS SET ID = NEXT VALUE FOR my_Sequence; Output When you execute the above query, the