· 7 years ago · Dec 18, 2018, 11:26 AM
1 -- DDL - Data Definition Language - Hur lagret ska se ut; CREATE, DROP...
2DROP TABLE IF EXISTS dig;
3DROP TRIGGER IF EXISTS delete_employee;
4DROP TABLE IF EXISTS former_staff;
5DROP TABLE IF EXISTS dig_location;
6DROP TABLE IF EXISTS artifact;
7DROP TABLE IF EXISTS slide;
8DROP TABLE IF EXISTS litterature;
9DROP TABLE IF EXISTS employee;
10DROP TABLE IF EXISTS student;
11DROP VIEW IF EXISTS lent_books_to_employees;
12DROP VIEW IF EXISTS digs_locations;
13DROP VIEW IF EXISTS lent_books_to_students;
14
15
16CREATE TABLE employee ( -- EMPLOYEE
17 PRIMARY KEY (employee_id),
18 employee_id SMALLINT,
19 first_name VARCHAR(25) NOT NULL,
20 last_name VARCHAR(25) NOT NULL,
21 e_role VARCHAR(25) NOT NULL
22 );
23
24CREATE TABLE student ( --STUDENT
25 PRIMARY KEY (student_id),
26 student_id SMALLINT,
27 first_name VARCHAR(25) NOT NULL,
28 last_name VARCHAR(25) NOT NULL
29 );
30
31CREATE TABLE litterature ( -- LITTERATURE
32 PRIMARY KEY (litterature_no),
33 litterature_no SMALLINT,
34 title VARCHAR(30) NOT NULL,
35 author VARCHAR(30) NOT NULL,
36 student_id SMALLINT,
37 employee_id SMALLINT,
38 CONSTRAINT [Only one person can loan a book at once.]
39 CHECK( (student_id IS NOT NULL AND employee_id IS NULL) OR
40 (student_id IS NULL AND employee_id IS NOT NULL) OR
41 (student_id IS NULL AND employee_id IS NULL)
42 ),
43 FOREIGN KEY(student_id)
44 REFERENCES student(student_id),
45 FOREIGN KEY(employee_id)
46 REFERENCES employee(employee_id)
47 ON DELETE CASCADE
48
49 );
50
51CREATE TABLE slide (
52 PRIMARY KEY (slide_no),
53 slide_no SMALLINT,
54 slide_description VARCHAR(50) NOT NULL,
55 loan_date DATE,
56 employee_id SMALLINT,
57 shelf_no CHAR(5),
58 CONSTRAINT [If a slide is not loaned out then it has to have a shelf-number.]
59 CHECK( (employee_id IS NULL AND shelf_no IS NOT NULL) OR
60 (employee_id IS NOT NULL AND shelf_no IS NULL)
61 ),
62 FOREIGN KEY(employee_id)
63 REFERENCES employee(employee_id)
64 ON DELETE CASCADE
65
66 );
67
68CREATE TABLE dig_location (
69 PRIMARY KEY (dig_location_no),
70 dig_location_no SMALLINT,
71 location_dig VARCHAR(30) NOT NULL
72 );
73
74
75CREATE TABLE dig (
76 PRIMARY KEY (dig_no),
77 dig_no SMALLINT,
78 dig_date DATE NOT NULL,
79 employee_id SMALLINT,
80 student_id SMALLINT,
81 dig_location_no SMALLINT,
82 CONSTRAINT [A researcher has to take part in a dig and a student can not be in a dig alone.]
83 CHECK( (employee_id IS NOT NULL AND student_id IS NULL) OR
84 (employee_id IS NOT NULL AND student_id IS NOT NULL)
85 ),
86 FOREIGN KEY (employee_id)
87 REFERENCES employee(employee_id),
88 FOREIGN KEY (student_id)
89 REFERENCES student(student_id),
90 FOREIGN KEY (dig_location_no)
91 REFERENCES dig_location(dig_location_no)
92 ON DELETE CASCADE
93
94 );
95
96
97
98CREATE TABLE artifact (
99 PRIMARY KEY (artifact_no),
100 artifact_no SMALLINT,
101 shelf_no VARCHAR(4),
102 found_date DATE NOT NULL,
103 depth VARCHAR(20),
104 employee_id SMALLINT,
105 CONSTRAINT [If an artifact is not loaned out then it has to have a shelf-number.]
106 CHECK( (employee_id IS NULL AND shelf_no IS NOT NULL) OR
107 (employee_id IS NOT NULL AND shelf_no IS NULL)
108 ),
109 FOREIGN KEY(employee_id)
110 REFERENCES employee(employee_id)
111 ON DELETE CASCADE
112
113
114 );
115
116
117
118INSERT INTO employee (employee_id, first_name, last_name, e_role)
119VALUES ('1', 'Torsten', 'Bengtsson', 'Researcher'),
120 ('2', 'Jimmy', 'Larsson', 'Janitor'),
121 ('3', 'Tiger', 'Skog', 'Researcher'),
122 ('4', 'Carl', 'Sone','Researcher'),
123 ('5', 'Moses', 'Mos', 'Assistant'),
124 ('6', 'Kim', 'Källström', 'Master Digger'),
125 ('7', 'Anders', 'Andersson', 'Researcher'),
126 ('8', 'Hasse', 'Ravelli', 'Researcher'),
127 ('9', 'Tomas', 'Tomsson', 'Researcher');
128
129INSERT INTO student (student_id, first_name, last_name)
130VALUES ('5', 'Mats', 'Olsson'),
131 ('6', 'Maj', 'Snok'),
132 ('7', 'Lego', 'Larsson'),
133 ('8', 'Kanye', 'Bach');
134
135INSERT INTO litterature (litterature_no, title, author, student_id, employee_id)
136VALUES ('10', 'Mazarin', 'George Orwell', null, '1'),
137 ('11', 'Asaklitt', 'Active Life', null, null),
138 ('12', 'Termosmugg', 'ICA CE', null, null),
139 ('13', 'Jag är fotboll', 'Zlatan', '5', null),
140 ('14', 'Lazarus', 'Lars Kepler', null, null),
141 ('15', 'Mamma Mu låtsas', 'Sven Nordqvist', '6', null),
142 ('16','Sju dagar med familjen', 'Francesca Hornak', null, null),
143 ('17', 'Jag kommer hem till jul', 'Joanna Bolouri', null, null),
144 ('18', 'Min historia', 'Michelle Obama', null, '3'),
145 ('19', '1793', 'Niklas Natt', null, null),
146 ('20', 'Ormen i Essex', 'Sarah Perry', null, '2'),
147 ('21', 'En annan Alice', 'Liane Moriarty', null, null),
148 ('22', 'Bränn alla mina brev', 'Alex Schulman', null, '1'),
149 ('23', 'Rymlingarna', 'Ulf Stark', null, null),
150 ('24', 'Min mammas hemlighet', 'Nikola Scott', '7', null),
151 ('25', 'Den frusna trädgården', 'Kristin Hannah', null, null),
152 ('26', 'Himmelskriket', 'Mons Kallentoft', null, null),
153 ('27', 'PÃ¥ hal is', 'Jeff Kinney', null, null),
154 ('28', 'Silvermysteriet', 'Martin Widmark', '8', null),
155 ('29', 'Sagor från Mumindalen', 'Tove Jansson', null, '4'),
156 ('30', 'Alfons hemliga världar', 'Gunilla Bergström', null, null),
157 ('31', 'Spöksystrar', 'Mårten Melin', null, null),
158 ('32', 'Mumiens gåta', 'Kristina Ohlsson', null, null),
159 ('33', 'Mareld', 'Camilla Sten', null, null),
160 ('34', 'Pappa Polis', 'Laura Trenter', null, null);
161
162INSERT INTO slide(slide_no, slide_description, loan_date, employee_id, shelf_no)
163VALUES ('35', 'lorem ipsum', '05-jan-1970', null, '00001'),
164 ('36', 'lorem ipsum', '05-jan-1970', null, '00002'),
165 ('37', 'lorem ipsum', '05-jan-1970', '1', null),
166 ('38', 'lorem ipsum', '05-jan-1970', '1', null),
167 ('39', 'lorem ipsum', '05-jan-1970', '3', null),
168 ('40', 'lorem ipsum', '05-jan-1970', '2', null),
169 ('41', 'lorem ipsum', '12-aug-1989', null, '00007'),
170 ('42', 'lorem ipsum', '12-aug-1989', null, '00008'),
171 ('43', 'lorem ipsum', '12-aug-1989', null, '00009'),
172 ('44', 'lorem ipsum', '12-aug-1989', '4', null),
173 ('45', 'lorem ipsum', '12-aug-1989', '4', null),
174 ('46', 'lorem ipsum', null, null, '00012'),
175 ('47', 'lorem ipsum', null, null, '00013'),
176 ('48', 'lorem ipsum', null, null, '00014'),
177 ('49', 'lorem ipsum', null, null, '00015'),
178 ('50', 'lorem ipsum', null, null, '00016');
179
180
181
182INSERT INTO artifact (artifact_no, shelf_no, found_date, depth, employee_id)
183VALUES ('51', null, '25-jan-1979', '200 cm', '2'),
184 ('52', null, '25-jan-1979', '200 cm', '2'),
185 ('53', '69B', '25-jan-1979', '200 cm', null),
186 ('50', null, '25-jan-1979', '200 cm', '2'),
187 ('54', null, '25-jan-1979', '200 cm', '2'),
188 ('55', '69B', '25-jan-1979', '200 cm', null),
189 ('56', '69B', '25-jan-1979', '200 cm', null),
190 ('57', null, '25-jan-1979', '200 cm', '2'),
191 ('58', null, '25-jan-1979', '200 cm', '2'),
192 ('59', '45A', '12-aug-1989', '105 cm', null),
193 ('60', '45A', '12-aug-1989', '105 cm', null),
194 ('61', '45A', '12-aug-1989', '105 cm', null),
195 ('62', '45A', '12-aug-1989', '105 cm', null),
196 ('63', '45A', '12-aug-1989', '105 cm', null),
197 ('64', '45A', '12-aug-1989', '105 cm', null),
198 ('65', '45A', '12-aug-1989', '105 cm', null),
199 ('66', '45A', '12-aug-1989', '105 cm', null),
200 ('67', '45A', '12-aug-1989', '105 cm', null),
201 ('68', '45A', '12-aug-1989', '105 cm', null),
202 ('69', '45A', '12-aug-1989', '105 cm', null),
203 ('70', '12F', '03-mar-1991', '265 cm', null),
204 ('71', '12F', '03-mar-1991', '265 cm', null),
205 ('72', '12F', '03-mar-1991', '265 cm', null),
206 ('73', '12F', '03-mar-1991', '265 cm', null),
207 ('74', '12F', '03-mar-1991', '265 cm', null),
208 ('75', '12F', '03-mar-1991', '265 cm', null),
209 ('76', '12F', '03-mar-1991', '265 cm', null),
210 ('77', '12F', '03-mar-1991', '265 cm', null),
211 ('78', null, '20-may-1990', '140 cm', '1'),
212 ('79', null, '20-may-1990', '140 cm', '1'),
213 ('80', null, '20-may-1990', '140 cm', '1'),
214 ('81', null, '20-may-1990', '140 cm', '1');
215
216INSERT INTO dig_location (dig_location_no, location_dig)
217VALUES ('82', 'Linköping'),
218 ('83', 'Norrköping'),
219 ('84', 'Mjölby'),
220 ('85', 'Motala'),
221 ('86', 'Boxholm'),
222 ('87', 'Grebo');
223
224
225INSERT INTO dig(dig_no, dig_date, employee_id, student_id, dig_location_no)
226VALUES ('88', '03-may-2010', '1', null, '82'),
227 ('89', '25-jun-2011', '3', null, '83'),
228 ('90', '17-feb-2012', '2', '7', '84'),
229 ('91', '12-mar-2012', '4', '8', '85'),
230 ('92', '12-mar-2012', '1', '6', '86'),
231 ('93', '12-mar-2011', '1', '6', '82'),
232 ('94', '12-mar-2011', '1', '6', '82'),
233 ('95', '12-mar-2011', '2', '5', '82'),
234 ('96', '23-aug-2014', '3', '8', '87');
235/*
236 SELECT * FROM litterature;
237 SELECT * FROM employee;
238 SELECT * FROM student;
239 SELECT * FROM slide;
240 SELECT * FROM artifact;
241 SELECT * FROM dig;
242*/
243SELECT * FROM artifact;
244SELECT * FROM employee;
245
246
247-- Views
248-- Visa lånade böcker för employees
249
250GO
251CREATE VIEW lent_books_to_employees AS
252 SELECT l.employee_id AS 'Employee ID' ,
253 l.litterature_no AS 'Book number',
254 l.title AS 'Book title',
255 e.first_name AS 'First name employee',
256 e.last_name AS 'Last name employee',
257 e.e_role AS 'Employee role'
258 FROM litterature AS l
259 INNER JOIN employee AS e
260 ON e.employee_id=l.employee_id
261
262GO
263
264GO
265CREATE VIEW lent_books_to_students AS
266 SELECT l.student_id AS 'Employee ID' ,
267 l.litterature_no AS 'Book number',
268 l.title AS 'Book title',
269 s.first_name AS 'First name student',
270 s.last_name AS 'Last name student'
271 FROM litterature AS l
272 INNER JOIN student AS s
273 ON s.student_id=l.student_id
274
275GO
276
277GO
278CREATE VIEW digs_locations AS
279 SELECT d.dig_date AS 'Date of dig',
280 d.dig_no AS 'Dig number',
281 dl.location_dig AS 'Location of dig'
282 FROM dig AS d
283 INNER JOIN dig_location AS dl
284 ON d.dig_location_no=dl.dig_location_no
285GO
286
287
288
289-- Trigger
290-- If you want to update an employee's role, you will receive a nice message
291CREATE TRIGGER updating_employee
292 ON employee
293 AFTER UPDATE
294 AS BEGIN
295 IF UPDATE(e_role)
296 print 'Your employee`s title has changed, good for that person'
297 IF UPDATE(last_name)
298 print 'Your employee has gotten married or divorced, good or bad'
299 END;
300 GO
301
302-- Queries
303
304SELECT employee_id, first_name, last_name
305 FROM employee AS A
306 ORDER BY last_name;
307
308 SELECT litterature_no, author, title, litterature.employee_id, first_name, last_name
309 FROM litterature
310 INNER JOIN employee
311 ON litterature.employee_id=employee.employee_id
312 WHERE litterature.employee_id IS NOT NULL AND student_id IS NULL
313 ORDER BY litterature_no;
314
315
316
317
318 /*
319SELECT dig_no, location_dig, employee.first_name AS 'First name employee', employee.last_name AS 'Last name employee', student.first_name AS 'First name student', student.last_name AS 'Last name student'
320 FROM dig
321 INNER JOIN dig_location
322 ON dig.dig_location_no=dig_location.dig_location_no
323 INNER JOIN employee
324 ON dig.employee_id=employee.employee_id
325 INNER JOIN student
326 ON dig.student_id=student.student_id AND student.student_id IS NOT NULL
327 WHERE location_dig LIKE 'L%' --AND student_id IS NOT NULL OR student_id IS NULL
328 ORDER BY dig_no;
329
330SELECT artifact_no, found_date, dig.dig_no, dig.dig_date, dig.employee_id
331 FROM artifact
332 INNER JOIN employee
333 ON artifact.employee_id=employee.employee_id
334 INNER JOIN dig
335 ON dig.employee_id=employee.employee_id
336 WHERE dig.dig_date BETWEEN '2010-01-01' AND '2012-01-01'
337 ORDER BY dig.dig_date;
338
339
340SELECT s.student_id AS 'Student ID', s.first_name AS 'First name student',
341 s.last_name 'Last name Student', COUNT(d.student_id) AS 'Amount of digs partaken in'
342 FROM dig AS d
343 INNER JOIN student AS s
344 ON d.student_id=s.student_id
345 WHERE d.student_id IS NOT NULL
346 GROUP BY d.student_id, s.student_id, s.first_name, s.last_name;
347 */