(Solved) : Consider Following Relational Schemas Passenger Pname Varchar2 10 Pnrnumber Number 8 Amoun Q31677065 . . .
Consider the following relational schemas
Passenger(pname: varchar2(10); pnr_number: number(8); amount:number(5,2); doj: date; status: varchar2(3))
Reserved_for(pnr_number: number(8); train_no: number(5))
Train(train_no: number(5); train_name: varchar2(10); source:varchar2(15); destination: varchar2(15))
Note: ‘doj’ stands for date of journey.
Status may be ‘cf’(confirmed), ‘ncf’ (not confirmed), and‘c’(cancelled).
Do the following
1. Create the above tables with composite primary on Passengertable and primary key on Train table.
Enforce the foreign keys on fields of ‘Reserved_for’ table.
2. See that ‘status’ field value should be any one of these ‘cf’ ,’ncf’, and ‘c’. Also see that ‘source’ and
‘destination’ fields should never be empty. Ensure that the date ofjourney should be not more than 30
days from date of reservation.
3. Add at least 6 records to each table as per the requirements ofqueries given as below. (e-5)
Mandatory: Display the structure and content of each table for eachchange and store it in your file to
be uploaded.
4. List tickets numbers along with train names that travels from‘Bangalore’ to ‘Chennai’ on journey
dates between 13-November-2015 and 15-November-2015.
5. Display date of journey field values in the format ‘ddth ofmonth yyyy’ by adding one day to it.
6. Get the total number of tickets reserved on each date ofjourney.
7. List the ticket numbers whose amount is more than the amount ofevery ticket on 13-November-
2015. (Sub Query)
8. Create a virtual table to display ticket numbers along withtrain numbers and train names on a
particular date of journey those travel from ‘Bangalore’ to‘Tirupati’
Answer the following queries in SQL and obtain required output(Output for each query be there in
your file and it carries half of the marks allotted to thequery)
Expert Answer
Answer to Consider Following Relational Schemas Passenger Pname Varchar2 10 Pnrnumber Number 8 Amoun Q31677065 . . .
OR