Category: sql

SQL – ANY, ALL Operators Table of content The SQL ANY Operator ANY with ”>” Operator ANY with ”<” Operator ANY with ”=” Operator The SQL ALL Operator ALL with WHERE Statement ALL with HAVING Clause ”; Previous Next The SQL ANY and ALL operators are used to perform a comparison between a single value and a range of values returned by the subquery. The ANY and ALL operators must be preceded by a standard comparison operator i.e. >, >=, <, <=, =, <>, != and followed by a subquery. The main difference between ANY and ALL is that ANY returns true if any of the subquery values meet the condition whereas ALL returns true if all of the subquery values meet the condition. The SQL ANY Operator The ANY operator is used to verify if any single record of a query satisfies the required condition. This operator returns a TRUE, if the given condition is satisfied for any of the values in the range. If none of the values in the specified range satisfy the given condition, this operator returns false. You can also use another query (subquery) along with this operator. Syntax The basic syntax of the SQL – ANY operator is as follows − Column_name operator ANY (subquery); Where, column_name is the name of a column in the main query. operator is a comparison operator such as =, <, >, <=, >=, or <>. subquery is a SELECT statement that returns a single column of values. ANY with ”>” Operator Typically, the ANY operator is used to compare a value with a set of values returned by a subquery, in such cases we can use it with the > (greater than) operator to verify if a particular column value is greater than column value of any of the records returned by the sub query. 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 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, let us list out the details of all the CUSTOMERS whose SALARY is greater than the SALARY of any customer whose AGE is 32 i.e. Chaitali, Hardik, Komal and Muffy in this case − SELECT * FROM CUSTOMERS WHERE SALARY > ANY (SELECT SALARY FROM CUSTOMERS WHERE AGE = 32); Output The result obtained is as follows − ID NAME AGE ADDRESS SALARY 4 Chaitali 25 Mumbai 6500.00 5 Hardik 27 Bhopal 8500.00 6 Komal 22 Hyderabad 4500.00 7 Muffy 24 Indore 10000.00 ANY with ”<” Operator Similar to the ”>” operator, we can use the ”<” (less than) operator along with ANY to verify if a particular column value is less than column value of any of the records returned by the sub query. Example In here, we are finding the distinct/different age of customers having any salary less than the average salary of all the customers from the CUSTOMERS table previously created − SELECT DISTINCT AGE FROM CUSTOMERS WHERE SALARY < ANY (SELECT AVG(SALARY) FROM CUSTOMERS); Output We get the following output while executing the above query − AGE 32 25 23 22 ANY with ”=” Operator When we use the = (equal to) operator along with ANY, it verifies if a particular column value is equal to the column value of any of the records returned by the sub query. Example In the query given below, we are retrieving the details of all the customers whose age is equal to the age of any customer whose name starts with ”K” − SELECT * FROM CUSTOMERS WHERE AGE = ANY (SELECT AGE FROM CUSTOMERS WHERE NAME LIKE ”K%”); Output The result produced is as follows − ID NAME AGE ADDRESS SALARY 2 Khilan 25 Delhi 1500.00 3 Kaushik 23 Kota 2000.00 4 Chaitali 25 Mumbai 6500.00 6 Komal 22 Hyderabad 4500.00 The

SQL – BETWEEN Operator Table of content The SQL BETWEEN Operator BETWEEN Operator with IN Operator BETWEEN Operator with UPDATE Statement BETWEEN Operator with DELETE Statement NOT BETWEEN Operator NOT BETWEEN Operator with IN ”; Previous Next The SQL BETWEEN Operator The BETWEEN operator is a logical operator in SQL, that is used to retrieve the data within a specified range. The retrieved values can be integers, characters, or dates. You can use the BETWEEN operator to replace a combination of “greater than equal AND less than equal” conditions. Let us understand it in a better way by using the below example table − Suppose we want to list out the names from the above table who are aged BETWEEN 20 and 30. So, we will get “Varma(21)”, “Nikhil(25)”, and “Bhargav(29)” as a result. Syntax Following is the syntax of the BETWEEN operator in SQL − SELECT column1, column2, column3,….columnN FROM table_name WHERE column BETWEEN value1 AND value2; Here, value1 is the beginning value of the range. value2 is the ending value of the range (inclusive). 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) ); Once the table is created, let us insert some values into the table using the following INSERT query − 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 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 using the BETWEEN operator to retrieve the details of the CUSTOMERS whose AGE (numeric data) is between 20 and 25 − SELECT * FROM CUSTOMERS WHERE AGE BETWEEN 20 AND 25; Output When we execute the above query, the output is obtained as follows − ID NAME AGE ADDRESS SALARY 2 Khilan 25 Delhi 1500.00 3 Kaushik 23 Kota 2000.00 4 Chaitali 25 Mumbai 6500.00 6 Komal 22 Hyderabad 4500.00 7 Muffy 24 Indore 10000.00 Example Here, we are using the BETWEEN operator with characters. Let us retrieve the details of the customers whose names starts in between the alphabets “A” and “L” using the following query − SELECT * FROM CUSTOMERS WHERE NAME BETWEEN ”A” AND ”L”; Output Following is the output of the above query − ID NAME AGE ADDRESS SALARY 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 BETWEEN Operator with IN Operator In SQL, we can combine the BETWEEN operator with the IN operator to select values that are within a specified range and also matches with values specified in the list of IN clause. Example In the following query, we are retrieving the details of all the customers whose salary is between 4000 and 10000. In addition, we are only retrieving the customers who lives in Hyderabad and Bhopal using the IN operator in SQL − SELECT * FROM CUSTOMERS WHERE SALARY BETWEEN 4000 AND 10000 AND ADDRESS IN (”Hyderabad”, ”Bhopal”); Output On executing the above query, the output is displayed as follows − ID NAME AGE ADDRESS SALARY 5 Hardik 27 Bhopal 8500.00 6 Komal 22 Hyderabad 4500.00 BETWEEN Operator with UPDATE Statement We can also use the BETWEEN operator with the UPDATE statement to update values within the specified range. The UPDATE statement is used to modify existing data in a database table. Example Let us update the salaries of the customers whose age lies between 25 to 30 using the following query − UPDATE CUSTOMERS SET SALARY = 10000 WHERE AGE BETWEEN 25 AND 30; Output The output for the above query is as given below − Query OK, 3 rows affected (0.02 sec) Rows matched: 3 Changed: 3 Warnings: 0 Verification Let us verify whether the salaries are updated or not using the following query − SELECT * FROM CUSTOMERS; The table for the above query produced as given below − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 2 Khilan 25 Delhi 10000.00 3 Kaushik 23 Kota 2000.00 4 Chaitali 25 Mumbai 10000.00 5 Hardik 27 Bhopal 10000.00 6 Komal 22 Hyderabad 4500.00 7 Muffy 24 Indore 10000.00 BETWEEN Operator with DELETE Statement We can also use the BETWEEN operator with the DELETE statement to delete rows within a specified range. Example Now, let us delete the customers whose age is between 20 and 24 using the DELETE statement − DELETE FROM CUSTOMERS WHERE AGE BETWEEN 20 AND 24; Output If we compile and run the above query, the result is produced as follows − Query OK, 3 rows affected (0.02 sec) Verification Let us verify whether the records with the specified age values are deleted or not, using the following query − SELECT * FROM CUSTOMERS; The table for the above query produced is as given below − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 2 Khilan 25 Delhi 1500.00 4 Chaitali 25 Mumbai 6500.00 5 Hardik 27 Bhopal 8500.00 NOT BETWEEN Operator The NOT BETWEEN operator in SQL works exactly opposite to BETWEEN operator. This is used to retrieve the data which is not present in the specified range. Let us understand in a better way by using the below example table − Suppose we want to list out the students from the above table who are aged not between 20 and 30. So, we will get “Prudhvi(45) and Ganesh(33)” as result. Syntax Following is the syntax

SQL – Right Join Table of content The SQL Right Join Joining Multiple Tables with Right Join Right Join with WHERE Clause ”; Previous Next SQL Joins are used to retrieve records from multiple tables based on a given condition. A Join includes the records that satisfy the given condition and outer join results a table that contains both matched and unmatched rows. Left Outer Join, as discussed in the previous tutorial, is used to find the union of two tables with respect to the left table. In this tutorial, let us discuss about the Right outer join. The SQL Right Join The Right Join or Right Outer Join query in SQL returns all rows from the right table, even if there are no matches in the left table. In short, a right join returns all the values from the right table, plus matched values from the left table or NULL in case of no matching join predicate. If the ON clause matches zero records in the left table; the join will still return a row in the result, but with a NULL value in each column of the left table. Syntax Following is the basic syntax of Right Join in SQL − SELECT table1.column1, table2.column2… FROM table1 RIGHT JOIN table2 ON table1.common_field = table2.common_field; Example The tables we are using in this example are named CUSTOMERS and ORDERS. Assume we are creating a table named CUSTOMERS, which contains the personal details of customers including their name, age, address and salary etc. 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 Let us create another table ORDERS, containing the details of orders made and the date they are made on. 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 table is displayed 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 Now, let us join these two tables using the Right Join query as follows − SELECT ID, NAME, AMOUNT, DATE FROM CUSTOMERS RIGHT JOIN ORDERS ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID; Output This would produce the following result − ID NAME AMOUNT DATE 3 Kaushik 3000.00 2009-10-08 00:00:00 3 Kaushik 1500.00 2009-10-08 00:00:00 2 Khilan 1560.00 2009-11-20 00:00:00 4 Chaitali 2060.00 2008-05-20 00:00:00 Joining Multiple Tables with Right Join Like Left Join, Right Join also joins multiple tables. However, the contrast occurs where the second table is returned as a whole instead of the first. In addition, the rows of first table are matched with the rows in second table. If the records are not matched and the number of records in the second table is greater than the first, NULL is returned as the values in first table. Syntax Following is the syntax to join multiple tables using Right Join − SELECT column1, column2, column3… FROM table1 RIGHT JOIN table2 ON condition_1 RIGHT JOIN table3 ON condition_2 …. …. RIGHT JOIN tableN ON condition_N; Example Here, let us consider the previously created tables CUSTOMERS and ORDERS; and create a new table named EMPLOYEE using the following query − CREATE TABLE EMPLOYEE ( EID INT NOT NULL, EMPLOYEE_NAME VARCHAR (30) NOT NULL, SALES_MADE DECIMAL (20) ); Now, we can insert values into this empty tables using the INSERT statement as follows − INSERT INTO EMPLOYEE VALUES (102, ”SARIKA”, 4500), (100, ”ALEKHYA”, 3623), (101, ”REVATHI”, 1291), (103, ”VIVEK”, 3426); The details of EMPLOYEE table can be seen below − EID EMPLOYEE_NAME SALES_MADE 102 SARIKA 4500 100 ALEKHYA 3623 101 REVATHI 1291 103 VIVEK 3426 Following query joins these three tables using the Right Join query − SELECT CUSTOMERS.ID, CUSTOMERS.NAME, ORDERS.DATE, EMPLOYEE.EMPLOYEE_NAME FROM CUSTOMERS RIGHT JOIN ORDERS ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID RIGHT JOIN EMPLOYEE ON ORDERS.OID = EMPLOYEE.EID; Through this query, we will display the id, name of the customer along with the date on which the orders are made and the name of the employee who sold the item. Output The resultant table is obtained as follows − ID NAME DATE EMPLOYEE_NAME 3 Kaushik 2009-10-08 00:00:00 SARIKA 3 Kaushik 2009-10-08 00:00:00 ALEKHYA 2 Khilan 2009-11-20 00:00:00 REVATHI 4 Chaitali 2008-05-20 00:00:00 VIVEK Right Join with WHERE Clause A WHERE Clause is used to filter out records that satisfy the condition specified by it. This clause can be used with the Right Join query to apply certain filters on the joined result-set. Syntax The syntax of Right Join when used with WHERE clause is given below − SELECT column_name(s) FROM table1 RIGHT JOIN table2 ON table1.column_name = table2.column_name WHERE condition; Example Records in the combined database tables can be filtered using the WHERE clause. Consider the previous two tables CUSTOMERS and ORDERS; and join them using the right join query by applying some constraints using the WHERE clause. SELECT ID, NAME, DATE, AMOUNT FROM CUSTOMERS RIGHT JOIN ORDERS ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID WHERE ORDERS.AMOUNT > 1000.00; Output The resultant table after applying the where clause with

SQL – Left Join Table of content What is Outer Join? The SQL Left Join Joining Multiple Tables with Left Join Left Join with WHERE Clause ”; Previous Next Joins are used to retrieve records from two or more tables based on a logical relation between them. This relation is defined using a join condition. As we discussed in the previous chapters, there are two types of Joins − Inner Join Outer Join Left Join is a type of outer join that retrieves all the records from the first table and matches them to the records in second table. First of all, let us understand what is outer join. What is Outer Join? Outer Join is used to join multiple database tables into a combined result-set, that includes all the records, even if they don”t satisfy the join condition. NULL values are displayed against these records where the join condition is not met. This scenario only occurs if the left table (or the first table) has more records than the right table (or the second table), or vice versa. There are three types of outer joins, namely − Left (Outer) Join: Retrieves all the records from the first table, Matching records from the second table and NULL values in the unmatched rows. Right (Outer) Join: Retrieves all the records from the second table, Matching records from the first table and NULL values in the unmatched rows. Full (Outer) Join: Retrieves records from both the tables and fills the unmatched values with NULL. Following diagram illustrates various outer joins between two tables namely, EmpDetails and MaritalStatus. Here, the join operation is presumed based on the join-predicate EmpDetails.EmpID = MaritalStatus.EmpID. The SQL Left Join Left Join or Left Outer Join in SQL combines two or more tables, where the first table is returned wholly; but, only the matching record(s) are retrieved from the consequent tables. If zero (0) records are matched in the consequent tables, the join will still return a row in the result, but with NULL in each column from the right table. If the number of rows in first table is less than the number of rows in second table, the rows in second table that do not have any counterparts in the first table will be discarded from the result. Syntax Following is the basic syntax of Left Join in SQL − SELECT column_name(s) FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name; Example To understand this query better, let us create some tables in an existing database and join them using Left Join or Left Outer Join. 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 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 Let us create another table ORDERS, containing the details of orders made and the date they are made on. 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 table is displayed 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 left join query, retrieves the details of customers who made an order at the specified date and who did not. If there is no match found, the query below will return NULL in that record. SELECT ID, NAME, AMOUNT, DATE FROM CUSTOMERS LEFT JOIN ORDERS ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID; Output The resultant table is obtained as − ID NAME AMOUNT DATE 1 Ramesh NULL NULL 2 Khilan 1560.00 2009-11-20 00:00:00 3 Kaushik 1500.00 2009-10-08 00:00:00 3 Kaushik 3000.00 2009-10-08 00:00:00 4 Chaitali 2060.00 2008-05-20 00:00:00 5 Hardik NULL NULL 6 Komal NULL NULL 7 Muffy NULL NULL As we can see in the table above, only Khilan, Kaushik and Chaitali made purchases on the mentioned dates in ORDERS table; hence, the records are matched. The other customers in CUSTOMERS table did not make purchases on the specified dates, so the records are returned as NULL. Joining Multiple Tables with Left Join Similar to the Inner Join query, Left Join also joins multiple tables where the first table is returned as it is and the remaining tables are matched with the rows in the first table. If the records are not matched, NULL is returned. The syntax to join multiple tables using Left Join is given below − SELECT column1, column2, column3… FROM table1 LEFT JOIN table2 ON condition_1 LEFT JOIN table3 ON condition_2 …. …. LEFT JOIN tableN ON condition_N; Example To demonstrate Left Join with multiple tables, let us consider the previously created tables CUSTOMERS and ORDERS. In addition to these we will create the EMPLOYEE table using the following query − CREATE TABLE EMPLOYEE ( EID INT NOT NULL, EMPLOYEE_NAME VARCHAR (30) NOT NULL, SALES_MADE DECIMAL (20) ); Now, we can insert values into this empty tables using the INSERT statement as follows − INSERT

