• Create a database called apartments
• Using this database, create two tables, one for owners and one for propertys (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
• We don't have or need access to your local database - that's just for you to test it and come up with the right query! So, in order to turn this assignment in, put your SQL queries into part-1.sql, part-2.sql, and part-3.sql which can be found in the starter-code folder.
  • Your process will likely be running your query, and once it works, copying it into the appropriate file in the starter-code directory
• Add, commit, and push your changes
• Make a pull request!
• Relax! 😎

Place your answers in part-1.sql.

• The owners table should consist of:
  • id (this should be the primary key as well as a unique number that increments automatically)
  • name - name of owner
  • age - age of owner
• The properties table should consist of:
  • id (this should be the primary key as well as a unique number that increments automatically)
  • name - name of property
  • units - number of units
  • owner_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)

Write down the following sql statements that are required to solve the following tasks. Place your answers in part-3.sql.

1. Show all the data in the owners table.
2. Show the names of all owners.
3. Show the ages of all of the owners in ascending order.
4. Show the name of an owner whose name is Donald.
5. Show the age of all owners who are older than 30.
6. Show the name of all owners whose name starts with an E.
7. Change Jane's age to 30.
8. Change Jane's name to Janet.
9. Delete the owner named Janet.
10. Show the names of the first three owners in your owners table.
11. Show the name of all owners whose name contains an a.
12. Show all of the properties in alphabetical order that are not named Archstone and do not have an id of 3 or 5.
13. Show the highest age of all owners.
14. Show the highest age of owners who are under 30 and whose name contains an o. Limit to one result.
15. 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!

16. List all properties sorted by the owners names
17. In the properties table change the name of the column "name" to "property_name".
18. Add a new property to the owner with an id of 3.

1. All content is licensed under a CC-BY-NC-SA 4.0 license.
2. All software code is licensed under GNU GPLv3. For commercial use or alternative licensing, please contact [email protected].