1. SQL WHERE Clause
Where clause is used to fetch a particular row or set of rows from a table. This clause filters records based on given conditions and only those row(s) comes out as result that satisfies the condition defined in WHERE clause of the SQL query.
Some Important Points:
-
To limit the number of rows use the WHERE clause.
-
The WHERE clause filters for rows that meet certain criteria.
-
WHERE is followed by a condition that returns either true or false.
-
WHERE is used with SELECT, UPDATE, and DELETE.
SQL Where Clause Syntax
SELECT Column_name1, Column_name2, ....
FROM Table_name
WHERE Condition;
Here we have used the where clause with the SQL SELECT statement, however we can use this clause with other SQL statements as well such as UPDATE, DELETE etc.
The SQL WHERE syntax
A WHERE clause with a SELECT statement:
SELECT column-names
FROM table-name
WHERE condition
A WHERE clause with an UPDATE statement:
UPDATE table-name
SET column-name = value
WHERE condition
A WHERE clause with a DELETE statement:
DELETE table-name
WHERE condition
Example – SQL WHERE Clause
We have a table EMPLOYEES:
------ ---------- --------- ----------
|SSN | EMP_NAME | EMP_AGE |EMP_SALARY|
------ ---------- --------- ----------
| 101 | Steve | 23 | 9000.00 |
| 223 | Peter | 24 | 2550.00 |
| 388 | Shubham | 19 | 2444.00 |
| 499 | Chaitanya| 29 | 6588.00 |
| 589 | Apoorv | 21 | 1400.00 |
| 689 | Rajat | 24 | 8900.00 |
| 700 | Ajeet | 20 | 18300.00 |
------ ---------- --------- ----------
Lets say we want to fetch the name of the those employees who are more than 23 years old. The SQL statement would look like this – Query:
SELECT EMP_NAME
FROM EMPLOYEES
WHERE EMP_AGE > 23;
Result:
----------
| EMP_NAME |
----------
| Peter |
| Chaitanya|
| Rajat |
----------
Q. Fetch all the details of employees having salary greater than 6000.
Query:
SELECT *
FROM EMPLOYEES
WHERE EMP_SALARY > 6000;
Result:
------ ---------- --------- ----------
|SSN | EMP_NAME | EMP_AGE |EMP_SALARY|
------ ---------- --------- ----------
| 101 | Steve | 23 | 9000.00 |
| 499 | Chaitanya| 29 | 6588.00 |
| 689 | Rajat | 24 | 8900.00 |
| 700 | Ajeet | 20 | 18300.00 |
------ ---------- --------- ----------
Text Field in Where clause condition
In the above examples, we have seen that when the condition required numeric values in conditions such as employee salary & employee age, we didn’t require the quotes. However when the condition involves a column that has text value then in such case we have to enclose the value in single quotes. Lets take an example to understand this.
Lets say I want to find out the SSN of employee “Ajeet”. I can do it like this:
Query:
SELECT SSN, EMP_NAME
FROM EMPLOYEES
WHERE EMP_NAME = 'Ajeet';
Note: Do note the single quotes around String “Ajeet”.
Result:
------ ----------
|SSN | EMP_NAME |
------ ----------
| 700 | Ajeet |
------ ----------
Operators allowed in The WHERE Clause conditions
In the above examples we have seen that where clause allowed operators as > & =. Lets see the complete list of operators that we can use in the where clause.
-
Operators List:
-
> Greater than operator
-
< Less than operator
-
= Equal operator
-
>= Greater than or equal
-
<= Less than or equal
-
<> Not equal.
-
IN To specify the set of values
-
BETWEEN To specify the range of values
-
LIKE To specify the pattern
SQL where clause with multiple conditions
Lets take the same table:
------ ---------- --------- ----------
|SSN | EMP_NAME | EMP_AGE |EMP_SALARY|
------ ---------- --------- ----------
| 101 | Steve | 23 | 9000.00 |
| 223 | Peter | 24 | 2550.00 |
| 388 | Shubham | 19 | 2444.00 |
| 499 | Chaitanya| 29 | 6588.00 |
| 589 | Apoorv | 21 | 1400.00 |
| 689 | Rajat | 24 | 8900.00 |
| 700 | Ajeet | 20 | 18300.00 |
------ ---------- --------- ----------
Lets fetch the employee details where employee age is greater than 23 and salary is greater than 5000. For such query we have to use multiple conditions in where clause.
Query:
SELECT *
FROM EMPLOYEES
WHERE EMP_SALARY > 5000 AND EMP_AGE > 23;
Result:
------ ---------- --------- ----------
|SSN | EMP_NAME | EMP_AGE |EMP_SALARY|
------ ---------- --------- ----------
| 499 | Chaitanya| 29 | 6588.00 |
| 689 | Rajat | 24 | 8900.00 |
------ ---------- --------- ----------
Another multiple conditions example: Fetch the employee names, where either employee age is less than 20 or salary is less than 5000.
Query:
SELECT EMP_NAME
FROM EMPLOYEES
WHERE EMP_SALARY < 5000 OR EMP_AGE < 20;
Result:
----------
| EMP_NAME |
----------
| Shubham |
| Peter |
| Apoorv |
----------
Problem: Update the city to Sydney for supplier CSDT.
UPDATE Supplier
SET City = 'Sydney'
WHERE Name = 'CSDT'
Problem: Delete all products with unit price higher than $50.
DELETE FROM Product
WHERE UnitPrice > 50
2. SQL ORDER BY Clause
This keyword is used to sort the result-set in ascending or descending order. The ORDER BY keyword shall sort the records in ascending order by default. If we want to sort the records in descending order, use the DESC keyword.
We know that SQL SELECT Statement returns the rows in no particular order. With the help of ORDER BY clause, we can order the rows in a desired order. ORDER BY clause is used to sort the returned records in an order. By using ORDER BY clause, we can sort the result in ascending or descending order. This clause can be used with multiple columns as well.
Key points related to ORDER By Clause/Keyword:
-
SELECT returns records in no particular order.
-
To ensure a specific order use the ORDER BY clause.
-
ORDER BY allows sorting by one or more columns.
-
Records can be returned in ascending or descending order.
ORDER BY Syntax
SELECT column_name1, column_name2, column_name3....
FROM table_name
WHERE Condition
ORDER BY column_name1, column_name2, .... (ASC OR DESC);
Here ASC is used for sorting in ascending order and DESC for descending order.
SQL ORDER BY clause example: Lets say we have a table “STUDENT” with the following records.
--------- ---------- ----- ----------- ----------
| ROLL_NO | NAME | AGE | BRANCH | CITY |
--------- ---------- ----- ----------- ----------
| 10001 | Kate | 22 | CSE | Mumbai |
| 10002 | Richard | 21 | ECE | Delhi |
| 10003 | Rick | 33 | ME | Chennai |
| 10004 | Peter | 26 | CSE | Delhi |
| 10005 | Steve | 22 | CSE | Noida |
| 10006 | Mark | 22 | ECE | Jaipur |
| 10007 | Brad | 23 | ME | Rampur |
--------- ---------- ----- ----------- ----------
The following SQL statement will fetch all the records from the table “STUDENT” and the returned rows will be in ascending order of student age.
SELECT *
FROM STUDENT
ORDER BY AGE;
Note: The default order of sorting is ascending so the rows will be sorted based on the column “AGE”, even though we have not used the ASC keyword in order by clause. So it is safe to say that the following query is same as the above query and would fetch the same result.
SELECT *
FROM STUDENT
ORDER BY AGE ASC;
Result:
--------- ---------- ----- ----------- ----------
| ROLL_NO | NAME | AGE | BRANCH | CITY |
--------- ---------- ----- ----------- ----------
| 10002 | Richard | 21 | ECE | Delhi |
| 10001 | Kate | 22 | CSE | Mumbai |
| 10005 | Steve | 22 | CSE | Noida |
| 10006 | Mark | 22 | ECE | Jaipur |
| 10007 | Brad | 23 | ME | Rampur |
| 10004 | Peter | 26 | CSE | Delhi |
| 10003 | Rick | 33 | ME | Chennai |
--------- ---------- ----- ----------- ----------
SQL ORDER BY DESC example
Table: STUDENT
--------- ---------- ----- ----------- ----------
| ROLL_NO | NAME | AGE | BRANCH | CITY |
--------- ---------- ----- ----------- ----------
| 10001 | Kate | 22 | CSE | Mumbai |
| 10002 | Richard | 21 | ECE | Delhi |
| 10003 | Rick | 33 | ME | Chennai |
| 10004 | Peter | 26 | CSE | Delhi |
| 10005 | Steve | 22 | CSE | Noida |
| 10006 | Mark | 22 | ECE | Jaipur |
| 10007 | Brad | 23 | ME | Rampur |
--------- ---------- ----- ----------- ----------
The following SQL statement will fetch the student names from the table “STUDENT” and the returned names will be sorted in descending order (we have used DESC for descending order in the ORDER BY clause).
SELECT NAME
FROM STUDENT
ORDER BY NAME DESC;
Result:
----------
| NAME |
----------
| Steve |
| Rick |
| Richard |
| Peter |
| Mark |
| Kate |
| Brad |
----------
As you can see, we have got a list of student names sorted in the descending order.
SQL ORDER BY Multiple Columns
In the above examples, we have done the sorting based on a single column. However we can use multiple columns in ORDER BY clause.
When multiple columns are used in ORDER BY, first the rows will be sorted based on the first column and then by the second column. Lets take an example to understand this.
Table: EMPLOYEE
EMP_ID EMP_NAME COUNTRY CITY PINCODE
------ -------- ------- ---- -------
1001 Paul India Noida 201310
1005 Raul India Agra 283126
1002 Ajeet India Noida 201301
1003 Jon India Agra 282008
1006 David India Agra 282005
1004 Chetan India Noida 201313
Sorting the rows based on the city first and then by pin code within each city:
SELECT *
FROM EMPLOYEE
ORDER BY CITY, PINCODE;
Result:
As you can see that the rows are sorted by CITY and then within city sorted by pincode.
EMP_ID EMP_NAME COUNTRY CITY PINCODE
------ -------- ------- ---- -------
1006 David India Agra 282005
1003 Jon India Agra 282008
1005 Raul India Agra 283126
1002 Ajeet India Noida 201301
1001 Paul India Noida 201310
1004 Chetan India Noida 201313
Sorting the rows based on the city first in ascending order and then by pin code in descending order within each city:
SELECT *
FROM EMPLOYEE
ORDER BY CITY ASC, PINCODE DESC;
Result:
As you can see that the rows are sorted by CITY in ascending order and then within city sorted in reverse order by pincode.
EMP_ID EMP_NAME COUNTRY CITY PINCODE
------ -------- ------- ---- -------
1005 Raul India Agra 283126
1003 Jon India Agra 282008
1006 David India Agra 282005
1004 Chetan India Noida 201313
1001 Paul India Noida 201310
1002 Ajeet India Noida 201301
3. SQL SELECT DISTINCT Statement
SELECT DISTINCT Statement is used to fetch unique records from a table. It only returns distinct values in the result.
Lets say we have a table “Employee” having a field “EmployeeDepartment”. Since each department can have several employees thus this field of table would be having duplicate values. Suppose we would like to fetch the department names alone from this table. In that case it would be wise to use DISTINCT Keyword since we do not want to have bunch of duplicate values.
There are several cases where DISTINCT keyword could be very useful. Lets see the syntax and example of SQL SELECT DISTINCT statement to understand it better.
SELECT DISTINCT Syntax
SELECT DISTINCT column_name1, column_name2,...
FROM TableName;
SELECT DISTINCT Example
We have a table STUDENT with the following records:
--------- ---------- ----- ----------- ----------
| ROLL_NO | NAME | AGE | BRANCH | CITY |
--------- ---------- ----- ----------- ----------
| 10001 | Kate | 22 | CSE | Mumbai |
| 10002 | Richard | 21 | ECE | Delhi |
| 10003 | Rick | 33 | ME | Chennai |
| 10004 | Peter | 26 | CSE | Delhi |
| 10005 | Steve | 22 | CSE | Noida |
| 10006 | Mark | 22 | ECE | Jaipur |
| 10007 | Brad | 23 | ME | Rampur |
--------- ---------- ----- ----------- ----------
Fetch the unique “Branches” from the STUDENT table –
SELECT DISTINCT BRANCH FROM STUDENT;
Result:
----------
| BRANCH |
----------
| CSE |
| ECE |
| ME |
----------
Lets say you want to sort the result. You can do so by using Order by clause along with Distinct –
SELECT DISTINCT BRANCH FROM STUDENT
ORDER BY BRANCH DESC;
Result:
----------
| BRANCH |
----------
| ME |
| ECE |
| CSE |
----------
SELECT DISTINCT multiple columns
In the above example, we have retrieved the data of only one column from a table. Lets see what happens when we use the SELECT DISTINCT statement with multiple columns – Consider this ORDER table:
CUSTOMER_NAME BILL_AMOUNT ORD_NUM
---------- ----------- -------
Rick 2000 1901
Rick 2000 1902
Rick 3000 1903
Rick 3000 1904
Rick 4500 1905
Steve 2000 1906
SQL Statement:
SELECT DISTINCT CUSTOMER_NAME,BILL_AMOUNT
FROM ORDER;
Output:
When we select multiple columns using SELECT DISTINCT statement, then the data of those columns combined is treated as a distinct value. As you can see in this example that Rick 2000 rows and Rick 3000 rows were present multiple times in the ORDER table so when we DISTINCT select these rows based on these columns, we got these rows only once in the output.
CUSTOMER_NAME BILL_AMOUNT
---------- -----------
Rick 2000
Rick 3000
Rick 4500
Steve 2000
4. LIKE Clause in SQL
Like clause is used for fetching similar values from table(s). For e.g. you may want to fetch all the names from a table that starts with alphabet “A” and ends with alphabet “X”, in such case you can use like clause in SQL query. In this tutorial we will see variations and use of Like clause in SQL queries.
Syntax:
SQL> SELECT column_name1, column_name2....
FROM TableName
WHERE [condition]
LIKE Expression;
Expression can contain percentage sign(%) or underscore(_) or both. Here percentage sign(%) represents a string of character having zero or more characters and underscore(_) refers a single character. It may sound confusing, lets take few examples to understand them better:
Lets say I have a table “STUDENT” with a field(column) called STUDENT_NAME.
1) Find all the student names that start with character “A”:
SQL> SELECT STUDENT_NAME FROM STUDENT
WHERE STUDENT_NAME LIKE 'A%';
2) Find all the student names that start with character “X” and end with char “Z”:
SQL> SELECT STUDENT_NAME FROM STUDENT
WHERE STUDENT_NAME LIKE 'A%Z';
3) Find student names having 2nd and 4th character as “C” and “A” respectively:
SQL> SELECT STUDENT_NAME FROM STUDENT
WHERE STUDENT_NAME LIKE '_C_A%';
4) Find student names that end with char “B” and have only 4 characters in name:
SQL> SELECT STUDENT_NAME FROM STUDENT
WHERE STUDENT_NAME LIKE '_ _ _B';
5) Find student names that have a “E” at the second position and end with “Y”:
SQL> SELECT STUDENT_NAME FROM STUDENT
WHERE STUDENT_NAME LIKE '_E%Y';
Complete Example
STUDENT table:
--------- ---------- ----- ----------- ----------
| ROLL_NO | NAME | AGE | BRANCH | CITY |
--------- ---------- ----- ----------- ----------
| 10001 | Kate | 22 | CSE | Mumbai |
| 10002 | Richard | 21 | ECE | Delhi |
| 10003 | Rick | 33 | ME | Chennai |
| 10004 | Peter | 26 | CSE | Delhi |
| 10005 | Steve | 22 | CSE | Noida |
| 10006 | Mark | 22 | ECE | Jaipur |
| 10007 | Brad | 23 | ME | Rampur |
--------- ---------- ----- ----------- ----------
Q. Find all the records from the above table that have CITY names starting with char “D”.
SQL> SELECT NAME FROM STUDENT
WHERE CITY LIKE 'D%';
Result:
--------- ---------- ----- ----------- ----------
| ROLL_NO | NAME | AGE | BRANCH | CITY |
--------- ---------- ----- ----------- ----------
| 10002 | Richard | 21 | ECE | Delhi |
| 10004 | Peter | 26 | CSE | Delhi |
--------- ---------- ----- ----------- ----------
5. Group By clause in SQL
Group by clause is used for grouping the similar data after fetching it from tables(s) or This functionality is used to arrange a similar type of data into a group. For instance, if the column in a table consists of similar data or value in different rows then we can use GROUP BY function to group the data.
Syntax
SELECT column_name1, column_name2,...
FROM TableName
WHERE clause
GROUP BY column_namei, column_namej...;
Example
Lets say this is my “EMPLOYEE_DETAILS” table. As you can see it has duplicate rows for several employees.
--------- ---------- ----- ----------- ----------
| S_NO | EMP_NAME | AGE | DEPT | INCOME |
--------- ---------- ----- ----------- ----------
| 10001 | Kate | 22 | CSE | 12000 |
| 10002 | Kate | 22 | ECE | 13000 |
| 10003 | Rick | 33 | ME | 24000 |
| 10004 | Rick | 33 | ME | 78000 |
| 10005 | Steve | 22 | CSE | 12000 |
| 10006 | Mark | 23 | ME | 90000 |
| 10007 | Mark | 23 | ME | 45000 |
| 10008 | Mark | 23 | ME | 15000 |
--------- ---------- ----- ----------- ----------
Suppose if we want to know the total income of each individual employee then I can write the query like this:
SQL> SELECT EMP_NAME, SUM(INCOME) FROM EMPLOYEE_DETAILS
GROUP BY EMP_NAME
ORDER BY EMP_NAME;
Output:
---------- ----------
| EMP_NAME | INCOME |
---------- ----------
| Kate | 25000 |
| Rick | 102000 |
| Steve | 12000 |
| Mark | 150000 |
---------- ----------
HAVING: This clause is used to place conditions where we need to decide which group will be the part of final result-set. Also, we can not use the aggregate functions like SUM(), COUNT() etc. with WHERE clause. At such situation, we have to use HAVING condition.
Syntax:
SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition;
Example:
SELECT Fname, SUM(Marks)
FROM Student
GROUP BY Fname
HAVING SUM(Marks)>500;