Solution.sql

-- EXERCISE 1:
CREATE DATABASE blog;

USE blog;

CREATE TABLE authors 
(
	id  INT auto_increment PRIMARY KEY,
    name VARCHAR(50) NOT NULL
);

CREATE TABLE blogs
(
	id INT auto_increment PRIMARY KEY,
    title VARCHAR(100) NOT NULL UNIQUE,
    word_count INT NOT NULL,
    views INT NOT NULL DEFAULT 0,
    author_id INT NOT NULL,
    CONSTRAINT fk_author FOREIGN KEY (author_id) REFERENCES authors(id) ON DELETE CASCADE
);

INSERT INTO authors (name) 
VALUES 
	('Maria Charlotte'),
	('Juan Perez'),
	('Gemma Alcocer') ;
    
INSERT INTO blogs (title,word_count,views,author_id)
VALUES
	('Best Paint Colors',              814,          14,1),
    ('Small Space Decorating Tips',    1146,         221,2),
    ('Hot Accessories',                986,          105,1),
    ('Mixing Textures',                765,          22,1),
    ('Kitchen Refresh',                1242,         307,2),
    ('Homemade Art Hacks',             1002,         193,1),
    ('Refinishing Wood Floors',        1571,         7542,3);
    
SELECT a.name,b.title,b.word_count,b.views
FROM blogs b
JOIN authors a ON b.author_id=a.id
ORDER BY b.views;

-- Exercise 2:
CREATE DATABASE airline;

USE airline;

CREATE TABLE customers
(
	id 	INT auto_increment PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    status ENUM('Gold','Silver','None') DEFAULT 'None',
    total_mileage INT NOT NULL
);

CREATE TABLE aircrafts
(
	id INT auto_increment PRIMARY KEY,
    name VARCHAR(30) NOT NULL,
    total_seats INT NOT NULL
);

CREATE TABLE flights
(
    flight_number VARCHAR(20) PRIMARY KEY,
    mileage INT NOT NULL,
    aircraft_id INT NOT NULL,
    FOREIGN KEY (aircraft_id) REFERENCES aircrafts(id)
);

CREATE TABLE bookings
(
	id INT auto_increment PRIMARY KEY,
    customer_id INT NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(id),
    flight_number VARCHAR(20) NOT NULL,
    FOREIGN KEY (flight_number) REFERENCES flights(flight_number),
    UNIQUE(customer_id,flight_number)
);

INSERT INTO customers (name,status,total_mileage)
VALUES 
	('Agustine Riviera','Silver',115235),
	('Alaina Sepulvida','None',6008),
	('Tom Jones','Gold',205767),
	('Sam Rio','None',2653),
	('Jessica James','Silver',127656),
	('Ana Janco','Silver',136773),
	('Jennifer Cortez','Gold',300582),
	('Christian Janco','Silver',14642);


INSERT INTO aircrafts (name,total_seats)
VALUES
	('Boeing 747',400),
	('Airbus A330',236),
	('Boeing 777',264);

INSERT INTO flights (flight_number,aircraft_id,mileage)
VALUES
	('DL143',1,135),
	('DL122',2,4370),
	('DL53',3,2078),
	('DL222',3,1765),
	('DL37',1,531);

INSERT INTO bookings (customer_id,flight_number)
VALUES 
(1, 'DL143'),
(1, 'DL122'),
(3, 'DL122'),
(3, 'DL53'),
(4, 'DL143'),
(3, 'DL222'),
(5, 'DL143'),
(6, 'DL222'),
(7, 'DL222'),
(5, 'DL122'),
(4, 'DL37'),
(8, 'DL222');

SELECT 
c.name AS 'Customer Name', 
c.status AS 'Customer Status',
f.flight_number AS 'Flight Number',
a.name AS 'Aircraft',
a.total_seats AS 'Total Aircraft Seats',
f.mileage AS 'Flight Mileage',
c.total_mileage AS 'Total Customer Mileage'
FROM bookings b
JOIN customers c ON b.customer_id=c.id
JOIN flights f ON f.flight_number=b.flight_number
JOIN aircrafts a ON a.id=f.aircraft_id;


-- Exercise 3:
SELECT COUNT(DISTINCT flight_number) FROM flights;

SELECT AVG(mileage) FROM flights;

SELECT AVG(total_seats) FROM aircrafts;

SELECT status, AVG(total_mileage) FROM customers GROUP BY status;

SELECT status, MAX(total_mileage) FROM customers GROUP BY status;

SELECT COUNT(*) FROM aircrafts WHERE name LIKE '%Boeing%';

SELECT * FROM flights WHERE mileage BETWEEN 300 AND 2000;

SELECT c.status, AVG(f.mileage)
FROM bookings b
JOIN customers c ON b.customer_id = c.id
JOIN flights f ON b.flight_number = f.flight_number
GROUP BY c.status;

SELECT a.name, COUNT(*) AS total_bookings
FROM bookings b
JOIN customers c ON b.customer_id = c.id
JOIN flights f ON b.flight_number = f.flight_number
JOIN aircrafts a ON f.aircraft_id = a.id
WHERE c.status = 'Gold'
GROUP BY a.name
ORDER BY total_bookings DESC
LIMIT 1;