1. What is SQL DQL?
SQL Data Query Language (DQL) commands are used to retrieve data from a database. The SELECT statement is the primary command in DQL, allowing you to query one or more tables and return relevant data.
2. Basic SELECT Statement
The simplest form of SELECT retrieves all columns from a table.
Example
SELECT * FROM employees;
This query returns all rows and columns from the employees table.
3. SELECT with WHERE Clause
The WHERE clause filters records based on specified conditions.
Example
SELECT name, age FROM employees
WHERE department = 'HR';
This query retrieves the names and ages of employees who work in the HR department.
4. SELECT with ORDER BY Clause
The ORDER BY clause sorts the results in ascending or descending order.
Example
SELECT name, age FROM employees
ORDER BY age DESC;
This query retrieves employees’ names and ages, sorted by age in descending order.
5. SELECT with GROUP BY and HAVING Clause
The GROUP BY clause groups rows that have the same values, and the HAVING clause filters these groups.
Example
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 50000;
This query shows the average salary of each department, only displaying departments where the average salary exceeds 50,000.
6. SELECT with JOINs (INNER, OUTER, SELF, CROSS)
SQL JOIN operations combine rows from two or more tables based on related columns.
INNER JOIN Example
SELECT employees.name, departments.department_name
FROM employees
INNER JOIN departments ON employees.department_id = departments.id;
LEFT OUTER JOIN Example
SELECT employees.name, departments.department_name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.id;
The INNER JOIN returns matching rows from both tables, while the LEFT JOIN returns all rows from the left table and matching rows from the right table.
7. SELECT with Aggregate Functions
Aggregate functions like COUNT, SUM, AVG, MAX, and MIN perform calculations on a set of values.
Example
SELECT COUNT(*) AS total_employees
FROM employees;
This query counts the total number of employees in the table.
8. SELECT with Subqueries
A subquery is a query nested inside another query.
Example
SELECT name FROM employees
WHERE department_id = (
SELECT id FROM departments WHERE department_name = 'HR'
);
This query retrieves the names of employees who work in the HR department using a subquery.
9. SELECT with CASE WHEN Statement
The CASE WHEN statement adds conditional logic to SQL queries.
Example
SELECT name,
CASE
WHEN salary > 50000 THEN 'High'
ELSE 'Low'
END AS salary_range
FROM employees;
This query categorizes employees’ salaries as ‘High’ or ‘Low’ based on a threshold.
10. Conclusion
The SELECT statement in SQL is a powerful tool for querying data. With options like WHERE, ORDER BY, GROUP BY, JOINs, aggregate functions, subqueries, and CASE WHEN, you can efficiently retrieve and analyze data from your database.