This week we learned SQL by using SQL Fiddle to create a simple database (of two tables) and then writing simple queries against the data. If you want to try the exercises, look at the questions below along with the links to SQL documentation to see if you can figure out how to answer the questions below.
SQL Tool
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
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 --
