Data Query Language (DQL)

Basic SELECT Statements

SELECT * FROM TABLENAME
SELECT COLUMN1, COLUMN2, COLUMN3 FROM TABLENAME
SELECT COLUMN1, COLUMN2, COLUMN3 FROM TABLENAME WHERE CONDITION

-- Applying operations on columns during SELECT
SELECT COLUMN1, COLUMN2*5, COLUMN3+10 FROM TABLENAME
SELECT STUDENT_NAME, MARKS*1.1 FROM STUDENT  -- Increase marks by 10%
SELECT EMPLOYEE_NAME, SALARY/12 FROM EMPLOYEE  -- Monthly salary
SELECT PRODUCT_NAME, PRICE-DISCOUNT FROM PRODUCTS
SELECT LENGTH, WIDTH, LENGTH*WIDTH FROM RECTANGLE  -- Calculate area

Operations in SELECT:

  • Arithmetic operations: +, -, *, /
  • Can combine multiple columns: COLUMN1 + COLUMN2
  • Can use constants: MARKS * 1.1, PRICE - 100
  • Results appear as calculated values in output
  • Original data in table remains unchanged

DISTINCT Clause

SELECT DISTINCT COLUMN1, COLUMN2... FROM TABLENAME WHERE CONDITION
SELECT DISTINCT COLUMN1, COLUMN2... FROM TABLENAME

Important DISTINCT Rules:

  • DISTINCT can only be placed first and only once
  • Correct: SELECT DISTINCT COLUMN1, COLUMN2... FROM TABLENAME
  • Wrong: SELECT COLUMN1, DISTINCT COLUMN2, COLUMN3... FROM TABLENAME
  • Wrong: SELECT DISTINCT COLUMN1, DISTINCT COLUMN2... FROM TABLENAME

How DISTINCT Works: Returns unique combinations of all selected columns.

SELECT DISTINCT COLUMN1, COLUMN2 FROM TABLENAME;

Example:

Original Data:
COLUMN1  COLUMN2  COLUMN3
1        1        1
1        2        3
1        1        3    (duplicate)
1        3        2

After DISTINCT:
COLUMN1  COLUMN2  COLUMN3
1        1        1
1        2        3
1        3        2

SELECT DISTINCT COLUMN1 FROM TABLE_NAME;

Example:

Original Data:
COLUMN1 COLUMN2 Column3
1       1       1   
1       2       3    (duplicate)
1       1       3    (duplicate)
1       3       2    (duplicate)

After DISTINCT:
COLUMN1
1

WHERE Clause Operators

OperatorUsage
=Equal to
!=Not equal to
<Less than
>Greater than
<=Less than equal to
>=Greater than equal to
!<Not less than
!>Not greater than
ANDLogical AND
ORLogical OR
BETWEENBetween two values
NOT BETWEENNot between two values
INMultiple OR conditions
NOT INNot in specified values
IS NULLIs a null value
IS NOT NULLIs not null
LIKEPattern matching

WHERE Examples

SELECT * FROM TABLENAME WHERE COLUMN1=VALUE AND COLUMN2>30
SELECT * FROM TABLENAME WHERE COLUMN1 IS NULL
SELECT * FROM TABLENAME WHERE AGE BETWEEN 19 AND 23
SELECT * FROM TABLENAME WHERE AGE IN (1,2,3,19,20)
SELECT * FROM TABLENAME WHERE AGE BETWEEN 19 AND 23 AND COLUMN2 IS NULL

LIKE Pattern Matching

Syntax: SELECT COLUMN1, COLUMN2... WHERE COLUMN_NAME LIKE PATTERN

Wildcards

  • % - Matches any string of zero or more characters
  • _ - Matches any one character

Pattern Examples

