· 7 years ago · Dec 10, 2018, 10:56 AM
1-- DDL - Data Definition Language - Hur lagret ska se ut; CREATE, DROP...
2
3DROP TABLE IF EXISTS dig;
4DROP TABLE IF EXISTS dig_location;
5DROP TABLE IF EXISTS artifact;
6DROP TABLE IF EXISTS slide;
7DROP TABLE IF EXISTS litterature;
8DROP TABLE IF EXISTS employee;
9DROP TABLE IF EXISTS student;
10
11CREATE TABLE employee (
12 PRIMARY KEY (employee_id),
13 employee_id SMALLINT,
14 first_name VARCHAR(25) NOT NULL,
15 last_name VARCHAR(25) NOT NULL,
16 e_role VARCHAR(25) NOT NULL
17 );
18
19CREATE TABLE student (
20 PRIMARY KEY (student_id),
21 student_id SMALLINT,
22 first_name VARCHAR(25) NOT NULL,
23 last_name VARCHAR(25) NOT NULL
24 );
25
26CREATE TABLE litterature (
27 PRIMARY KEY (litterature_no),
28 litterature_no SMALLINT,
29 title VARCHAR(30) NOT NULL,
30 author VARCHAR(30) NOT NULL,
31 student_id SMALLINT,
32 employee_id SMALLINT,
33 CONSTRAINT [Only one person can loan a book at once.]
34 CHECK( (student_id IS NOT NULL AND employee_id IS NULL) OR
35 (student_id IS NULL AND employee_id IS NOT NULL) OR
36 (student_id IS NULL AND employee_id IS NULL)
37 ),
38 FOREIGN KEY(student_id)
39 REFERENCES student(student_id),
40 FOREIGN KEY(employee_id)
41 REFERENCES employee(employee_id)
42 );
43
44CREATE TABLE slide (
45 PRIMARY KEY (slide_no),
46 slide_no SMALLINT,
47 slide_description VARCHAR(50) NOT NULL,
48 loan_date DATE,
49 employee_id SMALLINT,
50 shelf_no CHAR(5),
51 CONSTRAINT [If a slide is not loaned out then it has to have a shelf-number.]
52 CHECK( (employee_id IS NULL AND shelf_no IS NOT NULL) OR
53 (employee_id IS NOT NULL AND shelf_no IS NULL)
54 ),
55 FOREIGN KEY(employee_id)
56 REFERENCES employee(employee_id)
57 );
58
59CREATE TABLE dig_location (
60 PRIMARY KEY (dig_location_no),
61 dig_location_no SMALLINT,
62 location_dig VARCHAR(30) NOT NULL
63 );
64
65
66CREATE TABLE dig (
67 PRIMARY KEY (dig_no),
68 dig_no SMALLINT,
69 dig_date DATE NOT NULL,
70 employee_id SMALLINT,
71 student_id SMALLINT,
72 dig_location_no SMALLINT,
73 CONSTRAINT [A researcher has to take part in a dig and a student can not be in a dig alone.]
74 CHECK( (employee_id IS NOT NULL AND student_id IS NULL) OR
75 (employee_id IS NOT NULL AND student_id IS NOT NULL)
76 ),
77 FOREIGN KEY (employee_id)
78 REFERENCES employee(employee_id),
79 FOREIGN KEY (student_id)
80 REFERENCES student(student_id),
81 FOREIGN KEY (dig_location_no)
82 REFERENCES dig_location(dig_location_no)
83 );
84
85
86
87CREATE TABLE artifact (
88 PRIMARY KEY (artifact_no),
89 artifact_no SMALLINT,
90 shelf_no VARCHAR(4),
91 found_date DATE NOT NULL,
92 depth VARCHAR(20),
93 employee_id SMALLINT,
94 CONSTRAINT [If an artifact is not loaned out then it has to have a shelf-number.]
95 CHECK( (employee_id IS NULL AND shelf_no IS NOT NULL) OR
96 (employee_id IS NOT NULL AND shelf_no IS NULL)
97 ),
98 FOREIGN KEY(employee_id)
99 REFERENCES employee(employee_id)
100
101 );
102
103 INSERT INTO employee (employee_id, first_name, last_name, e_role)
104 VALUES ('1', 'Torsten', 'Bengtsson', 'Researcher'),
105 ('2', 'Jimmy', 'Larsson', 'Researcher'),
106 ('3', 'Tiger', 'Skog', 'Researcher'),
107 ('4', 'Carl', 'Sone','Researcher'),
108 ('5', 'Moses', 'Mos', 'Researcher'),
109 ('6', 'Kim', 'Källström', 'Researcher'),
110 ('7', 'Anders', 'Andersson', 'Researcher'),
111 ('8', 'Hasse', 'Ravelli', 'Researcher');
112
113INSERT INTO student (student_id, first_name, last_name)
114 VALUES ('5', 'Mats', 'Olsson'),
115 ('6', 'Maj', 'Snok'),
116 ('7', 'Lego', 'Larsson'),
117 ('8', 'Kanye', 'Bach');
118
119INSERT INTO litterature (litterature_no, title, author, student_id, employee_id)
120 VALUES ('10', 'Mazarin', 'George Orwell', null, '1'),
121 ('11', 'Asaklitt', 'Active Life', null, null),
122 ('12', 'Termosmugg', 'ICA CE', null, null),
123 ('13', 'Jag är fotboll', 'Zlatan', '5', null),
124 ('14', 'Lazarus', 'Lars Kepler', null, null),
125 ('15', 'Mamma Mu låtsas', 'Sven Nordqvist', '6', null),
126 ('16','Sju dagar med familjen', 'Francesca Hornak', null, null),
127 ('17', 'Jag kommer hem till jul', 'Joanna Bolouri', null, null),
128 ('18', 'Min historia', 'Michelle Obama', null, '3'),
129 ('19', '1793', 'Niklas Natt', null, null),
130 ('20', 'Ormen i Essex', 'Sarah Perry', null, '2'),
131 ('21', 'En annan Alice', 'Liane Moriarty', null, null),
132 ('22', 'Bränn alla mina brev', 'Alex Schulman', null, '1'),
133 ('23', 'Rymlingarna', 'Ulf Stark', null, null),
134 ('24', 'Min mammas hemlighet', 'Nikola Scott', '7', null),
135 ('25', 'Den frusna trädgården', 'Kristin Hannah', null, null),
136 ('26', 'Himmelskriket', 'Mons Kallentoft', null, null),
137 ('27', 'PÃ¥ hal is', 'Jeff Kinney', null, null),
138 ('28', 'Silvermysteriet', 'Martin Widmark', '8', null),
139 ('29', 'Sagor från Mumindalen', 'Tove Jansson', null, '4'),
140 ('30', 'Alfons hemliga världar', 'Gunilla Bergström', null, null),
141 ('31', 'Spöksystrar', 'Mårten Melin', null, null),
142 ('32', 'Mumiens gåta', 'Kristina Ohlsson', null, null),
143 ('33', 'Mareld', 'Camilla Sten', null, null),
144 ('34', 'Pappa Polis', 'Laura Trenter', null, null);
145
146INSERT INTO slide(slide_no, slide_description, loan_date, employee_id, shelf_no)
147 VALUES ('35', 'lorem ipsum', '05-jan-1970', null, '00001'),
148 ('36', 'lorem ipsum', '05-jan-1970', null, '00002'),
149 ('37', 'lorem ipsum', '05-jan-1970', '1', null),
150 ('38', 'lorem ipsum', '05-jan-1970', '1', null),
151 ('39', 'lorem ipsum', '05-jan-1970', '3', null),
152 ('40', 'lorem ipsum', '05-jan-1970', '2', null),
153 ('41', 'lorem ipsum', '12-aug-1989', null, '00007'),
154 ('42', 'lorem ipsum', '12-aug-1989', null, '00008'),
155 ('43', 'lorem ipsum', '12-aug-1989', null, '00009'),
156 ('44', 'lorem ipsum', '12-aug-1989', '4', null),
157 ('45', 'lorem ipsum', '12-aug-1989', '4', null),
158 ('46', 'lorem ipsum', null, null, '00012'),
159 ('47', 'lorem ipsum', null, null, '00013'),
160 ('48', 'lorem ipsum', null, null, '00014'),
161 ('49', 'lorem ipsum', null, null, '00015'),
162 ('50', 'lorem ipsum', null, null, '00016');
163
164
165
166INSERT INTO artifact (artifact_no, shelf_no, found_date, depth, employee_id)
167VALUES ('51', null, '25-jan-1979', '200 cm', '2'),
168 ('52', null, '25-jan-1979', '200 cm', '2'),
169 ('53', '69B', '25-jan-1979', '200 cm', null),
170 ('50', null, '25-jan-1979', '200 cm', '2'),
171 ('54', null, '25-jan-1979', '200 cm', '2'),
172 ('55', '69B', '25-jan-1979', '200 cm', null),
173 ('56', '69B', '25-jan-1979', '200 cm', null),
174 ('57', null, '25-jan-1979', '200 cm', '2'),
175 ('58', null, '25-jan-1979', '200 cm', '2'),
176 ('59', '45A', '12-aug-1989', '105 cm', null),
177 ('60', '45A', '12-aug-1989', '105 cm', null),
178 ('61', '45A', '12-aug-1989', '105 cm', null),
179 ('62', '45A', '12-aug-1989', '105 cm', null),
180 ('63', '45A', '12-aug-1989', '105 cm', null),
181 ('64', '45A', '12-aug-1989', '105 cm', null),
182 ('65', '45A', '12-aug-1989', '105 cm', null),
183 ('66', '45A', '12-aug-1989', '105 cm', null),
184 ('67', '45A', '12-aug-1989', '105 cm', null),
185 ('68', '45A', '12-aug-1989', '105 cm', null),
186 ('69', '45A', '12-aug-1989', '105 cm', null),
187 ('70', '12F', '03-mar-1991', '265 cm', null),
188 ('71', '12F', '03-mar-1991', '265 cm', null),
189 ('72', '12F', '03-mar-1991', '265 cm', null),
190 ('73', '12F', '03-mar-1991', '265 cm', null),
191 ('74', '12F', '03-mar-1991', '265 cm', null),
192 ('75', '12F', '03-mar-1991', '265 cm', null),
193 ('76', '12F', '03-mar-1991', '265 cm', null),
194 ('77', '12F', '03-mar-1991', '265 cm', null),
195 ('78', null, '20-may-1990', '140 cm', '1'),
196 ('79', null, '20-may-1990', '140 cm', '1'),
197 ('80', null, '20-may-1990', '140 cm', '1'),
198 ('81', null, '20-may-1990', '140 cm', '1');
199
200INSERT INTO dig_location (dig_location_no, location_dig)
201VALUES ('82', 'Linköping'),
202 ('83', 'Norrköping'),
203 ('84', 'Mjölby'),
204 ('85', 'Motala'),
205 ('86', 'Boxholm'),
206 ('87', 'Grebo');
207
208
209INSERT INTO dig(dig_no, dig_date, employee_id, student_id, dig_location_no)
210VALUES ('88', '03-may-2010', '1', null, '82'),
211 ('89', '25-jun-2011', '3', null, '83'),
212 ('90', '17-feb-2012', '2', '7', '84'),
213 ('91', '12-mar-2012', '4', '8', '85'),
214 ('92', '12-mar-2012', '1', '6', '86'),
215 ('93', '12-mar-2011', '1', '6', '82'),
216 ('94', '12-mar-2011', '1', '6', '82'),
217 ('95', '12-mar-2011', '2', '5', '82'),
218 ('96', '23-aug-2014', '3', '8', '87');
219
220 SELECT * FROM litterature;
221 SELECT * FROM employee;
222 SELECT * FROM student;
223 SELECT * FROM slide;
224 SELECT * FROM artifact;
225 SELECT * FROM dig;
226
227 /* 3.
228PÃ¥visa databasens funktionalitet med SQL.
229a.
230Skapa tre vyer (views) som sammanställer data från era tabeller.
231b.
232Skapa fyra frågor (queries) för att demonstrera era tabeller i användning
233i.
234Alla frågor ska använda sig av ORDER BY eller GROUP BY
235ii.
236Max en simpelfråga (Fråga utan join, aggregatfunktion och villkor)
237iii.
238Minst två frågor som ska använda Villkor (WHERE)
239iv.
240En fråga som använder aggregatfunktioner (MAX, SUM, AVG, etc.).
241c.
242Tänk på att koppla ihop tabeller med INNER JOIN
243*/
244
245SELECT employee_id, first_name, last_name
246 FROM employee AS A
247 ORDER BY last_name;
248
249
250SELECT litterature_no, author, title, litterature.employee_id, first_name, last_name
251 FROM litterature
252 INNER JOIN employee
253 ON litterature.employee_id=employee.employee_id
254 WHERE litterature.employee_id IS NOT NULL AND student_id IS NULL
255 ORDER BY litterature_no;
256
257SELECT 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'
258 FROM dig
259 INNER JOIN dig_location
260 ON dig.dig_location_no=dig_location.dig_location_no
261 INNER JOIN employee
262 ON dig.employee_id=employee.employee_id
263 INNER JOIN student
264 ON dig.student_id=student.student_id AND student.student_id IS NOT NULL
265 WHERE location_dig LIKE 'L%' --AND student_id IS NOT NULL OR student_id IS NULL
266 ORDER BY dig_no;
267
268
269
270SELECT artifact_no, found_date, dig.dig_no, dig.dig_date, dig.employee_id
271 FROM artifact
272 INNER JOIN employee
273 ON artifact.employee_id=employee.employee_id
274 INNER JOIN dig
275 ON dig.employee_id=employee.employee_id
276 WHERE dig.dig_date BETWEEN '2010-01-01' AND '2012-01-01'
277 ORDER BY dig.dig_date;
278
279
280SELECT COUNT(dig_no)
281 FROM dig AS d
282 INNER JOIN student AS S
283 ON d.student_id=s.student_id
284 WHERE d.student_id IS NOT NULL;