code

use aircargo;
 describe aircargo.routes;
 
CREATE TABLE IF NOT EXISTS aircargo.routes details (
route_id INT NOT NULL UNIQUE,
flight _num INT NOT NULL,
origin_airport VARCHAR (128) NOT NULL,
destination_airport VARCHAR (122) NOT NULL,
aircraft_id VARCHAR (182) NOT NULL,
distance_miles INT NOT NULL CHECK (distance_miles > 0),
CONSTRAINT flight num CHECK (flight_num > 0)
);

// Displaying passengers who have travelled in routes 01 to 25. Take data  from the passengers_on_flights table.

select * from aircargo.passengers_on_flights
where route_id between '01' and '25'
order by customer_id;

//Identifying the number of passengers and total revenue generated in each class of airline and finding total revenue generated so far.

select COUNT(no_of_tickets) AS total_passengers,SUM((no_of_tickets)*(Price_per_ticket)) AS business_class_revenue
FROM aircargo.ticket_details
WHERE class_id='Bussiness';

//Finding the  full name of the customer by extracting the first name and last name from the customer table.

select concat(first_name, ' ', last_name) as full_name
FROM aircargo.customer;

// write a query to extract the customers who have registered and booked a ticket.Use data from the customers and ticket_details_tables.

SELECT DISTINCT(customer_id),first_name,last_name
FROM aircargo.customer
INNER JOIN ticket_details USING (customer_id);

//write a query to identify the customer's first name and last name based on their customer ID and brand(EMIRATES) from ticket_details_table

SELECT first_name,last_name
FROM aircargo.customer
INNER JOIN ticket_details USING (customer_id)
WHERE brand='Emirates';

//write a query to identify the customers who have travelled by Economy Plus class using group by and having clause on passengers_on_flights table

SELECT customer_id,route_id,class_id
FROM aircargo. passengers_on_flights
GROUP BY customer_id,route_id,class_id
HAVING class_id='Economy Plus';

//write a query to identify whether the revenue has crossed 10000 using the IF clause on the ticket_details table.

SELECT SUM(Price_per_ticket) AS Total_Revenue,
if(SUM(Price_per_ticket) > 10000,"YES","NO") as Revenue_crossed
FROM ticket_details;

//write a query to create and grant access to a new user to perform operations a database

create user'username'@'localhost' IDENTIFIED BY 'password';
grant permission_type on aircargo to 'username'@'localhost';

// write a query to find the maximum ticket price for each class using window functions on the ticket_details_table.

SELECT p_id,customer_id,aircraft_id,class_id,no_of_tickets,a_code,brand,
MAX(price_per_ticket) OVER (partition by brand) AS Max_Ticket_Class_Price
From aircargo.ticket_details;

//write a query to extract the passengers whose route ID is 4 by improving the speed and perforance of the passengers_on_flights table.



CREATE INDEX route_id_4 ON passengers_on_flights(route_id);
SELECT * FROM aircargo.passengers_on_flights
WHERE route_id='4';

// for the route ID 4 , write a query to view the execution plan of the passengers_on_flights table.

SELECT * FROM aircargo.passenger_on_flights
WHERE route_id='4';

//write a query to calculate the total price of all tickets booked by a customer across different aircraft IDs using rollup functions.

SELECT customer_id,SUM(price_per_ticket) as total_price,COUNT(aircraft_id)
FROM aircargo.ticket_details
GROUP BY customer_id WITH ROLLUP;

//write a query to create a view with only business class customers along with the brand of airlines.

CREATE VIEW bussiness_class_view AS
SELECT customer_id,class_id,brand
FROM aircargo.ticket_details
WHERE class_id='Bussiness'
ORDER BY brand;
SELECT * from bussiness_class_view;

//write a query to create a stored procedure to get the details of the passengers flying between a range of routes defined in run time.

delemiter//
CREATE PROCEDURE route_range(in rangefrom int,in rangeto int)
begin
SELECT pass.customer_id,concat(cust.first_name,' ',cust.last_name) as cust_name,pass.class_id,
pass.route_id,pass_aircraft_id
FROM customer cust
join passengers_on_flights pass
where pass.route_id between rangefrom and rangeto;
end//
delimiter;

call route_range(1,100);


//write a query to create a stored procedure that extract all the details from the routes table where the travelled distance is more than 2000 miles.

DELIMITER//
CREATE PROCEDURE travelled_more_than_2000_miles()
BEGIN
SELECT * FROM aircargo_routes
WHERE distance_miles > 2000;
end//
call travelled_more_than_2000_miles();

// write a query to create a stored procedure that groups the distance travelled by each flights into three categories.
the categories are ,short distance travel (SDT) for >=0 and <=2000miles,intermediate distance travel (IDT) for >2000 miles and <=6500 miles,ling distance travel (LDT) for >6500 mies.

delimeter//
CREATE FUNCTION travelled_distance(distance_miles INT)
RETURNS VARCHAR(100) deterministic
BEGIN
DECLARE travelled_distance VARCHAR (100);
IF distance_miles BETWEEN 0 AND 2000 THEN SET travelled_distance='short distance travel (SDT)'
ELSEIF distance_miles BETWEEN 2000 AND 6500 THEN SET travelled_distance='intermediate distance travel (IDT)'
ELSEIF distance_miles > 6500 THEN SET travelled_distance='long distance travel (LDT)'
END IF;
RETURN(travelled_distance);
END//
DELIMITER//
SELECT route_id,flight_num,origin_airport,destination_airport,aircraft_id,travelled_distance(distance_miles) as distance_miles
FROM aircargo_routes
ORDER BY distance_miles;

//using a stored funtion in stored procedure on the ticket_details table.
conditions: if the class is Business and Economy Plus,then complimentry services are given as YES else it is NO.

DELIMITER//
CREATE FUNCTION complimentary_services(class_id VARCHAR(100))
RETURNS VARCHAR(100) deterministic
BEGIN
DECLARE complimentary_services VARCHAR(100);
IF class_id='Business' THEN SET complimentary_services='yes';
ELSEIF class_id='Economy Plus' THEN SET complimentary_services='yes';
ELSEIF class_id='Economy' THEN SET complimentary_services='no';
ELSEIF class_id='first_class' THEN SET complimentary_services='no';
END IF;
RETURN(complimentary services);
END//
DELIMETER//
SELECT p-date,customer_id,class_id,complimentary_services(class_id) AS complimentary_services
FROM aircargo_ticket_details;

// write a query to extract the first record of the customer whose last name ends with Scott using a cursor from the customer table.

DELIMITER//
CREATE PROCEDURE mu_cursor()
BEGIN
DECLARE a varchar(100);
DECLARE b varchar(100); 
DECLARE my_cursor CURSOR FOR SELSCT last_name,first_name FROM aircargo.customer
WHERE last_name ='Scott';
OPEN my_cursor;
REPEAT FETCH my_cursor INTO a,b;
UNTILL b=0 END REPEAT;
SELECT a AS last_name,b AS first_name;
CLOSE my_cursor; END;
// DELIMITER;
CALL my_cursor();