Go to SQLLITEONLINE - https://sqliteonline.com/
Click on MSSQL on the left hand side of the page.
REMEMBER: DO NOT RUN ALL THE CODE AT ONCE - highlight (select) the code you want to run each time.
Run the code to make the two tables and fill them with data (see “FULL SCRIPT - multi table” section).
What code could you run to test it?
What code could you run to reset the two tables?
select car_sales.*, car_dealership.*
from car_sales, car_dealership
where car_sales.dealership_postcode = car_dealership.dealership_postcode
order by car_sales.make asc
What does this code do?
This code selects all fields from the sales and dealership tables, and orders the output by make, ascending
Select
car_sales.registration,
car_sales.make,
car_sales.model,
car_dealership.dealership_city,
car_dealership.dealership_postcode
from car_sales, car_dealership
where car_sales.dealership_postcode = car_dealership.dealership_postcode
and car_dealership.dealership_postcode = 'L4 0TH'
What does this code do?
This code selects REGISTRATION, MAKE MODEL CITY & POSTCODE from car_sales & car_dealership, where the dealership_postcode is equal to L4 0TH
Now, work through the following challenges. You should be working through them on SQLLiteOnline.com, and then self validating your work with the answers that show when you click on the down arrow that is next to the question.....good luck!
NOTE: if there are any questions that you are not sure of, make sure you make a note & a) raise it with your teacher in class or b)raise it in the feedback section of this website.
CHALLENGE 1
select
car_sales.registration,
car_sales.make,
car_sales.model,
car_dealership.dealership_city,
car_dealership.dealership_postcode
from car_sales, car_dealership
where car_sales.dealership_postcode = car_dealership.dealership_postcode
What does this code do?
Selects REGISTRATION, MAKE MODEL CITY & POSTCODE from car_sales & car_dealership.
select
car_sales.make,
car_sales.model,
car_dealership.dealership_city
from car_sales, car_dealership
where car_sales.dealership_postcode = car_dealership.dealership_postcode
Selects REG, MAKE, MODEL, CITY from both tables.
Can you write the code to…. select the model, purchase price, sold price & dealership city?
select car_sales.model, car_sales.purchase_price, car_sales.sold_price, car_dealership.dealership_city
from car_sales, car_dealership
where car_sales.dealership_postcode = car_dealership.dealership_postcode
CHALLENGE 2
select
car_sales.make,
car_sales.model,
car_dealership.dealership_city
from car_sales, car_dealership
where car_sales.dealership_postcode = car_dealership.dealership_postcode
and car_sales.sold_price < 28000
What does this code do?
Selects make model city where thr sold price is less than 28000
Can you write the code to…. select the make, model, sold price & dealership name where sold price is more than £28,000?
select car_sales.make, car_sales.model, car_sales.sold_price, car_dealership.dealership_name
from car_sales, car_dealership
where car_sales.dealership_postcode = car_dealership.dealership_postcode
and car_sales.sold_price > 28000
CHALLENGE 3
select
car_sales.make,
car_sales.model,
car_sales.sold_date,
car_dealership.dealership_road
from car_sales, car_dealership
where car_sales.dealership_postcode = car_dealership.dealership_postcode
and car_sales.sold_date > '20210101'
What does this code do?
select make, model, sold_date, dealership_road, where the sold date is greater than 1st Jan 2021
Can you write the code to…. select the make, model, sold date & dealership city of the car sold on 2021-08-16?
select car_sales.make, car_sales.model, car_sales.sold_price, car_dealership.dealership_city
from car_sales, car_dealership
where car_sales.dealership_postcode = car_dealership.dealership_postcode
and car_sales.sold_date > '20210816'
CHALLENGE 4
select
car_sales.make,
car_sales.model,
car_sales.sold_date,
car_dealership.dealership_road
from car_sales, car_dealership
where car_sales.dealership_postcode = car_dealership.dealership_postcode
and car_sales.sold_date > '20210101'
order by car_sales.make asc
What does this code do?
Selects make, model, sold_date, dealership_road, where the sold_date is greater than 1st Jan 2021 ordered by make ascending
Can you re-write the code to…. order the output by dealership_road, descending ?
select
car_sales.make,
car_sales.model,
car_sales.sold_date,
car_dealership.dealership_road
from car_sales, car_dealership
where car_sales.dealership_postcode = car_dealership.dealership_postcode
and car_sales.sold_date > '20210101'
order by car_dealership.dealership_road desc
CHALLENGE 5
select sum(car_sales.purchase_price) as TotalPurchasePrice
from car_sales, car_dealership
where car_sales.dealership_postcode = car_dealership.dealership_postcode
What does this code do?
Totals up the purchase prices of all cars
Can you write the code to…. Select the total cost of purchasing all of the cars from the London dealership
select sum(car_sales.purchase_price) as TotalPurchasePrice
from car_sales, car_dealership
where car_sales.dealership_postcode = car_dealership.dealership_postcode
and car_dealership.dealership_city = 'London'
CHALLENGE 6
select sum(car_sales.sold_price - car_sales.purchase_price) as Profit
from car_sales, car_dealership
where car_sales.dealership_postcode = car_dealership.dealership_postcode
and car_dealership.dealership_city = 'manchester'
What does this code do?
Gives profit values for all cars sold in the Manchester dealership
Can you write the code to…. Select the total profit for BMW from Liverpool.
select sum(car_sales.sold_price - car_sales.purchase_price) as Profit
from car_sales, car_dealership
where car_sales.dealership_postcode = car_dealership.dealership_postcode
and car_dealership.dealership_city = 'liverpool'
and car_sales.make = 'bmw'
CHALLENGE 7
select count(*) as CountOfRecords
from car_sales, car_dealership
where car_sales.dealership_postcode = car_dealership.dealership_postcode
What does this code do?
Count the total number of rows produced when you join both tables
Can you write the code to…. Select a count of all records that contain cars sold for more than £50,000, from London.
select count(*)
from car_sales, car_dealership
where car_sales.dealership_postcode = car_dealership.dealership_postcode
and car_sales.sold_price > 50000
CHALLENGE 8
select
car_sales.*,
car_dealership.*
from car_sales, car_dealership
where car_sales.dealership_postcode = car_dealership.dealership_postcode
And car_sales.make = 'bmw'
What does this code do?
Select all records from both tables where the make is BMW
Can you write the code to…. Select all fields from both tables where make is Ferrari.
select
car_sales.*,
car_dealership.*
from car_sales, car_dealership
where car_sales.dealership_postcode = car_dealership.dealership_postcode
And car_sales.make = 'ferrari'
Can you write the code to…. Select all fields from both tables where dealership city is Liverpool.
select
car_sales.*,
car_dealership.*
from car_sales, car_dealership
where car_sales.dealership_postcode = car_dealership.dealership_postcode
And car_dealership.city = 'liverpool'
CHALLENGE 9
The owner of the business wants to create a new dealership.
Can you write the code to…. Insert a new record into the dealership table. It will be for a new dealership in London, across town from the current dealership that already presides in London.
INSERT INTO car_dealership (dealership_name, dealership_road, dealership_city, dealership_postcode)
VALUES ('south','1 East End Road','London','E2 3LM')
Can you create some new records of 3 cars of your choosing, and link them to the new dealership that you created?
insert into car_sales(registration,make,model,purchase_price,purchase_date,sold_price,sold_date,dealership_postcode)
values('DE3R1K','reliant','robin',1700000,'20190815',1900000,'20201005','E2 3LM')
insert into car_sales(registration,make,model,purchase_price,purchase_date,sold_price,sold_date,dealership_postcode)
values('HE38IE','vw','beetle',2000000,'20200815',3000000,'20200510','E2 3LM')
insert into car_sales(registration,make,model,purchase_price,purchase_date,sold_price,sold_date,dealership_postcode)
values('RA7OU','austin','mini metro',10,'19920201',20,'20230201','E2 3LM')
CHALLENGE 10 - MINI PROJECT
Step 1)
Create your own two tables, with your own data. Use any subject you want. You could chose school data (subject, grades, exam dates etc) or property business data (house address, purchase price, sold price, sold date etc), or choose one of your own.
At least 10 records
At least 7 fields
Include a record with some "test" data
Make sure you have created some questionable data too (badly spelled / incorrect)
Step 2)
Write the code to clean up your data.
Write the code to select 2 records that you consider to be important in your table.
Write the code to delete the test data row from your table.
Write the code to update the various badly spelt data.
Step 3)
Provide all code files to me in our revision lesson.