-
Notifications
You must be signed in to change notification settings - Fork 0
/
session4.sql
48 lines (44 loc) · 1.05 KB
/
session4.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
SELECT * FROM food_db.items;
use food_db;
select * ,
concat(name,variant_name) as combination_name,
(price+variant_price) as final_price
from items
cross join variants;
use moviesdb;
select m.movie_id,a.name,m.title
from movies m
join movie_actor ma
on m.movie_id=ma.movie_id
join actors a
on a.actor_id=ma.actor_id;
select title,m.movie_id,group_concat(a.name)
from movies m
join movie_actor ma
on m.movie_id=ma.movie_id
join actors a
on a.actor_id=ma.actor_id
group by m.movie_id;
select a.name ,group_concat(m.title separator " | ") as movies,
count(m.title) as no_of_movies
from actors a
join movie_actor ma
on a.actor_id=ma.actor_id
join movies m
on m.movie_id=ma.movie_id
group by a.name
order by no_of_movies desc;
select
m.title,revenue,currency,unit,
case
when unit="thousand" then revenue/1000
when unit="billions" then revenue*1000
else revenue
end as revenue_mil
from movies m
join financials f
on m.movie_id=f.movie_id
join languages l
on m.language_id=l.language_id
where l.name="Hindi"
order by revenue_mil;