SQL northwind exam_Test
Bạn đang xem bản rút gọn của tài liệu. Xem và tải ngay bản đầy đủ của tài liệu tại đây (50.86 KB, 9 trang )
USE Northwind
Select * FROM tblStudent WHERE FirstName LIKE '_hi';
Select * FROM tblStudent WHERE FirstName LIKE 'N_uyen';
Select * FROM tblStudent WHERE FirstName LIKE 'H_u_u';
Select * FROM tblStudent WHERE FirstName LIKE '[TBN]%'
// Lấy tất cả records nếu FirstName có các chữ cái đầu T,B,N
Select * FROM tblStudent WHERE FirstName LIKE '[!TBN]%'
// Lấy tất cả records nếu FirstName Không có các chữ cái đầu T,B,N
Select * FROM tblStudent WHERE FirstName LIKE '[A-F]%'
// Lấy tất cả records nếu FirstName có các chữ cái đầu Từ A đến F
// [A-F] : Lấy thứ tự A B C D F
--- Cau 1
SELECT CustomerID, CompanyName, City, Phone FROM Customers
---Cau
Cac employees , tinh age and ghep ten
SELECT EmployeeID, HomePhone, YEAR(getDate()) - YEAR(BirthDate) as
Age, LastName + ' ' + FirstName as Name
FROM Employees
---Cau 3 Cac employees nam sinh <= 1960
SELECT * FROM Employees
WHERE YEAR(BirthDate) <= 1960
---Cau 4 Cac QuantityPerUnit co chu boxes
SELECT * FROM Products
WHERE QuantityPerUnit like '%Boxes%' AND (UnitPrice BETWEEN 10
AND 15)
---Cau 5 Cac don hang trong thang 9 nam 1996
SELECT * FROM Orders
WHERE MONTH(OrderDate) = 9 AND YEAR(OrderDate) = 1996
--- Cach su dung index tot hon
SELECT * FROM Orders
WHERE OrderDate BETWEEN '19960901' AND '19960930'
-SELECT * FROM Orders
WHERE OrderDate = '19960902'
-- lưu ý chuỗi ngày tháng mặc định của ANSI có dạng yyyymmdd
---Cau 6 Product ung voi tien ton von
SELECT ProductID, ProductName, UnitPrice, UnitsInStock, UnitsInStock *
UnitPrice as TotalAccount
FROM Products
---Cau 7 Danh sach 5 customer dau tien co city bat dau 'M'
SELECT * FROM Customers
WHERE City like 'M%'
---Cau 8 Liet ke Custtomers khong co so fax
SELECT * FROM Customers
WHERE Fax IS NULL
---Cau 9 Cac employees khong o thanh pho London va Redmond
SELECT * FROM Employees
WHERE City NOT IN
(SELECT City FROM Employees
WHERE City like '%London%' OR City like '%Redmond%') --- Phai co dau ()
---- TRUY VAN CO KET NOT, JOIN
---Cau 10 Cac khach hnag khong lap hoa don trong thang 7/ 1997
SELECT * FROM Customers
WHERE CustomerID NOT IN
(SELECT c.CustomerID FROM
Customers c INNER JOIN Orders o
ON c.CustomerID = o.CustomerID
WHERE (o.OrderDate BETWEEN '19970701' AND '19970731'))
---Cau 11 Danh sach cac san pham duoc giao vao ngay 16/7/1996
SELECT ProductID FROM
Orders o INNER JOIN [Order Details] od
ON o.OrderID = od.OrderID
WHERE ShippedDate = '19970716'
---Cau 12
SELECT OrderID, CompanyName, OrderDate, RequiredDate FROM Orders o
INNER JOIN Customers c
ON o.CustomerID = o.CustomerID
WHERE OrderDate BETWEEN '19970401' AND '19970930'
ORDER BY CompanyName ASC, OrderDate DESC
---Cau 13: Danh sach cac customer chua mua hang trong nam 1997
SELECT c.CustomerID, c.CompanyName FROM Customers c INNER JOIN
Orders o
ON c.CustomerID = o.CustomerID
WHERE o.OrderDate NOT BETWEEN '19970101' AND '19971231'
------- TRUY VBN GROUP BY
--- Cau 1 Danh sach hoa don ung voi tong tien
SELECT one.OrderID, one.OrderDate, two.Total FROM
(SELECT OrderID, OrderDate FROM Orders)one
INNER JOIN
(SELECT o.OrderID, SUM(od.Quantity* od.UnitPrice) as Total
FROM Orders o INNER JOIN [Order Details] od
ON o.OrderID = od.OrderID
GROUP BY o.OrderID) two
ON one.OrderID = two.OrderID
---Cau 2 nhu tren nhung Shipcity = 'Madrid'
SELECT one.OrderID, one.OrderDate, one.ShipCity, two.Total FROM
(SELECT OrderID, OrderDate, ShipCity FROM Orders)one
INNER JOIN
(SELECT o.OrderID, SUM(od.Quantity* od.UnitPrice) as Total
FROM Orders o INNER JOIN [Order Details] od
ON o.OrderID = od.OrderID
WHERE o.ShipCity like '%Madrid%'
GROUP BY o.OrderID) two
ON one.OrderID = two.OrderID
----Cau 3 Danh sach cac Product co tong so luong lap hoa don lon nhat
SELECT DISTINCT ProductID FROM [Order Details]
WHERE ProductID NOT IN
(
SELECT one.ProductID FROM
(SELECT od.ProductID, SUM(od.Quantity) as Total
FROM [Order Details] od INNER JOIN Orders o
ON od.OrderID = o.OrderID
GROUP BY od.ProductID) one,
(SELECT od.ProductID, SUM(od.Quantity) as Total
FROM [Order Details] od INNER JOIN Orders o
ON od.OrderID = o.OrderID
GROUP BY od.ProductID) two
WHERE two.Total >one.Total
)
--- Cau 4: Moi Employee da lap bao nhieu hoa don ung voi tong tien
SELECT e.EmployeeID, COUNT(o.OrderID) as SoHD, SUM(od.Quantity *
od.UnitPrice) as Total
FROM Employees e INNER JOIN Orders o ON e.EmployeeID =
o.EmployeeID
INNER JOIN [Order Details] od ON od.OrderID = o.OrderID
GROUP BY e.EmployeeID
---CAu 5: Tim tat ca cac Cutomers mua it nhat 2 don hang
SELECT one.CustomerID, one.CompanyName, two.Number FROM
(SELECT c.CustomerID, c.CompanyName FROM Customers c)one
INNER JOIN
(SELECT c.CustomerID, COUNT(o.OrderID) as Number FROM Customers c
INNER JOIN Orders o
ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerID
HAVING COUNT(o.OrderID) >2) two
ON one.CustomerID = two.CustomerID
---Cau 6: Employees nao ban duoc nhieu tien nhat trong thang 7 1997
SELECT * FROM Employees
WHERE EmployeeID NOT IN
(SELECT one.EmployeeID FROM
(SELECT o.EmployeeID, SUM(od.Quantity * od.UnitPrice) as Total FROM
Employees e INNER JOIN Orders o
ON e.EmployeeID = o.EmployeeID
INNER JOIN [Order Details] od
ON o.OrderID = od.OrderID
GROUP BY o.EmployeeID)one ,
(SELECT o.EmployeeID, SUM(od.Quantity * od.UnitPrice) as Total FROM
Employees e INNER JOIN Orders o
ON e.EmployeeID = o.EmployeeID
INNER JOIN [Order Details] od
ON o.OrderID = od.OrderID
GROUP BY o.EmployeeID)two
WHERE one.Total < two.Total)
---Cau 7: 3 khach hang co nhieu don hang nhat 1997
SELECT TOP 3 * FROM
(SELECT c.CustomerID, COUNT(o.OrderID) as Total FROM Customers c
INNER JOIN Orders o
ON c.CustomerID = o.CustomerID
WHERE o.OrderDate BETWEEN '19970101' AND '19971231'
GROUP BY c.CustomerID
) one
ORDER BY one.Total DESC
------ SUB Query
---Cau 1: Danh sach cac City co nhieu hon 3 Customers
---Cau 2: Danh sach cac Customers va Employees khong o cung thanh pho
---Cau 3: Cac san pham co so luong ban ra lon hon so luong trung binh
---Cau 4: Danh asch khach hang mua nhung hoa don ma tat ca productID >=3
---Cau 5: cac city co Customer va/ hoac Emp
---Cau 6: Danh asch cac Suppliers cung cap tat ca cac mat hang
SELECT * FROM Suppliers s INNER JOIN Products p
ON s.SupplierID = p.SupplierID
