> ## Documentation Index
> Fetch the complete documentation index at: https://fastapi2day.codewithsiva.dev/llms.txt
> Use this file to discover all available pages before exploring further.

# DQL: Querying and Analyzing Your Data with SQL SELECT

> Master the SQL SELECT statement — filtering with WHERE, sorting, grouping with GROUP BY, aggregating data, and understanding execution order.

Data Query Language (DQL) is the heart of SQL. Almost every interaction you have with a database involves a `SELECT` statement, and learning to write precise, efficient queries is one of the most valuable skills you can build. This chapter covers the full `SELECT` toolkit: choosing columns, filtering rows with `WHERE`, eliminating duplicates with `DISTINCT`, sorting with `ORDER BY`, paginating with `LIMIT` and `OFFSET`, performing calculations with aggregate functions, grouping data with `GROUP BY`, and filtering groups with `HAVING`. Throughout, you'll use the sample Employee and Department tables you set up in the previous chapter.

## Reference Tables

All examples in this chapter use these tables:

**Department:**

| Department ID | Department Name |
| ------------- | --------------- |
| 1             | Engineering     |
| 2             | HR              |
| 3             | Sales           |

**Employee:**

| Employee ID | Employee Name | Salary | City      | Department ID |
| ----------- | ------------- | ------ | --------- | ------------- |
| 101         | Rahul         | 65000  | Hyderabad | 1             |
| 102         | Anitha        | 55000  | Bengaluru | 2             |
| 103         | Kiran         | 72000  | Hyderabad | 1             |
| 104         | Sneha         | 50000  | Chennai   | 3             |
| 105         | Ajay          | 80000  | Hyderabad | 1             |

## SELECT Statement

The `SELECT` statement retrieves data from one or more tables.

**Retrieve all columns:**

```sql theme={null}
SELECT *
FROM employee;
```

**Retrieve specific columns:**

```sql theme={null}
SELECT employee_name, salary
FROM employee;
```

<Accordion title="Practice: Display names and cities">
  Write a query to display only employee names and cities.

  ```sql theme={null}
  SELECT employee_name, city
  FROM employee;
  ```
</Accordion>

## Column Aliases

Use `AS` to give a column a temporary display name. Aliases make output easier to read and are especially useful when performing calculations.

```sql theme={null}
SELECT
    employee_name AS Name,
    salary        AS MonthlySalary,
    salary * 12   AS AnnualSalary
FROM employee;
```

<Accordion title="Practice: Rename columns">
  Display employee names as **Employee** and salaries as **Salary**.

  ```sql theme={null}
  SELECT
      employee_name AS Employee,
      salary        AS Salary
  FROM employee;
  ```
</Accordion>

## DISTINCT

`DISTINCT` removes duplicate values from a result set, returning only unique values.

```sql theme={null}
-- Show each city only once
SELECT DISTINCT city
FROM employee;
```

```sql theme={null}
-- Show each unique department ID
SELECT DISTINCT department_id
FROM employee;
```

<Accordion title="Practice: Unique department IDs">
  ```sql theme={null}
  SELECT DISTINCT department_id
  FROM employee;
  ```
</Accordion>

## WHERE Clause

The `WHERE` clause filters rows based on a condition. Only rows that satisfy the condition appear in the result.

```sql theme={null}
-- Employees earning more than ₹60,000
SELECT *
FROM employee
WHERE salary > 60000;
```

```sql theme={null}
-- Employees based in Hyderabad
SELECT *
FROM employee
WHERE city = 'Hyderabad';
```

### Comparison Operators

| Operator     | Meaning               |
| ------------ | --------------------- |
| `=`          | Equal                 |
| `!=` or `<>` | Not equal             |
| `>`          | Greater than          |
| `<`          | Less than             |
| `>=`         | Greater than or equal |
| `<=`         | Less than or equal    |

<Accordion title="Practice: Salaries under ₹60,000">
  ```sql theme={null}
  SELECT *
  FROM employee
  WHERE salary < 60000;
  ```
</Accordion>

## Logical Operators

Combine multiple conditions with `AND`, `OR`, and `NOT`.

**AND — both conditions must be true:**

```sql theme={null}
SELECT *
FROM employee
WHERE city = 'Hyderabad'
  AND salary > 60000;
```

**OR — at least one condition must be true:**

```sql theme={null}
SELECT *
FROM employee
WHERE city = 'Hyderabad'
   OR city = 'Chennai';
```

**NOT — negates a condition:**

```sql theme={null}
SELECT *
FROM employee
WHERE NOT city = 'Hyderabad';
```

<Accordion title="Practice: Hyderabad employees earning more than ₹65,000">
  ```sql theme={null}
  SELECT *
  FROM employee
  WHERE city = 'Hyderabad'
    AND salary > 65000;
  ```
</Accordion>

## BETWEEN

`BETWEEN` tests whether a value falls within an inclusive range.

```sql theme={null}
SELECT *
FROM employee
WHERE salary BETWEEN 50000 AND 70000;
```

<Accordion title="Practice: Salaries between ₹55,000 and ₹80,000">
  ```sql theme={null}
  SELECT *
  FROM employee
  WHERE salary BETWEEN 55000 AND 80000;
  ```
</Accordion>

## IN

`IN` checks whether a value matches any item in a list, replacing multiple `OR` conditions.

```sql theme={null}
-- Employees in departments 1 or 3
SELECT *
FROM employee
WHERE department_id IN (1, 3);
```

<Accordion title="Practice: Employees in departments 2 and 3">
  ```sql theme={null}
  SELECT *
  FROM employee
  WHERE department_id IN (2, 3);
  ```
</Accordion>

## LIKE

`LIKE` matches text patterns using wildcard characters.

