This document covers essential SQL query operations, including selectign data, filtering with conditions, aggregating results, grouping data, ordering output, and limiting the number of rows returned.

Basic SELECT Syntax

The fundamental SELECT statement retrieves data from a table. The * wildcard selects all columns, while specific column names can be listed, separated by commas. The AS keyword allows for aliasing columns in the result set.

SELECT column1, column2 AS alias_name, * FROM table_name;

Eliminating Duplicate Rows

The DISTINCT keyword, placed after SELECT, removes duplicate rows from the result set.

SELECT DISTINCT column_name FROM table_name;

Filtering Data with WHERE Clauses

The WHERE clause filters rows based on specified conditions. Only rows evaluating to TRUE are included in the result.

SELECT * FROM table_name WHERE condition;

Comparison Operators

Standard comparison operators (=, >, >=, <, <=, != or <>) are used for filtering.

-- Select students with IDs greater than 3
SELECT * FROM students WHERE id > 3;

-- Select subjects with IDs less than or equal to 4
SELECT * FROM subjects WHERE id <= 4;

-- Select students whose names are not 'Huang Rong'
SELECT * FROM students WHERE sname != 'Huang Rong';

-- Select students who have not been deleted (assuming isdelete=0 means not deleted)
SELECT * FROM students WHERE isdelete = 0;

Logical Operators

AND, OR, and NOT combine conditions.

-- Select female students with IDs greater than 3
SELECT * FROM students WHERE id > 3 AND gender = 0;

-- Select students with IDs less than 4 OR not deleted
SELECT * FROM students WHERE id < 4 OR isdelete = 0;

Pattern Matching with LIKE

The LIKE operator performs pattern matching. % matches any sequence of characters, and _ matches a single character.

-- Select students whose last name starts with 'Huang'
SELECT * FROM students WHERE sname LIKE 'Huang%';

-- Select students whose name starts with 'Huang' and has one more character
SELECT * FROM students WHERE sname LIKE 'Huang_';

-- Select students whose name starts with 'Huang' OR contains 'Jing'
SELECT * FROM students WHERE sname LIKE 'Huang%' OR sname LIKE '%Jing%';

Range Queries with IN and BETWEEN

• IN checks if a value exists within a list of discrete values.
• BETWEEN checks if a value falls within a continuous range (inclusive).

-- Select students with IDs 1, 3, or 8
SELECT * FROM students WHERE id IN (1, 3, 8);

-- Select students with IDs between 3 and 8 (inclusive)
SELECT * FROM students WHERE id BETWEEN 3 AND 8;

-- Select male students with IDs between 3 and 8
SELECT * FROM students WHERE id BETWEEN 3 AND 8 AND gender = 1;

Null Value Checking

Use IS NULL to find rows with null values and IS NOT NULL to find rows with non-null values. Note that NULL is distinct from an empty string ('').

-- Select students with no hometown specified
SELECT * FROM students WHERE hometown IS NULL;

-- Select students with a hometown specified
SELECT * FROM students WHERE hometown IS NOT NULL;

-- Select female students with a hometown specified
SELECT * FROM students WHERE hometown IS NOT NULL AND gender = 0;

Operator Precedence

Parentheses () have the highest precedence, followed by NOT, then comparison operators, and finally logical operators AND and OR. AND is evaluated before OR unless overridden by parentheses.

Aggregate Functoins

Aggregate functions compute a summary value from a set of rows.

• COUNT(*): Returns the total number of rows.
• MAX(column): Returns the maximum value in a column.
• MIN(column): Returns the minimum value in a column.
• SUM(column): Returns the sum of values in a column.
• AVG(column): Returns the average value in a column.

-- Get the total number of students
SELECT COUNT(*) FROM students;

-- Get the maximum ID of female students
SELECT MAX(id) FROM students WHERE gender = 0;

-- Get the minimum ID of non-deleted students
SELECT MIN(id) FROM students WHERE isdelete = 0;

-- Get the sum of IDs for male students
SELECT SUM(id) FROM students WHERE gender = 1;

-- Calculate the average ID for non-deleted female students
SELECT AVG(id) FROM students WHERE isdelete = 0 AND gender = 0;

Grouping Data with GROUP BY

The GROUP BY clause groups rows that have the same values in specified columns. Aggregate functions can then be applied to each group. Only columns included in the GROUP BY clause or used in aggregate functions can be selected.

-- Count students by gender
SELECT gender, COUNT(*) FROM students GROUP BY gender;

-- Count students by hometown
SELECT hometown, COUNT(*) FROM students GROUP BY hometown;

Filtering Groups with HAVING

The HAVING clause filters groups based on conditions applied to aggregate results. It is used after GROUP BY.

-- Count male students (using HAVING)
SELECT gender, COUNT(*)
FROM students
GROUP BY gender
HAVING gender = 1;

WHERE vs. HAVING

• WHERE filters individual rows before they are grouped.
• HAVING filters entire groups after aggregation.

Sorting Results with ORDER BY

The ORDER BY clause sorts the result set based on one or more columns. ASC specifies ascending order (default), and DESC specifies descending order.

-- Select non-deleted male students, ordered by ID descending
SELECT * FROM students
WHERE gender = 1 AND isdelete = 0
ORDER BY id DESC;

-- Select non-deleted subjects, ordered by title ascending
SELECT * FROM subject
WHERE isdelete = 0
ORDER BY stitle ASC;

Limiting Result Set Size

The LIMIT clause restricts the number of rows returned. LIMIT offset, count retrieves count rows starting from the offset (0-indexed).

-- Example: Paginate results, showing 10 items per page for page 'n'
-- Assuming m = items per page, n = current page number
-- For page n, the offset is (n-1)*m
SELECT * FROM students
WHERE isdelete = 0
LIMIT (n-1)*m, m;

Complete SELECT Statement Structure

The full SELECT statement incorporates various clauses in a specific order of execution.

SELECT DISTINCT column_list
FROM table_name
WHERE conditions
GROUP BY group_columns
HAVING group_conditions
ORDER BY sort_columns
LIMIT offset, count;

Order of Execution:

1. FROM
2. WHERE
3. GROUP BY
4. SELECT (including DISTINCT and aggregate functions)
5. HAVING
6. ORDER BY
7. LIMIT

Note that not all clauses are required in every query; they are combined as needed.