SQL TREASURE HUNT

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