· 6 years ago · May 13, 2019, 09:20 AM
1CREATE TABLE Cars (
2 CarNr INT AUTO_INCREMENT,
3 CONSTRAINT PK
4 PRIMARY KEY (CarNr),
5 Brand VARCHAR(55),
6 Model VARCHAR(55),
7 Color VARCHAR(55),
8 PricePerDay INT
9);
10
11
12SELECT * FROM Cars;
13DROP TABLE Cars;
14INSERT INTO Cars (CarNr, Brand, Model, Color, PricePerDay)
15 VALUES
16(1,'Peugeot', '208', 'Blue', 800),
17(2,'Peugeot', '3008', 'Green', 700),
18(3,'Volkswagen', 'Polo', 'Red', 600),
19(4,'Volvo', 'V70', 'Silver', 1200),
20(5,'Tesla', 'X', 'Black', 2000),
21(6,'SAAB', '9-5', 'Green', 850),
22(7,'Volvo', 'V40', 'Red', 900),
23(8,'Fiat', '500', 'Black', 1050),
24(9,'Volvo', 'V40', 'Green', 850),
25(10,'Fiat', '500', 'Red', 950),
26(11,'Volkswagen', 'Polo', 'Blue', 700),
27(12,'BMW', 'M3', 'Black', 1599),
28(13,'Volkswagen', 'Golf', 'Red', 1500);
29
30DROP TABLE Customers;
31
32CREATE TABLE Customers (
33 CustomerNr int,
34 Name varchar(100),
35 BirthDate varchar(55),
36 PRIMARY KEY(CustomerNR)
37);
38SELECT * FROM Customers;
39DROP TABLE Customers;
40INSERT INTO Customers (CustomerNr, Name, BirthDate)
41VALUES
42(1,'Alice Andersson', '1990-05-05'),
43(2,'Oscar Johansson', '1975-08-10'),
44(3,'Nora Hansen', '1981-10-27'),
45(4,'William Johansen', '2000-01-17'),
46(5,'LucÃa GarcÃa', '1987-12-13'),
47(6,'Hugo Fernández', '1950-03-16'),
48(7,'Sofia Rossi', '1995-08-04'),
49(8,'Francesco Russo', '2000-02-26'),
50(9,'Olivia Smith', '1972-05-23'),
51(10,'Oliver Jones', '1964-05-08'),
52(11,'Shaimaa Elhawary', '1999-12-23'),
53(12,'Mohamed Elshabrawy', '1997-11-07'),
54(13,'Jing Wong', '1947-07-15'),
55(14,'Wei Lee', '1962-09-29'),
56(15,'Aadya Singh', '1973-01-01'),
57(16,'Aarav Kumar', '1986-06-28'),
58(17,'Louise Martin', '1994-04-22'),
59(18,'Gabriel Bernard', '1969-12-01'),
60(19,'Emma Smith', '1971-03-18'),
61(20,'Noah Johnson', '1800-12-16'),
62(23,'Alice Silva', '1988-12-04'),
63(24,'Miguel Santos', '1939-12-29');
64
65CREATE TABLE Booking (
66 CustomerNr INT,
67 CarNumber INT,
68 StartDate DATE,
69 EndDate DATE
70);
71
72SELECT * FROM Booking;
73
74DROP TABLE Booking;
75
76INSERT INTO Booking
77VALUES
78 (1, 6, '2018-01-02', '2018-01-15'),
79 (2, 1, '2018-01-03', '2018-01-05'),
80 (4, 3, '2018-01-03', '2018-01-04'),
81 (5, 8, '2018-01-04', '2018-01-30'),
82 (6, 10, '2018-01-10', '2018-01-13'),
83 (1, 1, '2018-01-20', '2018-01-25'),
84 (2, 13, '2018-01-21', '2018-01-30'),
85 (3, 6, '2018-01-22', '2018-01-30'),
86 (1, 2, '2018-01-29', '2018-02-01'),
87 (2, 5, '2018-02-02', '2018-02-06'),
88 (6, 1, '2018-02-20', '2018-02-25'),
89 (7, 6, '2018-02-21', '2018-02-24'),
90 (8, 3, '2018-02-21', '2018-02-28'),
91 (10, 3, '2018-02-22', '2018-02-26'),
92 (9, 12, '2018-02-22', '2018-02-28'),
93 (10, 13, '2018-03-01', '2018-03-10'),
94 (11, 1, '2018-03-04', '2018-03-09'),
95 (10, 3, '2018-03-11', '2018-03-14'),
96 (8, 6, '2018-03-14', '2018-03-17'),
97 (9, 5, '2018-03-14', '2018-03-30'),
98 (7, 12, '2018-03-18', '2018-03-20'),
99 (6, 8, '2018-03-18', '2018-04-02');
100
101#3
102SELECT PricePerDay FROM Cars
103WHERE PricePerDay > 1000;
104#4
105SELECT Brand, Model FROM Cars
106WHERE Brand LIKE 'Volvo';
107#5
108SELECT Name FROM Customers
109ORDER BY Name ASC;
110#6
111SELECT Name FROM Customers
112ORDER BY Name DESC;
113#6
114SELECT Name FROM Customers
115WHERE BirthDate > 1990
116ORDER BY BirthDate asc;
117
118#7
119SELECT Brand, Model, PricePerDay FROM Cars
120WHERE Color LIKE 'red' AND PricePerDay < 1500;
121#8
122SELECT Name,BirthDate FROM Customers
123WHERE BirthDate BETWEEN 1970 AND 1990;
124#9
125SELECT CustomerNr, CarNumber FROM Booking
126WHERE EndDate - StartDate > 6;
127#10
128SELECT CustomerNumber, CarNumber FROM Booking
129WHERE StartDate > '2018-02-01' AND EndDate < '2018-02-25';
130#11
131SELECT * FROM Customers
132WHERE Name Like 'O%';
133
134#Agregated Functions
135
136#1
137SELECT AVG(PricePerDay) FROM Cars;
138
139#2
140SELECT SUM(PricePerDay) FROM Cars;
141
142#3
143SELECT AVG(PricePerDay) FROM Cars
144WHERE Color LIKE 'Red';
145
146#4
147SELECT SUM(PricePerDay), Color FROM Cars
148GROUP BY Color;
149
150#5
151SELECT COUNT(Color) FROM Cars
152WHERE Color like 'Red';
153
154#6
155SELECT Color, COUNT(Color) FROM Cars
156GROUP BY Color;
157
158#7
159SELECT * FROM Cars
160ORDER BY PricePerDay DESC LIMIT 1;
161
162
163#Joins
164#1
165SELECT * FROM Cars
166INNER JOIN Booking
167ON CarNr = CarNumber;
168
169#2
170SELECT Name, BirthDate, CustomerNumber FROM Customers
171INNER JOIN Booking
172ON CustomerNumber = CustomerNr;
173
174#3
175SELECT * From Cars
176INNER JOIN (Booking, Customers)
177ON CustomerNumber = CustomerNr AND CarNr = CarNumber ;
178
179#4
180SELECT DISTINCT Name FROM Customers
181INNER JOIN Booking
182ON CustomerNumber = CustomerNr
183WHERE CustomerNumber = CustomerNr;
184
185#5
186SELECT DISTINCT * FROM Cars
187INNER JOIN Booking
188ON CarNr = CarNumber;
189
190SELECT DISTINCT Name, BirthDate, CustomerNumber FROM Customers
191INNER JOIN Booking
192ON CustomerNumber = CustomerNr;
193
194SELECT DISTINCT * From Cars
195INNER JOIN (Booking, Customers)
196ON CustomerNumber = CustomerNr AND CarNr = CarNumber ;
197
198SELECT DISTINCT Name FROM Customers
199INNER JOIN Booking
200ON CustomerNumber = CustomerNr
201WHERE CustomerNumber = CustomerNr;
202
203#6
204SELECT DISTINCT CarNr, Brand, Model FROM Cars
205INNER JOIN Booking
206WHERE Booking.CarNumber = Cars.CarNr
207AND Brand LIKE 'Volkswagen';
208
209#7
210SELECT DISTINCT Name FROM Customers
211INNER JOIN (Booking, Cars)
212WHERE Booking.CarNumber = Cars.CarNr
213 AND Customers.CustomerNr = Booking.CustomerNumber
214 AND Brand LIKE 'Volkswagen';
215
216#8
217SELECT DISTINCT Brand, Model, CarNr FROM Cars
218INNER JOIN (Booking, Customers)
219WHERE Booking.CarNumber = Cars.CarNr;
220
221#9
222SELECT DISTINCT Brand, Model, CarNr FROM Cars
223INNER JOIN (Booking, Customers)
224WHERE NOT Booking.CarNumber = Cars.CarNr;
225
226#10
227SELECT Color, Brand, Model FROM Cars
228INNER JOIN (Booking, Customers)
229WHERE Cars.Color LIKE 'Black'
230AND Booking.CarNumber = Cars.CarNr;
231
232# NESTED QUERIES
233#1
234 SELECT * FROM Cars
235 WHERE PricePerDay > (SELECT AVG(PricePerDay) FROM Cars);
236
237#2
238SELECT MIN(PricePerDay), Brand, Color FROM Cars
239WHERE Color Like 'Black';
240
241#3
242SELECT MIN(PricePerDay) FROM Cars;
243
244#4
245SELECT DISTINCT Brand, Model, Color, CarNr FROM Cars
246INNER JOIN Booking
247WHERE Cars.Color Like 'Black' AND
248(SELECT DISTINCT CarNumber FROM Booking
249 WHERE Booking.CarNumber = Cars.CarNr);
250
251#IN
252#1
253SELECT * FROM Cars
254WHERE PricePerDay IN (700,800,850);
255
256#2
257SELECT * FROM Customers
258WHERE BirthDate IN (1990,1995,2000);
259
260#3
261SELECT * FROM Booking
262WHERE StartDate IN ('2018-01-03','2018-02-22', '2018-03-18');
263
264
265#Between
266#1
267SELECT * FROM Cars
268WHERE PricePerDay BETWEEN 600 AND 1000;
269
270#2
271SELECT * FROM Customers
272WHERE BirthDate BETWEEN 1960 AND 1980;
273
274#3
275SELECT * FROM Booking
276WHERE DATEDIFF(EndDate,StartDate) < 4 AND DATEDIFF(EndDate,StartDate) > 2;
277
278#A MIX OF EVERYTHING
279#1
280SELECT DISTINCT * FROM Cars
281INNER JOIN Booking
282WHERE StartDate BETWEEN '2018-01-10' AND '2018-01-20'
283AND EndDate BETWEEN '2018-01-10' AND '2018-01-20';
284
285SELECT * FROM Booking;
286#2
287SELECT MAX(CarNumber), Model, Brand FROM Cars
288INNER JOIN Booking;
289
290#3
291SELECT DISTINCT Name FROM Customers INNER JOIN Booking
292WHERE (MONTH(BirthDate) = 1 OR MONTH(BirthDate) = 2) AND Customers.CustomerNr = Booking.CustomerNumber;
293
294#DELETE, UPDATE, ALTER & INSERT
295#1
296SELECT * FROM Customers
297WHERE YEAR(BirthDate) = 1800;
298
299DELETE FROM Customers
300WHERE YEAR(BirthDate) = 1800;
301
302SELECT * FROM Customers;
303
304#2
305SELECT PricePerDay, Brand, Model FROM Cars
306WHERE Brand LIKE 'Tesla' AND Model LIKE 'x';
307
308UPDATE Cars
309SET PricePerDay= PricePerDay+200
310WHERE Brand LIKE 'Tesla' AND Model LIKE 'x';
311
312#3
313UPDATE Cars
314SET PricePerDay= PricePerDay*1.2
315WHERE Brand LIKE 'Peugeot';
316
317SELECT * FROM Cars
318WHERE Brand LIKE 'Peugeot';
319
320#4
321UPDATE Cars
322SET PricePerDay = PricePerDay/10;
323
324#5
325ALTER TABLE Booking
326ADD Primkey INT NOT NULL;
327
328ALTER TABLE Booking;
329
330
331ALTER TABLE Booking
332DROP COLUMN Primkey;
333
334
335UPDATE Booking
336WHERE ;
337
338
339
340SELECT Booking.Primkey FROM Booking;
341
342
343SELECT * FROM Booking;
344
345#VIEW
346#1
347
348CREATE VIEW Black_Cars AS
349SELECT Brand,Model, CarNr FROM Cars
350WHERE Color LIKE 'Black';
351
352SELECT * FROM Black_Cars;
353
354#2
355CREATE VIEW Black AS
356SELECT * FROM Cars
357WHERE Color LIKE 'Black';
358
359SELECT * FROM Black;
360
361#3
362INSERT INTO Black VALUES (1,'Porsche','911','Black',500);
363INSERT INTO Black_Cars VALUES (1,'Porsche','911');
364
365
366
367#Labb 2
368#USER-Defined Functions
369DELIMITER //
370DROP FUNCTION IF EXISTS Test;
371CREATE FUNCTION Test(CarNR INT, StartD DATE, EndD DATE)
372RETURNS INT DETERMINISTIC
373BEGIN
374 DECLARE testVar INT;
375 SET testVar = 0;
376 SET testVar = (SELECT COUNT(*)FROM Booking
377 WHERE CarNR = CarNumber AND StartDate NOT BETWEEN (StartD AND EndD)
378 AND EndDate NOT BETWEEN (StartD AND EndD) AND StartDate<= StartD AND EndDate >= EndD);
379
380 RETURN if(testVar>0,1,0);
381 END//
382 DELIMITER ;
383 SELECT Test(1, '2018-01-04', '2018-01-15');
384
385SELECT DISTINCT * FROM Booking
386WHERE CarNumber = 10 AND StartDate BETWEEN '2018-01-10' AND NOT '2018-01-20'
387AND EndDate BETWEEN '2018-01-10' AND '2018-01-20';
388
389SELECT Count(Name) FROM Customers
390 WHERE Name Like 'Alice%';
391SELECT * FR
392#2
393DELIMITER //
394DROP FUNCTION IF EXISTS TestF2;
395CREATE FUNCTION TestF2()
396RETURNS INT DETERMINISTIC
397BEGIN
398 DECLARE testVar INT;
399 SET testVar = (SELECT SUM(DATEDIFF(EndDate,StartDate))FROM Booking);
400 RETURN testVar;
401 END//
402
403 DELIMITER ;
404
405SELECT TestF2() AS TotalSum;
406SELECT * FROM Customers;
407
408SELECT COUNT(*) FRom Customers;
409
410#3
411DELIMITER //
412DROP FUNCTION IF EXISTS TestF3;
413CREATE FUNCTION TestF3(TestCar INT)
414RETURNS INT DETERMINISTIC
415BEGIN
416 DECLARE testVar INT DEFAULT 0;
417 SET testVar = (SELECT SUM(DATEDIFF(EndDate,StartDate))FROM Booking
418 WHERE TestCar = CarNumber);
419 RETURN if(testVar>0,testVar,-1);
420 END//
421
422 DELIMITER ;
423
424SELECT TestF3(23);
425
426
427#Stored Procedures
428#1
429
430DELIMITER //
431DROP PROCEDURE AvaliableCars;
432CREATE PROCEDURE AvaliableCars(StarD DATE, EndD DATE)
433 BEGIN
434 SELECT * FROM Cars
435 WHERE CarNr NOT IN
436 (SELECT Carnumber FROM Booking WHERE (StarD BETWEEN StartDate AND EndDate)OR
437 (EndD BETWEEN StartDate AND EndDate) OR
438 (StarD >= StartDate AND EndD <= EndDate));
439 END //
440
441DELIMITER ;
442
443SELECT * FROM Booking;
444CALL AvaliableCars('2018-03-18', '2018-04-02');
445
446#2
447DELIMITER //
448DROP PROCEDURE BookingCars;
449CREATE PROCEDURE BookingCars(CarPNR INT, CustNR INT, StarD DATE, EndD DATE)
450 BEGIN
451 (SELECT COUNT(*) FROM Booking
452 WHERE (CarPNR LIKE CarNumber AND
453 (CustNR LIKE CustomerNr) AND
454 (StarD LIKE StartDate)AND
455 (EndD LIKE EndDate)));
456 END //
457
458DELIMITER ;
459CALL BookingCars(122,1,'2018-01-02','2018-01-15');
460SELECT * FROM Booking;
461
462#Triggers
463#1
464
465ALTER TABLE Customers DROP COLUMN Triggers;
466ALTER TABLE Customers ADD COLUMN Triggers INT DEFAULT 0;
467SELECT * FROM Customers;
468DELIMITER //
469DROP TRIGGER TestTrigger;
470CREATE TRIGGER TestTrigger AFTER INSERT ON Booking FOR EACH ROW
471 BEGIN
472 UPDATE Customers SET Triggers = Triggers +1 WHERE CustomerNr = NEW.CustomerNr;
473 END //
474
475DELIMITER ;
476
477SELECT * FROM Booking;
478SELECT * FROM Customers;
479DROP TABLE Booking;
480
481CREATE TABLE Booking (
482 CustomerNr INT,
483 CarNumber INT,
484 StartDate DATE,
485 EndDate DATE
486);
487
488SELECT * FROM Booking;
489
490DROP TABLE Booking;
491
492INSERT INTO Booking
493VALUES
494 (1, 6, '2018-01-02', '2018-01-15'),
495 (2, 1, '2018-01-03', '2018-01-05'),
496 (4, 3, '2018-01-03', '2018-01-04'),
497 (5, 8, '2018-01-04', '2018-01-30'),
498 (6, 10, '2018-01-10', '2018-01-13'),
499 (1, 1, '2018-01-20', '2018-01-25'),
500 (2, 13, '2018-01-21', '2018-01-30'),
501 (3, 6, '2018-01-22', '2018-01-30'),
502 (1, 2, '2018-01-29', '2018-02-01'),
503 (2, 5, '2018-02-02', '2018-02-06'),
504 (6, 1, '2018-02-20', '2018-02-25'),
505 (7, 6, '2018-02-21', '2018-02-24'),
506 (8, 3, '2018-02-21', '2018-02-28'),
507 (10, 3, '2018-02-22', '2018-02-26'),
508 (9, 12, '2018-02-22', '2018-02-28'),
509 (10, 13, '2018-03-01', '2018-03-10'),
510 (11, 1, '2018-03-04', '2018-03-09'),
511 (10, 3, '2018-03-11', '2018-03-14'),
512 (8, 6, '2018-03-14', '2018-03-17'),
513 (9, 5, '2018-03-14', '2018-03-30'),
514 (7, 12, '2018-03-18', '2018-03-20'),
515 (6, 8, '2018-03-18', '2018-04-02');
516
517/*
518
519
520Show all cars that has been booked at least once.
521Show all cars that has never been booked.
522Show all the black cars that has been booked at least once.
523/