3.sql

CREATE DATABASE TOURISTRESORT;
USE TOURISTRESORT;


CREATE TABLE EMPLOYEE(
    Ename VARCHAR(50),
    Eaddress VARCHAR(50),
    Eaadharno BIGINT CONSTRAINT emp_aadhar_check CHECK(Eaadharno BETWEEN 100000000000 AND 999999999999),
    Emobno BIGINT,
    Eemailid VARCHAR(50),
    Ejoindate DATE,
    Esalary FLOAT,
    PRIMARY KEY(Eaadharno)
);


INSERT INTO EMPLOYEE (Ename, Eaddress, Eaadharno, Emobno, Eemailid, Ejoindate, Esalary) VALUES
('John Doe', '123 Elm Street, Springfield', 123456789012, 9876543210, 'john.doe@example.com', '2024-01-15', 55000.00),
('Jane Smith', '456 Oak Avenue, Springfield', 234567890123, 9123456789, 'jane.smith@example.com', '2024-02-20', 62000.00),
('Alice Johnson', '789 Maple Road, Springfield', 345678901234, 9456781234, 'alice.johnson@example.com', '2024-03-10', 59000.00),
('Bob Brown', '101 Pine Lane, Springfield', 456789012345, 9765432109, 'bob.brown@example.com', '2024-04-25', 50000.00);

SELECT * FROM EMPLOYEE;


CREATE TABLE RESIDENT(
    Rname VARCHAR(50),
    Raddress VARCHAR(50),
    Raadharno BIGINT CONSTRAINT res_aadhar_check CHECK(Raadharno BETWEEN 100000000000 AND 999999999999),
    Rgender VARCHAR(20),
    Rage INT CHECK(Rage > 0),
    Rmobno BIGINT,
    Remailid VARCHAR(50),
    PRIMARY KEY(Raadharno)
);


INSERT INTO RESIDENT VALUES
('Michael Anderson', '321 Birch Street, Springfield', 101234567890, 'Male', 35, 9876543210, 'michael.anderson@example.com'),
('Emily Davis', '654 Pine Avenue, Springfield', 202345678901, 'Female', 29, 9123456789, 'emily.davis@example.com'),
('Lucas Martinez', '987 Cedar Road, Springfield', 303456789012, 'Male', 42, 9456781234, 'lucas.martinez@example.com'),
('Sophia Wilson', '123 Maple Lane, Springfield', 404567890123, 'Female', 31, 9765432109, 'sophia.wilson@example.com');


SELECT * FROM RESIDENT;


CREATE TABLE ROOM(
    Rno INT,
    Rtype VARCHAR(50) CHECK(Rtype IN ('AC', 'NON-AC')),
    Rcapacity VARCHAR(50) CHECK(Rcapacity IN ('Single-bed', 'Double-bed')),
    PRIMARY KEY (Rno)
);


INSERT INTO ROOM  VALUES
(101, 'AC', 'Single-bed'),
(102, 'NON-AC', 'Double-bed'),
(103, 'AC', 'Double-bed'),
(104, 'NON-AC', 'Single-bed'),
(105, 'AC', 'Double-bed'),
(106, 'AC', 'Single-bed');


SELECT * FROM ROOM;


CREATE TABLE BOOKING(
    Bid INT,
    Rno INT,
    Raadharno BIGINT,
    checkindate DATE,
    checkoutdate DATE,
    FOREIGN KEY(Rno) REFERENCES ROOM(Rno) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY(Raadharno) REFERENCES RESIDENT(Raadharno) ON DELETE CASCADE ON UPDATE CASCADE,
    PRIMARY KEY(Bid)
);


INSERT INTO BOOKING  VALUES
(1, 101, 101234567890, '2024-08-01', '2024-08-07'),
(2, 102, 202345678901, '2024-08-10', '2024-08-15'),
(3, 103, 303456789012, '2024-08-20', '2024-08-25'),
(4, 104, 404567890123, '2024-09-01', '2024-09-05'),
(5, 101, 101234567890, '2024-09-10', '2024-09-15'),
(6, 103, 101234567890, '2024-09-20', '2024-09-25'),
(7, 105, 101234567890, '2024-10-01', '2024-10-05');


SELECT * FROM BOOKING;


CREATE TABLE FOODITEM(
    Fid INT,
    Fitem VARCHAR(50),
    Ftype VARCHAR(50) CHECK(Ftype IN ('Vegetarian', 'Non-vegetarian')),
    Fprice FLOAT,
    PRIMARY KEY(Fid)
);


INSERT INTO FOODITEM VALUES
(1, 'Veg Pizza', 'Vegetarian', 50.00),
(2, 'Chicken Burger', 'Non-vegetarian', 100.00),
(3, 'Pasta', 'Vegetarian', 200.00),
(4, 'Beef Steak', 'Non-vegetarian', 150.00);


SELECT * FROM FOODITEM;


CREATE TABLE COMPANION(
    Raadharno BIGINT,
    Cname VARCHAR(50),
    PRIMARY KEY(Raadharno, Cname),
    FOREIGN KEY(Raadharno) REFERENCES RESIDENT(Raadharno) ON DELETE CASCADE
);


