Question

Using the tables provided above, provide SQL statements for the following queries.

NOTE: YOU DO NOT NEED TO INSERT MORE DATA INTO THE TABLES. The list of tables available for this assignment is the following MODEL(modellD, economySeats, buisnessSeats, firstClassSeats, cargoCapacity, fuelCapacity, length, wingspan, serviceHours) LOCATION(airportCode, country, address, phone) TICKET ticketNum, luggageLimit, seatNum, classCode, medicalCondition, mealChoice, customerlD, flightID) ROUTE(routelD, description, arriveAirportCode, departAirportCode) IRREGULAR_EVENT(eventNumber,flightlD, eventDate TIme, eventDescription) SERVICE(serviceDate,airfD, description, cost) AIRCRAFT (aicraftlD, mailCargoCapacity, numMedPacks, numDefibritlators, haulType, modellD NEACC MEMBER(memberlD, flightGoldPoints) STAFF(stafflD, name, address, email, phone, passportNum, pilotYesNo, prevHrsPilotExp, attendantYesNo, memberlD) CUSTOMER(customerlD, name, address, country, email, phone, birthdate, passportNum, memberID) FLIGHT flightID, estDepartureDateTime, actDepartDateTime, actArriveDateTime, avgSpeed, avgHeight, estDuration, estFuel, haulType, captainStafflD,firstOfficerStafflD, routelD, aircrafilD) ADDITIONAL_PILOT stafD AightlD, activityCode, activityDesc) HOSTING(staffID, AlightlD) PILOT_QUALIFICATION(qualification, staffID) NOTE: PK is printed underlined and FK is printed in italics. i. Display a table of all customers who have purchased tickets for at least 5 flights, excluding any flights that have experienced irregular events. Display the customer ID and number of tickets purchased. 15 marks] . For each flight that has already departed, display the flight ID, actual departure date, actual departure time (in a separate column), and the number of days that have passed between now (system date) and the departure. The displayed table should have 4 columns. Display the date in the same format as Ό1st January 2019, and display the time in 12-hour format ending in am' or pm' with hours and minutes shown (but not seconds). I5 marks

Answer 1

----- I

SELECT customerID,count(ticketNum) As TicketCount FROM Ticket WHERE flightid NOT IN (SELECT flightid from Irregular_event) GROUP BY Customerid HAVING count(DISTINCT flightid)>=5;

in above query we have filtered flight with irregular events using sub query. Group by helps to get count of ticket's and Having helps to filter customers who have not purchased 5 flight ticket's. DISTINCT key word in Having clause helps to different flight I'd.

-------- j

SELECT flightid,

CAST(DAY(actDepartDateTime) AS VARCHAR(2)) + 'st ' + DATENAME(MM, actDepartDateTime) + ' ' + RIGHT(CAST(YEAR(actDepartDateTime) AS VARCHAR(4)), 2) AS DepartDate,

RIGHT(CONVERT(VARCHAR(20), actDepartDateTime, 100),7) AS DepartTime,

DATEDIFF(day,actDepartDateTime,GETDATE()) As NumDays FROM flight WHERE estDepartDateTime < GETDATE();

In SQL server GETDATE helps to get current system time. In Oracle you need to use SYSDATE for current date time