Advertisement
desislava_topuzakova

Table Relations - Exercise

Sep 29th, 2022
1,509
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
MySQL 5.65 KB | None | 0 0
  1. # 1. One-To-One Relationship
  2.  
  3. CREATE TABLE `passports` (
  4.    `passport_id` INT PRIMARY KEY AUTO_INCREMENT,
  5.    `passport_number` VARCHAR(50) UNIQUE);
  6.  
  7. INSERT INTO `passports` (`passport_id`, `passport_number`)
  8. VALUES
  9.     (101, 'N34FG21B'),
  10.     (102, 'K65LO4R7'),
  11.     (103, 'ZE657QP2');
  12.  
  13. CREATE TABLE `people` (
  14.    `person_id` INT PRIMARY KEY AUTO_INCREMENT,
  15.    `first_name` VARCHAR(45),
  16.    `salary` DECIMAL(9, 2),
  17.    `passport_id` INT UNIQUE,
  18. CONSTRAINT fk_pe_pa
  19. FOREIGN KEY (`passport_id`)
  20. REFERENCES `passports`(`passport_id`));
  21.  
  22. INSERT INTO `people` (`person_id`, `first_name`, `salary`, `passport_id`)
  23. VALUES
  24.     (1, 'Roberto', 43300.00, 102),
  25.     (2, 'Tom', 56100.00, 103),
  26.     (3, 'Yana', 60200.00, 101);
  27.    
  28. # 02. One-To-Many Relationship
  29.  
  30. CREATE TABLE `manufacturers` (
  31.    `manufacturer_id` INT PRIMARY KEY AUTO_INCREMENT,
  32.    `name` VARCHAR(45) NOT NULL UNIQUE,
  33.    `established_on` DATE
  34. );
  35.  
  36. INSERT INTO `manufacturers` (`manufacturer_id`, `name`, `established_on`)
  37. VALUES
  38.     (1, 'BMW', '1916/03/01'),
  39.     (2, 'Tesla', '2003/01/01'),
  40.     (3, 'Lada', '1966/05/01');
  41.  
  42. CREATE TABLE `models` (
  43.    `model_id` INT PRIMARY KEY AUTO_INCREMENT,
  44.    `name` VARCHAR(45) NOT NULL,
  45.    `manufacturer_id` INT,
  46. CONSTRAINT fk_model_manufacturers
  47. FOREIGN KEY (`manufacturer_id`)
  48. REFERENCES `manufacturers`(`manufacturer_id`));
  49.  
  50. INSERT INTO `models` (`model_id`, `name`, `manufacturer_id`)
  51. VALUES
  52.     (101, 'X1', 1),
  53.     (102, 'i6', 1),
  54.     (103, 'Model S', 2),
  55.     (104, 'Model X', 2),
  56.     (105, 'Model 3', 2),
  57.     (106, 'Nova', 3);
  58.    
  59. # 03. Many-To-Many Relationship
  60.  
  61. CREATE TABLE `students` (
  62.    `student_id` INT PRIMARY KEY AUTO_INCREMENT,
  63.    `name` VARCHAR(45) NOT NULL);
  64.  
  65. INSERT INTO `students` (`student_id`, `name`)
  66. VALUES
  67.     (1, 'Mila'),
  68.     (2, 'Toni'),
  69.     (3, 'Ron');
  70.  
  71. CREATE TABLE `exams` (
  72.    `exam_id` INT PRIMARY KEY AUTO_INCREMENT,
  73.    `name` VARCHAR(45) NOT NULL);
  74.  
  75. INSERT INTO `exams` (`exam_id`, `name`)
  76. VALUES
  77.     (101, 'Spring MVC'),
  78.     (102, 'Neo4j'),
  79.     (103, 'Oracle 11g');
  80.  
  81. CREATE TABLE `students_exams` (
  82.    `student_id` INT NOT NULL,
  83.    `exam_id` INT NOT NULL,
  84.    CONSTRAINT pk
  85.    PRIMARY KEY (`student_id`, `exam_id`),
  86.    CONSTRAINT fk_this_student
  87.    FOREIGN KEY (`student_id`)
  88.    REFERENCES `students` (`student_id`),
  89.    CONSTRAINT fk_this_exams
  90.    FOREIGN KEY (`exam_id`)
  91.    REFERENCES `exams` (`exam_id`)
  92. );
  93.    
  94. INSERT INTO `students_exams` (`student_id`, `exam_id`)
  95. VALUES
  96.     (1, 101),
  97.     (1, 102),
  98.     (2, 101),
  99.     (3, 103),
  100.     (2, 102),
  101.     (2, 103);
  102.    
  103. # 4. Self-Referencing    
  104.    
  105.  CREATE TABLE `teachers` (
  106.    `teacher_id` INT PRIMARY KEY AUTO_INCREMENT,
  107.    `name` VARCHAR(20) NOT NULL,
  108.    `manager_id` INT);
  109.                            
  110. INSERT INTO `teachers` (`teacher_id`, `name`, `manager_id`)
  111. VALUES
  112.     (101, 'John', NULL),       
  113.     (102, 'Maya', 106),
  114.     (103, 'Silvia', 106),
  115.     (104, 'Ted', 105),
  116.     (105, 'Mark', 101),
  117.     (106, 'Greta', 101);
  118.                              
  119. ALTER TABLE `teachers`
  120. ADD CONSTRAINT fk
  121. FOREIGN KEY (`manager_id`)
  122. REFERENCES `teachers` (`teacher_id`);
  123.  
  124. # 5. Online Store Database  
  125. CREATE TABLE `cities` (
  126.     `city_id` INT PRIMARY KEY AUTO_INCREMENT,
  127.     `name` VARCHAR(50) NOT NULL
  128. );
  129.  
  130. CREATE TABLE `item_types` (
  131.     `item_type_id` INT PRIMARY KEY AUTO_INCREMENT,
  132.     `name` VARCHAR(50) NOT NULL
  133. );
  134.  
  135. CREATE TABLE customers (
  136.     `customer_id` INT PRIMARY KEY AUTO_INCREMENT,
  137.     `name` VARCHAR(50) NOT NULL,
  138.     `birthday` DATE,
  139.     `city_id` INT NULL,
  140.     CONSTRAINT fk_customer_city
  141.     FOREIGN KEY (`city_id`)
  142.     REFERENCES `cities` (`city_id`)
  143. );
  144.  
  145. CREATE TABLE `orders` (
  146.     `order_id` INT PRIMARY KEY AUTO_INCREMENT,
  147.     `customer_id` INT NOT NULL,
  148.     CONSTRAINT fk_order_customer
  149.     FOREIGN KEY (`customer_id`)
  150.     REFERENCES `customers` (`customer_id`)
  151. );
  152.  
  153. CREATE TABLE `items` (
  154.     `item_id` INT PRIMARY KEY AUTO_INCREMENT,
  155.     `name` VARCHAR(50) NOT NULL,
  156.     `item_type_id` INT NOT NULL,
  157.     CONSTRAINT fk_items_type
  158.     FOREIGN KEY (`item_type_id`)
  159.     REFERENCES `item_types` (`item_type_id`)
  160. );
  161.  
  162. CREATE TABLE `order_items` (
  163.     `order_id` INT NOT NULL,
  164.     `item_id` INT NOT NULL,
  165.     CONSTRAINT pk
  166.     PRIMARY KEY(`order_id`, `item_id`),
  167.     CONSTRAINT fk_order
  168.     FOREIGN KEY (`order_id`)
  169.     REFERENCES `orders` (`order_id`),
  170.     CONSTRAINT fk_item
  171.     FOREIGN KEY (`item_id`)
  172.     REFERENCES `items` (`item_id`)
  173. );
  174.  
  175. # 6. University Database
  176. CREATE TABLE `subjects` (
  177.     `subject_id` INT PRIMARY KEY AUTO_INCREMENT,
  178.     `subject_name` VARCHAR(50) NOT NULL
  179. );
  180.  
  181. CREATE TABLE `majors` (
  182.     `major_id` INT PRIMARY KEY AUTO_INCREMENT,
  183.     `name` VARCHAR(50) NOT NULL
  184. );
  185.  
  186. CREATE TABLE `students` (
  187.     `student_id` INT PRIMARY KEY AUTO_INCREMENT,
  188.     `student_number` VARCHAR(12) UNIQUE NOT NULL,
  189.     `student_name` VARCHAR(50) NOT NULL,
  190.     `major_id` INT NULL,
  191.     CONSTRAINT fk_student_major
  192.     FOREIGN KEY (`major_id`)
  193.     REFERENCES `majors`(`major_id`)
  194. );
  195.  
  196. CREATE TABLE `payments` (
  197.     `payment_id` INT PRIMARY KEY AUTO_INCREMENT,
  198.     `payment_date` DATE NOT NULL,
  199.     `payment_amount` DECIMAL(8, 2),
  200.     `student_id` INT NULL,
  201.     CONSTRAINT fk_payment_student
  202.     FOREIGN KEY (`student_id`)
  203.     REFERENCES `students` (`student_id`)
  204. );
  205.  
  206. CREATE TABLE `agenda` (
  207.     `student_id` INT NOT NULL,
  208.     `subject_id` INT NOT NULL,
  209.     CONSTRAINT pk
  210.     PRIMARY KEY(`student_id`, `subject_id`),
  211.     CONSTRAINT fk_student
  212.     FOREIGN KEY (`student_id`)
  213.     REFERENCES `students` (`student_id`),
  214.     CONSTRAINT fk_item
  215.     FOREIGN KEY (`subject_id`)
  216.     REFERENCES `subjects` (`subject_id`)
  217. );
  218.  
  219. # 9. Peaks in Rila
  220. SELECT m.`mountain_range`, p.`peak_name`, p.`elevation`
  221. FROM `mountains` AS m
  222. JOIN `peaks` AS p
  223. ON p.`mountain_id` = m.`id`
  224. WHERE m.`mountain_range` = "Rila"
  225. ORDER BY p.`elevation` DESC;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement