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 – Rename View Table of content Renaming a View in MySQL Renaming a View in SQL Server Rules to be followed while Renaming Views ”; Previous Next There are various SQL statements that perform different operations on database objects, such as creating, updating, deleting and also renaming a database object. And since a view is also a database object, all these operations can also be performed on a view, you can create a view, update a view, delete a view and also rename a view. There is no direct query to rename a view in SQL. In MySQL we can rename a view using the RENAME TABLE statement and in MS SQL Server we can rename a view using the sp_rename procedure. In many cases, deleting the existing view and then re-creating it with a new name is rather recommended. Renaming a View in MySQL The RENAME TABLE statement in MySQL database is used to rename views. You just have to make sure that the new name of the view does not overlap with the name of any existing views. Syntax Following is the basic syntax to rename a view in MySQL − RENAME TABLE old_view_name To new_view_name; Here, we must ensure that old view name is present in the database and that new view name does not already exist. Otherwise, it will issue a warning. Also before executing this statement, it is important to make sure that the table is not locked and there are no active transactions. Example In this example, let us first create a table with the name CUSTOMERS 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 Following query creates a view based on the above created table − CREATE VIEW CUSTOMERS_VIEW AS SELECT * FROM CUSTOMERS WHERE AGE > 25; You can verify the contents of a view using the select query as shown below − SELECT * from CUSTOMERS_VIEW; The view is displayed as follows − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 5 Hardik 27 Bhopal 8500.00 Now we know that a view with the name CUSTOMERS_VIEW exists in our database. So, we are directly going to rename this view to VIEW_CUSTOMERS, using the following query − RENAME TABLE CUSTOMERS_VIEW TO VIEW_CUSTOMERS; Output The result obtained is as shown below − Query OK, 0 rows affected (0.08 sec) Verification We can verify whether the view is renamed or not by retrieving its contents using its new name in the SELECT statement. Following is the query to display the records in the VIEW_CUSTOMERS view − SELECT * from VIEW_CUSTOMERS; The view displayed is as follows − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 5 Hardik 27 Bhopal 8500.00 Renaming a View in SQL Server There isn”t a query in SQL Server that can rename a view directly. But, it does give you access to a stored procedure called sp_rename that can rename a view. You have to make sure there are no active transactions being performed on the view using its old name before renaming it. The sp_rename is a system stored procedure (set of pre-built subroutines that perform tasks within the database) in SQL that can be used to rename various database objects including tables, columns, indexes, and constraints. Syntax Following is the basic syntax to rename a view in SQL − EXEC sp_rename ”old_view_name”, ”new_view_name” Here, we must ensure that old view name is present in the database and that new view name does not already exist. Otherwise, it will issue a warning. Before executing this statement, it is important to make sure that the table is not locked and there are no active transactions. Example In this example, let us first try to create a table with the name CUSTOMERS 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 ); Following query creates a view based on the above created table − CREATE VIEW CUSTOMERS_VIEW AS SELECT * FROM CUSTOMERS WHERE SALARY >2000; You can verify the contents of a view using the select query as shown below − SELECT * from CUSTOMERS_VIEW; The view will be created as − ID NAME AGE ADDRESS SALARY 4 Chaitali 25 Mumbai 6500.00 Now, we know that we have an existing view CUSTOMERS_VIEW in our database. So, we are going to rename this view to VIEW_CUSTOMERS, using the following query − EXEC sp_rename CUSTOMERS_VIEW, VIEW_CUSTOMERS; Verification We can verify whether the view is renamed or not by retrieving its contents using its new name in the SELECT statement. Following is the query to display the records in the VIEW_CUSTOMERS view − SELECT * FROM VIEW_CUSTOMERS; The view displayed is

