To run sql statements from an external file, load them into your RDBMS client and run the commands from there.
Set your database name to be denormal_cars
.
Set your database username to be denormal_user
.
Set your database password to be denormalPassword
.
Write your queries in denormal_cars.sql
when instructed to.
- Run the provided
scripts/denormal_data.sql
script on thedenormal_cars
database. - Inspect the table named
car_models
by running\dS
and look at the data using someSELECT
statements. - In
denormal_cars.sql
Create a query to get a list of allmake_title
values in thecar_models
table. Without any duplicate rows, this should have 71 results. - In
denormal_cars.sql
Create a query to list allmodel_title
values where themake_code
is'VOLKS'
. Without any duplicate rows, this should have 27 results. - In
denormal_cars.sql
Create a query to list allmake_code
,model_code
,model_title
, andyear
fromcar_models
where themake_code
is'LAM'
, Without any duplicate rows, this should have 136 rows. - In
denormal_cars.sql
Create a query to list all fields from allcar_models
in years between2010
and2015
. Without any duplicate rows, this should have 7884 rows.
Write your queries in normal_cars.sql
when instructed to.
- Create a new postgres user named
normal_user
. - Create a new database named
normal_cars
owned bynormal_user
. - Connect to the new database by using
\c normal_cars
in your psql client. - Run the provided
scripts/denormal_data.sql
script on thenormal_cars
database. - Whiteboard your solution to normalizing the
denormal_cars
schema. - [bonus] Generate a diagram (somehow) in .png (or other) format, that of your normalized cars schema. (save and commit to this repo).
- In
normal_cars.sql
Create a query to generate the tables needed to accomplish your normalized schema, including any primary and foreign key constraints. Logical renaming of columns is allowed. - Using the resources that you now possess, In
normal_cars.sql
Create queries to insert all of the data that was in thecar_models
table, into the new normalized tables of thenormal_cars
database. - In
normal_cars.sql
Create a query to get a list of allmake_title
values in thecar_models
table. Without any duplicate rows, this should have 71 results. - In
normal_cars.sql
Create a query to list allmodel_title
values where themake_code
is'VOLKS'
Without any duplicate rows, this should have 27 results. - In
normal_cars.sql
Create a query to list allmake_code
,model_code
,model_title
, and year fromcar_models
where themake_code
is'LAM'
. Without any duplicate rows, this should have 136 rows. - In
normal_cars.sql
Create a query to list all fields from allcar_models
in years between2010
and2015
. Without any duplicate rows, this should have 7884 rows.