Mọi thông tin về tài liệu tham khảo bài viết trước tại đây
Bài thực hành 6:
select customers.city, count(customers.customerName) as SL from customers group by customers.city;
select count(orderDate) as count from orders where orderDate between “2005-03-01” and “2005-03-31”;
SELECT productCode, quantityOrdered*priceEach as totalPrice FROM classicmodels.orderdetails order by quantityOrdered*priceEach desc limit 10;
SELECT productLine, sum(quantityInStock) as totalQuantityInStock FROM classicmodels.products group by productLine;
Bài thực hành 7:
use classicmodels;
#C1
select * from employees join offices on employees.officeCode = offices.officeCode;
#C2
select customers.customerName, products.productName from customers join orders on customers.customerNumber = orders.customerNumber
join orderdetails on orders.orderNumber = orderdetails.orderNumber
join products on products.productCode = orderdetails.productCode;
#C3
select products.productName from products left join orderdetails on products.productCode = orderdetails.productCode where orderdetails.productCode is NULL;
#C4
select o.orderDate,o.requiredDate,o.status, sum(od.quantityOrdered*od.priceEach) price from orders o join orderdetails od on o.orderNumber = od.orderNumber where month(o.orderDate)=’03’ and year(o.orderDate)=’2005′ group by od.orderNumber;
#C5
select p.productLine, count(p.productCode) as total from productlines pl join products p on pl.productLine=p.productLine group by p.productLine order by total desc;
Bài thực hành 8:
#1
SELECT * FROM products
WHERE productCode IN
(SELECT productCode
FROM orderdetails
WHERE orderNumber IN
(SELECT orderNumber
FROM orders
WHERE EXTRACT(YEAR FROM orderDate) = 2005 AND EXTRACT(MONTH FROM orderDate) = 3));
#2
SELECT * FROM Products p
INNER JOIN orderdetails od ON p.productCode = od.productCode
INNER JOIN orders o ON od.orderNumber = o.orderNumber
WHERE EXTRACT(YEAR FROM o.orderDate) = ‘2005’ and extract(MONTH FROM o.orderDate) = ‘3’;
- Kết hợp của INNER JOIN và Subquerry
SELECT * FROM products p
WHERE productCode IN (
SELECT od.productCode
FROM orderDetails od
INNER JOIN orders o ON od.orderNumber = o.orderNumber
WHERE MONTH(o.orderDate) = 3 AND YEAR(o.orderDate) = 2005);
#3
SELECT *
FROM orders
WHERE EXTRACT(YEAR FROM orderDate) = EXTRACT(YEAR FROM(SELECT MAX(orderDate) FROM orders))
AND EXTRACT(MONTH FROM orderDate) = EXTRACT(MONTH FROM(SELECT MAX(orderDate) FROM orders));
#4
SELECT orderNumber, orderDate, requiredDate, shippedDate, (SELECT SUM(priceEach * quantityOrdered)
FROM orderdetails
WHERE orderNumber = o.orderNumber) AS priceTotal
FROM orders o;
#5
SELECT o.*, SUM(od.quantityOrdered * od.priceEach)
FROM orders o
INNER JOIN orderdetails od ON o.orderNumber = od.orderNumber
GROUP BY od.orderNumber;
#6
SELECT *, (SELECT SUM(quantityOrdered * priceEach)
FROM orderdetails
WHERE orderNumber IN (
SELECT orderNumber
FROM orders
WHERE customerNumber = c.customerNumber)) AS priceTotal,
(SELECT SUM(amount)payments
FROM payments
WHERE customerNumber = c.customerNumber) AS paymentTotal
FROM customers c;
Bài thực hành 9: < Khá dài>
USE mydatabase;
- 1 Thực hành các lệnh INSERT, UPDATE và DELETE trên các bảng trong hình dưới đây của CSDL classicmodels
INSERT INTO productlines (productLine, textDescription)
VALUES (‘Iphone 10’, ‘FRAGILE, PLEASE HANDLE WITH CARE’);
INSERT INTO productlines (productLine, textDescription)
VALUES (‘Iphone 11’, ‘FRAGILE, PLEASE HANDLE WITH CARE’);
INSERT INTO productlines (productLine, textDescription)
VALUES (‘Iphone 11 Plus’, ‘SALE OF 25%’);
UPDATE productlines
SET textDescription = “OUT OF ORDER”
WHERE productLine = ‘Iphone 11’;
DELETE FROM productlines
WHERE productLine = ‘Iphone 10’;
- products
INSERT INTO products
(productCode, productName, productLine, productScale,
productVendor, productDescription, quantityInStock, buyPrice)
VALUES (‘S101’, ‘Telephone’, ‘Iphone 10’, ‘1:1’, ‘PRO’, ‘None’, 5, 1000);
INSERT INTO products
(productCode, productName, productLine, productScale,
productVendor, productDescription, quantityInStock, buyPrice)
VALUES (‘S102’, ‘Telephone’, ‘Iphone 11’, ‘1:1’, ‘PRO’, ‘None’, 3, 1500);
INSERT INTO products
(productCode, productName, productLine, productScale,
productVendor, productDescription, quantityInStock, buyPrice)
VALUES (‘S103’, ‘Telephone’, ‘Iphone 11 Plus’, ‘1:1’, ‘PRO’, ‘None’, 4, 2000);
UPDATE products
SET quantityStock = ‘6’
WHERE productCode = ‘S102’;
DELETE FROM products
WHERE productCode = ‘S103’;
- orders
INSERT INTO orders (orderNumber, orderDate, requiredDate, shippedDate, status, comments, customerNumber )
VALUES (1, ‘2020-05-13’, ‘2020-05-25’, ‘2020-05-20’, ‘Shipped’, null, 11 );
INSERT INTO orders (orderNumber, orderDate, requiredDate, shippedDate, status, comments, customerNumber )
VALUES (2, ‘2020-06-13’, ‘2020-05-27’, ‘2020-05-19’, ‘Cancel’, null, 12 );
INSERT INTO orders (orderNumber, orderDate, requiredDate, shippedDate, status, comments, customerNumber )
VALUES (3, ‘2020-07-13’, ‘2020-05-24’, ‘2020-05-18’, ‘Shipped’, null, 13 );
UPDATE orders
SET status = ‘Cancel’
WHERE orderNumber = 3;
DELETE FROM orders
WHERE orderNumber = 1 ;
- orderdetails
INSERT INTO orderdetails (orderNumber, productCode, quantityOrdered, priceEach, orderLineNumber)
VALUES (‘1’, ‘S101′, ’10’, ‘500’, ‘6’);
INSERT INTO orderdetails (orderNumber, productCode, quantityOrdered, priceEach, orderLineNumber)
VALUES (‘2’, ‘S102’, ‘9’, ‘265’, ‘7’);
INSERT INTO orderdetails (orderNumber, productCode, quantityOrdered, priceEach, orderLineNumber)
VALUES (‘3’, ‘S103′, ’11’, ‘300’, ‘8’);
UPDATE orderdetails
SET priceEach = ‘501’
WHERE orderNumber = ‘1’;
DELETE FROM orderdetails
WHERE orderNumber = ‘3’;
- 2 Tạo một bảng đặt tên là temp_orderdetails, sau đó thực hiện thêm dữ liệu
— trong ngày gần đây nhất từ bảng orderdetails vào bảng trên.
#Cách 1:
CREATE TABLE temp_orderdetails (
orderNumber INT(11),
productCode VARCHAR(15),
quantityOrdered INT(11),
priceEach DOUBLE,
orderLineNumber SMALLINT(6),
CONSTRAINT temp_pk PRIMARY KEY (orderNumber, productCode));
INSERT INTO temp_orderdetails
SELECT classicmodels.orderdetails.*
FROM classicmodels.orderdetails
INNER JOIN classicmodels.orders on orderdetails.orderNumber = orders.orderNumber
WHERE orders.orderDate = (SELECT MAX(orders.orderDate) FROM orders);
#Cách 2:
CREATE TABLE temp_orderdetails
AS
SELECT od.* FROM orderdetails as od
JOIN orders as o on od.orderNumber = o.orderNumber
WHERE o.orderDate = (SELECT MAX(orderDate) FROM orders)
GROUP BY od.orderNumber;
- 3 Sửa các nhân viên có titleJob là ‘Sales Rep’ thành ‘Sales Representative’
USE classicmodels;
UPDATE employees
SET jobTitle = ‘Sales Representative’
WHERE jobTitle = ‘Sales Rep’;
Bài thực hành 10: Kéo thả thôi =))
Source: -S-Team
