Using the BIKES database structure and file create the BIKES objects. Be sure to create

the schema first $($then you no longer need the command$).$

Using the Excel Spreadsheet With Data create all the necessary INSERT statements to

populate all $4$ tables $(30$ Points$).$

You will need to submit a $.$sql $($text file$)$ that includes all your INSERTS and your queries

below $($do NOT include any screenshots $-$ your $.$sql code should adequately answer the

questions$).$

Write a query to demonstrate that each object has the required number of rows in the

table

$/*$ PRODUCTS Table Data $($contains $313$ rows$)-5$ Points$*/$

$/*$ BRANDS Table Data $($contains $9$ rows$)-5$ Points$*/$

$/*$ CATEGORIES Table Data $($contains $7$ rows$)-5$ Points $*/$

$/*$ STOCKS Table Data $($contains $939$ rows$)-5$ Points$*/$

Complex Queries

Graphics are to give you an idea of what your query should output. They are not to represent the

exact output your query will generate.

$/*$ Create a query that shows all the data attributes exactly like the spreadsheet $*/$

$(20$ Points$)$ Should look something like:

Compare the output from your query to the spreadsheet $-$ they should match.

$/*$ How many different models does the Bike business carry? Create a query that shows

how many different bikes of brands names are in each category name $-10$ Points$*/$

$10./*$ Not every store may carry every bike model. Let's check? Create a query that shows

category names, brand names and how many each store has $($not how many bikes the

store has but how many models each store carries $-$ they will naturally carry more bikes

than models and that question is below$)-10$ Points$*/$

Results

Messages

$11./*$ For insurance reasons we need to know the value of all our inventory.

Write a query that shows the total quantity and amount of inventory for each store

based upon list price broken out by category of bike and brand. Brand total dollars

should look like $$12,456\mathrm{.}00-10$ Points $*/$

$/*$ Uncomment line $2$ and only EXECUTE LINE $2.$ After you have created the schema bikes re$-$comment line $2$ and execute the remaining lines$*/$

$--$ create schema bikes;

$/******$ Object: Table $[$production$].[$brands$]$ Script Date: $10/31/20214$:$55$:$23$ PM $******/$

DROP TABLE IF EXISTS $[$bikes$].[$products$]$;

CREATE TABLE $[$bikes$].[$products$]($

$[$product$\_$id$][$int$]$ NOT NULL PRIMARY KEY,

$[$product$\_$name$]$ varchar NOT NULL,

$[$brand$\_$id$][$int$]$ NOT NULL,

$[$category$\_$id$][$int$]$ NOT NULL,

$[$model$\_$year$][$smallint$]$ NOT NULL,

$[$list$\_$price$][$decimal$](10,2)$ NOT NULL

$)$;

DROP TABLE IF EXISTS $[$bikes$].[$brands$]$;

CREATE TABLE $[$bikes$].[$brands$]($

$[$brand$\_$id$][$int$]$ NOT NULL PRIMARY KEY,

$[$brand$\_$name$]$ varchar NOT NULL

$)$;

DROP TABLE IF EXISTS $[$bikes$].[$categories$]$;

CREATE TABLE $[$bikes$].[$categories$]($

$[$category$\_$id$][$int$]$ NOT NULL PRIMARY KEY,

$[$category$\_$name$]$ varchar NOT NULL

$)$;

ALTER TABLE $[$bikes$].[$products$]$ ADD FOREIGN KEY $([$brand$\_$id$])$ REFERENCES $[$bikes$].[$brands$]([$brand$\_$id$])$;

ALTER TABLE $[$bikes$].[$products$]$ ADD FOREIGN KEY $([$category$\_$id$])$ REFERENCES $[$bikes$].[$categories$]([$category$\_$id$])$;

CREATE TABLE $[$bikes$].[$stocks$]($

$[$store$\_$id$][$int$]$ NOT NULL,

$[$product$\_$id$][$int$]$ NOT NULL,

$[$quantity$][$int$]$ NULL

$)$;

ALTER TABLE $[$bikes$].[$stocks$]$ ADD PRIMARY KEY $([$store$\_$id$],[$product$\_$id$])$;

ALTER TABLE $[$bikes$].[$stocks$]$ ADD FOREIGN KEY $([$product$\_$id$])$ REFERENCES $[$bikes$].[$products$]([$product$\_$id$])$;