SQL – Group By Clause Table of content The SQL GROUP BY Clause GROUP BY Clause with Aggregate Functions GROUP BY Clause on Single Columns GROUP BY Clause with Multiple Columns GROUP BY with ORDER BY Clause GROUP BY with HAVING Clause ”; Previous Next The SQL GROUP BY Clause The SQL GROUP BY clause is used in conjunction with the SELECT statement to arrange identical data into groups. This clause follows the WHERE clause in a SELECT statement and precedes the ORDER BY and HAVING clauses (if they exist). The main purpose of grouping the records of a table based on particular columns is to perform calculations on these groups. Therefore, The GROUP BY clause is typically used with aggregate functions such as SUM(), COUNT(), AVG(), MAX(), or MIN() etc. For example, if you have a table named SALES_DATA containing the sales data with the columns YEAR, PRODUCT, and SALES. To calculate the total sales in an year, the GROUP BY clause can be used to group the records in this table based on the year and calculate the sum of sales in each group using the SUM() function. Syntax Following is the basic syntax of the SQL GROUP BY clause − SELECT column_name(s) FROM table_name GROUP BY column_name(s); Where, column_name(s) refers to the name of one or more columns in the table that we want to group the data by and the table_name refers to the name of the table that we want to retrieve data from. GROUP BY Clause with Aggregate Functions Typically, we group the record of a table to perform calculations on them. Therefore, the SQL GROUP BY clause is often used with the aggregate functions such as SUM(), AVG(), MIN(), MAX(), COUNT(), etc. 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 The following SQL query groups the CUSTOMERS table based on AGE and counts the number of records in each group − SELECT AGE, COUNT(Name) FROM CUSTOMERS GROUP BY AGE; Output Following is the result produced − AGE COUNT(Name) 32 1 25 2 23 1 27 1 22 1 24 1 Example In the following query, we are finding the highest salary for each age − SELECT AGE, MAX(salary) AS MAX_SALARY FROM CUSTOMERS GROUP BY AGE; Output Following is the output of the above query − AGE MAX_SALARY 32 2000.00 25 6500.00 23 2000.00 27 8500.00 22 4500.00 24 10000.00 Similarly we can group the records of the CUSTOMERS table based on the AGE column and calculate the maximum salary, average and sum of the SALARY values in each group using the MIN(), AVG() and SUM() functions respectively. GROUP BY Clause on Single Columns When we use the GROUP BY clause with a single column, all the rows in the table that have the same value in that particular column will be merged into a single record. Example In the following example we are grouping the above created CUSTOMERS table by the ADDRESS column and calculating the average salary of the customer from each city − SELECT ADDRESS, AVG(SALARY) as AVG_SALARY FROM CUSTOMERS GROUP BY ADDRESS; Output This would produce the following result − ADDRESS AVG_SALARY Ahmedabad 2000.000000 Delhi 1500.000000 Kota 2000.000000 Mumbai 6500.000000 Bhopal 8500.000000 Hyderabad 4500.000000 Indore 10000.000000 GROUP BY Clause with Multiple Columns When we use the GROUP BY clause with multiple columns, all the rows in the table that have the same values in all of the specified columns will be merged into a single group. Example In the following query we are grouping the records of the CUSTOMERS table based on the columns ADDRESS and AGE and − SELECT ADDRESS, AGE, SUM(SALARY) AS TOTAL_SALARY FROM CUSTOMERS GROUP BY ADDRESS, AGE; Output This would produce the following result − ADDRESS AGE TOTAL_SALARY Ahmedabad 32 2000.00 Delhi 25 1500.00 Kota 23 2000.00 Mumbai 25 6500.00 Bhopal 27 8500.00 Hyderabad 22 4500.00 Indore 24 10000.00 GROUP BY with ORDER BY Clause We can use the ORDER BY clause with GROUP BY in SQL to sort the grouped data by one or more columns. Syntax Following is the syntax for using ORDER BY clause with GROUP BY clause in SQL − SELECT column1, column2, …, aggregate_function(columnX) AS alias FROM table GROUP BY column1, column2, … ORDER BY column1 [ASC | DESC], column2 [ASC | DESC], …; Example In here, we are finding the highest salary for each age, sorted by high to low − SELECT AGE, MIN(SALARY) AS MIN_SALARY FROM CUSTOMERS GROUP BY AGE ORDER BY MIN_SALARY DESC; Output Following is the result produced − AGE MIN_SALARY 24 10000.00 27 8500.00 22 4500.00 32 2000.00 23 2000.00 25 1500.00 GROUP BY with HAVING Clause We can also use the GROUP BY clause with the HAVING clause filter the grouped data in a table based on specific criteria. Syntax Following is the syntax for using ORDER BY clause with HAVING clause in SQL − SELECT column1, column2, aggregate_function(column) FROM table_name GROUP BY column1, column2 HAVING condition; Example In the following query, we are grouping the customers by their age and calculating the minimum salary for each group. Using the HAVING clause we

