Database Learning Part 3: SQL

SQL Series
Database Learning 1: Introduction: https://blog.yexca.net/archives/86
Database Learning 2: Relational Model: https://blog.yexca.net/archives/87
Database Learning 3: SQL: This article
Database Learning 4: Relational Database Theory: https://blog.yexca.net/archives/89
Database Learning 5: Normal Forms: https://blog.yexca.net/archives/90
Database Learning 6: Database Design: https://blog.yexca.net/archives/91
Database Learning 7: Database Control Functions: https://blog.yexca.net/archives/92

📢 This article was translated by gemini-3-flash-preview

SQL Classification

DDL (Data Definition Language)

In a database system, every database, table, view, and index is an object. DDL is used to create and delete these objects. DDL includes commands like CREATE, ALTER, and DROP.

DML (Data Manipulation Language)

DML is used to add, modify, and delete data within the database. It includes INSERT, DELETE, and UPDATE.

DQL (Data Query Language)

Querying is the fundamental function of a database. This is handled by SQL DQL, primarily using the SELECT statement.

DCL (Data Control Language)

DCL handles permission management and transaction management for database objects.

(I) DDL: Data Definition Language

1. Create Database

1
create database <database_name>

2. Create Base Table

1
2
3
4
5
6
7
create table <table_name>
(
    <column_name> <data_type> [column_level_constraint],
    [... ,]
    [table_level_constraint,]
    [...]
)

<1> Data Types

INT / INTEGER: Integer
FLOAT(n): Floating point, precision of at least n digits
NUMERIC(p, d) / DECIMAL(p, d) / DEC(p, d): Fixed point, p total digits (excluding sign/decimal point), d digits after the decimal
CHAR(n): Fixed-length string of length n
DATETIME: Date and time

1
2
3
4
5
6
-- Example
create table test
(
    f1 char(10),
    f2 int
)

<2> Column-Level Integrity Constraints

not null: Cannot be empty
unique: Values must be unique
not null unique: Cannot be empty and must be unique
default: Defines a default value

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
-- Example
create table test
(
    f1 int not null, 
    -- Column cannot be null
    
    f2 int unique, 
    -- Can be null, but non-null values must be unique
    
    f3 int not null unique, 
    -- Cannot be null and must be unique
    
    f4 char(5) default 'nashi' 
    -- Defaults to 'nashi' if no value is assigned
)

<3> Table-Level Integrity Constraints

Name	Clause	Relational Integrity
Primary Key	PRIMARY KEY	Entity Integrity
Check Clause	CHECK	User-defined Integrity
Foreign Key	FOREIGN KEY	Referential Integrity

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
-- Example
create table student
(
    sid int,
    primary key(sid) -- implies not null unique
)
-- Or
create table student
(
    sid int primary key
)

create table course
(
    cid int,
    sid int,
    score int,
    check(score >= 0 and score <= 100),
    foreign key(sid) references student(sid)
)

Integrity Checking

Assertions (Generally discouraged)

1
2
3
4
CREATE ASSERTION <assertion_name> <check_clause>  
-- check_clause is similar to a WHERE clause expression
-- Drop assertion
DROP ASSERTION <assertion_name>;

Triggers

Triggers check conditions when a table is inserted, deleted, or updated. If the condition is met, the trigger action executes.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
CREATE TRIGGER <trigger_name>           
-- Trigger activated on event
{BEFORE|AFTER} <trigger_event> ON <table_name> 
-- Executed before or after the event
REFERENCING NEW|OLD ROW AS <variable>  
-- References row variables
FOR EACH {ROW|STATEMENT}            
-- Trigger type/frequency
[WHEN <trigger_condition>] <trigger_body>      
-- Body executes if condition is true
 
-- Delete trigger
DROP TRIGGER <trigger_name> ON <table_name>

3. Modify Table Structure

Add a new column

1
2
3
4
alter table <table_name> add <column_name> <type>

-- Example
alter table test add f3 datetime

Modify a column

1
2
3
4
5
6
7
alter table <table_name> alter column <column_name> <new_type>
-- MySQL
-- alter table <table_name> modify <column_name> <new_type>

-- Example
alter table test alter column f3 float
-- alter table test modify f3 float

Delete a column

1
2
3
4
5
6
7
alter table <table_name> drop column <column_name>
-- MySQL
-- alter table <table_name> drop <column_name>

-- Example
alter table test drop column f3
-- alter table test drop f3

4. Drop Table

1
2
3
4
drop table <table_name>

-- Example
drop table test

(II) DML: Data Manipulation Language

The following examples are based on these tables:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
-- Note: Using English for clarity; avoid non-ASCII characters in production.
-- Student Table
create table student
(
    sno char(5) not null unique,
    sname char(8),
    ssex char(2),
    sbirthday datetime,
    class char(5)
)
-- Score Table
create table score
(
    sno char(5),
    cno char(5),
    degree int
)
-- Teacher Table
create table teacher
(
    tno char(5) not null unique,
    tname char(8),
    tsex char(2),
    tbirthday datetime,
    prof char(6),
    depart char(10)
)
-- Course Table
create table course
(
    cno char(5) not null unique,
    cname char(20),
    tno char(5)
)

