Chapter 5

SQL (Structured Query Language)

4.0
fit@hcmus
 Content

▪ Introduction
▪ Data definition
▪ Data manipulation
▪ Query
▪ Data update
▪ View
▪ Index

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 2

 Introduction
▪ Relational algebra language (ĐSQH )
- How to execute the query operations
- Difficult for users
▪ SQL (Structured Query Language)
- High level declarative language interface
- The user only specifies what the result is to be
- Developed by IBM (1970s)
- Also pronounced SEQUEL
- SQL became a standard of American National Standards
Institute (ANSI)
◼ SQL-86
◼ SQL-92
◼ SQL-99
© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 3
 Introduction

▪ SQL includes
- Data definition language (DDL)
SQL-92 standard
- Data manipulation language (DML)
- View definition
- Integrity constraint
- Authorization and security SQL Server
- Transaction control
▪ SQL terms
- Table ~ relation
- Column ~ attribute
- Row ~ tuple
© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 4
 Content

▪ Introduction
▪ Data definition
- Data type
- Data definition commands
▪ Data manipulation
▪ View
▪ Index

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 5

 Data definition
▪ Describes the structure of information in the DB
▪ Schema for the relation
▪ Domain of each attribute
▪ Integrity constraint
▪ Index on each relation
▪ Consists of
- CREATE TABLE
- DROP TABLE
- ALTER TABLE
- CREATE DOMAIN
- CREATE DATABASE

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 6

 Data definition

▪ Numeric
- INTEGER
- SMALLINT
- NUMERIC, NUMERIC(p), NUMERIC(p,s)
- DECIMAL, DECIMAL(p), DECIMAL(p,s)
- REAL
- DOUBLE PRECISION
- FLOAT, FLOAT(p)

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 7

 Data definition
▪ Character string
- CHARACTER (CHAR)
- CHARACTER(n) (CHAR (n))
- CHARACTER VARYING(n) (VARCHAR(n))
- NATIONAL CHARACTER (n) (NCHAR(n))
▪ Bit string
- BIT, BIT(x)
- BIT VARYING(x)
▪ Datetime
- DATE (M/D/YY), DATETIME, TIMESTAMP (M/D/YY hh:mm)
- TIME (hh:mm)

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 8

 Create table command
▪ Define a new table by giving
- A name
- Attributes
◼ Name
◼ Date type
◼ Integrity constraints on attribute
▪ Syntax
CREATE TABLE <Table_name> (
<Column_name> <Data_type> [<Contraint>],
<Column_name> <Data_type> [<Contraint>],
…
[<Constraint>]
)

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 9

 Example
▪ GIAOVIEN table includes:
- MaGV : primary key
- HoTen: not null
- Luong: default value = 1000
- Phai: ”Nam” or ”Nữ”
- NgaySinh: date of birth
- SoNha, Duong, Quan, ThanhPho
- GVQLCM: foreign key
- MaBM: foreign key

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 10

 Example
CREATE TABLE GIAOVIEN (
MaGV CHAR(9),
HoTen NVARCHAR(50),
Luong INT,
Phai CHAR(3),
NgaySinh DATETIME,
SoNha NVARCHAR(10),
Duong NVARCHAR(50),
Quan NVARCHAR(50),
ThanhPho NVARCHAR(50),
GVQLCM CHAR(9),
MaBM CHAR(9)
)

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 11

 Create table command
▪ <Constraint>
- NOT NULL
- NULL
- UNIQUE
- DEFAULT
- PRIMARY KEY
- FOREIGN KEY / REFERENCES
- CHECK

▪ Give a name to constraints

CONSTRAINT <Constraint_name> <Constraint>

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 12

 Example
CREATE TABLE GIAOVIEN (
MAGV CHAR(9) PRIMARY KEY,
HOTEN NVARCHAR(50) NOT NULL,
LUONG INT DEFAULT (1000),
PHAI CHAR(3) CHECK (PHAI IN(‘Nam’, ‘Nu’)),
NGAYSINH DATETIME,
SONHA NVARCHAR(10),
DUONG NVARCHAR(50),
QUAN NVARCHAR(50),
THANHPHO NVARCHAR(50),
GVQLCM CHAR(9),
MABM CHAR(9)
)

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 13

 Example
CREATE TABLE CONGVIEC (
MADT VARCHAR(10),
STT INT,
TENCV NVARCHAR(50),
NGAYBD DATETIME,
NGAYKT DATETIME,
PRIMARY KEY(MADT, STT)
)
CREATE TABLE DETAI (
MADT VARCHAR (10) PRIMARY KEY,
TENDT NVARCHAR (50) UNIQUE,
KINHPHI INT,
CAPQL NVARCHAR (50),
NGAYBD DATETIME,
NGAYKT DATETIME,
MACD VARCHAR (10),
GVCNDT CHAR (9)
)
© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 14
 Example
CREATE TABLE GIAOVIEN (
MAGV CHAR(9) CONSTRAINT PK_GV PRIMARY KEY,
HOTEN NVARCHAR(50) CONSTRAINT NN_HOTEN NOT NULL,
LUONG INT CONSTRAINT DE_LUONG DEFAULT (10000),
PHAI CHAR(3) CONSTRAINT CK_PHAI CHECK (PHAI IN('Nam', 'Nu'))
CONSTRAINT NN_PHAI NOT NULL,
NGAYSINH DATETIME,
SONHA NVARCHAR(10),
DUONG NVARCHAR(50),
QUAN NVARCHAR(50),
THANHPHO NVARCHAR(50),
GVQL CHAR(9),
MABM CHAR(9)
)
© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 15
 Example
CREATE TABLE CONGVIEC(
MADT VARCHAR(10),
STT INT,
TENCV NVARCHAR(50),
NGAYBD DATETIME,
NGAYKT DATETIME,

CONSTRAINT PK_CV PRIMARY KEY(MADT, STT),

CONSTRAINT FK_CONGVIEC_DETAI
FOREIGN KEY MADT REFERENCES DETAI(MADT)
)

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 16

 Create table command
▪ Is used for modification
- The structure of tables
- Integrity constraints
▪ Add columns
ALTER TABLE <Table_name> ADD
<Column_name> <Data_type> [<Constraint>]
▪ Drop columns
ALTER TABLE <Table_name> DROP COLUMN <Column_name>

▪ Alter columns
ALTER TABLE <Table_name> ALTER COLUMN
<Column_name> <Data_type>

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 17

 Alter table command
▪ Add constraints
ALTER TABLE <Table_name> ADD
CONSTRAINT <Constraint_name> <constraint>,
CONSTRAINT <Constraint_name> <constraint>,
…

▪ Drop constraints
ALTER TABLE <Table_name> DROP <Contraint_name>

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 18

 Example

ALTER TABLE GIAOVIEN ADD TUOI INT

CONSTRAINT CK_TUOI CHECK (TUOI >= 23 AND TUOI <=60) NOT NULL

ALTER TABLE GIAOVIEN DROP COLUMN HOTEN

ALTER TABLE GIAOVIEN ALTER COLUMN HOTEN

NVARCHAR(100)

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 19

 Example
