57nsdhrk

1#Create a function that sums the total amount of days cars have been booked and returns the sum.
2DELIMITER //
3DROP FUNCTION IF EXISTS FunTWO;
4CREATE FUNCTION FunTwo(Vecile INT)
5RETURNS INT DETERMINISTIC
6BEGIN
7  DECLARE ind INT DEFAULT 0;
8  SET ind = (SELECT SUM(DATEDIFF(EndDate,StartDate)) FROM Booking WHERE Vecile = CarNumber);
9  RETURN if(ind>0,ind,-1);
10END //
11DELIMITER ;
12SELECT FunTwo(3) AS Totaltimethecarhasbeenbooked;
13#Extend the previous function so that if there is an input parameter that matches a cars unique number, then it should only return the sum of that car. If the number doesn't match or it is -1, it returns the total sum as before.
14#Stored Procedures
15#Create a stored procedure that collects all the cars that are available between two dates. The inputs to the procedure is starting and ending dates, and prints all the car numbers that are available to be booked between the two dates.
16
17
18DELIMITER //
19DROP PROCEDURE AvaliableCars;
20CREATE PROCEDURE AvaliableCars(StarD DATE, EndD DATE)
21  BEGIN
22    SELECT * FROM Cars
23    WHERE CarNr NOT IN
24          (SELECT Carnumber FROM Booking WHERE (StartDate BETWEEN StarD AND EndD)OR
25                                               (EndDate BETWEEN StarD AND EndD) OR
26                                               (StarD >= StartDate AND EndD  <= EndDate));
27  END //
28
29DELIMITER ;
30
31SELECT * FROM Booking;
32CALL AvaliableCars('2018-01-02', '2018-01-15');
33#Create a stored procedure that handles the booking of renting a car. The input to the procedure is the starting and ending dates, the cars number,  and the customer number. If it is successful it should return 0, if it is unsuccessful in booking it should return 1.
34DROP PROCEDURE IF EXISTS ProTwo;
35CREATE PROCEDURE ProTwo(Car INT, Cusid INT, StartD DATE, EndD DATE)
36BEGIN
37  IF SELECT COUNT(*) AS AV FROM Booking WHERE Car = CarNumber AND Cusid = CustomerNr AND (StartD  BETWEEN StartDate AND EndDate)AND
38                                               (EndD  BETWEEN StartDate AND EndDate) = 1; THEN
39  END IF
40END;
41
42CALL ProTwo(6,1,'2018-01-02', '2018-01-14');
43#DELIMITER //
44#DROP PROCEDURE IF EXISTS ProTwo;
45#CREATE PROCEDURE ProTwo(Car INT, Cusid INT, StartD DATE, EndD DATE)
46#BEGIN
47#  DECLARE Var INT DEFAULT 0;
48#  SET Var = (SELECT COUNT(*) AS AV FROM Booking WHERE Car = CarNumber AND Cusid = CustomerNr AND (StartD  BETWEEN StartDate AND EndDate)AND
49#                                               (EndD  BETWEEN StartDate AND EndDate));
50#  if(Var=0, )
51#END//
52#
53
54CALL ProTwo(6,1,'2018-01-02', '2018-01-14');
55SELECT * FROM Booking;
56
57  #Triggers
58#Add an additional column to Customers that contains the amount of times a customer has booked a car. Then create an after insert trigger on the Bookings table that increments the newly created column in Customers whenever they do a successful booking of a car.
59ALTER TABLE Customers ADD COLUMN NRBookings INT DEFAULT 0;
60SELECT * FROM Customers;
61DELIMITER //
62DROP TRIGGER IF EXISTS TrigONE;
63CREATE TRIGGER TrigONE AFTER INSERT ON Booking FOR EACH ROW
64  BEGIN
65    UPDATE Customers SET Customers.NRBookings = Customers.NRBookings + 1 WHERE CustomerNr=NEW.CustomerNr;
66  END//
67
68  DELIMITER ;
69#Would it be possible to do this trigger with a BEFORE trigger? Why/Why not?
70
71  #inte klarat att lösa
72#Show all cars that has never been booked.
73#Alter the previous view, with the condition that the cars have to be available for at least 3 days of renting.