183 lines
3.8 KiB
SQL
183 lines
3.8 KiB
SQL
/* *** EX1-QACS.A *** */
|
|
SELECT *
|
|
FROM CUSTOMER
|
|
;
|
|
|
|
SELECT *
|
|
FROM ITEM
|
|
;
|
|
|
|
SELECT *
|
|
FROM SALE
|
|
;
|
|
|
|
SELECT *
|
|
FROM SALE_ITEM
|
|
;
|
|
|
|
/* *** EX1-QACS.B *** */
|
|
SELECT LastName, FirstName, Phone
|
|
FROM CUSTOMER
|
|
;
|
|
|
|
/* *** EX1-QACS.C *** */
|
|
SELECT LastName, FirstName, Phone
|
|
FROM CUSTOMER
|
|
WHERE FirstName LIKE 'John'
|
|
;
|
|
|
|
/* *** EX1-QACS.D *** */
|
|
SELECT c.LastName, c.FirstName, c.Phone, s.SaleDate, s.Total
|
|
FROM CUSTOMER c, SALE s
|
|
WHERE c.CustomerID = s.CustomerID AND s.Total > 100
|
|
;
|
|
|
|
/* *** EX1-QACS.E *** */
|
|
SELECT LastName, FirstName, Phone
|
|
FROM CUSTOMER
|
|
WHERE FirstName LIKE 'D%'
|
|
;
|
|
|
|
/* *** EX1-QACS.F *** */
|
|
SELECT LastName, FirstName, Phone
|
|
FROM CUSTOMER
|
|
WHERE LastName LIKE '%ne%'
|
|
;
|
|
|
|
/* *** EX1-QACS.G *** */
|
|
SELECT LastName, FirstName, Phone
|
|
FROM CUSTOMER
|
|
WHERE PHONE LIKE '%56_'
|
|
;
|
|
|
|
/* *** EX1-QACS.H *** */
|
|
SELECT MAX(Total) AS MaxSalesTotal, MIN(Total) AS MinSalesTotal
|
|
FROM SALE
|
|
;
|
|
|
|
/* *** EX1-QACS.I *** */
|
|
SELECT AVG(Total) AS AvgSalesTotal
|
|
FROM SALE
|
|
;
|
|
|
|
/* *** EX1-QACS.J *** */
|
|
SELECT COUNT(*) AS NumCustomers
|
|
FROM CUSTOMER
|
|
;
|
|
|
|
/* *** EX1-QACS.K *** */
|
|
SELECT LastName, FirstName
|
|
FROM CUSTOMER
|
|
GROUP BY LastName, FirstName
|
|
;
|
|
|
|
/* *** EX1-QACS.L *** */
|
|
SELECT LastName, FirstName, COUNT(*) AS NameCombo
|
|
FROM CUSTOMER
|
|
GROUP BY LastName, FirstName
|
|
;
|
|
|
|
/* *** EX1-QACS.M *** */
|
|
SELECT LastName, FirstName, Phone
|
|
FROM CUSTOMER
|
|
WHERE CustomerID IN
|
|
(SELECT CustomerID
|
|
FROM SALE
|
|
WHERE Total > 100
|
|
)
|
|
ORDER BY LastName ASC, FirstName DESC
|
|
;
|
|
|
|
/* *** EX1-QACS.N *** */
|
|
SELECT DISTINCT c.LastName, c.FirstName, c.Phone
|
|
FROM CUSTOMER c, SALE s
|
|
WHERE c.CustomerID = s.CustomerID AND s.Total > 100
|
|
ORDER BY LastName ASC, FirstName DESC
|
|
;
|
|
|
|
/* *** EX1-QACS.O *** */
|
|
SELECT DISTINCT c.LastName, c.FirstName, c.Phone
|
|
FROM CUSTOMER c JOIN SALE s
|
|
ON c.CustomerID = s.CustomerID
|
|
WHERE s.Total > 100
|
|
ORDER BY LastName ASC, FirstName DESC
|
|
;
|
|
|
|
/* *** EX1-QACS.P *** */
|
|
SELECT LastName, FirstName, Phone
|
|
FROM CUSTOMER
|
|
WHERE CustomerID IN
|
|
(SELECT CustomerID
|
|
FROM SALE
|
|
WHERE SaleID IN
|
|
(SELECT SaleID
|
|
FROM SALE_ITEM
|
|
WHERE ItemID IN
|
|
(SELECT ItemID
|
|
FROM ITEM
|
|
WHERE ItemDescription LIKE 'Desk Lamp'
|
|
)
|
|
)
|
|
)
|
|
ORDER BY LastName ASC, FirstName DESC
|
|
;
|
|
|
|
/* *** EX1-QACS.Q *** */
|
|
SELECT c.LastName, c.FirstName, c.Phone
|
|
FROM CUSTOMER c, SALE s, Sale_ITEM si, ITEM i
|
|
WHERE c.CustomerID = s.CustomerID
|
|
AND s.SaleID = si.SaleID
|
|
AND si.ItemID = i.ItemID
|
|
AND i.ItemDescription LIKE 'Desk Lamp'
|
|
ORDER BY LastName ASC, FirstName DESC
|
|
;
|
|
|
|
/* *** EX1-QACS.R *** */
|
|
SELECT c.LastName, c.FirstName, c.Phone
|
|
FROM CUSTOMER c JOIN SALE s ON c.CustomerID = s.CustomerID
|
|
JOIN Sale_ITEM si ON s.SaleID = si.SaleID
|
|
JOIN ITEM i ON si.ItemID = i.ItemID
|
|
WHERE i.ItemDescription LIKE 'Desk Lamp'
|
|
ORDER BY LastName ASC, FirstName DESC
|
|
;
|
|
|
|
/* *** EX1-QACS.S *** */
|
|
SELECT c.LastName, c.FirstName, c.Phone
|
|
FROM CUSTOMER c JOIN SALE s ON c.CustomerID = s.CustomerID
|
|
JOIN Sale_ITEM si ON s.SaleID = si.SaleID
|
|
WHERE si.ItemID IN
|
|
(SELECT ItemID
|
|
FROM ITEM
|
|
WHERE ItemDescription LIKE 'Desk Lamp'
|
|
)
|
|
ORDER BY LastName ASC, FirstName DESC
|
|
;
|
|
|
|
/* *** EX1-QACS.T *** */
|
|
SELECT c.LastName, c.FirstName, c.Phone
|
|
FROM CUSTOMER c JOIN SALE s ON c.CustomerID = s.CustomerID
|
|
WHERE s.SaleID IN
|
|
(SELECT SaleID
|
|
FROM SALE_ITEM
|
|
WHERE ItemID IN
|
|
(SELECT ItemID
|
|
FROM ITEM
|
|
WHERE ItemDescription LIKE 'Desk Lamp'
|
|
)
|
|
)
|
|
ORDER BY LastName ASC, FirstName DESC
|
|
;
|
|
|
|
/* *** EX1-QACS.U *** */
|
|
SELECT c.LastName, c.FirstName, c.Phone, 'Item' =
|
|
CASE
|
|
WHEN i.ItemDescription = 'Desk Lamp' THEN i.ItemDescription
|
|
ELSE ''
|
|
END
|
|
FROM CUSTOMER c JOIN SALE s ON c.CustomerID = s.CustomerID
|
|
JOIN Sale_ITEM si ON s.SaleID = si.SaleID
|
|
JOIN ITEM i ON si.ItemID = i.ItemID
|
|
ORDER BY 'Item' DESC, LastName ASC, FirstName DESC
|
|
;
|
|
|