· 8 years ago · Apr 04, 2018, 12:16 PM
1/* Themepark.SQL */
2/* Introduction to SQL */
3/* Script file for MySQL DBMS */
4/* This script file creates the following tables: */
5/* THEMEPARK, EMPLOYEE, TICKET, ATTRACTION, HOURS */
6/* and loads the default data rows */
7CREATE DATABASE IF NOT EXISTS 0408982209_themepark;
8USE 0408982209_themepark;
9
10DROP TABLE IF EXISTS SALES_LINE;
11DROP TABLE IF EXISTS SALES;
12DROP TABLE IF EXISTS HOURS;
13DROP TABLE IF EXISTS ATTRACTION;
14DROP TABLE IF EXISTS TICKET;
15DROP TABLE IF EXISTS EMPLOYEE;
16DROP TABLE IF EXISTS THEMEPARK;
17
18CREATE TABLE THEMEPARK (
19PARK_CODE VARCHAR(10) PRIMARY KEY,
20PARK_NAME VARCHAR(35) NOT NULL,
21PARK_CITY VARCHAR(50) NOT NULL,
22PARK_COUNTRY CHAR(2) NOT NULL);
23
24CREATE TABLE EMPLOYEE (
25EMP_NUM NUMERIC(4) PRIMARY KEY,
26EMP_TITLE VARCHAR(4),
27EMP_LNAME VARCHAR(15) NOT NULL,
28EMP_FNAME VARCHAR(15) NOT NULL,
29EMP_DOB DATE NOT NULL,
30EMP_HIRE_DATE DATE,
31EMP_AREA_CODE VARCHAR(4) NOT NULL,
32EMP_PHONE VARCHAR(12) NOT NULL,
33PARK_CODE VARCHAR(10),
34INDEX (PARK_CODE),
35CONSTRAINT FK_EMP_PARK FOREIGN KEY(PARK_CODE) REFERENCES THEMEPARK(PARK_CODE));
36
37CREATE TABLE TICKET (
38TICKET_NO NUMERIC(10) PRIMARY KEY,
39TICKET_PRICE NUMERIC(4,2) DEFAULT 00.00 NOT NULL,
40TICKET_TYPE VARCHAR(10),
41PARK_CODE VARCHAR(10),
42INDEX (PARK_CODE),
43CONSTRAINT FK_TICKET_PARK FOREIGN KEY(PARK_CODE) REFERENCES THEMEPARK(PARK_CODE));
44
45CREATE TABLE ATTRACTION (
46ATTRACT_NO NUMERIC(10) PRIMARY KEY,
47ATTRACT_NAME VARCHAR(35),
48ATTRACT_AGE NUMERIC(3) DEFAULT 0 NOT NULL,
49ATTRACT_CAPACITY NUMERIC(3) NOT NULL,
50PARK_CODE VARCHAR(10),
51INDEX (PARK_CODE),
52CONSTRAINT FK_ATTRACT_PARK FOREIGN KEY(PARK_CODE) REFERENCES THEMEPARK(PARK_CODE));
53
54CREATE TABLE HOURS (
55EMP_NUM NUMERIC(4),
56ATTRACT_NO NUMERIC(10),
57HOURS_PER_ATTRACT NUMERIC(2) NOT NULL,
58HOUR_RATE NUMERIC(4,2) NOT NULL,
59DATE_WORKED DATE NOT NULL,
60INDEX (EMP_NUM),
61INDEX (ATTRACT_NO),
62CONSTRAINT PK_HOURS PRIMARY KEY(EMP_NUM, ATTRACT_NO, DATE_WORKED),
63CONSTRAINT FK_HOURS_EMP FOREIGN KEY (EMP_NUM) REFERENCES EMPLOYEE(EMP_NUM),
64CONSTRAINT FK_HOURS_ATTRACT FOREIGN KEY (ATTRACT_NO) REFERENCES ATTRACTION(ATTRACT_NO));
65
66
67CREATE TABLE SALES (
68TRANSACTION_NO NUMERIC PRIMARY KEY,
69PARK_CODE VARCHAR(10),
70SALE_DATE DATE NOT NULL,
71INDEX (PARK_CODE),
72CONSTRAINT FK_SALES_PARK FOREIGN KEY(PARK_CODE) REFERENCES THEMEPARK(PARK_CODE));
73
74
75CREATE TABLE SALES_LINE (
76TRANSACTION_NO NUMERIC,
77LINE_NO NUMERIC(2,0) NOT NULL,
78TICKET_NO NUMERIC(10) NOT NULL,
79LINE_QTY NUMERIC(4) DEFAULT 0 NOT NULL,
80LINE_PRICE NUMERIC(9,2) DEFAULT 0.00 NOT NULL,
81INDEX (TRANSACTION_NO),
82INDEX (TICKET_NO),
83CONSTRAINT PK_SALES_LINE PRIMARY KEY (TRANSACTION_NO,LINE_NO),
84CONSTRAINT FK_SALES_LINE_SALES FOREIGN KEY (TRANSACTION_NO) REFERENCES SALES(TRANSACTION_NO) ON DELETE CASCADE,
85CONSTRAINT FK_SALES_LINE_TICKET FOREIGN KEY (TICKET_NO) REFERENCES TICKET(TICKET_NO));
86
87/* CREATING AN INDEX ON EMP_LNAME IN THE EMPLOYEE TABLE */
88
89CREATE INDEX EMP_LNAME_INDEX ON EMPLOYEE(EMP_LNAME(8));
90
91
92
93
94/* Loading data rows */
95
96
97/* ThemePark rows */
98INSERT INTO THEMEPARK VALUES ('FR1001','FairyLand','PARIS','FR');
99INSERT INTO THEMEPARK VALUES ('NL1202','Efling','NOORD','NL');
100INSERT INTO THEMEPARK VALUES ('SP4533','AdventurePort','BARCELONA','SP');
101INSERT INTO THEMEPARK VALUES ('SW2323','Labyrinthe','LAUSANNE','SW');
102INSERT INTO THEMEPARK VALUES ('UK2622','MiniLand','WINDSOR','UK');
103INSERT INTO THEMEPARK VALUES ('UK3452','PleasureLand','STOKE','UK');
104INSERT INTO THEMEPARK VALUES ('ZA1342','GoldTown','JOHANNESBURG','ZA');
105
106/* Employee rows */
107INSERT INTO EMPLOYEE VALUES (100,'Ms','Calderdale','Emma','1972-06-15','1992-03-15','0181','324-9134','FR1001');
108INSERT INTO EMPLOYEE VALUES (101,'Ms','Ricardo','Marshel','1978-03-19','1996-04-25','0181','324-4472','UK3452');
109INSERT INTO EMPLOYEE VALUES (102,'Mr','Arshad','Arif','1969-11-14','1990-12-20','7253','675-8993','FR1001');
110INSERT INTO EMPLOYEE VALUES (103,'Ms','Roberts','Anne','1974-10-16','1994-08-16','0181','898-3456','UK3452');
111INSERT INTO EMPLOYEE VALUES (104,'Mr','Denver','Enrica','1980-11-08','2001-10-20','7253','504-4434','ZA1342');
112INSERT INTO EMPLOYEE VALUES (105,'Ms','Namowa','Mirrelle','1990-03-14','2006-11-08','0181','890-3243','FR1001');
113INSERT INTO EMPLOYEE VALUES (106,'Mrs','Smith','Gemma','1968-02-12','1989-01-05','0181','324-7845','ZA1342');
114
115
116/* Ticket rows */
117INSERT INTO TICKET VALUES (11001,24.99,'Adult', 'SP4533');
118INSERT INTO TICKET VALUES (11002,14.99,'Child', 'SP4533');
119INSERT INTO TICKET VALUES (11003,10.99,'Senior','SP4533');
120INSERT INTO TICKET VALUES (13001,18.99,'Child','FR1001');
121INSERT INTO TICKET VALUES (13002,34.99,'Adult','FR1001');
122INSERT INTO TICKET VALUES (13003,20.99,'Senior','FR1001');
123INSERT INTO TICKET VALUES (67832,18.56,'Child','ZA1342');
124INSERT INTO TICKET VALUES (67833,28.67,'Adult','ZA1342');
125INSERT INTO TICKET VALUES (67855,12.12,'Senior','ZA1342');
126INSERT INTO TICKET VALUES (88567,22.50,'Child','UK3452');
127INSERT INTO TICKET VALUES (88568,42.10,'Adult','UK3452');
128INSERT INTO TICKET VALUES (89720,10.99,'Senior','UK3452');
129
130/* Attraction rows */
131
132INSERT INTO ATTRACTION VALUES (10034,'ThunderCoaster',11,34,'FR1001');
133INSERT INTO ATTRACTION VALUES (10056,'SpinningTeacups',4,62,'FR1001');
134INSERT INTO ATTRACTION VALUES (10067,'FlightToStars',11,24,'FR1001');
135INSERT INTO ATTRACTION VALUES (10078,'Ant-Trap',23,30,'FR1001');
136INSERT INTO ATTRACTION VALUES (10098,'Carnival',3,120,'FR1001');
137INSERT INTO ATTRACTION VALUES (20056,'3D-Lego_Show',3,200,'UK3452');
138INSERT INTO ATTRACTION VALUES (30011,'BlackHole2',12,34,'UK3452');
139INSERT INTO ATTRACTION VALUES (30012,'Pirates',10,42,'UK3452');
140INSERT INTO ATTRACTION VALUES (30044,'UnderSeaWord',4,80,'UK3452');
141INSERT INTO ATTRACTION VALUES (98764,'GoldRush',5,80,'ZA1342');
142/* Attraction with no name */
143INSERT INTO ATTRACTION VALUES (10082,NULL,10,40,'ZA1342');
144
145
146/* hours rows */
147
148INSERT INTO HOURS VALUES (100,10034,6,6.5,'2007-05-18');
149INSERT INTO HOURS VALUES (100,10034,6,6.5,'2007-05-20');
150INSERT INTO HOURS VALUES (101,10034,6,6.5,'2007-05-18');
151INSERT INTO HOURS VALUES (102,30012,3,5.99,'2007-05-23');
152INSERT INTO HOURS VALUES (102,30044,6,5.99,'2007-05-21');
153INSERT INTO HOURS VALUES (102,30044,3,5.99,'2007-05-22');
154INSERT INTO HOURS VALUES (104,30011,6,7.2,'2007-05-21');
155INSERT INTO HOURS VALUES (104,30012,6,7.2,'2007-05-22');
156INSERT INTO HOURS VALUES (105,10078,3,8.5,'2007-05-18');
157INSERT INTO HOURS VALUES (105,10098,3,8.5,'2007-05-18');
158INSERT INTO HOURS VALUES (105,10098,6,8.5,'2007-05-19');
159
160
161/* SALES rows */
162
163INSERT INTO SALES VALUES (12781,'FR1001','2007-05-18');
164INSERT INTO SALES VALUES (12782,'FR1001','2007-05-18');
165INSERT INTO SALES VALUES (12783,'FR1001','2007-05-18');
166INSERT INTO SALES VALUES (12784,'FR1001','2007-05-18');
167INSERT INTO SALES VALUES (12785,'FR1001','2007-05-18');
168INSERT INTO SALES VALUES (12786,'FR1001','2007-05-18');
169INSERT INTO SALES VALUES (34534,'UK3452','2007-05-18');
170INSERT INTO SALES VALUES (34535,'UK3452','2007-05-18');
171INSERT INTO SALES VALUES (34536,'UK3452','2007-05-18');
172INSERT INTO SALES VALUES (34537,'UK3452','2007-05-18');
173INSERT INTO SALES VALUES (34538,'UK3452','2007-05-18');
174INSERT INTO SALES VALUES (34539,'UK3452','2007-05-18');
175INSERT INTO SALES VALUES (34540,'UK3452','2007-05-18');
176INSERT INTO SALES VALUES (34541,'UK3452','2007-05-18');
177INSERT INTO SALES VALUES (67589,'ZA1342','2007-05-18');
178INSERT INTO SALES VALUES (67590,'ZA1342','2007-05-18');
179INSERT INTO SALES VALUES (67591,'ZA1342','2007-05-18');
180INSERT INTO SALES VALUES (67592,'ZA1342','2007-05-18');
181INSERT INTO SALES VALUES (67593,'ZA1342','2007-05-18');
182
183/* SALES_LINE rows */
184
185
186INSERT INTO SALES_LINE VALUES (12781,1,13002,2,69.98);
187INSERT INTO SALES_LINE VALUES (12781,2,13001,1,14.99);
188INSERT INTO SALES_LINE VALUES (12782,1,13002,2,69.98);
189INSERT INTO SALES_LINE VALUES (12783,1,13003,2,41.98);
190INSERT INTO SALES_LINE VALUES (12784,2,13001,1,14.99);
191INSERT INTO SALES_LINE VALUES (12785,1,13001,1,14.99);
192INSERT INTO SALES_LINE VALUES (12785,2,13002,1,34.99);
193INSERT INTO SALES_LINE VALUES (12785,3,13002,4,139.96);
194INSERT INTO SALES_LINE VALUES (34534,1,88568,4,168.40);
195INSERT INTO SALES_LINE VALUES (34534,2,88567,1,22.50);
196INSERT INTO SALES_LINE VALUES (34534,3,89720,2,21.98);
197INSERT INTO SALES_LINE VALUES (34535,1,88568,2,84.20);
198INSERT INTO SALES_LINE VALUES (34536,1,89720,2,21.98);
199INSERT INTO SALES_LINE VALUES (34537,1,88568,2,84.20);
200INSERT INTO SALES_LINE VALUES (34537,2,88567,1,22.50);
201INSERT INTO SALES_LINE VALUES (34538,1,89720,2,21.98);
202INSERT INTO SALES_LINE VALUES (34539,1,89720,2,21.98);
203INSERT INTO SALES_LINE VALUES (34539,2,88568,2,84.20);
204INSERT INTO SALES_LINE VALUES (34540,1,88568,4,168.40);
205INSERT INTO SALES_LINE VALUES (34540,2,88567,1,22.50);
206INSERT INTO SALES_LINE VALUES (34540,3,89720,2,21.98);
207INSERT INTO SALES_LINE VALUES (34541,1,88568,2,84.20);
208INSERT INTO SALES_LINE VALUES (67589,1,67833,2,57.34);
209INSERT INTO SALES_LINE VALUES (67589,2,67832,2,37.12);
210INSERT INTO SALES_LINE VALUES (67590,1,67833,2,57.34);
211INSERT INTO SALES_LINE VALUES (67590,2,67832,2,37.12);
212INSERT INTO SALES_LINE VALUES (67591,1,67832,1,18.56);
213INSERT INTO SALES_LINE VALUES (67591,2,67855,1,12.12);
214INSERT INTO SALES_LINE VALUES (67592,1,67833,4,114.68);
215INSERT INTO SALES_LINE VALUES (67593,1,67833,2,57.34);
216INSERT INTO SALES_LINE VALUES (67593,2,67832,2,37.12);
217
218commit;
219
220SELECT DISTINCT(DATE_FORMAT(SALE_DATE, '%W-%D of %M Mav %y')) AS sale_date FROM SALES;
221SELECT DAYOFMONTH(EMP_DOB) AS 'Day', MONTH(EMP_DOB) AS 'Month', YEAR(EMP_DOB) AS 'Year' FROM EMPLOYEE;
222SELECT * FROM EMPLOYEE WHERE MONTH(EMP_DOB) = 11;
223SELECT DATEDIFF('2018-12-25', '2018-01-01');
224SELECT DATEDIFF('2018-12-25', current_date()) AS 'Days until Christmas!';
225SELECT DATEDIFF('2018-08-04', current_date()) AS 'Days until I\'m 20!';
226SELECT EMP_LNAME, EMP_FNAME, DATE_FORMAT(EMP_HIRE_DATE, '%W-%D of %M %y') AS 'Hire date', DATE_FORMAT(DATE_ADD(EMP_HIRE_DATE, INTERVAL 11 MONTH), '%W-%D of %M %y') AS 'First work appraisal' FROM EMPLOYEE;
227
228# Exercise
229
230# 1
231SELECT EMP_LNAME, EMP_FNAME, DATE_FORMAT(EMP_DOB, '%W-%D of %M %y') AS 'date of birth' FROM EMPLOYEE WHERE DAYOFMONTH(EMP_DOB) = 14;
232
233# 2
234SELECT EMP_LNAME, EMP_FNAME, DATE_FORMAT(EMP_HIRE_DATE, '%W-%D of %M %y') AS 'Hire date', DATE_FORMAT(DATE_ADD(EMP_HIRE_DATE, '11-25-2008'), '%W-%D of %M %y') AS 'First work appraisal' FROM EMPLOYEE;