Table Relations - Exercise

01. One-To-One Relationship
create table persons(
    person_id INT NOT NULL AUTO_INCREMENT,
    first_name VARCHAR(50),
    salary DECIMAL(10 , 2 ),
    passport_id INT UNIQUE NOT NULL,
    constraint pk_persons primary key (person_id)
);
create table passports(
  passport_id INT not null,
  passport_number varchar(8),
  constraint pk_passports primary key (passport_id)
);

ALTER TABLE persons ADD CONSTRAINT fk_persons_passports
FOREIGN KEY (passport_id)
REFERENCES passports(passport_id);

insert into passports values(101,'N34FG21B'),(102,'K65LO4R7'),(103,'ZE657QP2');
insert into persons values(1,'Roberto',43300,102),(2,'Tom',56100,103),(3,'Yana',60200,101);
-------------------
02. One-To-Many Relationship
CREATE TABLE `manufacturers`(
    manufacturer_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20),
    established_on DATETIME
);
CREATE TABLE `models`(
    model_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20),
    manufacturer_id INT
);

ALTER TABLE `models`
ADD CONSTRAINT fk_manufacturer_model
FOREIGN KEY (manufacturer_id) REFERENCES `manufacturers`(manufacturer_id);

INSERT INTO `manufacturers`(manufacturer_id,name,established_on)
VALUES (1,'BMW','1916-03-01'),(2,'Tesla','2003-01-01'),(3,'Lada','1966-05-01');
INSERT INTO `models`(model_id,name,manufacturer_id)
VALUES (101,'X1',1),(102,'i6',1),(103,'Model S',2),(104,'Model X',2),
(105,'Model 3',2),(106,'Nova',3);
------------------
03. Many-To-Many Relationship
CREATE TABLE `students`(
    student_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20)
);
INSERT INTO `students`(student_id,name)
VALUES (1,'Mila'),(2,'Toni'),(3,'Ron');

CREATE TABLE `exams`(
    exam_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(30)
);
INSERT INTO `exams`(exam_id,name)
VALUES (101,'Spring MVC'),(102,'Neo4j'),(103,'Oracle 11g');

CREATE TABLE `students_exams`(
    student_id INT,
    exam_id INT
);
INSERT INTO `students_exams`(student_id,exam_id)
VALUES (1,101),(1,102),(2,101),(3,103),(2,102),(2,103);

ALTER TABLE `students_exams`
ADD CONSTRAINT pk_student_exams
PRIMARY KEY (student_id,exam_id),
ADD CONSTRAINT fk_student
FOREIGN KEY (student_id) REFERENCES `students`(student_id),
ADD CONSTRAINT fk_exam
FOREIGN KEY (exam_id) REFERENCES `exams`(exam_id);
--------------
04. Self-Referencing
CREATE TABLE `teachers`(
    teacher_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20),
    manager_id INT
);
INSERT INTO `teachers`(teacher_id,name,manager_id)
VALUES (101,'John',NULL),(102,'Maya',106),(103,'Silvia',106),
(104,'Ted',105),(105,'Mark',101),(106,'Greta',101);

ALTER TABLE `teachers`
ADD CONSTRAINT fk_teachers_managers
FOREIGN KEY (manager_id) REFERENCES `teachers`(teacher_id);
---------------
09. Peaks in Rila
SELECT mountain_range,peak_name,elevation
AS `peak_elevation` FROM peaks AS p JOIN mountains AS m ON
p.mountain_id=m.id WHERE mountain_range='Rila' ORDER BY `peak_elevation`
DESC;