Lab 7: SQL Queries

Overview

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

Materials

• SQLite3
• graddata.db

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.

• Boats ER in Draw.io
• Boats Schema in Vertabelo
• SQL code for creating the boats database
• SQLite Boats database
• Example SQL Queries for Boats

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.

Tables

colleges 

column	description	type
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

column	description	type
cip	a numeric key for the major	integer
major	the name of the major	text

grads

column	description	type
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

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

• Screencapture of your certificate of completion from Codecademy.
• ASCII text solution file for your queries. Please number your solutions according to the below list.

Grading

To Partially Complete this lab, you must

• complete the Codecademy tutorial and two queries

To Complete this lab, you must do the above and

• complete all four queries.

---

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