SQL – BOOLEAN Table of content Boolean in MySQL Boolean in MS SQL Server Filtering Boolean Data Negating Boolean Conditions Working with NULL Values Updating Boolean Values ”; Previous Next A Boolean is a universal data type which stores true or false values. It is used when we define a variable in a column of the table. For instance, a customer wants a list of all the red cars. So, we can find this using the BOOLEAN operator as given in the below table − Here, IS_RED is the BOOLEAN column that returns either TRUE or FALSE values based on the color of the cars. The databases like PostgreSQL and PL/SQL provides the Boolean data type which is abbreviated as BOOL. Whereas the databases like MySQL and oracle SQL does not have a Boolean data type. To represent Boolean values, they provide TINYINT and BIT data type respectively. Boolean in MySQL MySQL provides various options for handling Boolean data. You can use BOOL, BOOLEAN, or TINYINT to represent Boolean values. When you use BOOL or BOOLEAN, MySQL internally converts them into TINYINT. Similar to many programming languages like PHP, C, and C++, MySQL represents the TRUE literal as 1 and the FALSE literal as 0. Example Here, we are creating a table ”CARS” with column BOOLEAN. The query to create a table is as follows − CREATE TABLE CARS ( ID INT NOT NULL, Name VARCHAR(150), IsRed BOOLEAN ); In the above example, a table is created with a BOOLEAN column IsRed. You can insert TRUE as 1 or FALSE as 0 in this column to represent the corresponding Boolean values. Boolean in MS SQL Server In MS SQL Server, there is no direct BOOLEAN data type. Instead, you can use the BIT data type to represent Boolean values, where 0 represents FALSE and 1 represents TRUE. The BIT data type can also accept NULL values. Example Following is an example to create a table with a BOOLEAN column in SQL Server − CREATE TABLE CUSTOMERS ( ID INT NOT NULL, Name VARCHAR(150), IsAvailable BIT ); In the example above, a table named CUSTOMERS is created with a BOOLEAN column IsAvailable represented as a BIT data type. You can insert 0 for FALSE, 1 for TRUE, or NULL for an unknown value in this column. Now that you understand how Boolean data types are implemented in SQL Server and MySQL, let us explore how to handle Boolean data in SQL, including filtering and querying based on Boolean columns. Filtering Boolean Data You can filter data based on Boolean columns in SQL. For example, in MySQL, to find all the red cars, you can use the BOOLEAN column ”IsRed” to filter for TRUE values as shown below − SELECT * FROM CARS WHERE IsRed = TRUE; In SQL Server, to find cars that are red, you can filter for TRUE values (IsRed = 1) as follows − SELECT * FROM CARS WHERE IsRed = 1; Negating Boolean Conditions You can also negate Boolean conditions to find records that are NOT TRUE. For example, to find cars that are not red, use the following query in MySQL − SELECT * FROM CARS WHERE IsRed = 0; Following is the query in SQL Server − SELECT * FROM CARS WHERE IsRed = FALSE; Working with NULL Values You can handle NULL values of Boolean data in SQL as well. As mentioned earlier, the BIT data type in SQL Server and the BOOL/BOOLEAN data types in MySQL can accept NULL values, which can represent unknown or unspecified conditions. To filter records with NULL values in a Boolean column, you can use the IS NULL or IS NOT NULL condition in both MySQL and SQL Server − — Finding cars with unspecified availability SELECT * FROM CARS WHERE IsAvailable IS NULL; — Finding cars with specified availability SELECT * FROM CARS WHERE IsAvailable IS NOT NULL; In the queries above, we filter cars based on whether their IsAvailable column is NULL or not NULL. Updating Boolean Values You can also update Boolean values in your SQL tables. To change the value of a Boolean column in MySQL, you can use the UPDATE statement as shown below − — Changing IsRed to TRUE for car with ID 123 UPDATE CARS SET IsRed = TRUE WHERE ID = 123; In the above example, we updated the IsRed column for a specific car with the ID of 123, setting it to TRUE. To update Boolean values in SQL Server, use the following query − — Changing IsRed to TRUE for car with ID 123 UPDATE CARS SET IsRed = 1 WHERE ID = 123; Print Page Previous Next Advertisements ”;

