· 6 years ago · May 19, 2019, 02:32 PM
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);
10ALTER TABLE Cars AUTO_INCREMENT = 10;
11
12SELECT * FROM Cars;
13DROP TABLE Cars;
14
15
16INSERT INTO Cars ( Brand, Model, Color, PricePerDay)
17 VALUES
18('Peugeot', '208', 'Blue', 800),
19('Peugeot', '3008', 'Green', 700),
20('Volkswagen', 'Polo', 'Red', 600),
21('Volvo', 'V70', 'Silver', 1200),
22('Tesla', 'X', 'Black', 2000),
23('SAAB', '9-5', 'Green', 850),
24('Volvo', 'V40', 'Red', 900),
25('Fiat', '500', 'Black', 1050),
26('Volvo', 'V40', 'Green', 850),
27('Fiat', '500', 'Red', 950),
28('Volkswagen', 'Polo', 'Blue', 700),
29('BMW', 'M3', 'Black', 1599),
30('Volkswagen', 'Golf', 'Red', 1500);
31
32DROP TABLE Customers;
33
34CREATE TABLE Customers (
35 CustomerNr int,
36 Name varchar(100),
37 BirthDate varchar(55),
38 PRIMARY KEY(CustomerNR)
39);
40SELECT * FROM Customers;
41DROP TABLE Customers;
42INSERT INTO Customers (CustomerNr, Name, BirthDate)
43VALUES
44(1,'Alice Andersson', '1990-05-05'),
45(2,'Oscar Johansson', '1975-08-10'),
46(3,'Nora Hansen', '1981-10-27'),
47(4,'William Johansen', '2000-01-17'),
48(5,'LucÃa GarcÃa', '1987-12-13'),
49(6,'Hugo Fernández', '1950-03-16'),
50(7,'Sofia Rossi', '1995-08-04'),
51(8,'Francesco Russo', '2000-02-26'),
52(9,'Olivia Smith', '1972-05-23'),
53(10,'Oliver Jones', '1964-05-08'),
54(11,'Shaimaa Elhawary', '1999-12-23'),
55(12,'Mohamed Elshabrawy', '1997-11-07'),
56(13,'Jing Wong', '1947-07-15'),
57(14,'Wei Lee', '1962-09-29'),
58(15,'Aadya Singh', '1973-01-01'),
59(16,'Aarav Kumar', '1986-06-28'),
60(17,'Louise Martin', '1994-04-22'),
61(18,'Gabriel Bernard', '1969-12-01'),
62(19,'Emma Smith', '1971-03-18'),
63(20,'Noah Johnson', '1800-12-16'),
64(23,'Alice Silva', '1988-12-04'),
65(24,'Miguel Santos', '1939-12-29');
66
67CREATE TABLE Booking (
68 CustomerNr INT,
69 CarNumber INT,
70 StartDate DATE,
71 EndDate DATE
72);
73
74SELECT * FROM Booking;
75
76DROP TABLE Booking;
77
78DELETE FROM Booking WHERE Booking.BookingID > 0;
79
80ALTER TABLE Booking DROP COLUMN BookingID;
81
82INSERT INTO Booking
83VALUES
84 (1, 6, '2018-01-02', '2018-01-15'),
85 (2, 1, '2018-01-03', '2018-01-05'),
86 (4, 3, '2018-01-03', '2018-01-04'),
87 (5, 8, '2018-01-04', '2018-01-30'),
88 (6, 10, '2018-01-10', '2018-01-13'),
89 (1, 1, '2018-01-20', '2018-01-25'),
90 (2, 13, '2018-01-21', '2018-01-30'),
91 (3, 6, '2018-01-22', '2018-01-30'),
92 (1, 2, '2018-01-29', '2018-02-01'),
93 (2, 5, '2018-02-02', '2018-02-06'),
94 (6, 1, '2018-02-20', '2018-02-25'),
95 (7, 6, '2018-02-21', '2018-02-24'),
96 (8, 3, '2018-02-21', '2018-02-28'),
97 (10, 3, '2018-02-22', '2018-02-26'),
98 (9, 12, '2018-02-22', '2018-02-28'),
99 (10, 13, '2018-03-01', '2018-03-10'),
100 (11, 1, '2018-03-04', '2018-03-09'),
101 (10, 3, '2018-03-11', '2018-03-14'),
102 (8, 6, '2018-03-14', '2018-03-17'),
103 (9, 5, '2018-03-14', '2018-03-30'),
104 (7, 12, '2018-03-18', '2018-03-20'),
105 (6, 8, '2018-03-18', '2018-04-02');
106
107#Show all customers with all their information.
108SELECT * FROM Customers;
109#Show all customers, but only with their name and birthdate.
110 SELECT Name, BirthDate FROM Customers;
111#Show all cars that cost more than 1000:- per day.
112SELECT * FROM Cars WHERE PricePerDay > 1000;
113#Show all Volvo cars, only with their brand name and their model.
114 SELECT Brand, Model FROM Cars WHERE Brand LIKE 'Volvo';
115#Show all customers, only with their names, in a sorted fashion based on their name. Both in ascending and descending order.
116 SELECT Name FROM Customers GROUP BY Name DESC;
117 SELECT Name FROM Customers GROUP BY Name ASC;
118#Show all customers, only with their names, that were born in 1990 or later in a sorted fashion based on their birthdate.
119SELECT Name FROM Customers WHERE YEAR(BirthDate) >= 1990 GROUP BY BirthDate;
120#Show all cars that are red and cost less than 1500.
121SELECT * FROM Cars WHERE Color LIKE 'Red' AND PricePerDay < 1500;
122#Show all customers, only with their names, that were born between 1970-1990.
123SELECT Name FROM Customers WHERE YEAR(BirthDate) >=1970 AND YEAR(BirthDate) <= 1990;
124SELECT * FROM Customers;
125#Show all bookings that are longer than 6 days.
126SELECT * FROM Booking WHERE DATEDIFF(EndDate,StartDate) > 6;
127#Show all bookings that overlap with the interval 2018-02-01 - 2018-02-25.
128SELECT * FROM Booking WHERE ((StartDate BETWEEN '2018-02-01' AND '2018-02-25') OR
129 (EndDate BETWEEN '2018-02-01' AND '2018-02-25') AND
130 (StartDate < '2018-02-01') AND
131 EndDate > '2018-02-25');
132SELECT * FROM Booking;
133#Show all customers whose first name starts with an O.
134SELECT * FROM Customers WHERE Name Like 'O%';
135
136#Aggregated Functions
137#Show the average price per day for the cars.
138SELECT AVG(PricePerDay) FROM Cars;
139#Show the total price per day for the cars.
140SELECT Sum(PricePerDay) FROM Cars;
141#Show the average price for red cars.
142SELECT AVG(PricePerDay) FROM Cars WHERE Color LIKE 'Red';
143#Show the total price for all cars grouped by the different colors.
144SELECT Sum(PricePerDay), Color FROM Cars GROUP BY Color ASC;
145#Show how many cars are of red color.
146SELECT COUNT(*) FROM Cars WHERE Color LIKE 'Red';
147#Show how many cars exists of each color.
148SELECT COUNT(*), Color FROM Cars GROUP BY Color;
149#Show the car that is the most expensive to rent. (Do not hard code this with the most expensive price, instead use ORDER and LIMIT.)
150SELECT PricePerDay FROM Cars ORDER BY PricePerDay DESC LIMIT 1;
151#Joins
152#Show the Cartesian product between Cars and Bookings.
153SELECT * FROM Cars CROSS JOIN Booking;
154#Show the Cartesian product between Customers and Bookings.
155SELECT * FROM Customers CROSS JOIN Booking;
156#Show the results of converting the previous two joins to inner joins.
157SELECT DISTINCT * FROM Cars INNER JOIN Booking;
158SELECT DISTINCT * FROM Customers INNER JOIN Booking;
159#Show the names of all the customers that has made a booking.
160SELECT Name FROM Customers INNER JOIN Booking WHERE Booking.CustomerNr = Customers.CustomerNr;
161#Same as the previous but without all the duplicates.
162SELECT DISTINCT Name FROM Customers INNER JOIN Booking WHERE Booking.CustomerNr = Customers.CustomerNr;
163#Show all the Volkswagen cars that has been booked at least once.
164SELECT DISTINCT * FROM Cars INNER JOIN Booking
165 WHERE CarNr = CarNumber AND Brand LIKE 'Volkswagen';
166
167
168
169#Show all the customers that has rented a Volkswagen.
170SELECT DISTINCT * FROM Cars INNER JOIN (Customers,Booking)
171ON CarNr = CarNumber AND Customers.CustomerNr = Booking.CustomerNr AND Brand LIKE 'Volkswagen';
172#Show all cars that has been booked at least once.
173 SELECT Brand, Model, CarNr FROM Cars INNER JOIN Booking
174ON CarNr = CarNumber ORDER BY Brand ASC;
175SELECT COUNT(*) FROM Booking;
176SELECT * FROM Cars;
177#Show all cars that has never been booked.
178SELECT DISTINCT CarNr, Brand, Model FROM Cars INNER JOIN (Booking,Customers)
179WHERE CarNumber = CarNr AND Customers.CustomerNr = Booking.CustomerNr ORDER BY Brand ASC;
180#Show all the black cars that has been booked at least once.
181 SELECT DISTINCT Color, Brand, Model FROM Cars INNER JOIN Booking
182 WHERE CarNr = CarNumber AND Color LIKE 'Black';
183#Nested Queries
184#Show all the cars that cost more than the average.
185SELECT * FROM Cars
186 WHERE PricePerDay < (SELECT AVG(PricePerDay) FROM Cars);
187#Show the car with the lowest cost with black color.
188SELECT * FROM Cars
189WHERE PricePerDay = (SELECT MIN(PricePerDay) FROM Cars WHERE Color LIKE 'Black');
190#Show the car which has the lowest cost.
191SELECT * FROM Cars
192WHERE PricePerDay = (SELECT MIN(PricePerDay)FROM Cars);
193#Show all the black cars that has been booked at least once by using a sub query.
194 SELECT * FROM Booking
195WHERE CarNumber IN (SELECT CarNr FROM Cars WHERE Color LIKE 'blue');
196 SELECT * FROM Cars
197WHERE Color LIKE 'Black' AND (CarNR IN (SELECT CarNumber FROM Booking));
198#IN
199#Show all cars that has the cost 700, 800, and 850.
200SELECT * FROM Cars
201WHERE PricePerDay IN (700,800,850);
202#Show all the customers that born in 1990, 1995, and 2000. (Hint: YEAR function).
203SELECT * FROM Customers
204WHERE YEAR(BirthDate) IN (1990,1995,2000);
205#Show all the bookings that start on 2018-01-03, 2018-02-22, or 2018-03-18.
206SELECT * FROM Booking
207WHERE StartDate IN ('2018-01-03', '2018-02-22','2018-03-18');
208# BETWEEN
209#Show all cars whose price is in the range 600 - 1000.
210SELECT * FROM Cars WHERE PricePerDay BETWEEN 600 AND 1000;
211#Show all the customers who are born between 1960 - 1980.
212SELECT * FROM Customers WHERE YEAR(BirthDate) BETWEEN 1960 AND 1980;
213#Show all bookings that last between 2 - 4 days.
214SELECT * FROM Booking WHERE DATEDIFF(EndDate,StartDate) BETWEEN 2 AND 4;
215#A mix of everything
216#Show all the cars that are eligible for booking between 2018-01-10 - 2018-01-20.
217SELECT DISTINCT * FROM Cars INNER JOIN Booking
218WHERE CarNr = CarNumber AND StartDate NOT BETWEEN '2018-01-10' AND '2018-01-20' AND EndDate NOT BETWEEN '2018-01-10' AND '2018-01-20';
219#Show the car that has been booked the most.
220SELECT CarNR, COUNT(CarNr) AS antalbookningar, Model, Brand FROM Cars INNER JOIN Booking ON CarNr=CarNumber WHERE CarNumber = CarNr GROUP BY CarNr;#Funkar inte
221
222#Show all the customers who are born in January or February and has booked at least one car.
223SELECT * FROM Customers INNER JOIN Booking
224WHERE (MONTH(BirthDate) = 1 OR MONTH(BirthDate) = 2) AND Booking.CustomerNr=Customers.CustomerNr;
225
226#DELETE, UPDATE, ALTER, & INSERT
227#There is a customer born in 1800 according to the records, this is obviously not possible so delete that customer.
228DELETE FROM Customers WHERE YEAR(BirthDate) = 1800;
229SELECT * FROM Customers;
230#The Tesla X car that is available for renting needs to have its price increased by 200:-.
231UPDATE Cars SET PricePerDay = PricePerDay + 200 WHERE Brand LIKE 'Tesla' AND Model LIKE 'X';
232SELECT * FROM Cars;
233#All the Peugeot cars also needs to be increased in price, in this case by 20%.
234UPDATE Cars set PricePerDay = PricePerDay * 1.2 WHERE Brand LIKE 'Peugeot';
235SELECT * FROM Cars WHERE Brand LIKE 'Pe%';
236#Now we fast forward into the future and Sweden has changed its currency to Euros (€). Fix both the data itself (assume the conversion rate is 10SEK == 1 EUR) and the table so it can handle the new prices.
237ALTER TABLE Cars MODIFY COLUMN PricePerDay Float;
238UPDATE Cars SET PricePerDay = PricePerDay/10;
239SELECT * FROM Cars;
240#Can we construct a PK in the Bookings table without adding a new column? If yes, do that. If not, add another column that allows you to uniquely identify each booking.
241ALTER TABLE Booking ADD COLUMN BookingID INT AUTO_INCREMENT PRIMARY KEY;
242SELECT * FROM Booking;
243#VIEW
244#Create a view, that shows all the information about black cars.
245
246CREATE VIEW BlackCar AS SELECT * FROM Cars WHERE Color LIKE 'Black';
247
248SELECT * FROM BlackCar;
249#Create a view that shows all information about black cars and the addition of the weekly price as a column.
250ALTER TABLE Cars ADD PricePerWeek INT DEFAULT 0;
251 UPDATE Cars SET Cars.PricePerWeek = PricePerDay * 7;
252CREATE VIEW Informationiskey AS
253 SELECT * FROM Cars WHERE Color LIKE 'Black';
254
255SELECT * FROM Informationiskey;
256#Try and insert a car into both views created. What happens? Why? What's the difference between the views?
257INSERT INTO Informationiskey (carnr, brand, model, color, priceperday) VALUES (21,'Tesla','De','Black', 2000);
258SELECT * FROM Informationiskey;
259#Create a view that shows all the cars available for booking at this current time.
260DROP VIEW AvailibleCars;
261CREATE VIEW AvailibleCars AS
262SELECT DISTINCT Cars.CarNr, Cars.Brand, Cars.Model, Cars.Color, Cars.PricePerDay, Cars.PricePerWeek FROM Cars INNER JOIN Booking
263WHERE (StartDate > NOW() OR EndDate < NOW());
264SELECT DISTINCT * FROM AvailibleCars;
265#Alter the previous view, with the condition that the cars have to be available for at least 3 days of renting.
266ALTER VIEW AvailibleCars
267#DROP
268#Drop the table Cars.
269DROP TABLE Cars;
270#Why didn't it work? Fix so that you can drop the table.
271#Delete all the rows of table Customers.
272DELETE FROM Customers WHERE CustomerNr >0;
273SELECT * FROM Customers;
274#What's the difference between DROP TABLE and DELETE?
275#
276#
277#User-Defined Functions
278#Create a function that checks if a car is available for renting between two dates. The input to the function should be the starting and ending dates of the rental, the cars number, and it should return 0 if it is not available and 1 if it is available between the two dates.
279DELIMITER //
280DROP FUNCTION IF EXISTS FunONE;
281CREATE FUNCTION FunONE (Car INT, StartD DATE, EndD DATE)
282RETURNS INT DETERMINISTIC
283BEGIN
284 DECLARE index1 INT DEFAULT 0;
285 SET index1 = (SELECT COUNT(*) FROM Booking WHERE CarNumber = Car AND
286 StartDate BETWEEN StartD AND EndD AND
287 EndDate BETWEEN StartD AND EndD);
288 RETURN If(index1>=1 ,0,index1);
289END//
290DELIMITER ;
291SELECT FunONE(1,'2018-02-02', '2018-03-01');
292SELECT * FROM Booking;
293
294DELIMITER //
295DROP FUNCTION IF EXISTS Test;
296CREATE FUNCTION Test(CarNR INT, StartD DATE, EndD DATE)
297RETURNS INT DETERMINISTIC
298BEGIN
299 DECLARE testVar INT;
300 SET testVar = 0;
301 SET testVar = (SELECT COUNT(*)FROM Booking
302 WHERE CarNR = CarNumber AND StartDate NOT BETWEEN (StartD AND EndD)
303 AND EndDate NOT BETWEEN (StartD AND EndD) AND StartDate<= StartD AND EndDate >= EndD);
304
305 RETURN if(testVar>0,2,0);
306 END//
307 DELIMITER ;
308 SELECT Test(2,'2018-01-01','2001-01-10') AS Test;
309#Create a function that sums the total amount of days cars have been booked and returns the sum.
310DELIMITER //
311DROP FUNCTION IF EXISTS FunTWO;
312CREATE FUNCTION FunTwo(Vecile INT)
313RETURNS INT DETERMINISTIC
314BEGIN
315 DECLARE ind INT DEFAULT 0;
316 SET ind = (SELECT SUM(DATEDIFF(EndDate,StartDate)) FROM Booking WHERE Vecile = CarNumber);
317 RETURN if(ind>0,ind,-1);
318END //
319DELIMITER ;
320SELECT FunTwo(3) AS Totaltimethecarhasbeenbooked;
321#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.
322#Stored Procedures
323#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.
324
325
326DELIMITER //
327DROP PROCEDURE AvaliableCars;
328CREATE PROCEDURE AvaliableCars(StarD DATE, EndD DATE)
329 BEGIN
330 SELECT * FROM Cars
331 WHERE CarNr NOT IN
332 (SELECT Carnumber FROM Booking WHERE (StartDate BETWEEN StarD AND EndD)OR
333 (EndDate BETWEEN StarD AND EndD) OR
334 (StarD >= StartDate AND EndD <= EndDate));
335 END //
336
337DELIMITER ;
338
339SELECT * FROM Booking;
340CALL AvaliableCars('2018-01-02', '2018-01-15');
341#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.
342DROP PROCEDURE IF EXISTS ProTwo;
343CREATE PROCEDURE ProTwo(Car INT, Cusid INT, StartD DATE, EndD DATE)
344BEGIN
345 SELECT COUNT(*) AS AV FROM Booking WHERE Car = CarNumber AND Cusid = CustomerNr AND (StartD BETWEEN StartDate AND EndDate)AND
346 (EndD BETWEEN StartDate AND EndDate);
347END;
348
349CALL ProTwo(6,1,'2018-01-02', '2018-01-14');
350
351
352 #Triggers
353#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.
354ALTER TABLE Customers ADD COLUMN NRBookings INT DEFAULT 0;
355SELECT * FROM Customers;
356DELIMITER //
357DROP TRIGGER IF EXISTS TrigONE;
358CREATE TRIGGER TrigONE AFTER INSERT ON Booking FOR EACH ROW
359 BEGIN
360 UPDATE Customers SET Customers.NRBookings = Customers.NRBookings + 1 WHERE CustomerNr=NEW.CustomerNr;
361 END//
362
363 DELIMITER ;
364#Would it be possible to do this trigger with a BEFORE trigger? Why/Why not?
365
366 #inte klarat att lösa
367#Show all cars that has never been booked.
368#Alter the previous view, with the condition that the cars have to be available for at least 3 days of renting.