Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- #Create a function that sums the total amount of days cars have been booked and returns the sum.
- DELIMITER //
- DROP FUNCTION IF EXISTS FunTWO;
- CREATE FUNCTION FunTwo(Vecile INT)
- RETURNS INT DETERMINISTIC
- BEGIN
- DECLARE ind INT DEFAULT 0;
- SET ind = (SELECT SUM(DATEDIFF(EndDate,StartDate)) FROM Booking WHERE Vecile = CarNumber);
- RETURN if(ind>0,ind,-1);
- END //
- DELIMITER ;
- SELECT FunTwo(3) AS Totaltimethecarhasbeenbooked;
- #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.
- #Stored Procedures
- #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.
- DELIMITER //
- DROP PROCEDURE AvaliableCars;
- CREATE PROCEDURE AvaliableCars(StarD DATE, EndD DATE)
- BEGIN
- SELECT * FROM Cars
- WHERE CarNr NOT IN
- (SELECT Carnumber FROM Booking WHERE (StartDate BETWEEN StarD AND EndD)OR
- (EndDate BETWEEN StarD AND EndD) OR
- (StarD >= StartDate AND EndD <= EndDate));
- END //
- DELIMITER ;
- SELECT * FROM Booking;
- CALL AvaliableCars('2018-01-02', '2018-01-15');
- #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.
- DROP PROCEDURE IF EXISTS ProTwo;
- CREATE PROCEDURE ProTwo(Car INT, Cusid INT, StartD DATE, EndD DATE)
- BEGIN
- IF SELECT COUNT(*) AS AV FROM Booking WHERE Car = CarNumber AND Cusid = CustomerNr AND (StartD BETWEEN StartDate AND EndDate)AND
- (EndD BETWEEN StartDate AND EndDate) = 1; THEN
- END IF
- END;
- CALL ProTwo(6,1,'2018-01-02', '2018-01-14');
- #DELIMITER //
- #DROP PROCEDURE IF EXISTS ProTwo;
- #CREATE PROCEDURE ProTwo(Car INT, Cusid INT, StartD DATE, EndD DATE)
- #BEGIN
- # DECLARE Var INT DEFAULT 0;
- # SET Var = (SELECT COUNT(*) AS AV FROM Booking WHERE Car = CarNumber AND Cusid = CustomerNr AND (StartD BETWEEN StartDate AND EndDate)AND
- # (EndD BETWEEN StartDate AND EndDate));
- # if(Var=0, )
- #END//
- #
- CALL ProTwo(6,1,'2018-01-02', '2018-01-14');
- SELECT * FROM Booking;
- #Triggers
- #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.
- ALTER TABLE Customers ADD COLUMN NRBookings INT DEFAULT 0;
- SELECT * FROM Customers;
- DELIMITER //
- DROP TRIGGER IF EXISTS TrigONE;
- CREATE TRIGGER TrigONE AFTER INSERT ON Booking FOR EACH ROW
- BEGIN
- UPDATE Customers SET Customers.NRBookings = Customers.NRBookings + 1 WHERE CustomerNr=NEW.CustomerNr;
- END//
- DELIMITER ;
- #Would it be possible to do this trigger with a BEFORE trigger? Why/Why not?
- #inte klarat att lösa
- #Show all cars that has never been booked.
- #Alter the previous view, with the condition that the cars have to be available for at least 3 days of renting.
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement