Category: sql

SQL – Create Index Table of content What is SQL Index? Creating an SQL Index Creating an Index on Multiple Fields ”; Previous Next An index is an effective way to quickly retrieve data from an SQL database. It is a database object that references the data stored in a table, which significantly improves query and application performance of a database. The process of indexing in SQL is similar to that of an index in a book: it is a database object in the form of a table, contains details of data location, and holds a separate storage space. Even though indexes help accelerate search queries, users are not able to directly see these indexes in action. What is SQL Index? An SQL index is a special lookup table that helps in efficiently searching or querying database tables to retrieve required data. For example, when we try to retrieve data from multiple tables using joins, indexes improve the query performance. Indexes are used to optimize the query performance of any Relational Database Management System (RDBMS) as data volumes grow. Hence, they are preferred to be used on frequently queried large database tables. Creating an SQL Index An index can be created on one or more columns of a table in an SQL database using the CREATE INDEX statement. Syntax Following is the syntax of CREATE INDEX statement in SQL − CREATE INDEX index_name ON table_name (column_name1, column_name2,… column_nameN); Here, index_name This specifies the name of the index that you want to create. table_name This specifies the name of the table on which you want to create the index. (column_name1, column_name2…column_nameN) are the names of one or more columns on which the index is being created. Example To create an index on a database table, we first need to create a table. So, in this example, we are creating a table named CUSTOMERS using the following query − CREATE TABLE CUSTOMERS( ID INT NOT NULL, NAME VARCHAR(15) NOT NULL, AGE INT NOT NULL, ADDRESS VARCHAR(25), SALARY DECIMAL(10, 4), PRIMARY KEY(ID)); ); Then, insert some values into the CUSTOMERS table using the following query − INSERT INTO CUSTOMERS VALUES (1, ”Ramesh”, ”32”, ”Ahmedabad”, 2000), (2, ”Khilan”, ”25”, ”Delhi”, 1500), (3, ”Kaushik”, ”23”, ”Kota”, 2000), (4, ”Chaitali”, ”25”, ”Mumbai”, 6500), (5, ”Hardik”,”27”, ”Bhopal”, 8500), (6, ”Komal”, ”22”, ”Hyderabad”, 9000), (7, ”Muffy”, ”24”, ”Indore”, 5500); Once the table is created, create an index for the column named NAME in the CUSTOMERS table using the following query − CREATE INDEX index_name ON CUSTOMERS(NAME); Output When we execute the above query, the output is obtained as follows − Query OK, 0 rows affected (0.06 sec) Records: 0 Duplicates: 0 Warnings: 0 Verification The following SHOW INDEX query is used to display all the indexes created on an existing table. SHOW INDEX FROM CUSTOMERS; In the list obtained, you can find the column name NAME, along with the ID in the list of indexes. Table Non_unique Key_name Seq_in_index Column_name customers 0 PRIMARY 1 ID customers 1 index_name 1 NAME Creating an Index on Multiple Fields We can also create an index on multiple fields (or columns) of a table using the CREATE INDEX statement. To do so, you just need to pass the name of the columns (you need to create the index on). Example Instead of creating a new table, let us consider the previously created CUSTOMERS table. Here, we are creating an index on the columns NAME and AGE using the following query − CREATE INDEX mult_index_data on CUSTOMERS(NAME, AGE); Output When we execute the above query, the output is obtained as follows − Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0 Verification Now, let us list all the indexes that are created on the CUSTOMERS table using the following SHOW INDEX query − SHOW INDEX FROM CUSTOMERS; As you observe, you can find the column names NAME, and AGE along with ID (PRIMARY KEY), in the list of indexes. Table Non_unique Key_name Seq_in_index Column_name customers 0 PRIMARY 1 ID customers 1 index_name 1 NAME customers 1 mult_index_data 1 NAME customers 1 mult_index_data 2 AGE Print Page Previous Next Advertisements ”;

