Structure Query Language (SQL)

Southwest Coder Dojo / September 28, 2019

SQL Tool

SQL Fiddle

Questions

• How do we look at the data?
• What is the address of each person?
• Does anyone not have an address?
• Does anyone have more than one address?

Class Resources

• Create Table Statement
• Insert Into Statement
• Select Statement
• Where Clause
• Inner Join

Class Script

-- begin script --
CREATE TABLE Person (
    ID int,
    FIRST_NAME varchar(255),
    LAST_NAME varchar(255)
);

CREATE TABLE Address (
    ID int,
    PERSONID int,
    STREET varchar(255),
    CITY varchar(255)
 );

INSERT INTO Person (ID, FIRST_NAME, LAST_NAME) 
VALUES ('1', 'John', 'Snow');
INSERT INTO Person (ID, FIRST_NAME, LAST_NAME) 
VALUES ('2', 'Hank', 'Aaron');
INSERT INTO Person (ID, FIRST_NAME, LAST_NAME) 
VALUES ('3', 'Bill', 'Keaton');
INSERT INTO Person (ID, FIRST_NAME, LAST_NAME) 
VALUES ('4', 'Freddie', 'Mercury');
INSERT INTO Person (ID, FIRST_NAME, LAST_NAME) 
VALUES ('5', 'Steve', 'Jobs');
INSERT INTO Person (ID, FIRST_NAME, LAST_NAME) 
VALUES ('6', 'Johnny', 'Depp');
INSERT INTO Person (ID, FIRST_NAME, LAST_NAME)
VALUES ('7', 'Johnny', 'Carson');
INSERT INTO Person (ID, FIRST_NAME, LAST_NAME)
VALUES ('8', 'Steve', 'Carleton');

INSERT INTO Address (ID, PERSONID, STREET, CITY) 
VALUES('1', '1', '2019 Winterfall Lane', 'Westeros');
INSERT INTO Address (ID, PERSONID, STREET, CITY) 
VALUES('2', '4', 'Garden Lodge', 'Kensington');
INSERT INTO Address (ID, PERSONID, STREET, CITY) 
VALUES('3', '4', 'The Opera', 'London');
INSERT INTO Address (ID, PERSONID, STREET, CITY) 
VALUES('4', '5', '0 Infinite Loop', 'Palo Alto');
INSERT INTO Address (ID, PERSONID, STREET, CITY) 
VALUES('5', '6', 'Dead Eye Gulch', 'Pacific Ocean');
-- end script --