[Fly For Less SQL Schema] Airport Database Schema #sql #schema

README.md

Exercise 3 - Select Practise

Create SQL queries to accomplish the following tasks using the fly for less database

1. Select all the records from the price table.

2. Select just the names of all the cities

3. Select the IATA code and details of all the terminals

Exercise 4 - Filter Practise

Write queries to accomplish the following tasks:

1. Select the first and last names of all the customer born since Jan 1st,2000

2. Select the discount a type 2 passenger will receive when booking a reservation 19 days in advance

Exercise 5 - A bit of Joinery

Write Select statements to do the following:

1. Select the names of all the airports in New York

2. Select a list of all flights shwoing the flight number & name(rather than code) of their departure and arrival airport

3. Select a list of all the airports in London and the number of any flights leaving from each one.

Exercise 6 - Get Updated

Write two SQL statements to perform the following updates to the fly four less database

1. Customer 3607 has got married,update her last name to Samuells.

2. Decrease the number of seats on all flights by 1 to make room for undercover Security

Exercise 7 - Deleting Data

Deleting the following records from the fly four less database

1. Delete the records of all customers over the age of 80,our insurance policy does not allow us to carry them.

2. Delete all reservations for flights 375 on April 7th 2006,The Captain is going on holiday

Exercise 8 - Getting Some Order

Write a select statement that returns the names of all cities in alphabetical order

Exercise 9 - Aggregate Functions

Answer the following questions,provide the queries to prove your answers

1. What is the average age of all the customers

2. Which city has the most airports

3. Where does the longest flight go

4. How many flights leave London on a Tuesday

Exercise 10 - Sub-Queries

Here are a few exercises that will really exrecise your SQL skills.

1. Develop an SQL statement that returns the name of the airport with the most flights departing from it.Only return one row and one column data

2. In case of emergency pilots need to know nearby airports that they could divert to.Write a query that returnd a list of all flight numbers,their intended destination an other airports in the same city that thay could land at if required.

3. Develop a report that shows all the cities that the airline doen not currently fly to along with their country codes

Exercise 11 - Scalar Functions

Develop quries to accomplish the following tasks:

1. Return a list of all flights along with their un-discount price

2. Display the name of the arrival airport of all flights,format the name so that the names are in lower case except for the first letter of each word

3. Display a list of all customers under 10 that have a birthday in the next week

Scalar Functions

Scalar Functions are functions that can be used to perform operations on strings,dates,numbers etc.

Unlike aggregate functions they are called for each row of data rather than acting on agroup or all rows

You can use these functions to affect the output or in the where clause to affect what data is returned

select reverse(name)
from airports a inner join flight_profiles f
on a.iata_code = f.dep_apt
where length(name)>10
order by substring(name,4,1)

This example uses some simple functions to return the name of the departure airports that have more than ten characters reversed and ordered by the fourth character.You can even use functions to affect the data used in joins

Scalar functions are the biggest area of difference between different databases and most do not form part of ANSI Standard SQL

query-airport-database.sql

-- Exercise 2 - Insert Some Data

-- Exercise 3 - Select Practise
-- statement that selects all the records from the price table
SELECT * FROM price;

-- statement that selects just the names of the cities
SELECT name FROM cities;

-- statement that selects IATA Code and details of all the terminals
SELECT APT_IATA_CODE,details FROM airport_terminals;

-- Exercise 4 - Filter Database
-- statement that selects the first and last names of all the customers born since Jan 1st,2000
SELECT first_name,last_name FROM customer WHERE DOB >='2000-01-01';

-- statement that selects the discount a type 2 passeger will receive when looking a reservation 19 days in advance

SELECT DISCOUNT FROM discount WHERE PASSENGER_TYPE =2 AND DAYS_ADV_MIN <= 19 and DAYS_ADV_MAX >=19;

-- Exercise 5 - A Bit of Joinery
-- statement that selects the names of all the airports in New York
SELECT airports.NAME AS AIRPORTS_IN_NEW_YORK FROM airports RIGHT JOIN cities
ON airports.CTY_IATA_CODE = cities.CTY_IATA_CODE
WHERE CITIES.NAME = 'NEW YORKS';

-- statement that lists all flights showing the flight number and name(rather than code) of their departure and arrival airport
SELECT flight_profiles.FLT_NUMB,airports_departure.NAME AS Departure_Airport,airports_arrival.Names AS Arrival_Airport
FROM airports AS airports_departure RIGHT JOIN flight_profiles
ON airports_departure.IATA_CODE = flight_profiles.from
JOIN airports AS airports_arrival ON flight_profiles.to = airports_arrival.IATA_CODE;

-- statement that lists all airports in London and the number of any flights leaving from each one
SELECT AIRPORTS_IN_LONDON,COUNT(x)'NO OF FLIGHTS LEAVING' FROM
( SELECT airports.name AS AIRPORTS_IN_LONDON,
	flight_profiles.flt_numb AS x
	FROM flight_profiles RIGHT JOIN airports
	ON flight_profiles.from = airports.IATA_CODE
	WHERE airports.CTY_IATA_CODE='LON'
) AS P

    GROUP BY AIRPORTS_IN_LONDON
    ORDER BY  'NO OF FLIGHTS LEAVING' DESC;