SQL – Stored Procedures Table of content SQL Stored Procedures Creating a Procedure Stored Procedure Parameter Types Procedure with IN parameter Procedure with OUT parameter Procedure with INOUT parameter Advantages of Stored Procedures Drawbacks of Stored Procedures ”; Previous Next SQL Stored Procedures An SQL stored procedure is a group of pre-compiled SQL statements (prepared SQL code) that can be reused by simply calling it whenever needed. It can be used to perform a wide range of database operations such as inserting, updating, or deleting data, generating reports, and performing complex calculations. Stored procedures are very useful because they allow you to encapsulate (bundle) a set of SQL statements as a single unit and execute them repeatedly with different parameters, making it easy to manage and reuse the code. Procedures have similar structure as functions: they accept parameters and perform operations when we call them. But, the difference between them is that SQL stored procedures are simpler to write or create, whereas functions have a more rigid structure and support fewer clauses. Syntax The basic syntax to create an SQL stored procedure is as follows − DELIMITER // CREATE PROCEDURE procedure_name(parameter1 datatype, parameter2 datatype, …) BEGIN — SQL statements to be executed END DELIMITER ; The CREATE PROCEDURE statement is used to create the procedure. We can define any number of input parameters as per the requirement. The SQL statements that make up the procedure are placed between the BEGIN and END keywords. Creating a Procedure We can create a stored procedure using the CREATE PROCEDURE statement in SQL. Following are the simple steps for creating a stored procedure − Choose a name for the procedure. Write the SQL code for the procedure. We can then test the stored procedure by executing it with different input parameters. Example To understand it better let us consider the CUSTOMERS table which contains the personal details of customers including their name, age, address and salary etc. 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, insert values into this table using the INSERT statement as follows − 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 − 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 Now, let us look at a simple example of creating a stored procedure that takes an input parameter and returns a result set. In the following query, we are creating the stored procedure with the name GetCustomerInfo. then we provide it with a single input parameter called @CutomerAge. The stored procedure then selects all records from the CUSTOMERS table where the value of the CutomerAge matches the input parameter. DELIMITER // CREATE PROCEDURE GetCustomerInfo(IN CustomerAge INT) BEGIN SELECT * FROM CUSTOMERS WHERE AGE = CustomerAge; END // DELIMITER ; Output This would produce the following result − Query OK, 0 rows affected (0.01 sec) Verification We can test the stored procedure by executing it using the CALL statement as shown below − CALL GetCustomerInfo(25); This will return all columns from the CUSTOMERS table where the customers age is 25. ID NAME AGE ADDRESS SALARY 2 Khilan 25 Delhi 1500.00 4 Chaitali 25 Mumbai 6500.00 Stored Procedure Parameter Types Stored procedures in a database system can have different types of parameters, which are placeholders for values that will be passed to the stored procedure when it is executed. Following are the different types of stored procedure parameters in SQL − S.No. Parameter & Description 1 Input parameters These parameters are used to pass values from the calling statement to the stored procedure. 2 Output parameters These parameters are used to return values from the stored procedure. 3 Input/Output parameters These parameters allow a stored procedure to accept input values and return output values. Procedure with IN parameter IN is the default parameter of the procedure that will receive input values. We can pass the values as arguments when the stored procedure is being called. These values are read-only, so they cannot be modified by the stored procedure. Example In the following query, we are creating a stored procedure that takes a customer ID as an input parameter and returns the corresponding customer salary. The procedure body simply performs a SELECT statement to retrieve the “Salary” column from the “CUSTOMERS” table, where the “CustomerID” matches the input parameter. DELIMITER // CREATE PROCEDURE GetCustomerSalary(IN CustomerID Int) BEGIN SELECT SALARY FROM CUSTOMERS WHERE ID = CustomerID; END // DELIMITER ; Output This would produce

SQL – Alias Syntax Table of content The SQL Aliasing Aliasing Column Names Aliasing with Self Join ”; Previous Next You can rename a table or a column in a database temporarily by giving them another pseudo name. This pseudo name is known as Alias. The use of aliases is to address a specific table or a column in an SQL statement without changing their original name in the database. Aliases are created with the AS keyword. Aliases can be especially useful when working with complex queries involving multiple tables or columns with similar names. By assigning temporary names to these tables or columns, you can make your SQL query more readable and easier to understand. The SQL Aliasing Aliases are used to address database tables with a shorter or more meaningful name within an SQL query. The basic syntax of a table alias is as follows. SELECT column1, column2…. FROM table_name AS alias_name; Example Assume we have created a table with name 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) ); Following query inserts values into this table using the INSERT statement − 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 CUSTOMERS table obtained is 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 Now, we are creating the second table ORDERS using CREATE TABLE statement as shown below − CREATE TABLE ORDERS ( OID INT NOT NULL, DATES DATETIME NOT NULL, CUSTOMER_ID INT NOT NULL, AMOUNT INT NOT NULL, PRIMARY KEY (OID) ); Following query inserts values into this table using the INSERT statement − INSERT INTO ORDERS VALUES (102, ”2009-10-08 00:00:00”, 3, 3000), (100, ”2009-10-08 00:00:00”, 3, 1500), (101, ”2009-11-20 00:00:00”, 2, 1560), (103, ”2008-05-20 00:00:00”, 4, 2060); The ORDERS table obtained is as shown below − OID DATE CUSTOMER_ID AMOUNT 102 2009-10-08 00:00:00 3 3000.00 100 2009-10-08 00:00:00 3 1500.00 101 2009-11-20 00:00:00 2 1560.00 103 2008-05-20 00:00:00 4 2060.00 Now, the following query shows the usage of a table alias. The CUSTOMERS table is aliased as ”C” and the ORDERS table is aliased as ”O” − SELECT C.ID, C.NAME, C.AGE, O.AMOUNT FROM CUSTOMERS AS C, ORDERS AS O WHERE C.ID = O.CUSTOMER_ID; Output This would produce the following result − ID NAME AGE AMOUNT 3 Kaushik 23 3000.00 3 Kaushik 23 1500.00 2 Khilan 25 1560.00 4 Chaitali 25 2060.00 Aliasing Column Names We can also use an alias for a column name in SQL to give it a different name in the result set of a query. The basic syntax of a column alias is as follows − SELECT column_name AS alias_name FROM table_name; Example Following is the usage of a column alias. Here, the NAME column is aliased as ”CUSTOMER_NAME” − SELECT ID AS CUSTOMER_ID, NAME AS CUSTOMER_NAME FROM CUSTOMERS; Output This would produce the following result − CUSTOMER_ID CUSTOMER_NAME 1 Ramesh 2 Khilan 3 Kaushik 4 Chaitali 5 Hardik 6 Komal 7 Muffy Aliasing with Self Join The SQL Self Join is used to join a table to itself as if the table were two tables. During this process, we need to use alias for one of the tables with a temporary name to avoid misunderstandings. This renaming is done using aliases. Syntax Following is the syntax for performing a self-join with aliases − SELECT column_name(s) FROM my_table a, my_table b ON a.join_column = b.join_column; Example Now, let us join the CUSTOMERS table to itself using the following Self Join query. Our aim is to establish a relationship among customers on the basis of their earnings. In here, we are using aliases with column names as well as with the table names − SELECT a.ID, b.NAME as EARNS_HIGHER, a.NAME as EARNS_LESS, a.SALARY as LOWER_SALARY FROM CUSTOMERS a, CUSTOMERS b WHERE a.SALARY Output Output of the above query is as follows − ID EARNS_HIGHER EARNS_LESS LOWER_SALARY 2 Ramesh Khilan 1500.00 2 Kaushik Khilan 1500.00 6 Chaitali Komal 4500.00 3 Chaitali Kaushik 2000.00 2 Chaitali Khilan 1500.00 1 Chaitali Ramesh 2000.00 6 Hardik Komal 4500.00 4 Hardik Chaitali 6500.00 3 Hardik Kaushik 2000.00 2 Hardik Khilan 1500.00 1 Hardik Ramesh 2000.00 3 Komal Kaushik 2000.00 2 Komal Khilan 1500.00 1 Komal Ramesh 2000.00 6 Muffy Komal 4500.00 5 Muffy Hardik 8500.00 4 Muffy Chaitali 6500.00 3 Muffy Kaushik 2000.00 2 Muffy Khilan 1500.00 1 Muffy Ramesh 2000.00 Print Page Previous Next Advertisements ”;

