Schema (IMDB data):

movie (id, title, year, color, country, language, distrVotes,imdbVotes, imdbRank, releaseDate)
genre (movieId, genre)
movieRef (fromId,toId)


-- Queries related to lectures 4 and 5, September 27 and October 4, 2011

use IMDB
show tables
describe movie


-- Cartesian product:
select * from person where name LIKE "Tom C%";
select * from movie where title LIKE "Rain %";
select * from movie, person where title LIKE "Rain %" and name LIKE "Tom C%";

-- Join:
select * from Movie, movieRef where title="Pulp Fiction" and id=fromId;

select M2.year from Movie M1, Movie M2, movieRef where M1.title="Pulp Fiction" and M1.id=fromId and M2.id=toId ORDER BY year;


-- String operations:

select * from movie where title = "pULP fIcTiOn";

select title from Movie where title LIKE "Pu__ Fiction";

select title from Movie where title LIKE "Pu__ %";

select title, imdbRank/10 AS prank from Movie where title LIKE "Pu__ %";


-- Distinct

select title, year from movie where country="Denmark" and year>2009;

select distinct(year) from movie where country="Denmark" and year>2009;


-- Aggregation:

SELECT SUM(height) FROM person;
SELECT AVG(height) FROM person;
SELECT SUM(height)/AVG(height) FROM person;
SELECT COUNT(height) FROM person;
SELECT COUNT(*) FROM person;


-- Grouping:

select country,avg(imdbRank) from movie group by country;

select country,avg(imdbRank) r from movie group by country order by r;

select country,avg(imdbRank) r from movie group by country having avg(imdbRank)> 7.2 order by r;

select country,avg(imdbRank) r from movie group by country having country LIKE "N%" order by r


-- Subqueries:

select * from (select * from movie where year=1994) A where country="Denmark";

select * from movie where title in (select country from movie);


-- Views, outer joins:

CREATE VIEW BestMovies AS (SELECT id,title,year,country,imdbRank FROM Movie WHERE imdbRank>9.3);

CREATE VIEW USAmovies AS (SELECT id,title,year,country,imdbRank FROM Movie WHERE country="USA");

SELECT BestMovies.title,BestMovies.country FROM (BestMovies LEFT JOIN USAMovies ON BestMovies.id=USAmovies.id) WHERE USAmovies.id IS NULL;


-- NULLs:

SELECT * FROM BestMovies WHERE ((country="Canada") or (country!="Canada" and imdbRank>9.5));
-- Works correctly if no country is the empty string, but incorrectly if it is null.

UPDATE Movie SET country=null WHERE id=643;

SELECT * FROM BestMovies WHERE ((country="Canada") or (country!="Canada" and imdbRank>9.5));
-- "Band of brothers" is gone!

select count(*) from Person;
select count(height) from Person;

select count(*) from Person where height IN (null);
select count(*) from Person where height IS NULL;
select count(*) from Person where height=NULL;

select count(*) from Person where (height,gender) IN ((null,'m'));
select count(*) from Person where (height,gender) NOT IN ((null,'m'));
select count(*) from Person where gender!='m';