ASSESSMENT > Vellore Institute of Technology_ ITE 1003 DATA BASE MANAGEMENT SYSTEM ASSESMENT-3

Document Content and Description Below

DATA BASE MANAGEMENT SYSTEM ASSESMENT-3 NAME:MICAH JOSEPH REG NO:19BIT0404 Nested Queries: 1. List the airplane id, type and company name. SELECT AIRPLANE_ID,AIRPLANE_TYPE_NAME,COMPANY FROM AIRP... LANE,AIRPLANE_TYPE WHERE AIRPLANE.AIRPLANE_TYPE = AIRPLANE_TYPE.AIRPLANE_TYPE_NAME AND AIRPLANE_TYPE IN (SELECT AIRPLANE_TYPE_NAME FROM AIRPLANE_TYPE);2. List the airline for which fare is more than 8000. SELECT FLIGHT.AIRLINE FROM FLIGHT ,FARE WHERE FLIGHT.FLIGHT_NUMBER = FARE.FLIGHT_NUMBER AND FLIGHT.FLIGHT_NUMBER IN(SELECT FLIGHT_NUMBER FROM FARE WHERE AMOUNT>8000); 3. List the airport names which are arrival ports for at least 3 flights.SELECT COUNT(*) FROM AIRPORT A JOIN LEG_INSTANCE L ON A.AIRPORT_CODE=L.ARRIVAL_AIRPORT_CODE GROUP BY NAME HAVING COUNT(*)>=3;4. Retrieve airport code that is greater than all airplane id of given airplane type. select a.airport_code from airport a,airplane a1 where length(a.airport_code)>length(a1.airplane_id) and a1.airplane_type='&airplane_type'; 5. List the flight number that has no booking.(select flight_number from flight) minus (select flight_number from seat_reservation );6. Retrieve the airline names that has at least two bookings. select p.airline from flight p join seat_reservation s on p.flight_number=s.flight_number group by airline having count(*)>=2;7. Retrieve the airport code that are only arrival and not departure port. select s.airport_code from airport s ,flight_leg p where s.airport_code=p.arrival_airport_code and s.airport_code not in (select departure_airport_code from flight_leg) and s.airport_code in(select arrival_airport_code from flight_leg);8. Retrieve the flight numbers that arrive in all the cities of Tamilnadu. select f.flight_number from flight_leg f,airport s where s.airport_code=f.arrival_airport_code and s.state=’TAMILNADU’; 9. Retrieve the country which has more than 10 airports. select country from airport group by country having count(*)>=10; 10. Find the airline which has highest number of intermediate stoppings.select airline from flight where flight_number in(select flight_number from flight_leg group by flight_number having count(*)=(select max(count(leg_number)) from flight_leg group by flight_number));11. List the flight numbers and weekdays of all flights or flight legs that depart from Pune Airport (airport code ,‘PNQ’) and arrive in Los Angeles International Airport (airport code ,’LAX’). select s.flight_number,s.weekdays from flight s,flight_leg p where s.flight_number=p.flight_number and departure_airport_code='C234' and arrival_airport_code='C789';12. List all fare information for flight number ‘CO197’. SELECT * FROM FARE WHERE FLIGHT_NUMBER='C0197';13. Retrieve the number of available seats for flight number ’CO197’ on ‘30- nov-2020’. /*Input of the question is changed according to my table data.*/ SELECT LEG_NUMBER,NUMBER_OF_AVAILABLE_SEATS FROM LEG_INSTANCE WHERE FLIGHT_NUMBER='A567' AND DATE1='30- NOV-20'; [Show More]

Last updated: 1 year ago

Preview 1 out of 16 pages