THERE IS NO PRE-EXISTING TABLES. YOU WILL CREATE TABLE BASED ON QUESTIONS.WRITE THE COMMANDS AND GENERATE THE TABLE BASED ON WHAT IS BEING ASKED. EXAMPLE QUESTION 1 NEW TABLE CREATE NEW FOR EACH THING NEEDED EXAMPLE USER_HOME_ADDR table WITH HOME_ADDR stored. Can be random information put into these created tables as long as it runs correctly. IF NEEDED JUST WRITE THE COMMAND THAT WOULD WORK PROPERLY BASED ON THE QUESTION IF IT DOESN'T ASK YOU TO POPULATE NEW TABLES.

This lab covers aggregate functions and normalization. If asked for a SQL statement write the SQL to perform the requested task, do not use the GUI.

1. Select the population (count) of users, grouped by state. Only use the home address to determine the users home state. If a user has 2 home addresses, both should be counted.

Paste the SQL statement and results below.

Remember, pasting the results is 50% of the credit.

Select the count of DVDs released grouped by year. Only show the last 10 years with the current year on top.

Paste the SQL statement and the results below.

In you example database look at the vehicle table. Like the cableRawData table the data is not normalized. For this section you will be normalizing this table. In the cableRawData example we took the columns with repeating data (genre & station) and created separate tables for them. These lookup tables had 2 columns, one an int and one a varchar field. You will be performing the same process on the vehicle table.

The columns with repeating data will be moved to separate tables. Create tables for the following columns: make, model, drive, and fuelType. Name the tables, vehicleMake, vehicleModel, vehicleDrive and vehicleFuelType. Each table should have 2 columns. Created the id field of these tables as auto incrementing integers and the name field as varchars. The table type should be INNOdb.

Paste the SQL below.

Create a new table called vehicleNormal. Include the following columns: vehicleId, makeId, modelId, Year, cylinders, driveId, mpgHighway, mpgCity and fuelTypeId. Ids should be integers. MPG should be decimals.

Paste the SQL below.

Create 4 foreign keys on the vehicleNormal table to the 4 lookup tables.

Paste the SQL statements below.

Create unique indexes on the name (varchar) columns for the 4 tables we just created in the previous question.

Paste the SQL statements below.

Populate the 4 new tables with the appropriate data using the select into statement with the distinct function.

Paste the SQL statements below.

To increase the speed of the queries we will run in the next question create four indexes on the vehicle table for the columns: make, model, drive, and fuelType. There should be non-unique or regular indexes.

Paste the SQL below.

Populate vehicleNormal by joining the vehicle, vehicleMake, vehicleModel, vehicleDrive and vehicleFuelType tables.

Paste the SQL below.

Write a select statement to return the following fields: vehicle Id, make, model, year, drive, and fuel type. Do not use the vehicle table; instead use the 5 new tables we just created.

Paste the SQL and results below.

FULL SQL DATABASE DUMP NEEDED