SQL code

SQL_192.sql

/* Show all databases in on scidb.smith.edu server. */
SHOW DATABASES;

/* Load the imdb database. */
USE imdb;

/* Show the names of all tables in imdb. */
SHOW TABLES;

/* 
select() all columns from title table using *. 
Scroll left-right to see all columns:
*/
SELECT * FROM title;

/* 
select() all columns from movie_info table using *. 
Scroll left-right to see all columns: 
*/
SELECT * FROM movie_info;

/*
What are the "key" variables that allow you to 
left_join() these tables?
*/
SELECT *
FROM movie_info 
LEFT JOIN title ON movie_info.movie_id = title.id;

/*
Urgh, typing movie_info and title over and over again is tedious.
Let's give them abbreviations mi and t.
*/
SELECT * 
FROM movie_info mi 
LEFT JOIN title t ON mi.movie_id = t.id;

/*
Let's select() only some of the columns from the respective
tables
*/
SELECT t.title, t.production_year, mi.info 
FROM movie_info mi 
LEFT JOIN title t ON mi.movie_id = t.id;

/*
Let's filter() the rows so that only rows where
info_type_id %in% c(67, 97) AND
the info variable containts the string '%NC-17%' AND
kind_id == 1
*/
SELECT t.title, t.production_year, mi.info 
FROM movie_info mi 
LEFT JOIN title t ON mi.movie_id = t.id
WHERE info_type_id IN (67, 97) AND info LIKE '%NC-17%' AND kind_id = 1;

/*
Let's arrange(production_year)
*/
SELECT t.title, t.production_year, mi.info 
FROM movie_info mi 
LEFT JOIN title t ON mi.movie_id = t.id
WHERE info_type_id IN (67, 97) AND info LIKE '%NC-17%' AND kind_id = 1
ORDER BY production_year;