Day 13

March 16, 2017

Constructing SQL queries

Let us try to write SQL queries that implement the movie review questions you asked. Change first X to a Z.

Document for successfully implemented queries Change first W to a V.

Cleaned up database

Jonathan has cleaned up the data and added some more reviews. moviedb_jb.sql

Here is some assistance:

SQLite3 from Python

To be frank with you, I find writing complex SQL queries highly aggravating because of the almost total lack of constructs in the language that would allow you divide and conquer a problem.

This can be partially resolved by using Python to assist in construction of the queries ...

import sqlite3

con = sqlite3.connect('mov.db')
cur = con.cursor()

q = 'select * from movie'
cur.execute( q )

for row in cur.fetchall():
        print( row )

Overview of our database

all the data in one table

Using Python to organize construction of compound queries

You can give names to queries and compose them by string concatenation, resulting in nested queries that are actually readable!

q0 = 'select foo from blah where thatthing = blah'
q1 = 'select otherthing from (' + q0 + ')'