Answered step by step
Verified Expert Solution
Question
1 Approved Answer
Table Structure: flight COLUMN NAME DATA TYPE SIZE Constraint Fid Number 3 Primary key Fname Varchar 2 10 Splace Varchar 2 8 Dplace Varchar 2
Table Structure: flight COLUMN NAME DATA TYPE SIZE Constraint Fid Number 3 Primary key Fname Varchar 2 10 Splace Varchar 2 8 Dplace Varchar 2 8 Tdate date Seats Number 3 Note: Fid is Fight ID, Fname is Flight Name, Splace is a source place where the flight starts Dplace is destination place where the flight will reach, Tdate is tremel date of the flight and seats are the number of seats awilable in the flight Records inserted in to the flight table Fid Fname Splace Dplace Tdate Seats 101 Oman Air Muscat Delhi 20-Jan-2021 78 102 Air India Muscat Chennai 31-Dec-2020 null 201 Indigo Dubai Muscat 12-Feb-2021 93 103 Oman Air Chennai Dubai 23-Dec-2020 82 205 Air India Delhi Muscat 14-Feb-2021 115 202 Indigo Chennai Delhi 23-Jan-2021 null Table Structure: cust COLUMN NAME DATA TYPE SIZE Constraint Cid Varchar2 4 Cname Varchar2 10 Ticket Number 3 Amount Number 6,3 Fid Number 3 Foreign Key Note: Cid is Customer D, Oname is Customer Name, Ticket is number of tickets booked, Amount is ticket amount and Fid is Flight ID which refers the Fid column from flight table. Records inserted into the cust table Cid Cname Ticket Amount Fid C101 Ahmed 5 572.400 101 C102 Aisha 4 426.300 102 C103 Waleed null 143.800 201 C104 Mana 7 649.500 205 C108 Moza 5 438.600 102 2.Write an SQL query to display the flight Id, travel date and the number of seats available in the flight (if number of seats available value is 78, consider it as null and replace all the null values with 100) only for the flights whose travel date is before 22nd January 2021. [3 Marks] 3.Write an SQL query to display the biggest customer id, average value of ticket amount and standard deviation of number of tickets booked rounded with 3 decimal places for the customers whose name starts with the letter "M" or ends with the letter "d". [4 Marks]
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started