Introduction

Today’s SQL learning session covered important concepts like UNION, UNION ALL, data type changes in PostgreSQL, and CAST operations. These topics are crucial when handling large datasets, managing data types, and ensuring accurate data retrieval. Additionally, I attended a test in my classroom, where I encountered some challenging SQL queries, which I’ll also discuss in this blog.

1. UNION vs UNION ALL in PostgreSQL

Both UNION and UNION ALL are used to combine results from multiple SELECT queries, but they behave differently when handling duplicate values.

🔹 UNION

  • Combines results from multiple queries.
  • Removes duplicate values automatically.
  • Slower because it performs a sorting operation to eliminate duplicates.

Example: 

SELECT Movie_name FROM Cinema WHERE year >= 2020
UNION
SELECT Movie_name FROM Ratings WHERE ImDB_Rating > 7;

✅ This query returns unique movie names from both tables.

🔹 UNION ALL

  • Combines results from multiple queries.
  • Does NOT remove duplicates (faster performance).

Example: 

SELECT Movie_name FROM Cinema WHERE year >= 2020
UNION ALL
SELECT Movie_name FROM Ratings WHERE ImDB_Rating > 7;

✅ This query returns all movie names, including duplicates.

2. Data Type Changes in PostgreSQL

Sometimes, data in PostgreSQL is stored in the wrong format, and we need to convert it to the correct data type.

🔹 CAST Operation (Explicit Type Conversion)

  • Converts one data type to another.
  • Uses CAST(expression AS target_data_type).

Example 1: Converting Integer to Text 

SELECT CAST(2023 AS TEXT);

✅ This changes the integer 2023 to a text value.

Example 2: Converting Text to Integer 

SELECT CAST('100' AS INTEGER);

✅ This converts the string '100' into an integer.

🔹 Alternative Syntax for Casting

PostgreSQL also supports a shorthand syntax using :: for casting.

Example: 

SELECT 100::TEXT; -- Converts 100 to a text value
SELECT '123'::INTEGER; -- Converts text '123' to an integer

✅ Both return the same results as CAST().

3. DISTINCT – Removing Duplicates from Tables

The DISTINCT keyword is used to remove duplicate values from query results.

🔹 Example: 

SELECT DISTINCT Actor FROM Cinema;

✅ This retrieves unique actor names from the Cinema table.

SQL Test – Challenging Queries

During my test, I encountered some tough SQL questions. Below are the queries and their solutions.

1️⃣ List down all movies released after 2019 and acted by either Sasikumar or Vijay.

Solution: 

SELECT * FROM Cinema
WHERE year > 2019 AND (Actor = 'Sasikumar' OR Actor = 'Vijay');

✅ Retrieves movies after 2019, with Sasikumar or Vijay as the actor.

2️⃣ List down Ajith movies in alphabetical order.

Solution: 

SELECT Movie_name FROM Cinema
WHERE Actor = 'Ajith'
ORDER BY Movie_name ASC;

✅ Displays all Ajith movies in ascending order.

3️⃣ List down all movies where both Actor name and Movie name start with the same letter with their ImDB value in descending order.

Solution: 

SELECT c.Movie_name, c.Actor, r.ImDB_Rating 
FROM Cinema c
JOIN Ratings r ON c.id = r.id
WHERE LEFT(c.Movie_name, 1) = LEFT(c.Actor, 1)
ORDER BY r.ImDB_Rating DESC;

✅ Uses LEFT(column, 1) to compare the first letter of Movie_name and Actor.

4️⃣ List down all movies with less than 7 ImDB and Critique rating released between 2010 – 2020.

Solution: 

SELECT c.Movie_name, r.ImDB_Rating, r.Critique_Rating 
FROM Cinema c
JOIN Ratings r ON c.id = r.id
WHERE r.ImDB_Rating < 7 AND r.Critique_Rating < 7
AND c.year BETWEEN 2010 AND 2020;

✅ Retrieves movies between 2010 and 2020 with both ImDB and Critique Rating < 7.

Conclusion

Today's SQL session was exciting! Learning about UNION, UNION ALL, casting data types, and removing duplicates is essential for managing databases efficiently. The test was challenging, but it helped reinforce my understanding of writing complex SQL queries.

💡 Key Takeaways:

✔ UNION removes duplicates, while UNION ALL retains all values.

✔ CAST and :: are used for type conversions in PostgreSQL.

✔ DISTINCT eliminates duplicate records.

✔ Complex SQL queries require careful conditions and joins.