1. INSERT Statement

Two ways to insert data: direct tuple insertion or using query results.

Direct tuple insertion

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
insert into table_name[(column_list)] values (tuple_values)[, ...]
-- Or
insert into table_name[(column_list)]
(table (tuple_values)[, (tuple_values), ...])

-- Example
insert into student
values 
('108', 'Zhang San', 'M', '2000-01-01', '09033'),
('109', 'Li Si', 'M', '2000-05-05', '09034')
-- Insert specific columns
insert into student(sno, sname, ssex, sbirthday)
values (110, 'Wang Wu', 'M', '2000-04-18')

Insert from a query result

1
2
3
4
5
insert into table_name[(column_list)] select <query_statement>

-- Example
insert into student
select 111, 'Zhao Liu', 'M', '2000-03-19', '09035'

2. DELETE Statement

1
2
3
4
5
6
7
delete from <table_name> [where <condition>]

-- Example
delete from student -- Deletes all content in the table
-- Delete tuple where sno is 111
delete from student
where sno = 111

3. UPDATE Statement

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
update <table_name>
set <column_name> = <expression>[, ...]
[where <condition>]

-- Example
update student
set class = '09058'
where sno = 110
-- Or
update student
set class = '09057', ssex = 'F'
where sno = 110 and class = '09058'

(III) DQL: Data Query Language

DQL is implemented via the SELECT statement. Complete syntax:

1
2
3
4
5
6
select <column_list_or_expressions>
from <table_list>
[where <row_condition>]
[group by <column_list>]
[having <group_condition>]
[order by <column_name> [asc|desc]]

1. Projection Queries

SELECT retrieves specific columns. Use , for multiple columns. In FROM, list all involved tables.

1
2
3
4
5
6
7
8
9
-- Examples
select 1 + 1
select sno * 100 from student
-- Query student sno and scores
select student.sno, score.degree
from student, score
-- Query all columns
select *
from student

Use DISTINCT to remove duplicate rows.

1
2
3
-- Query distinct courses that were taken
select distinct cno
from score

Use AS for column aliasing.

1
2
3
-- Example
select sno as 'student_id'
from student

2. Selection Queries

Selection filters records based on specific criteria.

Common operators:

Operator	Syntax	Description
Set Membership	IN / NOT IN	In set / Not in set
String Matching	LIKE	Pattern match with `_` and `%`
Null Comparison	IS NULL / IS NOT NULL	Is null / Is not null
Arithmetic	>, >=, <, <=, =, <>	Standard comparison operators
Logic	AND, OR, NOT	Standard logical operators

<1> Range Comparison

1
2
3
4
5
6
-- Query scores between 60 and 80
select *
from score
where degree between 60 and 80
-- 'between' is inclusive: [60, 80]
-- Equivalent to: where degree >= 60 and degree <= 80

<2> String Matching

1
where [not] like 'pattern' [escape 'escape_char']

%: Any number of characters
_: Any single character

<3> Set Comparison

1
2
3
4
-- Query scores that are 85, 86, or 88
select *
from score
where degree in (85, 86, 88)

<4> Logical Combinations

1
2
3
4
-- Query all female students in class 09031
select *
from student
where class = '09031' and ssex = 'F'

3. Sorting Queries

Use ORDER BY for sorting.

1
2
3
4
5
6
7
8
-- Ascending (default)
select sno, sname
from student
order by sno
-- Descending
select sno, sname
from student
order by sno desc

Multiple columns can be specified.

1
2
3
4
-- Sort by cno ascending, then degree descending
select *
from score
order by cno asc, degree desc

ORDER BY must be the last clause!

4. Aggregate Functions

Aggregate functions calculate a single value from a set of values. Usually used with GROUP BY.

Function	Description
AVG	Average value
COUNT	Number of items (COUNT(*) for total rows)
MIN	Minimum value
MAX	Maximum value
SUM	Total sum
STDEV	Standard deviation

General format:

1
[all|distinct] expr

ALL is the default. DISTINCT only considers unique values.

1
2
3
4
-- Count students in class '09031'
select count(*) as 'total'
from student
where class = '09031'

5. Data Grouping

Aggregate functions return one result. Mixing raw columns with aggregate functions without grouping causes errors. GROUP BY changes the scope of the aggregate function to each group. Use HAVING to filter groups.

1
2
3
4
5
6
-- Query sno where min score > 70 and max score < 90
select sno
from score
where degree is not null
group by sno
having min(degree) > 70 and max(degree) < 90

Execution order:

WHERE: Filter rows
GROUP BY: Group rows
Aggregate functions calculated
HAVING: Filter groups

1
2
3
4
-- Average score per course
select cno, avg(degree)
from score
group by cno

HAVING filters groups (usually with aggregates). WHERE filters rows. When using SELECT with columns and aggregates, all columns must be in GROUP BY.

6. Table Joins

Specify tables in FROM and conditions in WHERE.

1
2
3
select <columns>
from <tables>
where <join_condition>

Common columns require prefixes (e.g., student.sno).