SQL – Check Constraint Table of content The SQL CHECK Constraint Check Constraint on Single Column Check Constraint on Multiple Columns Check Constraint at the Table Level Check Constraint on an Existing Column Removing a Check Constraint ”; Previous Next The SQL CHECK Constraint The SQL CHECK constraint is used to add conditions on a column of a table. Once you add the check constraint on a column, it ensures that the data entered into the column meets the specified conditions. If a particular record does not meet the conditions, the database will prevent you from inserting or updating that record. Suppose we have a table CUSTOMERS having a column AGE. We can add a CHECK constraint on this column to ensure that the age entered is always a positive number and not greater than 50 years. If someone tries to input a negative age or an age over 50, the database will reject it, ensuring that your data remains accurate and valid. Check Constraint on Single Column To add a check constraint on a column level, we have to specify the check constraint just after the column name during table creation. Syntax Following is the syntax to specify the check constraint on a single column − CREATE TABLE table_name ( column_name data_type CHECK (condition) ); Example In the following query, we are creating a table named CUSTOMERS. Here, we are specifying a column-level check constraint on the AGE column, that allows only those records to be inserted where the age value of the customer is greater than “20” − CREATE TABLE CUSTOMERS ( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL CHECK(AGE>=20), ADDRESS CHAR (25), SALARY DECIMAL (18, 2), PRIMARY KEY (ID) ); Verification To verify whether the check constraint is added to the AGE column, we can use the following query in the MySQL database − SELECT table_name, constraint_type, constraint_name FROM information_schema.table_constraints WHERE table_name=”CUSTOMERS”; Output The above query will show all the details of the CUSTOMERS table, including how many columns have check constraints and what constraints we have specified in the table as shown below − TABLE_NAME CONSTRAINT_TYPE CONSTRAINT_NAME customers PRIMARY KEY PRIMARY customers PRIMARY KEY PRIMARY customers PRIMARY KEY PRIMARY customers PRIMARY KEY PRIMARY customers CHECK employees_chk_1 Now, to verify if the CHECK constraint is working properly, let us insert a record into CUSTOMERS where AGE contains a value less than 20 (does not satisfy the given condition) − INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, ”Ramesh”, 15, ”Ahmedabad”, 2000.00 ); The output of the above query is as shown below − ERROR 3819 (HY000): Check constraint ”customers_chk_1” is violated. Check Constraint on Multiple Columns We can also add check constraint on multiple columns of a table by specifying the conditions that must be met for the combination of values in those columns. Suppose we have a table containing the details of products, including their start and end dates. We can add a CHECK constraint that ensures the end date is always greater than or equal to the start date. In this case, the constraint is checking the values in two columns (start date and end date) within the same row to make sure they follow a specific relationship. Example In the following example, we are specifying a column-level check constraint on multiple columns (AGE and SALARY) of the CUSTOMERS table. Here, the AGE column will allow only those records where the AGE is greater than or equal to 20, and the SALARY column will allow only those records where the SALARY is greater than 20000 − CREATE TABLE CUSTOMERS ( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL CHECK(AGE >= 20), ADDRESS CHAR (25), SALARY DECIMAL (18, 2) CHECK(SALARY >= 20000), PRIMARY KEY (ID) ); Verification To verify whether the check constraint is applied on both the columns, we can use the following query in the MySQL database − SELECT table_name, constraint_type, constraint_name FROM information_schema.table_constraints WHERE table_name=”CUSTOMERS”; Output It will show all the details of the created table, including how many columns have check constraints and what constraints we have specified in the table − TABLE_NAME CONSTRAINT_TYPE CONSTRAINT_NAME customers PRIMARY KEY PRIMARY customers PRIMARY KEY PRIMARY customers PRIMARY KEY PRIMARY customers PRIMARY KEY PRIMARY customers CHECK customers_chk_1 customers CHECK customers_chk_2 Now, we are inserting values into the CUSTOMERS table where the age is less than 20 and the salary is less than 20000. INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, ”Ramesh”, 15, ”Ahmedabad”, 2000.00 ); The above query throws an error because the values passed in the AGE and the SALARY columns are not satisfying the CHECK constraints − ERROR 3819 (HY000): Check constraint ”customers_chk_1” is violated. Check Constraint at the Table Level We must use the check constraint before completing the table creation in order to ensure the check constraint at the table level. Syntax Following is the syntax to specify the check constraint on the table level − CREATE TABLE table_name ( column1 data_type, column2 data_type,…, CONSTRAINT constraint_name CHECK(column_name condition_value) ); Example In the following SQL query, we are creating a table PRODUCTS. In here, we are specifying a

