IT360 Lab 4: SQL Joins, DDL, and DML

 

DUE: Thursday February 7, 2013, 2359 (paper copy BEFORE start of lab next day)

 

In this lab you will develop your database implementation skills by modifying and querying databases in MySQL using SQL.

 

Follow the tutorial on the course calendar – Lab 2 - to connect to MySQL.

 

In this lab, you will work on the PerformerConcerts database: you will create the following tables that will store information about performance types (ex. song, dance, etc.), about performers, arenas (for performances) and concerts. Here is the schema for these tables, with primary key underlined and foreign keys in italic:

 

PerformanceTypes(PerformanceTypeName) – types of performance

Performers(PerformerID, FirstName, LastName, Address, PerformanceTypeName) – Each performer has a performedID (integer), a name, address and type of performance

Arenas(ArenaID, ArenaName, City, Capacity)  -- Each arena has an id (integer), a name (Ex. Verizon Center), a city where the arena is located, and seating capacity of the arena

Concerts(PerformerID, ArenaID, ConcertDate) – each concert is given by one performer, on a given arena, at a given date (type ‘date’ in MySQL)

 

Now, write SQL queries to accomplish the following tasks. Save all queries in a file called yourlastname_yourfirstname_lab4.sql.  Write the Question number and English question as a comment before each SQL query (In MySQL Workbench, go to File-> New Query/Script tab. Copy-paste the queries below into the new script tab. Comment each question using /* */ and then write the SQL query below it. Save your script as yourlastname_yourfirstname_lab4.sql).

 

Important Note: Your queries should run correctly on any possible data, not just the sample data currently in the database.

 

  1. Write and execute the SQL statements to create the 4 tables given above. Choose appropriate data types for each column, and declare primary key and foreign key constraints as appropriate. If you want more information about the possible data types, you can consult the MySQL manual: http://dev.mysql.com/doc/refman/5.0/en/data-types.html

 

  1. Insert one row in the PerformanceTypes table, for type ‘singer’. Execute the query. Insert two more rows into the table, for types ‘dancer’ and ‘comedian’.

 

  1. Write the SQL statement to modify your Performers table to add a new column called DateOfBirth. Choose the appropriate data type for this column. The values in the column could be null.

 

  1. a) Insert one row in the Performers table with PerformerID 1, name John Dow, address Annapolis, MD, and performance type ‘singer’. Execute the query.

 

b) Try now to insert a performer row with a PerformanceTypeName that does not exist in PerformanceTypes table (ex. ‘painter’). What happens? (write your query and answer as a comment in yourlastname_yourfirstname_lab4.sql)

 

            c) Insert 3 more rows in the Performers table,  with  names ‘Matt Smith’, ‘Jane Brown’, and respectively ‘Jennifer Shade’, and addresses ‘Baltimore, MD’, ‘New York, NY’, and respectively ‘Seattle, WA’. All these 3 performers should be of type ‘dancer’.

 

  1. Change the DateOfBirth of the performer with id 1 to be ‘1990-03-02’.

 

  1. a) Remove the performer ‘Matt Smith’ from the Performers table.

 

b) What would happen if there are two performers with this name?             (write your answer in yourlastname_yourfirstname_lab4.sql)

 

  1. a) Write the SQL statement to remove performance type ‘singer’ from the PerformanceTypes table.

 

b) Execute the statement above, then list all rows from the PerformanceTypes table. Was ‘singer’ removed? Explain why the row was removed from the PerformanceTypes table, or why it was not removed. (write all your answers in yourlastname_yourfirstname_lab4.sql)

 

8.      a) Write the SQL statement to update the performance type name for ‘dancer’ in PerformanceTypes to be ‘dance performer’.

           

            b) Execute the statement above. Does the query succeed? What are the effects and why? (list all rows from Performers and look at the values)

 

9.      a) Write the SQL statements to insert 3 rows with ids 1, 2, and respectively 3 into the Arenas table. Execute the statements.

 

b) Write the SQL statements to insert 4 rows in the Concerts table, two of them for arena 1, and two for arena 2.

 

c) Write the SQL statement to update all arenas with at least 2 concerts to have the seat capacity 50000. The query should work for any possible data in the database.

 

10.  a) Create a new ‘Dancers’ table with the same structure as the Performers table (copy-paste-modify the create table statement you had for Performers. Note: remember that the constraints names need to be unique in the entire database)

 

b) Insert all performers of type ‘dancer’ from the Performers table into the Dancers table. You should use only one SQL INSERT statement and your statement should work for any data possible in the Performers table.

 

11.  (Effect of foreign key constraint on rows)

a) Change table Concerts to remove the foreign key constraint referencing the Performers table

 

b) Delete from the Performers table all performers of type ‘dancer’ (or ‘dance performer’ whichever is appropriate for your case).

 

c) Find all ids in the Concerts table that do not exist in the Performers table

 

d) Delete all rows from the Concerts table with a performer id that does not exist in the Performers table

 

12.  (Effect of foreign key constraint on tables)

a) Write the SQL statement to remove the PerformanceTypes table.

 

b) Try execute the statement above. What happens? Why? (write your answers to yourlastname_yourfirstname_lab4.sql)

           

            c) Change the Performers table to remove the foreign key constraint referencing the PerformanceTypes table. Also change the Dancers table to remove the foreign key constraint referencing the PerformanceTypes table.

 

            d) Execute the statement to remove the PerformanceTypes table. Does it succeed now? Why?

 

13.  (Joins)

List all the ArenaID, name, city, capacity from the Arenas table, and for those who held any concerts, also list the performer id and date of the concert (these columns should be null for the arenas with no concerts).

           

 

  1. (Extra credit) List the arena name for the arenas with seating capacity lower than the average seating capacity.

 

  1. (Extra credit) List the name and id for performers who had concerts both in arena 1 AND in arena 2.

 

  1. (Extra credit) List the performer id, performer name, and the number of concerts for that performer, for each performer that participated in at least 2 concerts.

 

  1. (Extra credit) List the id and name of all performers that gave concerts in each existing arena. For example, if arena 1, arena 2, arena 3 are all the arenas in the Arenas table, list the performers that had concerts at all these arenas.

 

 

Turn in BOTH electronic (due Thursday February 7, 2013 at 2359) and paper copies (due before start of lab on February 8, 2013):

 

Electronic:

  1. Upload the yourlastname_yourfirstname_lab4.sql file containing all the questions and answers for this lab to Lab 4 assignment on Blackboard.

Hard-copies:

  1. The completed assignment coversheet. Your comments will help us improve the course.
  2. A hard copy of yourlastname_yourfirstname_lab4.sql.