CREATE TABLE BOMON(
MABM INT NOT NULL, PRIMARY KEY constraint must
be defined as NOT NULL
TENBM NVARCHAR(50),
PHONG CHAR(10),
DIENTHOAI CHAR(15),
TRUONGBM CHAR(9),
MAKHOA CHAR(4),
NGAYNHANCHUC DATETIME
)
ALTER TABLE BOMON ADD
CONSTRAINT PK_BOMON PRIMARY KEY (MABM),
CONSTRAINT FK_TRBOMON FOREIGN KEY (TRUONGBM) Must ensure
that GIAOVIEN
REFERENCES GIAOVIEN(MAGV),
is existed
CONSTRAINT UNI_TENBM UNIQUE(TENBM),
CONSTRAINT DF_NGAYNHANCHUC DEFAULT(GETDATE()) FOR NGAYNHANCHUC
© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 20
 Drop table command
▪ Is used for deleting the structure of tables
- All the data in a table are also deleted
▪ Syntax
DROP TABLE <Table_name>

▪ Example
DROP TABLE GIAOVIEN

DROP TABLE BOMON

DROP TABLE THAMGIADT

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 21

 Drop table command

GIÁOVIÊN
MãGV HọTên Lương Phái NgàySinh SốNhà Đường Quận ThànhPhố GVQL MãBM

BỘMÔN
MãKhoa TrưởngBM NgàyNhậnChức ĐiệnThoại MãBM TênBM Phòng
KHOA
TrưởngKhoa NgàyNhậnChức TênKhoa NămTL Phòng ĐiệnThoại MãKhoa

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 22

 Create type command

▪ Is used for creating a new data type

▪ Syntax
CREATE TYPE <New_data_type> AS <Data_type>

▪ Ví dụ
CREATE TYPE MyString30 AS VARCHAR(30)

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 23

 Content
▪ Introduction
▪ Data definition
▪ Data manupulation
- Basic queries
- Set, set/multiset comparison and nested
queries
- Aggregate functions and grouping
▪ View
▪ Index

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 24

 Basic Query
▪ Is used for retrieving some tuples that often
satisfy a certain condition
▪ Is formed of the three clauses
SELECT <list_of_columns>
FROM <list_of_tables>
WHERE <condition>
- < list_of_columns >
◼ Column names showed in the result of the query
- < list_of_tables >
◼ Table names required to process the query
- < condition >
◼ Boolean expression that identifies the rows to be retrieved
◼ Expression’s connection : AND, OR, and NOT
◼ Operations: < , > , <=, >=, <>, =, LIKE and BETWEEN

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 25

 Basic query
▪ SQL and Relational Algebra

SELECT <list_of_columns>
 FROM <list_of_tables>
WHERE <condition>


SELECT L
FROM R
WHERE C
L (C (R))

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 26

 SELECT-clause

List all columns The entire tuple is produced

SELECT *
FROM KHOA
WHERE PHONG='I53'
AND NAMTL = '1995'

MaKhoa TenKhoa Phong NamTL DienThoai TruongKhoa NgayNhanChuc

CNTT Công nghệ thông tin I53 1995 08313964145 GV130 01/01/2007

SH Sinh học B32 1975 08313123545 GV250 01/01/1990

 PHG='I53'NamTL='1995' (KHOA)

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 27

 SELECT-clause
List some specific columns

SELECT MAKHOA, TENKHOA, PHONG

FROM KHOA
WHERE PHONG='I53'
AND NAMTL = '1995'

MaKhoa TenKhoa Phong

CNTT Công nghệ thông tin I53

 MAKHOA, TENKHOA, PHONG(  PHG='I53'NamTL='1995' (KHOA))

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 28

 SELECT-clause

Alias name

SELECT MAKHOA AS 'Mã khoa', TENKHOA AS 'Tên khoa', PHONG AS 'Mã phòng'
FROM KHOA
WHERE PHG='I53' AND NAMTL = '1995'

Mã khoa Tên khoa Mã phòng

CNTT Công nghệ thông tin I53

 Mã khoa, Tên khoa, Mã phòng(  MaKhoa,TenKhoa,PHG(  PHG='I53'NamTL='1995' (KHOA)))

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 29

 SELECT-clause
Extension

SELECT MAGV, HOTEN, SONHA + ',' + DUONG + ',' + ',' + QUAN + ',' +
THANHPHO AS 'DIA CHI'
FROM GIAOVIEN
WHERE PHAI='Nam'

MAGV HOTEN DIA CHI

GV001 Nguyễn Văn A 123 Phan Đăng Lưu, [Link]ú Nhuận, TP.Hồ Chí
Minh

 MAGV,HOTEN,DIA CHI(  MAGV,HOTEN,SONHA+DUONG+QUAN+THANHPHO(  PHAI=‘Nam’(GIAOVIEN)))

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 30

 SELECT-clause
Extension

SELECT MAGV, LUONG*1.1 AS 'LUONG10%'

FROM GIAOVIEN
WHERE PHAI='Nam'

MAGV LUONG10%

GV001 550000

 MAGV,LUONG10%(  MAGV,LUONG*1.1(  PHAI=‘Nam’(GIAOVIEN)))

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 31

 SELECT-clause
Duplicate tuples are eliminated

SELECT LUONG SELECT DISTINCT LUONG

FROM GIAOVIEN FROM GIAOVIEN
WHERE PHAI=‘Nam’ WHERE PHAI=‘Nam’
LUONG LUONG
30000 30000
25000 25000
25000 38000
38000

- Cost
- Users want to see all tuples

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 32

 Example
▪ Find the MAGV and TENGV of teachers who
work for the department ‘Hệ thống thông tin’

R1  GIAOVIEN [Link]=[Link] BOMON

KQ  MAGV, HOTEN (TENBM='Hệ thống thông tin' (R1))

SELECT MAGV, HOTEN

FROM GIAOVIEN, BOMON
WHERE TENBM= N'Hệ thống thông tin'
AND [Link]=[Link]

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 33

 WHERE-clause

▪ Use logical operators (AND, OR) to combine two

or more boolean expressions

SELECT MAGV, HOTEN

FROM GIAOVIEN, BOMON TRUE
Boolean
WHERE TENBM = N'Hệ thống thông tin'
expressions AND
AND [Link] = [Link]
TRUE

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 34

 WHERE-clause

Priority

SELECT MAGV, HOTEN

FROM GIAOVIEN, BOMON
WHERE (TENBM = N'Hệ thống thông tin' OR TENBM = N'Mạng máy tính')
AND [Link] = [Link]

The default priority of logical operators: left to right.

Use parentheses to explicitly specify the intended precedence of the
operator

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 35

 WHERE-clause

BETWEEN

SELECT MAGV, HOTEN

FROM GIAOVIEN
WHERE LUONG >= 20000 AND LUONG <= 30000

SELECT MAGV, HOTEN

FROM GIAOVIEN
WHERE LUONG BETWEEN 20000 AND 30000

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 36

 WHERE-clause

NOT BETWEEN

SELECT MAGV, HOTEN

FROM GIAOVIEN
WHERE LUONG NOT BETWEEN 20000 AND
30000

SELECT MAGV, HOTEN

FROM GIAOVIEN
WHERE LUONG < 20000 OR LUONG > 30000

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 37

 WHERE-clause

LIKE

SELECT MAGV, HOTEN

FROM GIAOVIEN
HOTEN LIKE ‘nguyen _ _ _ _’
WHERE HOTEN LIKE ‘Nguyen _ _ _ _’

