Due Sep 26, 2024
Similar to Assignment 03, 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)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 US customer names and zip codes.
Problem 2 (Sales)¶
Show all offices that are outside the US
Problem 3 (Sales)¶
Show the emails of all employees from Office 1.
Problem 4 (Sales)¶
Show the comments from all unshipped orders
Problem 5 (Sales)¶
Show all products names where the scale is greater than 1:50 (e.g. 1:72)
Hint: You will need to learn about
Hint: You should get 14 rows
Problem 6 (Sales)¶
Show all order numbers that were shipped late, i.e. where the shipped date was after the required date.
Hint: there is only one such order
Problem 7 (Sales)¶
Show all the order numbers for the orders that took a week or longer to ship, i.e. the difference between the order date and shipped date.
Hint: This will require learning about MySQL date functions
Problem 8 (Sales)¶
Show the product names for all motorcycles along with their markup price (the difference between MSRP and buy_price)
Problem 9 (University)¶
Show all students names with the initials R. S. (i.g. Rachel Shelton)
Problem 10 (University)¶
Show the student ids for all Math majors who declared their majors 2024.
Problem 11 (Nation)¶
Show all countries greater than 1 mil sq km that don’t have a national day.
Problem 12 (Nation)¶
Show the names of countries whose national day is before the year 1800.