We will create a concerts database: create the following tables that will store

Question

We will create a concerts database: 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 RED 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. Allstate 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 .

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

2.Remove the performer ‘Matt Smith’ from the Performers table.

3.Write the SQL statement to remove performance type ‘singer’ from the PerformanceTypes table.

4.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.

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

6.Execute the statement above. Does the query succeed?

7.Write the SQL statements to insert 3 rows with ids 1, 2, and respectively 3 into the Arenas table.

Explanation / Answer

1) In Performer table have no coloum field : DateOfBirth
So can't need to update
But if have then execute this query:

UPDATE Performer
SET DateOfBirth = '1990-03-02'
WHERE perfomerID = 1;
2)
DELETE FROM Performer
WHERE FirstName='Matt' And LastName='Smith';
3)First delete PerformanceTypeName in Performer table then delete PerformanceTypeName from PerformanceTypes table.
// delete from Performer table first.
DELETE FROM Performer
WHERE PerformanceTypeName ='singer';
// delete from PerformanceTypes table
DELETE FROM PerformanceTypes
WHERE PerformanceTypeName ='singer';

4) If you delete  PerformanceTypeName in Performer table then it's ok , but PerformanceTypeName still there in PerformanceTypes table so you need to delete PerformanceTypes table also .
because PerformanceTypeName is forien key in Performer table
and parent table is PerformanceTypes.
5)If you want to updete PerformanceTypeName then first update PerformanceTypes table then update in Performer table.

// first update in PerformanceTypes table first.
UPDATE PerformanceTypes
SET PerformanceTypeName='dance performer'
WHERE PerformanceTypeName ='dancer';
// delete from Performes table
UPDATE Performes
SET PerformanceTypeName ='dance performer'
WHERE PerformanceTypeName ='dancer';

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.