LAB2SQL.sql

-- Challenge 1
create database lab;
use lab

CREATE TABLE articless (
    author VARCHAR(100),
    title VARCHAR(255),
    word_count INT,
    views INT
);

INSERT INTO articless (author, title, word_count, views) VALUES
('Maria Charlotte', 'Best Paint Colors', 814, 14),
('Juan Perez', 'Small Space Decorating Tips', 1146, 221),
('Maria Charlotte', 'Hot Accessories', 986, 105),
('Maria Charlotte', 'Mixing Textures', 765, 22),
('Juan Perez', 'Kitchen Refresh', 1242, 307),
('Maria Charlotte', 'Homemade Art Hacks', 1002, 193),
('Gemma Alcocer', 'Refinishing Wood Floors', 1571, 7542);


-- Solution 1 
-- i made 2 (authors and Articles) table for 3NF 
CREATE TABLE authors (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);
select * from articles;
drop table articles;
INSERT INTO authors (name) VALUES 
('Maria Charlotte'), 
('Juan Perez'), 
('Gemma Alcocer'); 

CREATE TABLE articles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    author_id INT,
    title VARCHAR(255),
    word_count INT,
    views INT,
    FOREIGN KEY (author_id) REFERENCES authors(id)
);

INSERT INTO articles (author_id, title, word_count, views) VALUES
(1, 'Best Paint Colors', 814, 14),
(2, 'Small Space Decorating Tips', 1146, 221),
(1, 'Hot Accessories', 986, 105),
(1, 'Mixing Textures', 765, 22),
(2, 'Kitchen Refresh', 1242, 307),
(1, 'Homemade Art Hacks', 1002, 193),
(3, 'Refinishing Wood Floors', 1571, 7542);

-- it is for testing 
SELECT 
    a.name AS author, 
    art.title, 
    art.word_count, 
    art.views
FROM articles art
INNER JOIN authors a ON art.author_id = a.id;

-- Challenge 2

CREATE TABLE flight_bookings (
    customer_name VARCHAR(50),
    customer_status ENUM('NONE', 'SILVER', 'GOLD'),
    flight_number VARCHAR(10),
    aircraft VARCHAR(50) ,
    total_aircraft_seats INT,
    flight_mileage INT,
    total_customer_mileage INT
);
INSERT INTO flight_bookings (
    customer_name, 
    customer_status, 
    flight_number, 
    aircraft, 
    total_aircraft_seats, 
    flight_mileage, 
    total_customer_mileage
) VALUES
('Agustine Riviera', 'Silver', 'DL143', 'Boeing 747', 400, 135, 115235),
('Agustine Riviera', 'Silver', 'DL122', 'Airbus A330', 236, 4370, 115235),
('Alaina Sepulvida', 'None', 'DL122', 'Airbus A330', 236, 4370, 6008),
('Agustine Riviera', 'Silver', 'DL143', 'Boeing 747', 400, 135, 115235),
('Tom Jones', 'Gold', 'DL122', 'Airbus A330', 236, 4370, 205767),
('Tom Jones', 'Gold', 'DL53', 'Boeing 777', 264, 2078, 205767),
('Agustine Riviera', 'Silver', 'DL143', 'Boeing 747', 400, 135, 115235),
('Sam Rio', 'None', 'DL143', 'Boeing 747', 400, 135, 2653),
('Agustine Riviera', 'Silver', 'DL143', 'Boeing 747', 400, 135, 115235),
('Tom Jones', 'Gold', 'DL222', 'Boeing 777', 264, 1765, 205767),
('Jessica James', 'Silver', 'DL143', 'Boeing 747', 400, 135, 127656),
('Sam Rio', 'None', 'DL143', 'Boeing 747', 400, 135, 2653),
('Ana Janco', 'Silver', 'DL222', 'Boeing 777', 264, 1765, 136773),
('Jennifer Cortez', 'Gold', 'DL222', 'Boeing 777', 264, 1765, 300582),
('Jessica James', 'Silver', 'DL122', 'Airbus A330', 236, 4370, 127656),
('Sam Rio', 'None', 'DL37', 'Boeing 747', 400, 531, 2653),
('Christian Janco', 'Silver', 'DL222', 'Boeing 777', 264, 1765, 14642);

CREATE TABLE customers(
customer_id INT AUTO_INCREMENT PRIMARY KEY,
name varchar(50),
status ENUM ('NONE','SILVER','GOLD'),
total_milage INT
);

CREATE TABLE flights (
    flight_id INT AUTO_INCREMENT PRIMARY KEY,
    flight_number VARCHAR(10) UNIQUE,
    flight_mileage INT
);

CREATE TABLE aircrafts (
    aircraft_id INT AUTO_INCREMENT PRIMARY KEY,
    model VARCHAR(50) UNIQUE,
    total_seats INT
);

CREATE TABLE bookings (
    booking_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT,
    flight_id INT,
    aircraft_id INT,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
    FOREIGN KEY (flight_id) REFERENCES flights(flight_id),
    FOREIGN KEY (aircraft_id) REFERENCES aircrafts(aircraft_id)
);

-- Challenge 3 
-- 1
SELECT COUNT(DISTINCT flight_number) FROM flights;
-- 2
SELECT AVG(mileage) FROM flights;
-- 3 
SELECT AVG(total_seats) FROM aircrafts;
-- 4
SELECT status, AVG(total_mileage) FROM customers GROUP BY status;
-- 5 
SELECT status, MAX(total_mileage) FROM customers GROUP BY status;
-- 6 
SELECT COUNT(*) FROM aircrafts WHERE name LIKE '%Boeing%';
-- 7 
SELECT * FROM flights WHERE mileage BETWEEN 300 AND 2000;
-- 8 
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;
-- 9 
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;