Flights Database Query and Data Analysis Assignment

This assignment focuses on SQL queries applied to a flights database. The solution includes queries to determine the total number of flights, count flights by airline, and order airlines by the number of flights. It also includes queries to find the top 5 airlines by flight count and the top 5 airlines for flights of 1,000 miles or greater. The assignment also includes a query to display flight details for a specific month and a query to count the number of times each plane flew. This assignment provides a practical application of SQL for data analysis and database manipulation.

Q1. Show the total number of flights
SELECT count(*) FROM FLIGHTS
WHERE dep_time is not null;
Q2. Show the total number of flights by airline (carrier)
SELECT CARRIER, COUNT(*) TOTAL_FLIGHTS FROM FLIGHTS WHERE CARRIER
IN(SELECT CARRIER FROM AIRLINES) GROUP BY CARRIER;

Q3. Show all of the airlines, ordered by number of flights in descending order
SELECT CARRIER, COUNT(*) TOTAL_FLIGHTS FROM FLIGHTS WHERE CARRIER
IN(SELECT CARRIER FROM AIRLINES) GROUP BY CARRIER ORDER BY
TOTAL_FLIGHTS DESC;
Q4. Show only the top 5 airlines, by number of flights, ordered by number of flights in
descending order
SELECT CARRIER, COUNT(*) TOTAL_FLIGHTS FROM FLIGHTS
WHERE CARRIER IN(SELECT CARRIER FROM AIRLINES)
GROUP BY CARRIER
ORDER BY TOTAL_FLIGHTS DESC LIMIT 5;

Q5. Show only the top 5 airlines, by number of flights of distance 1,000 miles or greater,
ordered by number of
flights in descending order
SELECT CARRIER, COUNT(*) TOTAL_FLIGHTS, DISTANCE FROM FLIGHTS
WHERE CARRIER IN(SELECT CARRIER FROM AIRLINES) AND DISTANCE >= 1000
GROUP BY CARRIER
ORDER BY TOTAL_FLIGHTS DESC LIMIT 5;

6. Create a question that (a) uses data from the flights database, and (b) requires
aggregation to answer it, and
write down both the question, and the query that answers the question.
Q(A) Display flight details which are flew in month 12;
SELECT * FROM FLIGHTS WHERE MONTH = 12;
Q(B) How many times planes are flew.
SELECT F.TAILNUM, COUNT(FLIGHT) FROM FLIGHTS F INNER JOIN PLANES P
WHERE P.TAILNUM = F.TAILNUM
GROUP BY F.TAILNUM ORDER BY COUNT(FLIGHT) DESC;