"*** select * from Student ***"
sid sname gpa sizehs
123 Amy 3.9 1000
234 Bob 3.6 1500
320 Lori   2500
345 Craig 3.5 500
432 Kevin   1500
456 Doris 3.9 1000
543 Craig 3.4 2000
567 Edward 2.9 2000
654 Amy 3.9 1000
678 Fay 3.8 200
765 Jay 2.9 1500
789 Gary 3.4 800
876 Irene 3.9 400
987 Helen 3.7 800

(14 rows)

"*** select * from Apply ***"
sid cname major decision
123 Berkeley CS t
123 Cornell EE t
123 Stanford CS t
123 Stanford EE f
234 Berkeley biology f
321 MIT history f
321 MIT psychology t
345 Cornell bioengineering f
345 Cornell CS t
345 Cornell EE f
345 MIT bioengineering t
543 MIT CS f
678 Stanford history t
765 Cornell history f
765 Cornell psychology t
765 Stanford history t
876 MIT biology t
876 MIT marine biology f
876 Stanford CS f
987 Berkeley CS t
987 Stanford CS t

(21 rows)

"*** select * from College ***"
cname state enrollment
Berkeley CA 36000
Cornell NY 21000
Irene TX 25000
MIT MA 10000
Stanford CA 15000

(5 rows)

"*** stats on GPA of students ***" "*** Choose everything from Student ****"
sid sname gpa sizehs
123 Amy 3.9 1000
234 Bob 3.6 1500
320 Lori   2500
345 Craig 3.5 500
432 Kevin   1500
456 Doris 3.9 1000
543 Craig 3.4 2000
567 Edward 2.9 2000
654 Amy 3.9 1000
678 Fay 3.8 200
765 Jay 2.9 1500
789 Gary 3.4 800
876 Irene 3.9 400
987 Helen 3.7 800

(14 rows)

"*** count the number of rows in Student ****"
count
14

(1 row)

"*** Choose only GPAs from Student ****"
gpa
3.9
3.6
 
3.5
 
3.9
3.4
2.9
3.9
3.8
2.9
3.4
3.9
3.7

(14 rows)

"*** Compute the average of GPAs from Student ****"
avg
3.56666666666667

(1 row)

"*** Find the maximum GPA from Student ****"
max
3.9

(1 row)

"*** Find the minimum GPA from Student ****"
min
2.9

(1 row)

"*** Compute the sum of GPAs from Student ****"
sum
42.8

(1 row)

"*** stats on GPA of students applying to CS ***"
sid sname gpa sizehs
123 Amy 3.9 1000
876 Irene 3.9 400
987 Helen 3.7 800
345 Craig 3.5 500
543 Craig 3.4 2000

(5 rows)

count
5

(1 row)

gpa
3.9
3.9
3.7
3.5
3.4

(5 rows)

avg
3.68

(1 row)

max
3.9

(1 row)

min
3.4

(1 row)

sum
18.4

(1 row)

"*** number of colleges with enrollment > 15000 ***"
cname state enrollment
Berkeley CA 36000
Irene TX 25000
Cornell NY 21000
Stanford CA 15000
MIT MA 10000

(5 rows)

cname state enrollment
Berkeley CA 36000
Irene TX 25000
Cornell NY 21000

(3 rows)

count
3

(1 row)

"*** number of students who applied to Cornell ***" "this is not right, because of duplicates"
sid cname major decision
123 Cornell EE t
345 Cornell bioengineering f
345 Cornell CS t
345 Cornell EE f
765 Cornell history f
765 Cornell psychology t

(6 rows)

count
6

(1 row)

"this is right"
sid
123
345
765

(3 rows)

count
3

(1 row)

"*** students, such that *** " "*** the number of other students with the same GPA equals ***" "*** the number of other students with the same high school size ***"
sid sname gpa sizehs
320 Lori   2500
345 Craig 3.5 500
543 Craig 3.4 2000
567 Edward 2.9 2000
678 Fay 3.8 200
789 Gary 3.4 800

(6 rows)

"*** amount by which the average GPA of students applying to CS ***" "*** exceeds the average GPA of students not applying to CS ***"
gpa gpa
3.68 3.48571428571429

(1 row)

diff
0.194285714285715

(1 row)

"*** number of applications to each college ***" "*** order by cName ***";
sid cname major decision
987 Berkeley CS t
234 Berkeley biology f
123 Berkeley CS t
123 Cornell EE t
345 Cornell bioengineering f
345 Cornell EE f
345 Cornell CS t
765 Cornell psychology t
765 Cornell history f
345 MIT bioengineering t
321 MIT history f
321 MIT psychology t
543 MIT CS f
876 MIT biology t
876 MIT marine biology f
765 Stanford history t
123 Stanford CS t
987 Stanford CS t
876 Stanford CS f
678 Stanford history t
123 Stanford EE f

(21 rows)

"*** cName, count(*) grouped by cName ***"
cname count
Cornell 6
MIT 6
Berkeley 3
Stanford 6

(4 rows)

"*** number of students who applied to each college ***"
sid cname major decision
987 Berkeley CS t
234 Berkeley biology f
123 Berkeley CS t
123 Cornell EE t
345 Cornell bioengineering f
345 Cornell EE f
345 Cornell CS t
765 Cornell psychology t
765 Cornell history f
345 MIT bioengineering t
321 MIT history f
321 MIT psychology t
543 MIT CS f
876 MIT biology t
876 MIT marine biology f
765 Stanford history t
123 Stanford CS t
987 Stanford CS t
876 Stanford CS f
678 Stanford history t
123 Stanford EE f

(21 rows)

cname count
Cornell 6
MIT 6
Berkeley 3
Stanford 6

(4 rows)

cname count
Berkeley 3
Cornell 3
MIT 4
Stanford 5

(4 rows)

"*** stats on college enrollment by state ***"
cname state enrollment
Berkeley CA 36000
Stanford CA 15000
MIT MA 10000
Cornell NY 21000
Irene TX 25000

(5 rows)

state avg
NY 21000.000000000000
MA 10000.0000000000000000
CA 25500.000000000000
TX 25000.000000000000

(4 rows)

state max
NY 21000
MA 10000
CA 36000
TX 25000

(4 rows)

state min
NY 21000
MA 10000
CA 15000
TX 25000

(4 rows)

state sum
NY 21000
MA 10000
CA 51000
TX 25000

(4 rows)

"*** stats on GPA of applicants to each college and major ***"
sid sname gpa sizehs cname major decision
234 Bob 3.6 1500 Berkeley biology f
987 Helen 3.7 800 Berkeley CS t
123 Amy 3.9 1000 Berkeley CS t
345 Craig 3.5 500 Cornell bioengineering f
345 Craig 3.5 500 Cornell CS t
345 Craig 3.5 500 Cornell EE f
123 Amy 3.9 1000 Cornell EE t
765 Jay 2.9 1500 Cornell history f
765 Jay 2.9 1500 Cornell psychology t
345 Craig 3.5 500 MIT bioengineering t
876 Irene 3.9 400 MIT biology t
543 Craig 3.4 2000 MIT CS f
876 Irene 3.9 400 MIT marine biology f
876 Irene 3.9 400 Stanford CS f
123 Amy 3.9 1000 Stanford CS t
987 Helen 3.7 800 Stanford CS t
123 Amy 3.9 1000 Stanford EE f
765 Jay 2.9 1500 Stanford history t
678 Fay 3.8 200 Stanford history t

(19 rows)

cname major avg max min ?column?
Berkeley biology 3.6 3.6 3.6 0
Berkeley CS 3.8 3.9 3.7 0.2
Cornell bioengineering 3.5 3.5 3.5 0
Cornell CS 3.5 3.5 3.5 0
Cornell EE 3.7 3.9 3.5 0.4
Cornell history 2.9 2.9 2.9 0
Cornell psychology 2.9 2.9 2.9 0
MIT bioengineering 3.5 3.5 3.5 0
MIT biology 3.9 3.9 3.9 0
MIT CS 3.4 3.4 3.4 0
MIT marine biology 3.9 3.9 3.9 0
Stanford CS 3.83333333333333 3.9 3.7 0.2
Stanford EE 3.9 3.9 3.9 0
Stanford history 3.35 3.8 2.9 0.9

(14 rows)

"*** max spread between min and max GPA of applicants ***" "*** to each college and major ***";
max
0.9

(1 row)

cname major avg max min diff
Stanford history 3.35 3.8 2.9 0.9

(1 row)

"*** number of colleges applied to by each student ***" "does not include student who did not apply anywhere"
sid sname gpa sizehs cname major decision
123 Amy 3.9 1000 Berkeley CS t
123 Amy 3.9 1000 Cornell EE t
123 Amy 3.9 1000 Stanford CS t
123 Amy 3.9 1000 Stanford EE f
234 Bob 3.6 1500 Berkeley biology f
345 Craig 3.5 500 Cornell bioengineering f
345 Craig 3.5 500 Cornell CS t
345 Craig 3.5 500 Cornell EE f
345 Craig 3.5 500 MIT bioengineering t
543 Craig 3.4 2000 MIT CS f
678 Fay 3.8 200 Stanford history t
765 Jay 2.9 1500 Cornell history f
765 Jay 2.9 1500 Cornell psychology t
765 Jay 2.9 1500 Stanford history t
876 Irene 3.9 400 MIT biology t
876 Irene 3.9 400 MIT marine biology f
876 Irene 3.9 400 Stanford CS f
987 Helen 3.7 800 Berkeley CS t
987 Helen 3.7 800 Stanford CS t

(19 rows)

sid sname count_cname
123 Amy 4
345 Craig 4
876 Irene 3
765 Jay 3
987 Helen 2
543 Craig 1
234 Bob 1
678 Fay 1

(8 rows)

sid sname count_cname
123 Amy 3
345 Craig 2
765 Jay 2
876 Irene 2
987 Helen 2
678 Fay 1
543 Craig 1
234 Bob 1

(8 rows)

"does include student who did not apply anywhere";
sid sname count_cname
123 Amy 3
876 Irene 2
987 Helen 2
345 Craig 2
765 Jay 2
678 Fay 1
543 Craig 1
234 Bob 1
789 Gary 0
320 Lori 0
456 Doris 0
432 Kevin 0
654 Amy 0
567 Edward 0

(14 rows)

"*** colleges with fewer than 5 applications ***"
cname count
Cornell 6
MIT 6
Berkeley 3
Stanford 6

(4 rows)

cname count
Berkeley 3

(1 row)

"*** colleges with fewer than 5 applicants ***"
cname count
Berkeley 3
Cornell 3
MIT 4
Stanford 5

(4 rows)

cname count
Berkeley 3
Cornell 3
MIT 4

(3 rows)

"*** majors whose applicant's max GPA is less than the average ***"
avg
3.56666666666667

(1 row)

major max
psychology 2.9
EE 3.9
bioengineering 3.5
marine biology 3.9
CS 3.9
history 3.8
biology 3.9

(7 rows)

major
psychology
bioengineering

(2 rows)