SQL - Part 2

part_two.md

SQL - Part 2

Projection

Find Email, and Age for Students in Melbourne

SELECT Email, Age
FROM Students
WHERE HomeTown = 'Melbourne';

Students

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

After Projection

Students

Email	Age
user1@coderacademy.com	21

---

Distinct

Tables may have duplicate rows!!

Find the Sex for Students whose CurrentCity is Melbourne

SELECT Sex
FROM Students
WHERE CurrentCity = 'Melbourne';

Students

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 Selection

Students

Age
F
M
F

We can see from above, we see Female shows up twice. If we want to avoid duplicates, we need to use the DISTINCT keyword around the attribute you want returned.

SELECT DISTINCT(Sex)
FROM Students
WHERE CurrentCity = 'Melbourne';

Students

Age
F
M

---

Natural Inner Join

Find Email, Age, and Major for Students who have a major combining Student data with EmailMajor data

SELECT Students.Email, Age, Major
FROM Students, EmailMajor
WHERE Students.Email = EmailMajor.Email;

Students

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

EmailMajor

Email	Major
user1@coderacademy.com	Science
user2@coderacademy.com	Arts
user3@coderacademy.com	Floral Arrangements
user4@coderacademy.com	Math
coder@coderacademy.com	Medicine

After Inner Join

Results

Email	Age	Major
user1@coderacademy.com	21	Science
user2@coderacademy.com	30	Arts
user3@coderacademy.com	40	Floral Arrangements
coder@coderacademy.com	20	Medicine

---

Live Question

How many rows will result from the following SQL query:

SELECT Age
FROM Students
Where Sex = 'F';

Students

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

a) One
b) Two
c) Three
d) Four

---

Natural Inner Join (with aliases)

SELECT S.Email, S.Age, M.Major
FROM Students AS S, EmailMajor as M
WHERE S.Email = M.Email

Students

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

EmailMajor

Email	Major
user1@coderacademy.com	Science
user2@coderacademy.com	Arts
user3@coderacademy.com	Floral Arrangements
user4@coderacademy.com	Math
coder@coderacademy.com	Medicine

After Inner Join

Results

Email	Age	Major
user1@coderacademy.com	21	Science
user2@coderacademy.com	30	Arts
user3@coderacademy.com	40	Floral Arrangements
coder@coderacademy.com	20	Medicine

---

Left Outer Join

Find Email, Age, and Major for Students who have a major by combining Student data with EmailMajor data even when the Student has no major

SELECT Students.Email, Age, Major
FROM Students LEFT OUTER JOIN EmailMajor;

Students

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

EmailMajor

Email	Major
user1@coderacademy.com	Science
coder@coderacademy.com	Medicine

After Left Outer Join Results

Email	Age	Major
user1@coderacademy.com	21	Science
user2@coderacademy.com	30	NULL
user3@coderacademy.com	40	NULL
coder@coderacademy.com	20	Medicine

---

String Matching

Find Email, Age, and CurrentCity from Students who live in a CurrentCity that starts with 'San'

SELECT Email, Age, CurrentCity
FROM Students
WHERE CurrentCity LIKE 'San%'

Students

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

Results

Email	Age	Sex	CurrentCity
user1@coderacademy.com	21	F	San Francisco
user3@coderacademy.com	40	F	San Diego

The % matches any string, including the empty string

SELECT Email, Age, CurrentCity
FROM Students
WHERE CurrentCity LIKE 'H _ _ _ _ _ _'

Email	Age	Sex	CurrentCity
user2@coderacademy.com	30	M	Houston

The _ matches any single character