INSERT INTO COMPANION VALUES
(101234567890, 'Chris Evans'),
(101234567890, 'Taylor Swift'),
(101234567890, 'Robert Downey'),
(101234567890, 'Scarlett Johansson'),
(202345678901, 'Emma Watson');


SELECT * FROM COMPANION;


CREATE TABLE FOODORDER(
    Orderid INT,
    Bid INT,
    Fid INT,
    Quantity INT,
    FOREIGN KEY (Bid) REFERENCES BOOKING(Bid) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (Fid) REFERENCES FOODITEM(Fid) ON DELETE CASCADE ON UPDATE CASCADE,
    PRIMARY KEY(Orderid)
);


INSERT INTO FOODORDER VALUES
(1, 1, 1, 2),
(2, 1, 2, 1),
(3, 2, 3, 3),
(4, 3, 4, 1),
(5, 5, 1, 2), 
(6, 2, 4, 1), 
(7, 4, 4, 2);


SELECT * FROM FOODORDER;

-- Query 1: Residents with more than three companions in a single booking
SELECT R.Raadharno, R.Rname, R.Raddress, R.Rgender, R.Rage, R.Rmobno, R.Remailid
FROM RESIDENT R
JOIN COMPANION C ON R.Raadharno = C.Raadharno
GROUP BY R.Raadharno, R.Rname, R.Raddress, R.Rgender, R.Rage, R.Rmobno, R.Remailid
HAVING COUNT(C.Cname) > 3;

-- Query 2: Residents with the number of companions for a specified period
SELECT R.Raadharno, R.Rname, R.Raddress, R.Rgender, R.Rage, R.Rmobno, R.Remailid,
COUNT(C.Cname) AS number_of_companions FROM RESIDENT R
JOIN BOOKING B ON R.Raadharno = B.Raadharno
LEFT JOIN COMPANION C ON R.Raadharno = C.Raadharno
WHERE B.checkindate BETWEEN '2024-08-01' AND '2024-08-31'
GROUP BY R.Raadharno, R.Rname, R.Raddress, R.Rgender, R.Rage, R.Rmobno, R.Remailid;

-- Query 3: Residents who reserved more than two AC rooms in at least two different bookings
SELECT R.Raadharno, R.Rname, R.Raddress, R.Rgender, R.Rage, R.Rmobno, R.Remailid
FROM RESIDENT R
JOIN BOOKING B ON R.Raadharno = B.Raadharno
JOIN ROOM RO ON B.Rno = RO.Rno
WHERE RO.Rtype = 'AC'
GROUP BY R.Raadharno, R.Rname, R.Raddress, R.Rgender, R.Rage, R.Rmobno, R.Remailid
HAVING COUNT(DISTINCT B.Bid) >= 2 AND COUNT(RO.Rtype) > 2;

SELECT F.Fid, F.Fitem, F.Ftype, F.Fprice, COUNT(DISTINCT B.Raadharno) AS resident_count
FROM FOODITEM F
JOIN FOODORDER FO ON F.Fid = FO.Fid
JOIN BOOKING B ON FO.Bid = B.Bid
GROUP BY F.Fid, F.Fitem, F.Ftype, F.Fprice
HAVING COUNT(DISTINCT B.Raadharno) = (
SELECT MAX(resident_count) FROM (
SELECT COUNT(DISTINCT B.Raadharno) AS resident_count
FROM FOODITEM F JOIN FOODORDER FO ON F.Fid = FO.Fid
JOIN BOOKING B ON FO.Bid = B.Bid GROUP BY F.Fid ) AS MaxCounts
);

-- Query to get food items ordered by the minimum number of distinct residents
SELECT F.Fid, F.Fitem, F.Ftype, F.Fprice, COUNT(DISTINCT B.Raadharno) AS resident_count
FROM FOODITEM F
JOIN FOODORDER FO ON F.Fid = FO.Fid
JOIN BOOKING B ON FO.Bid = B.Bid
GROUP BY F.Fid, F.Fitem, F.Ftype, F.Fprice
HAVING COUNT(DISTINCT B.Raadharno) = (
SELECT MIN(resident_count) FROM (
SELECT COUNT(DISTINCT B.Raadharno) AS resident_count
FROM FOODITEM F JOIN FOODORDER FO ON F.Fid = FO.Fid
JOIN BOOKING B ON FO.Bid = B.Bid GROUP BY F.Fid ) AS MinCounts
);


-- Query 5: Food items ranked by preference within a specified period, based on total quantity ordered
SELECT F.Fid, F.Fitem, F.Ftype, F.Fprice, SUM(FO.Quantity) AS total_quantity
FROM FOODITEM F
JOIN FOODORDER FO ON F.Fid = FO.Fid
JOIN BOOKING B ON FO.Bid = B.Bid
WHERE B.checkindate BETWEEN '2024-08-01' AND '2024-08-31'
GROUP BY F.Fid, F.Fitem, F.Ftype, F.Fprice
ORDER BY total_quantity DESC, F.Fprice ASC;