SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE Movie ( MovieID char (4) NOT NULL, Title varchar (30) NOT NULL, Movie_Description varchar (255) NOT NULL, Release_Description Date, Rating decimal (3,2) DEFAULT 0, language varchar(7) NOT NULL DEFAULT 'English', Show_Times Time (5), isShowing BOOLEAN NOT NULL DEFAULT 'False', PRIMARY KEY (MovieID)); CREATE TABLE Customer ( CustomerID char (4) NOT NULL, Fname varchar (15) NOT NULL, Lname varchar (15) NOT NULL, Date_of_birth Date , Customer_Email varchar (50) NOT NULL, Phone_Number varchar (10) NOT NULL, PRIMARY KEY (CustomerID)); CREATE TABLE Service ( Service_Number char (4) NOT NULL, Service_Name varchar (25) NOT NULL CHECK (Service_Name IN ('Technical problem','refund issues') ), Service_Description varchar (255) NOT NULL, Complete BOOLEAN NOT NULL, CustomerID char(4) NOT NULL, PRIMARY KEY (Service_Number), FOREIGN KEY (CustomerID) REFERENCES Customer (CustomerID) ON DELETE CASCADE ON UPDATE CASCADE); CREATE TABLE Ticket ( TicketID char (4) NOT NULL, Movie_Date Date NOT NULL, Movie_Time Time (5) NOT NULL, Show_Hall varchar (10) NOT NULL, Seat_Number char (3) NOT NULL, Payment_Method varchar (15) NOT NULL, Price decimal (5,2) NOT NULL, Available BOOLEAN NOT NULL, VIP BOOLEAN NOT NULL, PRIMARY KEY (TicketID)); CREATE TABLE Book ( MovieID char (4) NOT NULL, TicketID char (4) NOT NULL, CustomerID char (4) NOT NULL, Booking_Time Time (5), PRIMARY KEY (MovieID, TicketID, CustomerID)); INSERT INTO Movie VALUES ( 'M001' , 'lion king' , 'Lion prince Simba and his father are targeted by his bitter uncle, who wants to ascend the throne himself.' ,'19-Jul-2019' ,8.5 , 'English' ,'06:10:00' , true ); INSERT INTO Movie VALUES('M002' , 'Parasite' ,'Greed and class discrimination threaten the newly formed symbiotic relationship between the wealthy Park family and the destitute Kim clan.' , '8-Nov-2019' , 8.6 ,'Korean' , '07:30:00' , true ) ; INSERT INTO Customer VALUES ('C001' , 'Lana' , 'alqahtani' , '22-Oct-1999' , 'Lana_12@gmail.com' , '0591245934'); INSERT INTO Customer VALUES('C002' , 'Mohammed' , 'Alhamlan' , '10-Jan-2002' , 'Mm_2002@gmail.com' , '0512589321' ) ; INSERT INTO Service VALUES('S001' , 'Technical problem' , 'A service provide help to any technical problem occurs during the process' , FALSE , 'C001'); INSERT INTO Service VALUES('S002' , 'refund issues' , 'A service helps the customer to get his/her refund' , TRUE , 'C002'); INSERT INTO Ticket VALUES ('T001' , '02-Feb-2020', '07:10:00' , 'Screen1' , 'B20' ,'CRIDET' , 60 , TRUE , FALSE ) ; INSERT INTO Ticket VALUES ('T002' , '09-Mar-2020', '08:10:00' , 'Screen2' , 'A01' ,'CASH' , 150 , TRUE , TRUE) ; INSERT INTO Book VALUES ('M001' , 'T001' , 'C002' , '09:10:00'); INSERT INTO Book VALUES ('M002' , 'T002' , 'C001' , '10:20:00'); Select c.Customer_Email From Customer c, Book b Where b.CustomerID = c.CustomerID And b.TicketID In (select TicketID From Ticket Where vip=true);