Intro
SQL is a complex and tricky beast to master this lab is meant to me a simple introduction to the most
basic SQL commands. All instructions can be carried out using this website:
IMPORTANT! Use Chrome, Safari, or Opera to access this website. They have features needed by
this site to work properly.
Review the following sections from https://www.w3schools.com/sql/default.asp:
SQL Intro
SQL Syntax
SQL Select
SQL Where
SQL Order By
SQL Insert into
SQL Update
SQL Delete
SQL Join
Part A setting up tables (14 marks)
For each step provide the SQL query you used and the results obtained
A) Create a table called “Buddies”
Include the following fields:
BID: INT
Fname: VARCHAR(255)
Lname: VARCHAR(255)
FavSportID: INT
B) Create a table called “sports”
Include the following fields:
SportID: INT
sport: VARCHAR(255)
C) Add the following to the sports table:
1,Hockey
2,Ping Pong
3,Football
4,Lacrosse
5,Tennis
6, Couch Surfing
D) Add the following to your buddies list
BID,Fname,Lname,FavSportID
1,Bobby,Butler,3
2,Martha,Bailey,2
3,Edward,Bowman,2
4,Larry,Russell,4
5,Howard,Andrews,1
6,Lori,Crawford,4
7,Sara,Long,1
8,Ronald,Perez,5
9,Martha,Brooks,3
10,Stephen,Foster,4
E) change Howard Andrews to your name and favourite sport. Make sure the sport is not hockey.
F) Add Kenneth Owen to the Buddies list and make couch surfing my favourite sport.
G) Delete Stephen from the buddies list
Part B Extracting Data (6 Marks)
A) List all the buddies in alphabetical order by last name
B) List all the buddies in reverse alphabetical order by last name
C) List all the buddies in alphabetical order by last name and include their favourite sports
D) List everyone who enjoys the same sport that you do.
E) List everyone with the first name “Martha”
F) List everyone with a last name that begins with a B
Part C Understanding Data (10 Marks)
Explain what an inner and outer join do and how they are used
Explain what Primary keys and Foreign keys are and how they were used in this lab.
Explain what a data type is and provide four examples.
Draw a diagram explaining the tables used in parts A and B. include connections to primary and foreign
keys.