Day 4: Practice Exercises

Hands-On SQL Assignments

Scenario: You manage a global database. Because of regional laws, customer data is stored in two separate tables: Customers_USA and Customers_UK. Both tables contain the columns: CustomerID, FullName, Email, and Age.

Task 1: Basic Combination

Write a query using UNION ALL to generate a complete master list of all FullName and Email addresses from both the USA and UK tables.
Some customers might be registered in both systems with the exact same email. Rewrite the query above using UNION to ensure the marketing team gets a list of unique emails without duplicates.

Task 2: Testing the Rules (Troubleshooting)

A junior developer wrote the following query, but SQL Server keeps throwing an error. Explain which rule this violates and rewrite it correctly.

SELECT FullName, Email, SignupDate FROM Customers_USA
UNION
SELECT FullName, Email FROM Customers_UK;

Task 3: Hardcoding and Sorting

Write a query to combine FullName and Email from both tables. However, add a third custom column to both SELECT statements called Region. Hardcode the value as 'USA' for the first table, and 'UK' for the second table.
Modify your query from the previous step so that the final combined result is sorted alphabetically by the FullName column.