IT360 Lab 4: SQL Joins, DDL, and DML

 

DUE: Monday February 10, 2014, 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 (ex. singer, dancer, etc.)

Performers(PerformerID, FirstName, LastName, Address, PerformanceTypeName) – Each performer has an id (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 another row into Performers table, but this time use ‘painter’ as the performance type (or any PerformanceTypeName that does not exist in PerformanceTypes table). 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 would be 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)

 

c) Write the SQL statement to remove performance type ‘comedian’ from the PerformanceTypes table.

 

d) Execute the statement above, then list all rows from the PerformanceTypes table. Was ‘comedian’ 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. What are the effects and why? (list all rows from PerformanceTypes and Performers and look at the values)

 

9.     a) Write the SQL statements to insert 3 rows into the Arenas table with ids 1, 2, and respectively 3 and whatever values you want for the other columns. 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, and whatever values you want for the other columns.

 

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’ (or ‘dance performer’ whichever is appropriate for your case) 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 (if you did not give a name to the foreign key constraint when you created it, use MySQL Workbench to find out the name given by the database management system to the constraint (in the object browser, expand the table Concerts, then look at Foreign Keys))

 

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

 

c) Find all performer 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 executing 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. If needed, 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. 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 Monday February 10, 2014 at 2359) and paper copies (due before start of lab on February 11, 2014):

 

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.