SELECT Query

The SELECT Query is one of the most frequently used SQL commands. It is used to retrieve and display data from one or more tables in a database.

Basic SELECT Syntax

The basic SELECT syntax is as follows:

SELECT column1, column2, column3
FROM table_name;

Retrieving All Data

Use the asterisk (*) to retrieve all columns from a table:

SELECT * FROM EMPLOYEE;

This query returns all rows and all columns from the EMPLOYEE table. Although practical for exploration, in production it is better to avoid * and specify columns explicitly for better performance.

Retrieving Specific Columns

It is better to specify only the columns you actually need:

SELECT name, position, salary
FROM EMPLOYEE;

This query only displays the name, position, and salary columns from each row.

Column Aliases with AS

We can give a temporary name (alias) to a column using the AS keyword:

SELECT
    name AS Employee_Name,
    position AS Position,
    salary AS Salary_Monthly
FROM EMPLOYEE;

Aliases are very useful for making output easier to read, especially when displaying data to end users or in reports.

The WHERE Clause

The WHERE clause is used to filter the returned rows based on specific conditions:

SELECT name, position, salary
FROM EMPLOYEE
WHERE position = 'Software Engineer';

The query above only returns employees with the job title Software Engineer.

The ORDER BY Clause

Use ORDER BY to sort the results by a specific column:

-- Sort by name in ascending order (A-Z)
SELECT name, position, salary
FROM EMPLOYEE
ORDER BY name ASC;

-- Sort by salary in descending order (highest to lowest)
SELECT name, position, salary
FROM EMPLOYEE
ORDER BY salary DESC;

ASC (ascending) is the default order, from smallest to largest. DESC (descending) is the reverse order, from largest to smallest.

The LIMIT Clause

LIMIT is used to restrict the number of rows returned by a query:

-- Get the 3 employees with the highest salary
SELECT name, position, salary
FROM EMPLOYEE
ORDER BY salary DESC
LIMIT 3;

LIMIT is very useful when working with large datasets and you only need a small portion of the data.

Combining WHERE, ORDER BY, and LIMIT

These three clauses are often used together to produce specific results:

-- Top 3 active employees with the highest salary
SELECT name, position, salary, hire_date
FROM EMPLOYEE
WHERE is_active = TRUE
ORDER BY salary DESC
LIMIT 3;

The query above fetches the 3 active employees with the highest salaries, ordered from highest to lowest.

Example Queries on the EMPLOYEE Table

Here are several useful example queries for the EMPLOYEE table:

-- All active employees
SELECT name, position FROM EMPLOYEE WHERE is_active = TRUE;

-- Employees with a salary above 10 million
SELECT name, salary FROM EMPLOYEE WHERE salary > 10000000;

-- Employees who joined in 2023
SELECT name, hire_date FROM EMPLOYEE WHERE YEAR(hire_date) = 2023;

-- Total number of employees
SELECT COUNT(*) AS total_employees FROM EMPLOYEE;

-- Average salary
SELECT AVG(salary) AS avg_salary FROM EMPLOYEE;

Calculating with Aggregate Functions

SQL provides several aggregate functions for computing on data:

SELECT
    COUNT(*) AS employee_count,
    AVG(salary) AS avg_salary,
    MIN(salary) AS min_salary,
    MAX(salary) AS max_salary,
    SUM(salary) AS total_salary
FROM EMPLOYEE
WHERE is_active = TRUE;

Aggregate functions will be covered in detail in their own chapter.

Summary

The SELECT Query is the most fundamental SQL command for retrieving data. By mastering SELECT along with the WHERE, ORDER BY, and LIMIT clauses and aliases, we can display data the right way. In the next chapter, we will dive deeper into the WHERE Clause to understand the various filtering operators available.