CSCI 340 - Lab 4
SQL Schema and Queries


Overview

In this lab, you will practice using your SQL Schema and Query skills.

Materials

Tutorial

Follow the tutorial on Codecademy for Learn SQL to learn and practice basic skills of SQL tables and queries.

Review

In class, we discussed queries for the Boats database.

Description

Your assignment is to compose SQL queries for a variety of tasks based on a pre-built database.

To start SQLite, enter the directory where the data file is downloaded and issue the command “sqlite3 graddata.db”.

The database includes three tables based on publicly available data at the U S Department of Education, including data reported by each college on how many students from the college graduated with each possible major. Rather than working with their full data, though, this database includes just nine small Southern private colleges (Birmingham-Southern College, Centenary College of Louisiana, Centre College, Hendrix College, Millsaps College Rhodes College, Sewanee — University of the South, Southwestern University, and Trinity University), with Bachelor's degree data only. It includes data for four years, academic year 2008–09 through 2011–12.

tablecolumns
colleges id a numeric key for the college (integer)
name the name of the college (text)
city the name of city where the college is (text)
state the two-letter postal state abbrevation (text)
zip the zip code (text)
majors: cip a numeric key for the major (integer)
major the name of the major (text)
grads: id a reference to colleges.id (integer)
year the year the data is from, between 2009 and 2012 (integer)
cip a reference to majors.cip (integer)
isfirst 1 if this count is of first majors, 0 for second majors (integer)
num the number of students in this category (integer)

Schema

Sketch the above table in Vertabelo, denoting all primary and foreign key relations. Export your schema both as SQL and a PNG.

Queries

Your assignment is to compose and test queries that accomplish each of the following using the above-described database.

  1. For each college, list the number of 2009 graduates with a first major in computer science (CIP 110701), along with the college's name. (Don't worry about colleges with no computer science students.)

    name                         total
    ---------------------------  ----------
    Birmingham Southern College  3
    Hendrix College              9
    Centre College               4
    Millsaps College             7
    Southwestern University      1
    Trinity University           11
    
  2. For each college, list the total number of students graduating across all majors (count first major only) over the four-year period, along with the college's name.

    name                         total
    ---------------------------  ----------
    Birmingham Southern College  1128
    Hendrix College              1092
    Centre College               1121
    Centenary College of Louisi  641
    Millsaps College             881
    Rhodes College               1520
    Sewanee-The University of t  1378
    Southwestern University      1140
    Trinity University           2258
    
  3. For each college, list the total number of students majoring in computer science (first or second major) over the four-year period, along with the college's name, sorted in increasing order by the total number of computer science graduates. (Don't worry about colleges with no computer science students.)

    name                                 total
    -----------------------------------  ----------
    Sewanee-The University of the South  9
    Southwestern University              13
    Rhodes College                       14
    Birmingham Southern College          16
    Millsaps College                     22
    Centre College                       23
    Hendrix College                      30
    Trinity University                   62
    
  4. List the number of students completing each major at Hendrix (ID 107080) over the four-year period, along with the major name, sorted by the number of students.

    major                total
    -------------------  ----------
    Psychology, General  134
    Biology/Biological   118
    English Language an  80
    Economics, General   78
    Biochemistry and Mo  75
    Multi-/Interdiscipl  58
    International Relat  57
    History, General     55
    Sociology            42
    Anthropology         39
    Political Science a  38
    Chemistry, General   32
    Environmental Studi  31
    Computer Science     30
    Physics, General     30
    Health Services/All  29
    Art/Art Studies, Ge  28
    Spanish Language an  28
    Religion/Religious   26
    Philosophy           25
    Drama and Dramatics  21
    Accounting           20
    Kinesiology and Exe  18
    Mathematics, Genera  14
    Music, General       12
    Philosophy and Reli  10
    American/United Sta  9
    Elementary Educatio  9
    German Language and  8
    Classics and Classi  7
    Chemical Physics     5
    French Language and  5
    

What to Hand In

Grading


Thanks to Carl Burch much of the information in this assignment.