SQL - Part 1

part_one.md

SQL - Part 1

These four modules are directly sourced from Georgia Institute of Technology and modified only for Coder Academy. These are not to be commercially used or shared. Modified by Aaron J Mendonsa.

More Spring Cleaning

For Macs:
Download PostgresApp
Download Postico

For Ubuntu/WSL: Download pgAdmin4

History

SEQUEL: Structured English Query Language (IBM Research) - 1973 SQL: Structured Query Language

Version History:

• SQL/86, SQL/89
• SQL/92 or SQL2
• SQL/99 or SQL3
• Further revisions in 2003, 2006, 2008, 2011...

SQL is supported by IBM, Oracle, SQLServer, MySQL, etc...

---

Insert

INSERT INTO Students (Email, Age, Sex)
VALUES ('coder@coderacademy.com', 20, 'F');

Students

Email	Age	Sex
user1@coderacademy.com	20	F
user2@coderacademy.com	30	M
user3@coderacademy.com	40	F

After Insert:

Students

Email	Age	Sex
user1@coderacademy.com	20	F
user2@coderacademy.com	30	M
user3@coderacademy.com	40	F
coder@coderacademy.com	20	F

Insert only inserts one row at a time

---

Delete

DELETE FROM Students
WHERE Age = 20

Students

Email	Age	Sex
user1@coderacademy.com	20	F
user2@coderacademy.com	30	M
user3@coderacademy.com	40	F
coder@coderacademy.com	20	F

After Delete

Students

Email	Age	Sex
user2@coderacademy.com	30	M
user3@coderacademy.com	40	F

Delete can delete a set of rows

---

Update

UPDATE Students
SET Age = 21
WHERE Email = 'user1@coderacademy.com'
  AND Age = 20

Students

Email	Age	Sex
user1@coderacademy.com	20	F
user2@coderacademy.com	30	M
user3@coderacademy.com	40	F
coder@coderacademy.com	20	F

After Update
Students

Email	Age	Sex
user1@coderacademy.com	21	F
user2@coderacademy.com	30	M
user3@coderacademy.com	40	F
coder@coderacademy.com	20	F

Update can update a set of rows

---

Live Question

What will happen when we perform an INSERT operation on the Students table?

INSERT INTO Students (Email, Age, Sex)
VALUES ('coder@coderacademy.com', 25, 'M');

Students

Email	Age	Sex
user1@coderacademy.com	21	F
user2@coderacademy.com	30	M
user3@coderacademy.com	40	F
coder@coderacademy.com	20	F

a) The table will have 5 rows
b) The Age and Sex of Student will be updated
c) The INSERT operation is rejected due to a primary key violation

---

Fundamental SQL Query Syntax

SELECT column_1, column_2, column_3,...,column_n
FROM table_1, table_2,..., table_3
WHERE condition;

Column is a name of a column, like Age, or Email
Table is the name of the table, like Students
Condition may compare values of columns to constants or to each other, eg.

• Age > 20
• Sex = 'M' Can be used with logical operators AND, OR, NOT'

---

Selection (Wildcard)

SELECT Email, Age, Sex
FROM Students;

Students

Email	Age	Sex
user1@coderacademy.com	21	F
user2@coderacademy.com	30	M
user3@coderacademy.com	40	F
coder@coderacademy.com	20	F

After Selection

Students

Email	Age	Sex
user1@coderacademy.com	21	F
user2@coderacademy.com	30	M
user3@coderacademy.com	40	F
coder@coderacademy.com	20	F

Alternatively:

SELECT *
FROM Students;

Students

Email	Age	Sex
user1@coderacademy.com	21	F
user2@coderacademy.com	30	M
user3@coderacademy.com	40	F
coder@coderacademy.com	20	F

After Selection

Students

Email	Age	Sex
user1@coderacademy.com	21	F
user2@coderacademy.com	30	M
user3@coderacademy.com	40	F
coder@coderacademy.com	20	F

---

Selection (with WHERE)

Find all Students who are Female.

SELECT *
FROM Students
WHERE Sex = 'F';

Students

Email	Age	Sex
user1@coderacademy.com	21	F
user2@coderacademy.com	30	M
user3@coderacademy.com	40	F
coder@coderacademy.com	20	F

After Selection

Students

Email	Age	Sex
user1@coderacademy.com	21	F
user3@coderacademy.com	40	F
cåoder@coderacademy.com	20	F

---

Selection (with composite WHERE)

Find all Students who have the same CurrentCity and HomeTown or whose hometown is in Melbourne.

SELECT *
FROM Students
WHERE CurrentCity = HomeTown OR 
  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 Selection

Students

Email	Age	Sex	CurrentCity	HomeTown
user1@coderacademy.com	21	F	Sydney	Melbourne
coder@coderacademy.com	20	F	Perth	Perth