SQL – AND and OR Conjunctive Operators Table of content The SQL AND Operator Multiple AND Operators AND with Other Logical Operators AND with UPDATE Statement The SQL OR Operator Multiple OR Operators OR with AND Operator OR with DELETE Statement ”; Previous Next Operators are reserved words primarily used in SQL to perform various operations on data, like addition (+), subtraction (-), or comparison (==). Conjunctive operators, specifically used in boolean logic, combines two conditions in an SQL statement. The most common conjunctive operators are: AND (&&), which returns true if both conditions are true, and OR (||), which returns true if at least one condition is true. The SQL AND Operator The SQL AND returns true or 1, if both its operands evaluates to true. We can use it to combine two conditions in the WHERE clause of an SQL statement. Syntax The basic syntax of the SQL AND operator with a WHERE clause is as follows − WHERE [condition1] AND [condition2]; Where, condition1, condition2 are the conditions we want to apply to the query. You can combine N number of conditions using the AND operator. For an action to be taken by the SQL statement, whether it be a transaction or a query, all the specified conditions (separated by the AND operator) must be TRUE. Example Assume we have created a table with name CUSTOMERS in MySQL database using the 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 table obtained 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 Following is an example which would fetch the ID, NAME and SALARY fields from the CUSTOMERS table, where the salary is greater than 2000 and the age is less than 25 years − SELECT ID, NAME, SALARY FROM CUSTOMERS WHERE SALARY > 2000 AND AGE < 25; Output This would produce the following result − ID NAME SALARY 6 Komal 4500.00 7 Muffy 10000.00 Multiple AND Operators You can also use multiple ”AND” operators in an SQL query to combine multiple conditions (or, expressions) together. Conditions combined with the ”AND” operators are evaluated from left to right. If any of the condition evaluate to false, the entire compound condition will be false and the record will not be included in the result set. Syntax Following is the syntax − WHERE [condition1] AND [condition2]…AND [conditionN]; Example In the following query, we are selecting all records from the CUSTOMERS table where the name of the customer starts with ”K”, the age of the customer is greater than or equal to 22, and their salary is less than 3742 − SELECT * FROM CUSTOMERS WHERE NAME LIKE ”k%” AND AGE >= 22 AND SALARY < 3742; Output Following is the result produced − ID NAME AGE ADDRESS SALARY 2 Khilan 25 Delhi 1500.00 3 Kaushik 23 Kota 2000.00 AND with Other Logical Operators The “AND” operator can be used in combination with other logical operators to filter records from a database table. When using multiple logical operators in SQL, the order of operations is important. Parentheses can be used to control the order of operations and ensure that the conditions are evaluated in the correct order. Additionally, using too many logical operators or complex expressions can negatively impact query performance, so it”s important to carefully consider the design of the WHERE clause when working with large datasets. Example In here, we are combining the AND operator with the NOT operator to create a NAND operation. The ”NAND” operation returns true if at least one of the input conditions is false, and false if both input conditions are true. In the following query, we are selecting all records from the CUSTOMERS table where the condition (salary is greater than 4500 and the age is less than 26) is false. The “NOT” operator negates the entire condition, and the “AND” operator combines two conditions − SELECT * FROM CUSTOMERS WHERE NOT (SALARY > 4500 AND AGE < 26); Output Following is the output of the above query − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 2 Khilan 25 Delhi 1500.00 3 Kaushik 23 Kota 2000.00 5 Hardik 27 Bhopal 8500.00 6 Komal 22 Hyderabad 4500.00 AND with UPDATE Statement We can use the AND operator in the WHERE clause of the UPDATE statement to modify the rows in a table that meet certain criteria. Syntax Following is the syntax of using the AND operator with the UPDATE statement − UPDATE table_name SET column1 = value1, column2 = value2, … WHERE condition1 AND condition2 AND …; Where, table_name is the name of the table we want to update, column1, column2, etc. are the columns we want to modify, and value1, value2, etc. are the new values we want to set for those columns. Example In the following query, we are updating the salary of all the customers whose age is greater than 27 and updating it to ”55000” using UPDATE statement − UPDATE CUSTOMERS SET SALARY = 55000 WHERE AGE > 27; Output We get the following result. We can observe that the salary of 1 customer has been modified − Query OK, 1 row affected (0.02 sec) Rows matched: 1 Changed: 1 Warnings: 0 Verification To verify if the changes are reflected in the tables, we can use SELECT statement to

