-- 1 va
CREATE TABLE `persons`(
`person_id` INT PRIMARY KEY AUTO_INCREMENT,
`first_name` VARCHAR(50) NOT NULL,
`salary` VARCHAR(40) NOT NULL,
`passport_id` INT NOT NULL UNIQUE
);

CREATE TABLE `passports`(
`passport_id` INT NOT NULL PRIMARY KEY  ,
`passport_number` VARCHAR(50) NOT NULL
);


  INSERT INTO `passports`
(`passport_id`,
`passport_number`)
VALUES
(101,'N34FG21B'),
(102,'K65LO4R7'),
(103,'ZE657QP2');

INSERT INTO `persons`
(`person_id`,
`first_name`,
`salary`,
`passport_id`)
VALUES
(1,'Roberto', 43300.00,102),
(2,'Tom', 56100.00,103),
(3,'Yana', 60200.00,101);

ALTER TABLE `persons`
ADD CONSTRAINT `fk_person_passport`
  FOREIGN KEY (`passport_id`)
  REFERENCES `passports` (`passport_id`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;












  -- 2ra


  CREATE TABLE `manufacturers`(
	`manufacturer_id` INT PRIMARY KEY AUTO_INCREMENT,
	`name` VARCHAR(50),
	`established_on` DATE
  );
  CREATE TABLE `models`(
	`model_id` INT PRIMARY KEY AUTO_INCREMENT,
	`name` VARCHAR(40),
	`manufacturer_id` INT,
    CONSTRAINT fk_models_manuefacturers
    FOREIGN KEY (`manufacturer_id`)
    REFERENCES `manufacturers`(`manufacturer_id`)
  );

INSERT
	INTO `manufacturers` (`name`, `established_on`)
	VALUES
		('BMW', '1916-03-01'),
		('Tesla', '2003-01-01'),
		('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);



  -- 3ta
CREATE TABLE `students` (
    `student_id` INT PRIMARY KEY AUTO_INCREMENT,
    `name` VARCHAR(40)
);

CREATE TABLE `exams` (
    `exam_id` INT PRIMARY KEY AUTO_INCREMENT,
    `name` VARCHAR(40)
)  AUTO_INCREMENT=101;


CREATE TABLE `students_exams` (
    `student_id` INT,
    `exam_id` INT,
    CONSTRAINT PRIMARY KEY (`student_id` , `exam_id`),
    CONSTRAINT FOREIGN KEY (`student_id`)
        REFERENCES `students` (`student_id`),
    CONSTRAINT FOREIGN KEY (`exam_id`)
        REFERENCES `exams` (`exam_id`)
);


  INSERT INTO `students`(`name`) VALUES
  ('Mila'),
  ('Toni'),
  ('Ron');

 INSERT INTO `exams` (`name`)VALUES
 ('Spring MVC'),
 ('Neo4j'),
 ('Oracle 11g');


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




  -- 4ta


CREATE TABLE `teachers`(
`teacher_id` INT PRIMARY KEY AUTO_INCREMENT,
`name`VARCHAR(50),
`manager_id` INT DEFAULT NULL
) AUTO_INCREMENT = 101;

  INSERT INTO `teachers`
(
`name`,
`manager_id`)
VALUES
('John',NULL),
('Maya',106),
('Silvia',106),
('Ted',105),
('Mark',101),
('Greta',101)
;

  ALTER TABLE `teachers` ADD CONSTRAINT FOREIGN KEY(`manager_id`) REFERENCES
  `teachers`(`teacher_id`);


--   5ta zad

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

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


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

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

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

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





--  6ta


 CREATE TABLE `majors`(
 `major_id` INT,
 `name` VARCHAR(50)
 );
 ALTER TABLE `majors` ADD CONSTRAINT PRIMARY KEY(`major_id`);

 CREATE TABLE `payments`(
 `payment_id` INT PRIMARY KEY,
 `payment_date` DATE,
 `payment_amount` DECIMAL(8,2),
 `student_id` INT
);

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

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


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



-- 9ta

SELECT m.`mountain_range`,
 e.`peak_name`,e.`elevation` AS `peak_elevation` FROM `peaks`AS e
 JOIN `mountains`AS m ON m.id= e.mountain_id
 WHERE `mountain_range` = 'Rila'
 ORDER  BY `peak_elevation` DESC;