Đề Thi PE DBI202 - PE - SP 2025 - Block 5 - Đề số - 4

[Minh Hằng1]

DBI202 - PE - SP 2025 - Block 5
07/05/2025 10h50

 

[3aevippro]

Đề chụp thiếu nhiều quá ;-;

 

[3aevippro]

ĐÁP ÁN 2-6, AE THAM KHẢO

--2
SELECT *
FROM MenuItems
WHERE Category LIKE 'Main Course'
AND PRICE BETWEEN 25000 AND 50000

--3
SELECT O.OrderID, O.OrderDate, C.FullName AS 'CustomerFullName', C.PhoneNumber, RT.TableNumber, RT.Capacity
FROM Orders O
JOIN RestaurantTables RT ON O.TableID = RT.TableID AND RT.Capacity = 6
JOIN Customers C ON O.CustomerID = C.CustomerID
WHERE YEAR(OrderDate) = 2024 AND MONTH(OrderDate) IN (11,12)

--4
SELECT M.MenuItemID, M.Name, M.Category, O.OrderID, O.OrderDate, OD.Quantity
FROM MenuItems M
LEFT JOIN (
OrderDetails OD
INNER JOIN Orders O
ON O.OrderID = OD.OrderID
AND YEAR(O.OrderDate) = 2025
AND MONTH (O.OrderDate) = 4
)ON OD.MenuItemID = M.MenuItemID
WHERE M.Category = 'Appetizer'
ORDER BY M.Name ASC, O.OrderDate DESC

--5
SELECT M.MenuItemID, M.Name, M.Category,
COALESCE(SUM(OD.Quantity),0) AS 'TotalQuantity',
COALESCE(SUM(OD.Quantity * OD.Price),0) AS 'TotalAmount',
COUNT(DISTINCT O.CustomerID) AS 'NumberOfCustomers'
FROM MenuItems M
LEFT JOIN (
OrderDetails OD
INNER JOIN Orders O
ON O.OrderID = OD.OrderID
AND YEAR(O.OrderDate) = 2024
AND MONTH (O.OrderDate) = 10
)ON OD.MenuItemID = M.MenuItemID
WHERE M.Category = 'Main Course'
GROUP BY M.MenuItemID, M.Name, M.Category
ORDER BY M.MenuItemID

--6
SELECT E.EmployeeID, E.FullName, E.Role, COUNT(O.OrderID) AS OrderCount
FROM Employees E
JOIN Orders O
ON O.EmployeeID = E.EmployeeID
GROUP BY E.EmployeeID, E.FullName, E.Role
HAVING COUNT(O.OrderID) > (
SELECT AVG (OrderCount)
FROM (
SELECT COUNT(O.OrderID) AS OrderCount
FROM Employees E
JOIN Orders O
ON O.EmployeeID = E.EmployeeID
GROUP BY E.EmployeeID, E.FullName, E.Role
) TEMP
)
ORDER BY OrderCount DESC, E.FullName ASC