SQL – Alternate Key Table of content The SQL Alternate Key Keys in a table ”; Previous Next The SQL Alternate Key SQL Alternate Keys in a database table are candidate keys that are not currently selected as a primary key. They can be used to uniquely identify a tuple(or a record) in a table. There is no specific query or syntax to set the alternate key in a table. It is just a column that is a close second candidate which could be selected as a primary key. Hence, they are also called secondary candidate keys. If a database table consists of only one candidate key, that is treated as the primary key of the table, then there is no alternate key in that table. Let us understand the concept of alternate key with an example. Suppose we have a table named CUSTOMERS with various fields like ID, NAME, AGE, AADHAAR_ID, MOBILE_NO and SALARY as shown below. The details like id, mobile number and aadhaar number of a customer are unique, and we can identify the records from the CUSTOMERS table uniquely using their respective fields; ID, AADHAAR_ID and MOBILE_NO. Therefore, these three fields can be treated as candidate keys. And among them, if one is declared as the primary key of the CUSTOMERS table then the remaining two would be alternate keys. Features of Alternate Keys Following are some important properties/features of alternate keys − The alternate key does not allow duplicate values. A table can have more than one alternate keys. The alternate key can contain NULL values unless the NOT NULL constraint is set explicitly. All alternate keys can be candidate keys, but all candidate keys can not be alternate keys. The primary key, which is also a candidate key, can not be considered as an alternate key. Example For a better understanding, let us create the above discussed table demonstrating the usage of the various keys and illustrating the fields that can be considered as alternate keys. CREATE TABLE CUSTOMERS( ID INT, NAME VARCHAR (20), AGE INT, AADHAAR_ID BIGINT, MOBILE_NO BIGINT, SALARY DECIMAL (18, 2), PRIMARY KEY(ID) ); Now, insert some records into the CUSTOMERS table using the INSERT statement as shown below − INSERT INTO CUSTOMERS VALUES (1, ”Ramesh”, 32, 90123498456, 9023456789, 22000.00 ), (2, ”Khilan”, 25, 91123249545, 9032456578, 24500.34 ), (3, ”Kaushik”, 23, 91223242546, 9012436789, 20000.12 ); The table will be created as − ID NAME AGE AADHAAR_ID MOBILE_NO SALARY 1 Ramesh 32 90123498456 9023456789 22000.00 2 Khilan 25 91123249545 9032456578 24500.34 3 Kaushik 23 91223242546 9012436789 20000.12 Keys in a table As a summary lets revisit all the keys in a database table − Candidate Key A Candidate key is a subset of super keys that is used to uniquely identify records of a table. It can be a single field or multiple fields. The primary keys, alternate keys, foreign keys in a table are all types of candidate key. Primary Key A Primary Key is a main key that is used to retrieve records from a table. It is a single column or field in a table that uniquely identifies each record in a database table. It can be set using the PRIMARY KEY keyword while creating a table using the CREATE TABLE statement. Following is the basic syntax to create primary key constraint on a column in a table − CREATE TABLE table_name( column1 datatype, column2 datatype, column3 datatype, ….. columnN datatype, PRIMARY KEY(column_name) ); Foreign Key The Primary key of one table will be the Foreign key in another table. While inserting values into these tables, values in the primary key field must match the values in the foreign key field; otherwise, the foreign key column will not accept the INSERT query and throws an error. In SQL server, the syntax to set a foreign key field in a table is − CREATE TABLE table_name ( column1 datatype, column2 datatype, … CONSTRAINT fk_name FOREIGN KEY (column_name) REFERENCES referenced_table(referenced_column) ); Alternate Key An Alternate key is a candidate key that could be a primary key but is not. Like primary key, it also uniquely identifies the records in a field of a table to retrieve row tuples from the said table. There can be a single or multiple fields identifying as alternate keys in a table. Print Page Previous Next Advertisements ”;

