Advertisement
YavorGrancharov

Minions

Sep 26th, 2017
360
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
MySQL 9.97 KB | None | 0 0
  1. CREATE DATABASE `minions`;
  2. -------------------------1
  3. USE minions;
  4. CREATE TABLE minions(
  5.     id INT PRIMARY KEY AUTO_INCREMENT,
  6.     name VARCHAR(30) NOT NULL,
  7.     age INT
  8. );
  9.  
  10. CREATE TABLE towns(
  11.     id INT PRIMARY KEY AUTO_INCREMENT,
  12.     name VARCHAR(30) NOT NULL
  13. );
  14. -------------------------------2
  15. ALTER TABLE minions
  16. ADD town_id INT;
  17. ALTER TABLE minions
  18. ADD CONSTRAINT fk_minions_towns
  19. FOREIGN KEY (town_id) REFERENCES towns(id);
  20. ----------------------------------3
  21. INSERT INTO towns(id, name)
  22. VALUES (1, 'Sofia'),
  23. (2, 'Plovdiv'),
  24. (3, 'Varna');
  25.  
  26. INSERT INTO minions(id, name, age, town_id)
  27. VALUES (1, 'Kevin', 22, 1),
  28. (2, 'Bob', 15, 3),
  29. (3, 'Steward', NULL, 2);
  30. ---------------------------------4
  31. TRUNCATE TABLE minions;
  32. --------------------------5
  33. DROP TABLE minions;
  34. DROP TABLE towns;
  35. ------------------------6
  36. CREATE TABLE people(
  37.     id INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
  38.     name VARCHAR(200) NOT NULL,
  39.     picture MEDIUMBLOB,
  40.     height FLOAT(6,2),
  41.     weight FLOAT(6,2),
  42.     gender ENUM('m','f') NOT NULL,
  43.     birthdate DATETIME NOT NULL,
  44.     biography TEXT
  45. );
  46.  
  47. INSERT INTO people(id,name,gender,birthdate,biography)
  48. VALUES (1,'Peter','m','1979-12-02','born'),
  49. (2,'Peter','m','1979-12-02','born'),
  50. (3,'Peter','m','1979-12-02','born'),
  51. (4,'Peter','m','1979-12-02','born'),
  52. (5,'Peter','m','1979-12-02','born');
  53. ------------------------------------7
  54. CREATE TABLE users(
  55.     id BIGINT UNIQUE PRIMARY KEY AUTO_INCREMENT,
  56.     username VARCHAR(30) UNIQUE NOT NULL,
  57.     password VARCHAR(26) NOT NULL,
  58.     profile_picture BLOB,
  59.     last_login_time DATE,
  60.     is_deleted BOOL
  61. );
  62.  
  63. INSERT INTO users(id,username,password,last_login_time,is_deleted)
  64. VALUES (1,'Ivan','dsjflsdj','2017-09-12',TRUE),
  65. (2,'Pesho','adasdad','2017-08-12',FALSE),
  66. (3,'Todor','aghfghfg','2017-07-12',TRUE),
  67. (4,'Svet','ouiouio','2017-06-12',FALSE),
  68. (5,'Milan','qeqeqwewq','2017-05-12',TRUE);
  69. --------------------------------------------8
  70. ALTER TABLE users
  71. DROP PRIMARY KEY,
  72. ADD CONSTRAINT pk_users PRIMARY KEY (`id`,`username`);
  73. ---------------------------------9
  74. ALTER TABLE users
  75. MODIFY COLUMN last_login_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;
  76. -------------------------10
  77. ALTER TABLE users
  78. MODIFY id BIGINT NOT NULL;
  79. ALTER TABLE users
  80. DROP PRIMARY KEY;
  81. ALTER TABLE users
  82. ADD CONSTRAINT pk_users PRIMARY KEY(id);
  83. ALTER TABLE users
  84. ADD CONSTRAINT uq_username UNIQUE (username);
  85. ---------------------11
  86. CREATE DATABASE `movies`;
  87. USE movies;
  88. CREATE TABLE directors(
  89.     id INT PRIMARY KEY AUTO_INCREMENT,
  90.     director_name VARCHAR(50) NOT NULL,
  91.     notes TEXT
  92. );
  93.  
  94. INSERT INTO directors(id,director_name)
  95. VALUES (1,'Mark'),(2,'Peter'),(3,'Frank'),(4,'Simon'),(5,'Mojo');
  96.  
  97. CREATE TABLE genres(
  98.     id INT PRIMARY KEY AUTO_INCREMENT,
  99.     genre_name VARCHAR(50) NOT NULL,
  100.     notes TEXT
  101. );
  102.  
  103. INSERT INTO genres(id,genre_name)
  104. VALUES (1,'Sci-Fi'),(2,'Sci-Fir'),(3,'Sci-Fi'),(4,'Sci-Fin'),(5,'Sci-Fi');
  105.  
  106. CREATE TABLE categories(
  107.     id INT PRIMARY KEY AUTO_INCREMENT,
  108.     category_name VARCHAR(50) NOT NULL,
  109.     notes TEXT
  110. );
  111.  
  112. INSERT INTO categories(id,category_name)
  113. VALUES (1,'Sci-Fi'),(2,'Sci-Fir'),(3,'Sci-Fi'),(4,'Sci-Fin'),(5,'Sci-Fi');
  114.  
  115. CREATE TABLE movies(
  116.     id INT PRIMARY KEY AUTO_INCREMENT,
  117.     title VARCHAR(30) NOT NULL,
  118.     director_id INT,
  119.     copyright_year DATETIME NOT NULL,
  120.     length INT,
  121.     genre_id INT,
  122.     category_id INT,
  123.     rating INT,
  124.     notes TEXT
  125. );
  126.  
  127. INSERT INTO movies(id,title,copyright_year,length,genre_id,category_id)
  128. VALUES (11, 'Marko','2016-05-09',12,1,3),
  129. (12, 'Marko','2016-05-09',12,1,3),
  130. (15, 'Marko','2016-05-09',12,1,3),
  131. (16, 'Marko','2016-05-09',12,1,3),
  132. (18, 'Marko','2016-05-09',12,1,3);
  133. ------------------------------12
  134. 83% correct
  135. CREATE DATABASE `car_rental`;
  136. USE car_rental;
  137. CREATE TABLE categories(
  138.     id INT PRIMARY KEY AUTO_INCREMENT,
  139.     category VARCHAR(50) NOT NULL,
  140.     daily_rate INT,
  141.     weekly_rate INT,
  142.     monthly_rate INT,
  143.     weekend_rate INT
  144. );
  145.  
  146. INSERT INTO categories(category)
  147. VALUES ('jljsflsjk'),('jljsflsjk'),('jljsflsjk');
  148.  
  149. CREATE TABLE cars(
  150.     id INT PRIMARY KEY AUTO_INCREMENT,
  151.     plate_number INT NOT NULL,
  152.     make VARCHAR(30) NOT NULL,
  153.     model VARCHAR(30) NOT NULL,
  154.     car_year DATETIME,
  155.     category_id INT,
  156.     doors INT,
  157.     picture BLOB,
  158.     car_condition VARCHAR(30),
  159.     available BIT
  160. );
  161.  
  162. INSERT INTO cars(plate_number,make,model)
  163. VALUES (20,'jljk','lklk'),(14,'where','jhkhkhk'),(11,'astra','asdasdas');
  164.  
  165. CREATE TABLE employees(
  166.     id INT PRIMARY KEY AUTO_INCREMENT,
  167.     first_name VARCHAR(30) NOT NULL,
  168.     last_name VARCHAR(30) NOT NULL,
  169.     title VARCHAR(50),
  170.     notes TEXT
  171. );
  172.  
  173. INSERT INTO employees(first_name, last_name)
  174. VALUES ('emil','bogdanov'),('ivailo','vasilev'),('biser','stoimenov');
  175.  
  176. CREATE TABLE customers(
  177.     id INT PRIMARY KEY AUTO_INCREMENT,
  178.     driver_licence_number VARCHAR(50) NOT NULL,
  179.     full_name VARCHAR(50) NOT NULL,
  180.     adress VARCHAR(50),
  181.     city VARCHAR(50) NOT NULL,
  182.     zip_code VARCHAR(50),
  183.     notes TEXT
  184. );
  185.  
  186. INSERT INTO customers(driver_licence_number,full_name,city)
  187. VALUES ('0809808080','jahdkadhakjds','098098'),
  188. ('0809808080','jhkhkhkh','jahdkadhakjds'),
  189. ('0809808080','jhkhkhkh','jahdkadhakjds');
  190.  
  191. CREATE TABLE rental_orders(
  192.     id INT PRIMARY KEY AUTO_INCREMENT,
  193.     employee_id INT NOT NULL,
  194.     customer_id INT NOT NULL,
  195.     car_id INT NOT NULL,
  196.     car_condition VARCHAR(50),
  197.     tank_level DECIMAL(20,2),
  198.     kilometrage_start INT,
  199.     kilometrage_end INT,
  200.     total_kilometrage INT,
  201.     start_date DATETIME,
  202.     end_date DATETIME,
  203.     total_days INT,
  204.     rate_applied VARCHAR(50),
  205.     tax_rate INT,
  206.     order_status VARCHAR(30),
  207.     notes TEXT
  208. );
  209.  
  210. INSERT INTO rental_orders(employee_id,customer_id,car_id)
  211. VALUES (5,2,1),(5,2,1),(5,2,1);
  212. ------------------------------13
  213. CREATE DATABASE `hotel`;
  214. USE hotel;
  215. CREATE TABLE employees(
  216.     id INT PRIMARY KEY AUTO_INCREMENT,
  217.     first_name VARCHAR(30) NOT NULL,
  218.     last_name VARCHAR(30) NOT NULL,
  219.     title VARCHAR(50),
  220.     notes TEXT
  221. );
  222.  
  223. INSERT INTO employees(first_name, last_name)
  224. VALUES ('emil','bogdanov'),('ivailo','vasilev'),('biser','stoimenov');
  225.  
  226. CREATE TABLE customers(
  227.     account_number INT PRIMARY KEY AUTO_INCREMENT,
  228.     first_name VARCHAR(30) NOT NULL,
  229.     last_name VARCHAR(30),
  230.     phone_number VARCHAR(30),
  231.     emergency_name VARCHAR(30),
  232.     emergency_number VARCHAR(30),
  233.     notes TEXT
  234. );
  235.  
  236. INSERT INTO customers(account_number,first_name,phone_number)
  237. VALUES (0809808080,'jahdkadhakjds','098098'),
  238. (5757,'jhkhkhkh','jahdkadhakjds'),
  239. (423423423,'jhkhkhkh','jahdkadhakjds');
  240.  
  241. CREATE TABLE room_status(
  242.     room_status VARCHAR(20) PRIMARY KEY NOT NULL,
  243.     notes TEXT
  244. );
  245.  
  246. INSERT INTO room_status(room_status)
  247. VALUES ('free'),('notfree'),('isfree');
  248.  
  249. CREATE TABLE room_types(
  250.     room_type VARCHAR(30) PRIMARY KEY NOT NULL,
  251.     notes TEXT
  252. );
  253.  
  254. INSERT INTO room_types(room_type)
  255. VALUES ('lkjljl'),('qwewq'),('bcvbc');
  256.  
  257. CREATE TABLE bed_types(
  258.     bed_type VARCHAR(30) PRIMARY KEY NOT NULL,
  259.     notes TEXT
  260. );
  261.  
  262. INSERT INTO bed_types(bed_type)
  263. VALUES ('ljlhgg'),('qeqw'),('ghjghj');
  264.  
  265. CREATE TABLE rooms(
  266.     room_number INT PRIMARY KEY NOT NULL,
  267.     room_type VARCHAR(30),
  268.     bed_type VARCHAR(30),
  269.     rate INT,
  270.     room_status VARCHAR(30),
  271.     notes TEXT
  272. );
  273.  
  274. INSERT INTO rooms(room_number)
  275. VALUES (1),(2),(3);
  276.  
  277. CREATE TABLE payments(
  278.     id INT PRIMARY KEY AUTO_INCREMENT,
  279.     employee_id VARCHAR(30) NOT NULL,
  280.     payment_date DATETIME,
  281.     account_number INT,
  282.     first_date_occupied DATETIME,
  283.     last_date_occupied DATETIME,
  284.     total_days INT,
  285.     amount_charged DECIMAL(20,2),
  286.     tax_rate INT,
  287.     tax_amount INT,
  288.     payment_total DECIMAL(20,2),
  289.     notes TEXT
  290. );
  291.  
  292. INSERT INTO payments(employee_id)
  293. VALUES ('435433'),('3131231'),('786786786');
  294.  
  295. CREATE TABLE occupancies(
  296.     id INT PRIMARY KEY AUTO_INCREMENT,
  297.     employee_id VARCHAR(30) NOT NULL,
  298.     date_occupied DATETIME,
  299.     account_number INT,
  300.     room_number INT,
  301.     rate_applied INT,
  302.     phone_charge INT,
  303.     notes TEXT
  304. );
  305.  
  306. INSERT INTO occupancies(employee_id)
  307. VALUES ('435433'),('3131231'),('786786786');
  308. -----------------------14
  309. CREATE DATABASE `soft_uni`;
  310. USE soft_uni;
  311. CREATE TABLE towns(
  312.     id INT PRIMARY KEY AUTO_INCREMENT,
  313.     name VARCHAR(30) NOT NULL
  314. );
  315.  
  316. CREATE TABLE addresses(
  317.     id INT PRIMARY KEY AUTO_INCREMENT,
  318.     address_text VARCHAR(50) NOT NULL,
  319.     town_id INT,
  320.     CONSTRAINT fk_addresses_towns
  321.     FOREIGN KEY (town_id) REFERENCES towns(id)
  322. );
  323.  
  324. CREATE TABLE departments(
  325.     id INT PRIMARY KEY AUTO_INCREMENT,
  326.     name VARCHAR(30) NOT NULL
  327. );
  328.  
  329. CREATE TABLE employees(
  330.     id INT PRIMARY KEY AUTO_INCREMENT,
  331.     first_name VARCHAR(20) NOT NULL,
  332.     middle_name VARCHAR(20),
  333.     last_name VARCHAR(20) NOT NULL,
  334.     job_title VARCHAR(20),
  335.     department_id INT,
  336.     hire_date DATETIME NOT NULL,
  337.     salary DOUBLE NOT NULL,
  338.     address_id INT,
  339.     CONSTRAINT fk_address_id
  340.     FOREIGN KEY (address_id) REFERENCES addresses(id),
  341.     CONSTRAINT fk_department_id
  342.     FOREIGN KEY (department_id) REFERENCES departments(id)
  343. );
  344. -----------------------15
  345. USE soft_uni;
  346. INSERT INTO towns(name)
  347. VALUES ('Sofia'),('Plovdiv'),('Varna'),('Burgas');
  348. INSERT INTO departments(name)
  349. VALUES ('Engineering'),('Sales'),('Marketing'),('Software Development'),('Quality Assurance');
  350. INSERT INTO employees(first_name,middle_name,last_name,job_title,department_id,hire_date,salary)
  351. VALUES ('Ivan','Ivanov','Ivanov','.NET Developer',4,'2013-02-01',3500.00),
  352. ('Petar','Petrov','Petrov','Senior Engineer',1,'2004-03-02',4000.00),
  353. ('Maria','Petrova','Ivanova','Intern',5,'2016-08-28',525.25),
  354. ('Georgi','Terziev','Ivanov','CEO',2,'2007-12-09',3000.00),
  355. ('Peter','Pan','Pan','Intern',3,'2016-08-28',599.88);
  356. ----------------------16
  357. USE soft_uni;
  358. SELECT * FROM towns;
  359. SELECT * FROM departments;
  360. SELECT * FROM employees;
  361. ------------------17
  362. USE soft_uni;
  363. SELECT * FROM towns ORDER BY name;
  364. SELECT * FROM departments ORDER BY name;
  365. SELECT * FROM employees ORDER BY salary DESC;
  366. -------------------18
  367. USE softuni;
  368. SELECT name FROM towns ORDER BY name;
  369. SELECT name FROM departments ORDER BY name;
  370. SELECT first_name,last_name,job_title,salary FROM employees ORDER BY salary DESC;
  371. ---------------19
  372. UPDATE employees
  373. SET salary=salary*1.1;
  374. SELECT salary FROM employees;
  375. ---------------20
  376. USE hotel;
  377. UPDATE payments
  378. SET tax_rate=tax_rate*0.97;
  379. SELECT tax_rate FROM payments;
  380. ------------------21
  381. USE hotel;
  382. DELETE FROM occupancies;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement