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.
table columns colleges:ida numeric key for the college (integer) namethe name of the college (text) citythe name of city where the college is (text) statethe two-letter postal state abbrevation (text) zipthe zip code (text) majors:cipa numeric key for the major (integer) majorthe name of the major (text) grads:ida reference to colleges.id(integer)yearthe year the data is from, between 2009 and 2012 (integer) cipa reference to majors.cip(integer)isfirst1 if this count is of first majors, 0 for second majors (integer) numthe number of students in this category (integer)
Sketch the above table in Vertabelo, denoting all primary and foreign key relations. Export your schema both as SQL and a PNG.
Your assignment is to compose and test queries that accomplish each of the following using the above-described database.
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.)
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.
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.)
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.