--Insructions:
--uncomment lines of sql to try them out

use imdb_new;

--anyone famous in your distant family tree?
/*SELECT * 
FROM Actor 
WHERE lname='Swisher';
*/

--movies with your favorite star
/*SELECT * 
FROM Movie m, Casts c, Actor a 
WHERE a.fname='gerard' AND a.lname='butler' AND c.pid = a.id AND c.mid = m.id;
*/

--oldest movies
/*SELECT m.id,m.name,m.year 
FROM Movie m,  (SELECT MIN(x.year) year 
		FROM Movie x) oldest 
WHERE m.year <= oldest.year+350;
*/

--group by, how many per year from the start
/*SELECT COUNT(m.id),m.year 
FROM Movie m,  (SELECT MIN(x.year) year 
		FROM Movie x) oldest 
WHERE m.year <= oldest.year+350 Group BY m.year;
*/

--what years has there been less than 100 movies made?
/*SELECT COUNT(*), m.year 
FROM Movie m 
GROUP BY m.year 
HAVING COUNT(*) < 100;
*/

--what years has there been less than x horror movies made?
/*SELECT COUNT(*), m.year FROM Movie m, Genre g 
WHERE m.id = g.mid AND g.genre='horror' 
GROUP BY m.year 
HAVING COUNT(*) < 30;
*/

--how many movies have Adam Sandler and Rob Schneider been in together?
/*SELECT m.*, c1.role rob, c2.role adam 
FROM Actor rob, Actor adam, Movie m, Casts c1, Casts c2 
WHERE adam.fname='adam'AND adam.lname='sandler' AND rob.fname='rob' AND rob.lname='schneider' AND m.id = c1.mid AND c1.mid = c2.mid AND c1.pid = rob.id AND c2.pid = adam.id;
*/

--or really for how many
/*SELECT COUNT(*) 
FROM Actor rob, Actor adam, Casts c1, Casts c2 
WHERE adam.fname='adam'AND adam.lname='sandler' AND rob.fname='rob' AND rob.lname='schneider' AND c1.mid = c2.mid AND c1.pid = rob.id AND c2.pid = adam.id;
*/

--how many movies has adam sandler been in without rob schneider?
/*SELECT * 
FROM Movie am, Casts ac, Actor aa 
WHERE aa.fname='adam' AND aa.lname='sandler' AND ac.pid=aa.id AND ac.mid=am.id AND am.id NOT IN (SELECT m.id FROM Movie m, Casts c, Actor rob WHERE rob.fname='rob' AND rob.lname='schneider' AND c.pid=rob.id AND c.mid=m.id);
*/

--really how many
/*SELECT COUNT(*) 
FROM Movie am, Casts ac, Actor aa 
WHERE aa.fname='adam' AND aa.lname='sandler' AND ac.pid=aa.id AND ac.mid=am.id AND am.id NOT IN (SELECT m.id FROM Movie m, Casts c, Actor rob WHERE rob.fname='rob' AND rob.lname='schneider' AND c.pid=rob.id AND c.mid=m.id);
*/