PatternDescriptionExamples
A%Starts with AA, AA, ARBI, ARBIND
%AEnds with AA, NIRA
%AA%Contains AANIRAA, AA, MUAAL
____Exactly 4 charactersNIRA, BIRA, JIRA, KIRA
QUA__5 chars starting with QUAQUALA, QUAHD
_RE%2nd char R, 3rd char ETREE, AREA, FRETFUL
%RE_RE as 2nd and 3rd last
2___35 chars, starts with 2, ends with 3
2%3Starts with 2, ends with 3
2_%_%Starts with 2, at least 3 chars

ORDER BY

SELECT COLUMNNAME,... FROM TABLENAME WHERE CONDITION ORDER BY COLUMNNAME [ASC/DESC]

Examples

-- Sort by name in ascending order
SELECT * FROM STUDENTS WHERE AGE > 30 ORDER BY NAME ASC;

-- Sort by name first, then by age if names are same
SELECT * FROM STUDENTS WHERE AGE > 30 ORDER BY NAME, AGE ASC;

Aggregate Functions

FunctionDescription
AVG(COLUMN)Returns average of column
MIN(COLUMN)Returns minimum value
MAX(COLUMN)Returns maximum value
SUM(COLUMN)Returns total sum
COUNT(COLUMN)Returns number of rows without NULL
COUNT(*)Returns total number of records (with and without NULL)

Example

-- For pass students
SELECT MIN(STUDENT_MARKS), MAX(STUDENT_MARKS), AVG(STUDENT_MARKS), SUM(STUDENT_MARKS) 
FROM STUDENT 
WHERE STUDENT_MARKS > 35;

SELECT COUNT(STUDENT_MARKS), COUNT(*) FROM STUDENT;

GROUP BY

SELECT COLUMN FROM TABLENAME GROUP BY COLUMNNAME;

How GROUP BY Works

Example:

SELECT DEPARTMENT, AVG(SALARY) FROM EMPLOYEE GROUP BY DEPARTMENT;

Example : consider a table with employee data:

| EMPLOYEE_ID | NAME   | DEPARTMENT | SALARY |
|-------------|--------|------------|--------|
| 1           | Alice  | HR         | 5000   |
| 2           | Bob    | IT         | 6000   |
| 3           | Charlie| HR        | 5500   |
| 4           | David  | IT         | 7000   |

SELECT DEPARTMENT, AVG(SALARY) FROM EMPLOYEE GROUP BY DEPARTMENT;

Then it first groups the rows by department:

HR -> Alice(5000), Charlie(5500) -> Average = (5000 + 5500) / 2 = 5250
IT -> Bob(6000), David(7000) -> Average = (6000 + 7000) / 2 = 6500

final table:

| DEPARTMENT | AVG(SALARY) |
|------------|-------------|
| HR         | 5250        |
| IT         | 6500        |

Multiple Column GROUP BY

SELECT Country, City, COUNT(*)
FROM Customers 
GROUP BY Country, City;

Example: Consider a table with customer data:

| CustomerID | Country | City      | name   |
|------------|---------|-----------|--------|
| 1          | USA     | New York  | Alice  |
| 2          | USA     | Los Angeles| Bob    |
| 3          | Canada  | Toronto   | Charlie|
| 4          | Canada  | Vancouver | David  |
| 5          | USA     | New York  | Eve    |

Then it groups by both Country and City:

USA,New York -> Alice, Eve -> Count = 2
USA,Los Angeles -> Bob -> Count = 1
Canada,Toronto -> Charlie -> Count = 1
Canada,Vancouver -> David -> Count = 1

final table:

| Country | City        | COUNT(*) |
|---------|-------------|----------|
| USA     | New York    | 2        |
| USA     | Los Angeles | 1        |
| Canada  | Toronto     | 1        |
| Canada  | Vancouver   | 1        |

GROUP BY with WHERE

SELECT DEPARTMENT, AVG(SALARY)
FROM EMPLOYEE
WHERE SALARY > 1000
GROUP BY DEPARTMENT;

