Chương 5-Bài 6. Bài tập Truy vấn CSDL

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

SELECT * FROM products;

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

SELECT product_code, product_name, standard_cost, list_price, quantity_per_unit, discount
FROM 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

SELECT product_code, product_name, standard_cost, list_price, quantity_per_unit, discount
FROM products
WHERE 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

SELECT id, product_code, product_name, standard_cost, list_price, quantity_per_unit, discount
FROM products
ORDER BY id DESC
LIMIT 3;

1.5. Lấy sản phẩm mà tên sản phẩm có chứa chữ 'Samsung'

SELECT *
FROM products
WHERE 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

SELECT *
FROM products
WHERE 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

-- Xài BETWEEN
SELECT *
FROM products
WHERE list_price BETWEEN 8000000 AND 10000000;

-- Xài AND
SELECT *
FROM products
WHERE 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?

-- Xài BETWEEN
SELECT * 
FROM orders
WHERE order_date BETWEEN '2016-07-01 00:00:00' AND '2016-07-31 23:59:59';

-- Xài >= <=
SELECT * 
FROM orders
WHERE 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

SELECT DISTINCT supplier_id
FROM 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

SELECT p.product_code, p.product_name, p.standard_cost, p.list_price, p.quantity_per_unit,
  s.supplier_name
FROM products AS p
JOIN 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.

SELECT p.product_code, p.product_name, p.standard_cost, p.list_price, p.quantity_per_unit,
  s.supplier_name,
  c.category_code, c.category_name
FROM products AS p
JOIN suppliers AS s ON p.supplier_id = s.id
JOIN categories AS c ON p.category_id = c.id
WHERE p.list_price > 5000000;

2.3. Lấy tất cả sản phẩm là máy tính bảng (tablet)

SELECT p.product_code, p.product_name, p.standard_cost, p.list_price, p.quantity_per_unit,
  c.category_code, c.category_name
FROM products AS p
JOIN categories AS c ON p.category_id = c.id
WHERE 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:

SELECT CONCAT('Order_', o.id) AS order_code, 
  CONCAT(c.last_name, ' ', c.first_name) AS full_name, 
  c.address1, 
  o.ship_address1, 
  o.shipping_fee, 
  o.payment_type 
FROM orders o
JOIN 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

SELECT 
  CONCAT(c.last_name, ' ', c.first_name) AS full_name,
  o.id, o.order_date,
  p.product_code, p.product_name, p.list_price
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN order_details od ON od.order_id = o.id
JOIN products p ON od.product_id = p.id
WHERE c.last_name = 'Sanchez' AND c.first_name = 'Sean'
  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.

SELECT c.category_name,
  COUNT(*) AS SoLuong
FROM products p
JOIN categories c ON p.category_id = c.id
GROUP 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

SELECT c.category_name,
  SUM(p.quantity_per_unit) AS TongSoLuongConLai
FROM products p
JOIN categories c ON p.category_id = c.id
GROUP 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

SELECT c.category_name,
  SUM(p.quantity_per_unit) AS TongSoLuongConLai
FROM products p
JOIN categories c ON p.category_id = c.id
GROUP BY c.category_name
HAVING 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?

SELECT *
FROM suppliers s
WHERE s.id NOT IN (
    SELECT distinct p.supplier_id
    FROM order_details od
    JOIN products p ON od.product_id = p.id
);

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?

SELECT *
FROM (
    SELECT concat(c.first_name, ' ', c.last_name) AS full_name, COUNT(*) AS order_count
    FROM customers c
    JOIN orders o ON o.customer_id = c.id
    GROUP BY c.id
) AS tmp
ORDER BY tmp.order_count DESC
LIMIT 3;

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ì?