SQL_Project

Air Cargo Analysis

Objective: Effective way to Write Queries to use fetch data using different Functions

Used Tool and Language:
• Tool: MySQL
• Language: SQL
• Server: Mysql Server

Following operations should be performed:

1. Create an ER diagram for the given airlines database.
2. Write a query to create route_details table using suitable data types for the fields, such as route_id, flight_num, origin_airport, destination_airport, aircraft d, and distance_miles. Implement the check constraint for the flight number and unique constraint for the route_id fields. Also, make sure that the distance miles field is greater than 0.
3. Write a query to display all the passengers (customers) who have travelled in routes 01 to 25. Take data from the passengers_on_flights table.
4. Write a query to identify the number of passengers and total revenue in business class from the ticket_details table.
5. Write a query to display the full name of the customer by extracting the first name and last name from the customer table.
6. Write a query to extract the customers who have registered and booked a ticket. Use data from the customer and ticket_details tables.
7. Write a query to identify the customer's first name and last name based on their customer ID and brand (Emirates) from the ticket details table.
8. Write a query to identify the customers who have travelled by Economy Plus class using Group By and Having clause on the passengers_on_flights table.
9. Write a query to identify whether the revenue has crossed 10000 using the IF clause on the ticket_details table.
10. Write a query to create and grant access to a new user to perform operations on a database.
11. Write a query to find the maximum ticket price for each class using window functions on the ticket_details table.
12. Write a query to extract the passengers whose route ID is 4 by improving the speed and performance of the passengers_on_fiights table.
13. For the route ID 4, write a query to view the execution plan of the passengers_on_flights table.
14. Write a query to calculate the total price of all tickets booked by a customer across different aircraft IDs using rollup function.
15. Write a query to create a view with only business class customers along with the brand of airlines.