5.42 KB
Newer Older
Sanjay Krishnan committed
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76
# SQL Exercises 

*Due 5/13/19 11:59 PM*
In this assignment, you will practice writing SQL queries against a synthetically generated
"Graduate Admissions" database. This database contains three tables: a table of students, a table of undergraduate institutions from which they came, and a table of cities from where the students attended high school. 
The tables are defined as follows:
create table students(id int, --student id number 
					  name varchar(64), --the full name of the student
					  college_id int, --the id of the college they attended
					  hometown_id, --the id of their home town
					  major varchar(64),  --their major
					  gpa float); --their gpa
The students table links to the college table and the hometown table with college_id and the hometown_id respectively:
cretea table colleges (id int, 
		               rank int, --the rank of the college 
		               name varchar(64), --the full name of the college
		               city varchar(32)); --the city in which the college is
create table hometown (id int, 
					   city varchar(32), --the name of the city
					   scode varchar(32), --the abbrev of the state (coded, e.g., CA)
					   state varchar(32), --the name of the state
					   county varchar(32), --the name of the county
					   population float);  --the name of the population of the city
You will write SQL queries to answer different questions across this dataset. Unlike the previous assignments, there will be no explicit tests. We will provide some guidance on what to expect but, you will have to convince yourselves that your solutions are correct.

## Getting Started
First, pull the most recent changes from the cmsc13600-public repository:
$ git pull
Then, copy the `hw2` folder to your submission repository. Change directories to enter your submission repository. Your code will go into each of the `.sql` files. You can add all of them to the repository using `git add`:
$ git add *.sql
$ git commit -m'initialized homework'

The file `admissions.db` stores all of the data in the database. To query the database you must run the `sqlite3` program:
$ sqlite3 admissions.db
SQLite version 3.14.0 2016-07-26 15:17:14
Enter ".help" for usage hints.
This will return a prompt in which you can type in your SQL queries. For example, 
sqlite> select * from colleges where id=0;
0|948|A.T. Still University|Kirksville
sqlite> select * from students where id=0;
0|John Muzquiz|436|9520|Mathematics|3.72663450103766
sqlite> select * from hometown where id=0;

## SQL Assignment
For each of the assigned SQL queries you must write the entire query in its respective file. The query consists of *everything you type in*. For example, above this is `select * from hometown where id=0;`.

### 0.sql
Calculates the average the gpa for each major over all students. The first column of the output must have the major, the second column must have the average of all students with that major titled `agpa`. Testing Hint: As a sanity check for your query there are 98 majors.

### 1.sql
Find the bottom 10 majors in terms of average gpa (the majors from 0.sql that have the 10 lowest GPAs). Then, calculate the number of students with one of these majors. Your result should be a single column titled 'cnt' and single row with the total count. Testing Hint: There are 30000 student records evenly distributed among 98 majors.

### 2.sql
For each major, calculate the fraction of students who have a 4.0. Your result should have the first column as the `major` and the second column called `frac` with the fraction of students. Testing Hint: No major has more than 0.55.

### 3.sql
Find the college that contributed the most number of students. Your result should have the first column as the college's `name` and the second column called `cnt` with the count of students who come from there. Testing Hint: The highest count is less than 100.

### 4a.sql
Krishnan Sanjay committed
Write a query to find all the distinct cities (rows with different id's) in the hometown table that have the same name. Hint: there may not be any but write the query any ways.
Sanjay Krishnan committed
78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95

### 4b.sql
Based on your answer to 4a, how might you match the `college` and `hometown` tables? Write a query to calculate the number of colleges per-capita (total divided by the population) in each state. Keep in mind that we are interested in the total number of colleges in the state divided by the total population of the state. Your result should have two columns the first being the state name `state` and the second being the `colpc` column giving the per capita fraction. Testing Hint: All of the numbers are low.

### 5.sql
Find the number of unique cities in the hometown table that don't have any students in the database. Your result should be a single column 'cnt' and a single row with the number.

### 6.sql
For each city find the student with the highest GPA (including ties). Return the names of those students who are the best in their respective cities only if there are at least 8 students from the city. Your result should have a single column `name` with the students' names.

### 7.sql
Calculate the probability (as a fraction) that two randomly picked students will be from the same city and same county. Your result should have two columns (`numerator`) and (`denominator`) representing this fraction and a single row. You may not hard code any sizes.

## Submission
After you finish the assignment you can submit your code with:
$ git push