Untitled Page

CREATE DATABASE IF NOT EXISTS experiment6;
USE experiment6;

-- 1. Create Passenger table
CREATE TABLE passenger2 (
    passportid INT PRIMARY KEY,
    name VARCHAR(30),
    age INT,
    sex CHAR(1),
    city VARCHAR(30)
);

-- 2. Insert Passenger records
INSERT INTO passenger2 VALUES
(82302, 'Smith', 23, 'M', 'Hyderabad'),
(82303, 'Neha', 23, 'F', 'Hyderabad'),
(82304, 'Neha', 35, 'F', 'Hyderabad'),
(82306, 'Ramu', 40, 'M', 'Hyderabad'),
(82308, 'Aakash', 40, 'M', 'Hyderabad'),
(82402, 'Aravind', 42, 'M', 'Hyderabad'),
(82403, 'Avinash', 42, 'M', 'Hyderabad'),
(82502, 'Ramesh', 23, 'M', 'Hyderabad'),
(82602, 'Rajesh', 23, 'M', 'Hyderabad');

-- 3. Create Reservation table
CREATE TABLE reservation2 (
    pnr_no INT PRIMARY KEY,
    booking_date DATETIME,
    seat_no INT,
    source VARCHAR(30),
    phone VARCHAR(20)
);

-- 4. Insert Reservation records
INSERT INTO reservation2 VALUES
(10201, '2012-02-20 10:20:25', 5, 'HYD', '9654235242'),
(10202, '2012-02-22 10:22:25', 5, 'HYD', '9654232451'),
(10203, '2012-03-22 10:30:25', 5, 'DELHI', '9654587960'),
(10204, '2013-03-22 11:30:25', 5, 'CHENNAI', '9845761254');

-- 5. Create PassengerTicket table
CREATE TABLE passengerticket (
    ticketno INT PRIMARY KEY,
    passportid INT,
    FOREIGN KEY (passportid) REFERENCES passenger2(passportid)
);

-- 6. Insert Ticket records
INSERT INTO passengerticket VALUES
(501, 82302),
(502, 82303),
(503, 82304),
(504, 82306),
(505, 82308),
(506, 82402),
(507, 82403),
(508, 82502),
(509, 82602);

-- QUERY 1: Display unique PNR numbers
SELECT DISTINCT pnr_no
FROM reservation2;

-- QUERY 2: Display names of male passengers using IN
SELECT p.name
FROM passenger2 p
WHERE p.passportid IN (
    SELECT p2.passportid
    FROM passenger2 p2
    WHERE p2.sex = 'M'
);

-- QUERY 3: Display ticket numbers and passenger names
SELECT t.ticketno, p.name
FROM passengerticket t
JOIN passenger2 p
ON t.passportid = p.passportid;

-- QUERY 4: Display ticket numbers for names
-- starting with R and ending with H
SELECT t.ticketno
FROM passengerticket t
JOIN passenger2 p
ON t.passportid = p.passportid
WHERE p.name LIKE 'R%H';

-- QUERY 5: Names of passengers aged between 30 and 45
SELECT name
FROM passenger2
WHERE age BETWEEN 30 AND 45;

-- QUERY 6: Display passengers whose names begin with A
SELECT name
FROM passenger2
WHERE name LIKE 'A%';

-- QUERY 7: Display passenger names in sorted order
SELECT name
FROM passenger2
ORDER BY name;