M01W3.5_SQL Advance

• 1. SQL Join
  • Inner join
  • Self join
  • Joinning multiple table
  • Compound join condition
  • Implicit join syntax
  • Outer join
  • Using clause
  • Unions
• 2. Common Table Expression
• 3. SQL Subqueries
• 4. SQL Temp Table
• 5. SQL Store Procedures
• 6. SQL Trigger
• 7. Practice
  • Pratice 1
  • Pratice 2
  • Pratice 3
  • Pratice 4
  • Pratice 5
  • Pratice 6
  • Pratice 7
  • Pratice 8
  • Pratice 9
  • Pratice 10
  • Pratice 11
  • Pratice 12
  • Pratice 13
  • Pratice 14
  • Pratice 15
  • Kahoot
• 8. Recursive in SQL (Đệ quy trong SQL)
• 9. Data Mining
  • Case study

1. SQL Join

Inner join

Get the first and last name of every customer placing orders

SELECT * 
FROM orders 
INNER JOIN customers ON orders.customer_id = customers.customer_id;

Self join

Joinning multiple table

Compound join condition

Implicit join syntax

Outer join

Using clause

Unions

Hãy xem kết quả của Query sau:

SELECT customer_id, first_name, points
FROM customers;

Nếu ta muốn rank những customer này bằng các cấp bậc

• < 2000 point: Bronze
• 2000 - 3000 point: Silver

• 3000 point: Gold Viết query cho từng nhóm: Kết hợp các bảng lại: ⇒ Vậy Union để gộp các bảng query lại với nhau

2. Common Table Expression

Using the sql_invoicing database, how would you find the client with the highest total invoice amount? → We need to sum all the invoice values, group by the client id, then we have to order them by the sum. After that we take the first client id, which is the person have the highest total invoice and query their infomation. How can we write this query? Cấu trúc tổng quát:

WITH ten_cte AS (
	SELECT ...
	FROM ...
	WHERE ...
)
SELECT *
FROM ten_cte
WHERE ...;

Query cho ví dụ trên: Ý tưởng: tìm ra được client có invoice cao nhất:

SELECT name
FROM clients
WHERE client_id = <highest_invoice_client>

Cách làm bằng cách join 2 table:

SELECT c.name
FROM clients c JOIN invoice i
USING (client_id) -- Use USING to join table
GROUP BY i.client_id
ORDER BY SUM (i.invoice_total) DESC
LIMIT 1; --Lay cao nhat

Cách làm bằng CTE First query: là mục đích cuối cùng Second query: là cách để query ra top total invoice client

WITH incoive_amount AS (
		SELECT client_id, SUM(invoice_total) AS invoice_amount
		FROM invoices
		GROUP BY client_id
		ORDER BY SUM(invoice_total) DESC
		LIMIT 1
)
SELECT c.client_id, c.name, i.invoice.amount
FROM clients c JOIN invoice.amount i
USING (client_id);

Sử dụng bảng tạm → Hiểu như là tái sử dụng lại hàm trong Python WITH … AS Khai báo bảng tạm Vấn đề: Bảng tạm chỉ tồn tại trong lúc chạy query. Nếu đóng file thì bảng sẽ mất.

3. SQL Subqueries

Lồng Query trong Query Cách Query câu hỏi trên bằng Sub-query

SELECT client_id,
			 name,
			 (SELECT SUM (invoice_total)
			  FROM invoices
			  WHERE c.client_id = client_id) AS invoice_amount
FROM clients c
ORDER BY invoice_amount DESC
LIMIT 1;

Vấn đề: Subquery không thể tái sử dụng như CTE Vậy để sử dụng Subquery nhiều lần ta sẽ sử dụng Temporary Table →

4. SQL Temp Table

Xét query sau:

SELECT client_id,
			 (SELECT AVG(invoice_total)
				FROM invoices
				WHERE client_id = c.client_id) AS avg_invoice
FROM clients c;

Vấn đề: How can we save this query result to reuse multiple times? Tạo bảng tạm để lưu data:

CREATE TEMPORARY TABLE temp_invoice (
		client_id INT,
		invoice_sum DECIMAL (10,2),
		invoice_avg DECIMAL(10,2)
);

Sau đó sử dụng Subquery để INSERT dữ liệu:

INSERT INTO temp_invoice
SELECT client_id,
			 SUM(invoice_total) AS invoice_sum,
			 AVG (invoice_total) AS invoice_avg
FROM invoices
GROUP BY client_id;

Check xem table vừa tạo: SELECT * FROM temp_invoice → Vậy chúng ta đã lưu lại được dữ liệu và gọi ra bất cứ khi nào ta cần. Ví dụ: Sử dụng Temp Table để giải quyết 2 câu hỏi trên Tuy nhiên Temp Table vẫn có vấn đề: Khi ta tắt chương trình thì vẫn bị xóa dữ liệu

5. SQL Store Procedures

DELIMITER //
CREATE PROCEDURE create_temp_invoice()
BEGIN
	DROP
	....
DELIMITER;

BEGIN … END: Giống như khai báo, tạo function mới trong Python

6. SQL Trigger

Kích hoạt sự kiện khi ta thao tác (insert, update, delete,…) Ví dụ: Trigger tạo log khi người dùng thêm employee

create trigger
	hire_log -- the name of the trigger
	after -- before or after the change
	insert -- which kind of change, (insert, update, or delete)
	on employees -- the name of the table to watch for changes
	for each row -- boilerplate to begin the trigger body
	insert into hiring values (new.id, current_time()) -- trigger body
