Notes

--1) Titanic filmi için kullanılan oy sayısı ve puan bilgisini ekrana yazdırınız.
use Film
go

SELECT votes, score
FROM Movie
WHERE title='Titanic'

--select votes, score
--from movie
--where title='Titanic'

--2) Robert De Niro'nun oynadığı filmlerin isimlerini ekrana yazdırınız
SELECT title
FROM (Movie INNER JOIN Casting ON Movie.id=Casting.movieid)
INNER JOIN Actor ON Casting.actorid=Actor.id
WHERE name='Robert De Niro'

--select title
--from (movie inner join Casting on movie.id=Casting.movieid)inner join Actor on Casting.actorid=Actor.id
--where name='Robert De Niro'


--3) Rob Reiner isimli aktörün oynadığı filmlerin başrol oyuncularını yazdırınız.
SELECT name
FROM Actor
WHERE id IN(
SELECT actorid
FROM Casting
WHERE movieid in (
SELECT movieid
FROM (Movie INNER JOIN Casting ON Movie.id=Casting.movieid)
INNER JOIN Actor ON Casting.actorid=Actor.id
WHERE name='Rob Reiner'
) AND ord=1
)

--select name
--from Actor
--where id in (
--select actorid
--from Casting
--where movieid in(
--select actorid
--from (Movie inner join Casting on Movie.id=Casting.movieid) inner join Actor on Casting.actorid=Actor.id
--where name='Rob Reiner'
--) and ord=1
--)
--4) Star Wars filmlerinin isimlerini ve yıllarını ekrana yazdırınız
SELECT title, yr
FROM Movie
WHERE title LIKE 'Star Wars%'

--select title,yr
--from Movie
--where title like 'Star Wars%'


--5) Tom Cruise ile birlikte yrol almış bütün aktörlerin isimlerini yazdırınız

--select name
--from Actor
--where id in(select actorid
-- from Casting
-- where movieid in
-- (select movieid
-- from (Movie inner join Casting on Movie.id=Casting.movieid) inner join Actor on Casting.actorid=Actor.id
-- where name='Tom Cruise'
-- ))
-- and id not in(select id
-- from Actor
-- where name='Tom Cruise')




SELECT name
FROM Actor
WHERE id IN (
SELECT actorid/*Film ıd sinden Actorid buluyoruz*/
FROM Casting
WHERE movieid IN (
SELECT movieid /*tom cruisenin oynadığı filim Id si*/
FROM (Movie INNER JOIN Casting ON Movie.id=Casting.movieid)
INNER JOIN Actor ON Casting.actorid=Actor.id
WHERE name='Tom Cruise'
)

)

AND id NOT IN (/*tomCruise yi çıkartıyoruz*/
SELECT id
FROM Actor
WHERE name='Tom Cruise'
)


--6) Casablanca filminin yılını ve skorunu ekrana yazdırınız
--Select yr,score
--from Movie
--where title='Casablanca'

SELECT yr,score
FROM Movie
WHERE title='Casablanca'

--7) Angelina Jolie 'nin oynadığı filmlerin isimlerini ekrana yazdırınız.
--select title
--from (Movie inner join Casting on Movie.id=Casting.movieid) inner join Actor on Casting.actorid=Actor.id
--where name='Angelina Jolie'

SELECT title
FROM (Movie INNER JOIN Casting ON Movie.id=Casting.movieid)
INNER JOIN Actor ON Casting.actorid=Actor.id
WHERE name='Angelina Jolie'

--8) Robin Williams ve Tom Hanks'in berarber oynadığı filmleri ekrana yazdırınız

--Select title
--from (Movie inner join Casting on Movie.id=Casting.movieid)inner join Actor on Casting.actorid=Actor.id
--where name= ' Robin Williams'
--intersect
--select title
--from (Movie inner join Casting on Movie.id=Casting.movieid)inner join Actor on Casting.actorid=Actor.id
--where name='Tom Hanks'

SELECT title
FROM (Movie INNER JOIN Casting ON Movie.id=Casting.movieid)
INNER JOIN Actor ON Casting.actorid=Actor.id
WHERE name='Robbin Williams'
INTERSECT
SELECT title
FROM (Movie INNER JOIN Casting ON Movie.id=Casting.movieid)
INNER JOIN Actor ON Casting.actorid=Actor.id
WHERE name='Tom Hanks'

--9) Terminator filmlerinin isimlerini ve yıllarını ekrana yazdrınız.
--Select title,yr
--from Movie
--where title like 'Terminator%'

SELECT title, yr
FROM Movie
WHERE title LIKE 'Terminator%'

--10) Johnny Depp 'in oynadığı filmlerin yönetmenlerini ekrana yazdırınız

--select name
--from Actor
--where id in(select director
-- from (Movie inner join Casting on Movie.id=Casting.movieid)inner join Actor on Casting.actorid=Actor.id
-- where name='Johnny Depp')
SELECT name
FROM Actor
WHERE id IN (
SELECT director
FROM (Movie INNER JOIN Casting ON Movie.id=Casting.movieid)
INNER JOIN Actor ON Casting.actorid=Actor.id
WHERE name='Johnny Depp'
)