ISOM3260 Database Assignment 2: SQL Query Solutions for Duffy Run

This document provides a comprehensive solution to an ISOM3260 database assignment, focusing on SQL queries for the Duffy Run event database. The solution includes SQL statements to address various tasks, such as altering table structures (adding columns, modifying column types, dropping columns), dropping tables, inserting, deleting, and updating data, and retrieving information using SELECT statements with JOIN operations, WHERE clauses, and ORDER BY clauses. The assignment covers a range of SQL functionalities to query and manipulate data across multiple tables (YEAR, TYPE, PARTICIPANT, PET, DUFFYRUN). The queries address specific requirements, including filtering by participant name, year, and type; aggregating data using COUNT and GROUP BY; and finding the minimum time spent. The document also includes a bibliography of database systems textbooks, providing references for further study. This resource is designed to help students understand and solve database problems effectively.

Running head: DATABASE SQL QUERY
Database SQL Query
Name of the Student
Name of the University
Author’s name:

1DATABASE SQL QUERY
Query 1: alter table PARTICIPANT add column gender varchar(15) check (gender in
('M', 'F'));
Query 2: alter table YEAR modify column theme varchar(100);
Query 3: alter table PET drop column colour;
Query 4: DROP TABLE QUOTA;
Query 5: insert into YEAR values (2020, ‘Gelatoni’);
Query 6: delete from YEAR where year = 2020 and theme = ‘Gelatoni’;
Query 7: update PET set colour = ‘panda’ where pet_no = ‘M377’;
Query 8: select name from PARTICIPANT where participant = ‘P3210’;
Query 9: select year, type_name, time_spent from DUFFYRUN inner join
PARTICIPANT on DUFFYRUN.Participant = PARTICIPANT.Participant inner join TYPE on
DUFFYRUN.Type = TYPE.Type_id where PARTICIPANT.Name = ‘Lily’ order by
DUFFYRUN.year desc;
Query 10: select theme, type_name, count(DUFFYRUN.Participant) as `Total
Participants` from DUFFYRUN inner join YEAR on DUFFYRUN.Year = YEAR.Year inner
join TYPE on DUFFYRUN.Type = TYPE.Type_id where DUFFYRUN.Year = 2019 Group By
DUFFYRUN.Type;
Query 11: select year, DUFFYRUN.Participant, PARTICIPANT.Name, time_spent from
DUFFYRUN D1 inner join YEAR on DUFFYRUN.Year = YEAR.Year inner join
PARTICIPANT on DUFFYRUN.Participant = PARTICIPANT.Participant where time_spent =

2DATABASE SQL QUERY
(min(D1.time_spent) from DUFFYRUN D2 where D1.Year = D2.Year and D1.Type =
D2.Type);
Bibliography:
Elmasri, R. and Navathe, S., 2017. Fundamentals of database systems. Pearson.
Jukic, N., Vrbsky, S. and Nestorov, S., 2016. Database systems: Introduction to databases and
data warehouses. Prospect Press.