-- Exercise 6 - Get Updated
-- statement that updates the record of customer 3607
UPDATE customer SET LAST_NAME ='Samuells' WHERE CUST_ID=3607;

-- statement that decreases the seats on all flights by 1

UPDATE flight_profiles SET SEATS = SEATS - 1;

-- Exercise 7 - Deleting Data
-- statement that deletes the records of all customers over the age of 80
DELETE FROM CUSTOMER WHERE YEAR(CURDATE()) - YEAR(DOB) > 80;

--statement that deletes reservations for flight 375 on April 7th 2006
DELETE FROM reservation WHERE FLT_NUMB = 375 AND DEP_DATE = '2006-04-07 00:00:00';

-- Exercise 8 - Getting Some Order
-- statement that returns the names of all cities in alphabetical order
SELECT NAME FROM cities Order BY NAME;

-- Exercise 9 - Aggregate Functions
-- statement that selects the average age of all customers
SELECT AVG(YEAR(CURDATE()) - YEAR(DOB)) AS AVERAGE_AGE FROM customer;

-- statement that selects the city that has the most airports
SELECT COUNT(NAME) AS NUMBER_OF_AIRPORTS,CTY_IATA_CODE AS city
FROM airports
GROUP BY CTY_IATA_CODE
ORDER BY NUMBER_OF_AIRPORTS
DESC LIMIT 1;

-- statement that selects the city that has the most airports
SELECT ARR FROM(
SELECT TIMEDIFF(TIME(arrive),TIME(depart)) AS
time_diff,arr FROM(
SELECT air_dept.NAME AS dept,air_arr.NAME AS arr,
flight_profiles.arrive,flight_profiles.depart FROM airports
AS  air_dept RIGHT JOIN flight_profiles ON air_dept.iata_code = flight_profiles.from JOIN
airport AS air_arr ON flight_profiles.to = air_arr.iata_code) AS V
ORDER BY time_diff DESC LIMIT 1) AS x;

-- statement that selects the number of flights that leave London on a Tuesday
SELECT COUNT(x) AS NO_OF_FLIGHTS_FROM_LONDON_ON_TUESDAY FROM flight_profiles
JOIN airports ON flight_profiles.from = airports.iata_code
WHERE(flight_profiles.TUE='Y') AND (airports.cty_iata_code='LON');

-- Exercise 10 - Sub-Queries
--statement that returns the name of the airports with the most flights departing from it
SELECT AIRPORTS FROM
(select airports.NAME AS AIRPORTS,COUNT(x)'TOTAL FLIGHTS'
FROM airports RIGHT JOIN flight_profiles
ON airports.iata_code = flight_profiles.from
GROUP BY flight_profiles.from
ORDER BY 'TOTAL FLIGHTS' desc limit 1) as m;

-- statement that lists all flights numbers,intended destinations and other airports in the same city the pilot can land
SELECT flight_number,destination,airports.name FROM
(SELECT flight_profiles.flt_numb AS flight_number,airports.NAME AS destination,cities.iata_code as city
	FROM cities,flight_profiles LEFT JOIN airport
	ON airports.iata_code = flight_profiles.to
	WHERE cities.iata_code = airports.cty_iata_code
	ORDER BY flight_number DESC) AS x left Join
    airports ON x.city = airports.cty_iata_code;

-- statement that shows all the cities that the airline does not fly to along with country codes
SELECT cities.name as CITY,cities.cou_iata_code AS COUNTRY_CODE
FROM cities LEFT JOIN(SELECT DISTINCT destinations.dest_city,cities.cou_iata_code
FROM cities join(SELECT cty_iata_code AS dest_city
FROM flight_profiles JOIN airports
ON flight_profiles.to = airports.iata_code) AS destinations
ON cities.iata_code = destinations.dest_city) AS reachable_cities
ON cities.iata_code = dest_city
WHERE dest_city IS NULL;

-- Exercise 11 - Scalar Functions
-- statement that lists all flights along with their undiscounted prices
SELECT flt_numb,price FROM flight_profiles
INNER JOIN price
ON flight_profiles.PRICE_CODE = price.PC_LEVEL
WHERE depart BETWEEN from_date AND to_date;


-- statement that lists the names of the arrival airports formatted so that the first letter is in caps and all others in lowercase
SELECT CONCAT(UPPER(SUBSTRING(airports.Name,1,1)),
LOWER(SUBSTRING(airports.Name,2))) AS Arrival_Airport
FROM airports RIGHT JOIN flight_profiles
ON aiports.iata_code = flight_profiles.to;

-- statement that displays all customers under 10 years that have a birthday next week
SELECT first_name,last_name,DOB FROM customer
WHERE YEAR(CURDATE() - YEAR(DOB) < 10) AND
WEEK(DOB) = WEEK(CURDATE()) + 1;