1) Write a query to find the lowest and highest GPA of students applying to CS

2) Write a query to find The spread of GPAs #arithmetics in SELECT

Hint: max(GPA)-min(GPA)

3) Write a query to find the minimum and maximum GPAs of applicants to each college and major

- Check to see what happens when you add a non-aggregation attribute, e.g., sName

- Check to see if cName or major or both can be omitted

4) Write a query to find the total college enrollments by state

5) Write a query to find the average GPA of students who applied to CS

Hint: use a subquery in the from clause

6) Write a query to find the amount by which the average GPA of students applying to CS exceeds the average of students not applying to CS

Hint: A student may apply to different schools, so calculating the average GPA after joining apply and student tables will return incorrect results.

7) Write a query to find the number of rows in the Student table

8) Write a query to find the number of distinct students applying to Cornell

Hint: Remember that you are looking for distinct students!

9a) Write a query to return each student's sid and the number of colleges that the student has applied to.

Hint: Remember that you are looking for distinct colleges!

9b) Write a query to find the name, sid of each student, and the number of colleges that he/she applied to.

10) Write a query to find the Colleges with fewer than 5 applications

11) Write the SQL script in part A, in your SQL environment and run.

compare the output of B and C (take a screenshot of each result and highlight the differences and specify what caused these differences)?

12) Write the SQL script in part D, in your SQL environment and run.

compare the output of B and C again? What is changed? Why?

Use college_application;

A)

 SELECT * FROM Student;

 Insert into Student values (432, 'Kevin', null, 1500);  Insert into Student values (321, 'Lori', null, 2500);

 SELECT * FROM Student;

B)

 SELECT count(*)  FROM Student;

C)

 SELECT count(GPA)  FROM Student;

D)

 SET SQL_SAFE_UPDATES = 0;  DELETE FROM Student WHERE GPA is null;

Note: MySQL will refuse to run the UPDATE or DELETE query if executed without the WHERE clause or LIMIT clause. MySQL will also refuse the query which has a WHERE clause but there is no condition with the KEY column, so here we use SET SQL_SAFE_UPDATES = 0; before the delete statement.

CollegeData.sql

script:

DROP DATABASE IF EXISTS college_application; CREATE DATABASE college_application; USE college_application;

drop table if exists Student; drop table if exists College; drop table if exists Apply;

/* Create the schema for our tables */

create table College(cName text, state text, enrollment int); create table Student(sID int, sName text, GPA real, sizeHS int); create table Apply(sID int, cName text, major text, decision text);

insert into Student values (123, 'Amy', 3.9, 1000); insert into Student values (234, 'Bob', 3.6, 1500); insert into Student values (345, 'Craig', 3.5, 500); insert into Student values (456, 'Doris', 3.9, 1000); insert into Student values (567, 'Edward', 2.9, 2000); insert into Student values (678, 'Fay', 3.8, 200); insert into Student values (789, 'Gary', 3.4, 800); insert into Student values (987, 'Helen', 3.7, 800); insert into Student values (876, 'Irene', 3.9, 400); insert into Student values (765, 'Jay', 2.9, 1500); insert into Student values (654, 'Amy', 3.9, 1000); insert into Student values (543, 'Craig', 3.4, 2000);

insert into College values ('Stanford', 'CA', 15000); insert into College values ('Berkeley', 'CA', 36000); insert into College values ('MIT', 'MA', 10000); insert into College values ('Cornell', 'NY', 21000);

insert into Apply values (123, 'Stanford', 'CS', 'Y'); insert into Apply values (123, 'Stanford', 'EE', 'N'); insert into Apply values (123, 'Berkeley', 'CS', 'Y'); insert into Apply values (123, 'Cornell', 'EE', 'Y'); insert into Apply values (234, 'Berkeley', 'biology', 'N'); insert into Apply values (345, 'MIT', 'bioengineering', 'Y'); insert into Apply values (345, 'Cornell', 'bioengineering', 'N'); insert into Apply values (345, 'Cornell', 'CS', 'Y'); insert into Apply values (345, 'Cornell', 'EE', 'N'); insert into Apply values (678, 'Stanford', 'history', 'Y'); insert into Apply values (987, 'Stanford', 'CS', 'Y'); insert into Apply values (987, 'Berkeley', 'CS', 'Y'); insert into Apply values (876, 'Stanford', 'CS', 'N'); insert into Apply values (876, 'MIT', 'biology', 'Y'); insert into Apply values (876, 'MIT', 'marine biology', 'N'); insert into Apply values (765, 'Stanford', 'history', 'Y'); insert into Apply values (765, 'Cornell', 'history', 'N'); insert into Apply values (765, 'Cornell', 'psychology', 'Y'); insert into Apply values (543, 'MIT', 'CS', 'N');