;

Tạo trigger khi thay đổi tên của employees: Trigger Validation: you want to make sure that a table uses only upper-case letters.

Tổng kết thì đây chỉ là giới thiệu, ôn tập qua SQL. Không nên lo lắng và hãy luyện tập thêm trên LeetCode để hiểu và advance hơn. Cố gắng nắm idea là được, không cần thuộc syntax.

7. Practice

Pratice 1

Using the sql_hr database, how would you retrieve a list of all employees who earn a higher salary than their manager using a subquery?

• Đáp án

Pratice 2

Using the sql_hr database, create a temporary table that includes the first name, last name, and office address for each employee

• Đáp án

Pratice 3

In the sql_invoicing database, can you write a stored procedure — that inserts a new payment record, then updates the corresponding invoice with the payment total?

• Đáp án

Pratice 4

Using the sql_invoicing database, can you create a stored procedure that finds the client who has
made the most payments in the last year?

• Đáp án

Pratice 5

In the sql_store database, how would you list the orders that have a total amount greater than the
average order amount using a subquery?

• Đáp án

Pratice 6

In the sql_store database, how would you identify the product that appears most frequently in orders using a Common Table Expression?

• Đáp án

Pratice 7

Using the sql_store database, create a temporary table that includes the customer_id, total quantity of products ordered, and total amount spent by each customer.

• Đáp án

Pratice 8

Using the sql_hr database, how would you find the employee who earns the least in each office using a Common Table Expression?

• Đáp án

Pratice 9

In the sql_invoicing database, can you write a stored procedure to calculate the total unpaid amount for each client?

• Đáp án

Pratice 10

Using the sql_invoicing database, how would you list the top 5 clients who have the most unpaid
invoices using a subquery?

• Đáp án

Pratice 11

Using the sql_store database, how would you determine the total revenue made from each product using a subquery?

• Đáp án

Pratice 12

In the sql_hr database, create a stored procedure that promotes an employee to a manager position, updating their salary and reports_to fields accordingly.

• Đáp án

Pratice 13

In the sql_invoicing database, can you write a stored procedure that applies a discount to all invoices over a certain amount?

• Đáp án

Pratice 14

In the sql_store database, how would you find the order that has the highest total amount using a
Common Table Expression?

• Đáp án

Pratice 15

Using the sql_store database, create a temporary table that includes the product_id, total quantity sold, and total revenue from each product.

• Đáp án

Kahoot

Viết Query để trả về:

SELECT Coutry,
			State,
			COUNT(*) AS HowMany
FROM WorldWideFriend
GROUP BY Country, State;

Viết Query đếm số đơn hàng của a Shipper

Join
Group by

8. Recursive in SQL (Đệ quy trong SQL)

CREATE TABLE employees (
		employee_id INT PRIMARY KEY,
		employee_name VARCHAR (100) ,
		manager_id INT -- NULL nếu là CEO
);
INSERT INTO employees (employee_id, employee_name, manager_id) VALUES
(1, 'Alice (CEO)', NULL),
(2, 'Bob', 1),
(3, 'Charlie', NULL),
(4, 'David', 2),
(5, 'Eve', 2),
(6, 'Frank', 3);

WITH RECURSIVE Subordinates AS (
		-- Phần Neo (Anchor Member)
		SELECT employee_id, employee_name, manager_id
		FROM employees
		WHERE employee_name = 'Alice (CEO)'
		
		UNION ALL
		
		-- Phân Đệ quy (Recursive Member)
		SELECT e. employee_id, e. employee_name, e.manager_id
		FROM employees e
		INNER JOIN Subordinates s ON e.manager_id = s.employee_id
)
SELECT * FROM Subordinates;

Quiz:

Cho trước bảng thông tin employees như sau
| EmployeeID           | Name         | ManagerID |
| 1                    | CEO          | NULL      |
| 2                    | Manager A    | 1         |
| 3                    | Staff A1     | 2         |
| 4                    | Staff A2     | 2         |
| 5                    | Manager B    | 1         |
| 6                    | Staff B1     | 5         |
WITH RECURSIVE EmpCTE AS (
    SELECT EmployeeID, Name, ManagerID
    FROM Employees
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.EmployeeID, e.Name, e.ManagerID
    FROM Employees e
    INNER JOIN EmpCTE cte ON e.ManagerID = cte.EmployeeID
)
SELECT COUNT(*) FROM EmpCTE;

Kết quả của truy vấn trên là gì?

9. Data Mining

Là từ data → infomation (insight) bằng việc ta query

Case study

Data mining dùng cơ sở dữ liệu Sakila, một hệ thống cho thuê phim mẫu. Mục tiêu khai phá dữ liệu

• Phân tích hành vi thuê phim

• Tìm khách hàng trung thành

• Gợi ý phim theo xu hướng Giới thiệu database: Viết các query:

• Xem khách nào hay thuê phim nhiều nhất

• Đáp án

• Những khách đó thích phim gì? Để chúng ta có thể gợi ý thêm phim họ thích

• Đáp án

• Phim nào được mượn nhiều nhất không? Để cửa hàng mua thêm.

• Đáp án

Giới thiệu công cụ Rapid Miner – Data Mining tool

• Kết nối Database
• Tự động đọc và phân tích dữ liệu
• Data visualization
• Predictive analytics
• Machine Learning