SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE COACH( -- changed name to firstName for clarification firstName VARCHAR(30) NOT NULL, surname VARCHAR(30) NOT NULL, DoB DATE NOT NULL, -- default/replacement coach has an iD of 23470001 -- note that all idCoach start with a 2347_ _ _ _ idCoach INT(8) NOT NULL DEFAULT '2347001', phone INT(11) NOT NULL, -- salary in GBP dailySalary INTEGER NOT NULL, -- F = Female; M= Male; O = Others gender VARCHAR(1) NOT NULL, PRIMARY KEY(idCoach), UNIQUE (phone) ); ALTER TABLE COACH ADD CONSTRAINT GENDERCHECK CHECK(gender IN ('F','M','O')); ALTER TABLE COACH AUTO_INCREMENT = 23470001; CREATE TABLE CONTENDER( stageName VARCHAR(30) NOT NULL, -- changed type to contenderType for clarification -- only allow contender of types group and individual contenderType VARCHAR(10) NOT NULL, -- all idContender start with a 3382 _ _ _ _ idContender INT(8) NOT NULL AUTO_INCREMENT, coach INT(8), PRIMARY KEY(idContender), -- since unable to SET DEFAULT, SET NULL first, then manually change it with the default value of '01234567' FOREIGN KEY(coach) REFERENCES COACH(idCoach) ON DELETE SET NULL ON UPDATE CASCADE ); ALTER TABLE CONTENDER ADD CONSTRAINT TYPECHECK CHECK (contenderType IN ('Group','Individual')); ALTER TABLE CONTENDER AUTO_INCREMENT = 33820001; CREATE TABLE PARTICIPANT( -- changed name to firstName for clarification firstName VARCHAR(30) NOT NULL, surname VARCHAR(30) NOT NULL, DoB DATE NOT NULL, -- all idParticipant start with a 5566 _ _ _ _ idParticipant INT(8) NOT NULL AUTO_INCREMENT, phone INT(11) NOT NULL, -- salary in GBP dailySalary INTEGER NOT NULL, gender VARCHAR(1) NOT NULL, contender INT(8) NOT NULL, PRIMARY KEY(idParticipant), UNIQUE(phone), FOREIGN KEY(contender) REFERENCES CONTENDER(idContender) ON UPDATE CASCADE ); ALTER TABLE PARTICIPANT ADD CONSTRAINT GENDERCHECK_2 CHECK(gender IN ('F','M','O')); ALTER TABLE PARTICIPANT AUTO_INCREMENT = 55660001; CREATE TABLE TVSHOW( location VARCHAR(50) NOT NULL DEFAULT "Television studio", -- changed date to TVdate for clarification TVdate DATE NOT NULL, -- all idShow start with a 7788_ _ _ _ idShow INT(8) NOT NULL AUTO_INCREMENT, startTime TIME NOT NULL, -- make sure the end time is exactly two hours later than the startTime endTime TIME NOT NULL, PRIMARY KEY(idShow) ); -- ensures that the end time is two hours later than the startTime ALTER TABLE TVSHOW ADD CONSTRAINT TIMECHECK CHECK(endTime > startTime); ALTER TABLE TVSHOW AUTO_INCREMENT = 77880001; CREATE TABLE COACHINSHOW( coach INT(8) NOT NULL, -- showID replaces show in the database model -- to prevent conflicts with reesevred word, show. showID INT(8) NOT NULL, PRIMARY KEY(coach, showID), -- since if a coach decides to leave the program, their information must be deleted from the database. FOREIGN KEY(coach) REFERENCES COACH(idCoach) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY(showID) REFERENCES TVSHOW(idShow) ON UPDATE CASCADE ); CREATE TABLE CONTENDERINSHOW( contender INT(8) NOT NULL, -- showID replaces show in the database model -- to prevent conflicts with reesevred word, show. showid INT(8) NOT NULL, PRIMARY KEY(contender, showID), FOREIGN KEY(contender) REFERENCES CONTENDER(idContender) ON UPDATE CASCADE, FOREIGN KEY(showID) REFERENCES TVSHOW(idShow) ON UPDATE CASCADE ); -- britney spears/23470001 is not coaching anyone INSERT INTO COACH(firstName,surname,DoB,phone,dailySalary,gender) VALUES ('Britney','Spears', '1981-12-02', 01234523231, 620000, 'F'), ('Mariah','Carey','1969-03-21', 01234589301,650000,'F'), ('Ariana', 'Grande','1993-06-26',01382938429,530000, 'F'), ('Rihanna', 'Fenty','1988-02-20' ,01482730491, 600000, 'F'), ('Harry', 'Styles','1994-02-01' ,01908765628, 520000, 'M'); INSERT INTO CONTENDER(stageName,contenderType,coach) VALUES ('Glee','Group',23470002), ('Paramore','Group', 23470003), ('Victorious','Group',23470005), ('Miley Cyrus', 'Individual', 23470004), ('Ed Sheeran','Individual', 23470005), ('Zayn', 'Individual', 23470003), ('Sia','Individual', 23470002); INSERT INTO PARTICIPANT(firstName,surname, DoB,phone,dailySalary,gender,contender) values ('Lea',' Michele','1986-08-29',0126754232, 23000,'F', 33820001), ('Kevin',' Mchale','1988-06-14',01232109879, 23000,'M', 33820001), ('Amber',' Riley','1988-02-15',01235263879, 23000,'F', 33820001), ('Hayley',' Williams','1988-12-27',01232565656, 22500,'F', 33820002), ('Zac',' Farro','1990-06-04',01237654330, 21500,'M', 33820002), ('Victoria',' Justice','1993-02-19',0169234017, 32500,'F', 33820003), ('Leon',' Thomas','1993-08-01',01512370126, 30500,'M', 33820003), ('Miley','Cyrus','1992-11-23',01676182342,32000,'F',33820004), ('Ed',' Sheeran','1991-02-17',01899901113, 33500,'M', 33820005), ('Zayn','Malik','1993-01-12',01768745231,32100,'M',33820006), ('Sia','Furler','1975-12-18',01923456354,34000,'F',33820007); INSERT INTO TVSHOW(location,TVdate,startTime,endTime) values (DEFAULT,'2021-03-06','18:00:00','20:00:00'), (DEFAULT,'2021-03-07','17:00:00','19:00:00'), (DEFAULT,'2021-03-13','18:00:00','20:00:00'), (DEFAULT,'2021-03-14','17:00:00','19:00:00'), (DEFAULT,'2021-03-20','13:00:00','15:00:00'), (DEFAULT,'2021-03-21','17:00:00','19:00:00'), (DEFAULT,'2021-03-27','13:00:00','15:00:00'), (DEFAULT,'2021-03-28','17:00:00','19:00:00'), (DEFAULT,'2021-04-03','19:00:00','21:00:00'), (DEFAULT,'2021-04-04','17:00:00','19:00:00'), (DEFAULT,'2021-04-10','18:00:00','20:00:00'), (DEFAULT,'2021-04-11','17:00:00','19:00:00'), (DEFAULT,'2021-04-17','19:00:00','21:00:00'), (DEFAULT,'2021-04-18','17:00:00','19:00:00'), (DEFAULT,'2021-04-24','14:00:00','16:00:00'), (DEFAULT,'2021-04-25','17:00:00','19:00:00'); INSERT INTO COACHINSHOW(coach,showID) values (23470001,77880001), (23470002,77880001), (23470004,77880002), (23470005,77880002), (23470001,77880003), (23470003,77880003), (23470003,77880004), (23470004,77880004), (23470002,77880005), (23470005,77880005), (23470001,77880006), (23470004,77880006), (23470001,77880007), (23470002,77880007), (23470003,77880008), (23470005,77880008), (23470002,77880009), (23470005,77880009), (23470004,77880010), (23470005,77880010), (23470001,77880011), (23470005,77880011), (23470003,77880012), (23470004,77880012), (23470002,77880013), (23470004,77880013), (23470001,77880014), (23470003,77880014), (23470002,77880015), (23470003,77880015), (23470001,77880016), (23470005,77880016); INSERT INTO CONTENDERINSHOW(contender,showID) values (3382001,77880001), (3382002,77880001), (3382006,77880001), (3382003,77880002), (3382004,77880002), (3382005,77880002), (3382002,77880003), (3382006,77880003), (3382007,77880003), (3382002,77880004), (3382004,77880004), (3382006,77880004), (3382001,77880005), (3382003,77880005), (3382005,77880005), (3382002,77880006), (3382004,77880006), (3382007,77880006), (3382001,77880007), (3382005,77880007), (3382006,77880007), (3382002,77880008), (3382003,77880008), (3382004,77880008), (3382001,77880009), (3382003,77880009), (3382005,77880009), (3382003,77880010), (3382004,77880010), (3382006,77880010), (3382001,77880011), (3382005,77880011), (3382007,77880011), (3382002,77880012), (3382005,77880012), (3382006,77880012), (3382004,77880013), (3382006,77880013), (3382007,77880013), (3382001,77880014), (3382002,77880014), (3382005,77880014), (3382003,77880015), (3382006,77880015), (3382007,77880015), (3382001,77880016), (3382002,77880016), (3382004,77880016);