1. Introduction to Data Query Language

---

Data Query Language (DQL) is a subset of SQL (Structured Query Language) specifically designed for retrieving data from databases.

Common DQL statements and their functions include:

• SELECT: Retrieves data from a database.

Example: SELECT column1, column2 FROM mytable WHERE condition;

• FROM: Specifies the table to query data from.

Example: SELECT column1, column2 FROM mytable;

• WHERE: Sets filtering conditions for the query.

Example: SELECT column1, column2 FROM mytable WHERE condition;

• GROUP BY: Groups query results by specified columns.

Example: SELECT column1, COUNT(column2) FROM mytable GROUP BY column1;

• HAVING: Filters grouped results based on specified conditions.

Example: SELECT column1, COUNT(column2) FROM mytable GROUP BY column1 HAVING COUNT(column2) > 10;

• ORDER BY: Sorts query results in ascending or descending order.

Example: SELECT column1, column2 FROM mytable ORDER BY column1 ASC;

• JOIN: Combines multiple tables based on related columns for querying.

Example: SELECT t1.column1, t2.column2 FROM table1 t1 JOIN table2 t2 ON t1.id = t2.id;

Note: Besides SELECT, DQL can also include other constructs like subqueries (using one query result as input for another), WITH clauses (defining reusable query blocks), and UNION operations (combining multiple query results).

2. Basic SELECT Operations

---

The SELECT keyword is fundamental in SQL for retrieving data from databases.

It's one of the most frequently used SQL operations for fetching data from database tables and returning result sets.

The basic syntax of a SELECT statement is:

SELECT [distinct]*|column1, column2, ...
FROM table_name
WHERE condition
GROUP BY column
HAVING condition
ORDER BY column [ASC|DESC]

Explanation of SELECT components:

• The DISTINCT keyword placed before column names removes duplicate records.
• The asterisk (*) selects all columns, equivalent to listing all column names.
• Column names specify wich columns to retrieve. You can use * to select all columns or specify individual or multiple columns.
• FROM clause specifies the table name from which to retrieve data.
• WHERE clause (optional) defines row selection criteria. Only rows meeting the condition will appear in the result set.
• GROUP BY clause (optional) groups the result set by specified columns, often used with aggregate functions like SUM, COUNT, etc.
• HAVING clause (optional) filters grouped results. The condition expression determines which groups appear in the result set.
• ORDER BY clause (optional) sorts the result set by specified columns. By default, it sorts in ascending order (ASC), with DESC for descending order.

Simple query example:

SELECT column1, column2, ...
FROM table_name

3. Conditional Queries

---

3.1 Overview of Conditional Queries

Conditional queries filter data that meets specific criteria using the WHERE clause within SELECT statements.

This allows extracting only data matching the specified conditions from database tables.

Basic syntax:

SELECT column1, column2, ...
FROM table_name
WHERE condition;

Explanation:

• column1, column2 represent columns to retrieve. You can choose specific columns or use * to retrieve all columns.
• table_name is the source table for data retrieval. WHERE clause sets filtering conditions; only matching rows appear in results.

Various comparison operators (equal, greater than, less than, etc.), logical operators (AND, OR, NOT) can be used in WHERE clauses to construct conditions.

Conditional queries are commonly used in applications to limit data ranges efficiently.

Conditional queries fall into several categories:

3.2 Comparison Queries

Comparison queries filter data using comparison operators to evaluate relationships between table fields and given values.

Common comparison operators include:

• Equal (=): Checks if two values are equal.
• Not Equal (<> or !=): Checks if two values are different.
• Greater Than (>): Checks if left value exceeds right value.
• Greater Than or Equal (>=): Checks if left value is greater than or equal to right value.
• Less Than (<): Checks if left value is less than right value.
• Less Than or Equal (<=): Checks if left value is less than or equal to right value.

The general syntax is:

SELECT column
FROM table_name
WHERE column operator value;

Explanation: column represents the field to compare, operator is the comparison operator, and value is what to compare against.

3.3 Null Value Queries

Null value queries filter out records containing NULL values in specified columns.

Use the IS NOT NULL operator to exclude NULL values.

Syntax:

SELECT * FROM table_name WHERE column_name IS NOT NULL;

Explanation:

• table_name is the target table, column_name is the column to check for non-null values.
• This query returns records where the specified column contains valid data, excluding NULL entries.

Null value queries help eliminate invalid or incomplete data since NULL represents missing, unknown, or inapplicable values. Note the distinction between NULL and empty strings: NULL indicates no value, while empty strings represent an actual empty value.

3.4 Range Queries

Range queries filter data falling within specified boundaries.

(1) Using BETWEEN AND:

Syntax:

column_name BETWEEN value1 AND value2

Example:

SELECT * FROM table_name WHERE column_name BETWEEN value1 AND value2;

Description: BETWEEN AND includes both boundary values in the range.

(2) Using IN:

Syntax:

column_name IN (value1, value2, ...)

Example:

SELECT * FROM table_name WHERE column_name IN (value1, value2, ...);

Description: IN matches any value from a list of specified values.

Key differences:

• BETWEEN AND works with continuous ranges, suitable for numeric or date types.
• IN works with discrete values, applicable to any data type.

Range queries improve efficiency and accuracy when filtering specific value ranges.

3.5 Pattern Matching Queries

Pattern matching queries use LIKE with wildcards to search for data matching specific patterns.

Common wildcards are percent (%) and underscore (_).

(1) Percent (%) wildcard: Matches zero or more characters at any position.

Example:

SELECT * FROM table_name WHERE column_name LIKE '%abc%';

Description: Matches values containing "abc", such as "abc123", "123abc", "abcdef".

(2) Underscore (_) wildcard: Matches exactly one character.

Example:

SELECT * FROM table_name WHERE column_name LIKE 'a_';

Description: Matches values starting with "a" followed by any single character, such as "ab", "ax", "az".

3.6 Logical Queries

Logical queries combine multiple conditions using logical operators (AND, OR, NOT) to retrieve data meeting specific criteria.

These typically use SELECT with WHERE clauses containing multiple expressions combined with logical operators.

Common operators:

(1) AND operator: Returns records meeting all specified conditions.

Example:

SELECT * FROM table_name WHERE condition1 AND condition2;

Description: Returns records satisfying both condition1 and condition2.

(2) OR operator: Returns records meeting any specified condition.

Example:

SELECT * FROM table_name WHERE condition1 OR condition2;

Description: Returns records satisfying either condition1 or condition2.

(3) NOT operator: Returns records not meeting a specified condition.

Example:

SELECT * FROM table_name WHERE NOT condition;

Description: Returns records failing to satisfy the condition.

Logical queries allow complex condition combinations and nesting for sophisticated data filtering.

4. Sorting Results

---

Sorting queries organize database data according to one or more fields for better analysis.

Syntax:

SELECT column1, column2, ...
FROM table
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...;

Explanation:

• [ASC|DESC]: Optional parameter specifying sort order, ASC for ascending (default), DESC for descending.
• Multiple fields can be specified with comma separation and individual sort directions.
• Default sorting is ascending if no direction is specified.
• Multi-column sorting prioritizes first field, then second field for ties, and so on.
• Execution sequence: SELECT retrieves result set, then ORDER BY sorts it.

5. Aggregate Functions

---

Previous queries were horizontal, checking conditions row by row.

Aggregate functions perform vertical calculations on column values, returning single values.

They ignore NULL values.

Aggregate queries perform statistical computations on data rather than simple retrieval.

They calculate sums, averages, maximums, minimums, counts, etc.

Basic syntax:

SELECT aggregate_function(column1), aggregate_function(column2), ...
FROM table
WHERE condition
GROUP BY column1, column2, ... 
HAVING condition;

Explanation:

• SELECT: Specifies returned aggregate results.
• aggregate_function: Functions calculating statistical values.
• FROM: Table to query.
• WHERE: Optional filtering condition.
• GROUP BY: Fields to group by.
• HAVING: Optional post-grouping filter.
• Common aggregate functions:

**Function**	**Purpose**
COUNT(column)	Counts non-NULL values in a column.
SUM(column)	Calculates sum of column values, returns 0 for non-numeric types.
MAX(column)	Finds maximum value, uses string sorting for text types.
MIN(column)	Finds minimum value, uses string sorting for text types.
AVG(column)	Calculates average of column values, returns 0 for non-numeric types.

6. Grouping and Having Clauses

---

6.1 Grouping Overview

Grouping queries categorize data by specified fields and perform aggregate calculations.

They divide data into groups and compute statistics for each group.

The GROUP BY clause organizes results by one or more columns, enabling aggregate operations.

HAVING filters grouped results similar to how WHERE filters individual rows.

Syntax:

SELECT column1, column2, ..., aggregate_function(column)
FROM table
WHERE condition
GROUP BY column1, column2, ...
HAVING condition;

• SELECT: Fields to query, including regular fields and aggregate functions.
• column1, column2, ...: Query fields, single or multiple.
• aggregate_function(column): Field for aggregation.
• FROM: Source table.
• WHERE: Optional pre-filtering condition.
• GROUP BY: Fields to group by.
• HAVING: Optional post-grouping filter.

Important considerations:

• All non-aggregate fields in SELECT must appear in GROUP BY clause.
• Field order doesn't affect grouping results.
• Common aggregate functions: SUM, AVG, COUNT, MAX, MIN.

6.2 Using GROUP BY

Examples:

-- Group by gender field
SELECT gender FROM students GROUP BY gender;
-- Group by name and gender fields
SELECT name, gender FROM students GROUP BY name, gender;

