QUERY.sql

-- =========================================================================
-- SYSTEM: Football Ticket Booking System Database Setup
-- =========================================================================

DROP TABLE IF EXISTS Bookings;
DROP TABLE IF EXISTS Matches;
DROP TABLE IF EXISTS Users;


-- =========================================================================
-- 1. CREATE USERS TABLE
-- =========================================================================
CREATE TABLE Users (
    -- user_id INT PRIMARY KEY,
    user_id INT,
    full_name VARCHAR(100) NOT NULL,

    -- email VARCHAR(100) UNIQUE NOT NULL,
    email VARCHAR(100) NOT NULL,
  
    role VARCHAR(50) NOT NULL,
    phone_number VARCHAR(50),

    CONSTRAINT pk_user_id PRIMARY KEY (user_id),

    CONSTRAINT unique_email UNIQUE (email),
  
    CONSTRAINT chk_user_role
    CHECK ( role IN ('Ticket Manager', 'Football Fan') )
);


-- =========================================================================
-- 2. CREATE MATCHES TABLE
-- =========================================================================

CREATE TABLE Matches (
    -- match_id INT PRIMARY KEY,
    match_id INT,

    fixture VARCHAR(150) NOT NULL,
    tournament_category VARCHAR(100) NOT NULL,

    -- base_ticket_price int NOT NULL,
    base_ticket_price DECIMAL(10,2) NOT NULL,
    match_status VARCHAR(50) NOT NULL,

    
    CONSTRAINT pk_match_id PRIMARY KEY (match_id),
    
    CONSTRAINT chk_ticket_price
    CHECK (base_ticket_price >= 0),

    CONSTRAINT chk_match_status
    CHECK (
        match_status IN (
            'Available',
            'Selling Fast',
            'Sold Out',
            'Postponed'
  
        )
    )
);


-- =========================================================================
-- 3. CREATE BOOKINGS TABLE
-- =========================================================================

CREATE TABLE Bookings (
    -- booking_id INT PRIMARY KEY,
    booking_id INT,

    user_id INT NOT NULL,
    match_id INT NOT NULL,
    seat_number VARCHAR(50),
    payment_status VARCHAR(50),

    -- total_cost int NOT NULL,
    total_cost DECIMAL(10,2) NOT NULL,

    CONSTRAINT pk_booking_id PRIMARY KEY (booking_id),
  
    CONSTRAINT fk_booking_user
    FOREIGN KEY (user_id)
    REFERENCES Users(user_id),

    CONSTRAINT fk_booking_match
    FOREIGN KEY (match_id)
    REFERENCES Matches(match_id),

    CONSTRAINT chk_total_cost
    CHECK (total_cost >= 0),

    CONSTRAINT chk_payment_status
    CHECK (
        payment_status IN (
            'Confirmed',
            'Pending',
            'Cancelled',
            'Refunded'
        )
    )
);


-- =========================================================================
-- DATA SEEDING: INSERT SAMPLE DATA INTO USERS
-- =========================================================================
INSERT INTO Users (user_id, full_name, email, role, phone_number)
VALUES
(1, 'Tanvir Rahman', 'tanvir@mail.com', 'Football Fan', '+8801711111111'),
(2, 'Asif Haque', 'asif@mail.com', 'Football Fan', '+8801722222222'),
(3, 'Sajjad Rahman', 'sajjad@mail.com', 'Ticket Manager', '+8801733333333'),
(4, 'Jannat Ara', 'jannat@mail.com', 'Football Fan', NULL);


-- =========================================================================
-- DATA SEEDING: INSERT SAMPLE DATA INTO MATCHES
-- =========================================================================
INSERT INTO Matches (
    match_id,
    fixture,
    tournament_category,
    base_ticket_price,
    match_status
)
VALUES
(101, 'Real Madrid vs Barcelona', 'Champions League', 150.00, 'Available'),
(102, 'Man City vs Liverpool', 'Premier League', 120.00, 'Selling Fast'),
(103, 'Bayern Munich vs PSG', 'Champions League', 130.00, 'Available'),
(104, 'AC Milan vs Inter Milan', 'Serie A', 90.00, 'Sold Out'),
(105, 'Juventus vs Roma', 'Serie A', 80.00, 'Available');


-- =========================================================================
-- DATA SEEDING: INSERT SAMPLE DATA INTO BOOKINGS
-- =========================================================================
INSERT INTO Bookings (
    booking_id,
    user_id,
    match_id,
    seat_number,
    payment_status,
    total_cost
)
VALUES
(501, 1, 101, 'A-12', 'Confirmed', 150.00),
(502, 1, 102, 'B-04', 'Confirmed', 120.00),
(503, 2, 101, 'A-13', 'Confirmed', 150.00),
(504, 2, 101,  NULL, NULL, 150.00),
(505, 3, 102, 'C-20', 'Pending', 120.00);



-- --- --- ------ ----
-- QUERY ANSWER PART --
-- --- --- ------ ----


-- 01

SELECT
    match_id,
    fixture,
    ROUND(base_ticket_price) AS base_ticket_price
FROM Matches
WHERE tournament_category = 'Champions League'
    AND match_status = 'Available';



--  02

SELECT
    user_id,
    full_name,
    email
FROM Users
WHERE full_name ILIKE 'Tanvir%'
   OR full_name ILIKE '%Haque%';



--  03

SELECT
    booking_id,
    user_id,
    match_id,
    COALESCE(payment_status, 'Action Required') AS systematic_status
FROM Bookings
WHERE payment_status IS NULL;


--  04

SELECT
    Bookings.booking_id,
    Users.full_name,
    Matches.fixture,
    ROUND(Bookings.total_cost) AS total_cost
FROM Bookings 
INNER JOIN Users 
    ON Bookings.user_id = Users.user_id
INNER JOIN Matches 
    ON Bookings.match_id = Matches.match_id;


--  05

SELECT
    Users.user_id,
    Users.full_name,
    Bookings.booking_id
FROM Users 
LEFT JOIN Bookings 
    ON Users.user_id = Bookings.user_id;


--  06

SELECT
    booking_id,
    match_id,
    ROUND(total_cost) AS total_cost
FROM Bookings
WHERE total_cost >
(
    SELECT AVG(total_cost)
    FROM Bookings
);



--  07

SELECT
    match_id,
    fixture,
    ROUND(base_ticket_price) AS base_ticket_price
FROM Matches
ORDER BY base_ticket_price DESC
OFFSET 1
LIMIT 2;