Format assignment 2

2 files changed, 54 insertions, 15 deletions

diff --git a/assignments/2/README.md b/assignments/2/README.md
index 7b9fe29..9d96c28 100644
--- a/assignments/2/README.md
+++ b/assignments/2/README.md
@@ -32,10 +32,10 @@ Answer the following questions (250 words max/question).
 
 **What are the typical integrity controls performed in both data integrity and referential integrity?**
 
-* default value
-* range control
-* null value control
-* referential integrity control
+* default value: ensure each field start with a default value if one isn't provided
+* range control: ensures that a field falls within a specific range of values
+* null value control: ensures that `null` values are not allowed to be inserted in a fields position.
+* referential integrity control: ensures that foreign keys to other tables exist ensuring orphaned records cannot be created.
 
 **Using an example for each situation, illustrate the three common situations that suggest relations should be denormalized.**
 
@@ -167,6 +167,21 @@ Employee(emp-no, name, department, salary)
 ProjAssigned(emp-no, proj-no, worked-hours)
 ```
 
+```sql
+CREATE TABLE Employee(
+  "emp-no" bigint primary key,
+  name varchar(255),
+  department varchar(255),
+  salary decimal
+);
+
+CREATE TABLE ProjAssigned(
+  "proj-no" bigint primary key,
+  "emp-no" bigint references Employee("emp-no"),
+  "worked-hours" integer
+);
+```
+
 **Write one SELECT SQL query to list the numbers and names of all employees with a salary greater than 66 000 who are assigned to projects, the projects they are assigned to, and the corresponding hours worked. Your list should be sorted by employee name.**
 
 ```sql
@@ -201,9 +216,29 @@ CUSTOMER (name, departure_city, destination, journey_class)
 TRANSACTION (number, cust_name, travel_agent_name, amount_paid)
 ```
 
+```sql
+CREATE TABLE TravelAgent(
+  name varchar(255),
+  age integer,
+  salary decimal
+);
+CREATE TABLE Customer(
+	name varchar(255),
+	departure_city varchar(255),
+	destination varchar(255),
+	journey_class varchar(255)
+);
+CREATE TABLE Transaction(
+	"number" integer,
+	cust_name varchar(255),
+	travel_agent_name varchar(255),
+	amount_paid decimal
+);
+```
+
 Write SQL statements to answer the following questions.
 
-* Compute the number of different customers who have a transaction.
+**Compute the number of different customers who have a transaction.**
 
 ```sql
 SELECT COUNT(DISTINCT c.name)
@@ -211,7 +246,7 @@ FROM Customer c
 INNER JOIN Transaction t ON t.cust_name = c.name;
 ```
 
-* Display the name of the oldest travel agent.
+**Display the name of the oldest travel agent.**
 
 ```sql
 SELECT t.name
@@ -221,7 +256,7 @@ WHERE t.age IN (
 );
 ```
 
-* List the total number of transactions for each travel agent. Consider only those transactions where the amount paid exceeds 1 000.
+**List the total number of transactions for each travel agent. Consider only those transactions where the amount paid exceeds 1 000.**
 
 ```sql
 SELECT travel_agent_name, COUNT(travel_agent_name)
@@ -231,7 +266,7 @@ GROUP BY travel_agent_name
 ORDER BY travel_agent_name;
 ```
 
-* Display the names and ages of the travel agents who have arranged journeys for customer “John Smith”, in descending order of age (use a subquery).
+**Display the names and ages of the travel agents who have arranged journeys for customer “John Smith”, in descending order of age (use a subquery).**
 
 ```sql
 SELECT name, age
@@ -244,7 +279,7 @@ WHERE name IN (
 ORDER BY age DESC;
 ```
 
-* Display the names and ages of travel agents who have arranged journeys for customer “John Smith”, in descending order of age (do not use a subquery).
+**Display the names and ages of travel agents who have arranged journeys for customer “John Smith”, in descending order of age (do not use a subquery).**
 
 ```sql
 SELECT DISTINCT(ta.name), ta.age
@@ -254,7 +289,7 @@ WHERE t.cust_name = 'John Smith'
 ORDER BY ta.age DESC;
 ```
 
-* Display the age of travel agents who have arranged journeys for customer “John Smith” to “Ottawa” (use a subquery).
+**Display the age of travel agents who have arranged journeys for customer “John Smith” to “Ottawa” (use a subquery).**
 
 ```sql
 SELECT age
@@ -271,7 +306,7 @@ WHERE name IN (
 );
 ```
 
-* Display the age of travel agents who have arranged journeys for customer “John Smith” to “Ottawa” (do not use a subquery).
+**Display the age of travel agents who have arranged journeys for customer “John Smith” to “Ottawa” (do not use a subquery).**
 
 ```sql
 SELECT DISTINCT(ta.age)
@@ -282,7 +317,7 @@ WHERE c.destination = 'Ottawa'
 AND c.name = 'John Smith';
 ```
 
-* Display the names and salaries of all travel agents who did not arrange journeys for customer “John Smith”, in ascending order of salary.
+**Display the names and salaries of all travel agents who did not arrange journeys for customer “John Smith”, in ascending order of salary.**
 
 ```sql
 SELECT Distinct(ta.name), ta.salary
@@ -295,7 +330,7 @@ WHERE ta.name NOT IN (
 ORDER BY ta.salary ASC;
 ```
 
-* Display the names of travel agents who have five or more transactions.
+**Display the names of travel agents who have five or more transactions.**
 
 ``sql
 SELECT DISTINCT(t.travel_agent_name)
@@ -304,7 +339,7 @@ GROUP BY t.travel_agent_name
 HAVING COUNT(t.travel_agent_name) > 5;
 ```
 
-* Display the names of all travel agents who have arranged at least ten journeys to “Ottawa”.
+**Display the names of all travel agents who have arranged at least ten journeys to "Ottawa".**
 
 ```sql
 SELECT DISTINCT(t.travel_agent_name)
diff --git a/assignments/2/run.sql b/assignments/2/run.sql
index 13d3692..652bcae 100644
--- a/assignments/2/run.sql
+++ b/assignments/2/run.sql
@@ -43,7 +43,11 @@ DROP TABLE IF EXISTS TravelAgent;
 DROP TABLE IF EXISTS Customer;
 DROP TABLE IF EXISTS Transaction;
 
-CREATE TABLE TravelAgent(name varchar(255), age integer, salary decimal);
+CREATE TABLE TravelAgent(
+  name varchar(255),
+  age integer,
+  salary decimal
+);
 CREATE TABLE Customer(
 	name varchar(255),
 	departure_city varchar(255),