SQL_CodeBasics/sql5.5.sql at main · suyogghate/SQL_CodeBasics · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
USE moviesdb;

SELECT m.movie_id, title, budget, revenue, currency, unit, (revenue-budget) AS profit
FROM movies m
JOIN financials f
ON m.movie_id = f.movie_id;

SELECT m.movie_id, title, budget, revenue, currency, unit, (revenue-budget) AS profit
FROM movies m
JOIN financials f
ON m.movie_id = f.movie_id
WHERE industry = "bollywood"
ORDER BY profit DESC;

SELECT m.movie_id, title, budget, revenue, currency, unit,
	CASE
		WHEN unit="thousands" THEN ROUND((revenue-budget)/1000, 2)
        WHEN unit="billions" THEN ROUND((revenue-budget)*1000, 2)
        ELSE ROUND((revenue-budget), 2)
	END AS profit_mn
FROM movies m
JOIN financials f ON m.movie_id = f.movie_id
WHERE industry = "bollywood"
ORDER BY profit_mn DESC;