Arbitrary characters
SELECT MAGV, HOTEN
FROM GIAOVIEN
WHERE HOTEN LIKE ‘Nguyen %’

Arbitrary strings

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 38

 WHERE-clause

NOT LIKE

SELECT MAGV, HOTEN

FROM GIAOVIEN
WHERE HOTEN LIKE ‘Nguyen’

negative

SELECT MAGV, HOTEN

FROM GIAOVIEN
WHERE HOTEN NOT LIKE ‘Nguyen’

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 39

 WHERE-clause

charlist MAGV HOTEN

GV001 Nguyễn Văn A
GV002 Hoàng Thị C

SELECT * SELECT *
FROM GIAOVIEN GV FROM GIAOVIEN GV
WHERE [Link] LIKE N'[n]%' WHERE [Link] LIKE N'[nh]%'

MAGV HOTEN MAGV HOTEN

GV001 Nguyễn Văn A GV001 Nguyễn Văn A
GV002 Hoàng Thị C

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 40

 WHERE-clause

charlist MAGV HOTEN

GV001 Nguyễn Văn A
GV002 Hoàng Thị C

SELECT * SELECT *
FROM GIAOVIEN GV FROM GIAOVIEN GV
WHERE [Link] LIKE N'[^h]%' WHERE [Link] LIKE N'[^nh]%'

MAGV HOTEN MAGV HOTEN

GV001 Nguyễn Văn A

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 41

 WHERE-clause

Ngày giờ

SELECT MAGV, HOTEN

FROM GIAOVIEN
WHERE NGAYSINH BETWEEN ‘1955-12-08’ AND ‘1966-07-19’

‘1955-12-08’ YYYY-MM-DD ’17:30:00’ HH:MI:SS

’12/08/1955’MM/DD/YYYY ’05:30 PM’
‘December 8, 1955’

‘1955-12-08 17:30:00’

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 42

 WHERE-clause
NULL

- SQL allows attributes to have value NULL

◼ Value unknown
◼ Value inapplicable
◼ Value withheld
- Operation on a NULL and any value, the result is NULL
◼ x has a value NULL
◼ x + 3 is also NULL
- Comparison on a NULL value and any value, the result is
UNKNOWN
◼ The value of x = 3 is UNKNOWN
◼ The comparison x = 3 is not correct SQL

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 43

 WHERE-clause

NULL

SELECT MAGV, HOTEN

FROM GIAOVIEN
WHERE GVQL IS NULL

SELECT MAGV, HOTEN

FROM GIAOVIEN
WHERE GVQL IS NOT NULL

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 44

 FROM-clause
From clause ~ x in Relational algebra

SELECT *
GIAOVIEN x BOMON FROM GIAOVIEN, BOMON
SELECT *
FROM GIAOVIEN, BOMON
MAGV MAB MABM TENBM WHERE TRUE
M
001 HTTT HTTT Hệ thống thông tin
Sử dụng thêm điều
001 HTTT MTT Mạng máy tính
kiện ở WHERE để biểu
001 HTTT CNPM Công nghệ PM
diễn phép kết
002 MMT HTTT Hệ thống thông tin
002 MTT Mạng máy tính GIAOVIEN BOMON
MMT
002 CNPM Công nghệ PM C
MMT SELECT *
… … … … FROM GIAOVIEN, BOMON
WHERE C

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 45

 FROM-clause
Alias name SELECT MAGV, MABM, TENBM

Ambigous ??? FROM GIAOVIEN, BOMON

WHERE MABM = MABM
MAGV MAB MABM TENBM
M
001 HTTT HTTT Hệ thống thông tin SELECT [Link], [Link], [Link]
001 HTTT MTT Mạng máy tính
FROM GIAOVIEN G, BOMON AS B
001 HTTT CNPM Công nghệ PM
002 MMT HTTT Hệ thống thông tin WHERE [Link] = [Link]
002 MMT MTT Mạng máy tính
002 MMT CNPM Công nghệ PM
use spaces or “AS” when you are
… … … … aliasing
MAGV MABM TENBM
001 HTTT Hệ thống thông tin Điều kiện kết để tìm ra bộ môn của
002 giáo viên
MTT Mạng máy tính
… … …
© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 46
 Example
▪ Cho biết tên của bộ môn và tên của
trưởng bộ môn của những bộ môn thuộc
khoa CNTT (mã khoa)
SELECT [Link], [Link] AS TEN_TRUONGBM
FROM BOMON BM, GIAOVIEN GV
WHERE [Link] = [Link] AND
[Link]= ‘CNTT’

Điều kiện kết để tìm ra giáo viên làm trưởng bộ môn

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 47

 Example

 Với những đề tài thuộc cấp quản lý ‘Thành

phố’, cho biết mã đề tài, đề tài thuộc về chủ
đề nào, họ tên người chủ nghiệm đề tài
cùng với ngày sinh và địa chỉ của người ấy
SELECT [Link], [Link], [Link], [Link], [Link]
FROM DETAI D, CHUDE C, GIAOVIEN G
WHERE [Link] = ‘Thanh Pho’ AND [Link] = [Link] AND
[Link] = [Link]

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 48

 Example
▪ Tìm họ tên của giáo viên viên thuộc bộ
môn “HTTT” có tham gia vào đề tài
“ Mobile Database” với số tiền phụ cấp
cho mỗi công việc trên 10 triệu.
SELECT [Link]
FROM GIAOVIEN GV, THAMGIADT TG, DETAI DT
WHERE [Link] = [Link] AND
[Link] = [Link] AND
[Link]=‘HTTT’ AND
[Link]=‘Mobile Database’ AND [Link]>10

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 49

 Example

▪ Tìm họ tên của từng giáo viên và người

phụ trách chuyên môn trực tiếp của nhân
viên đó.

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 50

 Example

▪ Tìm họ tên của những giáo viên được

“Trần Trà Hương” phụ trách quản lý
chuyên môn.

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 51

 ORDER BY-clause
▪ Is used for presenting a query in sorted
order
▪ Syntax
SELECT <<List_of_columns>
FROM <List_of_tables>
WHERE <Conditions>
ORDER BY <List_of columns>

- ASC: ascending order(default)

- DESC:descending order

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 52

 ORDER BY-clause
▪ Example
SELECT *
FROM THAMGIADT
ORDER BY MAGV DESC, MADT ASC, STT DESC

MAGV MADT STT MAGV MADT STT

GV01 DT01 1 GV02 DT01 3
GV01 DT01 2 GV02 DT01 2
GV01 DT02 1 GV02 DT03 4
GV02 DT01 2 GV02 DT03 1
GV02 DT01 3 GV01 DT01 2
GV02 DT03 1 GV01 DT01 1
GV02 DT03 4 GV01 DT02 1

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 53

 Content
▪ Introduction
▪ Data definition
▪ Data manupulation
- Basic queries
- Set, set/multiset comparison and nested queries
- Aggregate functions and grouping
▪ View
▪ Index

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 54

 Phép toán tập hợp trong SQL
▪ SQL has implemented set operators
- UNION
- INTERSECT
- EXCEPT

▪ The result is a set

- Eliminate identical tuples
- To keep identical tuples
◼ UNION ALL
◼ INTERSECT ALL
◼ EXCEPT ALL

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 55

 Set operations in SQL
 Syntax
