import pymysql
pymysql.install_as_MySQLdb()
%load_ext sql
%config SqlMagic.autocommit=True
%sql mysql://root:simple@127.0.0.1

import datajoint as dj

sales = dj.Schema('classicsales')
sales.spawn_missing_classes()

nations = dj.Schema('nation')
nations.spawn_missing_classes()

hotel = dj.Schema('hotel')
hotel.spawn_missing_classes()

university = dj.Schema('university')
university.spawn_missing_classes()

app = dj.Schema('app')
app.spawn_missing_classes()

[2023-10-31 22:53:10,315][INFO]: Connecting root@fakeservices.datajoint.io:3306
[2023-10-31 22:53:10,987][INFO]: Connected root@fakeservices.datajoint.io:3306

dj.Diagram(sales)

dj.Diagram(nations)

dj.Diagram(hotel)

dj.Diagram(university)

dj.Diagram(app)

dj.Diagram(sales) + dj.Diagram(university) + dj.Diagram(nations) + dj.Diagram(hotel) + dj.Diagram(app)

Homework 6: Joins and Subqueries¶

The following queries may require joins. Some queries may be performed using subquiries without joins.

Problem 1 (sales): Show customer names along with the last names of their sales rep (omitting ones that don’t have a sales rep).

Problem 2 (sales): Show all employees, including the last name of their boss (omitting the top boss who reports to no one).

Problem 3 (sales): Show all employees whose boss’ office is outside the USA.

Problem 4 (sales): Show all employees whose boss is in a different office.

Problem 5 (sales): Show all customers who have bought model trains.

Problem 6 (sales): Show all employees who have not sold model trains.

Problem 7 (nations): Show the names of all countries in North America along with their populations in 1996

Problem 8 (nations): Show the names of countries on the continent of Oceania along with their populations in 1996

Problem 9 (nations): Show the top 10 contries by their absolute population increase between 1990 and 2010.

Problem 10 (nations): Show the top 10 contries by their percent increase in per capita gdp from 1990 to 2010.

Problem 11 (nations): List the top 5 most populous countries where Spanish is the official languag in 2010.

Problem 12 (nations): List the top 10 wealthiest (per capita) non-English speaking countries in 2015.

Problem 13 (hotel): List all the reservations for 2023-11-01, including the room price, and the guest’s last name. (Feel free to pick a different date.)

Problem 14 (hotel): Show all guests who have checked in and not checked out.

Problem 15 (university): Pick one student and show his or her course enrollments in the current term.

Problem 16 (university): Show all students who have received As in math in the current term.

Problem 17 (app): List names of the buyers from the latest 10 sales of the Marathon app.

Problem 18 (app): List the latest purchase made on the buyers’ birthday, including the name of the addon that was purchased.

dj.Diagram(app)

((Purchase * AddOn * Account & 'month(dob) = month(purchase_date)' 
& 'day(dob) = day(purchase_date)').proj('purchase_date', 'addon_name')).fetch(
    order_by='purchase_date desc', limit=1, format='frame')

%%sql
use app

 * mysql://root:***@127.0.0.1
0 rows affected.

[]

%%sql
show tables

%%sql
-- show the latest purchase made on a person's birthday, show the addon name
SELECT phone, addon_name, purchase_date FROM account NATURAL JOIN purchase NATURAL JOIN `#add_on` 
WHERE month(purchase_date) = month(dob) AND day(purchase_date) = day(dob)
ORDER BY purchase_date DESC
LIMIT 1

%%sql
-- show the latest purchase made on a person's birthday, show the addon name
SELECT phone, addon_name, purchase_date FROM account NATURAL JOIN purchase NATURAL JOIN `#add_on` 
WHERE month(purchase_date) = month(dob) AND day(purchase_date) = day(dob)
AND purchase_date = (
    SELECT purchase_date FROM account NATURAL JOIN purchase NATURAL JOIN `#add_on` 
    WHERE month(purchase_date) = month(dob) AND day(purchase_date) = day(dob)
    ORDER BY purchase_date DESC
     LIMIT 1)

dj.Diagram(nations)

# problem 7
Regions()

((Continents.proj(..., continent_name="name") * 
Regions.proj(..., region_name="name") * 
Countries.proj(..., country_name="name") * CountryStats())
 & 'continent_name="North America"' & 'year=1996').proj('country_name', 'population')

dj.config['display.limit'] = 30

%%sql
use nation;

SELECT countries.name, country_stats.population FROM country_stats NATURAL JOIN countries 
JOIN regions USING (region_id) JOIN continents USING (continent_id)
WHERE year=1996 and continents.name="North America"
ORDER BY population DESC
LIMIT 10

Countries - CountryStats

Queries

Joins