Coursera BIG DATA 1 SQL Coding Assignment 10/10 points earned (100%) Quiz passed!

Document Content and Description Below

SQL Coding Assignment 10/10 points earned (100%) Quiz passed!  Back to Week 3 1. In this quiz, you'll be writing queries based on the following database. Follow this link to access a larger ... picture of the database. Note: your SQL code entries will not be saved between quiz attempts! Please copy paste them somewhere so you don't have to retype the entire code when you take the quiz again. How many aircrafts are there in the PLANES table? Refer to the following video if you need a refresher: video 1. Correct Correct! Here's the code for your reference: SELECT COUNT(*)  p1oi/n1ts 25 1 SELECT Run FROM PLANES 50 562 10961 2. Write a query that provides a list of all planes that have a seat count of 100 or more, ordered from lowest to highest number of seats. What is the TAIL_NUMBER of the plane with the second lowest number of seats in that list? Refer to the following video if you need a refresher: video 2. Correct Correct! Here's the code for your reference: SELECT * FROM PLANES WHERE SEAT_COUNT >= 100 ORDER BY SEAT_COUNT  p1oi/n1ts N114DD N132AA N120EE N126AA 1 SELECT Run 3. Write a query that provides the number of Ƞights Ƞown by each aircraft. Which aircraft Ƞew the most Ƞights? Refer to the following video if you need a refresher: video 2. Correct Correct! Here's the code for your reference: SELECT TAIL_NUMBER, COUNT(*) AS NUM_FLIGHTS FROM FLIGHTS GROUP BY TAIL_NUMBER ORDER BY NUM_FLIGHTS DESC  p1oi/n1ts N125EE N135DD N120EE N111AA 4.  p1oi/n1ts 1 SELECT Run Write a query that provides a list of planes that Ƞew more than 600 passengers across all Ƞights. How many planes are in that list? Refer to the following video if you need a refresher: video 2. Correct Correct! Here's the code for your reference: SELECT TAIL_NUMBER, SUM(PASSENGER_COUNT) AS TOT_PASSENGERS FROM FLIGHTS GROUP BY TAIL_NUMBER HAVING TOT_PASSENGERS > 600 5 2 2 3 7 5. Write a query that provides the total number of Ƞights by country. How many Ƞights originated in the United States (Country US)? Refer to the following video if you need a refresher: video 3.  p1oi/n1ts SELECT SELECT 1 1 Run Run Correct Correct! Here's the code for your reference: SELECT COUNTRY, COUNT(*) AS NUM_FLIGHTS FROM FLIGHTS a LEFT JOIN AIRPORTS b ON a.DEPARTURE_AIRPORT = b.AIRPORT 34 23 12 2 6. Write a query that provides the total number of Ƞights by regionality. Which regionality has the second highest number of Ƞights? Refer to the following video if you need a refresher: video 3. Correct Correct! Here's the code for your reference: SELECT REGIONALITY, COUNT(*) AS NUM_FLIGHTS  p1oi/n1ts US-US US-ROW ROW-NA ROW-ROW 1 SELECT Run FROM FLIGHTS a LEFT JOIN CITY_PAIRS b ON a.DEPARTURE_AIRPORT = b.DEPARTURE_AIRPORT AND a.ARRIVAL_AIRPORT = b.ARRIVAL_AIRPORT GROUP BY REGIONALITY 7. How many CITY_PAIRS are there which depart from one of the following airports? KLAX, KDEN, KORD, KDET, KLGA Hint: use the IN operator! Refer to the following video if you need a refresher: video 5. Correct Correct! Here's the code for your reference: SELECT COUNT(*) FROM CITY_PAIRS WHERE DEPARTURE_AIRPORT IN ('KLAX','KDEN','KORD','KDET','KLGA')  p1oi/n1ts 722 803 245 104 1 / 1 1 SELECT 8. How many airports are missing elevation values? Refer to the following video if you need a refresher: video 5. Correct Correct! Here's the code for your reference: SELECT COUNT(*) FROM AIRPORTS WHERE ELEVATION IS NULL  1 / 1 points 10 8 6 4 9. What Ƞight number had the lowest passenger count (try using a subquery if you can!)? Refer to the following video if you need a refresher: video 6. Correct Correct! Here's the code for your reference:  p1oi/n1ts ALN626 SELECT SELECT 1 1 Run Run SELECT FLIGHT_NUMBER FROM FLIGHTS WHERE PASSENGER_COUNT IN (SELECT MIN(PASSENGER_COUNT) FROM FLIGHTS) ALN745 GLB805 SKY996 10. What is the average distance Ƞown by SKY Airline Ƞights (nearest mile)? Refer to the following video if you need a refresher: video 6. Correct Correct! Here's the code for your reference: SELECT AVG (DISTANCE) AS AVG_DISTANCE FROM FLIGHTS a LEFT JOIN PLANES b ON a.TAIL_NUMBER = b.TAIL_NUMBER  p1oi/n1ts 1474 1570 1577 1768 1 SELECT Run [Show More]

Last updated: 1 year ago

Preview 1 out of 8 pages