Module - 2

Writing SELECT Queries in PostgreSQL

What is a SELECT Query?
A SELECT statement is used to retrieve data from one or more tables. It is the
most commonly used SQL command — everything from fetching a single value
to complex multi-table reports uses SELECT.

Basic Syntax
SELECT column1, column2, ...
FROM table_name
WHERE condition
ORDER BY column
LIMIT n;

Sample Table for Practice

CREATE TABLE employees (
emp_id SERIAL PRIMARY KEY,
name VARCHAR(100),
dept VARCHAR(50),
salary NUMERIC(10,2),
city VARCHAR(50),
age SMALLINT,
hire_date DATE,
is_active BOOLEAN
);

INSERT INTO employees (name, dept, salary, city, age, hire_

date, is_active)
VALUES
('Alice', 'Engineering', 75000, 'Hyderabad', 28, '2022-01
-15', TRUE),

Module - 2 1
 ('Bob', 'Marketing', 60000, 'Mumbai', 35, '2021-03
-10', TRUE),
('Charlie', 'Engineering', 90000, 'Bangalore', 32, '2020-06
-01', TRUE),
('Diana', 'HR', 50000, 'Hyderabad', 29, '2023-07
-20', FALSE),
('Eve', 'Marketing', 70000, 'Chennai', 31, '2022-11
-05', TRUE),
('Frank', 'Engineering', 85000, 'Bangalore', 40, '2019-08
-15', TRUE),
('Grace', 'HR', 55000, 'Mumbai', 27, '2023-01
-10', FALSE),
('Henry', 'Finance', 95000, 'Hyderabad', 45, '2018-04
-01', TRUE);

1. Select All Columns

SELECT * FROM employees;

emp_id | name | dept | salary | city | a

ge | hire_date | is_active
--------+---------+--------------+----------+-----------+--
---+------------+-----------
1 | Alice | Engineering | 75000.00 | Hyderabad |
28 | 2022-01-15 | t
2 | Bob | Marketing | 60000.00 | Mumbai |
35 | 2021-03-10 | t
...

⚠️ Avoid SELECT * in production — always specify the columns you need.

2. Select Specific Columns

SELECT name, dept, salary
FROM employees;

Module - 2 2
 name | dept | salary
---------+--------------+----------
Alice | Engineering | 75000.00
Bob | Marketing | 60000.00
Charlie | Engineering | 90000.00

3. Column Aliases — AS
Rename columns in output using AS:

SELECT
name AS employee_name,
dept AS department,
salary AS monthly_salary
FROM employees;

-- Alias with spaces (use double quotes)

SELECT
name AS "Employee Name",
salary AS "Monthly Salary"
FROM employees;

4. Expressions in SELECT
-- Calculate annual salary
SELECT
name,
salary AS monthly_salary,
salary * 12 AS annual_salary,
salary * 0.10 AS bonus
FROM employees;

name | monthly_salary | annual_salary | bonus

---------+----------------+---------------+---------

Module - 2 3
 Alice | 75000.00 | 900000.00 | 7500.00
Bob | 60000.00 | 720000.00 | 6000.00

5. WHERE Clause — Filtering Rows

-- Employees in Engineering
SELECT * FROM employees WHERE dept = 'Engineering';

-- Salary greater than 70000

SELECT name, salary FROM employees WHERE salary > 70000;

-- Specific city
SELECT * FROM employees WHERE city = 'Hyderabad';

-- Active employees only

SELECT * FROM employees WHERE is_active = TRUE;
-- Shorthand
SELECT * FROM employees WHERE is_active;

6. Comparison Operators
SELECT * FROM employees WHERE salary = 75000; -- equal
SELECT * FROM employees WHERE salary != 75000; -- not equa
l
SELECT * FROM employees WHERE salary <> 75000; -- not equa
l (same as !=)
SELECT * FROM employees WHERE salary > 70000; -- greater
than
SELECT * FROM employees WHERE salary < 70000; -- less tha
n
SELECT * FROM employees WHERE salary >= 75000; -- greater
or equal
SELECT * FROM employees WHERE salary <= 60000; -- less or
equal

7. Logical Operators — AND, OR, NOT

Module - 2 4
 -- AND — both conditions must be true
SELECT * FROM employees
WHERE dept = 'Engineering' AND salary > 80000;

-- OR — at least one condition must be true

SELECT * FROM employees
WHERE city = 'Hyderabad' OR city = 'Bangalore';

-- NOT — negates a condition

SELECT * FROM employees
WHERE NOT dept = 'HR';

-- Combined
SELECT * FROM employees
WHERE (dept = 'Engineering' OR dept = 'Finance')
AND salary > 80000
AND is_active = TRUE;

8. BETWEEN — Range Filter

-- Salary between 60000 and 80000 (inclusive)
SELECT name, salary FROM employees
WHERE salary BETWEEN 60000 AND 80000;

-- Age between 28 and 35

SELECT name, age FROM employees
WHERE age BETWEEN 28 AND 35;

-- Date range
SELECT name, hire_date FROM employees
WHERE hire_date BETWEEN '2021-01-01' AND '2023-01-01';

-- NOT BETWEEN
SELECT name, salary FROM employees
WHERE salary NOT BETWEEN 60000 AND 80000;

Module - 2 5
 9. IN — Match Multiple Values
-- Employees in specific departments
SELECT * FROM employees
WHERE dept IN ('Engineering', 'Finance');

-- Employees in specific cities

SELECT * FROM employees
WHERE city IN ('Hyderabad', 'Bangalore', 'Chennai');

-- NOT IN
SELECT * FROM employees
WHERE dept NOT IN ('HR', 'Marketing');

10. LIKE — Pattern Matching

-- Names starting with 'A'
SELECT * FROM employees WHERE name LIKE 'A%';

-- Names ending with 'e'

SELECT * FROM employees WHERE name LIKE '%e';

-- Names containing 'ar'

SELECT * FROM employees WHERE name LIKE '%ar%';

-- Names with exactly 3 characters

SELECT * FROM employees WHERE name LIKE '___';

-- Case insensitive (ILIKE)

SELECT * FROM employees WHERE name ILIKE 'alice';
SELECT * FROM employees WHERE city ILIKE 'hy%';

Wildcards: % — matches any sequence of characters (including none)

_ — matches exactly one character

11. IS NULL / IS NOT NULL

Module - 2 6
 -- Employees with no city
SELECT * FROM employees WHERE city IS NULL;

-- Employees who have a city

SELECT * FROM employees WHERE city IS NOT NULL;

12. ORDER BY — Sorting Results

-- Sort by salary ascending (default)
SELECT name, salary FROM employees ORDER BY salary;

-- Sort by salary descending

SELECT name, salary FROM employees ORDER BY salary DESC;

-- Sort by name ascending

SELECT name, dept FROM employees ORDER BY name ASC;

-- Sort by multiple columns

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

-- Sort by column position (2nd column)

SELECT name, salary FROM employees ORDER BY 2 DESC;

13. LIMIT and OFFSET — Pagination

-- Get first 3 rows
SELECT * FROM employees LIMIT 3;

-- Skip first 3 rows, get next 3

SELECT * FROM employees LIMIT 3 OFFSET 3;

-- Top 3 highest paid employees

SELECT name, salary FROM employees
ORDER BY salary DESC
LIMIT 3;

Module - 2 7
 -- Page 2 (page size = 3)
SELECT name, salary FROM employees
ORDER BY emp_id
LIMIT 3 OFFSET 3; -- skip first 3, get next 3

14. DISTINCT — Remove Duplicates

-- Unique departments
SELECT DISTINCT dept FROM employees;

-- Unique cities
SELECT DISTINCT city FROM employees;

-- Unique department + city combinations

SELECT DISTINCT dept, city FROM employees
ORDER BY dept;

15. Aggregate Functions

-- Count all rows
SELECT COUNT(*) FROM employees;

-- Count non-null values

SELECT COUNT(phone) FROM employees;

-- Sum of salaries
SELECT SUM(salary) FROM employees;

-- Average salary
SELECT AVG(salary) FROM employees;
SELECT ROUND(AVG(salary), 2) AS avg_salary FROM employees;

-- Highest salary
SELECT MAX(salary) FROM employees;

-- Lowest salary

Module - 2 8
 SELECT MIN(salary) FROM employees;

-- Multiple aggregates
SELECT
COUNT(*) AS total_employees,
ROUND(AVG(salary),2) AS avg_salary,
MAX(salary) AS highest_salary,
MIN(salary) AS lowest_salary,
SUM(salary) AS total_salary
FROM employees;

16. GROUP BY — Aggregate by Category

-- Count employees per department
SELECT dept, COUNT(*) AS total
FROM employees
GROUP BY dept;

-- Average salary per department

SELECT dept, ROUND(AVG(salary), 2) AS avg_salary
FROM employees
GROUP BY dept
ORDER BY avg_salary DESC;

-- Total salary per city

SELECT city, SUM(salary) AS total_salary
FROM employees
GROUP BY city
ORDER BY total_salary DESC;

17. HAVING — Filter After GROUP BY

-- Departments with more than 1 employee
SELECT dept, COUNT(*) AS total
FROM employees
GROUP BY dept
HAVING COUNT(*) > 1;

Module - 2 9
 -- Departments where avg salary > 70000
SELECT dept, ROUND(AVG(salary),2) AS avg_salary
FROM employees
GROUP BY dept
HAVING AVG(salary) > 70000;

WHERE vs HAVING:
WHERE HAVING
Filters Individual rows Grouped results
Used with Any query GROUP BY only
Aggregates ❌ Not allowed ✅ Allowed
18. Full SELECT Clause Order
This is the correct order of clauses in a SELECT statement:

SELECT columns / expressions -- 5. what to show

FROM table -- 1. where to get dat
a
WHERE row conditions -- 2. filter rows
GROUP BY columns -- 3. group rows
HAVING group conditions -- 4. filter groups
ORDER BY columns -- 6. sort results
LIMIT n -- 7. limit rows
OFFSET n; -- 8. skip rows

-- Full example using all clauses

SELECT
dept,
COUNT(*) AS total_employees,
ROUND(AVG(salary),2) AS avg_salary,
MAX(salary) AS highest_salary
FROM employees
WHERE is_active = TRUE
GROUP BY dept
HAVING COUNT(*) > 1

Module - 2 10
 ORDER BY avg_salary DESC
LIMIT 5
OFFSET 0;

19. CASE — Conditional Output

-- Label salary ranges
SELECT
name,
salary,
CASE
WHEN salary >= 90000 THEN 'High'
WHEN salary >= 70000 THEN 'Medium'
ELSE 'Low'
END AS salary_band
FROM employees;

-- Label active status

SELECT
name,
CASE is_active
WHEN TRUE THEN 'Active'
WHEN FALSE THEN 'Inactive'
END AS status
FROM employees;

20. Useful String Functions in SELECT

-- Uppercase / Lowercase
SELECT UPPER(name), LOWER(dept) FROM employees;

-- Concatenation
SELECT name || ' works in ' || dept AS description FROM emp
loyees;

-- CONCAT function
SELECT CONCAT(name, ' - ', city) AS info FROM employees;

Module - 2 11
 -- Length of name
SELECT name, LENGTH(name) AS name_length FROM employees;

-- Trim whitespace
SELECT TRIM(name) FROM employees;

-- Substring
SELECT SUBSTRING(name, 1, 3) AS short_name FROM employees;

Best Practices
Always specify column names instead of SELECT * in production — it is faster
and more readable.
Use aliases to make output column names clear and readable.
Always use ORDER BY with LIMIT — without ORDER BY, results are
unpredictable.
Use WHERE to filter before grouping and HAVING to filter after grouping.
Use DISTINCT sparingly — it can be slow on large tables. Consider if your data
model needs fixing instead.
Always use IS NULL / IS NOT NULL — never = NULL .
Use ILIKE instead of LIKE for case-insensitive searches in PostgreSQL.

WHERE Clause in PostgreSQL

What is WHERE Clause?
The WHERE clause is used to filter rows from a table based on one or more
conditions. Only rows that satisfy the condition are returned — all other rows
are ignored.

SELECT columns
FROM table
WHERE condition;

Module - 2 12
 Think of it like a filter or sieve — data passes through only if it meets the
condition.

Sample Table for Practice

CREATE TABLE employees (
emp_id SERIAL PRIMARY KEY,
name VARCHAR(100),
dept VARCHAR(50),
salary NUMERIC(10,2),
city VARCHAR(50),
age SMALLINT,
hire_date DATE,
is_active BOOLEAN
);

INSERT INTO employees (name, dept, salary, city, age, hire_

date, is_active)
VALUES
('Alice', 'Engineering', 75000, 'Hyderabad', 28, '2022-01
-15', TRUE),
('Bob', 'Marketing', 60000, 'Mumbai', 35, '2021-03
-10', TRUE),
('Charlie', 'Engineering', 90000, 'Bangalore', 32, '2020-06
-01', TRUE),
('Diana', 'HR', 50000, 'Hyderabad', 29, '2023-07
-20', FALSE),
('Eve', 'Marketing', 70000, 'Chennai', 31, '2022-11
-05', TRUE),
('Frank', 'Engineering', 85000, 'Bangalore', 40, '2019-08
-15', TRUE),
('Grace', 'HR', 55000, 'Mumbai', 27, '2023-01
-10', FALSE),
('Henry', 'Finance', 95000, 'Hyderabad', 45, '2018-04
-01', TRUE);

1. Basic Comparison Operators

Module - 2 13
 -- Equal to
SELECT * FROM employees WHERE dept = 'Engineering';

-- Not equal to
SELECT * FROM employees WHERE dept != 'HR';
SELECT * FROM employees WHERE dept <> 'HR'; -- same as !=

-- Greater than
SELECT * FROM employees WHERE salary > 70000;

-- Less than
SELECT * FROM employees WHERE salary < 70000;

-- Greater than or equal

SELECT * FROM employees WHERE salary >= 75000;

-- Less than or equal

SELECT * FROM employees WHERE age <= 30;

2. AND Operator
Both conditions must be TRUE:

-- Engineering employees with salary > 80000

SELECT name, dept, salary
FROM employees
WHERE dept = 'Engineering' AND salary > 80000;

name | dept | salary

---------+--------------+----------
Charlie | Engineering | 90000.00
Frank | Engineering | 85000.00

-- Active employees from Hyderabad

SELECT name, city, is_active
FROM employees

Module - 2 14
 WHERE city = 'Hyderabad' AND is_active = TRUE;

-- Age between 28 and 35 in Engineering

SELECT name, age, dept
FROM employees
WHERE age >= 28 AND age <= 35 AND dept = 'Engineering';

3. OR Operator
At least one condition must be TRUE:

-- Employees from Hyderabad or Bangalore

SELECT name, city
FROM employees
WHERE city = 'Hyderabad' OR city = 'Bangalore';

name | city
---------+-----------
Alice | Hyderabad
Charlie | Bangalore
Diana | Hyderabad
Frank | Bangalore
Henry | Hyderabad

-- HR or Finance employees
SELECT name, dept
FROM employees
WHERE dept = 'HR' OR dept = 'Finance';

-- Salary less than 60000 or greater than 90000

SELECT name, salary
FROM employees
WHERE salary < 60000 OR salary > 90000;

4. NOT Operator
Negates a condition:

Module - 2 15
 -- Not in Engineering
SELECT name, dept
FROM employees
WHERE NOT dept = 'Engineering';

-- Not active
SELECT name, is_active
FROM employees
WHERE NOT is_active;

-- Not from Mumbai

SELECT name, city
FROM employees
WHERE NOT city = 'Mumbai';

5. Combining AND, OR, NOT

Use parentheses to control evaluation order:

-- Engineering or Finance AND salary > 80000

-- Without parentheses (wrong logic)
SELECT name, dept, salary FROM employees
WHERE dept = 'Engineering' OR dept = 'Finance' AND salary >
80000;
-- AND is evaluated first → gives wrong results

-- With parentheses (correct logic)

SELECT name, dept, salary FROM employees
WHERE (dept = 'Engineering' OR dept = 'Finance') AND salary
> 80000;

name | dept | salary

---------+--------------+----------
Charlie | Engineering | 90000.00
Frank | Engineering | 85000.00
Henry | Finance | 95000.00

Module - 2 16
 -- Active employees in Engineering or Finance with salary >
70000
SELECT name, dept, salary, is_active
FROM employees
WHERE is_active = TRUE
AND (dept = 'Engineering' OR dept = 'Finance')
AND salary > 70000;

6. BETWEEN — Range Filter

-- Salary between 60000 and 80000 (inclusive)
SELECT name, salary
FROM employees
WHERE salary BETWEEN 60000 AND 80000;

name | salary
-------+----------
Alice | 75000.00
Bob | 60000.00
Eve | 70000.00

-- Age between 28 and 35

SELECT name, age
FROM employees
WHERE age BETWEEN 28 AND 35;

-- Hired between two dates

SELECT name, hire_date
FROM employees
WHERE hire_date BETWEEN '2021-01-01' AND '2023-01-01';

-- NOT BETWEEN
SELECT name, salary
FROM employees
WHERE salary NOT BETWEEN 60000 AND 80000;

Module - 2 17
 7. IN — Match a List of Values
-- Employees in specific departments
SELECT name, dept
FROM employees
WHERE dept IN ('Engineering', 'Finance', 'HR');

name | dept
---------+--------------
Alice | Engineering
Charlie | Engineering
Diana | HR
Frank | Engineering
Grace | HR
Henry | Finance

-- Employees in specific cities

SELECT name, city
FROM employees
WHERE city IN ('Hyderabad', 'Chennai');

-- NOT IN
SELECT name, dept
FROM employees
WHERE dept NOT IN ('HR', 'Marketing');

-- IN with numbers
SELECT name, emp_id
FROM employees
WHERE emp_id IN (1, 3, 5, 7);

8. LIKE — Pattern Matching

-- Names starting with 'A'
SELECT name FROM employees WHERE name LIKE 'A%';

Module - 2 18
 -- Names ending with 'e'
SELECT name FROM employees WHERE name LIKE '%e';

-- Names containing 'ar'

SELECT name FROM employees WHERE name LIKE '%ar%';

-- Exactly 3 characters
SELECT name FROM employees WHERE name LIKE '___';

-- Second character is 'r'

SELECT name FROM employees WHERE name LIKE '_r%';

Wildcard Characters:
Symbol Meaning Example
% Zero or more characters 'A%' → Alice, Anna
_ Exactly one character 'A__' → Amy, Ann

-- ILIKE — case insensitive matching

SELECT * FROM employees WHERE name ILIKE 'alice';
SELECT * FROM employees WHERE city ILIKE 'hy%';

-- NOT LIKE
SELECT * FROM employees WHERE name NOT LIKE 'A%';

9. IS NULL / IS NOT NULL

-- Employees with no phone (NULL)
SELECT name FROM employees WHERE phone IS NULL;

-- Employees who have a phone

SELECT name FROM employees WHERE phone IS NOT NULL;

-- Never use = NULL (always wrong!)

SELECT name FROM employees WHERE phone = NULL; -- ❌ retu
rns nothing

Module - 2 19
 SELECT name FROM employees WHERE phone IS NULL; -- ✅ corr
ect

10. WHERE with Dates

-- Hired in specific year
SELECT name, hire_date
FROM employees
WHERE EXTRACT(YEAR FROM hire_date) = 2022;

-- Hired before 2021

SELECT name, hire_date
FROM employees
WHERE hire_date < '2021-01-01';

-- Hired in last 2 years

SELECT name, hire_date
FROM employees
WHERE hire_date >= CURRENT_DATE - INTERVAL '2 years';

-- Hired in specific month

SELECT name, hire_date
FROM employees
WHERE EXTRACT(MONTH FROM hire_date) = 1; -- January hires

11. WHERE with Boolean

-- Active employees
SELECT name FROM employees WHERE is_active = TRUE;
SELECT name FROM employees WHERE is_active; -- short
hand

-- Inactive employees
SELECT name FROM employees WHERE is_active = FALSE;
SELECT name FROM employees WHERE NOT is_active; -- short
hand

Module - 2 20
 12. WHERE with String Functions
-- Case insensitive exact match
SELECT * FROM employees
WHERE LOWER(name) = 'alice';

SELECT * FROM employees

WHERE UPPER(dept) = 'ENGINEERING';

-- Filter by name length

SELECT name FROM employees
WHERE LENGTH(name) > 5;

-- Filter by first character

SELECT name FROM employees
WHERE SUBSTRING(name, 1, 1) = 'A';

13. WHERE with Expressions

-- Annual salary greater than 900000
SELECT name, salary
FROM employees
WHERE salary * 12 > 900000;

-- Employees whose age + years of experience > 50

SELECT name, age
FROM employees
WHERE age + EXTRACT(YEAR FROM AGE(CURRENT_DATE, hire_date))
> 50;

14. WHERE with Subquery

-- Employees earning more than average salary
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

Module - 2 21
 -- Employees in departments that have more than 2 people
SELECT name, dept
FROM employees
WHERE dept IN (
SELECT dept
FROM employees
GROUP BY dept
HAVING COUNT(*) > 2
);

Operator Precedence (Evaluation Order)

When mixing AND, OR, NOT — PostgreSQL evaluates in this order:

1. NOT (highest priority)

2. AND
3. OR (lowest priority)

-- This:
WHERE dept = 'HR' OR dept = 'Finance' AND salary > 80000

-- Is evaluated as:
WHERE dept = 'HR' OR (dept = 'Finance' AND salary > 80000)
-- AND is evaluated first!

-- Use parentheses to be explicit and safe:

WHERE (dept = 'HR' OR dept = 'Finance') AND salary > 80000

WHERE Clause Execution Order

It is important to understand when WHERE is executed:

SELECT -- 5. select columns

FROM -- 1. get table data
WHERE -- 2. filter rows ← runs here
GROUP BY -- 3. group results
HAVING -- 4. filter groups

Module - 2 22
 ORDER BY -- 6. sort results
LIMIT -- 7. limit rows

WHERE runs before GROUP BY — so you cannot use aggregate functions in

WHERE:

-- WRONG ❌ — cannot use aggregate in WHERE

SELECT dept, AVG(salary)
FROM employees
WHERE AVG(salary) > 70000
GROUP BY dept;

-- CORRECT ✅
— use HAVING for aggregates
SELECT dept, AVG(salary)
FROM employees
GROUP BY dept
HAVING AVG(salary) > 70000;

Quick Reference
Operator Description Example
= Equal salary = 75000

!= / <> Not equal dept != 'HR'

> Greater than age > 30

< Less than age < 30

>= Greater or equal salary >= 70000

<= Less or equal salary <= 70000

AND Both true dept = 'IT' AND age > 25

OR Either true city = 'Mumbai' OR city = 'Delhi'

NOT Negate NOT is_active

BETWEEN Range salary BETWEEN 50000 AND 80000

IN Match list dept IN ('HR', 'IT')

LIKE Pattern name LIKE 'A%'

ILIKE Case-insensitive pattern name ILIKE 'alice'

IS NULL Check null phone IS NULL

Module - 2 23
 Operator Description Example
IS NOT NULL Check not null phone IS NOT NULL

Best Practices
Always use parentheses when combining AND and OR — never rely on
operator precedence.
Use IN instead of multiple OR conditions — it is cleaner and faster.
Use BETWEEN for range conditions — more readable than >= AND <= .
Always use IS NULL and IS NOT NULL — never = NULL .
Use ILIKE for case-insensitive searches instead of converting with
UPPER/LOWER.
Put the most selective condition first in AND chains — PostgreSQL can short-
circuit evaluation.
Never use aggregate functions in WHERE — use HAVING instead.

Comparison and Logical Operators in PostgreSQL

What are Operators?
Operators are special symbols or keywords used to perform operations on
values in SQL. They are the building blocks of conditions in WHERE, HAVING,
CASE, and CHECK clauses.

salary > 50000 -- > is a comparison operator

dept = 'HR' AND age > 30 -- AND is a logical operator

Sample Table for Practice

CREATE TABLE employees (
emp_id SERIAL PRIMARY KEY,
name VARCHAR(100),
dept VARCHAR(50),
salary NUMERIC(10,2),
city VARCHAR(50),

Module - 2 24
 age SMALLINT,
hire_date DATE,
is_active BOOLEAN
);

INSERT INTO employees (name, dept, salary, city, age, hire_

date, is_active)
VALUES
('Alice', 'Engineering', 75000, 'Hyderabad', 28, '2022-01
-15', TRUE),
('Bob', 'Marketing', 60000, 'Mumbai', 35, '2021-03
-10', TRUE),
('Charlie', 'Engineering', 90000, 'Bangalore', 32, '2020-06
-01', TRUE),
('Diana', 'HR', 50000, 'Hyderabad', 29, '2023-07
-20', FALSE),
('Eve', 'Marketing', 70000, 'Chennai', 31, '2022-11
-05', TRUE),
('Frank', 'Engineering', 85000, 'Bangalore', 40, '2019-08
-15', TRUE),
('Grace', 'HR', 55000, 'Mumbai', 27, '2023-01
-10', FALSE),
('Henry', 'Finance', 95000, 'Hyderabad', 45, '2018-04
-01', TRUE);

PART 1 — Comparison Operators

Comparison operators compare two values and return TRUE, FALSE, or NULL.

1. Equal To ( = )
-- Exact match
SELECT * FROM employees WHERE dept = 'Engineering';

SELECT * FROM employees WHERE salary = 75000;

SELECT * FROM employees WHERE city = 'Hyderabad';

Module - 2 25
 name | dept | salary
---------+--------------+----------
Alice | Engineering | 75000.00
Charlie | Engineering | 90000.00
Frank | Engineering | 85000.00

2. Not Equal To ( != or <> )

-- Both are identical in PostgreSQL
SELECT * FROM employees WHERE dept != 'HR';

SELECT * FROM employees WHERE dept <> 'HR';

SELECT * FROM employees WHERE salary != 75000;

3. Greater Than ( > )

SELECT name, salary FROM employees WHERE salary > 80000;

name | salary
---------+----------
Charlie | 90000.00
Frank | 85000.00
Henry | 95000.00

-- Age greater than 35

SELECT name, age FROM employees WHERE age > 35;

-- Hire date greater than (after) a date

SELECT name, hire_date FROM employees
WHERE hire_date > '2022-01-01';

4. Less Than ( < )

Module - 2 26
 SELECT name, salary FROM employees WHERE salary < 60000;

SELECT name, age FROM employees WHERE age < 30;

5. Greater Than or Equal To ( >= )

SELECT name, salary FROM employees WHERE salary >= 75000;

SELECT name, age FROM employees WHERE age >= 32;

6. Less Than or Equal To ( <= )

SELECT name, salary FROM employees WHERE salary <= 60000;

SELECT name, age FROM employees WHERE age <= 29;

7. BETWEEN — Range Comparison

-- Inclusive on both ends
SELECT name, salary FROM employees
WHERE salary BETWEEN 60000 AND 80000;

-- Age range
SELECT name, age FROM employees
WHERE age BETWEEN 28 AND 35;

-- Date range
SELECT name, hire_date FROM employees
WHERE hire_date BETWEEN '2021-01-01' AND '2023-12-31';

-- NOT BETWEEN
SELECT name, salary FROM employees
WHERE salary NOT BETWEEN 60000 AND 80000;

BETWEEN a AND b is equivalent to >= a AND <= b

Module - 2 27
 8. IN — List Comparison
-- Match any value in the list
SELECT name, dept FROM employees
WHERE dept IN ('Engineering', 'Finance');

-- City filter
SELECT name, city FROM employees
WHERE city IN ('Hyderabad', 'Bangalore');

-- NOT IN
SELECT name, dept FROM employees
WHERE dept NOT IN ('HR', 'Marketing');

-- IN with numbers
SELECT name, emp_id FROM employees
WHERE emp_id IN (1, 3, 5, 7);

9. LIKE / ILIKE — Pattern Comparison

-- Starts with A
SELECT name FROM employees WHERE name LIKE 'A%';

-- Ends with e
SELECT name FROM employees WHERE name LIKE '%e';

-- Contains 'ar'
SELECT name FROM employees WHERE name LIKE '%ar%';

-- ILIKE — case insensitive

SELECT name FROM employees WHERE name ILIKE 'alice';
SELECT city FROM employees WHERE city ILIKE 'hy%';

-- NOT LIKE
SELECT name FROM employees WHERE name NOT LIKE 'A%';

10. IS NULL / IS NOT NULL

Module - 2 28
 -- Check for NULL
SELECT * FROM employees WHERE phone IS NULL;

-- Check for non-NULL

SELECT * FROM employees WHERE phone IS NOT NULL;

-- NEVER use = NULL

SELECT * FROM employees WHERE phone = NULL; -- ❌ always
returns nothing

11. IS DISTINCT FROM / IS NOT DISTINCT FROM

NULL-safe comparison operators:

-- Normal = fails with NULL

SELECT NULL = NULL; -- NULL (not TRUE!)

-- IS NOT DISTINCT FROM is NULL safe

SELECT NULL IS NOT DISTINCT FROM NULL; -- TRUE ✅
-- Practical use
SELECT * FROM employees
WHERE phone IS NOT DISTINCT FROM NULL; -- same as IS NULL
but NULL-safe

-- IS DISTINCT FROM
SELECT * FROM employees
WHERE dept IS DISTINCT FROM 'Engineering';
-- Returns rows where dept != 'Engineering' AND where dept
IS NULL

Expression Result
1 = 1 TRUE
1 = NULL NULL
NULL = NULL NULL
1 IS NOT DISTINCT FROM 1 TRUE

Module - 2 29
 Expression Result
1 IS NOT DISTINCT FROM NULL FALSE
NULL IS NOT DISTINCT FROM NULL TRUE

Comparison Operators Summary

Operator Description Example
= Equal salary = 75000

!= or <> Not equal dept != 'HR'

> Greater than age > 30

< Less than age < 30

>= Greater or equal salary >= 70000

<= Less or equal salary <= 60000

BETWEEN Inclusive range salary BETWEEN 50000 AND 80000

IN Match in list dept IN ('HR','IT')

NOT IN Not in list dept NOT IN ('HR')

LIKE Pattern match name LIKE 'A%'

ILIKE Case-insensitive name ILIKE 'alice'

IS NULL Is empty phone IS NULL

IS NOT NULL Is not empty phone IS NOT NULL

IS DISTINCT FROM NULL-safe != dept IS DISTINCT FROM 'HR'

IS NOT DISTINCT FROM NULL-safe = dept IS NOT DISTINCT FROM 'HR'

PART 2 — Logical Operators

Logical operators combine multiple conditions and return TRUE, FALSE, or
NULL.

1. AND Operator
Both conditions must be TRUE:

-- Engineering employees with salary > 80000

SELECT name, dept, salary FROM employees

Module - 2 30
 WHERE dept = 'Engineering' AND salary > 80000;

name | dept | salary

---------+--------------+----------
Charlie | Engineering | 90000.00
Frank | Engineering | 85000.00

-- Multiple AND conditions

SELECT name, dept, city, salary FROM employees
WHERE dept = 'Engineering'
AND city = 'Bangalore'
AND salary > 80000;

-- Active employees from Hyderabad with age < 35

SELECT name, city, age FROM employees
WHERE is_active = TRUE
AND city = 'Hyderabad'
AND age < 35;

AND Truth Table:

A B A AND B
TRUE TRUE TRUE
TRUE FALSE FALSE
FALSE TRUE FALSE
FALSE FALSE FALSE
TRUE NULL NULL
FALSE NULL FALSE
NULL NULL NULL

2. OR Operator
At least one condition must be TRUE:

-- Employees from Hyderabad or Bangalore

SELECT name, city FROM employees

Module - 2 31
 WHERE city = 'Hyderabad' OR city = 'Bangalore';

name | city
---------+-----------
Alice | Hyderabad
Charlie | Bangalore
Diana | Hyderabad
Frank | Bangalore
Henry | Hyderabad

-- HR or Finance department
SELECT name, dept FROM employees
WHERE dept = 'HR' OR dept = 'Finance';

-- Low or high salary

SELECT name, salary FROM employees
WHERE salary < 55000 OR salary > 90000;

OR Truth Table:
A B A OR B
TRUE TRUE TRUE
TRUE FALSE TRUE
FALSE TRUE TRUE
FALSE FALSE FALSE
TRUE NULL TRUE
FALSE NULL NULL
NULL NULL NULL

3. NOT Operator
Negates / reverses a condition:

-- Not in Engineering
SELECT name, dept FROM employees
WHERE NOT dept = 'Engineering';

Module - 2 32
 -- Not active
SELECT name, is_active FROM employees
WHERE NOT is_active;

-- Not in Hyderabad or Mumbai

SELECT name, city FROM employees
WHERE NOT city IN ('Hyderabad', 'Mumbai');

-- NOT with LIKE

SELECT name FROM employees
WHERE name NOT LIKE 'A%';

-- NOT with BETWEEN

SELECT name, salary FROM employees
WHERE salary NOT BETWEEN 60000 AND 80000;

NOT Truth Table:

A NOT A
TRUE FALSE
FALSE TRUE
NULL NULL

4. Combining AND, OR, NOT

-- Engineering or Finance with salary > 80000
SELECT name, dept, salary FROM employees
WHERE (dept = 'Engineering' OR dept = 'Finance')
AND salary > 80000;

name | dept | salary

---------+--------------+----------
Charlie | Engineering | 90000.00
Frank | Engineering | 85000.00
Henry | Finance | 95000.00

Module - 2 33
 -- Active employees NOT in HR with salary >= 70000
SELECT name, dept, salary, is_active FROM employees
WHERE is_active = TRUE
AND NOT dept = 'HR'
AND salary >= 70000;

-- Complex condition
SELECT name, dept, city, salary FROM employees
WHERE (city = 'Hyderabad' OR city = 'Bangalore')
AND (dept = 'Engineering' OR dept = 'Finance')
AND salary > 70000
AND is_active = TRUE;

5. Operator Precedence
When mixing operators PostgreSQL evaluates in this order:

1. NOT → evaluated first

2. AND → evaluated second
3. OR → evaluated last

-- Ambiguous — relies on precedence

WHERE dept = 'HR' OR dept = 'Finance' AND salary > 80000

-- PostgreSQL reads this as:

WHERE dept = 'HR' OR (dept = 'Finance' AND salary > 80000)
-- Returns ALL HR employees + Finance employees with salary
> 80000

-- What you probably wanted:

WHERE (dept = 'HR' OR dept = 'Finance') AND salary > 80000
-- Returns only HR and Finance employees with salary > 8000
0

⚠️ Always use parentheses — never rely on operator precedence. It avoids

bugs and improves readability.

Module - 2 34
 6. NULL Behavior with Logical Operators
NULL behaves unexpectedly in logical operations — this is called three-valued
logic (TRUE, FALSE, NULL):

-- NULL in AND
SELECT TRUE AND NULL; -- NULL
SELECT FALSE AND NULL; -- FALSE (short-circuit)
SELECT NULL AND NULL; -- NULL

-- NULL in OR
SELECT TRUE OR NULL; -- TRUE (short-circuit)
SELECT FALSE OR NULL; -- NULL
SELECT NULL OR NULL; -- NULL

-- NULL in NOT
SELECT NOT NULL; -- NULL

-- NULL in comparison
SELECT NULL = NULL; -- NULL (not TRUE!)
SELECT NULL != NULL; -- NULL
SELECT NULL > 5; -- NULL

This is why filters on nullable columns can return unexpected results:

-- Employees NOT in Engineering

-- This will MISS rows where dept IS NULL
SELECT * FROM employees WHERE dept != 'Engineering';

-- Correct way — handle NULLs explicitly

SELECT * FROM employees
WHERE dept != 'Engineering' OR dept IS NULL;

7. ALL Practical Combinations

-- 1. Simple AND
SELECT * FROM employees
WHERE dept = 'Engineering' AND salary > 75000;

Module - 2 35
 -- 2. Simple OR
SELECT * FROM employees
WHERE dept = 'HR' OR dept = 'Finance';

-- 3. AND + OR with parentheses

SELECT * FROM employees
WHERE (dept = 'Engineering' OR dept = 'Marketing')
AND salary BETWEEN 60000 AND 80000;

-- 4. NOT + IN
SELECT * FROM employees
WHERE dept NOT IN ('HR', 'Marketing')
AND is_active = TRUE;

-- 5. LIKE + AND
SELECT * FROM employees
WHERE name LIKE 'A%'
OR name LIKE 'E%'
AND salary > 60000;

-- 6. Date + AND
SELECT * FROM employees
WHERE hire_date >= '2022-01-01'
AND is_active = TRUE
AND salary > 70000;

-- 7. NULL check + AND

SELECT * FROM employees
WHERE phone IS NOT NULL
AND city = 'Hyderabad';

-- 8. BETWEEN + OR
SELECT * FROM employees
WHERE salary BETWEEN 70000 AND 90000
OR age < 29;

-- 9. Complex real-world filter

Module - 2 36
 SELECT name, dept, salary, city
FROM employees
WHERE is_active = TRUE
AND dept IN ('Engineering', 'Finance')
AND salary >= 75000
AND city != 'Mumbai'
AND hire_date BETWEEN '2018-01-01' AND '2023-01-01'
ORDER BY salary DESC;

Logical Operators Summary

Operator Returns TRUE when Example
AND Both sides are TRUE age > 25 AND salary > 50000

OR At least one side is TRUE city = 'Delhi' OR city = 'Mumbai'

NOT Condition is FALSE NOT is_active

Best Practices
Always use parentheses when mixing AND and OR — never rely on
precedence.
Use IN instead of multiple OR conditions — cleaner and more readable.

-- Instead of this
WHERE city = 'Mumbai' OR city = 'Delhi' OR city = 'Chennai'

-- Use this
WHERE city IN ('Mumbai', 'Delhi', 'Chennai')

Always handle NULL explicitly in nullable columns — use IS NULL / IS NOT

NULL.
Use BETWEEN instead of >= AND <= for range conditions — more readable.
Put the most restrictive condition first in AND chains — helps performance.
Use NOT IN carefully with nullable columns — if the list contains NULL, NOT IN
returns no rows.

Module - 2 37
 -- Dangerous if dept can be NULL
WHERE dept NOT IN ('HR', NULL) -- returns nothing!

-- Safe approach
WHERE dept NOT IN ('HR') AND dept IS NOT NULL

ORDER BY in PostgreSQL — Sorting Results

What is ORDER BY?
ORDER BY is used to sort the result set of a query in either ascending or
descending order based on one or more columns. Without ORDER BY,
PostgreSQL returns rows in an unpredictable order — never assume a default
order.

SELECT columns
FROM table
WHERE condition
ORDER BY column [ASC | DESC];

Sample Table for Practice

CREATE TABLE employees (
emp_id SERIAL PRIMARY KEY,
name VARCHAR(100),
dept VARCHAR(50),
salary NUMERIC(10,2),
city VARCHAR(50),
age SMALLINT,
hire_date DATE,
is_active BOOLEAN
);

INSERT INTO employees (name, dept, salary, city, age, hire_

date, is_active)
VALUES

Module - 2 38
 ('Alice', 'Engineering', 75000, 'Hyderabad', 28, '2022-01
-15', TRUE),
('Bob', 'Marketing', 60000, 'Mumbai', 35, '2021-03
-10', TRUE),
('Charlie', 'Engineering', 90000, 'Bangalore', 32, '2020-06
-01', TRUE),
('Diana', 'HR', 50000, 'Hyderabad', 29, '2023-07
-20', FALSE),
('Eve', 'Marketing', 70000, 'Chennai', 31, '2022-11
-05', TRUE),
('Frank', 'Engineering', 85000, 'Bangalore', 40, '2019-08
-15', TRUE),
('Grace', 'HR', 55000, 'Mumbai', 27, '2023-01
-10', FALSE),
('Henry', 'Finance', 95000, 'Hyderabad', 45, '2018-04
-01', TRUE);

1. ASC — Ascending Order (Default)

Ascending means smallest to largest, A to Z, oldest to newest:

-- Sort by salary ascending (lowest first)

SELECT name, salary
FROM employees
ORDER BY salary ASC;

name | salary
---------+----------
Diana | 50000.00
Grace | 55000.00
Bob | 60000.00
Eve | 70000.00
Alice | 75000.00
Frank | 85000.00
Charlie | 90000.00
Henry | 95000.00

Module - 2 39
 -- ASC is default — same result without keyword
SELECT name, salary
FROM employees
ORDER BY salary;

-- Sort by name A to Z
SELECT name, dept
FROM employees
ORDER BY name ASC;

-- Sort by hire_date oldest first

SELECT name, hire_date
FROM employees
ORDER BY hire_date ASC;

2. DESC — Descending Order

Descending means largest to smallest, Z to A, newest to oldest:

-- Sort by salary descending (highest first)

SELECT name, salary
FROM employees
ORDER BY salary DESC;

name | salary
---------+----------
Henry | 95000.00
Charlie | 90000.00
Frank | 85000.00
Alice | 75000.00
Eve | 70000.00
Bob | 60000.00
Grace | 55000.00
Diana | 50000.00

Module - 2 40
 -- Sort by name Z to A
SELECT name, dept
FROM employees
ORDER BY name DESC;

-- Sort by hire_date newest first

SELECT name, hire_date
FROM employees
ORDER BY hire_date DESC;

-- Sort by age oldest first

SELECT name, age
FROM employees
ORDER BY age DESC;

3. Sorting by Multiple Columns

You can sort by more than one column — the second column is used as a
tiebreaker when the first column has equal values:

-- Sort by dept A-Z, then by salary highest first within ea

ch dept
SELECT name, dept, salary
FROM employees
ORDER BY dept ASC, salary DESC;

name | dept | salary

---------+--------------+----------
Charlie | Engineering | 90000.00
Frank | Engineering | 85000.00
Alice | Engineering | 75000.00
Henry | Finance | 95000.00
Grace | HR | 55000.00
Diana | HR | 50000.00
Eve | Marketing | 70000.00
Bob | Marketing | 60000.00

Module - 2 41
 -- Sort by city A-Z, then name A-Z
SELECT name, city, salary
FROM employees
ORDER BY city ASC, name ASC;

-- Sort by dept A-Z, then age youngest first

SELECT name, dept, age
FROM employees
ORDER BY dept ASC, age ASC;

-- Three column sort

SELECT name, dept, city, salary
FROM employees
ORDER BY dept ASC, city ASC, salary DESC;

4. Sorting by Column Position

Instead of column name, use its position number in SELECT list:

-- Sort by 2nd column (salary)

SELECT name, salary, dept
FROM employees
ORDER BY 2 DESC;

-- Sort by 3rd column (dept) then 2nd column (salary)

SELECT name, salary, dept
FROM employees
ORDER BY 3 ASC, 2 DESC;

⚠️ Column position sorting is convenient but fragile — if you reorder SELECT

columns, sorting breaks. Prefer using column names.

5. Sorting by Alias
You can sort by a column alias defined in SELECT:

-- Sort by alias annual_salary

SELECT

Module - 2 42
 name,
salary * 12 AS annual_salary
FROM employees
ORDER BY annual_salary DESC;

name | annual_salary
---------+--------------
Henry | 1140000.00
Charlie | 1080000.00
Frank | 1020000.00
Alice | 900000.00
Eve | 840000.00
Bob | 720000.00
Grace | 660000.00
Diana | 600000.00

-- Sort by expression alias

SELECT
name,
dept,
salary,
CASE
WHEN salary >= 90000 THEN 'High'
WHEN salary >= 70000 THEN 'Medium'
ELSE 'Low'
END AS salary_band
FROM employees
ORDER BY salary_band ASC, salary DESC;

6. Sorting by Expression
Sort directly by a calculated expression:

-- Sort by annual salary without alias

SELECT name, salary
FROM employees
ORDER BY salary * 12 DESC;

Module - 2 43
 -- Sort by name length (shortest name first)
SELECT name
FROM employees
ORDER BY LENGTH(name) ASC;

name
---------
Bob
Eve
Alice
Diana
Frank
Grace
Henry
Charlie

-- Sort by last character of name

SELECT name
FROM employees
ORDER BY RIGHT(name, 1) ASC;

-- Sort by extracted year of hire

SELECT name, hire_date
FROM employees
ORDER BY EXTRACT(YEAR FROM hire_date) DESC;

7. Sorting with WHERE

ORDER BY always comes after WHERE:

-- Active employees sorted by salary desc

SELECT name, salary, is_active
FROM employees
WHERE is_active = TRUE
ORDER BY salary DESC;

Module - 2 44
 -- Engineering dept sorted by age
SELECT name, dept, age
FROM employees
WHERE dept = 'Engineering'
ORDER BY age ASC;

-- Salary range sorted by name

SELECT name, salary
FROM employees
WHERE salary BETWEEN 60000 AND 90000
ORDER BY name ASC;

8. Sorting with LIMIT

Use ORDER BY with LIMIT to get top N records:

-- Top 3 highest paid employees

SELECT name, salary
FROM employees
ORDER BY salary DESC
LIMIT 3;

name | salary
---------+----------
Henry | 95000.00
Charlie | 90000.00
Frank | 85000.00

-- Bottom 3 lowest paid employees

SELECT name, salary
FROM employees
ORDER BY salary ASC
LIMIT 3;

-- Top 3 oldest employees

SELECT name, age
FROM employees

Module - 2 45
 ORDER BY age DESC
LIMIT 3;

-- Most recently hired employee

SELECT name, hire_date
FROM employees
ORDER BY hire_date DESC
LIMIT 1;

-- Second highest salary

SELECT name, salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;

9. Sorting NULL Values

By default in PostgreSQL:
ASC — NULLs appear LAST
DESC — NULLs appear FIRST
You can control this with NULLS FIRST or NULLS LAST:

-- NULLs at the end in descending order (override default)

SELECT name, phone
FROM employees
ORDER BY phone DESC NULLS LAST;

-- NULLs at the beginning in ascending order (override defa

ult)
SELECT name, phone
FROM employees
ORDER BY phone ASC NULLS FIRST;

-- Practical use — show employees with salary, NULLs at bot

tom
SELECT name, salary

Module - 2 46
 FROM employees
ORDER BY salary DESC NULLS LAST;

10. Sorting by CASE — Custom Order

Sort in a custom defined order using CASE:

-- Custom department order (not alphabetical)

SELECT name, dept
FROM employees
ORDER BY
CASE dept
WHEN 'Engineering' THEN 1
WHEN 'Finance' THEN 2
WHEN 'Marketing' THEN 3
WHEN 'HR' THEN 4
ELSE 5
END ASC;

name | dept
---------+--------------
Alice | Engineering
Charlie | Engineering
Frank | Engineering
Henry | Finance
Bob | Marketing
Eve | Marketing
Diana | HR
Grace | HR

-- Sort active employees first then inactive

SELECT name, is_active
FROM employees
ORDER BY
CASE is_active
WHEN TRUE THEN 1
WHEN FALSE THEN 2

Module - 2 47
 END ASC;

-- Custom salary band order

SELECT name, salary,
CASE
WHEN salary >= 90000 THEN 'High'
WHEN salary >= 70000 THEN 'Medium'
ELSE 'Low'
END AS band
FROM employees
ORDER BY
CASE
WHEN salary >= 90000 THEN 1
WHEN salary >= 70000 THEN 2
ELSE 3
END;

11. Sorting Text — Collation

Text sorting is affected by collation (language rules):

-- Default sort (depends on database collation)

SELECT name FROM employees ORDER BY name;

-- Explicit collation sort

SELECT name FROM employees
ORDER BY name COLLATE "en_US";

-- Case insensitive sort

SELECT name FROM employees
ORDER BY LOWER(name) ASC;

ORDER BY in Full SELECT Structure

SELECT name, dept, salary -- 5. choose columns
FROM employees -- 1. from table
WHERE is_active = TRUE -- 2. filter rows

Module - 2 48
 GROUP BY dept, name, salary -- 3. group
HAVING salary > 50000 -- 4. filter groups
ORDER BY dept ASC, salary DESC -- 6. sort ← here
LIMIT 5 -- 7. limit rows
OFFSET 0; -- 8. skip rows

Complete Practical Examples

-- 1. All employees sorted by name A-Z
SELECT name, dept, salary
FROM employees
ORDER BY name ASC;

-- 2. Highest to lowest salary

SELECT name, salary
FROM employees
ORDER BY salary DESC;

-- 3. Department A-Z, salary high to low within dept

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

-- 4. Top 3 highest paid active employees

SELECT name, salary
FROM employees
WHERE is_active = TRUE
ORDER BY salary DESC
LIMIT 3;

-- 5. Most recently hired employees first

SELECT name, hire_date
FROM employees
ORDER BY hire_date DESC;

-- 6. Youngest to oldest in Engineering

SELECT name, age

Module - 2 49
 FROM employees
WHERE dept = 'Engineering'
ORDER BY age ASC;

-- 7. Sort by annual salary (expression)

SELECT name, salary, salary * 12 AS annual_salary
FROM employees
ORDER BY salary * 12 DESC;

-- 8. Sort by city then name, show top 5

SELECT name, city, salary
FROM employees
ORDER BY city ASC, name ASC
LIMIT 5;

Quick Reference
Syntax Meaning
ORDER BY salary Ascending (default)
ORDER BY salary ASC Ascending (A→Z, low→high)
ORDER BY salary DESC Descending (Z→A, high→low)
ORDER BY dept ASC, salary DESC Multi-column sort
ORDER BY 2 DESC Sort by 2nd column
ORDER BY alias DESC Sort by alias
ORDER BY salary DESC NULLS LAST NULLs at bottom
ORDER BY salary ASC NULLS FIRST NULLs at top

Best Practices
Always use ORDER BY when using LIMIT — without it results are unpredictable.
Prefer column names over column positions in ORDER BY — positions break
when SELECT list changes.
Use NULLS LAST with DESC to prevent NULLs from appearing at the top
unexpectedly.

Module - 2 50
 Use multi-column sorting for deterministic results when many rows share the
same value in the first sort column.
Use CASE in ORDER BY for custom business-defined sort orders.
For case-insensitive text sorting use ORDER BY LOWER(column) .
Always put ORDER BY after WHERE and GROUP BY — it is the second to last
clause before LIMIT.

LIMIT & OFFSET in PostgreSQL

What is LIMIT?
LIMIT restricts the number of rows returned by a query. Instead of getting all
rows, you get only the first N rows.

What is OFFSET?
OFFSET skips a specified number of rows before starting to return results. It
tells PostgreSQL — "ignore the first N rows, then start returning".

SELECT columns
FROM table
ORDER BY column
LIMIT n
OFFSET n;

Sample Table for Practice

CREATE TABLE employees (
emp_id SERIAL PRIMARY KEY,
name VARCHAR(100),
dept VARCHAR(50),
salary NUMERIC(10,2),
city VARCHAR(50),
age SMALLINT,
hire_date DATE,
is_active BOOLEAN
);

Module - 2 51
 INSERT INTO employees (name, dept, salary, city, age, hire_
date, is_active)
VALUES
('Alice', 'Engineering', 75000, 'Hyderabad', 28, '2022-01
-15', TRUE),
('Bob', 'Marketing', 60000, 'Mumbai', 35, '2021-03
-10', TRUE),
('Charlie', 'Engineering', 90000, 'Bangalore', 32, '2020-06
-01', TRUE),
('Diana', 'HR', 50000, 'Hyderabad', 29, '2023-07
-20', FALSE),
('Eve', 'Marketing', 70000, 'Chennai', 31, '2022-11
-05', TRUE),
('Frank', 'Engineering', 85000, 'Bangalore', 40, '2019-08
-15', TRUE),
('Grace', 'HR', 55000, 'Mumbai', 27, '2023-01
-10', FALSE),
('Henry', 'Finance', 95000, 'Hyderabad', 45, '2018-04
-01', TRUE);

1. Basic LIMIT
-- Get first 3 rows
SELECT name, salary
FROM employees
LIMIT 3;

name | salary
-------+----------
Alice | 75000.00
Bob | 60000.00
Charlie| 90000.00

-- Get first 5 rows

SELECT * FROM employees LIMIT 5;

Module - 2 52
 -- Get only 1 row
SELECT * FROM employees LIMIT 1;

⚠️ Without ORDER BY, which 3 rows you get is unpredictable. Always use
ORDER BY with LIMIT.

2. LIMIT with ORDER BY

-- Top 3 highest paid employees
SELECT name, salary
FROM employees
ORDER BY salary DESC
LIMIT 3;

name | salary
---------+----------
Henry | 95000.00
Charlie | 90000.00
Frank | 85000.00

-- Top 1 highest paid (single record)

SELECT name, salary
FROM employees
ORDER BY salary DESC
LIMIT 1;

-- 3 lowest paid employees

SELECT name, salary
FROM employees
ORDER BY salary ASC
LIMIT 3;

-- Most recently hired employee

SELECT name, hire_date
FROM employees
ORDER BY hire_date DESC

Module - 2 53
 LIMIT 1;

-- 3 youngest employees
SELECT name, age
FROM employees
ORDER BY age ASC
LIMIT 3;

3. Basic OFFSET
-- Skip first 3 rows, return rest
SELECT name, salary
FROM employees
ORDER BY salary DESC
OFFSET 3;

name | salary
---------+----------
Alice | 75000.00
Eve | 70000.00
Bob | 60000.00
Grace | 55000.00
Diana | 50000.00

-- Skip first 5 rows

SELECT name, salary
FROM employees
ORDER BY emp_id
OFFSET 5;

-- Skip first 1 row (get everyone except top paid)

SELECT name, salary
FROM employees
ORDER BY salary DESC
OFFSET 1;

Module - 2 54
 4. LIMIT + OFFSET Together — Pagination
This is the most common use case — paginating through records:

-- Page 1 — rows 1 to 3
SELECT name, salary
FROM employees
ORDER BY emp_id
LIMIT 3 OFFSET 0;

name | salary
---------+----------
Alice | 75000.00
Bob | 60000.00
Charlie | 90000.00

-- Page 2 — rows 4 to 6
SELECT name, salary
FROM employees
ORDER BY emp_id
LIMIT 3 OFFSET 3;

name | salary
---------+----------
Diana | 50000.00
Eve | 70000.00
Frank | 85000.00

-- Page 3 — rows 7 to 9
SELECT name, salary
FROM employees
ORDER BY emp_id
LIMIT 3 OFFSET 6;

name | salary
-------+----------

Module - 2 55
 Grace | 55000.00
Henry | 95000.00

5. Pagination Formula
OFFSET = (page_number - 1) * page_size
LIMIT = page_size

-- Page size = 3
-- Page 1: LIMIT 3 OFFSET 0 → (1-1)*3 = 0
-- Page 2: LIMIT 3 OFFSET 3 → (2-1)*3 = 3
-- Page 3: LIMIT 3 OFFSET 6 → (3-1)*3 = 6
-- Page 4: LIMIT 3 OFFSET 9 → (4-1)*3 = 9

-- Page size = 5
-- Page 1: LIMIT 5 OFFSET 0
-- Page 2: LIMIT 5 OFFSET 5
-- Page 3: LIMIT 5 OFFSET 10
-- Page 4: LIMIT 5 OFFSET 15

-- Practical pagination query

-- Page 2, page size = 3
SELECT
emp_id,
name,
dept,
salary
FROM employees
ORDER BY emp_id ASC
LIMIT 3 OFFSET 3;

6. Top N Per Group

-- Top 2 highest paid per department
-- (using LIMIT is not directly possible per group — use su

Module - 2 56
 bquery)
SELECT name, dept, salary
FROM (
SELECT
name, dept, salary,
ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salar
y DESC) AS rn
FROM employees
) ranked
WHERE rn <= 2
ORDER BY dept, salary DESC;

name | dept | salary

---------+--------------+----------
Charlie | Engineering | 90000.00
Frank | Engineering | 85000.00
Henry | Finance | 95000.00
Grace | HR | 55000.00
Diana | HR | 50000.00
Eve | Marketing | 70000.00
Bob | Marketing | 60000.00

7. Second, Third Highest Salary

-- 2nd highest salary
SELECT name, salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;

-- 3rd highest salary

SELECT name, salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 2;

-- Nth highest salary (N=4)

Module - 2 57
 SELECT name, salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 3; -- OFFSET = N - 1

8. LIMIT with WHERE and ORDER BY

-- Top 3 highest paid active employees
SELECT name, salary
FROM employees
WHERE is_active = TRUE
ORDER BY salary DESC
LIMIT 3;

-- Top 2 engineering employees by salary

SELECT name, dept, salary
FROM employees
WHERE dept = 'Engineering'
ORDER BY salary DESC
LIMIT 2;

-- Most recent hire in Hyderabad

SELECT name, city, hire_date
FROM employees
WHERE city = 'Hyderabad'
ORDER BY hire_date DESC
LIMIT 1;

9. LIMIT ALL and OFFSET Only

-- LIMIT ALL means no limit (same as no LIMIT)
SELECT * FROM employees
ORDER BY salary DESC
LIMIT ALL;

-- OFFSET without LIMIT — skip first 5, return rest

SELECT name, salary

Module - 2 58
 FROM employees
ORDER BY salary DESC
OFFSET 5;

10. LIMIT with NULL

-- LIMIT NULL is same as LIMIT ALL (no restriction)
SELECT * FROM employees LIMIT NULL;

-- OFFSET NULL is same as OFFSET 0 (no skip)

SELECT * FROM employees OFFSET NULL;

11. Full SELECT Execution Order

SELECT name, dept, salary -- 5. pick columns
FROM employees -- 1. get data
WHERE is_active = TRUE -- 2. filter rows
GROUP BY dept, name, salary -- 3. group
HAVING salary > 50000 -- 4. filter groups
ORDER BY salary DESC -- 6. sort
LIMIT 3 -- 7. limit ← here
OFFSET 0; -- 8. skip ← here

12. Practical Pagination Example

Imagine building an employee directory with pages:

-- Total records (for frontend to calculate total pages)

SELECT COUNT(*) AS total FROM employees;

-- Page 1 (page_size = 3)
SELECT emp_id, name, dept, salary
FROM employees
ORDER BY name ASC
LIMIT 3 OFFSET 0;

Module - 2 59
 -- Page 2
SELECT emp_id, name, dept, salary
FROM employees
ORDER BY name ASC
LIMIT 3 OFFSET 3;

-- Page 3
SELECT emp_id, name, dept, salary
FROM employees
ORDER BY name ASC
LIMIT 3 OFFSET 6;

LIMIT vs OFFSET Visual

All 8 rows sorted by salary DESC:
┌────┬─────────┬──────────┐
│ 1 │ Henry │ 95000.00 │ ← OFFSET 0 starts here
│ 2 │ Charlie │ 90000.00 │
│ 3 │ Frank │ 85000.00 │ ← LIMIT 3 stops here (Page 1)
├────┼─────────┼──────────┤
│ 4 │ Alice │ 75000.00 │ ← OFFSET 3 starts here (Page
2)
│ 5 │ Eve │ 70000.00 │
│ 6 │ Bob │ 60000.00 │ ← LIMIT 3 stops here (Page 2)
├────┼─────────┼──────────┤
│ 7 │ Grace │ 55000.00 │ ← OFFSET 6 starts here (Page
3)
│ 8 │ Diana │ 50000.00 │ ← LIMIT 3 stops here (Page 3)
└────┴─────────┴──────────┘

Performance Consideration
-- Small OFFSET — fast
SELECT * FROM employees ORDER BY emp_id LIMIT 10 OFFSET 10;

-- Large OFFSET — slow on big tables

SELECT * FROM employees ORDER BY emp_id LIMIT 10 OFFSET 100

Module - 2 60
 0000;
-- PostgreSQL must scan and skip 1 million rows!

For large datasets, use keyset pagination instead:

-- Instead of OFFSET, use WHERE on last seen ID (Keyset Pag

ination)
-- Page 1
SELECT emp_id, name, salary
FROM employees
ORDER BY emp_id ASC
LIMIT 3;
-- Last emp_id returned = 3

-- Page 2 — start after last seen ID

SELECT emp_id, name, salary
FROM employees
WHERE emp_id > 3 -- keyset condition
ORDER BY emp_id ASC
LIMIT 3;
-- Last emp_id returned = 6

-- Page 3
SELECT emp_id, name, salary
FROM employees
WHERE emp_id > 6
ORDER BY emp_id ASC
LIMIT 3;

OFFSET Pagination Keyset Pagination

Simplicity ✅ Simple Requires last seen value
Performance ❌ Slow on large data ✅ Always fast
Skip to any page ✅ Yes ❌ Sequential only
Handles inserts ❌ Can skip/duplicate rows ✅ Stable
Quick Reference

Module - 2 61
 Syntax Meaning
LIMIT 5 Return only 5 rows
LIMIT 1 Return only 1 row
LIMIT ALL No limit (all rows)
OFFSET 3 Skip first 3 rows
OFFSET 0 No skip (start from beginning)
LIMIT 3 OFFSET 3 Skip 3, return next 3 (Page 2)
LIMIT 1 OFFSET 1 2nd row (2nd highest salary etc.)

Best Practices
Always use ORDER BY with LIMIT — without it the rows returned are
unpredictable.
Use LIMIT 1 to get a single specific record like highest, lowest, latest, oldest.
Use OFFSET = (page - 1) × page_size for simple pagination in smaller datasets.
For large tables, prefer keyset pagination over OFFSET — it does not degrade
with large offsets.
Always retrieve total count separately when paginating so the frontend knows
total pages.
Use LIMIT 1 OFFSET N-1 to get the Nth highest or lowest value.
Never use LIMIT without testing edge cases — what happens on the last page
when fewer rows remain than the page size.

DISTINCT and Column Alias in PostgreSQL

PART 1 — DISTINCT

What is DISTINCT?
DISTINCT removes duplicate rows from query results and returns only unique
values. When you query a column that has repeated values, DISTINCT ensures
each value appears only once.

SELECT DISTINCT column

Module - 2 62
 FROM table;

Why Use DISTINCT?

Without DISTINCT:

SELECT dept FROM employees;

dept
--------------
Engineering
Marketing
Engineering
HR
Marketing
Engineering
HR
Finance

With DISTINCT:

SELECT DISTINCT dept FROM employees;

dept
--------------
Engineering
Marketing
HR
Finance

Sample Table for Practice

CREATE TABLE employees (
emp_id SERIAL PRIMARY KEY,
name VARCHAR(100),
dept VARCHAR(50),

Module - 2 63
 salary NUMERIC(10,2),
city VARCHAR(50),
age SMALLINT,
hire_date DATE,
is_active BOOLEAN
);

INSERT INTO employees (name, dept, salary, city, age, hire_

date, is_active)
VALUES
('Alice', 'Engineering', 75000, 'Hyderabad', 28, '2022-01
-15', TRUE),
('Bob', 'Marketing', 60000, 'Mumbai', 35, '2021-03
-10', TRUE),
('Charlie', 'Engineering', 90000, 'Bangalore', 32, '2020-06
-01', TRUE),
('Diana', 'HR', 50000, 'Hyderabad', 29, '2023-07
-20', FALSE),
('Eve', 'Marketing', 70000, 'Chennai', 31, '2022-11
-05', TRUE),
('Frank', 'Engineering', 85000, 'Bangalore', 40, '2019-08
-15', TRUE),
('Grace', 'HR', 55000, 'Mumbai', 27, '2023-01
-10', FALSE),
('Henry', 'Finance', 95000, 'Hyderabad', 45, '2018-04
-01', TRUE);

1. DISTINCT on Single Column

-- Unique departments
SELECT DISTINCT dept
FROM employees;

dept
--------------
Engineering
Finance

Module - 2 64
 HR
Marketing

-- Unique cities
SELECT DISTINCT city
FROM employees;

city
-----------
Hyderabad
Mumbai
Bangalore
Chennai

-- Unique is_active values

SELECT DISTINCT is_active FROM employees;

is_active
-----------
t
f

2. DISTINCT on Multiple Columns

When using DISTINCT on multiple columns, the combination of all columns
must be unique:

-- Unique dept + city combinations

SELECT DISTINCT dept, city
FROM employees
ORDER BY dept, city;

dept | city
--------------+-----------
Engineering | Bangalore

Module - 2 65
 Engineering | Hyderabad
Finance | Hyderabad
HR | Hyderabad
HR | Mumbai
Marketing | Chennai
Marketing | Mumbai

-- Unique dept + is_active combinations

SELECT DISTINCT dept, is_active
FROM employees
ORDER BY dept;

-- Unique city + is_active

SELECT DISTINCT city, is_active
FROM employees
ORDER BY city;

3. DISTINCT with ORDER BY

-- Unique departments sorted A-Z
SELECT DISTINCT dept
FROM employees
ORDER BY dept ASC;

-- Unique cities sorted Z-A

SELECT DISTINCT city
FROM employees
ORDER BY city DESC;

-- Unique salaries sorted highest first

SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC;

4. DISTINCT with WHERE

Module - 2 66
 -- Unique departments of active employees
SELECT DISTINCT dept
FROM employees
WHERE is_active = TRUE;

-- Unique cities in Engineering dept

SELECT DISTINCT city
FROM employees
WHERE dept = 'Engineering';

-- Unique depts with salary > 70000

SELECT DISTINCT dept
FROM employees
WHERE salary > 70000;

5. DISTINCT with COUNT

Count unique values using COUNT + DISTINCT:

-- How many unique departments exist

SELECT COUNT(DISTINCT dept) AS total_departments
FROM employees;

total_departments
-------------------
4

-- How many unique cities

SELECT COUNT(DISTINCT city) AS total_cities
FROM employees;

-- How many unique salaries

SELECT COUNT(DISTINCT salary) AS unique_salaries
FROM employees;

-- How many unique dept+city combinations

Module - 2 67
 SELECT COUNT(DISTINCT (dept, city)) AS unique_combinations
FROM employees;

6. DISTINCT vs GROUP BY
Both can remove duplicates but work differently:

-- DISTINCT — just removes duplicates

SELECT DISTINCT dept FROM employees;

-- GROUP BY — removes duplicates AND allows aggregation

SELECT dept, COUNT(*) AS total
FROM employees
GROUP BY dept;

DISTINCT GROUP BY
Purpose Remove duplicates Group + aggregate
Aggregation ❌ Not directly ✅ Yes
Performance Similar Similar
Use when Just need unique values Need counts/sums too

7. DISTINCT ON — PostgreSQL Specific

DISTINCT ON is a powerful PostgreSQL feature — it returns one row per unique
value of the specified column, keeping the first matching row based on ORDER
BY:

-- Get highest paid employee per department

SELECT DISTINCT ON (dept)
name,
dept,
salary
FROM employees
ORDER BY dept, salary DESC;

name | dept | salary

---------+--------------+----------

Module - 2 68
 Charlie | Engineering | 90000.00
Henry | Finance | 95000.00
Grace | HR | 55000.00
Eve | Marketing | 70000.00

-- Most recently hired per department

SELECT DISTINCT ON (dept)
name,
dept,
hire_date
FROM employees
ORDER BY dept, hire_date DESC;

-- Youngest employee per city

SELECT DISTINCT ON (city)
name,
city,
age
FROM employees
ORDER BY city, age ASC;

⚠️ With DISTINCT ON, the ORDER BY must start with the same column(s) as
DISTINCT ON.

8. DISTINCT with NULL

NULL values are treated as equal by DISTINCT — multiple NULLs collapse into
one:

-- If phone has multiple NULLs, DISTINCT returns only one N

ULL
SELECT DISTINCT phone FROM employees;

phone
-----------
NULL

Module - 2 69
 9876543210
9999999999

DISTINCT Summary
Syntax Description
SELECT DISTINCT col Unique values of one column
SELECT DISTINCT col1, col2 Unique combinations
SELECT DISTINCT ON (col) One row per unique value (PostgreSQL)
COUNT(DISTINCT col) Count unique values

PART 2 — Column Alias

What is a Column Alias?
A Column Alias gives a column or expression a temporary name in the query
result. It makes output more readable and meaningful. The alias only exists for
the duration of the query — it does not change the actual column name in the
table.

SELECT column AS alias_name

FROM table;

1. Basic Column Alias

-- Without alias (unclear output)
SELECT name, salary * 12
FROM employees;

name | ?column?
---------+------------
Alice | 900000.00

Module - 2 70
 -- With alias (clear output)
SELECT name, salary * 12 AS annual_salary
FROM employees;

name | annual_salary
---------+--------------
Alice | 900000.00

2. AS Keyword — Optional
The AS keyword is optional — both work the same:

-- With AS (recommended — more readable)

SELECT name AS employee_name, salary AS monthly_salary
FROM employees;

-- Without AS (works but less clear)

SELECT name employee_name, salary monthly_salary
FROM employees;

Best practice — always use AS for clarity.

3. Alias for Columns

SELECT
emp_id AS id,
name AS employee_name,
dept AS department,
salary AS monthly_salary,
city AS location,
is_active AS active_status
FROM employees;

id | employee_name | department | monthly_salary | locat

ion | active_status
----+---------------+--------------+----------------+------

Module - 2 71
 -----+--------------
1 | Alice | Engineering | 75000.00 | Hyder
abad | t
2 | Bob | Marketing | 60000.00 | Mumba
i | f

4. Alias for Expressions

-- Calculated columns
SELECT
name,
salary AS monthly_salary,
salary * 12 AS annual_salary,
salary * 0.10 AS bonus,
salary + (salary * 0.10) AS total_compensation
FROM employees;

name | monthly_salary | annual_salary | bonus | total

_compensation
---------+----------------+---------------+---------+------
-------------
Alice | 75000.00 | 900000.00 | 7500.00 |
82500.00
Bob | 60000.00 | 720000.00 | 6000.00 |
66000.00

-- String expressions
SELECT
name,
dept,
name || ' works in ' || dept AS description,
UPPER(name) AS name_upper,
LENGTH(name) AS name_length
FROM employees;

-- Date expressions

Module - 2 72
 SELECT
name,
hire_date,
CURRENT_DATE - hire_date AS days_employed,
AGE(CURRENT_DATE, hire_date) AS employment_duratio
n
FROM employees;

5. Alias for Aggregate Functions

SELECT
COUNT(*) AS total_employees,
MAX(salary) AS highest_salary,
MIN(salary) AS lowest_salary,
ROUND(AVG(salary),2) AS average_salary,
SUM(salary) AS total_salary_cost
FROM employees;

total_employees | highest_salary | lowest_salary | average

_salary | total_salary_cost
-----------------+----------------+---------------+--------
--------+------------------
8 | 95000.00 | 50000.00 | 7250
0.00 | 580000.00

-- GROUP BY with aliases

SELECT
dept AS department,
COUNT(*) AS headcount,
ROUND(AVG(salary), 2) AS avg_salary,
MAX(salary) AS top_salary
FROM employees
GROUP BY dept
ORDER BY avg_salary DESC;

Module - 2 73
 6. Alias with Spaces — Double Quotes
If alias contains spaces or special characters, wrap in double quotes:

SELECT
name AS "Employee Name",
salary AS "Monthly Salary",
salary * 12 AS "Annual CTC",
dept AS "Department Name"
FROM employees;

Employee Name | Monthly Salary | Annual CTC | Department N

ame
---------------+----------------+------------+-------------
----
Alice | 75000.00 | 900000.00 | Engineering
Bob | 60000.00 | 720000.00 | Marketing

7. Alias in ORDER BY
You can use aliases in ORDER BY — PostgreSQL resolves them:

SELECT
name,
salary * 12 AS annual_salary
FROM employees
ORDER BY annual_salary DESC; -- using alias in ORDER BY

name | annual_salary
---------+--------------
Henry | 1140000.00
Charlie | 1080000.00
Frank | 1020000.00

8. Alias in WHERE — NOT Allowed

Module - 2 74
 Aliases cannot be used in WHERE clause — WHERE is evaluated before
SELECT:

-- WRONG ❌
SELECT name, salary * 12 AS annual_salary
FROM employees
WHERE annual_salary > 900000;
-- ERROR: column "annual_salary" does not exist

-- CORRECT ✅ — use expression directly

SELECT name, salary * 12 AS annual_salary
FROM employees
WHERE salary * 12 > 900000;

-- OR use subquery
SELECT name, annual_salary
FROM (
SELECT name, salary * 12 AS annual_salary
FROM employees
) AS salary_data
WHERE annual_salary > 900000;

9. Alias in HAVING — NOT Allowed

Same rule applies to HAVING:

-- WRONG ❌
SELECT dept, AVG(salary) AS avg_salary
FROM employees
GROUP BY dept
HAVING avg_salary > 70000;
-- ERROR: column "avg_salary" does not exist

-- CORRECT ✅
SELECT dept, AVG(salary) AS avg_salary
FROM employees

Module - 2 75
 GROUP BY dept
HAVING AVG(salary) > 70000;

10. Table Alias

You can alias table names too — very useful in JOINs:

-- Table alias
SELECT [Link], [Link], [Link]
FROM employees AS e
WHERE [Link] > 70000;

-- Without AS
SELECT [Link], [Link]
FROM employees e;

11. Combining DISTINCT and Alias

-- Unique departments with alias
SELECT DISTINCT dept AS department
FROM employees
ORDER BY department;

-- Unique cities with alias

SELECT DISTINCT city AS location
FROM employees
ORDER BY location;

-- Count unique values with alias

SELECT
COUNT(DISTINCT dept) AS unique_departments,
COUNT(DISTINCT city) AS unique_cities
FROM employees;

Alias Usage Rules

Module - 2 76
 Clause Can Use Alias?
SELECT ✅ Define alias here
ORDER BY ✅ Yes
GROUP BY ✅ Yes (PostgreSQL allows)
WHERE ❌ No
HAVING ❌ No
FROM ✅ Table alias only
Complete Practical Example
SELECT
e.emp_id AS id,
[Link] AS "Employee Name",
[Link] AS department,
[Link] AS monthly_salary,
[Link] * 12 AS annual_salary,
[Link] * 0.10 AS bonus,
[Link] AS location,
AGE(CURRENT_DATE, e.hire_date) AS experience,
CASE e.is_active
WHEN TRUE THEN 'Active'
WHEN FALSE THEN 'Inactive'
END AS status
FROM employees AS e
WHERE e.is_active = TRUE
ORDER BY annual_salary DESC
LIMIT 5;

Best Practices
Always use AS keyword when defining aliases — it is more readable.
Use meaningful alias names that describe the data — annual_salary not col1 .
Use double quotes for aliases with spaces — "Employee Name" not Employee Name .
Remember aliases cannot be used in WHERE or HAVING — use the original
expression instead.

Module - 2 77
 Use table aliases in queries with multiple tables or JOINs to keep code clean.
Use DISTINCT ON in PostgreSQL when you need the top/first record per group
— it is more efficient than subqueries.
Use COUNT(DISTINCT column) to count unique values in a column.

Module - 2 78