· 7 years ago · Dec 13, 2018, 09:38 AM
1-- DDL
2
3-- (1) DROPS
4-- (2) CREATE TABLES
5-- (3) INSERT VALUES
6-- (4) CREATE VIEWS
7-- (5) CREATE TRIGGERS/PROCEDURES
8DROP VIEW IF EXISTS books_loans;
9DROP VIEW IF EXISTS slide_loans;
10DROP VIEW IF EXISTS artefact_loans;
11DROP VIEW IF EXISTS books_in;
12
13DROP TABLE IF EXISTS dig_extension;
14DROP TABLE IF EXISTS slides;
15DROP TABLE IF EXISTS books;
16DROP TABLE IF EXISTS digs;
17DROP TABLE IF EXISTS artefacts;
18DROP TABLE IF EXISTS documents;
19DROP TABLE IF EXISTS persons;
20
21
22
23
24-- person (person_id, p_name)
25CREATE TABLE persons (
26 PRIMARY KEY(p_id),
27
28 p_id INT,
29 firstname VARCHAR(35),
30 lastname VARCHAR(35),
31);
32
33-- documents (document_id, title, published_year, d_location, author, person)
34CREATE TABLE documents (
35 PRIMARY KEY(d_id),
36
37 d_id INT,
38 title VARCHAR(30),
39 published_year INT,
40 d_location VARCHAR(30),
41 author VARCHAR(30),
42 person INT,
43
44 FOREIGN KEY (person)
45 REFERENCES persons(p_id)
46 );
47
48-- artefacts(a_id, sequential_artefact, coordinate, depth, date_found, description, location, date_out, date_back)
49
50
51
52CREATE TABLE artefacts(
53 PRIMARY KEY(a_id),
54
55 a_id AS CONCAT (dig_id, '-', sequential_artefact) PERSISTED NOT NULL,
56 sequential_artefact INT,
57 coordinate VARCHAR(30),
58 depth VARCHAR(30),
59 date_found DATE,
60 a_descritpion VARCHAR(50),
61 a_location VARCHAR(30),
62 date_out DATE,
63 date_back DATE,
64 person INT,
65 dig_id INT,
66
67 FOREIGN KEY (person)
68 REFERENCES persons(p_id)
69
70
71);
72-- SELECT CONCAT (dig_id, '-', sequential_artefact) FROM artefacts;
73-- digs( d_dig_site, d_dig_id, d_description, d_p_id)
74
75CREATE TABLE digs (
76 PRIMARY KEY (dig_id),
77
78 dig_site VARCHAR(100),
79 dig_id INT,
80 d_description VARCHAR(250),
81 p_id INT,
82
83 FOREIGN KEY (p_id)
84 REFERENCES persons(p_id),
85
86);
87
88CREATE TABLE books(
89 PRIMARY KEY (b_id),
90
91 b_id INT,
92 title VARCHAR(30),
93 published_year VARCHAR(10),
94 author VARCHAR(35),
95 date_out DATE,
96 b_location VARCHAR(50),
97 date_back DATE,
98 person INT,
99
100 FOREIGN KEY (person)
101 REFERENCES persons(p_id)
102);
103
104CREATE TABLE slides(
105 PRIMARY KEY (s_id),
106
107 s_id AS CONCAT (dig_id, '-', sequential_slide) PERSISTED NOT NULL,
108 sequential_slide INT,
109 s_subject VARCHAR(35),
110 s_description VARCHAR(40),
111 date_out DATE,
112 date_back DATE,
113 s_location VARCHAR(35),
114 person_id INT,
115 dig_id INT,
116
117
118 FOREIGN KEY (person_id)
119 REFERENCES persons(p_id),
120
121 FOREIGN KEY (dig_id)
122 REFERENCES digs(dig_id)
123
124);
125
126SELECT CONCAT (dig_id, '-', sequential_slide) FROM slides;
127
128
129SELECT COL_LENGTH ( 'artefacts', 'a_id')
130SELECT COL_LENGTH ( 'slides', 's_id')
131SELECT COL_LENGTH ( 'slides', 's_description')
132
133
134
135
136
137 INSERT INTO persons (p_id, firstname, lastname)
138 VALUES (21, 'Bengt', 'Andersson'),
139 (32, 'Anna', 'Nilsson'),
140 (33, 'Johan', 'Ahlberg'),
141 (97, 'Anders', 'Svensson'),
142 (12, 'Jennifer', 'Berg'),
143 (11, 'Emelie', 'Palner'),
144 (77, 'Elinor', 'Nyberg'),
145 (87, 'Hannes', 'Lindgren'),
146 (99, 'Jacob', 'Lycke'),
147 (94, 'Olivia', 'Wennborg'),
148 (55, 'Johan', 'Haggar'),
149 (67, 'Axel', 'Guditz'),
150 (61, 'Jonathan', 'Solberg'),
151 (81, 'Sara', 'Andersdotter'),
152 (85, 'Amanda', 'Silfversparre'),
153 (74, 'Niklas', 'Guldsked'),
154 (49, 'Sven', 'Laxnacke'),
155 (41, 'Calle', 'Hoffeinheim');
156
157
158 INSERT INTO documents (d_id, title, published_year, d_location, author, person)
159 VALUES (392, 'Life On Earth', 1992, 'Shelf: A', 'James Cameron', 41),
160 (3582, 'Book Of Life', 2002, 'Shelf: D', 'Niklas Berg', 85),
161 (4921, 'Artefacts 101', 1952, 'Shelf: B', 'Gunnar Martin', 94),
162 (353, 'Cosmos', 2016, 'Shelf: C', 'Joseph Bang', 77),
163 (981, 'Space', 2002, 'Shelf: G', 'Anna Smith', 49),
164 (111, 'Moon landing 1969', 1970, 'Shelf: C', 'Anna Smith', 12),
165 (455, 'Festerist 101', 1994, 'Shelf: A', 'Clarence Coleman', 97),
166 (12, 'History of Tour de France', 2005, 'Shelf: A', 'Phil Stevenson', 87),
167 (71, 'How to dig a tunnel', 2010, 'Shelf: A', 'Christopher O.Neil', 33),
168 (9746, 'Basejump', 2013, 'Shelf: B', 'Dustin Hoffman', 32),
169 (32221, 'History of dinosaurs', 1987, 'Shelf C', 'Michael Bay', 21),
170 (4942837, 'Formel 1', 2018, 'Shelf: D', 'Stan Lee', 55);
171
172
173 INSERT INTO books (b_id, title, published_year, author, date_out, date_back, b_location, person)
174 VALUES (123, 'FOR AZEROTH!', 2007,'Dillon McNillon', '2018-11-05 ','2018-12-05 ', 'Borrowed', 77),
175 (435, 'National Treasure', 2003, 'Nicolas Cage',NULL, NULL, 'Shelf:A22', NULL),
176 (563, 'WAOOW', 2001, 'Owen Wilson', NULL, NULL, 'Shelf:A22', NULL),
177 (152, 'Nonstop', 2018, 'Drake', '2018-10-12', '2018-11-12', 'Borrowed', 99),
178 (880, 'Bridget Jones dagbok', 2002, 'Yung Nylla', NULL, NULL, 'Shelf:B12', NULL),
179 (981, 'Life of Dillon', 2001, 'John Cleese', '2018-09-11', '2018-10-11', 'Borrowed', 67),
180 (654, 'Hejhej', 2005, 'J&J', '2018-08-23', '2018-09-23', 'Borrowed', 21);
181
182
183
184
185
186
187 INSERT INTO digs (dig_site, dig_id, d_description, p_id)
188 VALUES ('Chan-Chan', 12, 'Beach on the coast of the commune of MehuÃn in southern Chile.', 41),
189 ('Monte Verde', 43, 'Monte Verde is an archaeological site in southern Chile, located near Puerto Montt, Southern Chile', 21),
190 ('Fell Cave', 9, 'Cueva Fell is a natural cave and archaeological site in southern Patagonia.', 32),
191 ('Pali Aike', 4, 'The Pali-Aike National Park is a park located in the Magallanes Region of the Chilean Patagonia.', 99),
192 ('An Yang', 6, 'An yang is a prefecture-level city in Henan province, China.', 67),
193 ('Kykkos Monastery', 21, 'Kykkos Monastery which lies 20 km west of Pedoulas, is one of the wealthiest and best-known monasteries in Cyprus.', 85),
194 ('Notre Dame de tyre', 67, 'Notre Dame de Tyre or Our Lady of Tyre is a monastic church in Nicosia.', 41);
195
196
197 INSERT INTO artefacts (sequential_artefact, coordinate, depth, date_found, a_descritpion, a_location, date_out, date_back, person,dig_id)
198 VALUES (465, '30:40', '20 cm', '2017-05-23', 'Small round object', 'Borrowed', '2018-12-01', '2019-01-24', 55, 12),
199 (266, '30:45', '15 cm', '2017-05-23', 'Small round object', 'Borrowed', '2018-12-01', '2019-02-24', 67, 43),
200 (266, '30:35', '39 cm', '2017-05-23', 'Small round object', 'Borrowed', '2018-12-01', '2019-03-24', 81, 9),
201
202 (275, '32:42', '10 cm', '1997-05-29', 'Silver coin', 'Borrowed', '2018-12-01', '2019-01-30', 21,6),
203 (765, '32:42', '180 cm', '1997-05-12', 'Gold Coin', 'Borrowed', '2018-12-01', '2019-01-12', 41,6),
204 (865, '32:42', '20 cm', '1997-05-27', 'Chinese sword', 'Borrowed', '2018-12-01', '2019-01-22', 41,6),
205 (145, '32:32', '0.5 cm', '1997-01-12', 'Chinese golden knife', 'Borrowed', '2018-12-01', '2019-01-20', 77,6),
206 (132, '32:41', '200 cm', '1997-09-01', 'Chinese helmet', 'Borrowed', '2018-12-01', '2019-04-24', 41,6),
207 (166, '29:45', '234 cm', '1997-07-14', 'Chinese breast plate', 'Borrowed', '2018-12-01', '2019-05-24', 77,6),
208 (125, '27:43', '500 cm', '1997-06-14', 'Chinese shield', 'Borrowed', '2018-12-01', '2019-09-24', 41,6),
209
210 (151, '12:92:14', '37 cm', '1999-02-24', 'Wooden crucifix', 'In-House', NULL, NULL, NULL,21),
211 (118, '30:40:21', '94 cm', '1992-05-19', 'Golden ring', 'In-House', NULL, NULL, NULL, 21),
212 (119, '30:40:23', '122 cm', '1993-08-17', 'Monk cape', 'In-House', NULL, NULL, NULL,21),
213 (117, '30:40:55', '431 cm', '1992-01-12', 'Golden chain', 'In-House', NULL, NULL, NULL,21),
214 (110, '30:40:12', '12 cm', '1992-08-24', 'Candle holder', 'In-House', NULL, NULL, NULL,21),
215 (109, '30:40:98', '542 cm', '1992-02-02', 'Golden candle holder', 'In-House', NULL, NULL, NULL, 21),
216 (108, '30:40:24', '122 cm', '1993-02-23', 'Metal crucifix', 'In-House', NULL, NULL, NULL,21),
217 (102, '30:40:77', '32 cm', '1993-05-29', 'Wooden bowl', 'In-House', NULL, NULL, NULL,21),
218 (100, '30:40:98', '96 cm', '1992-06-28', 'black priest cape', 'In-House', NULL, NULL, NULL, 21),
219
220 (964, '98:34', '200 cm', '2015-02-23', 'wolf skeleton', 'Borrowed', '2016-04-01', '2019-01-10', 41,4),
221 (965, '95:42', '95 cm', '2015-03-24', 'Puma skeleton', 'Borrowed', '2016-04-01', '2019-01-10', 74,4),
222 (936, '93:43', '94 cm', '2015-03-22', 'Patagonian Hairy Armadillo', 'Borrowed', '2016-04-01', '2019-01-10', 74,4),
223
224 (462, '432:431', '0,1 cm', '1992-04-11', 'Old brick wall', 'Borrowed', '1992-12-01', '2017-12-24', 11,67),
225
226 (732, '84:42', '43 cm', '2013-05-21', 'Stuffed Lizard', 'In-House', NULL, NULL, NULL,43),
227 (757, '82:53', '53 cm', '2013-08-22', 'Snake skin', 'In-House', NULL, NULL, NULL,43),
228 (752, '81:95', '123 cm', '2017-08-19', 'Puma Skeleton', 'In-House', NULL, NULL, NULL, 43),
229
230
231 (343, '11:40', '13 cm', '1982-01-23', 'Fish skeleton', 'In-house',NULL, NULL, NULL, 9),
232 (365, '11:42', '11 cm', '1983-04-13', 'Big fish skeleton', 'In-House', NULL, NULL, NULL, 9),
233 (375, '11:53', '8 cm', '1982-06-19', 'Medium fish skeleton', 'In-House', NULL, NULL, NULL,9),
234 (392, '11:52', '1.5 cm', '1983-05-29', 'Random fish skeleton', 'In-House', NULL, NULL, NULL,9),
235 (326, '11:39', '5.5 cm', '1982-09-27', 'Old seaweed', 'Borrowed', '1997-05-29', '1997-09-29', NULL,9),
236
237 (753, '321:401', '00 cm', '1999-05-23', 'Rock statue', 'Borrowed', '2018-12-03', '2019-04-24', 21,12),
238 (725, '324:402', '45 cm', '1999-03-23', 'Wooden statue', 'Borrowed', '2018-12-03', '2019-04-24', 67,12),
239 (769, '381:471', '921 cm', '1999-06-28', 'Metal statue', 'Borrowed', '2018-12-03', '2019-04-24', 33,12),
240 (726, '339:412', '549 cm', '2000-03-12', 'Round random thing', 'Borrowed', '2018-12-03', '2019-04-24', 85,12),
241
242
243
244 (212, '282:52', '70 cm', '2017-02-22', 'Small round object', 'In-House', NULL, NULL, NULL,12),
245 (214, '284:42', '30 cm', '2017-05-23', 'Dinosaur bone', 'Borrowed', '2018-12-01', '2019-01-24', 41,12),
246 (285, '295:15', '65 cm', '2017-03-18', 'Skin bone', 'In-House', NULL, NULL, NULL,9),
247 (286, '251:12', '800 cm', '2015-05-23', 'Mammoth', 'In-House', NULL, NULL, NULL,12),
248 (203, '295:09', '300 cm', '2012-11-23', 'Tiger bone', 'Borrowed', '2018-10-01', '2019-02-24', 55,12),
249
250 (434, '302:140', '80 cm', '2002-02-06', 'Small round object', 'Borrowed', '2018-10-01', '2019-03-24', 55,6),
251 (491, '350:470', '470 cm', '2004-03-12', 'Big round object', 'In-House', NULL, NULL, NULL,4),
252 (461, '730:430', '205 cm', '2007-06-21', 'Small round object', 'Borrowed', '2018-12-01', '2019-12-24', 55,12),
253
254 (453, '230:240', '240 cm', '2011-02-23', 'Small round object', 'Borrowed', '2018-12-01', '2019-01-24', 55,12),
255 (361, '530:340', '120 cm', '2013-01-23', 'Small round object', 'Borrowed', '2018-12-01', '2017-01-14', 55,12);
256
257
258
259 INSERT INTO slides(sequential_slide, s_subject, s_description, s_location, date_out, date_back, person_id,dig_id)
260 VALUES (721, 'Animal tooth', 'Pictures of tiger tooth', 'In-House', NULL, NULL, NULL, 12),
261 (123, 'Animal tooth', 'Pictures of Megaladon tooth', 'In-House', NULL, NULL, NULL,12),
262 (46, 'Animal tooth', 'Pictures of snake tooth', 'Borrowed', '2018-03-21', '2019-01-20', 77,12),
263 (15, 'Fish', 'Fish documents', 'In-House', NULL, NULL, NULL,43),
264 (426, 'Human', 'Pictures of human bone', 'Borrowed', '2018-11-21', '2019-01-29', 87,4),
265 (646, 'Wand', 'Picture of Voldemorts wand', 'Borrowed', '2018-10-10', '2019-10-20', 55,67),
266 (223, 'Fish', 'Octopus', 'In-House', NULL, NULL, NULL,21),
267
268 (536, 'Animal tooth', 'Pictures of mammoth bone', 'Borrowed', '2018-11-12', '2019-02-20', 97,67),
269 (234, 'Coin', 'Documents of chinese Coins','Borrowed','2018-05-12','2019-01-12', 74,9),
270 (921, 'Sword', 'Journals swords from the roman empire', 'Borrowed', '2018-08-18', '2019-01-03', 12,6),
271 (391, 'Fish', 'Pictures of dead fishes', 'In-house', NULL, NULL, NULL,21),
272 (999, 'Crucifix', 'Journals of crucifixs', 'In-house', NULL, NULL, NULL,43),
273
274 (175, 'Statue', 'Drawings of old statues', 'Borrowed', '2018-09-21', '2019-08-10', 32,67),
275 (382, 'Wood', 'Wooden artefacts', 'Borrowed', '2012-05-13', '2020-11-17', 99,4),
276 (322, 'Wood', 'Wooden artefacts', 'Borrowed', '2018-05-13', '2019-08-17', 11,4);
277
278GO
279CREATE VIEW books_loans AS
280 SELECT SA.b_id,
281 (ST.title) AS title,
282 (CO.p_id) AS person
283 FROM books AS SA
284 INNER JOIN books AS ST
285 ON SA.b_id = ST.b_id
286 INNER JOIN persons AS CO
287 ON SA.person = CO.p_id;
288GO
289SELECT * FROM books_loans;
290
291SELECT * FROM books
292
293
294
295GO
296CREATE VIEW slide_loans AS
297 SELECT SA.s_id,
298 (ST.s_description) AS s_description,
299 (CO.p_id) AS person
300 FROM slides AS SA
301 INNER JOIN slides AS ST
302 ON SA.s_id = ST.s_id
303 INNER JOIN persons AS CO
304 ON SA.person_id = CO.p_id;
305GO
306SELECT * FROM slide_loans;
307
308
309
310GO
311CREATE VIEW artefact_loans AS
312 SELECT SA.a_id,
313 (ST.a_descritpion) AS a_description,
314 (CO.p_id) AS person
315 FROM artefacts AS SA
316 INNER JOIN artefacts AS ST
317 ON SA.a_id = ST.a_id
318 INNER JOIN PERSONS AS CO
319 ON SA.person = CO.p_id;
320
321GO
322SELECT * FROM artefact_loans;
323SELECT * FROM artefacts;
324
325-- Detta ger oss ett snittvärde
326SELECT CAST (AVG (person) AS decimal(10,2))
327FROM artefacts;
328
329
330
331/*SELECT COUNT (date_out)
332 FROM artefacts
333 WHERE date_out LIKE '2018-12-03'
334 OR date_back Like '2019-04-24'
335 ;
336*/
337
338/*SELECT a_id AS Artefacts
339 FROM artefacts
340 LEFT JOIN persons
341 ON artefacts.a_id = persons.p_id
342 WHERE persons.p_id IS NULL;
343
344 */
345
346
347
348
349/*SELECT t_name AS Topping
350 FROM toppings
351 LEFT JOIN pasteries
352 ON toppings.t_name = pasteries.topping
353 WHERE pasteries.p_name IS NULL;
354 */