SQL – EXISTS Operator Table of content The SQL EXISTS Operator EXISTS Operator with SELECT Statement EXISTS Operator with UPDATE Statement EXISTS Operator with DELETE Statement NOT Operator with EXISTS Operator Usage of SQL EXISTS Operator ”; Previous Next The SQL EXISTS Operator The SQL EXISTS operator is used to verify whether a particular record exists in a MySQL table. While using this operator we need to specify the record (for which you have to check the existence) using a subquery. The EXISTS operator is used in the WHERE clause of a SELECT statement to filter records based on the existence of related records in another table. It is a logical operator. It returns a Boolean value TRUE or FALSE. It returns TRUE if the subquery returns at least one record. If the EXISTS operator returns TRUE, the outer query will get executed; otherwise not. It can be used in SELECT, UPDATE, DELETE or INSERT statements. The EXISTS operator is more efficient than other operators, such as IN, because it only needs to determine whether any rows are returned by the subquery, rather than actually returning the data. The use of the EXISTS operator is an efficient way to filter data in many real-life scenarios, including filtering records based on the existence of related data, aggregating data based on the existence of related records, and optimizing queries. Syntax The basic syntax of the SQL EXISTS operator is as follows − WHERE EXISTS (subquery); Where, the subquery used is the SELECT statement. The EXISTS operator will evaluate to TRUE if the subquery returns at least one record in its result set; otherwise FALSE. EXISTS Operator with SELECT Statement The SELECT statement in SQL is used to retrieve data from one or more tables in a database. We can use the EXISTS operator with a SELECT statement to check for the existence of rows that meet a certain condition. 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 Let us create another table CARS, containing the details such as id of the customer, name and price of the car, using the following query − create table CARS( ID INT NOT NULL, NAME VARCHAR(20) NOT NULL, PRICE INT NOT NULL, PRIMARY KEY(ID) ); Using the INSERT statement, let us insert values into this table − insert INTO CARS VALUES (2, ”Maruti Swift”, 450000), (4, ”VOLVO”, 2250000), (7, ”Toyota”, 2400000); The ”CARS” table obtained is as follows − ID NAME PRICE 2 Maruti Swift 450000 4 VOLVO 2250000 7 Toyota 2400000 Now, we are retrieving the lists of the customers with the price of the car greater than 2,000,000 − SELECT * FROM CUSTOMERS WHERE EXISTS ( SELECT PRICE FROM CARS WHERE CARS.ID = CUSTOMERS.ID AND PRICE > 2000000 ); Output The result produced is as follows − ID NAME AGE ADDRESS SALARY 4 Chaitali 25 Mumbai 6500.00 7 Muffy 24 Indore 10000.00 EXISTS Operator with UPDATE Statement We can also use the SQL EXISTS operator with an UPDATE statement. It helps us to update rows in a table based on the existence of matching rows in another table. Example Suppose if we want to change the name of certain customers from the CUSTOMERS and CARS tables previously created, then this can be done using UPDATE statement. Here, we are modifying the name ”Kushal” of all the customers whose id is equal to the id of the CARS table, using the EXISTS operator, as follows − UPDATE CUSTOMERS SET NAME = ”Kushal” WHERE EXISTS ( SELECT NAME FROM CARS WHERE CUSTOMERS.ID = CARS.ID ); Output We get the following result. We can observe that 3 rows have been modified − Query OK, 3 rows affected (0.01 sec) Rows matched: 3 Changed: 3 Warnings: 0 Verification We can verify whether the changes are reflected in a table by retrieving its contents using the SELECT statement. Following is the query to display the records in the CUSTOMERS table − SELECT * FROM CUSTOMERS; The table is displayed as follows − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 2 Kushal 25 Delhi 1500.00 3 Kaushik 23 Kota 2000.00 4 Kushal 25 Mumbai 6500.00 5 Hardik 27 Bhopal 8500.00 6 Komal 22 Hyderabad 4500.00 7 Kushal 24 Indore 10000.00 As we can see in the above table, the NAME of ”Khilan”, ”Chaitali” and ”Muffy” has been updated to ”Kushal”. EXISTS Operator with DELETE Statement The EXISTS operator can also be used with a DELETE statement to delete rows based on the existence of rows returned by a subquery. Example In here, we are deleting the row in the CUSTOMERS table whose id is equal to the id of the CARS table having price equal to ”2250000” − DELETE FROM CUSTOMERS WHERE EXISTS ( SELECT * FROM CARS WHERE CARS.ID = CUSTOMERS.ID AND CARS.PRICE = 2250000 ); Output We get the following result. We can observe that 1 row has been deleted − Query OK, 1 row affected (0.01 sec) Verification We can rectify the changes done in the CUSTOMERS table using the following query −

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