Explanation:

• GROUP BY performs deduplication
• Used for statistical grouping (GROUP BY + aggregate functions)

6.3 GROUP BY with Aggregate Functions

Example: Count employees and calculate average salary per department

SELECT department_id, COUNT(*) AS employee_count, AVG(salary) AS average_salary
FROM employees
GROUP BY department_id;

Explanation:

• Groups results by department_id
• Uses COUNT(*) to count employees per department
• Uses AVG(salary) to calculate average salary per department
• Important rule: Non-aggregate fields in SELECT must appear in GROUP BY

6.4 GROUP BY with HAVING

HAVING filters grouped results, unlike WHERE which filters individual rows.

HAVING is used exclusively with GROUP BY.

Syntax:

SELECT column1, column2, ..., aggregate_function(column)
FROM table
WHERE condition
GROUP BY column1, column2, ...
HAVING condition;

Examples:

-- Group by gender, show groups with more than 2 members
SELECT gender, COUNT(*) FROM students GROUP BY gender HAVING COUNT(*) > 2;

-- Count products per category, show only those with more than 1 item
SELECT category_id, COUNT(*) FROM product GROUP BY category_id HAVING COUNT(*) > 1;

-- Find customers with total order amounts exceeding 1000
SELECT customer_id, SUM(order_amount) AS total_amount
FROM orders
GROUP BY customer_id
HAVING SUM(order_amount) > 1000;

-- Find departments with average salary above 5000
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 5000;

Explanation:

• HAVING filters grouped results based on aggregate conditions
• Provides flexible filtering of grouped data

7. Pagination with LIMIT

---

The LIMIT keyword restricts query results to a specific number of records for pagination.

LIMIT syntax in SELECT statements:

SELECT column1, column2, ...
FROM table
LIMIT offset, count;

• column1, column2, ...: Columns to query
• table: Source table
• offset: Starting position (zero-based index)
• count: Number of records to return

Example:

SELECT * 
FROM customers
LIMIT 10, 5;

Explanation:

• Starts from record 11 (offset 10) and returns 5 records
• Commonly used for web/mobile page displays
• Best practice: Use ORDER BY before LIMIT for consistent results

LIMIT helps optimize performance with large datasets by loading data incrementally.

8. Join Operations

---

8.1 Introduction to Joins

Join operations combine related data from multiple tables based on shared columns.

They match rows from different tables using common fields.

Common join types:

• Inner Join: Returns matching rows from both tables
• Left Join: Returns all left table rows plus matching right table rows
• Right Join: Returns all right table rows plus matching left table rows
• Full Outer Join: Returns all rows from both tables

8.2 Inner Join

Inner joins return only rows where both tables have matching data.

Syntax:

SELECT column_list
FROM tableA
INNER JOIN tableB ON join_condition;

Characteristics:

• Returns only matching records from both tables
• Supports multiple join conditions
• Can join multiple tables
• Allows filtering, aggregation, and sorting on joined data

8.3 Left Outer Join

Left outer joins return all left table rows and matching right table rows.

Missing right table matches appear as NULL.

Syntax:

SELECT column_list
FROM tableA
LEFT JOIN tableB ON join_condition;

8.4 Right Outer Join

Right outer joins return all right table rows and matching left table rows.

Missing left table matches appear as NULL.

Syntax:

SELECT column_list
FROM tableA
RIGHT JOIN tableB ON join_condition;

8.5 Full Outer Join

Full outer joins return all rows from both tables.

Missing matches appear as NULL.

Syntax:

SELECT column_list
FROM tableA
FULL JOIN tableB ON join_condition;

Note: Different DBMS may support FULL JOIN differently; some require OUTER keyword.

9. Subqueries

---

Subqueries are nested queries within larger queries.

They provide dynamic conditions or data sources based on other query results.

Syntax:

SELECT column_list
FROM table
WHERE column IN (SELECT column_list FROM table WHERE condition);

Explanation:

• WHERE column IN: Uses subquery result as filter condition
• (SELECT column_list FROM table WHERE condition): Nested query returning result set
• Subqueries can return single values, column lists, or multi-column results

Relationship between main and subqueries:

• Subqueries are embedded within main queries
• They act as conditions or data sources
• Subqueries are complete SELECT statements that can execute independently

Common subquery scenarios:

(1) Subquery as condition (scalar subquery):

SELECT column_list
FROM table
WHERE column = (SELECT column FROM table WHERE condition);

(2) Subquery as column value:

SELECT column, (SELECT column FROM table WHERE condition) AS alias
FROM table;

(3) Subquery as table:

SELECT column_list
FROM (SELECT column_list FROM table WHERE condition) AS subquery_table
JOIN table ON subquery_table.column = table.column;

Subqueries can be nested multiple times for complex logic, providing advanced filtering capabilities.