SQL – WHERE Clause Table of content The SQL Where Clause WHERE Clause with SELECT Statement WHERE Clause with UPDATE Statement WHERE Clause with IN Operator WHERE Clause with NOT IN Operator WHERE Clause with LIKE Operator WHERE Clause with AND, OR Operators ”; Previous Next The SQL Where Clause The SQL WHERE clause is used to filter the results obtained by the DML statements such as SELECT, UPDATE and DELETE etc. We can retrieve the data from a single table or multiple tables(after join operation) using the WHERE clause. For instance, you can use the WHERE clause to retrieve details of employees of a department in an organization, or employees earning salary above/below certain amount, or details of students eligible for scholarships etc. This clause basically provides the specification of which records to be retrieved and which are to be to be neglected. Syntax The basic syntax of the SQL WHERE clause is as shown below − DML_Statement column1, column2,… columnN FROM table_name WHERE [condition]; Here, the DML_Statement can be any statement, such as SELECT, UPDATE, DELETE etc. You can specify a condition using the comparison or logical operators such as, >, <, =, LIKE, NOT, etc. WHERE Clause with SELECT Statement Typically, the SELECT statement is used to retrieve data from a table. If we use the WHERE clause with the SELECT statement, we can filter the rows to be retrieved based on a specific condition (or expression). Following is the syntax for it − SELECT column1, column2, … FROM table_name WHERE condition; 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) ); Following INSERT query inserts 7 records into this 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 In the following query, we are fetching the ID, NAME and SALARY fields from the CUSTOMERS table for the records where the SALARY is greater than 2000 − SELECT ID, NAME, SALARY FROM CUSTOMERS WHERE SALARY > 2000; Output This would produce the following result − ID NAME SALARY 4 Chaitali 6500.00 5 Hardik 8500.00 6 Komal 4500.00 7 Muffy 10000.00 WHERE Clause with UPDATE Statement The UPDATE statement is used to modify the existing records in a table. Using the SQL WHERE clause with the UPDATE statement, we can update particular records. If the WHERE clause is not used, the UPDATE statement would affect all the records of a table. Following is the syntax − UPDATE table_name SET column1 = value1, column2 = value2, … WHERE condition; Example In the following query, we are incrementing the salary of the customer named Ramesh by 10000 by using the WHERE clause along with the UPDATE statement − UPDATE CUSTOMERS set SALARY = SALARY+10000 where NAME = ”Ramesh”; Output We get the following result. We can observe that the age of 2 customers have been modified − Query OK, 2 rows affected (0.02 sec) Rows matched: 2 Changed: 2 Warnings: 0 Verification To verify if the changes are reflected in the table, we can use SELECT statement as shown in the following query − SELECT * FROM CUSTOMERS WHERE NAME = ”Ramesh”; The table is displayed as follows − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 12000.00 WHERE Clause with IN Operator Using the IN operator you can specify the list of values or sub query in the where clause. If you use WHERE and IN with the SELECT statement, it allows us to retrieve the rows in a table that match any of the values in the specified list. Following is the syntax for it − WHERE column_name IN (value1, value2, …); Where, the column_name is the column of a table and value1, value2, etc. are the list of values that we want to compare with the column_name. Example Suppose you want to display records with NAME values Khilan, Hardik and Muffy from the CUSTOMERS table, you can use the following query − SELECT * from CUSTOMERS WHERE NAME IN (”Khilan”, ”Hardik”, ”Muffy”); Output The result obtained is as follows − ID NAME AGE ADDRESS SALARY 2 Khilan 25 Delhi 1500.00 5 Hardik 27 Bhopal 8500.00 7 Muffy 24 Indore 10000.00 WHERE Clause with NOT IN Operator The WHERE clause with NOT IN operator is the negation of WHERE clause with the IN operator. If you use WHERE with the IN operator, the DML statement will act on the the list of values (of a column) specified Whereas, if you use WHERE with the NOT IN operator, the DML operation is performed on the values (of a column) that are not there in the specified list. Hence, if you use WHERE Clause with NOT IN Operator along with the SELECT statement, the rows that do not match the list of values are retrieved. Following is the syntax − WHERE column_name NOT IN (value1, value2, …); Example In this example, we are displaying the records from CUSTOMERS table, where AGE is NOT equal to 25, 23 and 22. SELECT * from CUSTOMERS WHERE AGE NOT IN (25, 23, 22); Output We obtain the result as given below − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 12000.00 5 Hardik 27 Bhopal 8500.00 7 Muffy 24 Indore 10000.00 WHERE Clause with LIKE Operator The WHERE clause with LIKE operator allows us to filter rows that matches

SQL – TOP Clause Table of content The SQL TOP Clause TOP with ORDER BY Clause TOP Clause with PERCENT TOP with WHERE Clause TOP Clause With DELETE Statement TOP and WITH TIES Clause Uses of TOP Clause ”; Previous Next The SQL TOP Clause While we are retrieving data from an SQL table, the SQL TOP clause is used to restrict the number of rows returned by a SELECT query in SQL server. In addition, we can also use it with UPDATE and DELETE statements to limit (restrict) the resultant records. For instance, if you have a large number of data stored in a database table, and you only want to perform operations on first N rows, you can use the TOP clause in your SQL server query. MySQL database does not support TOP clause instead of this, we can use the LIMIT clause to select a limited number of records from a MySQL table. Similarly, Oracle supports the ROWNUM clause to restrict the records of a table. The TOP clause is similar to the LIMIT clause. Syntax The basic syntax of the SQL TOP clause is as follows − SELECT TOP value column_name(s) FROM table_name WHERE [condition] Where, value is the number/ percentage of number of rows to return from the top. 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 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 using the TOP clause to fetch the top 4 records from the CUSTOMERS table without specifying any conditional clauses such as WHERE, ORDER BY, etc. − SELECT TOP 4 * FROM CUSTOMERS; Output This would produce the following result − 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 TOP with ORDER BY Clause The ORDER BY clause in SQL is used to sort the result-set of a query in ascending or descending order. We can use it along with the TOP clause to retrieve (or, update or, delete etc.) first N records in sorted order. Example Using the following query we are retrieving the top 4 records of the CUSTOMERS table in a sorted order. Here, we are sorting the table in descending order based on the SALARY column − SELECT TOP 4 * FROM CUSTOMERS ORDER BY SALARY DESC; Output We obtain the result as follows − ID NAME AGE ADDRESS SALARY 7 Muffy 24 Indore 10000.00 5 Hardik 27 Bhopal 8500.00 4 Chaitali 25 Mumbai 6500.00 6 Komal 22 Hyderabad 4500.00 Note − By default, the ORDER BY clause sorts the data in ascending order. So, if we need to sort the data in descending order, we must use the DESC keyword. TOP Clause with PERCENT We can also restrict the records by specifying percentage value instead of number, using the PERCENT clause along with the TOP clause. Example The following query selects the first 40% of the records from the CUSTOMERS table sorted in the ascending order by their SALARY − SELECT TOP 40 PERCENT * FROM CUSTOMERS ORDER BY SALARY Output We have the total of 7 records in our table. So 40% of 7 is 2.8. Therefore, SQL server rounds the result to three rows (the next whole number) as shown in the output below − ID NAME AGE ADDRESS SALARY 2 Khilan 25 Delhi 1500.00 3 Kaushik 23 Kota 2000.00 1 Ramesh 32 Ahmedabad 2000.00 TOP with WHERE Clause We can use the TOP clause with the WHERE clause to limit the given number of rows and filter them based on a specified condition. Example Following is the query to show the details of the first two customers whose name starts with K from the CUSTOMERS table − SELECT TOP 2 * FROM CUSTOMERS WHERE NAME LIKE ”k%” Output Following result is produced − ID NAME AGE ADDRESS SALARY 2 Khilan 25 Delhi 1500.00 3 Kaushik 23 Kota 2000.00 TOP Clause With DELETE Statement The TOP clause can be used with the DELETE statement to delete a specific number of rows that meet the given criteria. Example In the following query, we are using DELETE statement with TOP clause. Here, we are deleting the top 2 customers whose NAME starts with K − DELETE TOP(2) FROM CUSTOMERS WHERE NAME LIKE ”K%”; Output We get the output as shown below − (2 rows affected) Verification We can verify whether the changes are reflected in a table by retrieving its contents using the SELECT statement as shown below − SELECT * FROM CUSTOMERS; The table is displayed as follows − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 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 TOP and WITH TIES Clause While sorting the data in a table using the ORDER BY clause based on a column, some times multiple rows may contain same values in the column(s) specified in the ORDER BY clause. If you try to restrict the number of records using the TOP clause, all the

