DAT380 Advanced Database Architecture

DAT/380 Week 2 LAB 2.9 - Select movie ratings with left join

The Movie table has the following columns:
ID - integer, primary key
Title - variable-length string
Genre - variable-length string
RatingCode - variable-length string
Year - integer
The Rating table has the following columns:

Code - variable-length string, primary key
Description - variable-length string
Write a SELECT statement to select the Title, Year, and rating Description. Display all movies, whether or not a RatingCode is available.

Hint: Perform a LEFT JOIN on the Movie and Rating tables, matching the RatingCode and Code columns.

DAT/380 Week 5 LAB 5.6 - Implement independent entity (Sakila)

Implement a new independent entity phone in the Sakila database. Attributes and relationships are shown in the following diagram:

The phone entity appears on the right. The phone entity contains four attributes, each followed by cardinality information: phone_id 1-1(1), country_code M-1(1), phone_numer M-1(1), and phone_type M-1(0). Three entities appear on the left: store, staff, and customer, connected to the phone entity by three identical relationships. The three relationships are named 'has' and have cardinality 1(0) on both sides.

The diagram uses Sakila naming conventions. Follow the Sakila conventions for your table and column names:
All lower case
Underscore separator between root and suffix
Foreign keys have the same name as referenced primary key

Write CREATE TABLE and ALTER TABLE statements that:
1. Implement the entity as a new phone table.
2. Implement the has relationships as foreign keys in the Sakila customer, staff, and store tables.
3. Remove the existing phone column from the Sakila address table.

Step 2 requires adding a foreign key constraint to an existing table. Ex:
ALTER TABLE customer
ADD FOREIGN KEY (phone_id) REFERENCES phone(phone_id)
ON DELETE SET NULL
ON UPDATE CASCADE;

Specify data types as follows:
phone_id, phone_number, and country_code have data type INT.
phone_type has date type VARCHAR(12) and contains strings like 'Home', 'Mobile', and 'Other'.

Apply these constraints:
NOT NULL constraints correspond to cardinalities on the diagram above.
Foreign key actions are SET NULL for delete rules and CASCADE for update rules.
Specify a suitable column as the phone table primary key.

DAT/380 Week 4 LAB 4.7 - Select number of movies grouped by year

The Movie table has the following columns:
ID - integer, primary key
Title - variable-length string
Genre - variable-length string
RatingCode - variable-length string
Year - integer

Write a SELECT statement to select the year and the total number of movies for that year.

Hint: Use the COUNT() function and GROUP BY clause

DAT/380 Week 4 LAB 4.6 - Select lesson schedule with inner join

The database has three tables for tracking horse-riding lessons:
Horse with columns:
ID - primary key
RegisteredName
Breed
Height
BirthDate

Student with columns:
ID - primary key
FirstName
LastName
Street
City
State
Zip
Phone
EmailAddress

LessonSchedule with columns:
HorseID - partial primary key, foreign key references Horse(ID)
StudentID - foreign key references Student(ID)
LessonDateTime - partial primary key

Write a SELECT statement to create a lesson schedule with the lesson date/time, horse ID, and the student's first and last names. Order the results in ascending order by lesson date/time, then by horse ID. Unassigned lesson times (student ID is NULL) should not appear in the schedule.

Hint: Perform a join on the Student and LessonSchedule tables, matching the student IDs.

DAT/380 Week 3 LAB 3.16 - Select horses with logical operators

The Horse table has the following columns:
ID - integer, primary key
RegisteredName - variable-length string
Breed - variable-length string
Height - decimal number
BirthDate - date

Write a SELECT statement to select the registered name, height, and birth date for only horses that have a height between 15.0 and 16.0 (inclusive) or have a birth date on or after January 1, 2020.

DAT/380 Week 3 LAB 3.15 - Delete rows from Horse table

The Horse table has the following columns:
ID - integer, auto increment, primary key
RegisteredName - variable-length string
Breed - variable-length string
Height - decimal number
BirthDate - date

Delete the following rows:
Horse with ID 5.
All horses with breed Holsteiner or Paint.
All horses born before March 13, 2013.

DAT/380 Week 3 LAB 3.14 - Update rows in Horse table

The Horse table has the following columns:
ID - integer, auto increment, primary key
RegisteredName - variable-length string
Breed - variable-length string, must be one of the following: Egyptian Arab, Holsteiner, Quarter Horse, Paint, Saddlebred
Height - decimal number, must be ≥ 10.0 and ≤ 20.0
BirthDate - date, must be ≥ Jan 1, 2015
Make the following updates:

Change the height to 15.6 for horse with ID 2.
Change the registered name to Lady Luck and birth date to May 1, 2015 for horse with ID 4.
Change every horse breed to NULL for horses born on or after December 22, 2016.

DAT/380 Week 3 LAB 3.13 - Insert rows into Horse table

The Horse table has the following columns:
ID - integer, auto increment, primary key
RegisteredName - variable-length string
Breed - variable-length string, must be one of the following: Egyptian Arab, Holsteiner, Quarter Horse, Paint, Saddlebred
Height - decimal number, must be between 10.0 and 20.0
BirthDate - date, must be on or after Jan 1, 2015

Insert the following data into the Horse table:
RegisteredName Breed Height BirthDate
Babe Quarter Horse 15.3 2015-02-10
Independence Holsteiner 16.0 2017-03-13
Ellie Saddlebred 15.0 2016-12-22
NULL Egyptian Arab 14.9 2019-10-12

DAT/380 Week 3 LAB 3.12 - Create LessonSchedule table with FK constraints

Two tables are created:
Horse with columns:
ID - integer, primary key
RegisteredName - variable-length string

Student with columns:
ID - integer, primary key
FirstName - variable-length string
LastName - variable-length string

Create the LessonSchedule table with columns:
HorseID - integer with range 0 to 65 thousand, not NULL, partial primary key, foreign key references Horse(ID)
StudentID - integer with range 0 to 65 thousand, foreign key references Student(ID)
LessonDateTime - date/time, not NULL, partial primary key

If a row is deleted from Horse, the rows with the same horse ID should be deleted from LessonSchedule automatically.

If a row is deleted from Student, the same student IDs should be set to NULL in LessonSchedule automatically.

Note: Table and column names are case sensitive in the auto-grader.

CREATE TABLE Horse (
ID SMALLINT UNSIGNED AUTO_INCREMENT,
RegisteredName VARCHAR(15),
PRIMARY KEY (ID)
);

CREATE TABLE Student (
ID SMALLINT UNSIGNED AUTO_INCREMENT,
FirstName VARCHAR(20),
LastName VARCHAR(30),
PRIMARY KEY (ID)
);

-- Your SQL statements go here