When connecting to your pg database, connect from this project directory.
To run sql statements from an external file, use the \i [filepath]
command.
Write your queries in denormal.sql
when instructed to.
- Create a new postgres user named
denormal_user
- Create a new database named
denormal_cars
owned bydenormal_user
- 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.sql
Create a query to get a list of allmake_title
values in thecar_models
table, without any duplicate rows. (should have 71 results) - In
denormal.sql
Create a query to list allmodel_title
values where themake_code
is'VOLKS'
, without any duplicate rows (should have 27 results) - In
denormal.sql
Create a query to list allmake_code
,model_code
,model_title
, and year fromcar_models
where themake_code
is'LAM'
(should have 136 rows) - In
denormal.sql
Create a query to list all fields from allcar_models
in years between2010
and2015
(should have 7884 rows)
Write your queries in normalized.sql
when instructed to.
- Create a new postgres user named
normal_user
- Create a new database named
normal_cars
owned bynormal_user
- 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
normalized.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.sql
Create queries to insert all of the data that was in thedenormal_cars.car_models
table, into the new normalized tables of thenormal_cars
database. - In
normal.sql
Create a query to get a list of allmake_title
values in thecar_models
table. (should have 71 results) - In
normal.sql
Create a query to list allmodel_title
values where themake_code
is'VOLKS'
(should have 27 results) - In
normal.sql
Create a query to list allmake_code
,model_code
,model_title
, and year fromcar_models
where themake_code
is'LAM'
(should have 136 rows) - In
normal.sql
Create a query to list all fields from allcar_models
in years between2010
and2015
(should have 7884 rows)