SQL – Numeric Functions ”; Previous Next SQL numeric functions are used primarily for numeric manipulation and/or mathematical calculations. The following table details the numeric functions − Sr.No. Function & Description 1 ABS() Returns the absolute value of numeric expression. 2 ACOS() Returns the arccosine of numeric expression. Returns NULL if the value is not in the range -1 to 1. 3 ASIN() Returns the arcsine of numeric expression. Returns NULL if value is not in the range -1 to 1 4 ATAN() Returns the arctangent of numeric expression. 5 ATN2() Returns the arctangent of the two variables passed to it. 6 CEILING() Returns the smallest (closest to negative infinity) integer value that is greater than or equal to this value. 7 COS() Returns the trigonometric cosine of the given value. 8 COT() Returns the trigonometric cotangent of the given value. 9 DEGREES() Returns numeric expression converted from radians to degrees. 10 EXP() Returns the base of the natural logarithm (e) raised to the power of passed numeric expression. 11 FLOOR() Returns the largest integer value that is not greater than passed numeric expression. 12 LOG() Returns the natural logarithm of the passed numeric expression. 13 LOG10() Returns the base-10 logarithm of the passed numeric expression. 14 PI() Returns the value of pi 15 POWER() Returns the value of one expression raised to the power of another expression 16 RADIANS() Returns the value of passed expression converted from degrees to radians. 17 RAND() Returns the random value between 0 and 1. 18 ROUND() Returns numeric expression rounded to an integer. Can be used to round an expression to a number of decimal points 19 SIGN() Returns the sign of a number, indicating whether it is positive, negative, or zero. 20 SIN() Returns the sine of numeric expression given in radians. 21 SQRT() Returns the non-negative square root of numeric expression. 22 TAN() Returns the tangent of numeric expression expressed in radians. Print Page Previous Next Advertisements ”;

SQL – UNION vs UNION ALL Table of content What is UNION? What is UNION ALL ”; Previous Next UNION and UNION ALL operators are just the SQL implementation of algebraic set operators. Both of them are used to retrieve the rows from multiple tables and return them as one single table. The difference between these two operators is that UNION only returns distinct rows while UNION ALL returns all the rows present in the tables. However, for these operators to work on these tables, they need to follow the conditions given below − The tables to be combined must have the same number of columns with the same datatype. The number of rows need not be the same. Once these criterion are met, UNION or UNION ALL operator returns the rows from multiple tables as a resultant table. Column names of first table will become column names of the resultant table, and contents of second table will be merged into resultant columns of same data type. What is UNION? UNION is a type of operator/clause in SQL, that works similar to the union operator in relational algebra. It just combines the information from multiple tables that are union compatible. Only distinct rows from the tables are added to the resultant table, as UNION automatically eliminates all the duplicate records. Syntax Following is the syntax of UNION operator in SQL − SELECT * FROM table1 UNION SELECT * FROM table2; Example Let us first create two tables COURSES_PICKED and EXTRA_COURSES_PICKED with the same number of columns having the same data types. Create table COURSES_PICKED using the following query − CREATE TABLE COURSES_PICKED( STUDENT_ID INT NOT NULL, STUDENT_NAME VARCHAR(30) NOT NULL, COURSE_NAME VARCHAR(30) NOT NULL ); Insert values into the COURSES_PICKED table with the help of the query given below − INSERT INTO COURSES_PICKED VALUES (1, ”JOHN”, ”ENGLISH”), (2, ”ROBERT”, ”COMPUTER SCIENCE”), (3, ”SASHA”, ”COMMUNICATIONS”), (4, ”JULIAN”, ”MATHEMATICS”); The table will be displayed as shown below − STUDENT_ID STUDENT_NAME COURSE_NAME 1 JOHN ENGLISH 2 ROBERT COMPUTER SCIENCE 3 SASHA COMMUNICATIONS 4 JULIAN MATHEMATICS Now, let us create another table EXTRA_COURSES_PICKED using the following query − CREATE TABLE EXTRA_COURSES_PICKED( STUDENT_ID INT NOT NULL, STUDENT_NAME VARCHAR(30) NOT NULL, EXTRA_COURSE_NAME VARCHAR(30) NOT NULL ); Following is the query to insert values into the “EXTRA_COURSES_PICKED” table − INSERT INTO EXTRA_COURSES_PICKED VALUES (1, ”JOHN”, ”PHYSICAL EDUCATION”), (2, ”ROBERT”, ”GYM”), (3, ”SASHA”, ”FILM”), (4, ”JULIAN”, ”MATHEMATICS”); The table will be created as shown below − STUDENT_ID STUDENT_NAME COURSES_PICKED 1 JOHN PHYSICAL EDUCATION 2 ROBERT GYM 3 SASHA FILM 4 JULIAN MATHEMATICS Now, let us combine both of these tables using the UNION query as follows − SELECT * FROM COURSES_PICKED UNION SELECT * FROM EXTRA_COURSES_PICKED; Output The resultant table obtained after performing the UNION operation is as follows − STUDENT_ID STUDENT_NAME COURSE_NAME 1 JOHN ENGLISH 2 ROBERT COMPUTER SCIENCE 3 SASHA COMMUNICATIONS 4 JULIAN MATHEMATICS 1 JOHN PHYSICAL EDUCATION 2 ROBERT GYM 3 SASHA FILM What is UNION ALL? UNION ALL is also an operator/clause in SQL, that is used to combine multiple tables into one table. However, this operator also preserves the duplicate rows in the resultant tables. Suppose there are two tables, one of which contains the number of games a player competed in internationally and the other contains the number of games a player played nationally. As we can see in the tables above, Kohli played 234 matches internationally and 234 matches nationally. Even though the data in these columns is the same, they are all separate matches. There is a need to include both rows in the resultant table displaying the total matches played by a player. So, we use the UNION ALL operator in such cases. Syntax Following is the syntax of UNION ALL operator in SQL − SELECT * FROM table1 UNION ALL SELECT * FROM table2; Example In the following example, let us perform UNION ALL operation on the same sample tables given above: “COURSES_PICKED” and “EXTRA_COURSES_PICKED”, using the given query below − SELECT * FROM COURSES_PICKED UNION ALL SELECT * FROM EXTRA_COURSES_PICKED; Output The resultant table is displayed as follows − STUDENT_ID STUDENT_NAME COURSE_NAME 1 JOHN ENGLISH 2 ROBERT COMPUTER SCIENCE 3 SASHA COMMUNICATIONS 4 JULIAN MATHEMATICS 1 JOHN PHYSICAL EDUCATION 2 ROBERT GYM 3 SASHA FILM 4 JULIAN MATHEMATICS Print Page Previous Next Advertisements ”;