First filters records where salary > 1000, then groups and calculates average.

HAVING Clause

Used to filter groups created by GROUP BY.

-- Find students whose average marks > 30
SELECT STUDENT_NAME, AVG(MARKS) 
FROM STUDENT 
GROUP BY STUDENT_NAME 
HAVING AVG(MARKS) > 30;

-- Get only student names
SELECT STUDENT_NAME 
FROM STUDENT 
GROUP BY STUDENT_NAME 
HAVING AVG(MARKS) > 30;

WHERE vs HAVING

  • WHERE: Filters individual rows before grouping
  • HAVING: Filters groups after GROUP BY
SELECT STUDENT_NAME 
FROM STUDENT 
WHERE MARKS > 30 
GROUP BY STUDENT_NAME 
HAVING AVG(MARKS) > 50;

Process: Filter rows (MARKS > 30) → Group by name → Filter groups (AVG > 50)

JOINS

CROSS JOIN

SELECT COLUMN_NAME FROM TABLE1 CROSS JOIN TABLE2 CROSS JOIN TABLE3...

Returns Cartesian product of all tables.

INNER JOIN

SELECT  TEACHER.STUDENT_ID, STUDENT.STUDENT_ID,TEACHER.ENROLLMENT_ID,
 STUDENT.ENROLLMENT_ID,teacher_name, student_name,teacher_subject, 
 student_ageenrollment_date 
FROM TEACHER 
INNER JOIN STUDENT 
ON TEACHER.STUDENT_ID = STUDENT.STUDENT_ID
INNER JOIN ENROLLMENT 
ON TEACHER.STUDENT_ID = ENROLLMENT.STUDENT_ID

How INNER JOIN Works:

  1. First, it performs a Cartesian product of both tables (creates all possible combinations)
  2. Then, it keeps only those rows that satisfy the JOIN condition
  3. Discards all rows where the condition is false

More performance-optimized than CROSS JOIN with WHERE condition because it applies the condition during the join process rather than after creating the full Cartesian product.

NATURAL JOIN

SELECT * FROM TEACHER NATURAL JOIN STUDENTS;

Requirements:

  • Tables must have at least one attribute with the same name
  • Automatically joins on columns with identical names

How NATURAL JOIN Works:

  1. First, it identifies all columns with the same name in both tables
  2. Performs a join where corresponding values of same-named attributes are equal
  3. Keeps only rows where all common attribute values match
  4. Finally, merges the common columns into one (removes duplicates)

Key Difference from Other Joins:

  • Other Joins: If two tables have same attribute names, both columns appear separately in result
    • Need to specify using TABLE.ATTRIBUTE notation to distinguish
    • Example: TEACHER.STUDENT_ID and STUDENT.STUDENT_ID both appear
  • Natural Join: Automatically merges/combines same-named columns into one
    • Only one column appears in result for each matching attribute name
    • No need for TABLE.ATTRIBUTE specification

OUTER JOINS

LEFT OUTER JOIN

SELECT * FROM STUDENTS 
LEFT OUTER JOIN TEACHER 
ON STUDENTS.STUDENT_ID = TEACHER.STUDENT_ID;

Returns all rows from left table, matched rows from right table, NULL for unmatched. Also it is union of INNER JOIN and a left table.

RIGHT OUTER JOIN

SELECT * FROM STUDENTS 
RIGHT OUTER JOIN TEACHER 
ON STUDENTS.STUDENT_ID = TEACHER.STUDENT_ID;

Returns all rows from right table, matched rows from left table, NULL for unmatched. Also it is union of INNER JOIN and a right table.

FULL OUTER JOIN

-- Oracle syntax
SELECT * FROM STUDENT 
FULL OUTER JOIN TEACHER 
ON STUDENTS.STUDENT_ID = TEACHER.STUDENT_ID;

