EXISTS, NOT EXISTS, GROUP BY, JOIN, UNION, UNION ALL trong SQL

Cú pháp EXISTS và NOT EXISTS

SELECT column_name

FROM table_name

WHERE [NOT] EXISTS

(SELECT column_name

FROM table_name

WHERE condition)

Subquery trả về giá trị True hoặc False

Ví dụ: Lấy họ và tên của customer đã đặt ít nhất 1 đơn hàng

Cho 2 bảng:

Bảng customers: fname, lname

Bảng orders: order_id, customer_id

SELECT fname, lname

FROM customers

WHERE EXISTS

(SELECT *

FROM orders

WHERE customers.customer_id = orders.customer_id)

Xóa bản ghi các dòng trong bảng order khi customer có họ là ‘Mehra’.

DELETE *

FROM customer

WHERE EXISTS

(SELECT *

FROM customer

WHERE customer.customer_id=orders.customer_id AND customer.lname = ‘Mehra’

Cú pháp Group By

SELECT column1, SUM (column2)

FROM table_name

WHERE condition

GROUP BY col1, col2

HAVING condition

ORDER BY col1, col2

Group by with Count, Aggregate functions (Max, Min, Avg…)

Bài tập

Write a query to list the `name` of `courses` that do not have a book published in `2014`. Set the header for the single column of results, to be `name`.

Bảng students

id	name	age	grade	marks	courseId	studentMentorId
1	Johnson	12	6	80	1	ㅤ
2	Harding	6	1	94	2	16
3	Taft	7	2	77	3	ㅤ
4	Hoover	13	7	30	4	9
5	Lincoln	12	6	81	5	ㅤ
6	Garfield	15	9	91	6	10
7	Polk	12	6	49	7	ㅤ
8	Grant	16	10	25	8	ㅤ
9	Bruce	13	7	51	3	ㅤ
10	Tony	14	9	81	1	22
11	Peter	15	10	5	3	8
12	Andrew	18	12	49	6	ㅤ
13	Edgar	ㅤ	8	24	4	ㅤ
14	Abraham	13	ㅤ	26	5	11
15	Hugh	16	10	12	ㅤ	ㅤ
16	Tonya	ㅤ	1	6	2	ㅤ
17	Murray	14	ㅤ	91	7	15
18	Descartes	12	6	28	8	ㅤ
19	Cisco	12	6	65	13	18
20	Barry	13	7	42	12	ㅤ
21	Kara	15	8	12	ㅤ	25
22	Oliver	16	9	86	11	ㅤ
23	Kate	17	10	55	9	24
24	Cobb	18	11	59	20	ㅤ
25	Paige	19	12	98	3	ㅤ

Bảng courses

id	name	abbreviatedName
1	JavaScript	js
2	Java	java
3	Python	py
4	Ruby	rb
5	Rust	rs
6	C#	cs
7	SQL	sql
8	PHP	php
9	C++	cpp
10	C	c
11	Objective-C	objc
12	Swift	swft
13	Scala	sc

Bảng books

id	name	price	publishedIn	publisherName	courseID	authorID	nextVolumeId
1	Basics of JavaScript - Volume 1	647	2015	Monarch Press	1	10	13
2	Basics of Java - Volume 1	491	2012	HYDRA Inc.	2	5	4
3	Advanced Python - The Ultimate Edition	531	2010	SHIELD Books	3	8	ㅤ
4	Basics of Java - Volume 2	995	2013	HYDRA Inc.	2	5	9
5	Objective-C for Beginners - 1st Edition	722	2017	Ten Rings Pvt Ltd	11	2	32
6	C++ Basics - Volume 1	165	2014	SHIELD Books	9	1	7
7	C++ Basics - Volume 2	320	2014	SHIELD Books	9	1	ㅤ
8	Basics of Rust	614	2018	AIM Books	5	4	ㅤ
9	Basics of Java - Volume 3	515	2014	HYDRA Inc.	2	5	15
10	Building Basic Apps in Ruby - Part 1	194	2013	Ten Rings Pvt Ltd	4	6	11
11	Building Basic Apps in Ruby - Part 2	236	2013	Ten Rings Pvt Ltd	4	8	12
12	Building Basic Apps in Ruby - Part 3	178	2014	Ten Rings Pvt Ltd	4	6	ㅤ
13	Basics of JavaScript - Volume 2	799	2017	Monarch Press	1	9	ㅤ
14	Advanced Concepts in Rust - Part 1	113	2018	AIM Books	5	4	16
15	Basics of Java - Volume 4	142	2016	HYDRA Inc.	2	5	ㅤ
16	Advanced Concepts in Rust - Part 2	851	2019	AIM Books	5	4	ㅤ
17	Basics of Python - The Ultimate Edition	612	2011	SHIELD Books	3	8	ㅤ
18	JAVA for Professionals	724	2011	HYDRA Inc.	2	5	ㅤ
19	Advanced Algorithms in C++ - The Ultimate Edition	193	2015	SHIELD Books	9	1	ㅤ
20	PHP for Beginners and Professionals - The Ultimate Guide	199	2010	Monarch Press	8	3	ㅤ
21	Advanced Scala for Research - Volume 1	593	2013	Monarch Press	13	6	23
22	Introduction to Elixir on Phoenix	603	2013	AIM Books	ㅤ	10	ㅤ
23	Advanced Scala for Research - Volume 2	599	2014	Monarch Press	13	6	ㅤ
24	Advanced JavaScript, The Collector's Edition	632	2016	Monarch Press	1	9	ㅤ
25	Advanced Java - Volume 1	998	2014	HYDRA Inc.	2	5	26
26	Advanced Java - Volume 2	234	2015	HYDRA Inc.	2	5	ㅤ
27	Building Apps with Ruby on Rails - Part 1	104	2015	Ten Rings Pvt Ltd	4	6	30
28	C# and ASP.NET - The Beginners Edition	786	2008	HYDRA Inc.	6	ㅤ	ㅤ
29	Basic Concepts in Elm - Volume 1	297	2011	AIM Books	ㅤ	ㅤ	34
30	Building Apps with Ruby on Rails - Part 2	164	2015	Ten Rings Pvt Ltd	4	6	ㅤ
31	C# and ASP.NET - The Professionals Edition	156	2009	HYDRA Inc.	6	1	ㅤ
32	Objective-C for Beginners - 2nd Edition	324	2018	Ten Rings Pvt Ltd	11	2	ㅤ
33	Basics of Scala	889	2014	Monarch Press	13	6	ㅤ
34	Basic Concepts in Elm - Volume 2	155	2012	AIM Books	ㅤ	6	ㅤ

Bảng authors

id	name	gender	codeName
1	Steve Strange	M	Phil
2	Bruce Quill	M	Grant
3	Natasha Rogers	F	Melinda
4	Clint Banner	M	Barry
5	Wanda Romanoff	F	Simmons
6	Tony Barton	M	Leo
7	Scott Maximoff	M	Garfield
8	Hope Stark	F	Tonya
9	Stephen Lang	M	Odin
10	Peter Pym	M	Bilbo

SELECT name
FROM courses
WHERE NOT EXISTS
(SELECT *
FROM books
WHERE books.courseID=courses.id AND books.publishedIn = '2014');

Find maximum marks obtained by students in each grade.

Bảng students

id	name	age	grade	course	marks
1	Johnson	12	6	JavaScript	80
2	Harding	6	1	Java	94
3	Taft	7	2	Python	77
4	Hoover	13	7	Ruby	30
5	Lincoln	12	6	Rust	81
6	Garfield	15	9	C#	91
7	Polk	12	6	SQL	49
8	Grant	16	10	PHP	25
9	Bruce	13	7	Python	51
10	Tony	14	9	JavaScript	81
11	Peter	15	10	Python	5
12	Andrew	18	12	C#	49
13	Edgar	ㅤ	8	Ruby	24
14	Abraham	13	ㅤ	Rust	26
15	Hugh	16	10	ㅤ	12
16	Tonya	ㅤ	1	Java	6
17	Murray	ㅤ	ㅤ	SQL	91
18	Descartes	12	6	PHP	28

SELECT grade, MAX(marks)
FROM students
WHERE grade IS NOT NULL
GROUP BY grade

SELF JOIN?

The table students contains a column studentMentorId. It stores the id of another student who is a mentor of the student in a particular row.

Let's say we want to display the name of all students who have a mentor, along with the name of their mentors. Let's name the headers studentName and mentorName respectively.

In this case, the data for both our result columns, belongs to one and the same column in students, i.e. name. So, we need join the table students with itself. This kind of a JOIN is called a SELF JOIN.

id	name	age	grade	marks	courseId	studentMentorId
1	Johnson	12	6	80	1	ㅤ
2	Harding	6	1	94	2	16
3	Taft	7	2	77	3	ㅤ
4	Hoover	13	7	30	4	9
5	Lincoln	12	6	81	5	ㅤ
6	Garfield	15	9	91	6	10
7	Polk	12	6	49	7	ㅤ
8	Grant	16	10	25	8	ㅤ
9	Bruce	13	7	51	3	ㅤ
10	Tony	14	9	81	1	22
11	Peter	15	10	5	3	8
12	Andrew	18	12	49	6	ㅤ
13	Edgar	ㅤ	8	24	4	ㅤ
14	Abraham	13	ㅤ	26	5	11
15	Hugh	16	10	12	ㅤ	ㅤ
16	Tonya	ㅤ	1	6	2	ㅤ
17	Murray	14	ㅤ	91	7	15
18	Descartes	12	6	28	8	ㅤ
19	Cisco	12	6	65	13	18
20	Barry	13	7	42	12	ㅤ
21	Kara	15	8	12	ㅤ	25
22	Oliver	16	9	86	11	ㅤ
23	Kate	17	10	55	9	24
24	Cobb	18	11	59	20	ㅤ
25	Paige	19	12	98	3	ㅤ

SELECT s1.name AS studentName,
s2.name AS mentorName
FROM   students s1 JOIN students s2
ON     s1.studentMentorId = s2.id

Khi nào dùng UNION hay UNION ALL?

Dùng lại bảng books và students từ các ví dụ trên.

Let's say, we want to print id cards for all the students and all the books. An id card for a student will just have the student's id, and an id card for a book will just contain the book's id. For this, we have to print all the ids in a single result, under a single column.

SELECT id
FROM students
UNION
SELECT id
FROM books

The ids for students range from 1 to 25, and for books, range from 1 to 34.

The results of the above query are just 1 to 34.

This helps explain that if there are matching records from multiple queries in a UNION, only one of them will be included in the final results. That's another way of saying that the results of a UNION are always unique.

Then how do we solve our problem? We need all 25 student ids and all 34 book ids. In such cases, there is an extension to UNION, called UNION ALL.

SELECT id
FROM students
UNION ALL
SELECT id
FROM books

Xem thêm về EXCEPT và INTERSECT.

Bài tập lớn

CREATE DATABASE nhasachnhanam; USE nhasachnhanam; CREATE TABLE dm_sach (ma_dmsach INT PRIMARY KEY AUTO_INCREMENT, the_loai VARCHAR(50), mo_ta VARCHAR (50));

CREATE TABLE dm_dochoi (ma_dmdochoi INT PRIMARY KEY AUTO_INCREMENT, nhom VARCHAR(50), mo_ta VARCHAR (50));

CREATE TABLE dm_dungcu (ma_dmdungcu INT PRIMARY KEY AUTO_INCREMENT, khoi VARCHAR(50), mo_ta VARCHAR (50));

CREATE TABLE sanpham_sach (masp_sach VARCHAR(50) PRIMARY KEY, tensp_sach VARCHAR(50), so_luong INT, don_gia INT, don_vi VARCHAR(50), ma_dmsach INT, FOREIGN KEY (ma_dmsach) REFERENCES dm_sach(ma_dmsach), nha_xuat_ban VARCHAR(50), nam_xuat_ban YEAR, tac_gia VARCHAR(50), ngay_xuat_ban DATETIME, lan_tai_ban INT);

CREATE TABLE sanpham_dochoi (masp_dochoi VARCHAR(50) PRIMARY KEY, tensp_dochoi VARCHAR(50), so_luong INT, don_gia INT, don_vi VARCHAR(50), ma_dmdochoi INT, FOREIGN KEY (ma_dmdochoi) REFERENCES dm_dochoi(ma_dmdochoi), xuat_su VARCHAR(50), thuong_hieu VARCHAR(50), nha_cung_cap VARCHAR(50), huong_dan VARCHAR(50));

INSERT INTO sanpham_dungcu (masp_dungcu, tensp_dungcu, so_luong, don_gia, don_vi, ma_dmdungcu, xuat_su, thuong_hieu, nha_cung_cap, mau_sac, kich_thuoc, chat_lieu, huong_dan)

VALUES ('Dungcu1','Vo',100,4000, 'VND', 1, 'Thai Lan', 'ZZZ', 'Trang Tien', 'Xanh', 'S', 'Giay', 'AAA'), ('Dungcu2','Dong ho',6000,2000000, 'VND', 2, 'My', 'YYY', 'Dong Hoi', 'Do', 'L', 'Go', 'BBB'), ('Dungcu3','May tinh',10,10000000, 'VND', 3, 'Duc', 'NNN', 'Ha Anh', 'Xam', 'M', 'Nhua', 'DDD');

SQL liên quan gì đến Testing?

Ví dụ bạn có chức năng tạo mới user và hiển thị list user chính xác.

Tuy nhiên chức năng list user chưa hoàn thiện nên khi tạo mới user xong bạn có thể xác nhận bằng cách kiểm tra trong database xem đã tạo bản ghi mới là lưu đúng các trường, đúng value hay chưa.

Việc lưu trữ các thông tin bằng excel thì quản lý rất khó khăn. Trong trường hợp bạn muốn thêm hay sửa xóa thông tin ai đó sẽ mất nhiều thời gian. SQL sẽ giúp bạn quản lý và truy vấn thông tin nhanh hơn và bảo trì thông tin cũng dễ dàng hơn.

Có rất nhiều test case đòi hỏi môi trường test trong sạch (tức là clear hết dữ liệu), thay vì ngồi xóa thủ công mất rất nhiều thời gian, thậm chí có những dữ liệu không thể xóa trên giao diện, bạn nên sử dụng câu lệnh Delete để tiết kiệm thời gian và đảm bảo dữ liệu được dọn dẹp sạch sẽ.

Dummy Data: Bạn tạo ra 1 bộ data test với những data bạn cần. Khi tạo data, mình dùng câu lệnh INSERT, hoặc những website như https://www.mockaroo.com/ sẽ giúp bạn làm chuyện này tốt hơn.

Tham khảo

Exercise - Subqueries - NOT EXISTS operator | BigBinary Academy

SQL testing (Phần 1 - Ứng dụng của SQL trong testing) (viblo.asia)

Tầm quan trọng của SQL đối với một Tester | Anh Tester

EXISTS, NOT EXISTS, GROUP BY, JOIN, UNION, UNION ALL trong SQL

Cú pháp EXISTS và NOT EXISTS

Ví dụ: Lấy họ và tên của customer đã đặt ít nhất 1 đơn hàng

Xóa bản ghi các dòng trong bảng order khi customer có họ là ‘Mehra’.

Cú pháp Group By

Bài tập

Write a query to list the name of courses that do not have a book published in 2014. Set the header for the single column of results, to be name.

Find maximum marks obtained by students in each grade.

SELF JOIN?

Khi nào dùng UNION hay UNION ALL?

Bài tập lớn

SQL liên quan gì đến Testing?

Tham khảo

Write a query to list the `name` of `courses` that do not have a book published in `2014`. Set the header for the single column of results, to be `name`.