Mastering SQL Data Retrieval, Constraints, and Relational Joins

Sorting Query Results

The ORDER BY clause controls how rows are returned. It does not alter the stored data, only the presentation.

SELECT column_list FROM table_name
WHERE condition
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC];

Single‑column ordering
Sort employees by age in descending order:

SELECT name, age FROM employees ORDER BY age DESC;

Composite ordering
If two employees share the same age, further sort by salary ascending:

SELECT name, age, salary
FROM employees
ORDER BY age DESC, salary ASC;

Aggregate Functions

Aggregate operations scan a column vertically and return a single computed value. NULL values are ignored unless handled explicitly.

SELECT AGG_FUNC(column) FROM table_name;

Count total rows (include NULLs): COUNT(*)
Count non‑null values in a column: COUNT(column)
Replace NULL with a default before aggregating: IFNULL(column, default_value)

Examples with an employees table (columns: id, name, age, salary, dept_id):

--1. total number of employees
SELECT COUNT(*) FROM employees;

--2. replace NULL salary with 0 and sum
SELECT SUM(IFNULL(salary, 0)) FROM employees;

--3. average, maximum, minimum salary
SELECT AVG(salary), MAX(salary), MIN(salary) FROM employees;

Grouping Data with `GROUP BY`

Group rows that share a common value, then aggregate each group.

SELECT column, AGG_FUNC(column)
FROM table_name
WHERE condition
GROUP BY column
HAVING group_condition;

The GROUP BY clause creates distinct groups; without aggregation it merely returns one row per group. Always include the grouping column in the SELECT list when you need to identify groups.

Examples:

-- Average salary per department
SELECT dept_id, AVG(salary) AS avg_sal
FROM employees
GROUP BY dept_id;

-- Count employees per department, only show departments with more than 5 employees
SELECT dept_id, COUNT(*) AS emp_cnt
FROM employees
GROUP BY dept_id
HAVING emp_cnt > 5;

-- Departments where the maximum salary exceeds 80000
SELECT dept_id, MAX(salary) AS max_sal
FROM employees
WHERE age > 30
GROUP BY dept_id
HAVING max_sal > 80000;

HAVING vs WHERE

WHERE filters rows before grouping.
HAVING filters groups after aggregation.
Use WHERE for individual row conditions, HAVING for conditions on aggregate results.

Limiting Result Sets with `LIMIT`

Restrict the number of rows returned, commonly used for pagination.

SELECT columns FROM table_name
[WHERE ...] [ORDER BY ...]
LIMIT offset, row_count;

offset is the starting position (0‑based for many databases, though MySQL uses 0‑based offset).
row_count is the number of rows to fetch.

Example: fetch rows 11 to 20 (10 rows starting from the 11th):

SELECT * FROM employees ORDER BY hire_date LIMIT 10, 10;

encoding pagination parameters:

page number = n
rows per page = m
LIMIT (n-1)*m, m.

Database Table Constraints

Constraints enforce data integrity and validity at the schema level.

Primary Key

A primary key uniquely identifies each row. It prohibits NULL and duplicates.

-- define during table creation
CREATE TABLE departments (
  id INT PRIMARY KEY,
  name VARCHAR(50)
);

-- add to existing table
ALTER TABLE employees ADD PRIMARY KEY (id);

-- drop primary key
ALTER TABLE employees DROP PRIMARY KEY;

Create a table departments with id INT PRIMARY KEY and dept_name VARCHAR(50).
Insert a duplicate id – the database rejects it.
Remove the constraint and NULL values become acceptable.

A table can have only one primary key (which may span multiple columns as a composite key).

Auto‑increment

Let the database generate unique numeric values automatically.

CREATE TABLE employees (
  emp_id INT PRIMARY KEY AUTO_INCREMENT,
  full_name VARCHAR(100),
  age INT
) AUTO_INCREMENT=1000;

-- change starting value later
ALTER TABLE employees AUTO_INCREMENT=5000;

DELETE keeps the sequence unchanged.
TRUNCATE resets the sequence to the original start value.

Unique Constraint

Ensures a column (or combination) contains distinct values. NULL is allowed because its not considered a duplicate.

CREATE TABLE users (
  user_id INT PRIMARY KEY,
  email VARCHAR(100) UNIQUE
);

-- add afterwards
ALTER TABLE users ADD UNIQUE (email);

Not‑Null Constraint

Forbids NULL entries.

CREATE TABLE products (
  product_id INT PRIMARY KEY,
  product_name VARCHAR(100) NOT NULL
);

Default Values

Assign a fallback value when no explicit value is given.

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  status VARCHAR(20) DEFAULT 'pending',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Foreign Key Constraints

Maintain referential integrity between tables. A foreign key in a child table references a primary key in a parent table.

Consider a single, unnormalised company_data table that stores both employee and department details, causing redundancy. A better design separates them:

-- parent (department)
CREATE TABLE departments (
  dept_id INT PRIMARY KEY AUTO_INCREMENT,
  dept_name VARCHAR(50),
  location VARCHAR(50)
);

-- child (employee)
CREATE TABLE employees (
  emp_id INT PRIMARY KEY AUTO_INCREMENT,
  emp_name VARCHAR(100),
  age INT,
  dept_id INT,
  CONSTRAINT fk_dept FOREIGN KEY (dept_id)
    REFERENCES departments(dept_id)
    ON UPDATE CASCADE
    ON DELETE CASCADE
);

. prevents inserts with a dept_id that does not exist in departments.
Cascading options:

ON UPDATE CASCADE – propagate primary key changes.
ON DELETE CASCADE – automatically delete child rows when the parent is deleted.
ON DELETE SET NULL / RESTRICT are alternatives.

add foreign key to an existing table:

ALTER TABLE employees
  ADD CONSTRAINT fk_dept
  FOREIGN KEY (dept_id) REFERENCES departments(dept_id);

remove a foreign key:

ALTER TABLE employees DROP FOREIGN KEY fk_dept;

Multi‑table Queries

A relational design requires joining tables to gather related information.

Sample data:

CREATE TABLE departments (
  dept_id INT PRIMARY KEY AUTO_INCREMENT,
  dept_name VARCHAR(50)
);
INSERT INTO departments (dept_name) VALUES ('Engineering'),('Sales'),('Finance');

CREATE TABLE employees (
  emp_id INT PRIMARY KEY AUTO_INCREMENT,
  emp_name VARCHAR(50),
  gender CHAR(1),
  salary DECIMAL(10,2),
  join_date DATE,
  dept_id INT,
  FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);
INSERT INTO employees (emp_name, gender, salary, join_date, dept_id) VALUES
('Alice','F',7200,'2013-02-24',1),
('Bob','M',3600,'2010-12-02',2),
('Charlie','M',9000,'2008-08-08',2),
('Diana','F',5000,'2015-10-07',3),
('Eve','F',4500,'2011-03-14',1);

Cross Join (Cartesian Product)

Combining tables without a join condition produces every possible row combination, rarely useful on its own.

SELECT * FROM employees, departments;

Adding a proper join condition eliminates meaningless pairings.

SELECT employees.emp_name, departments.dept_name
FROM employees, departments
WHERE employees.dept_id = departments.dept_id;

Inner Join

Return rows that match in both tables.

Implicit syntax (condition in WHERE):

SELECT e.emp_name, d.dept_name
FROM employees e, departments d
WHERE e.dept_id = d.dept_id;

Explicit syntax (recommended):

SELECT e.emp_name, d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id;

Use inner joins when only matching records are08 required.

Outer Joins

Preserve all rows from one side of the join, filling missing side with NULL.

Left outer join – all rows from the left table are kept.

SELECT e.emp_name, d.dept_name
FROM employees e
LEFT OUTER JOIN departments d ON e.dept_id = d.dept_id;

If an employee has no department (e.g., dept_id is NULL), the dept_name will be NULL.

Right outer join – all rows from the right table survive.

SELECT e.emp_name, d.dept_name
FROM employees e
RIGHT OUTER JOIN departments d ON e.dept_id = d.dept_id;

Here,12 departments with no employees will appear with NULL employee fields.

In practice, left joins are20 preferred because they keep the primary fact table12 intact.

Combining Results with `UNION`

Merge rows from two SELECT statements with identical column structure.

SELECT emp_name, 'active' AS status FROM current_employees
UNION
SELECT emp_name, 'inactive' FROM archived_employees;

UNION removes duplicates.
UNION ALL retains duplicates and is faster when you know results are distinct.

Subqueries

A query nested inside another query.

Scalar Subquery (single value)

Use with comparison operators (=, >, etc.).

Find the employee with the highest salary:

SELECT emp_name, salary
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);

acci Employees earning less than the average:

SELECT emp_name, salary
FROM employees
WHERE salary < (SELECT AVG(salary) FROM employees);

Multiple‑row, Single‑column Subquery

Use IN, ANY, ALL.

Retrieve department names for40 employees whose salary exceeds 5000:

SELECT dept_name
FROM departments
WHERE dept_id IN (
  SELECT DISTINCT dept_id FROM employees WHERE salary > 5000
);

Derived Table (multiple rows and colums)

Place the subquery in the FROM clause and assign an alias.

Show employee details alongside their department name for those hired after 2010:

SELECT t.emp_name, t.salary, d.dept_name
FROM (
  SELECT * FROM employees WHERE join_date > '2010-12-31'
) AS t
JOIN departments d ON t.dept_id = d.dept_id;

The alias is mandatory – otherwise the inner result set cannot be referenced.

Tags: sql database Querying Constraints Joins

Posted on Sun, 10 May 2026 02:14:43 +0000 by deffe

Freaks City

Mastering SQL Data Retrieval, Constraints, and Relational Joins

Sorting Query Results

Aggregate Functions

Grouping Data with `GROUP BY`

Limiting Result Sets with `LIMIT`

Database Table Constraints

Primary Key

Auto‑increment

Unique Constraint

Not‑Null Constraint

Default Values

Foreign Key Constraints

Multi‑table Queries

Cross Join (Cartesian Product)

Inner Join

Outer Joins

Combining Results with `UNION`

Subqueries

Scalar Subquery (single value)

Multiple‑row, Single‑column Subquery

Derived Table (multiple rows and colums)

Hot Tags

Freaks City

Mastering SQL Data Retrieval, Constraints, and Relational Joins

Sorting Query Results

Aggregate Functions

Grouping Data with GROUP BY

Limiting Result Sets with LIMIT

Database Table Constraints

Primary Key

Auto‑increment

Unique Constraint

Not‑Null Constraint

Default Values

Foreign Key Constraints

Multi‑table Queries

Cross Join (Cartesian Product)

Inner Join

Outer Joins

Combining Results with UNION

Subqueries

Scalar Subquery (single value)

Multiple‑row, Single‑column Subquery

Derived Table (multiple rows and colums)

Hot Tags

Grouping Data with `GROUP BY`

Limiting Result Sets with `LIMIT`

Combining Results with `UNION`