Table Relations - Lab

1. Mountains and Peaks
CREATE TABLE mountains(
    id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL
);
CREATE TABLE peaks(
    id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    mountain_id INT NOT NULL
);
ALTER TABLE peaks
ADD CONSTRAINT fk_mountains_peaks
FOREIGN KEY (mountain_id) REFERENCES mountains(id);
--------------
2. Books and Authors
CREATE TABLE authors(
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL
);
CREATE TABLE books(
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    author_id INT NOT NULL
);
ALTER TABLE books
ADD CONSTRAINT fk_authors_books
FOREIGN KEY (author_id) REFERENCES authors(id)
ON DELETE CASCADE;
------------------
3. Trip Organization
SELECT driver_id,vehicle_type, CONCAT(first_name, ' ', last_name)
AS `driver_name` FROM vehicles AS v
 JOIN campers AS c ON v.driver_id=c.id;
-------------
4. SoftUni Hiking
SELECT starting_point AS `route_starting_point`,end_point AS
`route_ending_point`,leader_id AS leader_id,
CONCAT (first_name, ' ',last_name) AS `leader_name` FROM routes AS r
JOIN campers AS c ON r.leader_id=c.id;
-------------