SQL – Having Clause Table of content The SQL HAVING Clause HAVING with GROUP BY Clause HAVING with ORDER BY Clause HAVING Clause with COUNT() Function HAVING Clause with AVG() Function HAVING Clause with MAX() Function ”; Previous Next The SQL HAVING Clause The SQL HAVING clause is similar to the WHERE clause; both are used to filter rows in a table based on specified criteria. However, the HAVING clause is used to filter grouped rows instead of single rows. These rows are grouped together by the GROUP BY clause, so, the HAVING clause must always be followed by the GROUP BY clause. Moreover, the HAVING clause can be used with aggregate functions such as COUNT(), SUM(), AVG(), etc., whereas the WHERE clause cannot be used with them. Syntax Following is the basic syntax of the SQL HAVING clause − SELECT column1, column2, aggregate_function(column) FROM table_name GROUP BY column1, column2 HAVING condition; The following code block shows the position of the HAVING Clause in a query − SELECT FROM WHERE GROUP BY HAVING ORDER BY HAVING with GROUP BY Clause We can use the HAVING clause with the GROUP BY clause to filter groups of rows that meet certain conditions. It is used to apply a filter to the result set after the aggregation has been performed. Example Assume we have created a table named CUSTOMERS, which contains the personal details of customers including their name, age, address and salary, 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 insert values into this table using the INSERT statement as follows − 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 Now, we are grouping the records of the CUSTOMERS table based on the columns ADDRESS and AGE and filtering the groups where the AGE value is less than 25. SELECT ADDRESS, AGE, MIN(SALARY) AS MIN_SUM FROM CUSTOMERS GROUP BY ADDRESS, AGE HAVING AGE > 25; Output The result produced is as follows − ADDRESS AGE MIN_SUM Ahmedabad 32 2000.00 Bhopal 27 8500.00 HAVING with ORDER BY Clause The ORDER BY clause is used to arrange/sort the records of the result of a SELECT query based on a specific column (either in ascending order or in descending order). If we use the ORDER BY clause with the HAVING clause we can sort the filtered groups in the desired order. Example Following query groups the records of the CUSTOMERS table based on the columns AGE and ADDRESS, filters the groups where the SALARY value is less than 5000 and, arranges the remaining groups in descending order based the total salaries of each group. SELECT ADDRESS, AGE, SUM(SALARY) AS TOTAL_SALARY FROM CUSTOMERS GROUP BY ADDRESS, AGE HAVING TOTAL_SALARY >=5000 ORDER BY TOTAL_SALARY DESC; Output The result produced is as follows − ADDRESS AGE TOTAL_SALARY Indore 24 10000.00 Bhopal 27 8500.00 Mumbai 25 6500.00 HAVING Clause with COUNT() Function The HAVING clause can be used with the COUNT() function to filter groups based on the number of rows they contain. Example Following query groups the records of the CUSTOMERS table based on the AGE column and, retrieves the details of the group that has more than two entities − SELECT AGE, COUNT(AGE) FROM CUSTOMERS GROUP BY AGE HAVING COUNT(age) > 2; Output This would produce the following result − Query OK, 0 rows affected (0.02 sec) HAVING Clause with AVG() Function The HAVING clause can also be used with the AVG() function to filter groups based on the average value of a specified column. Example Now, we are retrieving the city of the customers whose average salary is greater than 5240 − SELECT ADDRESS, AVG(SALARY) as AVG_SALARY FROM CUSTOMERS GROUP BY ADDRESS HAVING AVG(SALARY) > 5240; Output Following is the output of the above query − ADDRESS AVG_SALARY Mumbai 6500.000000 Bhopal 8500.000000 Indore 10000.000000 HAVING Clause with MAX() Function We can also use the HAVING clause with MAX() function to filter groups based on the maximum value of a specified column. Example Now, we are retrieving the city of the customers whose maximum salary is greater than 5240 − SELECT ADDRESS, MAX(SALARY) as MAX_SALARY FROM CUSTOMERS GROUP BY ADDRESS HAVING MAX(SALARY) > 5240; Output The result obtained is as follows − ADDRESS MAX_SALARY Mumbai 6500.00 Bhopal 8500.00 Indore 10000.00 Print Page Previous Next Advertisements ”;

