11 SQL DQL (Data Query Language)

DATA QUERY LANGUAGE (DQL)

SELECT - Tuples to Display

Basic SELECT Syntax

SELECT [columns]  -- Use * to select all columns
FROM tablename;

Key Concepts

• SELECT is used to query/retrieve table results
• Returns data from one or more tables based on specified criteria

SELECT Examples

1. Select All Columns

SELECT *
FROM department;

• Returns all columns and all rows from the department table
• Use * as a wildcard to select everything

2. Select Specific Columns

SELECT dname, location
FROM department;

• Returns only the specified columns (dname and location)
• More efficient than selecting all columns when you don't need all data

3. Using Column Aliases

SELECT dname AS department
FROM department;

• Alias names temporarily rename tables or columns
• Makes output more readable
• Useful in complex queries or reports

4. SELECT DISTINCT (Remove Duplicates)

SELECT DISTINCT sex AS gender
FROM employee;

• Returns only unique values from the specified column
• Eliminates duplicate entries
• Example: If there are multiple M and F entries, returns only M and F once

5. LIMIT - Restrict Number of Results

SELECT dname
FROM department
LIMIT 3;

• Returns only the top N records
• Useful for:
  • Testing queries
  • Pagination
  • Getting sample data

SELECT with Computed Columns

Arithmetic Operations

• SQL supports standard arithmetic operators:
  • + Addition
  • - Subtraction
  • * Multiplication
  • / Division
  • ^ Power (some applications use **)

Computing Values Example

SELECT essn, 
       proj_no AS projn,
       (hours * hourly_rate) AS wage
FROM assignment;

• Creates a computed column called wage
• Calculates the product of hours and hourly_rate
• Useful for:
  • Calculations on-the-fly
  • Derived metrics
  • Financial computations

SELECT with Sorting (ORDER BY)

Syntax

SELECT [columns]
FROM tablename
ORDER BY [columns] ASC|DESC;

Sorting Options

• ASC (Ascending): Default, sorts from lowest to highest (A-Z, 0-9)
• DESC (Descending): Sorts from highest to lowest (Z-A, 9-0)

Examples

Ascending Order:

SELECT fname, lname
FROM employee
ORDER BY lname ASC;

• Sorts employees by last name alphabetically

Descending Order:

SELECT fname, lname
FROM employee
ORDER BY lname DESC;

• Sorts employees by last name in reverse alphabetical order

SELECT WITH CONDITIONS

WHERE Clause

Syntax

SELECT [columns]
FROM tablename
WHERE [conditions]
[ORDER BY [columns] ASC|DESC];

Purpose

• Filter records based on specific conditions
• Only returns rows that satisfy the WHERE clause
• Can combine multiple conditions using logical operators

SQL Operators

Comparison Operators

Comparison Operators Examples

Equal Operator (=)

SELECT fname, lname, dept_no
FROM employee
WHERE dept_no = 1;

• Returns only employees in department 1

Greater Than Operator (>)

SELECT fname, lname, salary
FROM employee
WHERE salary > 2000;

• Returns employees with salary greater than 2000

BETWEEN Operator

Characteristics

• Checks if a value is within a range
• The BETWEEN operator is inclusive (includes boundary values)

Syntax

WHERE column_name BETWEEN value1 AND value2

Examples

Using BETWEEN:

SELECT fname, lname, dept_no
FROM employee
WHERE dept_no BETWEEN 2 AND 4;

Equivalent to:

SELECT fname, lname, dept_no
FROM employee
WHERE dept_no >= 2 AND dept_no <= 4;

• Both return employees in departments 2, 3, and 4

IS NULL / IS NOT NULL

Important Notes

• NULL represents missing or unknown data
• NULL is NOT the same as 0 or empty string
• Cannot use = operator with NULL

❌ INCORRECT:

WHERE dept_no = NULL  -- This will NOT work!

✅ CORRECT:

SELECT fname, lname, dept_no
FROM employee
WHERE dept_no IS NULL;

• Returns employees with no assigned department

SELECT fname, lname, dept_no
FROM employee
WHERE dept_no IS NOT NULL;

• Returns employees who have an assigned department

LIKE Operator (Pattern Matching)

Purpose

• Used with wildcards to find patterns within string attributes
• Case-sensitive in some databases

Wildcards

• Percent (%): Matches any sequence of characters (zero or more)
• Underscore (_): Matches exactly one character