-- MySQL equivalent
SELECT * FROM STUDENT 
LEFT JOIN TEACHER ON STUDENT.STUDENT_ID = TEACHER.STUDENT_ID
UNION
SELECT * FROM STUDENT 
RIGHT JOIN TEACHER ON STUDENT.STUDENT_ID = TEACHER.STUDENT_ID;

Set Operations

Prerequisites:

  • Two relations must have same number of attributes
  • All attributes must be of same domain/type

UNION

SELECT TEACHER_NAME FROM TEACHER 
UNION 
SELECT STUDENT_NAME FROM STUDENTS;

Returns unique records from both queries.

UNION ALL

SELECT TEACHER_NAME FROM TEACHER 
UNION ALL 
SELECT STUDENT_NAME FROM STUDENTS;

Returns all records including duplicates.

INTERSECT

SELECT TEACHER_NAME FROM TEACHER 
INTERSECT 
SELECT STUDENT_NAME FROM STUDENTS;

Returns common records from both queries.

MINUS/EXCEPT

-- SQL standard
SELECT TEACHER_NAME FROM TEACHER 
EXCEPT 
SELECT STUDENT_NAME FROM STUDENTS;

-- Oracle
SELECT TEACHER_NAME FROM TEACHER 
MINUS 
SELECT STUDENT_NAME FROM STUDENTS;

Returns records from first query that are not in second query.

Nested Queries (Subqueries)

A query that is embedded within another SQL query is called a nested query or subquery.A sub query is used to return data that will be used in the main query as a condition to further restrict the data to be retrieved. Properties of Subqueries:

  • Subqueries must be enclosed in parentheses.
  • A subquery can have only one column in the SELECT statement.

Syntax:

SELECT column_name(s)
FROM table_name
WHERE column_name operator
    (SELECT column_name FROM table_name WHERE condition);

Types of Subqueries:

  1. Single Row Subquery: Returns a single row.

    SELECT * FROM STUDENTS 
WHERE AGE = (SELECT MAX(AGE) FROM STUDENTS);

  2. Multiple Row Subquery: Returns multiple rows.

    if using IN:

     SELECT * FROM STUDENTS 
 WHERE AGE IN (SELECT AGE FROM STUDENTS WHERE AGE > 20);

    if using ANY

    SELECT * FROM STUDENTS 
WHERE AGE > ANY (SELECT AGE FROM STUDENTS WHERE AGE < 30);

  3. if using ALL

    SELECT * FROM STUDENTS 
WHERE AGE > ALL (SELECT AGE FROM STUDENTS WHERE AGE < 30);

Aliases (AS Keyword)

Column Aliases

-- Using AS keyword
SELECT COLUMN1 AS COL1, COLUMN2 AS COL2 FROM TABLENAME
SELECT STUDENT_NAME AS NAME, STUDENT_MARKS AS MARKS FROM STUDENT
SELECT AVG(SALARY) AS AVERAGE_SALARY FROM EMPLOYEE

-- Without AS keyword (optional)
SELECT COLUMN1 COL1, COLUMN2 COL2 FROM TABLENAME
SELECT COUNT(*) TOTAL_STUDENTS FROM STUDENT

Table Aliases

-- Using AS keyword
SELECT S.STUDENT_NAME, T.TEACHER_NAME 
FROM STUDENT AS S, TEACHER AS T 
WHERE S.STUDENT_ID = T.STUDENT_ID

-- Without AS keyword (optional)
SELECT S.STUDENT_NAME, T.TEACHER_NAME 
FROM STUDENT S, TEACHER T 
WHERE S.STUDENT_ID = T.STUDENT_ID

-- Useful in JOINs
SELECT S.NAME, T.SUBJECT 
FROM STUDENT AS S 
INNER JOIN TEACHER AS T ON S.ID = T.STUDENT_ID

Benefits of Aliases:

  • Makes column names more readable in output
  • Shortens long table names for easier reference
  • Essential when using aggregate functions
  • Required when same column names exist in multiple tables