Identify SQL Joins and Correct Query Errors
Company: Amazon
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
Winner
+----+-------+
| ID | Name |
+----+-------+
| 1 | Alice |
| 2 | Bob |
| 3 | Carol |
+----+-------+
Loser
+----+-------+
| ID | Name |
+----+-------+
| 4 | Dave |
| 5 | Erin |
| 6 | Frank |
+----+-------+
##### Scenario
Database fundamentals and querying names from separate winner and loser tables
##### Question
What is a primary key and why is it important in relational databases? List the different types of SQL joins you know and explain when you would use each. The following query has errors. Identify and correct them:
select name MONTH(timestamp)
FROM table
WHEREMONTH(timestamp)>2010 Given tables Winner(ID, Name) and Loser(ID, Name), write a query that outputs all names in a single column. Use both UNION and UNION ALL, and explain the difference between the two operators.
##### Hints
Review relational keys, join semantics, date functions, UNION vs UNION ALL, and standard SQL syntax corrections.
Overview: This question evaluates relational database fundamentals including the role of primary keys, SQL join types, set operations (UNION vs UNION ALL), date functions, and the ability to debug and correct SQL query syntax, reflecting both conceptual understanding and practical query-writing skills.
Verify Primary Key Uniqueness
Show that Winner.ID (the primary key) is unique by returning the total number of rows and the count of distinct IDs from the Winner table.
Tables
Winner(ID INTEGER, Name VARCHAR(50))
Hints
- Use COUNT(*) and COUNT(DISTINCT ID) in the same SELECT.
- If the two counts are equal, the primary key column has no duplicates.
List All Winner-Loser Pairs
Return all possible pairs of Winner and Loser names using a CROSS JOIN. Output columns as WinnerName and LoserName, ordered alphabetically by WinnerName and then LoserName.
Tables
Winner(ID INTEGER, Name VARCHAR(50))
Loser(ID INTEGER, Name VARCHAR(50))
Hints
- Use CROSS JOIN to produce the Cartesian product of the two tables.
- Order by both WinnerName and LoserName for deterministic output.
Fix Function and WHERE Syntax
Correct the erroneous query "select Name LENGTH(Name) FROM Winner WHERELENGTH(Name) > 3" so that it properly lists each Winner name with its character length where the length is greater than 3.
Tables
Winner(ID INTEGER, Name VARCHAR(50))
Hints
- Add a comma between expressions in the SELECT list.
- Put a space after WHERE and alias the computed column.
Combine Names With UNION and UNION ALL
Write two queries that output a single-column list of all names from Winner and Loser: one using UNION and one using UNION ALL. Order by Name for deterministic results and be prepared to explain the difference between the two set operators.
Tables
Winner(ID INTEGER, Name VARCHAR(50))
Loser(ID INTEGER, Name VARCHAR(50))
Hints
- UNION removes duplicate rows, whereas UNION ALL preserves them.
- Both SELECT statements combined by UNION or UNION ALL must have the same number of columns and compatible data types.