Set Operations using Sakila

List all the unique email addresses from both the customer and staff tables in the Sakila database. Sort the results alphabetically by email address.

Combine first names and last names from both staff and customer tables. Return the columns first_name, last_name, and type. Use aliases to differentiate between staff and customers. Sort the results by last_name, then by first_name.

Retrieve all the dates related to rentals and payments, distinguishing between customer rentals and staff payments.

• Return the following columns:
  • rental_date (alias for both rental and payment dates)
  • type
• Use these aliases:
  • Customer Rental for rental dates
  • Staff Payment for payment dates
• Sort the results by rental date

Retrieve all the first names that are shared by both actors and customers.

• Return the first_name column
• Sort the results alphabetically by first_name

Fetch all the customers who are not also employed as staff members, based on their names. Return the columns first_name and last_name. Sort the results by last_name, then by first_name.

Fetch all the addresses used by both stores and staff, indicating the source of each address.

• Return the following columns:

  • address (from the address table)
  • source (an alias to indicate whether the address is for a Store or Staff)

• Perform the following joins:

  • Join the store table with the address table using address_id
  • Join the staff table with the address table using address_id

• Combine the results from both queries

• Sort the final result by the address column

1. Generate a combined list of all film titles and actor names, using aliases to distinguish between films and actors in the results.
2. Return two columns: name (containing either the film title or the actor's full name) and type (indicating whether the entry is a Film or an Actor).
3. For actors, concatenate their first and last names into a single field.
4. Sort the final results alphabetically by the name column.

1. Fetch all the unique city names that meet either of these criteria:
   • The city has at least one store located in it.
   • The city has at least one customer living in it.
2. Use subqueries to find the relevant city_id values from the address, store, and customer tables
3. Combine results from both conditions without duplicates
4. Return a single column named city containing the unique city names

1. Fetch all customer emails along with their active status.
2. Return two columns:
   • email: containing the customer's email address
   • status: an alias indicating whether the customer is Active or Inactive
3. Use a value of 1 in the active column to represent active customers, and 0 for inactive customers.
4. Combine the results for both active and inactive customers into a single list.

1. Connect three tables:

   • film (f): contains film information
   • film_category (fc): links films to their categories
   • category (c): contains category information

2. Return two columns:

   • title: name of the film from the film table
   • category: name of the category from the category table

3. Use JOIN conditions to link the tables:

   • Join film and film_category on film_id
   • Join film_category and category on category_id

4. Filter results using WHERE clause to include only Action and Comedy categories

5. Order the results first by category name, then by film title

1. Fetch all the rental and return dates for a specific customer (customer_id = 1).
2. Return two columns:
   • date: containing either the rental_date or return_date
   • action: an alias indicating whether the date represents when the film was Rented or Returned
3. Include all rentals for the customer, but only include return dates that are not null.
4. Combine the rental and return information into a single list.
5. Sort the results by date in ascending order.

1. Connect the actor, film_actor, film, film_category, and category tables to link actors with film categories
2. Return three columns:
   • actor_id: unique identifier for each actor
   • first_name: actor's first name
   • last_name: actor's last name
3. Use EXCEPT to exclude actors who have appeared in films with category names Action or Comedy
4. Order the final results by actor_id in ascending order

1. Connect the film table with the inventory table twice:

   • First join (i1) links films to inventory items in store_id 1
   • Second join (i2) links films to inventory items in store_id 2

2. Return two columns:

   • film_id: unique identifier for each film
   • title: name of the film

3. Use JOIN conditions to ensure films are present in both store_id 1 and store_id 2:

   • i1.store_id = 1 for the first join
   • i2.store_id = 2 for the second join

4. Apply DISTINCT to remove any potential duplicate rows resulting from multiple inventory items per film in each store

5. Order the final results by film title alphabetically

1. Connect the actor, film_actor, film, film_category, and category tables to link actors with film genres
2. Return three columns:
   • actor_id: unique identifier for each actor
   • first_name: actor's first name
   • last_name: actor's last name
3. Use subqueries to get the category_id for Comedy and Drama genres
4. Use UNION to combine actors from both Comedy and Drama films into a single list
5. Include DISTINCT to prevent duplicate actor entries

1. Use the customer table to check customer activity status
2. Return four columns:
   • customer_id: unique identifier for each customer
   • first_name: customer's first name
   • last_name: customer's last name
   • status: custom column labeled either Active or Inactive
3. Use UNION to combine active customers (where active = 1) and inactive customers (where active = 0)
4. Order the final results by customer_id in ascending order