Homework 5. Simple Queries - The DataJoint Book

In this project, you will design simple queries that rely on relational restriction and projection. Some restrictions may require a subquery.

We will use the App database that was designed and populated in 004-Design.

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
schema = dj.Schema('app')
schema.spawn_missing_classes()
dj.Diagram(schema)

Problem 1 (solved). All accounts last_names for persons named “Paul” or “Paula” born in the 1990s¶


Account & 'first_name in ("Paul", "Paula")' & 'year(dob) between 1990 and 1999'

Problem 2: Show the 10 youngest males whose last names start with “Ra”¶

Hint: Look into the use of wildcard pattern matching in MySQL https://dev.mysql.com/doc/refman/8.0/en/pattern-matching.html

Account & 'sex="M"' & 'last_name LIKE "Ra%"' & 'dob BETWEEN "2004-01-01" AND "2023-01-01"'

(Account & 'sex="M"' & 'last_name LIKE "Ra%"').fetch(order_by='dob DESC', limit=10)

array([(84217041960, 'Timothy', 'Ramos', datetime.date(2023, 8, 14), 'M'),
       (48210412157, 'Charles', 'Ramirez', datetime.date(2022, 7, 19), 'M'),
       (48982711569, 'Matthew', 'Ramirez', datetime.date(2018, 10, 29), 'M'),
       (57783081396, 'Michael', 'Ramsey', datetime.date(2015, 2, 11), 'M'),
       (10856410224, 'Russell', 'Ramirez', datetime.date(2013, 3, 15), 'M'),
       (23530945303, 'Raymond', 'Ramirez', datetime.date(2012, 2, 24), 'M'),
       (61704905238, 'Bryan', 'Ramirez', datetime.date(2010, 5, 28), 'M'),
       (34265700814, 'Steven', 'Raymond', datetime.date(2007, 9, 26), 'M'),
       (13536738702, 'Charles', 'Ramirez', datetime.date(2006, 8, 12), 'M'),
       (11633552162, 'Daniel', 'Ramirez', datetime.date(1998, 1, 20), 'M')],
      dtype=[('phone', '<i8'), ('first_name', 'O'), ('last_name', 'O'), ('dob', 'O'), ('sex', 'O')])

(Account & 'sex="M"' & 'last_name LIKE "Ra%"').fetch(order_by='dob DESC', limit=10, format="frame")

%%sql
USE app; 
SELECT * 
  FROM account 
  WHERE sex="M" AND last_name LIKE "Ra%" 
  ORDER BY dob DESC LIMIT 10

Problem 3: Show full names of the people who omitted their date of birth. Sort them alphabetically by last name / first name. Show the first 10 only.¶

Hint: look into the use of string https://dev.mysql.com/doc/refman/8.0/en/string-functions.html

Hint: Comparing to NULL, use IS NULL or IS NOT NULL https://dev.mysql.com/doc/refman/8.0/en/working-with-null.html

(Account  & 'dob IS NULL').proj(full_name="CONCAT(last_name, ', ', first_name)").fetch(
    order_by= 'full_name', limit=10, format="frame")

%%sql

SELECT CONCAT(last_name, ', ', first_name) as full_name FROM account WHERE dob is NULL ORDER BY (full_name) LIMIT 10

Problem 4: Show the full names of all females born in June, also showing their age in years. Sort by last name / first name and show the first 10 only.¶

Hint: look into date functions https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html

%%sql
SELECT CONCAT(last_name," ",first_name) as full_name, 
   FLOOR(DATEDIFF(NOW(),dob) / 365.25) as age FROM account Where EXTRACT(MONTH FROM dob) = 6 and sex = 'F'
ORDER BY full_name
limit 10

(Account & 'sex = "F"' & 'MONTH(dob) = 6').proj(full_name='CONCAT(last_name, ", ", first_name)',
age='YEAR(CURDATE()) - YEAR(dob)').fetch(order_by='last_name, first_name', limit=10, format='frame')

%%sql

SELECT FLOOR(DATEDIFF(CURDATE(),  "2022-10-24") / 365.25) as age

Problem 5: Show the full information of the youngest person who has a credit card.¶

%%sql 

SELECT *
FROM account
WHERE phone IN (SELECT phone FROM credit_card)
ORDER BY dob DESC
LIMIT 1

(Account & CreditCard).aggr(Account.proj(excluded='sex'), dob='MIN(dob)', full_name='CONCAT(last_name, ", ", first_name)',
age_in_day='DAY(CURDATE()) - DAY(dob)').fetch(order_by='dob DESC', limit=1, format='frame')

Problem 6: Show the full information of the oldest person who does not have a credit card.¶

%%sql

SELECT *
FROM account
WHERE phone NOT IN (SELECT phone FROM credit_card) and dob IS NOT NULL
ORDER BY dob
LIMIT 1

Problem 7: Show the first 10 customers who purchased the “Sprint” addon, including their age in years.¶

%%sql
SELECT phone, first_name, last_name, sex, floor(datediff(now(), dob)/365.25) age  
FROM account
WHERE phone IN (SELECT phone FROM purchase WHERE addon_id=3 ORDER BY (purchase_date) LIMIT 10)

 * mysql://root:***@127.0.0.1
(pymysql.err.NotSupportedError) (1235, "This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'")
[SQL: SELECT phone, first_name, last_name, sex, floor(datediff(now(), dob)/365.25) age  
FROM account
WHERE phone IN (SELECT phone FROM purchase WHERE addon_id=3 ORDER BY (purchase_date) LIMIT 10)]
(Background on this error at: https://sqlalche.me/e/20/tw8g)

((Account * Purchase * AddOn) & ('addon_name = "Sprint"') & ('dob IS NOT NULL')
            ).aggr(Account.proj(excluded='sex'),
                   'last_name', 'first_name', 'purchase_date','addon_name', 
                    age='YEAR(CURDATE()) - YEAR(dob)'
               ).fetch(order_by='purchase_date', limit=10, format='frame')

Purchase() & "purchase_date < '2023-09-01'"

(Account * Purchase * AddOn & 'addon_name="Sprint"').fetch(order_by='purchase_date', limit=10, format="frame")

Operations

Transactions

Queries