Identify SQL Joins and Correct Query Errors

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.

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.

Table Winner

column	type	constraints
ID	INTEGER	PRIMARY KEY, NOT NULL
Name	VARCHAR(50)	NOT NULL

Sample data:

ID	Name
1	Alice
2	Bob
3	Carol

Expected output

The counts should match (3 and 3), demonstrating the uniqueness of the primary key Winner.ID.

distinct_ids	total_rows
3	3

Hints

1. Use COUNT(*) and COUNT(DISTINCT ID) in the same SELECT.
2. 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.

Table Winner

column	type	constraints
ID	INTEGER	PRIMARY KEY, NOT NULL
Name	VARCHAR(50)	NOT NULL

Sample data:

ID	Name
1	Alice
2	Bob
3	Carol

Table Loser

column	type	constraints
ID	INTEGER	PRIMARY KEY, NOT NULL
Name	VARCHAR(50)	NOT NULL

Sample data:

ID	Name
4	Dave
5	Erin
6	Frank

Expected output

All 3x3 combinations (9 rows) of Winner and Loser names.

LoserName	WinnerName
Dave	Alice
Erin	Alice
Frank	Alice
Dave	Bob
Erin	Bob
Frank	Bob
Dave	Carol
Erin	Carol
Frank	Carol

Hints

1. Use CROSS JOIN to produce the Cartesian product of the two tables.
2. 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.

Table Winner

column	type	constraints
ID	INTEGER	PRIMARY KEY, NOT NULL
Name	VARCHAR(50)	NOT NULL

Sample data:

ID	Name
1	Alice
2	Bob
3	Carol

Expected output

Names from Winner with more than 3 characters and their lengths.

Name	NameLength
Alice	5
Carol	5

Hints

1. Add a comma between expressions in the SELECT list.
2. Put a space after WHERE and alias the computed column.
3. Use LENGTH(Name) in both the SELECT list and WHERE clause.

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.

Table Winner

column	type	constraints
ID	INTEGER	PRIMARY KEY, NOT NULL
Name	VARCHAR(50)	NOT NULL

Sample data:

ID	Name
1	Alice
2	Bob
3	Carol

Table Loser

column	type	constraints
ID	INTEGER	PRIMARY KEY, NOT NULL
Name	VARCHAR(50)	NOT NULL

Sample data:

ID	Name
4	Dave
5	Erin
6	Frank

Expected output

A single-column list of all names from both tables. UNION removes duplicates; UNION ALL keeps them. With the provided data (no overlapping names), both queries return the same six names.

Name
Alice
Bob
Carol
Dave
Erin
Frank

Hints

1. UNION removes duplicate rows, whereas UNION ALL preserves them.
2. Both SELECT statements combined by UNION or UNION ALL must have the same number of columns and compatible data types.
3. Use ORDER BY Name at the end of each combined query for deterministic output.