SQL Clauses

In SQL (Structured Query Language), clauses are used to filter, group, and sort the data retrieved from a database. Clauses are used in conjunction with SQL commands to perform specific operations on the data.

WHERE Clause

The WHERE clause is used to filter records based on a specified condition. It is used to retrieve only those records that satisfy the specified condition. The WHERE clause is commonly used with SELECT, UPDATE, and DELETE statements.

SELECT
  *
FROM
  employees
WHERE
  emp_salary > 50000;

GROUP BY Clause

The GROUP BY clause is used to group the result set based on one or more columns. It is used in conjunction with aggregate functions like COUNT, SUM, AVG, etc. The GROUP BY clause is commonly used with the SELECT statement.

SELECT
  emp_dept,
  COUNT(*)
FROM
  employees
GROUP BY
  emp_dept;

HAVING Clause

The HAVING clause is used to filter the result set based on aggregate functions. It is used to filter the groups created by the GROUP BY clause. The HAVING clause is commonly used with the SELECT statement.

SELECT
  emp_dept,
  AVG(emp_salary)
FROM
  employees
GROUP BY
  emp_dept
HAVING
  AVG(emp_salary) > 50000;

Note: The HAVING clause is used to filter groups, whereas the WHERE clause is used to filter rows.

ORDER BY Clause

The ORDER BY clause is used to sort the result set based on one or more columns. It is used to specify the order in which the result set should be displayed. The ORDER BY clause is commonly used with the SELECT statement.

SELECT
  *
FROM
  employees
ORDER BY
  emp_salary DESC;

Note: The ORDER BY clause can be used to sort the result set in ascending (ASC) or descending (DESC) order. by default, it sorts in ascending order.

LIMIT Clause

The LIMIT clause is used to limit the number of rows returned by a query. It is used to restrict the number of rows in the result set. The LIMIT clause is commonly used with the SELECT statement.

SELECT
  *
FROM
  employees
LIMIT
  10;

OFFSET Clause

The OFFSET clause is used to skip a specified number of rows before returning the result set. It is used in conjunction with the LIMIT clause to implement pagination. The OFFSET clause is commonly used with the SELECT statement.

SELECT
  *
FROM
  employees
LIMIT
  10 OFFSET 20;