144 lines
3.0 KiB
Transact-SQL
144 lines
3.0 KiB
Transact-SQL
--- 1.
|
|
CREATE DATABASE Airbnb
|
|
|
|
CREATE TABLE Customers(
|
|
id int primary key,
|
|
username VARCHAR(100),
|
|
nationality VARCHAR(100),
|
|
dateofbirth DATE,
|
|
)
|
|
ALTER TABLE Customers
|
|
ADD UNIQUE (username);
|
|
|
|
CREATE TABLE Emails(
|
|
id int primary key,
|
|
email VARCHAR(256),
|
|
userid int FOREIGN key REFERENCES Customers(id)
|
|
)
|
|
|
|
CREATE TABLE Properties(
|
|
id int primary key,
|
|
name varchar(100),
|
|
decription varchar(100),
|
|
checkin Time,
|
|
checkout Time,
|
|
numberofpeople int,
|
|
price decimal(18,2),
|
|
freecancellation bit
|
|
)
|
|
|
|
CREATE TABLE Bookings(
|
|
id int primary key,
|
|
client int FOREIGN key REFERENCES Customers(id),
|
|
property int FOREIGN key REFERENCES Properties(id),
|
|
startdate DATE,
|
|
enddate DATE,
|
|
)
|
|
|
|
CREATE TABLE Payments(
|
|
id int primary key,
|
|
amount decimal(18,2),
|
|
dateofpayment DATETIME,
|
|
typeofpayment VARCHAR(100),
|
|
booking int FOREIGN key REFERENCES Bookings(id)
|
|
)
|
|
|
|
--- 2.
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE addPayment @amount int, @typeofpay VARCHAR(100), @booking int
|
|
AS
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM Bookings where id=@booking)
|
|
begin
|
|
RAISERROR('Invalid Booking',17,1);
|
|
end
|
|
DECLARE @TOTAL DECIMAL(18,2);
|
|
SET @TOTAL=(SELECT SUM(amount) FROM Payments p WHERE p.booking= @booking);
|
|
DECLARE @Totaltopay DECIMAL(18,2)
|
|
SET @Totaltopay= (SELECT price*DATEDIFF(DAY,b.startdate,b.enddate) FROM Properties p join Bookings b on p.id=b.property WHERE b.id=@booking)
|
|
IF @TOTAL < @Totaltopay
|
|
BEGIN
|
|
INSERT INTO Payments (id, amount, dateofpayment, typeofpayment, booking)
|
|
VALUES
|
|
(
|
|
(SELECT MAX(id)+1 FROM Payments),
|
|
@amount,
|
|
GETDATE(),
|
|
@typeofpay,
|
|
@booking
|
|
)
|
|
END
|
|
END
|
|
|
|
--- 3.
|
|
GO
|
|
|
|
CREATE VIEW maxBookers
|
|
AS
|
|
SELECT username FROM Customers c join Bookings b on c.id=b.client
|
|
GROUP BY username
|
|
HAVING COUNT(b.id) = (SELECT TOP 1 COUNT(b.id) FROM Customers c join Bookings b on c.id=b.client
|
|
GROUP BY username ORDER BY COUNT(b.id) DESC)
|
|
|
|
GO
|
|
--- 4.
|
|
|
|
CREATE OR ALTER FUNCTION lessThanR (@r int)
|
|
RETURNS int AS
|
|
BEGIN
|
|
DECLARE @res int;
|
|
SET @res = (SELECT COUNT(*) FROM Customers c Join Bookings b on c.id=b.client JOIN Payments p ON p.booking=b.id
|
|
WHERE EXISTS(SELECT * FROM Bookings b2 INNER JOIN Payments p2 on p2.booking=b2.id WHERE p2.typeofpayment='PayPal' and b2.id=b.id)
|
|
GROUP BY c.username HAVING COUNT(b.id)<@r);
|
|
IF ISNULL(@res, 0)=0
|
|
BEGIN
|
|
return 0
|
|
END
|
|
RETURN @res
|
|
END
|
|
|
|
GO
|
|
--- inserts
|
|
|
|
INSERT INTO Customers (id,
|
|
username ,
|
|
nationality ,
|
|
dateofbirth)
|
|
VALUES
|
|
(1,'Andrei','Romania', '2001-01-01'),
|
|
(2,'Mircea','Grecia', '2002-02-02'),
|
|
(3,'Vlad','Danemarca', '2003-03-03')
|
|
|
|
INSERT INTO Emails (id, email, userid)
|
|
VALUES
|
|
(1,'andrei@yahoo.com',1),
|
|
(2,'andrei2@yahoo.com',1),
|
|
(3,'mircea@yahoo.com',2),
|
|
(4,'vlad@yahoo.com',3)
|
|
|
|
|
|
INSERT INTO Properties (
|
|
id,
|
|
name ,
|
|
decription ,
|
|
checkin,
|
|
checkout,
|
|
numberofpeople,
|
|
price,
|
|
freecancellation
|
|
)
|
|
VALUES
|
|
(1,'Pensiune','Descrierre','10:30:00', '09:00:00', 10, 400.50, 1),
|
|
(2,'Hotel','Descrierre','10:30:00', '09:00:00', 5, 1000.50, 0)
|
|
|
|
|
|
INSERT INTO Bookings (id, client, property, startdate, enddate)
|
|
VALUES
|
|
(1,1,1,'2023-11-11','2023-11-12'),
|
|
(2,3,2,'2023-10-10','2023-10-12')
|
|
|
|
|
|
DECLARE @ret int
|
|
EXEC @ret = lessThanR @r=100
|
|
SELECT @ret |