⭐ This is my third assignment for the CFG degree (Spring '23)
📊 I designed and built a database of the Great Walks in Aotearoa New Zealand using MySQL
🗺️ I wrote queries for:
- walks that take an hour or under to drive to from the closest town
- names of the walks and the regions they are in
- all South Island walks in ascending order by distance
- walks that cost under $20 per night to camp in summer and are under an hour drive away
- total number of walks per region
- average price of huts in NZD across all walks
- converting driving distance to town from mins to hours
- stored procedure for increasing campsite costs by %
Assignment requirements:
- Create a database with at least 3 tables with several columns
- Link tables using primary and foreign keys
- Populate the database with at least 8 rows of mock data per table to show use of DML commands
- Use at least 3 different data types while creating tables
- Use at least 2 constraints while creating tables, not including primary key or foreign key
- Use at least 3 queries to insert data
- Use at least 5 queries to retrieve data
- Use at least 1 query to delete data
- Use at least 2 aggregate functions
- Use at least 2 joins
- Use at least 2 additional in-built functions
- Use data sorting for majority of queries with ORDER BY
- Create and use one stored procedure or function to achieve a goal
- Normalise the DB by splitting the data out in tables where appropriate and not containing any duplicate data
- Provide a creative scenario of use