Examples

Names beginning with "M":

SELECT *
FROM employee
WHERE fname LIKE "M%";

• Matches: Mary, Miles, Michael, etc.

Names ending with "n":

SELECT *
FROM employee
WHERE lname LIKE "%n";

• Matches: Watson, Jameson, Osborn, etc.

Names containing "on":

SELECT *
FROM employee
WHERE fname LIKE "%on%" OR lname LIKE "%on%";

• Matches: Jonah Jameson (first name contains "on")
• Matches: Watson, Jameson (last name contains "on")

Pattern with specific positions:

SELECT *
FROM department
WHERE location LIKE "_B_01%";

• Matches room format: _B_01%
• Examples: 2B101, 2B201, 2B301
• First character: any digit
• Second character: must be "B"
• Third character: any digit
• Fourth and fifth: must be "01"
• Remaining characters: anything

IN Operator

Purpose

• Checks if a value matches any value in a list
• Cleaner alternative to multiple OR conditions

Syntax

WHERE column_name IN (value1, value2, value3, ...)

Example

SELECT *
FROM project
WHERE dept_no IN (2, 3, 4);

Equivalent to:

WHERE dept_no = 2 OR dept_no = 3 OR dept_no = 4

• Returns projects from departments 2, 3, or 4
• เหมือนกันเลย แต่อันนี้เขียนง่ายกว่าล่ะ5555

Complex WHERE Conditions

Combining Multiple Operators

SELECT fname, lname, salary
FROM employee
WHERE (salary BETWEEN 1000 AND 5000 OR salary IS NULL)
  AND sex = "M"
ORDER BY fname ASC;

Breakdown:

• Outer condition: Must be Male (sex = "M")
• Inner condition (in parentheses):
  • Salary between 1000 and 5000, OR
  • Salary is NULL
• Results sorted by first name alphabetically

Best Practices:

• Use parentheses to control evaluation order
• Combine conditions logically
• Test complex queries incrementally

SQL FUNCTIONS

Overview

• Built-in functions applied to string, numeric, and date values
• Can appear anywhere in SQL where a value or attribute can be used
• Usually DBMS-dependent (syntax may vary)
• Reference: MySQL Functions Documentation

String Functions

String Function Examples

CONCAT() - Combine Strings:

SELECT CONCAT(fname, " ", lname) AS "Full name"
FROM employee;

• Combines first name, a space, and last name
• Output: "Peter Parker", "Mary Watson", etc.

อันนี้แหละช่วยแก้ปัญหา Composite attribute ใช่ม้าาา

UPPER() - Convert to Uppercase:

SELECT UPPER(CONCAT(fname, " ", lname)) AS "Full name"
FROM employee
WHERE lname = "osborn";

• Combines names and converts to uppercase
• Output: "NORMAN OSBORN", "HARRY OSBORN"

LOWER() - Convert to Lowercase:

SELECT LOWER(email) AS email_lower
FROM employee;

• Useful for case-insensitive comparisons

Numeric Functions

Numeric Function Examples

ROUND(), FLOOR(), CEIL():

SELECT salary,
       ROUND(salary) AS RDSalary,
       FLOOR(salary) AS FLSalary,
       CEIL(salary) AS CLSalary
FROM employee
WHERE salary IS NOT NULL;

Example Output:

• Original: 3400.40
• ROUND: 3400
• FLOOR: 3400
• CEIL: 3401

ABS(), POW(), LOG10():

SELECT ABS(-1000), POW(2,5), LOG10(100000);

• ABS(-1000) = 1000
• POW(2,5) = 32
• LOG10(100000) = 5

Date Functions

Date Function Examples

YEAR(), DATEDIFF():

SELECT bdate,
       (2020 - YEAR(bdate)) AS Age,
       DATEDIFF("2020-10-26", bdate) AS DiffDate
FROM employee;

• Calculates age based on birth year
• Calculates days between birth date and 2020-10-26

NOW(), MONTH(), MONTHNAME(), DAY(), DAYOFWEEK():

SELECT NOW() AS NOW,
       MONTH(NOW()) AS MM,
       MONTHNAME(NOW()) AS MMN,
       DAY(NOW()) AS DD,
       DAYOFWEEK(NOW()) AS DW;

• NOW(): Full timestamp (2020-10-26 10:08:45)
• MONTH: 10
• MONTHNAME: October
• DAY: 26
• DAYOFWEEK: 2 (Monday, since Sunday=1)

