Given the following relations
TRAIN (NAME, START, DEST)
TICKET (PNRNO., START, DEST, FARE)
PASSENGER (NAME, ADDRESS, PNRNO.)
Write SQL expressions for the following queries:
Note: Assume NAME of Train is a column of Ticket.
(i) List the names of passengers who are travelling from the begin to the destination station of the train.
(ii) List the names of passengers who have a return journey ticket.
(iii) Insert a new Shatabti train from Delhi to Bangalore.
(iv) Cancel the ticket of Tintin.
(i) SELECT P.NAME FROM TRAIN T, TICKET I, PASSENGER P
WHERE P.PNRNO = I.PNRNO AND T.NAME = I.NAME
AND T.START = I.START AND T.DEST = I.DEST
(ii) SELECT NAME FROM PASSENGER
WHERE PNRNO IN (SELECT DISTINCT A.PNRNO
FROM TICKET A, TICKET B WHERE A.PNRNO = B.PNRNO
AND A.START = B.DEST AND A.DEST = B.START)
(iii) INSERT INTO TRAIN
VALUES(‘Shatabdi', ‘Delhi', ‘Banglore'
(iv) DELETE FROM TICKET
WHERE PNRNO = (SELECT PNRNO FROM PASSENGER
WHERE NAME = ‘Tintin')