<1> Inner Join

Theta join, Equi-join, Natural join. INNER JOIN is the basic join.

Equi-join

1
2
3
4
5
6
7
8
9
-- Query names, course IDs, and scores
select student.sname, score.cno, score.degree
from student, score
where student.sno = score.sno
-- Or using syntax:
select student.sname, score.cno, score.degree
from student
inner join score
on student.sno = score.sno

Table aliases simplify queries:

1
2
3
select x.sname, y.cno, y.degree
from student x, score y
where x.sno = y.sno

Non-equi join: Relationship is not “=”.
Self-join: Joining a table to itself.

1
2
3
4
5
6
-- Students in course '3-105' with scores higher than student '109' in the same course
select x.cno, x.sno, x.degree
from score x, score y
where x.cno = '3-105' and x.degree > y.degree
        and y.sno = '109' and y.cno = '3-105'
order by x.degree desc

<2> Outer Join

LEFT OUTER JOIN (or LEFT JOIN)
RIGHT OUTER JOIN (or RIGHT JOIN)
FULL OUTER JOIN (or FULL JOIN)

1
2
3
4
-- Left join example
select course.cname, teacher.tname
from course left join teacher
on (course.tno = teacher.tno)

7. Subqueries

<1> General Subquery

Used when a query condition depends on another query. Frequently in the WHERE clause.

1
2
3
4
5
6
7
8
-- Students born in the same year as student '105'
select sno, sname, year(sbirthday) as 'birth_year'
from student
where year(sbirthday) = (
    select year(sbirthday)
    from student
    where sno = '105'
    )

<2> Correlated Subquery

In a correlated subquery, the inner query references columns from the outer query and executes once for each candidate row in the outer query.

1
2
3
4
5
6
7
8
9
-- Students whose score is lower than the course average
select sno, cno, degree
from score a
where degree < (
    select avg(degree)
    from score b
    where a.cno = b.cno 
        and degree is not null
    )

<3> EXISTS Subquery

EXISTS returns true if the subquery returns any rows.

1
2
3
4
5
6
7
8
-- Names and departments of all teachers who teach a course
select tname, depart
from teacher a
where exists (
    select *
    from course b
    where a.tno = b.tno
    )

NOT EXISTS negates the logic.

8. Set Operations

Combine multiple SELECT results:

UNION: Union (Set sum)
INTERSECT: Intersection
EXCEPT: Difference

1
2
3
4
5
6
7
-- Union example
-- Names, genders, and birth years of all teachers and students
select sname, ssex, year(sbirthday)
from teacher
union
select sname, ssex, year(sbirthday)
from student

(IV) DCL: Data Control Language

DCL controls access rights, usually managed by the DBA (Database Administrator).

Database Security

Security factors:

Malicious access/destruction
Sensitive data leakage
Environmental vulnerabilities

Controls:

Identity Authentication (passwords, biometrics, etc.)
Access Control
Discretionary Access Control (DAC): Users can grant permissions to others.

Discretionary Access Control (DAC)

Functions:

Notify system of authorization via GRANT and REVOKE.
Check requests against authorizations in the data dictionary.

Standard permissions: DELETE, INSERT, SELECT, UPDATE. SELECT is equivalent to READ. REFERENCES restricts the ability to create foreign keys.

1. GRANT Syntax

1
2
3
4
grant <privileges> 
on <object_type> <object_name>
to <user>
[with grant option]

PUBLIC: Grants permission to all users.
WITH GRANT OPTION: Allows the user to grant that permission to others.

2. REVOKE Syntax

1
2
3
revoke <privileges>
on <object_type> <object_name>
from <user>

Database Roles

Roles represent groups of users (e.g., CEO, Developer).

Create Role: create role <role_name>
Grant to Role: grant <privilege> on <object> to <role>
Assign Role to User: grant <role> to <user> [with admin option]
Revoke from Role: revoke <privilege> on <object> from <role>

View Mechanism

A view is a virtual table derived from base tables or other views. It hides unnecessary data from users for security and simplicity.

Create View

1
2
3
create view <view_name>
as select <query>
[with check option]

Subqueries usually shouldn’t use ORDER BY or DISTINCT.
WITH CHECK OPTION ensures UPDATE/INSERT/DELETE operations on the view satisfy the view’s query condition.

1
2
3
4
5
6
-- Example: View for CS students
create view cs_student
    as select sno, sname, ssex
    from student
    where depart = 'CS'
    with check option

Drop View

1
drop view <view_name>

Auditing

Records all database operations to an audit log to detect illegal behavior.

1
2
AUDIT UPDATE ON score;
NOAUDIT UPDATE ON score;

Data Encryption

Uses algorithms to transform plaintext into ciphertext for storage.

Indexes

Indexes allow the DB engine to find data without scanning the whole table. They are defined at the internal schema level.

Create Index

1
2
create [unique] [cluster] index <index_name>
    on <table_name>(column_name [ASC|DESC], ...)

UNIQUE: Each index value maps to exactly one record.
CLUSTER: The physical order of records matches the index order.

Drop Index

1
drop index <index_name>