Previous

SQL SELECT

The SELECT statement is one of the most commonly used SQL commands, enabling you to retrieve data from one or more tables in a database. It is highly flexible and allows for various clauses like WHERE, ORDER BY, GROUP BY, and others to filter, sort, and manipulate the data in different ways.

 

Basic Syntax of SELECT

SELECT column1, column2, ...
FROM table_name;
  • column1, column2, ...: Specifies the columns to be retrieved.
  • table_name: Specifies the table from which to retrieve the data.

 

Key Features of the SELECT Statement

1. Selecting All Columns

You can retrieve all columns from a table using the * symbol.

SELECT * FROM employees;

This will retrieve all columns for every row in the employees table.

2. Selecting Specific Columns

You can specify the columns you want to retrieve by listing them after SELECT.

SELECT first_name, last_name FROM employees;

This will retrieve only the first_name and last_name columns from the employees table.

3. Using WHERE Clause

The WHERE clause is used to filter records based on a condition.

SELECT * FROM employees WHERE department = 'Sales';

This query will retrieve all records where the department is 'Sales'.

4. Using ORDER BY

The ORDER BY clause sorts the result set in either ascending (ASC) or descending (DESC) order.

SELECT * FROM employees ORDER BY last_name ASC;

This query will retrieve all employee records and order them by last_name in ascending order.

5. Using LIMIT

The LIMIT clause is used to specify the number of records to return.

SELECT * FROM employees LIMIT 5;

This will retrieve only the first 5 rows from the employees table.

6. Using DISTINCT

The DISTINCT keyword removes duplicate values from the result set.

SELECT DISTINCT department FROM employees;

This query will retrieve unique department values, removing duplicates.

7. Using Aggregate Functions

SQL aggregate functions like COUNT(), AVG(), SUM(), MAX(), and MIN() are used to perform calculations on the data.

SELECT COUNT(*) FROM employees WHERE department = 'Sales';

This query will count how many employees are in the 'Sales' department.

8. Using JOIN

You can use JOIN to retrieve data from multiple tables based on a related column.

SELECT employees.first_name, employees.last_name, departments.department_name
FROM employees
JOIN departments ON employees.department_id = departments.department_id;

This query will join the employees table with the departments table and retrieve employees' names along with their respective department names.

 

Example Queries

1. Retrieve All Employees in the 'HR' Department

SELECT * FROM employees WHERE department = 'HR';

2. Get the Total Number of Employees

SELECT COUNT(*) AS total_employees FROM employees;

3. Get Employee Names and Their Departments, Sorted by Department

SELECT first_name, last_name, department
FROM employees
ORDER BY department ASC;

4. Find Employees with a Salary Greater Than 50,000

SELECT * FROM employees WHERE salary > 50000;

5. Get the Average Salary in Each Department

SELECT department, AVG(salary) AS average_salary
FROM employees
GROUP BY department;

 

Summary

The SELECT statement is essential for querying data in SQL. It allows you to retrieve and filter data from one or more tables, sort the results, and even perform calculations with aggregate functions. Understanding the usage of SELECT, along with its clauses like WHERE, ORDER BY, and JOIN, is crucial for effective data retrieval in SQL.

Previous