databases.sql

-- Question #1
SELECT `name`, `date_of_birth` 
FROM `moviedata`.`actor`
WHERE `name` LIKE '% B%';

-- Question #2
SELECT `title`, `gross_earnings` 
FROM `moviedata`.`film`
WHERE `gross_earnings` > 100000000 
AND `gross_earnings` < 300000000
ORDER BY `gross_earnings` DESC;

-- Question #3
SELECT `name`, YEAR(`date_of_birth`) AS 'year_of_birth'
FROM `moviedata`.`actor` 
WHERE YEAR(`date_of_birth`) BETWEEN 1990 AND 2000
ORDER BY `name` DESC;

-- Question #4
SELECT 
	FORMAT(MIN(`gross_earnings`), 0) AS 'minimum_gross',
	FORMAT(MAX(`gross_earnings`), 0) AS 'maximum_gross',
	FORMAT(AVG(`gross_earnings`), 0) AS 'average_gross'
FROM `moviedata`.`film`;

-- Question #5
SELECT `nationality`, COUNT(*) AS 'no_of_actors'
FROM `moviedata`.`actor`
GROUP BY `nationality`
ORDER BY COUNT(*) DESC;

-- Question #6
SELECT `distributor_id`,
		MAX(`year`) AS 'most_recent_release_year',
		COUNT(*) AS 'number_of_films' 
FROM `moviedata`.`release`
GROUP BY `distributor_id`
HAVING COUNT(*) > 2;

-- Question #7
SELECT `film_id`, `title` 
FROM `moviedata`.`film`
WHERE `film_id` 
	IN
	(
		SELECT `film_id` FROM `moviedata`.`release` 
		WHERE `year` > 2000
	);

-- Question #8
SELECT f.`film_id`, `title`, `director`, `year`
FROM `moviedata`.`film` f
JOIN `moviedata`.`release` s
ON f.`film_id` = s.`film_id`;

-- Question #9
SELECT `title`, `name` AS 'distributor', `year`
FROM `moviedata`.`release` f 
	JOIN `moviedata`.`distributor` s ON f.`distributor_id` = s.`distributor_id`
	JOIN `moviedata`.`film` k ON f.`film_id` = k.`film_id`
WHERE `year` BETWEEN 1995 AND 2005;

-- Question #10
/*A film programmer wants to get a listing of all non-identical pairs of films directed by the
same director. Develop a query to generate the following result table: There are two possible*/
SELECT a.`director`, b.`title`, a.`title` 
FROM `moviedata`.`film` a
	JOIN `moviedata`.`film` b
    ON a.`film_id` != b.`film_id` 
    AND a.`director` = b.`director`
GROUP BY a.`director`;

SELECT DISTINCT a.`director`, b.`title`, a.`title`
FROM `moviedata`.`film` a, `moviedata`.`film` b
	WHERE a.`film_id` != b.`film_id`
	AND a.`director` = b.`director`
GROUP BY a.`director`;