SELECT <ColList> FROM <TabList> WHERE <Condition>
UNION [ALL]
SELECT <ColList> FROM <TabList> WHERE < Condition >

SELECT < ColList > FROM < TabList > WHERE < Condition >
INTERSECT [ALL]
SELECT < ColList > FROM < TabList > WHERE < Condition >

SELECT < ColList > FROM < TabList > WHERE < Condition >
EXCEPT [ALL]
SELECT < ColList > FROM < TabList > WHERE < Condition >

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 56

 Example
▪ Cho biết mã của các giáo viên có họ tên bắt đầu là
'Nguyễn' và lương trên 200000 hoặc, giáo viên
là trưởng bộ môn nhận chức sau năm 1995

SELECT MAGV
FROM GIAOVIEN
WHERE HOTEN LIKE N'Nguyễn%'
AND LUONG > 200000
UNION
SELECT TRUONGBM
FROM BOMON
WHERE YEAR(NGAYNHANCHUC)>=1995

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 57

 Example
▪ Tìm những giáo viên vừa là trưởng bộ môn vừa
chủ nhiệm đề tài
SELECT TRUONGBM
FROM BOMON
INTERSECT
SELECT GVCNDT
FROM DETAI

SELECT [Link]
FROM BOMON BM, DETAI DT
WHERE [Link] = [Link]

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 58

 Example
▪ Tìm những giáo viên không tham gia bất kỳ đề
tài nào

SELECT MAGV SELECT MAGV, HOTEN

FROM GIAOVIEN FROM GIAOVIEN
EXCEPT EXCEPT
SELECT MAGV SELECT MAGV, HOTEN
FROM THAMGIADT FROM THAMGIADT DT JOIN GIAOVIEN GV
ON [Link]=[Link]

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 59

 Nested query

SELECT MAGV, HOTEN

FROM GIAOVIEN, BOMON
WHERE TENBM = N'Hệ thống thông tin'
AND [Link] = [Link]

Outer query SELECT <danh sách các cột>

FROM <danh sách các bảng>
WHERE <so sánh tập hợp> (
SELECT <danh sách các cột>
FROM <danh sách các bảng> Subquery

WHERE <điều kiện>)

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 60

 Nested query
▪ Queries can have several nested levels
▪ Subqueries of a WHERE clause are connected
by logical connective
- OR, AND
▪ Subqueries will return
- A single attribute and a single tuple (a single value)
- A table (a set or multiset of tuples)

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 61

 Nested query
▪ WHERE clause of the outer query
- <Expression> <set operation> <subquery>

- Set comparison includes many operators

◼ IN, NOT IN
◼ ALL
◼ ANY hoặc SOME

- Check whether the result of subqueries is empty or not

◼ EXISTS
◼ NOT EXISTS

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 62

 Nested query
▪ Categories
- Non-correlated subqueries
◼ WHERE clause of subqueries do not refer to
attributes of relations in FROM clause of the outer
query
◼ Subqueries will be performed before the outer query,
and be executed just one time
- Correlated subqueries
◼ WHERE clause of subqueries refer to at least one
attribute of relations in FROM clause of the outer
query
◼ Subqueries will be executed many times, each time
will correlate to one tuple of the outer query

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 63

 Example
