- Create a database called
apartments
- Using this database, create two tables, one for
owners
and one forproperties
(table names shoud be lowercase and plural) - Keep this relationship in mind when designing your schema:
- One owner can have many properties
- Fork and clone this as normal
- In order to turn this assignment in, put your SQL commands into
part-1.sql
,part-2.sql
, andpart-3.sql
which can be found in thestarter-code
folder.- Your process will likely be writing your command in the relevant file, and then copying it and pasting it in your
psql
shell. If it doesn't work, you can edit it in your IDE and try again.
- Your process will likely be writing your command in the relevant file, and then copying it and pasting it in your
- Add, commit, and push your changes when you finish each part
- Make a pull request!
- Relax! 😎
Place your answers in part-1.sql
.
- Create a table called
owners
. This owners table should consist of:id
(this should be the primary key as well as a unique number that increments automatically)name
- name of ownerage
- age of owner
- Create a
properties
table which should consist of:id
(this should be the primary key as well as a unique number that increments automatically)name
- name of propertyunits
- number of unitsowner_id
- reference to owner table
Place your answers in part-2.sql
.
-
Insert the following owners
- Donald - age 29
- John - age 33
- Jane - age 43
- Yuki - Age 67
- Erin - Age 21
- Siobhan - Age 55
- Add 3 more people (you choose name / age)
-
Insert the following properties (you can pick and choose the property owners)
- Archstone - 20 units
- Zenith Hills - 10 units
- Willowspring - 30 units
- Ridgefield Bay - 5 units
- Brookvista - 20 units
- Goldendale - 15 units
- Green Haven - 40 units
- Fair Creek - 35 units
- Parkview Pointe - 50 units
- Royal Gardens Court - 45 units
- Add 3 more properties (you choose name / units / property owners)
Write down the following sql statements that are required to solve the following tasks. Place your answers in part-3.sql
.
- Show all the data in the owners table.
- Show the names of all owners.
- Show the ages of all of the owners in ascending order.
- Show the name of an owner whose name is Donald.
- Show the age of all owners who are older than 30.
- Show the name of all owners whose name starts with an E.
- Change Jane's age to 30.
- Change Jane's name to Janet.
- Delete the owner named Janet.
- Show the names of the first three owners in your owners table.
- Show the name of all owners whose name contains an
a
. - Show all of the properties in alphabetical order that are not named Archstone and do not have an id of 3 or 5.
- Show the highest age of all owners.
- Show the highest age of owners who are under 30 and whose name contains an
o
. Limit to one result. - Count the total number of properties where the owner_id is between 1 and 3.
These might require you to look up documentation online, or look at the next section in the notes. This is a bonus after all, so we're getting a little bit ahead!
- List all properties sorted by the owners names
- In the properties table change the name of the column "name" to "property_name".
- Add a new property to the owner with an id of 3.
Need a hint for 16?
Look up the JOIN keyword. This allows you to pull data from two different tables based on fields they have in common.Need a hint for 17?
Look up documentation for ALTER TABLE. This allows you to change the schema (column name in this case).Need a hint for 18?
Look up INSERT INTO. Don't forget that the foreign key, `owner_id` needs to exist as an id in the owner table!- All content is licensed under a CC-BY-NC-SA 4.0 license.
- All software code is licensed under GNU GPLv3. For commercial use or alternative licensing, please contact [email protected].