Design an SQL query to determine what Flights departed with 10 or more passenger

Question

Design an SQL query to determine what Flights departed with 10 or more passengers. In your results, show the Flight Number and the number of passengers per flight. Label the columns in your results using brief terms. Sort your results so that the flight number(s) with the highest number of passengers appears at the top of the results.

Design an SQL query to list passengers flying from JFK who earned the same or more miles per flight (>=) than the average amount of frequent flier miles earned for ANY / ALL passengers flying from JFK (the variable is called miles in the manifest table and should not be confused with the cumulativemiles variable in the frequent flier table). Include two columns in your results: [Passenger Name] and [Miles per Flight] sorted by [Miles per Flight] in descending order. Your query should exclude from the subquery passengers whose average miles is less than or equal to 500 miles (hint: this is not something that you adjust for on a WHERE line since you're using GROUP BY to compute passenger averages).

Design an SQL query to find the 11th ranked passenger in our database, ranked by the total amount of money they spent on airfares. The fact that you can identify the top 10 already will be useful if you want to use it IN a subquery. In your result, just show the name of the 11th ranked passenger and the total amount of money they spent on their airfares. If your code works, you should find that the answer is Richard Coyle with $5,340 but under no circumstances should you hard code either of these two results into your code to produce the answers. Make use of the ORDER BY statement. PLEASE SHOW CODES

TicketNumber RecordLocator FlightNumber FlightDate TicketPrice Seat SeatType Captain FirstOfficer Miles Passenger Number 9000167 Q4165N UA7110 03-Jan-17 $798.00 29C E Schmidt Oscar 6350 21 9008899 V2232G UA200 07-Aug-16 $400.00 5F E Gates Lynch 1100 17 9013465 G4482K UA7110 03-Jan-17 $688.00 14F E Schmidt Oscar 6350 32 9017327 D8348T UA19 08-Aug-16 $9,000.00 1A B Terez Palac 4500 18 9021962 V1281G UA200 07-Aug-16 $232.00 12D E Gates Lynch 1100 18 9024617 V7396V UA500 21-Jan-17 $900.00 15E E Pieter Klass 3200 30 9025865 W4498N UA7111 10-Jan-17 $1,455.00 32B E Schmidt Oscar 6350 33 9028365 K7852P UA2322 11-Oct-16 $300.00 1B E Reilly Horowitz 1250 30 9031984 S5444U UA200 07-Aug-16 $232.00 19A E Gates Lynch 1100 28 9037378 S7969U UA370 09-Feb-17 $920.00 16A E Costos Jones 4230 20 9037937 R1098C UA90 14-Sep-16 $800.00 10F E Browne Locke 6600 15 9038310 C3558Q UA7111 10-Jan-17 $8,000.00 1B B Schmidt Oscar 12700 8 9041086 S5443F UA3710 28-Feb-17 $1,085.00 17F E Peters DaSilva 3900 26 9058470 S1767J UA200 10-Mar-17 $500.00 5A B Browne Lynch 2200 18 9064228 C1447M UA2322 11-Oct-16 $200.00 1A E Reilly Horowitz 1250 28 9067064 U1062F UA370 09-Feb-17 $910.00 12B E Costos Jones 4230 31 9075158 P1099I UA7111 10-Jan-17 $1,900.00 5B EP Schmidt Oscar 6350 26 9090167 C7989W UA333 18-Feb-17 $690.00 20A E Piper Kline 3200 34 9095723 I6381F UA1010 12-Mar-17 $340.00 11C E Rice Oscar 890 32 9097585 B1645C UA4511 20-Feb-17 $1,010.00 15A E Gates Kline 3150 22 9100495 P3310I UA1115 11-Oct-16 $99.00 3A E Phillips Retz 500 14 9100992 N5564C UA556 01-Apr-16 $344.00 22A E Rice Kline 1200 13 9107142 M9454U UA4511 20-Feb-17 $717.00 15B E Gates Kline 3150 34 9108647 G2828M UA178 04-Jan-17 $444.00 18F E Walters Potter 3500 26 9115862 H6805B UA7111 10-Jan-17 $1,450.00 23A E Schmidt Oscar 6350 22 9117607 M6877I UA200 07-Aug-16 $300.00 11A EP Gates Lynch 1100 25 9124250 F8462E UA995 03-Mar-17 $1,200.00 18E E Peters DaSilva 3900 20 9149480 M1052U UA3710 28-Feb-17 $1,310.00 17D E Peters DaSilva 3900 18 9154705 U6560S UA333 18-Feb-17 $650.00 19A E Piper Kline 3200 18 9157697 W8998E UA178 04-Jan-17 $590.00 11G E Walters Potter 3500 22 9165165 V9386S UA556 01-Apr-16 $299.00 11D E Rice Kline 1200 19 9175235 E7196Z UA178 04-Jan-17 $550.00 19D E Walters Potter 3500 29 9179261 D5375R UA2322 11-Oct-16 $399.00 13A EP Reilly Horowitz 1250 6 9185158 J8619U UA320 01-Oct-16 $555.00 10C EP Seville Smith 850 11 9191511 A8346Z UA1010 12-Mar-17 $400.00 11D E Rice Oscar 890 25 9204232 D6435T UA995 03-Mar-17 $1,210.00 18B E Peters DaSilva 3900 33 9211518 J4591U UA991 13-Mar-17 $210.00 8A E Seville Potter 550 22 9213721 S9099N UA556 01-Apr-16 $299.00 21A E Rice Kline 1200 17 9226783 C1470W UA7111 10-Jan-17 $1,450.00 31A E Schmidt Oscar 6350 21 9230449 G6854X UA219 15-Mar-17 $1,540.00 15C E McCarthy Long 4150 25 9235155 Y2788Q UA370 09-Feb-17 $965.00 16B E Costos Jones 4230 35 9243285 W3009J UA995 03-Mar-17 $1,180.00 18D E Peters DaSilva 3900 18 9263646 I8731O UA995 03-Mar-17 $1,200.00 19D E Peters DaSilva 3900 24 9266346 Z1799C UA90 14-Sep-16 $455.00 9C E Browne Locke 6600 18 9266472 K5587V UA200 08-Aug-16 $232.00 11A E Nellis Daly 1100 12 9298622 B4760D UA4511 20-Feb-17 $990.00 16C E Gates Kline 3150 21 9300676 X4450U UA500 21-Jan-17 $820.00 16C E Pieter Klass 3200 21 9300837 N6622C UA811 01-Apr-17 $150.00 19A E Oswald DeVille 400 26 9306029 Q7433N UA178 04-Jan-17 $900.00 3G B Walters Potter 7000 32 9315397 H3619A UA219 15-Mar-17 $1,440.00 12A E McCarthy Long 4150 28 9322453 K3787S UA500 21-Jan-17 $900.00 15C E Pieter Klass 3200 8 9325058 N9735X UA4511 20-Feb-17 $1,000.00 18D E Gates Kline 3150 27 9329864 N2186U UA370 09-Feb-17 $1,200.00 6D EP Costos Jones 4230 18 9337213 P3310I UA1115 11-Oct-16 $107.00 4A E Phillips Retz 500 8 9341706 W6406J UA995 03-Mar-17 $1,230.00 19B E Peters DaSilva 3900 21 9345253 T4489O UA995 03-Mar-17 $1,210.00 19C E Peters DaSilva 3900 27 9351912 I7936E UA1115 11-Oct-16 $100.00 11A E Phillips Retz 500 28 9367347 P3630K UA370 09-Feb-17 $910.00 17B E Costos Jones 4230 23 9373804 E5439J UA771 07-Feb-17 $210.00 9F E Quinn Belton 500 23 9381022 G9257J UA317 28-Jan-17 $500.00 12A E Higgins Clarke 800 30 9383830 M2404K UA219 15-Mar-17 $1,500.00 15B E McCarthy Long 4150 22 9399499 I3259N UA995 03-Mar-17 $1,220.00 18F E Peters DaSilva 3900 35 9399602 W3032Z UA7111 10-Jan-17 $1,390.00 22A E Schmidt Oscar 6350 29 9403205 A3865T UA2322 11-Oct-16 $999.00 9C B Reilly Horowitz 1250 17 9405592 P3630K UA370 09-Feb-17 $900.00 17A E Costos Jones 4230 34 9416037 O3287M UA370 09-Feb-17 $920.00 18A E Costos Jones 4230 21 9417398 T8414J UA1010 12-Mar-17 $360.00 12C E Rice Oscar 890 11 9435560 U9915E UA370 09-Feb-17 $900.00 12A E Costos Jones 4230 30 9441047 R7449I UA7111 10-Jan-17 $1,380.00 29C E Schmidt Oscar 6350 20 9443524 R5297U UA200 10-Mar-17 $390.00 6A B Browne Lynch 2200 11 9444213 O4834A UA556 01-Apr-16 $455.00 23A E Rice Kline 1200 35 9445423 P1813S UA320 01-Oct-16 $433.00 11D EP Seville Smith 850 17 9455046 T1197Y UA4511 20-Feb-17 $990.00 3A B Gates Kline 6300 20 9463021 A1560G UA320 01-Oct-16 $225.00 15F EP Seville Smith 850 1 9476105 Y8525K UA370 09-Feb-17 $915.00 18B E Costos Jones 4230 11 9510213 H8222D UA500 21-Jan-17 $920.00 16F E Pieter Klass 3200 25 9518284 R9502F UA320 01-Oct-16 $653.00 18C EP Seville Smith 850 16 9518951 A6124T UA995 03-Mar-17 $1,210.00 19A E Peters DaSilva 3900 15 9525011 M6122N UA22 09-Sep-16 $1,999.00 8D B Browne Locke 6600 30 9551729 K3787S UA500 21-Jan-17 $900.00 15B E Pieter Klass 3200 14 9570543 L1676O UA90 14-Sep-16 $500.00 9D E Browne Locke 6600 30 9578755 E5439J UA771 07-Feb-17 $190.00 7C E Quinn Belton 500 34 9580415 U9077X UA317 28-Jan-17 $350.00 18D E Higgins Clarke 800 15 9584656 A2222E UA202 12-Aug-16 $999.00 11C E Terez Palac 4500 2 9585809 O6272Q UA7110 03-Jan-17 $689.00 32A E Schmidt Oscar 6350 31 9585860 U9727J UA3710 28-Feb-17 $2,800.00 2A B Peters DaSilva 7800 11 9588595 C5742F UA200 08-Aug-16 $339.00 5D EP Nellis Daly 1100 4 9590737 J8771D UA7110 03-Jan-17 $790.00 19C E Schmidt Oscar 6350 18 9597470 I6987P UA995 03-Mar-17 $1,200.00 18A E Peters DaSilva 3900 26 9601911 P8206E UA3710 28-Feb-17 $1,105.00 17E E Peters DaSilva 3900 21 9602255 G4206U UA992 14-Mar-17 $205.00 9A E Seville Potter 550 22 9605765 J5652B UA771 05-Feb-17 $375.00 11A E Whelan Kaltowski 500 33 9605859 V6402A UA22 09-Sep-16 $333.00 8C E Browne Locke 6600 18 9620095 F5066J UA202 12-Aug-16 $900.00 11B EP Terez Palac 4500 7 9623290 L2055O UA2322 11-Oct-16 $300.00 11A EP Reilly Horowitz 1250 5 9630723 W9752U UA4511 20-Feb-17 $800.00 2A B Gates Kline 6300 28 9632088 O4666U UA7110 03-Jan-17 $790.00 11A B Schmidt Oscar 12700 34 9632801 C3993Q UA7111 10-Jan-17 $1,460.00 32A E Schmidt Oscar 6350 32 9643412 Z1015I UA7110 03-Jan-17 $790.00 29B E Schmidt Oscar 6350 15 9655353 W2518Z UA500 21-Jan-17 $800.00 15A E Pieter Klass 3200 22 9660160 M2127I UA333 18-Feb-17 $660.00 19B E Piper Kline 3200 21 9664289 I4111V UA200 10-Mar-17 $550.00 3A B Browne Lynch 2200 21 9666639 B1712S UA2322 11-Oct-16 $232.00 4A E Reilly Horowitz 1250 3 9679522 Z9855H UA7111 10-Jan-17 $1,330.00 23A E Schmidt Oscar 6350 11 9681484 C7989W UA333 18-Feb-17 $690.00 20B E Piper Kline 3200 23 9681819 F6775F UA19 08-Aug-16 $911.00 4G EP Terez Palac 4500 14 9684643 A2792M UA219 15-Mar-17 $1,430.00 13A E McCarthy Long 4150 18 9692573 F6775F UA19 08-Aug-16 $900.00 4D EP Terez Palac 4500 8 9692830 A5102D UA771 05-Feb-17 $315.00 11C E Whelan Kaltowski 500 24 9697303 Q4568M UA333 18-Feb-17 $600.00 19C E Piper Kline 3200 25 9698762 E5205J UA320 01-Oct-16 $444.00 4A E Seville Smith 850 20 9711968 L2969M UA219 15-Mar-17 $1,900.00 14A E McCarthy Long 4150 35 9712338 N2754L UA7110 03-Jan-17 $711.00 10A B Schmidt Oscar 12700 31 9725932 J2960Z UA556 01-Apr-16 $1,655.00 22F B Rice Kline 1200 10 9728271 J5835V UA200 10-Mar-17 $440.00 4A B Browne Lynch 2200 20 9742245 M9454U UA4511 20-Feb-17 $910.00 16A E Gates Kline 3150 23 9752074 B6382S UA500 21-Jan-17 $811.00 16E E Pieter Klass 3200 23 9772956 J3499B UA3710 28-Feb-17 $1,100.00 19F E Peters DaSilva 3900 22 9785546 O4758X UA500 21-Jan-17 $910.00 15D E Pieter Klass 3200 15 9788797 V2688A UA995 03-Mar-17 $1,190.00 18C E Peters DaSilva 3900 22 9792480 H7084Q UA7111 10-Jan-17 $1,430.00 29A E Schmidt Oscar 6350 31 9807454 V2865T UA333 18-Feb-17 $550.00 19D E Piper Kline 3200 32 9816312 C5509S UA3710 28-Feb-17 $1,200.00 17A E Peters DaSilva 3900 31 9817665 G6263G UA500 21-Jan-17 $900.00 16A E Pieter Klass 3200 26 9827371 R7303H UA219 15-Mar-17 $1,450.00 11A E McCarthy Long 4150 26 9837980 W9066O UA7110 03-Jan-17 $800.00 23B E Schmidt Oscar 6350 20 9840879 L2038Z UA771 05-Feb-17 $290.00 11B E Whelan Kaltowski 500 27 9844045 G1882T UA1010 12-Mar-17 $410.00 10D E Rice Oscar 890 33 9858968 S3115Q UA4511 20-Feb-17 $880.00 19D E Gates Kline 3150 24 9871908 X2771R UA7111 10-Jan-17 $1,800.00 5A EP Schmidt Oscar 6350 30 9872107 H7702U UA556 01-Apr-16 $299.00 11A E Rice Kline 1200 9 9884041 D4466N UA200 07-Aug-16 $320.00 12A E Gates Lynch 1100 5 9884621 U6496J UA500 21-Jan-17 $810.00 16B E Pieter Klass 3200 33 9884985 E3197X UA7110 03-Jan-17 $800.00 29A E Schmidt Oscar 6350 30 9894079 U3717N UA7111 10-Jan-17 $1,400.00 29B E Schmidt Oscar 6350 18 9894569 B3153E UA3710 28-Feb-17 $1,210.00 17C E Peters DaSilva 3900 32 9903307 R6510Q UA200 07-Aug-16 $430.00 5C E Gates Lynch 1100 19 9922261 Z9447W UA219 15-Mar-17 $1,965.00 15A E McCarthy Long 4150 32 9931860 C3558Q UA7111 10-Jan-17 $8,000.00 1A B Schmidt Oscar 12700 14 9934056 B6382S UA500 21-Jan-17 $811.00 16D E Pieter Klass 3200 34 9940492 O4666U UA7110 03-Jan-17 $790.00 11B B Schmidt Oscar 12700 23 9951036 R7454P UA556 01-Apr-16 $334.00 30G E Rice Kline 1200 18 9953856 K4792M UA500 21-Jan-17 $710.00 15F E Pieter Klass 3200 18 9964299 U6334Y UA771 07-Feb-17 $310.00 4F E Quinn Belton 500 31 9964627 L2750S UA3710 28-Feb-17 $1,080.00 19D E Peters DaSilva 3900 33 9965409 L3320Q UA202 12-Aug-16 $4,000.00 11A B Terez Palac 4500 18 9976071 A6337M UA3710 28-Feb-17 $1,100.00 17B E Peters DaSilva 3900 25 9983812 U7886B UA7110 03-Jan-17 $790.00 13D E Schmidt Oscar 6350 29 9992092 Q3833M UA178 04-Jan-17 $390.00 5D B Walters Potter 7000 21 9998037 O2176G UA811 01-Apr-17 $150.00 21A E Oswald DeVille 400 36

Explanation / Answer

If you have any doubts, please give me comment...

SELECT FlightNumber, COUNT(*) AS no_of_passengers

FROM Fight

GROUP BY FlightNumber

HAVING COUNT(*)>10

ORDER BY no_of_passengers;

Schema not sufficient for rest of two questions, please provide schema, I will give the solution for those two questions also...

Related Questions

Navigate

• Browse (All)
• Browse D
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.