Similar to previous assignments, we will make use of Example Databases.
Populate the Example Databases by executing five scrips:
The Sales Database
The Nation Database
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)# prepare SQL magic
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_stringProblems¶
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.
To get the full credit, the query must be work correctly and should be minimal, without any extraneous information.
Problem 1 (Sales)¶
Show all offices with their locations and the number of employees in each
Problem 2 (Sales)¶
Show all employees and the number of people who report to them.
Problem 3 (Sales)¶
Show all orders placed in January 2005, including their total amounts.
Problem 4 (Sales)¶
Show the total payment amounts collected in 2005
Problem 5 (Sales)¶
Show the top product for 2005 by total revenue generated.
Problem 6 (Sales)¶
Show the employees and the total sales amounts they have generated.
Problem 7 (Nation)¶
Show all continents, along with their populations and GDP in 2018.
Problem 8 (Nation)¶
Show the top 12 languages by the number of countries in which they are spoken.
Problem 9 (Nation)¶
Show the top 12 languages by the number of countries in which they are the official language.
Problem 10 (University)¶
Show all departments along with the numbers of students who have majored in them.
Problem 11 (University)¶
Show the top 10 students who have taken the most courses.
Problem 12 (University)¶
Show all the math courses offered in the current term and the number of students registered in each.
Problem 13 (University)¶
List all MATH courses along with the total number of students who have ever taken them.