class: center, middle, inverse, title-slide # Database querying with SQL ## Connect and example ### Ben Baumer ### SDS 192</br>April 17, 2020</br>(<a href="http://beanumber.github.io/sds192/lectures/mdsr_sql_02-connect.html" class="uri">http://beanumber.github.io/sds192/lectures/mdsr_sql_02-connect.html</a>) --- class: center, middle, inverse # An example --- ## *Henry & June (1990)* .footnote[https://en.wikipedia.org/wiki/Henry_%26_June] .pull-left[  ] .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[  ] .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`)  --- ## 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.