CHALLENGES - multi table

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

What does this code do?

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.

Page updated

Google Sites

Report abuse