COMP1201 Canada Provinces and Cities DB

Create Your Own Database

For this assignment, you'll write the MySQL statements to create a database, create 3 tables, populate tables with data, and code a few queries.

The topic of this database is Canadian provinces and cities. One province has many cities.

You'll have 3 tables in total: two will be in a 1 (province) : M (city) relationship and one 1:1, you decide what the topic of this table will be, and which table it will connect to.

The specifications

Database name - should be your name ie -jpichie

Columns (fields):

• at least 4 fields each in the primary and related table
• at least 3 fields in the table that is in a 1:1 relationship
• REMEMBER YOUR PRIMARY KEYS AND FOREIGN KEYS

Rows (records):

• at least 5 records in primary table (province)
• at least 10 in related table (city)
• and at least 3 in the 1:1 table

Data Types: include each type at least once throughout your database

CHAR VARCHAR
INT DECIMAL
DATE BOOLEAN

Standards: Follow programming standards

Queries - code a SELECT statements for 1-5 below, use aliases for all joins. Put the question number as a comment ( two dashes and a space) before each of the statements below.

• Code a select statement to display ALL data from any one of your tables, sorted by something other than primary key column
• Code a select statement to display 3 columns from one of your tables with values above or below a numeric test of your choice. E.g. all rows with a price less than $10.
• Code a select statement to display at least 3 columns from one table with values between two dates. Sort from most recent to oldest date.
• Display several columns from one table that includes a calculated field. Include a heading (alias) for the new calculated column. Sort results from highest to lowest on your calculated column.
• Display 2 columns from the primary table and 3 from its related table. Sort by one of the columns in the primary table, then within that sort, sort one of the fields in the related table.
• Code an update statement to change any column's field value to a different value. For example, change 'Kingston to Kings Town. Simply make up the data.
• Create a stored procedure that will accept an input parameter. Your stored procedure will be a SELECT statement that will use the INPUT parameter as the value for the WHERE clause.