· 8 years ago · Jun 15, 2018, 08:20 PM
1--
2-- Queries to create extra table to add concertHall
3--
4
5DROP TABLE IF EXISTS `ConcertSongs`;
6DROP TABLE IF EXISTS `Concert`;
7DROP TABLE IF EXISTS `ConcertHall`;
8
9-- create concert hall to prevent duplicate data
10CREATE TABLE ConcertHall(
11 ConcertHallID INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
12 NameOfHall VARCHAR(255) NOT NULL DEFAULT 'na',
13 Capacity INT NOT NULL
14);
15
16#2Write a statement to create the table Concert
17## CREATE concert table
18CREATE TABLE Concert(
19 ConcertID INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
20 BandID INT NOT NULL,
21 ConcertDate DATE NOT NULL,
22 ConcertHallID INT NOT NULL,
23 NumOfTicketSold INT NOT NULL,
24 FOREIGN KEY(BandID) REFERENCES Bands(BandID),
25 FOREIGN KEY(ConcertHallID) REFERENCES ConcertHall(ConcertHallID)
26);
27
28#1Write a statement to create the table ConcertSongs
29CREATE TABLE ConcertSongs(
30 ConcertSongID INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
31 SongID INT NOT NULL,
32 ConcertID INT NOT NULL,
33 FOREIGN KEY(concertID) REFERENCES Concert(ConcertID),
34 FOREIGN KEY(SongID) REFERENCES Songs(SongID)
35);
36
37
38--
39-- Use the new data base
40--
41
42#1. Write queries to insert at least two Concert records.
43INSERT INTO ConcertHall(NameOfHall,Capacity)
44VAlUES ('The Opera',12000),('Nha Hat Lon',20000),
45 ('The Mega Dome',12000),('The Gorge',32000);
46INSERT INTO Concert(BandID,ConcertDate,ConcertHallID,NumOfTicketSold)
47VAlUES (52,'2018-09-05',1,5),
48 (9,'2017-12-18',2,10),
49 (2,'2018-01-13',3,20),
50 (52,'2017-05-20',1,30);
51
52
53
54#2. Write queries to associate at least 3 songs with each of the two concerts
55INSERT INTO ConcertSongs(SongID,ConcertID)
56VALUES (1,1),(2,1),(3,1),(4,1),
57 (5,2),(6,3),(7,4),(4,4);
58
59
60#3. Write a select-join query to retrieve these new results and produce a playlist for each concert
61SELECT cs.ConcertID,cs.SongID,s.SongTitle FROM ConcertSongs cs
62JOIN Songs s ON cs.SongID=s.SongID
63GROUP BY ConcertID;
64
65#4. Modify the query to include the name of the band playing the concert. # If such a query is not
66# possible, explain why and sketch an alternative design in which in0
67# would be possible.
68SELECT c.BandID,c.ConcertDate,c.ConcertHallID,c.NumOfTicketSold,b.BandName
69FROM Concert c JOIN Bands b ON b.BandID=c.BandID;