Bases de Dados (Aula 7)

1)

(
    SELECT first_name, last_name
    FROM customer
) UNION ALL (
    SELECT first_name, last_name
    FROM staff
)
ORDER BY first_name, last_name;

--------------------------------------------------

2)

SELECT title
FROM film
WHERE (
    SELECT MAX(length)
    FROM film
) = length
ORDER BY title;

--------------------------------------------------

3)

SELECT title
FROM film
WHERE film_id NOT IN (
    SELECT film_id
    FROM inventory
    WHERE inventory_id IN (
        SELECT inventory_id
        FROM rental
        WHERE customer_id IN (
            SELECT customer_id
            FROM customer
            WHERE first_name = "LEE" AND last_name = "HAWKS"
))) AND
film_id IN (
    SELECT film_id
    FROM inventory
    WHERE inventory_id IN (
    SELECT inventory_id FROM rental
))
ORDER BY title;

--------------------------------------------------

4)

SELECT title
FROM film
WHERE film_id NOT IN (
    SELECT film_id
    FROM film_actor
)
ORDER BY title;

--------------------------------------------------

5)

SELECT title
FROM film
WHERE film_id NOT IN (
    SELECT film_id
    FROM inventory
    WHERE inventory_id IN (
        SELECT inventory_id
        FROM rental
)) AND film_id IN (
    SELECT film_id FROM inventory
) ORDER BY title;

--------------------------------------------------

6)

SELECT first_name, last_name
FROM customer
WHERE (
    SELECT COUNT(DISTINCT category_id)
    FROM film
    JOIN inventory ON (
        film.film_id = inventory.inventory_id
)) = (
    SELECT COUNT(DISTINCT category_id)
    FROM film
    JOIN inventory ON (
        film.film_id = inventory.film_id
    ) JOIN rental ON (
        inventory.inventory_id = rental.inventory_id
    ) WHERE rental.customer_id = customer.customer_id
)
ORDER BY first_name, last_name;

--------------------------------------------------

7)