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

SQL (Structured Query Language) is a powerful language used for managing and manipulating relational databases. The SELECT statement is one of the fundamental SQL statements, allowing us to retrieve data from database tables. To enhance the flexibility and efficiency of SELECT queries, SQL provides various clauses that can be used in combination with the SELECT statement. In this article, we will explore some of the common clauses used with the SELECT query in SQL, along with examples.

1. WHERE Clause: The WHERE clause is used to filter rows based on specific conditions. It allows us to specify criteria that must be met for a row to be included in the result set. 

For example:

sql
SELECT * FROM Customers WHERE Country = 'USA';

This query selects all rows from the "Customers" table where the "Country" column has a value of 'USA'.

2. ORDER BY Clause: The ORDER BY clause is used to sort the result set in ascending (default) or descending order based on one or more columns.

 For example:

sql
SELECT * FROM Employees ORDER BY LastName ASC, FirstName ASC;

This query selects all rows from the "Employees" table and sorts them first by "LastName" in ascending order, and then by "FirstName" in ascending order.

3. GROUP BY Clause: The GROUP BY clause is used to group rows based on one or more columns. It is typically used with aggregate functions to perform calculations on each group. 

For example:

sql
SELECT Department, COUNT(*) FROM Employees GROUP BY Department;

This query groups the rows from the "Employees" table by the "Department" column and calculates the count of employees in each department.

4. HAVING Clause: The HAVING clause is used in conjunction with the GROUP BY clause to filter groups based on conditions. It allows us to specify criteria for groups that should be included in the result set. 

For example:

sql
SELECT Department, AVG(Salary) FROM Employees GROUP BY Department HAVING AVG(Salary) > 50000;

This query groups the rows by the "Department" column, calculates the average salary for each department, and selects only the groups where the average salary is greater than 50000.

5. DISTINCT Clause: The DISTINCT clause is used to retrieve unique values from a specific column in the result set. It eliminates duplicate rows. 

For example:

sql
SELECT DISTINCT Country FROM Customers;

This query selects all unique values from the "Country" column in the "Customers" table.

Conclusion: In SQL, the SELECT statement is enhanced by various clauses that provide additional functionality and flexibility. The WHERE clause filters rows based on conditions, the ORDER BY clause sorts the result set, the GROUP BY clause groups rows, the HAVING clause filters groups, and the DISTINCT clause retrieves unique values. Understanding and effectively using these common clauses with the SELECT query allows developers to retrieve and manipulate data in a precise and controlled manner, making SQL a versatile language for data analysis and management.