SQL – Joins Table of content The SQL Join Clause Types of joins in SQL ”; Previous Next The SQL Join Clause The SQL Join clause is used to combine data from two or more tables in a database. When the related data is stored across multiple tables, joins help you to retrieve records combining the fields from these tables using their foreign keys. The part of the Join clause that specifies the columns on which records from two or more tables are joined is known as join-predicate. This predicate is usually specified along with the ON clause and uses various comparison operators such as, <, >, <>, <=, >=, !=, BETWEEN, LIKE, and NOT etc. We can also connect multiple join predicates with logical operators AND, OR, and NOT. We can use JOINs along with update and delete, SQL queries to update and delete records from across multiple tables. When you retrieve a table using joins, the resultant table displayed is not stored anywhere in the database. Syntax Following is the basic syntax of a the SQL JOIN CLAUSE − SELECT column_name(s) FROM table1 JOIN table2; Example Assume we have created a CUSTOMERS table that contains details of the customers of an organization 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 insert values into this table using the INSERT statement as follows − 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 CUSTOMERS 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 Following is another table ORDERS which contains the order details made by the customers. CREATE TABLE ORDERS ( OID INT NOT NULL, DATE VARCHAR (20) NOT NULL, CUSTOMER_ID INT NOT NULL, AMOUNT DECIMAL (18, 2) ); Using the INSERT statement, insert values into this table as follows − INSERT INTO ORDERS VALUES (102, ”2009-10-08 00:00:00”, 3, 3000.00), (100, ”2009-10-08 00:00:00”, 3, 1500.00), (101, ”2009-11-20 00:00:00”, 2, 1560.00), (103, ”2008-05-20 00:00:00”, 4, 2060.00); The ORDERS table will be created as follows − OID DATE CUSTOMER_ID AMOUNT 102 2009-10-08 00:00:00 3 3000.00 100 2009-10-08 00:00:00 3 1500.00 101 2009-11-20 00:00:00 2 1560.00 103 2008-05-20 00:00:00 4 2060.00 Following query performs the join operation on the tables CUSTMERS and ORDERS − SELECT ID, NAME, AGE, AMOUNT FROM CUSTOMERS JOIN ORDERS ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID; Output By executing the query above, the resultant table is displayed and contains the values present in ID, NAME, AGE fields of CUSTOMERS table and AMOUNT field of ORDERS table. ID NAME AGE AMOUNT 3 Kaushik 23 3000 3 Kaushik 23 1500 2 Khilan 25 1560 4 Chaitali 25 2060 Types of joins in SQL SQL provides various types of Joins that are categorized based on the way data across multiple tables are joined together. They are listed as follows − Inner Join An INNER JOIN is the default join which retrieves the intersection of two tables. It compares each row of the first table with each row of the second table. If the pairs of these rows satisfy the join-predicate, they are joined together. Outer Join An Outer Join retrieves all the records in two tables even if there is no counterpart row of one table in another table, unlike Inner Join. Outer join is further divided into three subtypes – Left Join, Right Join and Full Join. Following are the different types of outer Joins − LEFT JOIN − returns all rows from the left table, even if there are no matches in the right table. RIGHT JOIN − returns all rows from the right table, even if there are no matches in the left table. FULL JOIN − returns rows when there is a match in one of the tables. Other Joins In addition to these there are two more joins − SELF JOIN − is used to join a table to itself as if the table were two tables, temporarily renaming at least one table in the SQL statement. CROSS Join − returns the Cartesian product of the sets of records from the two or more joined tables. Print Page Previous Next Advertisements ”;

