SQL as the query language in Intro to Computing - Practice Problems & Coding Challenges

Challenge - 5 Problems

🔍 Analysis

intermediate

What is the output of this SQL query?

Consider a table Employees with columns id, name, and salary. The table has the following rows:

• (1, 'Alice', 5000)
• (2, 'Bob', 7000)
• (3, 'Charlie', 7000)
• (4, 'Diana', 6000)

What will this query return?

SELECT name FROM Employees WHERE salary = (SELECT MAX(salary) FROM Employees);

Intro to Computing

SELECT name FROM Employees WHERE salary = (SELECT MAX(salary) FROM Employees);

A

Bob
Charlie

B

Alice
Diana

C Bob

D Charlie

💡 Hint

Think about which salary is the highest and which employees have that salary.

The subquery (SELECT MAX(salary) FROM Employees) returns 7000, the highest salary. The main query selects all employees with salary 7000, which are Bob and Charlie.

🧠 Conceptual

intermediate

Which SQL clause filters rows after grouping?

After grouping rows with GROUP BY, which SQL clause is used to filter groups based on a condition?

A HAVING

B WHERE

C ORDER BY

D FROM

💡 Hint

Remember, WHERE filters rows before grouping.

HAVING filters groups after GROUP BY. WHERE filters individual rows before grouping.

❓ trace

advanced

Trace the output of this SQL query with JOIN

Given two tables:

Students: (id, name)

• (1, 'Anna')
• (2, 'Ben')
• (3, 'Cara')

Scores: (student_id, score)

• (1, 85)
• (2, 90)
• (2, 95)
• (4, 80)

What is the output of this query?

SELECT Students.name, Scores.score FROM Students LEFT JOIN Scores ON Students.id = Scores.student_id ORDER BY Students.id, Scores.score DESC;

Intro to Computing

SELECT Students.name, Scores.score FROM Students LEFT JOIN Scores ON Students.id = Scores.student_id ORDER BY Students.id, Scores.score DESC;

A

Anna NULL
Ben 90
Ben 95
Cara 80

B

Anna 85
Ben 90
Cara NULL

C

Anna 85
Ben 90
Ben 95
Cara NULL

D

Anna 85
Ben 95
Ben 90
Cara NULL

💡 Hint

LEFT JOIN keeps all rows from Students even if no matching Scores exist.

Anna has one score 85, Ben has two scores 90 and 95 (ordered descending so 95 first), Cara has no score so NULL.

❓ identification

advanced

Identify the error in this SQL query

What error will this query produce?

SELECT name, COUNT(*) FROM Employees;

A TypeError: name column not found

B SyntaxError: Missing GROUP BY clause

C RuntimeError: COUNT(*) cannot be used with other columns

D No error, query runs successfully

💡 Hint

When using aggregation functions with other columns, what else is required?

When selecting a column and an aggregate function together, SQL requires a GROUP BY clause for the non-aggregated column.

❓ Comparison

expert

Compare the results of these two queries

Given a table Orders with columns order_id, customer_id, and amount, which statement is true?

Query 1: SELECT customer_id, SUM(amount) FROM Orders GROUP BY customer_id HAVING SUM(amount) > 1000;

Query 2: SELECT customer_id, SUM(amount) FROM Orders WHERE amount > 1000 GROUP BY customer_id;

A Query 1 filters rows with amount > 1000; Query 2 filters groups with total amount > 1000

B Both queries return the same results

C Query 1 filters groups with total amount > 1000; Query 2 filters rows with amount > 1000 before grouping

D Query 1 will cause a syntax error; Query 2 runs correctly

💡 Hint

Consider when filtering happens in each query.

Query 1 uses HAVING to filter groups after aggregation. Query 2 filters rows before aggregation with WHERE, so only amounts > 1000 are summed.