SQL AGGREGATE FUNCTIONS

Overview

• Aggregate = Grouping several elements together
• Used to summarize data across multiple rows
• Return a single value from a set of values
• ==Commonly used with GROUP BY clause==

Aggregate Functions List

COUNT Function

Purpose

• Counts the number of non-null values in a column
• COUNT(*) counts all rows, including NULLs

Examples

Count all employees:

SELECT COUNT(*) AS numemp
FROM employee;

• Returns: 6 (total number of employees)

Count male employees:

SELECT COUNT(*) AS numemp
FROM employee
WHERE sex = "M";

• Returns: 5 (number of male employees)

COUNT with DISTINCT:

-- Without DISTINCT (counts all)
SELECT COUNT(dept_no) AS deptcount
FROM employee;
-- Returns: 4 (counts all non-null dept_no values)
 
-- With DISTINCT (counts unique)
SELECT COUNT(DISTINCT(dept_no)) AS deptcount2
FROM employee;
-- Returns: 3 (counts only unique dept_no values)

MAX and MIN Functions

Purpose

• MAX: Find the maximum (highest) value
• MIN: Find the minimum (lowest) value

Examples

Maximum salary:

SELECT MAX(salary) AS max_salary
FROM employee;

• Returns: 4000.50

Maximum salary for males:

SELECT MAX(salary) AS max_salary
FROM employee
WHERE sex = "M";

• Returns: 4000.50

Minimum salary for males:

SELECT MIN(salary) AS min_salary
FROM employee
WHERE sex = "M";

• Returns: 1800.50

SUM Function

Purpose

• Computes the total sum for any numeric column
• Ignores NULL values

Examples

Total hours:

SELECT SUM(hours) AS totalhr
FROM assignment;

• Returns: 40 (sum of all hours)

Total payment:

SELECT SUM(hours * hourly_rate) AS totalpaid
FROM assignment;

• Returns: 2140.00
• Calculates wage for each row, then sums all wages

AVG Function

Purpose

• Computes the average (mean) value
• Ignores NULL values

Examples

Average hourly rate:

SELECT AVG(hourly_rate) AS avgrate
FROM assignment;

• Returns: 54.500000

Average hours for project 1:

SELECT AVG(hours) AS avghr_proj1
FROM assignment
WHERE proj_no = 1;

• Returns: 15.0000

GROUP BY Clause

The Problem

Until now, aggregate functions summarize all data at once:

• "Find the maximum salary of male employees"
• "Find the average salary of department #3"

What if we want to summarize by groups?

• Find the maximum salary per gender
• Find the average salary per department

The Solution: GROUP BY

GROUP BY Concept

How It Works

• Groups rows into smaller collections based on column values
• Aggregate functions then operate on each group separately

Example: GROUP BY sex

GROUP BY sex

If the employee table has two genders (F and M), this slices data into:

• FEMALE group - all employees where sex = 'F'
• MALE group - all employees where sex = 'M'

GROUP BY Syntax

SELECT [columns]
FROM tablename
[WHERE [conditions]]
GROUP BY [columns]
[ORDER BY [columns] ASC|DESC];

Important Notes:

• GROUP BY is only valid with aggregate functions
• Columns in SELECT should either be:
  • In GROUP BY clause, OR
  • Used with aggregate functions

GROUP BY Examples

Example 1: Average and Maximum Salary by Gender

SELECT sex,
       AVG(salary),
       MAX(salary)
FROM employee
GROUP BY sex;

Result:

Explanation:

• Divides employees into two groups (F and M)
• Calculates average and maximum for each group separately

Example 2: Group with WHERE and ORDER BY

SELECT sex,
       AVG(salary)
FROM employee
WHERE dept_no IN (1, 3)
GROUP BY sex
ORDER BY AVG(salary) ASC;

Result:

Explanation:

1. WHERE filters first: Only departments 1 and 3
2. GROUP BY creates groups by sex
3. AVG calculates average for each group
4. ORDER BY sorts by average salary ascending

Query Execution Order

Important: SQL executes in this order:

1. FROM - Get data from table
2. WHERE - Filter rows
3. GROUP BY - Create groups
4. Aggregate functions - Calculate on each group
5. ORDER BY - Sort results

This means:

• WHERE filters before grouping
• You can't use aggregate results in WHERE

