Some common SQL clauses used in conjunction with a SELECT query are as follows:
The WHERE clause in SQL is used to filter records based on specific conditions. It allows you to only include records in your query results that meet certain criteria.
The ORDER BY clause is used to sort the records in your query results. It allows you to specify the field or fields that you want to use to sort the records, and whether you want the results to be sorted in ascending or descending order. By default, the records are sorted in ascending order. You can use the ASC keyword to specify ascending order, or the DESC keyword to specify descending order.
Employee Table
| emp_id | emp_name | salary | dept_id | manager_id |
|---|---|---|---|---|
| E1 | Rahul | 15000 | D1 | M1 |
| E2 | Manoj | 15000 | D1 | M1 |
| E3 | James | 55000 | D2 | M2 |
| E4 | Michael | 25000 | D2 | M2 |
| E5 | Ali | 20000 | D10 | M3 |
| E6 | Robin | 35000 | D10 | M3 |
Example
The following SQL statement selects all the employees who have salaries equal to 15000 and then orders the result in descending order.
SELECT *
FROM Employee
WHERE salary = 15000
ORDER BY emp_id DESC;
Output
| emp_id | emp_name | salary | dept_id | manager_id |
|---|---|---|---|---|
| E2 | Manoj | 15000 | D1 | M1 |
| E1 | Rahul | 15000 | D1 | M1 |
The GROUP BY clause in SQL is used to group records that have the same data. This clause is often used with aggregation functions to produce summarized results from the database.
The HAVING clause is similar to the WHERE clause, but it is used in combination with the GROUP BY clause to filter groups of records, rather than individual records. The HAVING clause allows you to apply filters to the groups of records that are produced by the GROUP BY clause, whereas the WHERE clause only allows you to filter individual records.
Example
SELECT COUNT(emp_id), emp_name,manager_id
FROM Employee
GROUP BY manager_id
HAVING COUNT(manager_id) > 1;
Output
| emp_id | emp_name | manager_id |
|---|---|---|
| 2 | Rahul | M1 |
| 2 | James | M2 |
| 2 | Ali | M3 |
Code Runner
Copy the below queries in the code runner to see the result
SELECT *
FROM Employee
WHERE salary = 15000
ORDER BY emp_id DESC;
SELECT COUNT(emp_id), emp_name,manager_id
FROM Employee
GROUP BY manager_id
HAVING COUNT(manager_id) > 1;