SQL - Part 4

part_four.md

SQL - Part 4 (Advanced)

HAVING - Conditions on Group By

Group Student by Email.
For each group, return the Email, the number of classes, and the average Grade.
Sort the groups by ascending number of classes.

BUT, return only the groups with more than 2 classes.

SELECT Email, count(*) AS NumClasses, avg(Grade) AS AvgGrade
FROM Student
GROUP BY Email
HAVING count(*) > 2
ORDER BY NumClasses ASC;

Student

Email	Age	Sex	Classes	Grade
user1@coderacademy.com	21	F	Math	80
user2@coderacademy.com	30	M	Arts	90
coder@coderacademy.com	20	F	Science	90
user1@coderacademy.com	21	F	Arts	70
user2@coderacademy.com	30	M	Math	90
coder@coderacademy.com	20	F	Programming	90
coder@coderacademy.com	20	F	Shower Singing	100

After Group By

Email	NumClasses	AvgGrade
coder@coderacademy.com	3	93.33

---

Nested Queries - IN/NOT IN

Find Email and Class for Student(s) whose CurrentCity is Melbourne

SELECT Email, Class
FROM Student_Class
WHERE Email IN 
  (SELECT Email
  FROM Student
  WHERE CurrentCity = 'Melbourne'
  );

Student

Email	Age	Sex	CurrentCity	HomeTown
user1@coderacademy.com	21	F	Melbourne	Melbourne
user2@coderacademy.com	30	M	Sydney	Canberra
user3@coderacademy.com	40	F	Melbourne	Adelaide
coder@coderacademy.com	20	F	Perth	Perth

Student_Class

Email	Classes	Grade
user1@coderacademy.com	Math	80
user2@coderacademy.com	Arts	90
coder@coderacademy.com	Science	90
user1@coderacademy.com	Arts	70
user3@coderacademy.com	Math	90
coder@coderacademy.com	Programming	90
coder@coderacademy.com	Shower Singing	100

After IN operation

Email	Class
user1@coderacademy.com	Math
user1@coderacademy.com	Arts
user3@coderacademy.com	Math

Alternatively, we could write the above query as:

SELECT S.Email, C.Class
FROM Student AS S, Student_Class AS C
WHERE S.Email = C.Email 
AND S.CurrentCity = 'Melbourne';

---

Nested Queries - SOME/ALL

Find CurrentCity with at least one Student with a Grade that's higher than all the Grades of Student(s) with HomeTown Melbourne.

SELECT CurrentCity
FROM Student AS S, Student_Grade AS G
WHERE S.Email = G.Email AND Grade > ALL
  (SELECT Grade
  FROM Student AS S, Student_Grade AS G
  WHERE S.Email = G.Email AND S.HomeTown = 'Melbourne');

Student

Email	Age	Sex	CurrentCity	HomeTown
user1@coderacademy.com	21	F	Melbourne	Melbourne
user2@coderacademy.com	30	M	Sydney	Canberra
user3@coderacademy.com	40	F	Melbourne	Adelaide
coder@coderacademy.com	20	F	Perth	Perth

Student_Grade

Email	Grade
user1@coderacademy.com	90
user2@coderacademy.com	92
user3@coderacademy.com	90
coder@coderacademy.com	90

After Nested Query

Result

CurrentCity
Sydney

---

Nested Queries - Correlated

Find Email and Age of Student(s) who have no Grades.

SELECT S.Email, Age
FROM Student AS S
WHERE NOT EXIST
  (SELECT *
  FROM Student_Grade AS G
  WHERE G.Email = S.Email);

Student

Email	Age	Sex	CurrentCity	HomeTown
user1@coderacademy.com	21	F	Melbourne	Melbourne
user2@coderacademy.com	30	M	Sydney	Canberra
user3@coderacademy.com	40	F	Melbourne	Adelaide
user4@coderacademy.com	23	M	Brisbane	Brisbane
coder@coderacademy.com	20	F	Perth	Perth

Student_Grade

Email	Grade
user1@coderacademy.com	90
user2@coderacademy.com	92
user3@coderacademy.com	90
coder@coderacademy.com	90

After Nested Query Result

Email	Age
user4@coderacademy.com	23

---