HAVING Clause

The Problem

What if you want to filter groups based on aggregate results?

Example: "Show me departments with average salary > 3000"

❌ This won't work:

SELECT dept_no, AVG(salary)
FROM employee
WHERE AVG(salary) > 3000  -- ERROR!
GROUP BY dept_no;

Why? WHERE executes before GROUP BY and aggregate functions

The Solution: HAVING

Purpose

• Operates like WHERE, but for GROUP BY results
• Filters after grouping and aggregation
• Can use aggregate functions in conditions

Syntax

SELECT [columns]
FROM tablename
[WHERE [conditions]]
GROUP BY [columns]
HAVING [conditions]
[ORDER BY [columns] ASC|DESC];

HAVING Examples

Example 1: Total Paid per Project

Without HAVING (all projects):

SELECT proj_no,
       SUM(hours * hourly_rate) AS totalpaid
FROM assignment
GROUP BY proj_no;

Result:

With HAVING (filter groups):

SELECT proj_no,
       SUM(hours * hourly_rate) AS totalpaid
FROM assignment
GROUP BY proj_no
HAVING totalpaid < 1000;

Result:

Explanation:

• Calculates total paid for each project
• HAVING filters out project 1 (1560.00 > 1000)
• Only shows project 2 (580.00 < 1000)

WHERE vs HAVING

Example Using Both

SELECT dept_no,
       COUNT(*) AS emp_count,
       AVG(salary) AS avg_salary
FROM employee
WHERE salary IS NOT NULL      -- WHERE: Filter rows first
GROUP BY dept_no
HAVING COUNT(*) > 1           -- HAVING: Filter groups
ORDER BY avg_salary DESC;

Step by step:

1. WHERE removes employees with NULL salary
2. GROUP BY creates groups by department
3. COUNT and AVG calculated for each group
4. HAVING removes groups with 1 or fewer employees
5. ORDER BY sorts by average salary descending

Complete Query Structure

Full SQL SELECT Syntax

SELECT [columns]              -- 5. Choose columns to display
FROM tablename                -- 1. Get data from table
WHERE [conditions]            -- 2. Filter individual rows
GROUP BY [columns]            -- 3. Create groups
HAVING [conditions]           -- 4. Filter groups
ORDER BY [columns] ASC|DESC   -- 6. Sort final results
LIMIT [number];               -- 7. Limit number of results

Execution Order

1. FROM - Identify the table
2. WHERE - Filter rows
3. GROUP BY - Create groups
4. HAVING - Filter groups
5. SELECT - Choose columns
6. ORDER BY - Sort results
7. LIMIT - Restrict output

Key Takeaways

DQL Fundamentals

✅ SELECT retrieves data from tables
✅ Use WHERE to filter rows before aggregation
✅ Use ORDER BY to sort results
✅ Use LIMIT to restrict number of results
✅ Aliases (AS) improve readability

Operators and Functions

✅ Comparison operators: =, <>, >, <, >=, <=
✅ Special operators: BETWEEN, IN, LIKE, IS NULL
✅ String functions: CONCAT, UPPER, LOWER
✅ Numeric functions: ROUND, FLOOR, CEIL, ABS
✅ Date functions: NOW, YEAR, MONTH, DATEDIFF

Aggregation

✅ COUNT, SUM, AVG, MAX, MIN summarize data
✅ GROUP BY creates groups for aggregation
✅ HAVING filters groups (use after GROUP BY)
✅ WHERE filters rows (use before GROUP BY)

Best Practices

✅ Use DISTINCT to remove duplicates
✅ Always use IS NULL / IS NOT NULL for NULL checks
✅ Use parentheses in complex WHERE conditions
✅ Test queries incrementally
✅ Remember execution order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

Common Patterns

Pattern 1: Basic Filtering

SELECT column1, column2
FROM table
WHERE condition;

Pattern 2: Aggregation Without Grouping

SELECT COUNT(*), AVG(column)
FROM table
WHERE condition;

Pattern 3: Grouping and Aggregation

SELECT group_column, COUNT(*), AVG(column)
FROM table
WHERE row_condition
GROUP BY group_column
HAVING group_condition
ORDER BY column;

Pattern 4: Pattern Matching

SELECT *
FROM table
WHERE column LIKE "pattern%";

Pattern 5: Range Queries

SELECT *
FROM table
WHERE column BETWEEN value1 AND value2;