SQL – EXCEPT Table of content The SQL EXCEPT Operator EXCEPT with BETWEEN Operator Except with IN Operator EXCEPT with LIKE Operator ”; Previous Next The SQL EXCEPT Operator The EXCEPT operator in SQL is used to retrieve all the unique records from the left operand (query), except the records that are present in the result set of the right operand (query). In other words, this operator compares the distinct values of the left query with the result set of the right query. If a value from the left query is found in the result set of the right query, it is excluded from the final result. For better understanding consider two tables with records as shown in the following image − If we perform the EXCEPT operator on the above two tables to retrieve the names, it will display the distinct records only from the first table which are not in common with the records of the second table. Here, “Dev” is common in both tables. So, the EXECPT operator will eliminate it and retrieves only “Sara” and “Jay” as output. MySQL database does not support the EXCEPT operator. Instead of this, we can use the DISTINCT keyword along with the LEFT JOIN clause to retrieve distinct values from the left table. Syntax Following is the SQL syntax of the EXCEPT operator in Microsoft SQL server − SELECT column1, column2,…, columnN FROM table1, table2,…, tableN [Conditions] //optional EXCEPT SELECT column1, column2,…, columnN FROM table1, table2,…, tableN [Conditions] //optional The number and order of columns in both SELECT statements should be the same. Example First of all, let us create a table named STUDENTS using the following query − CREATE TABLE STUDENTS( ID INT NOT NULL, NAME VARCHAR(20) NOT NULL, SUBJECT VARCHAR(20) NOT NULL, AGE INT NOT NULL, HOBBY VARCHAR(20) NOT NULL, PRIMARY KEY(ID) ); Let”s insert some values into the table using the following query − INSERT INTO STUDENTS VALUES (1, ”Naina”, ”Maths”, 24, ”Cricket”), (2, ”Varun”, ”Physics”, 26, ”Football”), (3, ”Dev”, ”Maths”, 23, ”Cricket”), (4, ”Priya”, ”Physics”, 25, ”Cricket”), (5, ”Aditya”, ”Chemistry”, 21, ”Cricket”), (6, ”Kalyan”, ”Maths”, 30, ”Football”), (7, ”Aditya”, ”Chemistry”, 21, ”Cricket”), (8, ”Kalyan”, ”Chemistry”, 32, ”Cricket”); The table produced is as shown below − ID NAME SUBJECT AGE HOBBY 1 Naina Mathematics 24 Cricket 2 Varun Physics 26 Football 3 Dev Mathematics 23 Cricket 4 Priya Physics 25 Cricket 5 Aditya Chemistry 21 Cricket 6 Kalyan Mathematics 30 Football 7 Aditya Chemistry 21 Cricket 8 Kalyan Chemistry 32 Cricket Now, let us create another table named STUDENTS_HOBBY using the following query − CREATE TABLE STUDENTS_HOBBY( ID INT NOT NULL, NAME VARCHAR(20) NOT NULL, HOBBY VARCHAR(20) NOT NULL, AGE INT NOT NULL, PRIMARY KEY(ID) ); Once the table is created, let us insert some values to the table using the query below − INSERT INTO STUDENTS_HOBBY VALUES (1, ”Vijay”, ”Cricket”, 18), (2, ”Varun”, ”Football”, 26), (3, ”Surya”, ”Cricket”, 19), (4, ”Karthik”, ”Cricket”, 25), (5, ”Sunny”, ”Football”, 26), (6, ”Dev”, ”Cricket”, 23); The table created is as follows − ID NAME HOBBY AGE 1 Vijay Cricket 18 2 Varun Football 26 3 Surya Cricket 19 4 Karthik Cricket 25 5 Sunny Football 26 6 Dev Cricket 23 Now, let us perform the except operation on the above two tables − SELECT NAME, HOBBY, AGE FROM STUDENTS EXCEPT SELECT NAME, HOBBY, AGE FROM STUDENTS_HOBBY; Output Output of the above query is as shown below − NAME HOBBY AGE Aditya Cricket 21 Kalyan Cricket 32 Kalyan Football 30 Naina Cricket 24 Priya Cricket 25 EXCEPT with BETWEEN Operator We can use the EXCEPT operator with the BETWEEN operator in SQL to exclude records that fall within a specified range. Example In the following SQL query, we are retrieving the records of students aged between 20 and 30 from the STUDENTS table, excluding those who are also aged between 20 and 30 from the STUDENTS_HOBBY table − SELECT NAME, HOBBY, AGE FROM STUDENTS WHERE AGE BETWEEN 20 AND 30 EXCEPT SELECT NAME, HOBBY, AGE FROM STUDENTS_HOBBY WHERE AGE BETWEEN 20 AND 30 Output When we execute the program query, the output is obtained as follows − NAME HOBBY AGE Aditya Cricket 21 Kalyan Football 30 Naina Cricket 24 Priya Cricket 25 Except with IN Operator The IN operator is used to filter a result set based on a list of specified values. We can also use the EXCEPT operator with the IN operator in SQL to exclude records that matches values in the specified list. Example Here, we are retrieving the records of students with Cricket as a hobby, from the STUDENTS table, excluding those who also have Cricket as hobby from the STUDENTS_HOBBY table − SELECT NAME, HOBBY, AGE FROM STUDENTS WHERE HOBBY IN(”Cricket”) EXCEPT SELECT NAME, HOBBY, AGE FROM STUDENTS_HOBBY WHERE HOBBY IN(”Cricket”) Output Following is the output of the above query − NAME HOBBY AGE Aditya Cricket 21 Kalyan Cricket 32 Naina Cricket 24 Priya Cricket 25 EXCEPT with LIKE Operator The LIKE operator is used to perform pattern matching on a string. The EXCEPT operator can also be used with the LIKE operator in SQL to exclude rows that matches with the specified pattern. Example In here, we are retrieving records from the STUDENTS table where the values in the HOBBY column starts with ”F”, while excluding similar rows from the STUDENTS_HOBBY table − SELECT ID, NAME, HOBBY, AGE FROM STUDENTS WHERE HOBBY LIKE ”F%” EXCEPT SELECT ID, NAME, HOBBY, AGE FROM STUDENTS_HOBBY WHERE HOBBY LIKE ”F%”; Output The output for the above query is produced as given below − ID NAME HOBBY AGE 6 Kalyan Football 30 Print Page Previous Next Advertisements ”;