SQL – CASE Table of content The SQL CASE Statement CASE Statement with ORDER BY Clause CASE Statement with GROUP BY Clause CASE Statement with WHERE Clause CASE Statement with UPDATE CASE Statement with INSERT ”; Previous Next The SQL CASE Statement The SQL CASE statement is a conditional statement that helps us to make decisions based on a set of conditions. It evaluates the set of conditions and returns the respective values when a condition is satisfied. The CASE statement works like a simplified IF-THEN-ELSE statement and allows for multiple conditions to be tested. This starts with the keyword CASE followed by multiple conditionals statements. Each conditional statement consists of at least one pair of WHEN and THEN statements. Where WHEN specifies conditional statements and THEN specifies the actions to be taken. It is often used to create a new column with values based on the value of an existing column. Let us look at a simple scenario to understand this statement. For e.g. when the credit limit of a customer is above ”10,000”, then the customer will be recognized as a ”High value customer”; when the credit limit is above ”5000”, then the customer will be recognized as a ”Mid value customer”; otherwise the customer will be recognized as the ”Low value customer” as shown in the table below − Syntax Following is the syntax of SQL CASE statement − CASE WHEN condition1 THEN statement1, WHEN condition2 THEN statement2, WHEN condition THEN statementN ELSE result END; Where, condition1, condition2, etc. Are the conditional statements and statement1, statement2, etc.. are the actions to be taken when the condition is true. Once the condition is met, the CASE statement will stop verifying further and it will return the result. If none of the conditions are met (TRUE), then it returns the value mentioned in the ELSE clause. It returns NULL if the ELSE part is not mentioned and none of the conditions are TRUE. Example Assume we have created a table named CUSTOMERS which contains the personal details of customers including their name, age, address and salary etc. 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 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 In the following query, we are using multiple WHEN and THEN conditions to the CASE statement along with the ELSE clause. If the AGE of the customer is greater than 30, it returns Gen X otherwise moves to the further WHEN and THEN conditions. If none of the conditions is matched with the CUSTOMERS table, CASE returns the ”Gen Alpha” value as mentioned in the ELSE part of the query − SELECT NAME, AGE, CASE WHEN AGE > 30 THEN ”Gen X” WHEN AGE > 25 THEN ”Gen Y” WHEN AGE > 22 THEN ”Gen Z” ELSE ”Gen Alpha” END AS Generation FROM CUSTOMERS; Output The output produced is as follows − NAME AGE Generation Ramesh 32 Gen X Khilan 25 Gen Z Kaushik 23 Gen Z Chaitali 25 Gen Z Hardik 27 Gen Y Komal 22 Gen Alpha Muffy 24 Gen Z Example Let us take a look at another query where we want to provide a 25% increment to each customer if the amount is less than 4500 from the CUSTOMERS table previously created − SELECT *, CASE WHEN SALARY < 4500 THEN (SALARY + SALARY * 25/100) END AS INCREMENT FROM CUSTOMERS; Output Here, the SQL command checks if the salary is less than 4500. If this condition is satisfied, a new column ”INCREMENT” will contain the values that is equal to salary with 25% of increment. Since the ELSE part is not mentioned in the above query and none of the conditions are true for few CUSTOMERS, NULL is returned, which shows that they didn”t get any increment. ID NAME AGE ADDRESS SALARY INCREMENT 1 Ramesh 32 Ahmedabad 2000.00 2500.000000 2 Khilan 25 Delhi 1500.00 1875.000000 3 Kaushik 23 Kota 2000.00 2500.000000 4 Chaitali 25 Mumbai 6500.00 NULL 5 Hardik 27 Bhopal 8500.00 NULL