▪ Tìm những giáo viên là trưởng bộ môn
SELECT MAGV, HOTEN Cartesian product
FROM GIAOVIEN, BOMON
WHERE [Link] = [Link]
SELECT MAGV, HOTEN
FROM GIAOVIEN
SELECT MAGV, HOTEN WHERE MAGV IN (‘001',
FROM GIAOVIEN ‘002', ‘004', ‘005‘, ‘007‘ )
WHERE MAGV IN (SELECT TRUONGBM
Nested query with IN
FROM BOMON

Subquery does not use attributes from relations in the outer query

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 64

 Example

SELECT HOTEN
FROM GIAOVIEN
WHERE MAGV IN (SELECT MAGV
FROM GIAOVIEN
WHERE HOTEN LIKE N'Nguyễn%'
AND LUONG > 200000)
OR MAGV IN (SELECT TRUONGBM
FROM BOMON
WHERE YEAR(NGAYNHANCHUC)>=1995)

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 65

 Example
▪ Tìm những giáo viên không tham gia đề
tài nào
SELECT *
FROM GIAOVIEN
WHERE MAGV NOT IN(SELECT MAGV
FROM THAMGIADT)

SELECT *
FROM GIAOVIEN
WHERE MAGV <> ALL(SELECT MAGV
FROM THAMGIADT)

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 66

 Example
▪ Tìm những giáo viên có lương lớn hơn lương
của ít nhất một giáo viên bộ môn ‘Công nghệ
phần mềm’
SELECT *
FROM GIAOVIEN
WHERE LUONG > ANY (SELECT [Link]
FROM GIAOVIEN GV, BOMON BM
WHERE [Link] = [Link]
AND [Link] = N'Công nghệ phần mềm')

SELECT GV1.*
FROM GIAOVIEN GV1, GIAOVIEN GV2, BOMON BM
WHERE [Link] = [Link]
AND [Link] = N'Công nghệ phần mềm' AND [Link] > [Link]
© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 67
 Example
▪ Tìm những giáo viên có lương lớn hơn lương
của tất cả giáo viên thuộc bộ môn ‘Hệ thống
thông tin’

SELECT *
FROM GIAOVIEN
WHERE LUONG > ALL (SELECT LUONG
FROM GIAOVIEN GV, BOMON BM
WHERE [Link] = [Link]
AND [Link] = N'Hệ thống thông tin')

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 68

 Example
▪ Tìm những trưởng bộ môn tham gia tối thiểu 1
đề tài
SELECT *
FROM GIAOVIEN
WHERE MAGV IN (SELECT TRUONGBM
FROM BOMON)
AND MAGV IN (SELECT MAGV
FROM THAMGIADT)

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 69

 Example
▪ Tìm những giáo viên là trưởng bộ môn
SELECT MAGV, HOTEN
FROM GIAOVIEN Nested query with IN
WHERE MAGV IN (SELECT TRUONGBM FROM BOMON)
SELECT MAGV, HOTEN
FROM GIAOVIEN GV
WHERE EXISTS (SELECT *
Nested query with EXISTS
FROM BOMON BM
WHERE [Link] = [Link])
Giáo viên là trưởng bộ môn khi tồn tại một bộ môn có TRUONGBM =
MAGV của giáo viên đó
Mệnh đề WHERE của truy vấn con tham chiếu ít nhất một thuộc tính của các
quan hệ trong mệnh đề FROM ở truy vấn cha
© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 70
 Example
▪ Tìm những giáo viên có lương lớn nhất
Giáo viên là có lương lớn
SELECT MAGV, HOTEN nhất khi không tồn tại một
FROM GIAOVIEN GV giáo viên nào mà có lương
WHERE NOT EXISTS (SELECT * lớn hơn giáo viên đó
FROM GIAOVIEN GV2
WHERE [Link] > [Link])

SELECT MAGV, HOTEN Giáo viên là có lương lớn

nhất khi lương của giáo viên
FROM GIAOVIEN GV
lớn hơn hoặc bằng tất cả
WHERE LUONG >= ALL (SELECT LUONG lương của các giáo viên (lồng
FROM GIAOVIEN GV2) phân cấp)
© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 71
 Example
▪ Tìm giáo viên trùng tên và cùng giới tính với
giáo viên khác trong cùng bộ môn

SELECT *
FROM GIAOVIEN GV1
WHERE EXISTS (SELECT *
FROM GIAOVIEN GV2
WHERE [Link] LIKE [Link]
AND [Link] = [Link]
AND [Link] = [Link]
AND [Link] <> [Link])

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 72

 Example
▪ Tìm những giáo viên không tham gia đề tài nào

SELECT *
FROM GIAOVIEN GV
WHERE NOT EXISTS (SELECT *
FROM THAMGIADT PC
WHERE [Link] = [Link])

Giáo viên GV không tham gia đề

tài khi không tồn tại một dòng
nào trong THAMGIADT mà có
MAGV = [Link]

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 73

 Example
▪ Tìm những giáo viên có lương lớn hơn lương
của ít nhất một giáo viên bộ môn ‘Công nghệ
phần mềm’

SELECT *
FROM GIAOVIEN GV1
WHERE EXISTS (SELECT *
FROM GIAOVIEN GV2, BOMON BM
WHERE [Link] = [Link]
AND [Link] = N'Công nghệ phần
mềm'
AND [Link] > [Link])

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 74

 Example
▪ Tìm những trưởng bộ môn tham gia tối thiểu 1
đề tài
SELECT *
FROM GIAOVIEN GV
WHERE EXISTS (SELECT *
FROM BOMON BM
WHERE [Link] =[Link])
AND EXISTS (SELECT * FROM THAMGIADT PC
WHERE [Link] = [Link])

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 75

 Discussion IN and EXISTS
▪ IN
- <Column_name> IN <Subquery>
- Attributes in the subquery’s SELECT clause have the
same data types as attributes in the outer query’s WHERE
clause
▪ EXISTS
- Do not need attributes, constants or any expressions
before it
- Do not need to specify column names in the subquery’s
SELECT clause
- Queries containing “= ANY” or IN can be converted
queries containing EXISTS

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 76

 Discussion IN and EXISTS
▪ EXISTS:
- Is used for checking an existing row
- Syntax
- … EXISTS (Select … From … Where…)
Sub query

If the subquery returns at least one row => EXISTS (…) = TRUE
If the sub query returns no row => EXISTS (…) = FALSE

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 77

 Example
▪ Tìm những giáo viên có tham gia đề tài

SELECT *
FROM GIAOVIEN GV
WHERE EXISTS (SELECT *
FROM THAMGIADT PC
WHERE [Link] = [Link])

Nếu giáo viên GV có tham gia đề tài →

câu truy vấn bên trong sẽ có dữ liệu
(>= 1 dòng) → mệnh EXISTS (S .. F…
W) có giá trị TRUE

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 78

 Example
▪ Tìm những giáo viên không tham gia đề tài

SELECT *
FROM GIAOVIEN GV
WHERE NOT EXISTS (SELECT *
FROM THAMGIADT PC
WHERE [Link] = [Link])

Nếu giáo viên GV không tham gia đề

tài → câu truy vấn bên trong sẽ rỗng
(0 dòng) → NOT EXISTS (S…F..W) có
giá trị TRUE

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 79

 Divide operation in SQL
R A B C D E S D E RS A B C

 a  a 1 bi a 1 ai  a 
 a  a 1 b 1  a 
 a  b 1
 a  a 1
 a  b 3
 a  a 1
 a  b 1
 a  b 1

▪ R÷S is a set of values ai in R such that there is no values

bi in S that makes the tuple (ai , bi) does not exist in R

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 80

 Divide operation in SQL
▪ Using EXCEPT
SELECT R1.A, R1.B, R1.C
FROM R R1
WHERE NOT EXISTS (
( SELECT S.D, S.E FROM S)
EXCEPT
( SELECT R2.D, R2.E
FROM R R2
WHERE R1.A=R2.A AND R1.B=R2.B
AND R1.C=R2.C )
)

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 81

 Divide operation in SQL
▪ Using NOT EXISTS
SELECT R1.A, R1.B, R1.C
FROM R R1
WHERE NOT EXISTS (
SELECT *
FROM S
WHERE NOT EXISTS (
SELECT *
FROM R R2
WHERE R2.D=S.D AND R2.E=S.E
AND R1.A=R2.A AND R1.B=R2.B AND R1.C=R2.C ))

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 82

 Example

 Tìm tên các giáo viên được phân công làm tất cả
các đề tài

- Tìm tên các nhân viên mà không có đề án nào là

không được phân công làm

- Tập bị chia: THAMGIADT(MAGV, MADT)

- Tập chia: DETAI(MADT)
- Tập kết quả: KQ(MAGV)
- Kết KQ với GIAOVIEN để lấy ra TENGV

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 83

 Example
SELECT DISTINCT [Link], [Link]
FROM GIAOVIEN GV, THAMGIADT PC1
WHERE [Link] = [Link]

AND NOT EXISTS ( (SELECT [Link] FROM DETAI DT)Toàn bộ đề tài

EXCEPT
Những đề tài
mà giáo viên (SELECT [Link] Những đề tài mà
[Link] FROM THAMGIADT PC2 [Link] đã tham gia
không tham
gia WHERE [Link] = [Link]) )
Nếu danh sachs đề tài chưa tham gia = rỗng →
Giáo viên đã tham gia tất cả các đề tài

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 84

 Example
SELECT DISTINCT [Link], [Link]
FROM GIAOVIEN GV, THAMGIADT PC1
WHERE [Link] = [Link]
AND NOT EXISTS ( SELECT *
FROM DETAI DT
WHERE NOT EXISTS (SELECT *
Những đề tài
mà giáo viên FROM THAMGIADT PC2
[Link] WHERE [Link] = [Link]
không tham gia AND [Link] = [Link]))

Tìm những giáo viên mà danh sách đề tài không tham gia = rỗng

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 85

 Example
SELECT DISTINCT [Link], [Link]
FROM GIAOVIEN GV, THAMGIADT PC1
WHERE [Link] = [Link]
AND 0 = (SELECT COUNT(*)
FROM DETAI DT
WHERE NOT EXISTS (SELECT *
FROM THAMGIADT PC2
WHERE [Link] = [Link]
AND [Link] = [Link]))

Tìm những giáo viên mà số lượng đề tài chưa tham gia bằng 0

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 86

 Divide operation in SQL
▪ Cho R(A,B), S(B), thực hiện RS

SELECT R.A
FROM R
[WHERE R.B IN (SELECT S.B FROM S [WHERE <ĐK>]]
GROUP BY R.A
HAVING COUNT(DISTINCT R.B) = ( SELECT COUNT(S.B)
FROM S
[WHERE <ĐK>])

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 87

 Example

SELECT DISTINCT [Link], [Link]

FROM GIAOVIEN GV, THAMGIADT PC1
WHERE [Link] = [Link]
GROUP BY [Link], [Link]
HAVING COUNT(DISTINCT [Link]) = (SELECT COUNT (MADT)
FROM DETAI DT)

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 88

 Example
▪ Tìm tên các giáo viên được phân công làm tất cả
các đề tài có kinh phí trên 100 triệu?

SELECT DISTINCT [Link], [Link]

FROM GIAOVIEN GV, THAMGIADT PC1
WHERE [Link] = [Link]
AND [Link] IN (SELECT [Link] FROM DETAI WHERE KINHPHI > 100)
GROUP BY [Link], [Link]
HAVING COUNT(DISTINCT [Link]) = (SELECT COUNT (MADT)
FROM DETAI DT
WHERE KINHPHI >100)

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 89

 Exercise
1. Cho biết mã số, họ tên, ngày sinh của giáo viên tham gia
tất cả các công việc của đề tài ‘Ứng dụng hóa học xanh’.
2. Cho biết mã số, họ tên, tên bộ môn và tên người quản lý
chuyên môn của giáo viên tham gia tất cả các đề tài thuộc
chủ đề ‘Nghiên cứu phát triển’.
3. Cho biết họ tên, ngày sinh, tên khoa, tên trưởng khoa của
giáo viên tham gia tất cả các đề tài có giáo viên ‘Nguyễn
Hoài An’ tham gia.
4. Cho biết họ tên giáo viên khoa ‘Công nghệ thông tin’ tham
gia tất cả các công việc của đề tài có trưởng bộ môn của bộ
môn đông nhất khoa ‘Công nghệ thông tin’ làm chủ nhiệm.

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 90

 Content

▪ Introduction
▪ Data definition
▪ Data manupulation
- Basic queries
- Set, set/multiset comparison and nested queries
- Aggregate functions and grouping
- Other
▪ View
▪ Index

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 91

 Aggregate functions
▪ COUNT
- COUNT(*) : the number of rows
- COUNT(<Column_name>): the number of non-zero values of
the column
- COUNT(DISTINCT <Column_name>): the number of
different and non-zero values of the columnMIN
▪ MAX
▪ SUM
▪ AVG

▪ These function is in SELECT and HAVING clause

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 92

 Example
▪ Tìm tổng lương, lương cao nhất, lương thấp nhất
và lương trung bình của các giáo viên

SELECT SUM(LUONG), MAX(LUONG), MIN(LUONG), AVG(LUONG)

FROM GIAOVIEN

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 93

 Example

▪ Cho biết số lượng giáo viên của bộ môn

‘Mạng máy tính’

SELECT COUNT(*) AS SL_GV

FROM GIAOVIEN GV, BOMON BM
WHERE [Link] = [Link]
AND TENBM=N'Mạng máy tính'

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 94

 Example
▪ Tìm những giáo viên có lương thuộc 3 mức
lương cao nhất
SELECT *
FROM GIAOVIEN GV1
WHERE 2 >= (SELECT COUNT(*)
FROM GIAOVIEN GV2
WHERE [Link] > [Link])

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 95

 Example
▪ Cho biết số lượng giáo viên của từng bộ môn
Bộ môn Số lượng
HTTT 2
CNPM 1
MMT 1

MANV HOTEN … MABM

GV001 Nguyễn Văn A … HTTT

GV002 Trần Văn B … HTTT

GV003 Trần Thị C … CNPM

GV004 Đặng Thị D … MMT

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 96

 Grouping
 Syntax
SELECT <ColumnList>
FROM <TableList>
WHERE <Conditions>
GROUP BY <List_of_grouping_columns>

 After grouping
- Each group will have identical values at grouping
attributes

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 97

 Example
▪ Cho biết số lượng giáo viên của từng bộ môn

SELECT MABM, COUNT(*) 'Số lượng giáo viên'

FROM GIAOVIEN
GROUP BY MABM

SELECT [Link], COUNT(*) 'Số lượng giáo viên'

FROM GIAOVIEN GV, BOMON BM
WHERE [Link] = [Link]
GROUP BY [Link]

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 98

 Example
▪ Với mỗi giáo viên cho biết mã số, mã đề tài
và số công việc mà họ tham gia ứng với mỗi
đề tài
MAGV MADT STT
GV001 DT001 1
GV001 DT001 2
GV001 DT002 1
GV002 DT002 2
GV003 DT001 3
GV003 DT002 3

SELECT [Link], [Link], COUNT(*) AS 'Số lượng công việc'

FROM THAMGIADT PC
GROUP BY [Link], [Link]

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 99

 Example
▪ Cho biết những giáo viên tham gia từ 2 công
việc trở lên cho mỗi đề tài?
MAGV MADT STT
GV001 DT001 1
GV001 DT001 2
GV001 DT002 1
GV002 DT002 2
GV003 DT001 3
GV003 DT002 3

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 100

 Conditions on groups
▪ Syntax
SELECT <ColumnList>
FROM <TableList>
WHERE <Conditions>
GROUP BY <List_of_grouping_columns>
HAVING < Conditions >

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 101

 Example
▪ Cho biết những giáo viên tham gia từ 2 công việc
trở lên cho mỗi đề tài?
SELECT [Link], [Link], COUNT(*) AS 'Số lượng công việc'
FROM THAMGIADT PC
GROUP BY [Link], [Link]
HAVING COUNT(*) >= 2

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 102

 Example
▪ Cho biết những giáo viên tham gia từ 2 đề tài trở
lên
MAGV MADT STT
GV001 DT001 1
GV001 DT001 2
GV001 DT002 1
GV002 DT002 2
GV003 DT001 3
GV003 DT002 3

SELECT [Link], COUNT(DISTINCT MADT) AS 'Số lượng đề tài'

FROM THAMGIADT PC
GROUP BY [Link]
HAVING COUNT(DISTINCT MADT) >= 2

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 103

 Example
▪ Cho biết những bộ môn (TENBM) có lương trung
bình của các giáo viên lớn hơn 20000
SELECT [Link], AVG([Link]) AS 'Lương trung
bình'
FROM GIAOVIEN GV
GROUP BY [Link]
HAVING AVG([Link])>20000

SELECT [Link], AVG([Link]) AS 'Lương trung bình'

FROM GIAOVIEN GV, BOMON BM
WHERE [Link] = [Link]
GROUP BY [Link], [Link]
HAVING AVG([Link])>=20000

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 104

 Discussion

▪ GROUP BY -clause
- Attributes in SELECT clause (excepting attributes of
aggregate functions) must appear in GROUP BY-clause
▪ HAVING-clause
- Use aggregate functions in SELECT-clause to check a certain
condition
- Just validate the conditions for groups, not a condition for
filtering rows
- After grouping, conditions on groups will be performed

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 105

 Discussion

▪ The order of the query execution

▪ (1) Pick out rows that satisfy conditions in the WHERE
clause
▪ (2) Group these rows into many groups in GROUP BY-clause
▪ (3) Apply aggregate functions for each group
▪ (4) Eliminate groups that do not satisfy conditions in the
HAVING-clause
▪ (5) Retrieve values from columns and aggregate functions in
SELECT clause

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 106

 Example
▪ Tìm những phòng ban có lương trung bình cao
nhất

SELECT [Link], AVG([Link]) AS 'Lương trung bình'

FROM GIAOVIEN GV
GROUP BY [Link]
HAVING AVG([Link])) = (SELECT MAX(AVG([Link]))
FROM GIAOVIEN GV
GROUP BY [Link])

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 107

 Example
▪ Tìm những phòng ban có lương trung bình cao
nhất
SELECT [Link], AVG([Link]) AS 'Lương trung bình'
FROM GIAOVIEN GV
GROUP BY [Link]
HAVING AVG([Link])>= ALL(SELECT AVG([Link])
FROM GIAOVIEN GV
GROUP BY [Link])

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 108

 Example
▪ Tìm tên các giáo viên được phân công làm tất cả
các đề tài

SELECT [Link], COUNT(DISTINCT [Link]) AS 'Số lượng đề tài'

FROM THAMGIADT PC
GROUP BY [Link]
HAVING COUNT(DISTINCT [Link]) = (SELECT COUNT(MADT)
FROM DETAI)

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 109

 Content
▪ Introduction
▪ Data definition
▪ Data manupulation
- Basic queries
- Set, set/multiset comparison and nested queries
- Aggregate functions and grouping
- Others
▪ View
▪ Index

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 110

 Other queries

▪ Subquery in FROM clause

▪ Joining conditions in FROM clause

▪ Natural join
▪ Outer join

▪ CASE structure

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 111

 Subquery in FROM clause
▪ The result of a subquery is a table
▪ Intermediate table in the process of query execution
▪ Do not store this result into the database

▪ Syntax
SELECT <ColumnList>
FROM R1, R2, (<Subquery>) AS Table_name
WHERE <Conditions>

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 112

 Example
▪ Cho biết những bộ môn (TENBM) có lương trung
bình của các giáo viên lớn hơn 20000

SELECT [Link], AVG([Link]) AS LUONG_TB

FROM GIAOVIEN GV, BOMON BM
WHERE [Link] = [Link]
GROUP BY [Link], [Link]
HAVING AVG([Link])>=20000

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 113

 Example

▪ Cho biết những bộ môn (TENBM) có lương trung

bình của các giáo viên lớn hơn 20000

SELECT [Link], LUONG_GV.LUONG_TB

FROM BOMON BM, (SELECT MABM, AVG(LUONG) LUONG_TB
FROM GIAOVIEN
GROUP BY MABM) AS LUONG_GV
WHERE [Link] = LUONG_GV.MABM

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 114

 Join conditions in FROM clause
▪ Equijoin
SELECT <ColumnList>
FROM R1 [INNER] JOIN R2 ON <Expression>
WHERE <Conditions>

▪ Outer join
SELECT < ColumnList >
FROM R1 LEFT|RIGHT [OUTER] JOIN R2 ON < Expression >
WHERE < Conditions >

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 115

 Example
▪ Tìm mã và tên các giáo viên làm việc tại bộ môn
‘Hệ thống thông tin’

SELECT MAGV, HOTEN

FROM GIAOVIEN, BOMON
WHERE TENBM = N'Hệ thống thông tin'
AND [Link] = [Link]

SELECT MAGV, HOTEN

FROM GIAOVIEN GV INNER JOIN BOMON BM ON [Link] = [Link]
WHERE TENBM = N'Hệ thống thông tin'

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 116

 Example
▪ Tìm họ tên các giáo viên và tên các đề tài giáo viên
tham gia nếu có

SELECT DISTINCT GV.*

FROM GIAOVIEN GV LEFT JOIN (THAMGIADT PC JOIN DETAI DT ON
[Link] = [Link]) ON [Link] = [Link]

GIAOVIEN GIAOVIEN JOIN THAMGIADT

[Link]= [Link]
extend

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 117

 CASE structure
▪ Allow us to check conditions or output the
information in each case

▪ Syntax
CASE <Column_name>
WHEN <value> THEN <Expression>
WHEN < value > THEN <Expression>
…
[ELSE < Expression >]
END

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 118

 Example
▪ Cho biết họ tên các giáo viên đã đến tuổi về hưu
(nam 60 tuổi, nữ 55 tuổi)

SELECT HOTEN
FROM GIAOVIEN
WHERE YEAR(GETDATE()) - YEAR(NGAYSINH) >= ( CASE PHAI
WHEN 'Nam' THEN 60
WHEN 'Nu' THEN 55
END)

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 119

 Example
▪ Cho biết họ tên các giáo viên và năm về hưu
SELECT [Link], YEAR([Link]) + ( CASE PHAI
WHEN 'Nam' THEN 60
WHEN 'Nu' THEN 55
END) AS NAMVEHUU
FROM GIAOVIEN GV

SELECT [Link], ( CASE PHAI

WHEN 'Nam' THEN YEAR(NGAYSINH) + 60
WHEN 'Nu' THEN YEAR(NGAYSINH) + 55
END) AS NAMVEHUU
FROM GIAOVIEN GV

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 120

 Summary

SELECT <List_of_columns>
FROM <List_of_tables>
[WHERE <Conditions>]
[GROUP BY <List_grouping_columns>]
[HAVING <Conditions>]
[ORDER BY <List_of_ordering_columns>]

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 121

 Content

▪ Introduction
▪ Data definition
▪ Data manipulation
▪ Data update
▪ View
▪ Index

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 122

 INSERT command
▪ Is used to add 1 or more tuple(s) to a
relation

▪ In order to add a tuple

– Relation name
– List of column names
– List of values for the tuple

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 123

 INSERT command
▪ Syntax (one tuple)

INSERT INTO <Table_name>(<List_of_columns>)

VALUES (<List_of_values>)

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 124

 Example

INSERT INTO THAMGIADT(MAGV, MADT, STT, PHUCAP, KETQUA)

VALUES(’002', ‘001', 1, 1.0, N’ĐẠT')

INSERT INTO THAMGIADT(MAGV, MADT, STT, PHUCAP, KETQUA)

VALUES(’002', ‘001', 2, 1.2, NULL)

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 125

 INSERT command
▪ Discussion
– The order of values is the same to the order of columns
– The NULL value can be used for non-primary-key attributes
– INSERT command will raise errors if the integrity constraint
is violated
▪ Primary key
▪ Reference
▪ NOT NULL constraint

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 126

 INSERT command
▪ Syntax (many tuples)

INSERT INTO <Table_name>(<List_of_columns>)

<Query>

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 127

 Example
CREATE TABLE THONGKE_BM (
TENBM NVARCHAR(50),
SL_GV INT,
LUONG_TC INT
PRIMARY KEY(TENBM)
)

INSERT INTO THONGKE_BM

SELECT [Link], COUNT([Link]), SUM([Link])
FROM GIAOVIEN GV, BOMON BM
WHERE [Link] = [Link]
GROUP BY [Link], [Link]

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 128

 DELETE command
▪ Is used to remove tuples from a relation

▪ Syntax
DELETE FROM <Table_name>
[WHERE <Conditions>]

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 129

 Example

DELETE FROM GIAOVIEN

WHERE HOTEN LIKE N'Trần%'

DELETE FROM GIAOVIEN

WHERE MAGV = 'GV001'

DELETE FROM GIAOVIEN

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 130

 Example
▪ Xóa đi những giáo viên ở bộ môn ‘Hệ thống thông
tin’
DELETE FROM GIAOVIEN
WHERE MABM IN (SELECT MABM
FROM BOMON
WHERE TENBM = N'Hệ thống thông tin')

DELETE FROM GIAOVIEN

FROM BOMON BM
WHERE [Link] = [Link]
AND [Link] = N'Hệ thống thông tin'

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 131

 DELETE command
▪ Discussion
– The number of removed tuples depends on the condition in
WHERE clause
– A missing WHERE clause specifies that all tuples can be
deleted
– DELETE command can cause the violation of reference
constraints
▪ Do not permit to remove
▪ Remove tuples whose value is being referred
▪ CASCADE
▪ Set the NULL value to reference values

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 132

 DELETE command
MAGV HOTEN … MABM

GV001 Nguyễn Văn A … HTTT

GV002 Trần Văn B … HTTT

GV003 Trần Thị C … CNPM

GV004 Đặng Thị D … MMT

MAGV MADT STT PHUCAP KETQUA

GV001 001 1 ... ...

GV001 001 3 ... ...

GV003 002 1 ...

GV004 003 1 ... ...

... ... ... ... ...

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 133

 DELETE command
MABM TENBM

HTTT Hệ thống thông tin

CNPM Công nghệ phần mềm

MMT Mạng máy tính

KHMT Khoa học máy tính

MANV HOTEN … MABM

GV001 Nguyễn Văn A … NULL

HTTT

GV002 Trần Văn B … NULL

HTTT

GV003 Trần Thị C … CNPM

GV004 Đặng Thị D … MMT

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 134

 UPDATE command

▪ Is used to change the value of attributes

▪ Syntax
UPDATE <Table_name>
SET <Attribute_name>=<The_new_value>,
<Attribute_name>=<The_new_value>,
…
[WHERE <Conditions>]

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 135

 Example

UPDATE GIAOVIEN
SET NGAYSINH='08/12/1965'
WHERE MAGV='GV001'

UPDATE GIAOVIEN
SET LUONG=LUONG*1.1

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 136

 Example
▪ Với mỗi giáo viên của bộ môn ‘Hệ thống thông tin’,
nâng lương của các giáo viên gấp 1.5 lần và gán
giáo viên quản lý (GVQL) thành null

UPDATE GIAOVIEN
SET LUONG = LUONG*1.5, GVQL = NULL
WHERE MABM = (SELECT MABM
FROM BOMON
WHERE TENBM = N'Hệ thống thông tin')

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 137

 Example
▪ Tăng 10% lương cho giáo viên có tham gia đề tài
UPDATE GIAOVIEN
SET LUONG = LUONG * 1.1
FROM THAMGIADT TG
WHERE [Link] = [Link]

UPDATE GIAOVIEN UPDATE GIAOVIEN

SET LUONG = LUONG * 1.1 SET LUONG = LUONG * 1.1
WHERE EXISTS ( WHERE MAGV IN (
SELECT * SELECT [Link]
FROM THAMGIADT TG FROM THAMGIADT TG
WHERE [Link] = [Link] ) WHERE [Link] = [Link] )

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 138

 Example
▪ Tăng lương 10% cho tất cả các giáo viên làm cho
đề tài ‘DT001’ nhiều hơn 3 công việc.
UPDATE GIAOVIEN
SET LUONG = LUONG * 1.1
WHERE MAGV IN (SELECT [Link]
FROM THAMGIADT TG
WHERE [Link] = 'DT001'
GROUP BY [Link], [Link]
HAVING COUNT(*) >= 3)

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 139

 UPDATE command
▪ Discussion
– Tuples that satisfy conditions in WHERE clause will be
modified to the new value
– A missing WHERE clause specifies that all tuples can be
modified
– UPDATE command can cause violations of the reference
constraint
▪ Do not allow to modify
▪ Modify the values of tuples that are being referred
▪ CASCADE

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 140

 Content
▪ Introduction
▪ Data definition
▪ Data manipulation
▪ Data update
▪ View
▪ Index

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 141

 View
▪ Table is a relation that exist actually the database
▪ Stored in some physical organization
▪ Persistent

▪ View is also a relation

– Do not exist physically (virtual table)
– Do not contain the data
– Is derived from other tables
– Can query or even modify the data through views

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 142

 View
▪ Why do we use views?
– Hide the complexity of data
– Simplify queries
– Present data with the purpose “easy to use”
– Mechanism of data safety

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 143

 View definition
▪ Syntax
CREATE VIEW <View_name> AS
<Query>

DROP VIEW <View_name>

▪ View contains
- A list of attributes that are the same as attributes in SELECT
clause
- The number of tuples depending on the conditions in
WHERE clause
- Data derived from tables in FROM clause

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 144

 Example

CREATE VIEW GV_HTTT AS

SELECT GV.*
FROM GIAOVIEN GV
WHERE [Link] = 'HTTT'

CREATE VIEW THONGKE_BM AS

SELECT [Link], COUNT([Link]) SL_GV,
SUM([Link]) TONG_LUONG
FROM GIAOVIEN GV, BOMON BM
WHERE [Link] = [Link]
GROUP BY [Link], [Link]

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 145

 Querying views
▪ Although views do not contain data, we can do the
query on views
SELECT [Link]
FROM GV_HTTT GV
WHERE [Link] = 'GV003'

GV_HTTT  MABM='HTTT' (GIAOVIEN)

 HOTEN 
( MAGV='GV003' (GV_HTTT))

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 146

 Querying views
▪ Can query data from both tables and views
SELECT DISTINCT GV.*
FROM GV_HTTT GV, THAMGIADT PC
WHERE [Link] = [Link]

GV_HTTT  MABM='HTTT' (GIAOVIEN)

KQ  GV_HTTT GV_HTTT.MAGV=[Link] THAMGIADT

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 147

 Modifying views
▪ Can apply INSERT, DELETE, and UPDATE
commands to simple views
- Views built on one table and having the key attribute of that
table
▪ Cannot modify views
- Views have a key word DISTINCT
- Views use aggregate functions
- Views have extended SELECT clause
- Views are derived from table containing constrains on
columns
- Views are derived from many tables

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 148

 Modifying views
▪ Sửa lại lương cho giáo viên mã ‘GV003’ ở bộ
môn 'Hệ thống thông tin' tăng lên 10%
UPDATE GV_HTTT
SET LUONG = LUONG * 1.1
WHERE MAGV = 'GV003'

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 149

 Content
▪ Introduction
▪ Data definition
▪ Data manipulation
▪ Data update
▪ View
▪ Index

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 150

 Index
▪ The index on an attribute A is the data structure
that makes it efficient to find tuples having a fixed
value for attribute A

SELECT *
FROM GIAOVIEN Đọc 10.000 bộ
WHERE MABM='HTTT' AND PHAI=‘Nu’

Đọc 200 bộ

Bảng GIAOVIEN có 10.000 bộ

Có 200 giáo viên làm việc cho bộ môn ‘HTTT’ Đọc 70 bộ

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 151

 Index
▪ Syntax
CREATE INDEX <Index_name> ON <Table_name>(<Column_name>)

DROP INDEX <Index_name>

▪ Example
CREATE INDEX MABM_IND ON GIAOVIEN(MABM)

CREATE INDEX MABM_PHAI_IND ON GIAOVIEN(MABM, PHAI)

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 152

 Index
▪ Discussion
- Speed up queries in which a value for an attribute is
specified, join operations
- Make insertion, deletion, and update more complex and
time-consuming
- Cost
◼ Index storage
◼ Disk access (HDD)

▪ Selection of indexes
- One of the principal factors that influence a database
- One of the hardest parts of database design

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 153

© Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 154