Font size
WorksheetsSQL TREASURE HUNT
Total questions: 20
Worksheet time: 11mins
SELECT Location, SUM(Quantity) FROM Inventory GROUP BY Location HAVING COUNT(DISTINCT ItemName) > 1;
Which location has more than one distinct item and what is the sum of the quantities there?
Warehouse A, 15
Warehouse B, 35
Warehouse A, 25
Warehouse B, 25
The minimum number of tables needed to represent M, N, P, R1, and R2 is
2
3
4
5
Which SQL query will find orders placed in the last 30 days that have no matching customer?
SELECT order_id FROM Orders
WHERE order_date >= CURDATE() - INTERVAL 30 DAY
AND customer_id IS NULL;
SELECT o.order_id FROM Orders o
LEFT JOIN Customers c ON o.customer_id = c.customer_id
WHERE c.customer_id IS NULL AND o.order_date >= CURDATE() - INTERVAL 30 DAY;
SELECT order_id FROM Orders
WHERE order_date >= DATE_SUB(NOW(), INTERVAL 30 DAY);
SELECT o.order_id FROM Orders o
JOIN Customers c ON o.customer_id = c.customer_id
WHERE o.order_date >= CURDATE() - INTERVAL 30 DAY;
SELECT ProductID FROM Sales GROUP BY ProductID HAVING COUNT(DISTINCT SaleDate) > 2;
Which product ID was sold on more than two distinct dates?
101
102
103
None
What is the minimum number of table needed ?
one
two
four
three
Which SQL query will return transactions with a NULL amount?
SELECT * FROM Transactions WHERE amount = 0;
SELECT * FROM Transactions WHERE amount = NULL;
SELECT * FROM Transactions WHERE amount IS NULL;
SELECT * FROM Transactions WHERE amount NOT NULL;
Which query correctly retrieves these products?
SELECT p.product_id, p.name FROM Products p
JOIN Discounts d ON p.product_id = d.product_id
WHERE d.discount_percentage > 50 AND p.category = 'Luxury';
SELECT p.product_id, p.name FROM Products p
WHERE discount_percentage > 50 AND category = 'Luxury';
SELECT p.product_id, p.name FROM Discounts d
WHERE discount_percentage > 50 AND category = 'Luxury';
SELECT product_id, name FROM Products
WHERE discount_percentage > 50 OR category = 'Luxury';
SELECT EmployeeID FROM Projects GROUP BY EmployeeID HAVING COUNT(*) > 1 AND MAX(EndDate) > '2023-11-01';
Which Employee ID worked on more than one project and had at least one project ending after '2023-11-01'?
1
2
3
1,2
In an Entity-Relationship (ER) model, suppose R is a many-to-one relationship from entity set E1 to entity set E2. Assume that E1 and E2 participate totally in R and that the cardinality of E1 is greater than the cardinality of E2.
Which one of the following is true about R?
Every entity in E1 is associated with at most one entity in E2.
Every entity in E1 is associated with exactly one entity in E2.
Every entity in E2 is associated with exactly one entity in E1.
Every entity in E2 is associated with at most one entity in E1.
SELECT AccountID FROM Transactions WHERE TransactionDate = '2023-10-27' INTERSECT SELECT AccountID FROM Transactions WHERE TransactionType = 'Debit';
Which Account ID had a Debit transaction on '2023-10-27'?
1001
1002
1003
1001,1002
SELECT StudentID, AVG(Grade) FROM CourseEnrollments GROUP BY StudentID HAVING COUNT(CourseID) > 1 AND AVG(Grade) > 85;
Which Student ID has taken more than one course and has an average grade greater than 85?
101
102
103
101,102
Which SQL query will find users who accessed the system more than 5 times in an hour from different IP addresses?
SELECT user_id FROM AccessLogs
WHERE COUNT(ip_address) > 5 GROUP BY user_id;
SELECT user_id FROM AccessLogs
GROUP BY user_id, HOUR(timestamp)
HAVING COUNT(DISTINCT ip_address) > 5;
SELECT user_id FROM AccessLogs
WHERE timestamp >= NOW() - INTERVAL 1 HOUR
GROUP BY user_id HAVING COUNT(ip_address) > 5;
SELECT DISTINCT user_id FROM AccessLogs
WHERE ip_address > 5;
Which SQL query retrieves employees without salaries?
SELECT e.emp_id, e.name FROM Employees e
JOIN Salaries s ON e.emp_id = s.emp_id
WHERE s.salary IS NULL;
SELECT e.emp_id, e.name FROM Employees e
LEFT JOIN Salaries s ON e.emp_id = s.emp_id
WHERE s.emp_id IS NULL;
SELECT e.emp_id, e.name FROM Employees e
WHERE emp_id NOT IN (SELECT emp_id FROM Salaries);
SELECT e.emp_id, e.name FROM Salaries s
WHERE emp_id IS NULL;
SELECT ProductID, SUM(Quantity Price) FROM ProductSales GROUP BY ProductID HAVING COUNT(DISTINCT SaleDate) > 2 AND SUM(QuantityPrice) > 100;
Which Product ID was sold on more than two distinct dates and has total sales revenue (Quantity * Price) greater than 100?
1001
1002
1003
1001,1002
Which SQL query will identify duplicate bookings where a customer has booked the same room on the same date more than once?
SELECT customer_id, room_id, booking_date
FROM Bookings
GROUP BY customer_id, room_id, booking_date
HAVING COUNT(*) > 1;
SELECT customer_id, room_id FROM Bookings
WHERE COUNT(*) > 1
GROUP BY customer_id, room_id, booking_date;
SELECT * FROM Bookings
WHERE room_id IN (SELECT room_id FROM Bookings GROUP BY room_id HAVING COUNT(*) > 1);
SELECT DISTINCT customer_id, room_id, booking_date
FROM Bookings
WHERE COUNT(*) > 1;
SELECT Department, AVG(Salary) FROM DepartmentSalaries GROUP BY Department HAVING COUNT(*) > 1 AND MAX(HireDate) > '2023-10-31';
Which department has more than one employee and has at least one employee hired after '2023-10-31'?
Sales
IT
Marketing
Sales, IT
SELECT EmployeeID FROM EmployeeProjects WHERE Role = 'Developer' GROUP BY EmployeeID HAVING COUNT(DISTINCT ProjectID) > 1;
Which Employee ID worked as a 'Developer' on more than one distinct project?
1
2
3
4
SELECT MemberID FROM LibraryLoans WHERE BookID = 101 GROUP BY MemberID HAVING COUNT(*) > 1;
Which Member ID borrowed Book ID 101 more than once?
201
202
203
201,203
SELECT StudentID FROM StudentCourses WHERE Semester = 'Spring 2023' GROUP BY StudentID HAVING AVG(Grade) > 90;
Which Student ID took courses in 'Spring 2023' and has an average grade greater than 90?
101
102
103
101,102
Which query retrieves the top 3 products based on sales volume?
SELECT product_id, SUM(quantity) AS total_sold
FROM Sales
WHERE sale_date >= NOW() - INTERVAL 6 MONTH
GROUP BY product_id
ORDER BY total_sold DESC
LIMIT 3;
SELECT product_id, COUNT(*) AS total_sold
FROM Sales
GROUP BY product_id
ORDER BY total_sold DESC
LIMIT 3;
SELECT product_id FROM Sales
ORDER BY quantity DESC
LIMIT 3;
SELECT product_id, SUM(quantity)
FROM Sales
GROUP BY product_id
HAVING SUM(quantity) > 3;