| Pattern | Matches                |
| ------- | ---------------------- |
| `A%`    | Starts with A          |
| `%a`    | Ends with a            |
| `%an%`  | Contains "an" anywhere |
| `_`     | Any single character   |

```sql theme={null}
-- Names starting with 'A'
SELECT *
FROM employee
WHERE employee_name LIKE 'A%';
```

```sql theme={null}
-- Names ending with 'a'
SELECT *
FROM employee
WHERE employee_name LIKE '%a';
```

<Accordion title="Practice: Names starting with S">
  ```sql theme={null}
  SELECT *
  FROM employee
  WHERE employee_name LIKE 'S%';
  ```
</Accordion>

## NULL Values

Use `IS NULL` or `IS NOT NULL` to find rows where a value is missing or present.

```sql theme={null}
-- Employees with no department assigned
SELECT *
FROM employee
WHERE department_id IS NULL;

-- Employees who have a department
SELECT *
FROM employee
WHERE department_id IS NOT NULL;
```

<Warning>
  You cannot use `= NULL` to test for missing values in SQL. You must always use `IS NULL` or `IS NOT NULL`.
</Warning>

## ORDER BY

`ORDER BY` sorts the result set by one or more columns.

```sql theme={null}
-- Ascending order (default)
SELECT * FROM employee ORDER BY salary;

-- Descending order
SELECT * FROM employee ORDER BY salary DESC;

-- Sort by department first, then by salary within each department
SELECT *
FROM employee
ORDER BY department_id, salary DESC;
```

<Accordion title="Practice: Sort by employee name">
  ```sql theme={null}
  SELECT *
  FROM employee
  ORDER BY employee_name;
  ```
</Accordion>

## LIMIT and OFFSET

`LIMIT` restricts how many rows are returned. `OFFSET` skips a number of rows before starting to return results — useful for pagination.

```sql theme={null}
-- Return only the first 3 rows
SELECT * FROM employee LIMIT 3;

-- Skip the first 3 rows, then return the next 2
SELECT * FROM employee LIMIT 2 OFFSET 3;
```

<Accordion title="Practice: First four employees">
  ```sql theme={null}
  SELECT *
  FROM employee
  LIMIT 4;
  ```
</Accordion>

## Aggregate Functions

Aggregate functions perform calculations across multiple rows and return a single value.

| Function  | Purpose        |
| --------- | -------------- |
| `COUNT()` | Count rows     |
| `SUM()`   | Sum of values  |
| `AVG()`   | Average value  |
| `MIN()`   | Smallest value |
| `MAX()`   | Largest value  |

```sql theme={null}
SELECT COUNT(*) AS total_employees  FROM employee;
SELECT AVG(salary)  AS avg_salary   FROM employee;
SELECT MAX(salary)  AS highest      FROM employee;
SELECT MIN(salary)  AS lowest       FROM employee;
SELECT SUM(salary)  AS payroll      FROM employee;
```

<Accordion title="Practice: Find minimum salary">
  ```sql theme={null}
  SELECT MIN(salary) AS minimum_salary
  FROM employee;
  ```
</Accordion>

## GROUP BY

`GROUP BY` groups rows that share the same value in one or more columns, then applies an aggregate function to each group.

```sql theme={null}
-- Average salary per department
SELECT
    department_id,
    AVG(salary) AS avg_salary
FROM employee
GROUP BY department_id;
```

```sql theme={null}
-- Number of employees in each city
SELECT
    city,
    COUNT(*) AS employee_count
FROM employee
GROUP BY city;
```

<Accordion title="Practice: Count employees per city">
  ```sql theme={null}
  SELECT
      city,
      COUNT(*) AS employee_count
  FROM employee
  GROUP BY city;
  ```
</Accordion>

## HAVING

`HAVING` filters groups *after* aggregation, whereas `WHERE` filters individual rows *before* grouping. Use `HAVING` whenever your filter condition involves an aggregate function.

```sql theme={null}
-- Departments where the average salary exceeds ₹60,000
SELECT
    department_id,
    AVG(salary) AS avg_salary
FROM employee
GROUP BY department_id
HAVING AVG(salary) > 60000;
```

<Accordion title="Practice: Cities with more than one employee">
  ```sql theme={null}
  SELECT
      city,
      COUNT(*) AS employee_count
  FROM employee
  GROUP BY city
  HAVING COUNT(*) > 1;
  ```
</Accordion>

<Tip>
  A quick way to remember the difference: **WHERE filters rows, HAVING filters groups.** You write `WHERE` before `GROUP BY` and `HAVING` after it.
</Tip>

## SQL Execution Order

You write SQL clauses in this order:

```text theme={null}
SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT
```

But SQL *executes* them in a different order:

```text theme={null}
FROM
  ↓
WHERE
  ↓
GROUP BY
  ↓
HAVING
  ↓
SELECT
  ↓
ORDER BY
  ↓
LIMIT
```

This explains why column aliases created in `SELECT` cannot be used in `WHERE` — `WHERE` runs before `SELECT`, so the alias doesn't exist yet at that point.

## Summary

In this chapter you learned how to:

* Retrieve data using `SELECT` with specific columns or `*`
* Rename output columns with `AS` aliases
* Remove duplicates using `DISTINCT`
* Filter rows with `WHERE`, comparison operators, `AND`/`OR`/`NOT`, `BETWEEN`, `IN`, `LIKE`, and `IS NULL`
* Sort results with `ORDER BY` (ascending and descending)
* Limit rows with `LIMIT` and `OFFSET`
* Calculate totals, averages, counts, and extremes with aggregate functions
* Group rows with `GROUP BY`
* Filter groups with `HAVING`
* Understand SQL's execution order

The next chapter introduces **SQL JOINs**, which let you combine data from multiple tables in a single query.