class: center, middle, inverse, title-slide # Database querying with SQL ## Connect and example ### Ben Baumer ### SDS 192April 17, 2020(
) --- class: center, middle, inverse # An example --- ## *Henry & June (1990)* .footnote[https://en.wikipedia.org/wiki/Henry_%26_June] .pull-left[ !(https://images-na.ssl-images-amazon.com/images/M/MV5BNDUzNzQ1OTI3Ml5BMl5BanBnXkFtZTgwMjY5Mzk4NjE@._V1_UX182_CR0,0,182,268_AL_.jpg) ] .pull-right[ - The first film to earn an [NC-17](https://en.wikipedia.org/wiki/Motion_Picture_Association_of_America_film_rating_system#X_replaced_by_NC-17) rating from the MPAA was [Henry & June (1990)](https://en.wikipedia.org/wiki/Henry_%26_June). However, that is not the NC-17 film with the earliest `production_year` in the IMDB. - Write a query that returns the list of *feature-length* NC-17 movies. What are the main reasons given for NC-17 ratings? ] --- ## Connecting to MySQL .pull-left[ !(https://www.logolynx.com/images/logolynx/12/121d61bfeccafb13cd58c1f2e65ee1a6.png) ] .pull-right[ - Client GUI - [MySQL Workbench](https://dev.mysql.com/downloads/workbench/) - many other GUIs - Command line client - `mysql -h scidb.smith.edu` - R - [R Markdown integration!](https://bookdown.org/yihui/rmarkdown/language-engines.html#sql) - `dbplyr`, `DBI` - [db.rstudio.com](http://db.smith.edu) ] .footnote[https://dev.mysql.com/downloads/workbench/] --- ## Connect (pw: `DSismfc@S`) !(../gfx/scidb_workbench.png) --- ## Solution > Hint: Note that both `info_type = LD certification` and `info_type = mpaa` can contain MPAA ratings. ```sql 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; ``` - Most of the NC-17 ratings seem to be given for explicit sexual content.