11-mysql

create table passports (
`passport_id` INT PRIMARY KEY AUTO_INCREMENT,
`passport_number` VARCHAR(45) UNIQUE
);
create table people (
`person_id` INT PRIMARY KEY AUTO_INCREMENT,
`first_name` VARCHAR(45) not NULL,
`salary` DECIMAL(10, 2) ,
`passport_id` int unique,
FOREIGN KEY (`passport_id`)
REFERENCES `passports` (`passport_id`)
);
INSERT INTO `passports` (`passport_id`, `passport_number`) VALUES
('101', 'N34FG21B'),
('102', 'K65LO4R7'),
('103', 'ZE657QP2');
INSERT INTO `persons` (`first_name`, `salary`, `passport_id`) VALUES
('Roberto ', '43300.00', '102'),
('Tom', '56100.00', '103'),
('Yana', '60200.00', '101');

*************************************************************************

create table manufacturers (
manufacturer_id int primary key auto_increment,
`name` varchar(20),
established_on date
);
create table models (
model_id int primary key auto_increment,
`name` varchar(20) not null,
manufacturer_id int,
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');

**************************************************************************

CREATE TABLE `students` (
`student_id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
`name` VARCHAR(45) NOT NULL);

CREATE TABLE `exams` (
`exam_id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
`name` VARCHAR(45) NOT NULL);

CREATE TABLE `students_exams` (
`student_id` INT NOT NULL,
`exam_id` INT NOT NULL,
PRIMARY KEY (`student_id`,`exam_id`),
CONSTRAINT `fk_exams`
FOREIGN KEY (`exam_id`)
REFERENCES `exams` (`exam_id`),
CONSTRAINT `fk_students`
FOREIGN KEY (`student_id`)
REFERENCES `students` (`student_id`));
INSERT INTO `students` (`student_id`, `name`)
VALUES
('1', 'Mila'),
('2', 'Toni'),
('3', 'Ron');
INSERT INTO `exams` (`exam_id`, `name`)
VALUES
('101', 'Spring MVC'),
('102', 'Neo4j'),
('103', 'Oracle 11g');
INSERT INTO `students_exams` (`student_id`, `exam_id`)
VALUES
('1', '101'),
('1', '102'),
('2', '101'),
('3', '103'),
('2', '102'),
('2', '103');

**********************************************************************

CREATE TABLE `teachers` (
`teacher_id` INT primary key AUTO_INCREMENT,
`name` VARCHAR(45) NOT NULL,
`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`);

*********************************************************************

CREATE TABLE `item_types` (
`item_type_id` INT PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(50));

CREATE TABLE `customers` (
`customer_id` INT PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(50),
`birthday` DATE,
`city_id` INT,
FOREIGN KEY (`city_id`)
REFERENCES `cities` (`city_id`));

CREATE TABLE `cities` (
`city_id` INT PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(50));

CREATE TABLE `orders` (
`order_id` INT PRIMARY KEY AUTO_INCREMENT,
`customer_id` INT,
FOREIGN KEY (`customer_id`)
REFERENCES `customers` (`customer_id`));

CREATE TABLE `items` (
`item_id` INT PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(50),
`item_type_id` INT,
FOREIGN KEY (`item_type_id`)
REFERENCES `item_types` (`item_type_id`));

CREATE TABLE `order_items` (
`item_id` INT ,
`order_id` INT,
PRIMARY KEY (`order_id`, `item_id`),
FOREIGN KEY (`order_id`)
REFERENCES `orders` (`order_id`),
FOREIGN KEY (`item_id`)
REFERENCES `items` (`item_id`));

**************************************************************************

CREATE TABLE `majors` (
`major_id` INT PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(50));

CREATE TABLE `subjects` (
`subject_id` INT PRIMARY KEY AUTO_INCREMENT,
`subject_name` VARCHAR(50));

CREATE TABLE `students` (
`student_id` INT PRIMARY KEY AUTO_INCREMENT,
`student_number` VARCHAR(12),
`student_name` VARCHAR(50),
`major_id` INT,
FOREIGN KEY (`major_id`)
REFERENCES `majors` (`major_id`));

CREATE TABLE `payments` (
`payment_id` INT PRIMARY KEY AUTO_INCREMENT,
`payment_date` date,
`payment_amount` DECIMAL(8,2),
`student_id` INT,
FOREIGN KEY (`student_id`)
REFERENCES `students` (`student_id`));

CREATE TABLE `agenda` (
`student_id` INT,
`subject_id` INT,
PRIMARY KEY (`student_id`, `subject_id`),
FOREIGN KEY (`student_id`)
REFERENCES `students` (`student_id`),
FOREIGN KEY (`subject_id`)
REFERENCES `subjects` (`subject_id`));

**************************************************************************
SELECT  mr.mountain_range, pk.peak_name, pk.elevation FROM peaks as pk
join mountains as mr
on mr.id=pk.mountain_id
where mountain_range = 'Rila'
order by elevation desc;