atjB42kD

1# LABB 1:
2# New table: Cars, CarNumber primary key.
3CREATE TABLE Cars (CarNumber VARCHAR(20) NOT NULL, Brand VARCHAR(20) NOT NULL, Model VARCHAR(20) NOT NULL, Color VARCHAR(20) NOT NULL, PricePerDay BIGINT(20) NOT NULL);
4
5SELECT * FROM Cars;
6
7INSERT INTO Cars (CarNumber, Brand, Model, Color, PricePerDay) VALUES
8(1, 'Peugeot', '208', 'Blue', 800),
9(2, 'Peugeot', '3008', 'Green', 700),
10(3, 'Volkswagen', 'Polo', 'Red', 600),
11(4, 'Volvo', 'V70', 'Silver', 1200),
12(5, 'Tesla', 'X', 'Black', 2000),
13(6, 'SAAB', '9-5', 'Green', 850),
14(7, 'Volvo', 'V40', 'Red', 900),
15(8, 'Fiat', '500', 'Black', 1050),
16(9, 'Volvo', 'V40', 'Green', 850),
17(10, 'Fiat', '500', 'Red', 950),
18(11, 'Volkswagen', 'Polo', 'Blue', 700),
19(12, 'BMW', 'M3', 'Black', 1599),
20(13, 'Volkswagen', 'Golf', 'Red', 1500);
21
22DELETE FROM Cars WHERE CarNumber = 'Testing Testing';
23
24SELECT * FROM Cars;
25
26# New table: Customers, CustomerNumber primary key.
27CREATE TABLE Customers (CustomerNumber VARCHAR(20) NOT NULL, Name VARCHAR(20) NOT NULL, BirthDate DATE);
28
29SELECT * FROM Customers;
30
31INSERT INTO Customers (CustomerNumber, Name, BirthDate) VALUES
32(1, 'Alice Andersson', '1990-05-05'),
33(2, 'Oscar Johansson', '1975-08-10'),
34(3, 'Nora Hansen', '1981-10-27'),
35(4, 'William Johansen', '2000-01-17'),
36(5, 'LucÃ-aGarcÃ-a', '1987-12-13'),
37(6, 'Hugo Fernández', '1950-03-16'),
38(7, 'Sofia Rossi', '1995-08-04'),
39(8, 'Francesco Russo', '2000-02-26'),
40(9, 'Olivia Smith', '1972-05-23'),
41(10, 'Oliver Jones', '1964-05-08'),
42(11, 'Shaimaa Elhawary', '1999-12-23'),
43(12, 'Mohamed Elshabrawy', '1997-11-07'),
44(13, 'Jing Wong', '1947-07-15'),
45(14, 'Wei Lee', '1962-09-29'),
46(15, 'Aadya Singh', '1973-01-01'),
47(16, 'Aarav Kumar', '1986-06-28'),
48(17, 'Louise Martin', '1994-04-22'),
49(18, 'Gabriel Bernard', '1969-12-01'),
50(19, 'Emma Smith', '1971-03-18'),
51(20, 'Noah Johnson', '1800-12-16'),
52(21, 'Alice Silva', '1988-12-04'),
53(22, 'Miguel Santos', '1939-12-29');
54
55DELETE FROM Customers WHERE Name = 'Testing Testing';
56
57SELECT * FROM Customers;
58
59# New table: Bookings, CustomerNumber as foreign key.
60# Might add a BookingNumber to identify current booking...
61CREATE TABLE Bookings (CustomerNumber VARCHAR(20) NOT NULL, CarNumber VARCHAR(20) NOT NULL, StartDate DATE, EndDate DATE);
62
63SELECT * FROM Bookings;
64
65INSERT INTO Bookings (CustomerNumber, CarNumber, StartDate, EndDate) VALUES
66(1, 6, '2018-01-02', '2018-01-15'),
67(2, 1, '2018-01-03', '2018-01-05'),
68(4, 3, '2018-01-03', '2018-01-04'),
69(5, 8, '2018-01-04', '2018-01-30'),
70(6, 10, '2018-01-10', '2018-01-13'),
71(1, 1, '2018-01-20', '2018-01-25'),
72(2, 13, '2018-01-21', '2018-01-30'),
73(3, 6, '2018-01-22', '2018-01-30'),
74(1, 2, '2018-01-29', '2018-02-01'),
75(2, 5, '2018-02-02', '2018-02-06'),
76(6, 1, '2018-02-20', '2018-02-25'),
77(7, 6, '2018-02-21', '2018-02-24'),
78(8, 3, '2018-02-21', '2018-02-28'),
79(10, 3, '2018-02-22', '2018-02-26'),
80(9, 12, '2018-02-22', '2018-02-28'),
81(10, 13, '2018-03-01', '2018-03-10'),
82(11, 1, '2018-03-04', '2018-03-09'),
83(10, 3, '2018-03-11', '2018-03-14'),
84(8, 6, '2018-03-14', '2018-03-17'),
85(9, 5, '2018-03-14', '2018-03-30'),
86(7, 12, '2018-03-18', '2018-03-20'),
87(6, 8, '2018-03-18', '2018-04-02');
88
89DELETE FROM Bookings WHERE CustomerNumber = 'Testing Testing';
90
91SELECT * FROM Bookings;
92
93# Selection, Projection and Restriction
94# 1
95SELECT * FROM Customers;
96# 2
97SELECT Name, BirthDate FROM Customers;
98# 3
99SELECT * FROM Cars WHERE PricePerDay > 1000;
100# 4
101SELECT Brand, Model FROM Cars WHERE Brand = 'Volvo';
102# 5
103SELECT Name FROM Customers ORDER BY Name ASC;
104SELECT Name FROM Customers ORDER BY Name DESC;
105# 6
106SELECT Name, BirthDate FROM Customers WHERE BirthDate > '1990-01-01' ORDER BY BirthDate;
107# 7
108SELECT * FROM Cars WHERE Color = 'Red' AND PricePerDay < 1500;
109# 8
110SELECT Name FROM Customers WHERE BirthDate > '1970-01-01' AND BirthDate < '1990-01-01';
111# 9
112SELECT * FROM Bookings WHERE DATEDIFF(EndDate, StartDate) > 6;
113# 10
114SELECT * FROM Bookings WHERE EndDate BETWEEN '2018-02-01' AND '2018-02-25' OR StartDate BETWEEN '2018-02-01' AND '2018-02-25';
115# 11
116SELECT * FROM Customers WHERE Name LIKE 'O%';
117
118# Aggregated Functions
119# 1
120SELECT AVG(PricePerDay) FROM Cars;
121# 2
122SELECT SUM(PricePerDay) FROM Cars;
123# 3
124SELECT AVG(PricePerDay) FROM Cars WHERE Color = 'Red';
125# 4
126SELECT SUM(PricePerDay), Color FROM Cars GROUP BY Color;
127# 5
128SELECT COUNT(PricePerDay), Color FROM Cars WHERE Color = 'Red';
129# 6
130SELECT COUNT(PricePerDay), Color FROM Cars GROUP BY Color;
131# 7
132SELECT Model, PricePerDay FROM Cars ORDER BY PricePerDay DESC LIMIT 1;
133
134# Joins
135# 1
136SELECT Brand, Model, CustomerNumber, StartDate, EndDate FROM Cars, Bookings;
137# 2
138# 3
139# 4
140# 5
141# 6
142# 7
143# 8
144# 9
145# 10
146
147# Nested Queries
148# 1
149# SELECT Brand, Model, PricePerDay FROM Cars WHERE PricePerDay > AVG(PricePerDay);
150# 2
151# 3
152# 4
153
154# IN
155# 1
156SELECT Brand, Model, PricePerDay FROM Cars WHERE PricePerDay IN(700, 800, 850);
157# 2
158SELECT Name, BirthDate FROM Customers WHERE YEAR(BirthDate) IN (1990, 1995, 2000);
159# 3
160SELECT * FROM Bookings WHERE StartDate IN('2018-01-03', '2018-02-22','2018-03-18');
161
162# BETWEEN
163# 1
164SELECT * FROM Cars WHERE PricePerDay BETWEEN 600 AND 1000;
165# 2
166SELECT * FROM Customers WHERE YEAR(BirthDate) BETWEEN 1960 AND 1980;
167# 3
168SELECT * FROM Bookings WHERE DATEDIFF(EndDate, StartDate) BETWEEN 2 AND 4;
169
170# MIX OF EVERYTHING
171# 1
172# 2
173# 3
174
175# DELETE, UPDATE, ALTER & INSERT
176# 1
177# 2
178# 3
179# 4
180# 5
181
182# VIEW
183# 1
184# 2
185# 3
186# 4
187# 5
188
189# DROP
190# 1
191# 2
192# 3
193# 4
194
195# LABB 2:
196# USER-DEFINED FUNCTIONS
197# 1
198
199DROP FUNCTION IF EXISTS CarAvailable;
200CREATE FUNCTION CarAvailable(SearchedStartDate DATE, SearchedEndDate DATE, SearchedCarNumber VARCHAR(20))
201    RETURNS INTEGER NOT DETERMINISTIC
202    IF (SELECT COUNT(*) Bookings WHERE CarNumber = SearchedCarNumber AND Bookings.StartDate BETWEEN SearchedStartDate AND SearchedEndDate OR Bookings.EndDate BETWEEN SearchedStartDate AND SearchedEndDate) > 0 THEN
203        RETURN 1;
204    ELSE
205        RETURN 0;
206    END IF;
207
208SELECT CarAvailable('2018-01-03', '2018-01-05', 1);
209
210# 2
211# 3
212
213# STORED PROCEDURES
214# 1
215# 2
216
217# TRIGGERS
218# 1
219# 2
220
221DROP TABLE Cars;
222DROP TABLE Customers;
223DROP TABLE Bookings;