SQL - Part 3

part_three.md

SQL - Part 3

Sorting

Find Email, Sex, CurrentCity from Students who are male. Sort the data by ascending CurrentCity

SELECT Email, Sex, CurrentCity
FROM Student
WHERE Sex = 'M'
ORDER BY CurrentCity ASC;

Student

Email	Age	Sex	CurrentCity
user1@coderacademy.com	21	F	San Francisco
user2@coderacademy.com	30	M	Houston
user3@coderacademy.com	40	M	San Diego
coder@coderacademy.com	20	F	Washington D.C.

After Sorting

Email	Sex	CurrentCity
user2@coderacademy.com	M	Houston
user3@coderacademy.com	M	San Diego

You can sort by ASC or DESC

---

Set Operations - UNION

Find all HomeTown(s) and CurrentCity(s) without duplicates from Students

SELECT HomeTown
FROM Student
UNION
SELECT CurrentCity
FROM Student;

Student

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

After Union

Result
Melbourne
Canberra
Perth
Sydney

As UNION is a set operation, there are no duplicates

If you do want duplicates, then use UNION ALL

SELECT HomeTown
FROM Student
UNION ALL
SELECT CurrentCity
FROM Student;

Result
Melbourne
Melbourne
Melbourne
Perth
Melbourne
Canberra
Sydney
Perth

---

Set Operations - INTERSECTION

Find all cities from Students that are HomeTown(s) for someone and CurrentCity(s) for someone. No duplicates.

SELECT CurrentCity
FROM Student
INTERSECT
SELECT HomeTown
FROM Student;

Student

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

After Intersection

Result
Melbourne
Perth

As INTERSECT is a set operation, there are no duplicates

If you do want duplicates, use INTERSECT ALL

---

Set Operations - EXCEPT

Find all cities that are CurrentCity(s) but exclude those that are HomeTown(s). No duplicates.

SELECT CurrentCity
FROM Student
EXCEPT
SELECT HomeTown
FROM Student;

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

After EXCEPT

Result
Sydney

As EXCEPT is a set operation, there are no duplicates

If you do want duplicates, use EXCEPT ALL

---

Functions - count, min, max, avg, sum

Count the number of Student(s)

SELECT count(*)
FROM Student;

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

AFTER count

Result
4

---

Find the Email, and the Age for the oldest student

SELECT Email, max(Age)
FROM Student;

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

After max

Email	Age
user3@coderacademy.com	40

---

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.

SELECT Email, count(*) AS NumClasses, avg(Grade) AS AvgGrade
FROM Student
GROUP BY Email
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
user1@coderacademy.com	2	75
user2@coderacademy.com	2	90
coder@coderacademy.com	3	93.33

---