9ZZ1xJgv

1#Labb 2
2#USER-Defined Functions
3DELIMITER //
4DROP FUNCTION IF EXISTS Test;
5CREATE FUNCTION Test(CarNR INT, StartD DATE, EndD DATE)
6RETURNS INT DETERMINISTIC
7BEGIN
8  DECLARE testVar INT;
9  SET testVar = 0;
10  SET testVar = (SELECT COUNT(*)FROM Booking
11  WHERE CarNR = CarNumber AND NOT StartDate BETWEEN (StartD AND EndD)
12  AND NOT EndDate BETWEEN (StartD AND EndD) AND StartDate<= StartD AND EndDate >= EndD);
13
14  RETURN if(testVar>0,1,0);
15  END//
16  DELIMITER ;
17  SELECT Test(8, '2018-01-04', '2018-01-30');
18
19SELECT DISTINCT * FROM Booking
20WHERE CarNumber = 10 AND  StartDate BETWEEN '2018-01-10' AND '2018-01-20'
21AND EndDate BETWEEN '2018-01-10' AND '2018-01-20';
22
23#2
24DELIMITER //
25DROP FUNCTION IF EXISTS TestF2;
26CREATE FUNCTION TestF2()
27RETURNS INT DETERMINISTIC
28BEGIN
29  DECLARE testVar INT;
30  SET testVar = (SELECT SUM(DATEDIFF(EndDate,StartDate))FROM Booking);
31  RETURN testVar;
32  END//
33
34  DELIMITER ;
35
36SELECT TestF2() AS TotalSum;
37
38
39#3
40DELIMITER //
41DROP FUNCTION IF EXISTS TestF3;
42CREATE FUNCTION TestF3(TestCar INT)
43RETURNS INT DETERMINISTIC
44BEGIN
45  DECLARE testVar INT DEFAULT 0;
46  SET testVar = (SELECT SUM(DATEDIFF(EndDate,StartDate))FROM Booking
47    WHERE TestCar = CarNumber);
48  RETURN if(testVar>0,testVar,-1);
49  END//
50
51  DELIMITER ;
52
53SELECT TestF3(23);
54
55
56#Stored Procedures
57#1
58
59DELIMITER //
60DROP PROCEDURE AvaliableCars;
61CREATE PROCEDURE AvaliableCars(StarD DATE, EndD DATE)
62  BEGIN
63    SELECT * FROM Cars
64    WHERE CarNr NOT IN
65          (SELECT Carnumber FROM Booking WHERE (StarD BETWEEN StartDate AND EndDate)OR
66                                               (EndD BETWEEN StartDate AND EndDate) OR
67                                               (StarD >= StartDate AND EndD  <= EndDate));
68  END //
69
70DELIMITER ;
71
72SELECT * FROM Booking;
73CALL AvaliableCars('2018-03-18', '2018-04-02');
74
75#2
76DELIMITER //
77DROP PROCEDURE BookingCars;
78CREATE PROCEDURE BookingCars(CarPNR INT, CustNR INT, StarD DATE, EndD DATE)
79  BEGIN
80   (SELECT COUNT(*) FROM Booking
81    WHERE (CarPNR LIKE CarNumber AND
82      (CustNR LIKE CustomerNr) AND
83           (StarD LIKE StartDate)AND
84           (EndD LIKE EndDate)));
85  END //
86
87DELIMITER ;
88CALL BookingCars(122,1,'2018-01-02','2018-01-15');
89SELECT * FROM Booking;
90
91#Triggers
92#1
93
94ALTER TABLE Customers DROP COLUMN Triggers;
95ALTER TABLE Customers ADD COLUMN Triggers INT DEFAULT 0;
96SELECT * FROM Customers;
97DELIMITER //
98DROP TRIGGER TestTrigger;
99CREATE TRIGGER TestTrigger AFTER INSERT ON Booking FOR EACH ROW
100  BEGIN
101      UPDATE Customers SET Triggers = Triggers +1 WHERE CustomerNr = NEW.CustomerNr;
102  END //
103
104DELIMITER ;