SELECT with WHERE, ORDER BY, GROUP BY in DBMS Theory - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of SELECT with WHERE and ORDER BY

Given the table Employees with columns id, name, department, and salary, what is the output of this query?

SELECT name, salary FROM Employees WHERE department = 'Sales' ORDER BY salary DESC;

DBMS Theory

CREATE TABLE Employees (id INT, name VARCHAR(50), department VARCHAR(50), salary INT);
INSERT INTO Employees VALUES
(1, 'Alice', 'Sales', 70000),
(2, 'Bob', 'HR', 50000),
(3, 'Charlie', 'Sales', 60000),
(4, 'David', 'IT', 80000),
(5, 'Eve', 'Sales', 75000);

A [('Eve', 75000), ('Alice', 70000), ('Charlie', 60000)]

B [('Charlie', 60000), ('Alice', 70000), ('Eve', 75000)]

C [('Bob', 50000), ('David', 80000), ('Eve', 75000)]

D [('Alice', 70000), ('Charlie', 60000), ('Eve', 75000)]

💡 Hint

Focus on filtering by department 'Sales' and ordering salaries from highest to lowest.

The query filters employees in the 'Sales' department and orders them by salary descending. Eve has the highest salary (75000), followed by Alice (70000), then Charlie (60000).

❓ query_result

intermediate

Result count with GROUP BY and HAVING

Consider the table Orders with columns order_id, customer_id, and amount. What is the number of customers who have total order amount greater than 1000?

SELECT COUNT(*) FROM (SELECT customer_id, SUM(amount) AS total FROM Orders GROUP BY customer_id HAVING total > 1000) AS subquery;

DBMS Theory

CREATE TABLE Orders (order_id INT, customer_id INT, amount INT);
INSERT INTO Orders VALUES
(1, 101, 500),
(2, 102, 1200),
(3, 101, 600),
(4, 103, 700),
(5, 102, 300),
(6, 104, 400);

A 4

B 3

C 1

D 2

💡 Hint

Sum amounts per customer and count how many sums exceed 1000.

Customer 101 has total 1100 (500+600), customer 102 has 1500 (1200+300), customer 103 has 700, and customer 104 has 400. Only customers 101 and 102 exceed 1000, so count is 2.

📋 Factual

advanced

Identify the syntax error in GROUP BY query

Which option contains a syntax error in this SQL query?

SELECT department, COUNT(*) FROM Employees GROUP BY;

DBMS Theory

CREATE TABLE Employees (id INT, name VARCHAR(50), department VARCHAR(50));

A SELECT department, COUNT(*) FROM Employees GROUP BY department;

B SELECT department, COUNT(*) FROM Employees GROUP BY;

C SELECT department, COUNT(*) FROM Employees GROUP BY department ORDER BY COUNT(*) DESC;

D SELECT department, COUNT(*) FROM Employees GROUP BY department, name;

💡 Hint

GROUP BY must specify at least one column.

Option B ends with GROUP BY without specifying any column, which is a syntax error. GROUP BY requires at least one column name.

❓ optimization

advanced

Optimize query with WHERE and GROUP BY

You want to find the total sales per product only for products with sales above 5000. Which query is more efficient?

Options:
A)

SELECT product_id, SUM(sales) FROM Sales GROUP BY product_id HAVING SUM(sales) > 5000;

B)

SELECT product_id, SUM(sales) FROM Sales WHERE sales > 5000 GROUP BY product_id;

C)

SELECT product_id, SUM(sales) FROM Sales WHERE product_id IN (SELECT product_id FROM Sales GROUP BY product_id HAVING SUM(sales) > 5000) GROUP BY product_id;

D)

SELECT product_id, SUM(sales) FROM Sales GROUP BY product_id WHERE SUM(sales) > 5000;

DBMS Theory

CREATE TABLE Sales (sale_id INT, product_id INT, sales INT);

A Correct and efficient: filters after grouping using HAVING

B Incorrect: filters individual sales > 5000 before grouping, missing some totals

C Correct but less efficient: uses subquery to filter products

D Syntax error: WHERE cannot be used after GROUP BY

💡 Hint

Filtering aggregated results requires HAVING, not WHERE.

Option A correctly filters groups after aggregation using HAVING. Option A filters rows before aggregation, missing groups with total > 5000 but individual sales ≤ 5000. Option A works but is less efficient due to subquery. Option A is invalid syntax.

🧠 Conceptual

expert

Understanding ORDER BY with GROUP BY and aggregate functions

Given the table Transactions with columns transaction_id, customer_id, and amount, which query will list customers with their total transaction amount, ordered by total amount descending?

Options:
A)

SELECT customer_id, SUM(amount) FROM Transactions ORDER BY SUM(amount) DESC GROUP BY customer_id;

B)

SELECT customer_id, SUM(amount) FROM Transactions GROUP BY customer_id ORDER BY SUM(amount) DESC;

C)

SELECT customer_id, SUM(amount) FROM Transactions GROUP BY customer_id ORDER BY amount DESC;

D)

SELECT customer_id, SUM(amount) FROM Transactions ORDER BY amount DESC;

DBMS Theory

CREATE TABLE Transactions (transaction_id INT, customer_id INT, amount INT);

A Error: ORDER BY amount invalid because amount is not selected or grouped

B Syntax error: ORDER BY must come after GROUP BY

C Correct: groups by customer and orders by total amount descending

D Incorrect: does not group or aggregate totals per customer

💡 Hint

Remember the order of clauses: GROUP BY before ORDER BY, and ORDER BY can use aggregate functions.

Option C is correct syntax and logic: it groups by customer_id, sums amount, then orders by that sum descending. Option C has wrong clause order. Options C and D fail to properly aggregate and order by total amount.