What are some common clauses used with SELECT query in SQL?

Some common SQL clauses used in conjunction with a SELECT query are as follows:

  • WHERE clause in SQL is used to filter records that are necessary, based on specific conditions.
  • ORDER BY clause in SQL is used to sort the records based on some field(s) in ascending (ASC) or descending order (DESC).

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 order 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
  • GROUP BY clause in SQL is used to group records with identical data and can be used in conjunction with some aggregation functions to produce summarized results from the database.
  • HAVING clause in SQL is used to filter records in combination with the GROUP BY clause. It is different from WHERE, since the WHERE clause cannot filter aggregated 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