Assignment 05 - The DataJoint Book

Due Oct 10, 2024

Similar to Assignment 03, we will make use of Example Databases.

Populate the Example Databases by executing five scrips:

If you populate everything right, the following code should produce the schema diagrams for the example databases. You can refer to them when writing queries.

# Diagrams 
import datajoint as dj

sales = dj.VirtualModule('sales', 'classicsales')
university = dj.VirtualModule('university', 'university')
nation = dj.VirtualModule('nation', 'nation')

dj.Diagram(sales) + dj.Diagram(university) + dj.Diagram(nation)

import pymysql
import os
pymysql.install_as_MySQLdb()

connection_string = "mysql://{user}:{password}@{host}".format(
    user=os.environ['DJ_USER'],
    host=os.environ['DJ_HOST'],
    password=os.environ['DJ_PASS']
)

%load_ext sql
%sql $connection_string

Problems¶

Write the following queries. Make sure that at least one query is written in DataJoint, one in SQL IPython Magic, and one using a mysql client cursor.

All the queries in this assignment require only one table. No multi-table queries will be required here.

To get the full credit, the query must be work correctly and should be minimal, without any extraneous information.

Problem 1 (Sales)¶

Show all the employees in the USA offices.

Problem 2 (Sales)¶

Show all customers whose sales rep is in the USA.

Problem 3 (Sales)¶

Show all customers who hae bought items of over $200 in price.

Problem 4 (Sales)¶

Show all employees whose boss works in a different office than they.

Problem 5 (Sales)¶

Show all customers who have never made purchses on Saturday or Sunday.

Problem 6 (Nation)¶

Show the names of countries where Spanish is spoken but is not the official language.

Problem 7 (Nation)¶

List all country names and their 2018 GDP in North and South America.

Problem 8 (Nation)¶

List all country names whose per capita GDP exceeded 50,000 in 2018.

Problem 9 (University)¶

List all math majors who are not enrolled in a math course in the current term.

Problem 10 (University)¶

Show the names of all students from Texas who are math majors