University Queries

from university import *

dj.config['display.limit'] = 5  # keep ouptput short

dj.Diagram(schema)

assert Grade(), "populate the database first"

# All student info. Displaying a query gives you a preview of the results but does not fetch the data into memory.
Student()

# You can fetch the data into memory using the fetch() method.
query = Student()  # forms the query
data = query.fetch()  # fetches query results into memory
# See help(query.fetch) help help(query.fetch1) for more options.

data

# Show female students from Utah
Student & {'home_state': 'UT', 'sex': "F"}

# Show names female students not from Utah
((Student & {'sex': "F"}) - {'home_state': 'UT'}).proj('first_name', 'last_name')

# Show full names of female students not from Utah
(Student & "sex='F' AND home_state <> 'UT'").proj(full_name='CONCAT(first_name, " ",last_name)')

# Show the three youngest students named "David"
Student & {'first_name': 'David'} & dj.Top(limit=3, order_by='date_of_birth DESC')

# Show student names and their ages in years
Student.proj('first_name', 'last_name', age='FLOOR(DATEDIFF(CURDATE(), date_of_birth) / 365.25)')

# IDs of sudents whose birthday is today
(Student & 'month(date_of_birth) = month(CURDATE()) AND day(date_of_birth) = day(CURDATE())').proj()

# Students with their age and all other attributes
# ... is used to select all attributes in addition to the calculated ones
Student.proj(..., age='FLOOR(DATEDIFF(CURDATE(), date_of_birth) / 365.25)')

# Show all info except for the date of birth and phone number
Student.proj(..., '-date_of_birth', '-home_phone')

# Show all students who are either from Utah or California
Student & [{'home_state': 'UT'}, {'home_state': 'CA'}]  # condition in a list are combined with OR

# Show all students who are either from Utah or California
Student & 'home_state in ("UT", "CA")'   # this relies on SQL condition syntax

# Students with majors in math

Student & (StudentMajor & {'dept': 'MATH'})

# Students who have not taken a course in math

Student.proj('first_name', 'last_name') - (Enroll & {'dept': 'MATH'})

# Students with ungraded enrollments in current term

Student.proj('first_name', 'last_name') & ((Enroll & CurrentTerm) - Grade)

# All-'A' students
(Student.proj('first_name', 'last_name') & Grade) - (Grade - {'grade': 'A'})

# All unique first names among male students

dj.U('first_name') & (Student & {'sex': 'M'})

# Show all years of birth for students registered in the current term

dj.U('year') & (Student.proj(year='year(date_of_birth)') & (Enroll & CurrentTerm))

# Students with their majors

Student.proj('first_name', 'last_name') * StudentMajor

# Student names along with their majors and declaration dates

Student.proj('first_name', 'last_name') * StudentMajor

# Show students names along with their majors and declaration dates, includes students without majors
Student.proj('first_name', 'last_name').join(StudentMajor, left=True)

# Show departments with the number of students in each
Department.aggr(StudentMajor, count='COUNT(student_id)')

# Departments with the number of male and female students 
Department.aggr(StudentMajor*Student, males='SUM(sex="M")', females='SUM(sex="F")')

# Courses taught in the current term with student enrollment counts
Course.aggr(Enroll & CurrentTerm, ..., students='COUNT(student_id)')

# Students and their GPA and total credits
Student.aggr(Course*Grade*LetterGrade,
    'first_name', 'last_name', credits='sum(credits)', gpa='sum(points*credits)/sum(credits)')

# Top five students by GPA with at least 12 credits
Student.aggr(Course*Grade*LetterGrade,
    'first_name', 'last_name', credits='sum(credits)', gpa='sum(points*credits)/sum(credits)'
    ) & 'credits > 12' & dj.Top(limit=5, order_by='gpa DESC')