1. Sử dụng câu lệnh truy vấn dữ liệu đơn giản với các yêu cầu sau.
1.1. Lấy danh sách tất cả sản phẩm
1.2. Chỉ lấy các thông tin về mã sản phẩm, tên sản phẩm, giá nhập, giá niêm yết (giá bán), số lượng, % giảm giá của các sản phẩm
1SELECT product_code, product_name, standard_cost, list_price, quantity_per_unit, discount
2FROM products;
1.3. Lấy mã sản phẩm, tên sản phẩm, giá nhập, giá niêm yết (giá bán) của những sản phẩm có số lượng còn lại > 60
1SELECT product_code, product_name, standard_cost, list_price, quantity_per_unit, discount
2FROM products
3WHERE quantity_per_unit > 60;
1.4. Lấy 3 sản phẩm mới nhất vừa được thêm vào Hệ thống
1SELECT id, product_code, product_name, standard_cost, list_price, quantity_per_unit, discount
2FROM products
3ORDER BY id DESC
4LIMIT 3;
1.5. Lấy sản phẩm mà tên sản phẩm có chứa chữ 'Samsung'
1SELECT *
2FROM products
3WHERE product_name LIKE '%Samsung%';
1.6. Lấy các sản phẩm với tên có chứa 'MacBook' và giá niêm yết (giá bán) trên 7,000,000
1SELECT *
2FROM products
3WHERE product_name LIKE '%Macbbook%' AND list_price > 7000000;
1.7. Lấy các sản phẩm với tên có giá niêm yết (giá bán) từ 8,000,000 đến 10,000,000
1-- Xài BETWEEN
2SELECT *
3FROM products
4WHERE list_price BETWEEN 8000000 AND 10000000;
5
6-- Xài AND
7SELECT *
8FROM products
9WHERE list_price >= 8000000 AND list_price <= 10000000;
1.8. Liệt kệ Danh sách các Đơn hàng trong tháng 07/2016?
1-- Xài BETWEEN
2SELECT *
3FROM orders
4WHERE order_date BETWEEN '2016-07-01 00:00:00' AND '2016-07-31 23:59:59';
5
6-- Xài >= <=
7SELECT *
8FROM orders
9WHERE order_date >= '2016-07-01 00:00:00' AND order_date <= '2016-07-31 23:59:59';
1.9. Lấy danh sách ID nhà cung cấp (duy nhất) có sản phẩm trong hệ thống
1SELECT DISTINCT supplier_id
2FROM products;
2. Sử dụng kết nối các bảng với lệnh truy vấn SQL để:
2.1. Lấy mã sản phẩm, tên sản phẩm, giá nhập, giá niêm yết (giá bán), số lượng, tên nhà cung cấp của tất cả sản phẩm
1SELECT p.product_code, p.product_name, p.standard_cost, p.list_price, p.quantity_per_unit,
2 s.supplier_name
3FROM products AS p
4JOIN suppliers AS s ON p.supplier_id = s.id;
2.2. Lấy mã sản phẩm, tên sản phẩm, giá nhập, giá niêm yết (giá bán), số lượng, mã nhà cung cấp, tên nhà cung cấp, mã loại sản phẩm, tên loại sản phẩm của những sản phẩm có giá lớn hơn 5.000.000.
1SELECT p.product_code, p.product_name, p.standard_cost, p.list_price, p.quantity_per_unit,
2 s.supplier_name,
3 c.category_code, c.category_name
4FROM products AS p
5JOIN suppliers AS s ON p.supplier_id = s.id
6JOIN categories AS c ON p.category_id = c.id
7WHERE p.list_price > 5000000;
2.3. Lấy tất cả sản phẩm là máy tính bảng (tablet)
1SELECT p.product_code, p.product_name, p.standard_cost, p.list_price, p.quantity_per_unit,
2 c.category_code, c.category_name
3FROM products AS p
4JOIN categories AS c ON p.category_id = c.id
5WHERE c.category_name LIKE '%Tablet%';
2.4. Hiển thị sản phẩm có giá cao nhất
2.5. Hiển thị thông tin của các đơn đặt hàng, bao gồm:
| Mã đơn hàng
"Order_" + id | Họ tên Khách hàng
first_name + last_name | Địa chỉ khách hàng
address1 | Địa chỉ giao hàng
ship_address1 | Phí vận chuyển
shipping_pee | Hình thức thanh toán
payment_type |
1SELECT CONCAT('Order_', o.id) AS order_code,
2 CONCAT(c.last_name, ' ', c.first_name) AS full_name,
3 c.address1,
4 o.ship_address1,
5 o.shipping_fee,
6 o.payment_type
7FROM orders o
8JOIN customers c ON o.customer_id = c.id;
2.6. Hiển thị thông tin các Đơn hàng và danh sách Các sản phẩm mà khách hàng "Sanchez Sean" (ID=40) đã mua trong năm 2017
1SELECT
2 CONCAT(c.last_name, ' ', c.first_name) AS full_name,
3 o.id, o.order_date,
4 p.product_code, p.product_name, p.list_price
5FROM orders o
6JOIN customers c ON o.customer_id = c.id
7JOIN order_details od ON od.order_id = o.id
8JOIN products p ON od.product_id = p.id
9WHERE c.last_name = 'Sanchez' AND c.first_name = 'Sean'
10 AND o.order_date BETWEEN '2017-01-01 00:00:00' AND '2017-12-31 23:59:59';
3. Thực hiện câu lệnh Group By và Having để thực hiện các thống kê sau
3.1. Hiển thị mỗi Loại sản phẩm có bao nhiêu sản phẩm.
1SELECT c.category_name,
2 COUNT(*) AS SoLuong
3FROM products p
4JOIN categories c ON p.category_id = c.id
5GROUP BY c.category_name;
3.2. Tính tổng số lượng sản phẩm Còn lại của từng loại sản phẩm
1SELECT c.category_name,
2 SUM(p.quantity_per_unit) AS TongSoLuongConLai
3FROM products p
4JOIN categories c ON p.category_id = c.id
5GROUP BY c.category_name;
3.3. Tìm loại sản phẩm mà có tổng số lượng Sản phẩm đang có trong hệ thống > 50
1SELECT c.category_name,
2 SUM(p.quantity_per_unit) AS TongSoLuongConLai
3FROM products p
4JOIN categories c ON p.category_id = c.id
5GROUP BY c.category_name
6HAVING SUM(p.quantity_per_unit) > 150;
4. Thực hiện câu lệnh truy vấn trả lời các yêu cầu sau:
4.1. Tìm nhà cung cấp không bán được sản phẩm nào trong Hệ thống?
[su_spoiler title="Bài giải (Nên nhớ tự làm trước khi click vào đây)"]
1SELECT *
2FROM suppliers s
3WHERE s.id NOT IN (
4 SELECT distinct p.supplier_id
5 FROM order_details od
6 JOIN products p ON od.product_id = p.id
7);
[/su_spoiler]
4.2. Tìm top 3 Khách hàng mua nhiều nhất trong năm nay để tri ân khách hàng?
[su_spoiler title="Bài giải (Nên nhớ tự làm trước khi click vào đây)"]
1SELECT *
2FROM (
3 SELECT concat(c.first_name, ' ', c.last_name) AS full_name, COUNT(*) AS order_count
4 FROM customers c
5 JOIN orders o ON o.customer_id = c.id
6 GROUP BY c.id
7) AS tmp
8ORDER BY tmp.order_count DESC
9LIMIT 3;
[/su_spoiler]
4.3. Thống kê danh sách các sản phẩm nào đã bán gần hết số lượng (số lượng < 10 xem như gần hết), để Chủ SHOP nhập thêm hàng về?
4.3. Hình thức thanh toán phổ biến nhất là gì?