deyanmalinov

11-mysql

Jun 13th, 2021 (edited)
137
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. create table passports (
  2. `passport_id` INT PRIMARY KEY AUTO_INCREMENT,
  3. `passport_number` VARCHAR(45) UNIQUE
  4. );
  5. create table people (
  6. `person_id` INT PRIMARY KEY AUTO_INCREMENT,
  7. `first_name` VARCHAR(45) not NULL,
  8. `salary` DECIMAL(10, 2) ,
  9. `passport_id` int unique,
  10. FOREIGN KEY (`passport_id`)
  11. REFERENCES `passports` (`passport_id`)
  12. );
  13. INSERT INTO `passports` (`passport_id`, `passport_number`) VALUES
  14. ('101', 'N34FG21B'),
  15. ('102', 'K65LO4R7'),
  16. ('103', 'ZE657QP2');
  17. INSERT INTO `persons` (`first_name`, `salary`, `passport_id`) VALUES
  18. ('Roberto ', '43300.00', '102'),
  19. ('Tom', '56100.00', '103'),
  20. ('Yana', '60200.00', '101');
  21.  
  22. *************************************************************************
  23.  
  24. create table manufacturers (
  25. manufacturer_id int primary key auto_increment,
  26. `name` varchar(20),
  27. established_on date
  28. );
  29. create table models (
  30. model_id int primary key auto_increment,
  31. `name` varchar(20) not null,
  32. manufacturer_id int,
  33. foreign key (manufacturer_id)
  34. references manufacturers(manufacturer_id)
  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. INSERT INTO `models` (`model_id`, `name`, `manufacturer_id`)
  42. VALUES
  43. ('101', 'X1', '1'),
  44. ('102', 'i6', '1'),
  45. ('103', 'Model S', '2'),
  46. ('104', 'Model X', '2'),
  47. ('105', 'Model 3', '2'),
  48. ('106', 'Nova', '3');
  49.  
  50. **************************************************************************
  51.  
  52. CREATE TABLE `students` (
  53. `student_id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  54. `name` VARCHAR(45) NOT NULL);
  55.  
  56. CREATE TABLE `exams` (
  57. `exam_id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  58. `name` VARCHAR(45) NOT NULL);
  59.  
  60. CREATE TABLE `students_exams` (
  61. `student_id` INT NOT NULL,
  62. `exam_id` INT NOT NULL,
  63. PRIMARY KEY (`student_id`,`exam_id`),
  64. CONSTRAINT `fk_exams`
  65. FOREIGN KEY (`exam_id`)
  66. REFERENCES `exams` (`exam_id`),
  67. CONSTRAINT `fk_students`
  68. FOREIGN KEY (`student_id`)
  69. REFERENCES `students` (`student_id`));
  70. INSERT INTO `students` (`student_id`, `name`)
  71. VALUES
  72. ('1', 'Mila'),
  73. ('2', 'Toni'),
  74. ('3', 'Ron');
  75. INSERT INTO `exams` (`exam_id`, `name`)
  76. VALUES
  77. ('101', 'Spring MVC'),
  78. ('102', 'Neo4j'),
  79. ('103', 'Oracle 11g');
  80. INSERT INTO `students_exams` (`student_id`, `exam_id`)
  81. VALUES
  82. ('1', '101'),
  83. ('1', '102'),
  84. ('2', '101'),
  85. ('3', '103'),
  86. ('2', '102'),
  87. ('2', '103');
  88.  
  89. **********************************************************************
  90.  
  91. CREATE TABLE `teachers` (
  92. `teacher_id` INT primary key AUTO_INCREMENT,
  93. `name` VARCHAR(45) NOT NULL,
  94. `manager_id` INT
  95. );
  96.    
  97. INSERT INTO `teachers` (`teacher_id`, `name`, `manager_id`) VALUES
  98. ('101', 'John', null),
  99. ('102', 'Maya', '106'),
  100. ('103', 'Silvia', '106'),
  101. ('104', 'Ted', '105'),
  102. ('105', 'Mark', '101'),
  103. ('106', 'Greta', '101');
  104.    
  105. alter table `teachers`
  106. add constraint fk_teachers_managers
  107. foreign key (`manager_id`)
  108. references `teachers`(`teacher_id`);
  109.  
  110. *********************************************************************
  111.  
  112. CREATE TABLE `item_types` (
  113. `item_type_id` INT PRIMARY KEY AUTO_INCREMENT,
  114. `name` VARCHAR(50));
  115.  
  116. CREATE TABLE `customers` (
  117. `customer_id` INT PRIMARY KEY AUTO_INCREMENT,
  118. `name` VARCHAR(50),
  119. `birthday` DATE,
  120. `city_id` INT,
  121. FOREIGN KEY (`city_id`)
  122. REFERENCES `cities` (`city_id`));
  123.  
  124. CREATE TABLE `cities` (
  125. `city_id` INT PRIMARY KEY AUTO_INCREMENT,
  126. `name` VARCHAR(50));
  127.  
  128. CREATE TABLE `orders` (
  129. `order_id` INT PRIMARY KEY AUTO_INCREMENT,
  130. `customer_id` INT,
  131. FOREIGN KEY (`customer_id`)
  132. REFERENCES `customers` (`customer_id`));
  133.  
  134. CREATE TABLE `items` (
  135. `item_id` INT PRIMARY KEY AUTO_INCREMENT,
  136. `name` VARCHAR(50),
  137. `item_type_id` INT,
  138. FOREIGN KEY (`item_type_id`)
  139. REFERENCES `item_types` (`item_type_id`));
  140.  
  141. CREATE TABLE `order_items` (
  142. `item_id` INT ,
  143. `order_id` INT,
  144. PRIMARY KEY (`order_id`, `item_id`),
  145. FOREIGN KEY (`order_id`)
  146. REFERENCES `orders` (`order_id`),
  147. FOREIGN KEY (`item_id`)
  148. REFERENCES `items` (`item_id`));
  149.  
  150. **************************************************************************
  151.  
  152. CREATE TABLE `majors` (
  153. `major_id` INT PRIMARY KEY AUTO_INCREMENT,
  154. `name` VARCHAR(50));
  155.  
  156. CREATE TABLE `subjects` (
  157. `subject_id` INT PRIMARY KEY AUTO_INCREMENT,
  158. `subject_name` VARCHAR(50));
  159.  
  160. CREATE TABLE `students` (
  161. `student_id` INT PRIMARY KEY AUTO_INCREMENT,
  162. `student_number` VARCHAR(12),
  163. `student_name` VARCHAR(50),
  164. `major_id` INT,
  165. FOREIGN KEY (`major_id`)
  166. REFERENCES `majors` (`major_id`));
  167.  
  168. CREATE TABLE `payments` (
  169. `payment_id` INT PRIMARY KEY AUTO_INCREMENT,
  170. `payment_date` date,
  171. `payment_amount` DECIMAL(8,2),
  172. `student_id` INT,
  173. FOREIGN KEY (`student_id`)
  174. REFERENCES `students` (`student_id`));
  175.  
  176. CREATE TABLE `agenda` (
  177. `student_id` INT,
  178. `subject_id` INT,
  179. PRIMARY KEY (`student_id`, `subject_id`),
  180. FOREIGN KEY (`student_id`)
  181. REFERENCES `students` (`student_id`),
  182. FOREIGN KEY (`subject_id`)
  183. REFERENCES `subjects` (`subject_id`));
  184.  
  185. **************************************************************************
  186. SELECT  mr.mountain_range, pk.peak_name, pk.elevation FROM peaks as pk
  187. join mountains as mr
  188. on mr.id=pk.mountain_id
  189. where mountain_range = 'Rila'
  190. order by elevation desc;
  191.  
RAW Paste Data