CSC330S2020SingleSQLLab < CenterForComputing

Tags: view all tags
---++Single Table SQL Lab
---+++Overview
   1. Use the data in the course, etc. tables
   1. Turn in a soft copy (a .sql file with just the sql statements and comments)  Be sure to label each statement/answer
---+++Questions
   * Part 1 (simple single queries)
      1. Display all the courses you have taken (code, number) ordered by code then number
      1. Display the courses taught by Dr. Bareiss (3249) last fall or Dr. Ramos (1366) last spring
      1. Display the ids of students who were in a math course taught by Prof. Campeau (1533) last fall (MATH 070 and MATH 111).  Do not use an IN operator.
      1. Display the ids of students who were in a math course taught by Prof. Campeau last fall (MATH 070 and MATH 111).  Use the IN operator.
      1. Display all courses taught within the Religion and Philosophy department that are lower level (not 300 or above).
      1. Display the emails and names of all professors in one column with just a space separating the two.
      1. Display all the courses (code, number, section, session) that don't have a start time
      1. Display all the different unique combinations of what days courses have been taught at Bethel
      1. Display the students whose email has yet to be updated from last year
   * Part 2 (using aggregation and/or functions)
      1. Display the courses you have taken (code, number, section, session) ordered by when they were taken.  Assume that there is at least three years of data to be dealt with.
      1. Display the number of distinct Biblical literature courses taught last semester.
      1. Display the number of instructors teaching English courses last semester.
      1. Display the number of instructors teaching English courses each semester that is in the database ordered by when they were taught. Don't worry if the summer session is out of order -> extra credit to fix that!
      1. Display the time of day (military time) that each sociology course was taught last semester
      1. Display the max course number and min course number offered by each "code" last semester
      1. Display the number of times each student has changed his/her major.
      1. For each student, classify them based upon how often they changed their major:  0 -> stable, 1-2 -> typical, 3-4 -> searching,  >= 5 -> unsure
      1. List the codes for the courses that have at least one class with an enrollment greater than 100 last semester
      1. Display all students names sorted alphabetically by last name then first name.  You may assume that each student only has two names listed.
Topic revision: r1 - 2021-10-19 - cathybareiss