Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE DATABASE `minions`;
- -------------------------1
- USE minions;
- CREATE TABLE minions(
- id INT PRIMARY KEY AUTO_INCREMENT,
- name VARCHAR(30) NOT NULL,
- age INT
- );
- CREATE TABLE towns(
- id INT PRIMARY KEY AUTO_INCREMENT,
- name VARCHAR(30) NOT NULL
- );
- -------------------------------2
- ALTER TABLE minions
- ADD town_id INT;
- ALTER TABLE minions
- ADD CONSTRAINT fk_minions_towns
- FOREIGN KEY (town_id) REFERENCES towns(id);
- ----------------------------------3
- INSERT INTO towns(id, name)
- VALUES (1, 'Sofia'),
- (2, 'Plovdiv'),
- (3, 'Varna');
- INSERT INTO minions(id, name, age, town_id)
- VALUES (1, 'Kevin', 22, 1),
- (2, 'Bob', 15, 3),
- (3, 'Steward', NULL, 2);
- ---------------------------------4
- TRUNCATE TABLE minions;
- --------------------------5
- DROP TABLE minions;
- DROP TABLE towns;
- ------------------------6
- CREATE TABLE people(
- id INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
- name VARCHAR(200) NOT NULL,
- picture MEDIUMBLOB,
- height FLOAT(6,2),
- weight FLOAT(6,2),
- gender ENUM('m','f') NOT NULL,
- birthdate DATETIME NOT NULL,
- biography TEXT
- );
- INSERT INTO people(id,name,gender,birthdate,biography)
- VALUES (1,'Peter','m','1979-12-02','born'),
- (2,'Peter','m','1979-12-02','born'),
- (3,'Peter','m','1979-12-02','born'),
- (4,'Peter','m','1979-12-02','born'),
- (5,'Peter','m','1979-12-02','born');
- ------------------------------------7
- CREATE TABLE users(
- id BIGINT UNIQUE PRIMARY KEY AUTO_INCREMENT,
- username VARCHAR(30) UNIQUE NOT NULL,
- password VARCHAR(26) NOT NULL,
- profile_picture BLOB,
- last_login_time DATE,
- is_deleted BOOL
- );
- INSERT INTO users(id,username,password,last_login_time,is_deleted)
- VALUES (1,'Ivan','dsjflsdj','2017-09-12',TRUE),
- (2,'Pesho','adasdad','2017-08-12',FALSE),
- (3,'Todor','aghfghfg','2017-07-12',TRUE),
- (4,'Svet','ouiouio','2017-06-12',FALSE),
- (5,'Milan','qeqeqwewq','2017-05-12',TRUE);
- --------------------------------------------8
- ALTER TABLE users
- DROP PRIMARY KEY,
- ADD CONSTRAINT pk_users PRIMARY KEY (`id`,`username`);
- ---------------------------------9
- ALTER TABLE users
- MODIFY COLUMN last_login_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;
- -------------------------10
- ALTER TABLE users
- MODIFY id BIGINT NOT NULL;
- ALTER TABLE users
- DROP PRIMARY KEY;
- ALTER TABLE users
- ADD CONSTRAINT pk_users PRIMARY KEY(id);
- ALTER TABLE users
- ADD CONSTRAINT uq_username UNIQUE (username);
- ---------------------11
- CREATE DATABASE `movies`;
- USE movies;
- CREATE TABLE directors(
- id INT PRIMARY KEY AUTO_INCREMENT,
- director_name VARCHAR(50) NOT NULL,
- notes TEXT
- );
- INSERT INTO directors(id,director_name)
- VALUES (1,'Mark'),(2,'Peter'),(3,'Frank'),(4,'Simon'),(5,'Mojo');
- CREATE TABLE genres(
- id INT PRIMARY KEY AUTO_INCREMENT,
- genre_name VARCHAR(50) NOT NULL,
- notes TEXT
- );
- INSERT INTO genres(id,genre_name)
- VALUES (1,'Sci-Fi'),(2,'Sci-Fir'),(3,'Sci-Fi'),(4,'Sci-Fin'),(5,'Sci-Fi');
- CREATE TABLE categories(
- id INT PRIMARY KEY AUTO_INCREMENT,
- category_name VARCHAR(50) NOT NULL,
- notes TEXT
- );
- INSERT INTO categories(id,category_name)
- VALUES (1,'Sci-Fi'),(2,'Sci-Fir'),(3,'Sci-Fi'),(4,'Sci-Fin'),(5,'Sci-Fi');
- CREATE TABLE movies(
- id INT PRIMARY KEY AUTO_INCREMENT,
- title VARCHAR(30) NOT NULL,
- director_id INT,
- copyright_year DATETIME NOT NULL,
- length INT,
- genre_id INT,
- category_id INT,
- rating INT,
- notes TEXT
- );
- INSERT INTO movies(id,title,copyright_year,length,genre_id,category_id)
- VALUES (11, 'Marko','2016-05-09',12,1,3),
- (12, 'Marko','2016-05-09',12,1,3),
- (15, 'Marko','2016-05-09',12,1,3),
- (16, 'Marko','2016-05-09',12,1,3),
- (18, 'Marko','2016-05-09',12,1,3);
- ------------------------------12
- 83% correct
- CREATE DATABASE `car_rental`;
- USE car_rental;
- CREATE TABLE categories(
- id INT PRIMARY KEY AUTO_INCREMENT,
- category VARCHAR(50) NOT NULL,
- daily_rate INT,
- weekly_rate INT,
- monthly_rate INT,
- weekend_rate INT
- );
- INSERT INTO categories(category)
- VALUES ('jljsflsjk'),('jljsflsjk'),('jljsflsjk');
- CREATE TABLE cars(
- id INT PRIMARY KEY AUTO_INCREMENT,
- plate_number INT NOT NULL,
- make VARCHAR(30) NOT NULL,
- model VARCHAR(30) NOT NULL,
- car_year DATETIME,
- category_id INT,
- doors INT,
- picture BLOB,
- car_condition VARCHAR(30),
- available BIT
- );
- INSERT INTO cars(plate_number,make,model)
- VALUES (20,'jljk','lklk'),(14,'where','jhkhkhk'),(11,'astra','asdasdas');
- CREATE TABLE employees(
- id INT PRIMARY KEY AUTO_INCREMENT,
- first_name VARCHAR(30) NOT NULL,
- last_name VARCHAR(30) NOT NULL,
- title VARCHAR(50),
- notes TEXT
- );
- INSERT INTO employees(first_name, last_name)
- VALUES ('emil','bogdanov'),('ivailo','vasilev'),('biser','stoimenov');
- CREATE TABLE customers(
- id INT PRIMARY KEY AUTO_INCREMENT,
- driver_licence_number VARCHAR(50) NOT NULL,
- full_name VARCHAR(50) NOT NULL,
- adress VARCHAR(50),
- city VARCHAR(50) NOT NULL,
- zip_code VARCHAR(50),
- notes TEXT
- );
- INSERT INTO customers(driver_licence_number,full_name,city)
- VALUES ('0809808080','jahdkadhakjds','098098'),
- ('0809808080','jhkhkhkh','jahdkadhakjds'),
- ('0809808080','jhkhkhkh','jahdkadhakjds');
- CREATE TABLE rental_orders(
- id INT PRIMARY KEY AUTO_INCREMENT,
- employee_id INT NOT NULL,
- customer_id INT NOT NULL,
- car_id INT NOT NULL,
- car_condition VARCHAR(50),
- tank_level DECIMAL(20,2),
- kilometrage_start INT,
- kilometrage_end INT,
- total_kilometrage INT,
- start_date DATETIME,
- end_date DATETIME,
- total_days INT,
- rate_applied VARCHAR(50),
- tax_rate INT,
- order_status VARCHAR(30),
- notes TEXT
- );
- INSERT INTO rental_orders(employee_id,customer_id,car_id)
- VALUES (5,2,1),(5,2,1),(5,2,1);
- ------------------------------13
- CREATE DATABASE `hotel`;
- USE hotel;
- CREATE TABLE employees(
- id INT PRIMARY KEY AUTO_INCREMENT,
- first_name VARCHAR(30) NOT NULL,
- last_name VARCHAR(30) NOT NULL,
- title VARCHAR(50),
- notes TEXT
- );
- INSERT INTO employees(first_name, last_name)
- VALUES ('emil','bogdanov'),('ivailo','vasilev'),('biser','stoimenov');
- CREATE TABLE customers(
- account_number INT PRIMARY KEY AUTO_INCREMENT,
- first_name VARCHAR(30) NOT NULL,
- last_name VARCHAR(30),
- phone_number VARCHAR(30),
- emergency_name VARCHAR(30),
- emergency_number VARCHAR(30),
- notes TEXT
- );
- INSERT INTO customers(account_number,first_name,phone_number)
- VALUES (0809808080,'jahdkadhakjds','098098'),
- (5757,'jhkhkhkh','jahdkadhakjds'),
- (423423423,'jhkhkhkh','jahdkadhakjds');
- CREATE TABLE room_status(
- room_status VARCHAR(20) PRIMARY KEY NOT NULL,
- notes TEXT
- );
- INSERT INTO room_status(room_status)
- VALUES ('free'),('notfree'),('isfree');
- CREATE TABLE room_types(
- room_type VARCHAR(30) PRIMARY KEY NOT NULL,
- notes TEXT
- );
- INSERT INTO room_types(room_type)
- VALUES ('lkjljl'),('qwewq'),('bcvbc');
- CREATE TABLE bed_types(
- bed_type VARCHAR(30) PRIMARY KEY NOT NULL,
- notes TEXT
- );
- INSERT INTO bed_types(bed_type)
- VALUES ('ljlhgg'),('qeqw'),('ghjghj');
- CREATE TABLE rooms(
- room_number INT PRIMARY KEY NOT NULL,
- room_type VARCHAR(30),
- bed_type VARCHAR(30),
- rate INT,
- room_status VARCHAR(30),
- notes TEXT
- );
- INSERT INTO rooms(room_number)
- VALUES (1),(2),(3);
- CREATE TABLE payments(
- id INT PRIMARY KEY AUTO_INCREMENT,
- employee_id VARCHAR(30) NOT NULL,
- payment_date DATETIME,
- account_number INT,
- first_date_occupied DATETIME,
- last_date_occupied DATETIME,
- total_days INT,
- amount_charged DECIMAL(20,2),
- tax_rate INT,
- tax_amount INT,
- payment_total DECIMAL(20,2),
- notes TEXT
- );
- INSERT INTO payments(employee_id)
- VALUES ('435433'),('3131231'),('786786786');
- CREATE TABLE occupancies(
- id INT PRIMARY KEY AUTO_INCREMENT,
- employee_id VARCHAR(30) NOT NULL,
- date_occupied DATETIME,
- account_number INT,
- room_number INT,
- rate_applied INT,
- phone_charge INT,
- notes TEXT
- );
- INSERT INTO occupancies(employee_id)
- VALUES ('435433'),('3131231'),('786786786');
- -----------------------14
- CREATE DATABASE `soft_uni`;
- USE soft_uni;
- CREATE TABLE towns(
- id INT PRIMARY KEY AUTO_INCREMENT,
- name VARCHAR(30) NOT NULL
- );
- CREATE TABLE addresses(
- id INT PRIMARY KEY AUTO_INCREMENT,
- address_text VARCHAR(50) NOT NULL,
- town_id INT,
- CONSTRAINT fk_addresses_towns
- FOREIGN KEY (town_id) REFERENCES towns(id)
- );
- CREATE TABLE departments(
- id INT PRIMARY KEY AUTO_INCREMENT,
- name VARCHAR(30) NOT NULL
- );
- CREATE TABLE employees(
- id INT PRIMARY KEY AUTO_INCREMENT,
- first_name VARCHAR(20) NOT NULL,
- middle_name VARCHAR(20),
- last_name VARCHAR(20) NOT NULL,
- job_title VARCHAR(20),
- department_id INT,
- hire_date DATETIME NOT NULL,
- salary DOUBLE NOT NULL,
- address_id INT,
- CONSTRAINT fk_address_id
- FOREIGN KEY (address_id) REFERENCES addresses(id),
- CONSTRAINT fk_department_id
- FOREIGN KEY (department_id) REFERENCES departments(id)
- );
- -----------------------15
- USE soft_uni;
- INSERT INTO towns(name)
- VALUES ('Sofia'),('Plovdiv'),('Varna'),('Burgas');
- INSERT INTO departments(name)
- VALUES ('Engineering'),('Sales'),('Marketing'),('Software Development'),('Quality Assurance');
- INSERT INTO employees(first_name,middle_name,last_name,job_title,department_id,hire_date,salary)
- VALUES ('Ivan','Ivanov','Ivanov','.NET Developer',4,'2013-02-01',3500.00),
- ('Petar','Petrov','Petrov','Senior Engineer',1,'2004-03-02',4000.00),
- ('Maria','Petrova','Ivanova','Intern',5,'2016-08-28',525.25),
- ('Georgi','Terziev','Ivanov','CEO',2,'2007-12-09',3000.00),
- ('Peter','Pan','Pan','Intern',3,'2016-08-28',599.88);
- ----------------------16
- USE soft_uni;
- SELECT * FROM towns;
- SELECT * FROM departments;
- SELECT * FROM employees;
- ------------------17
- USE soft_uni;
- SELECT * FROM towns ORDER BY name;
- SELECT * FROM departments ORDER BY name;
- SELECT * FROM employees ORDER BY salary DESC;
- -------------------18
- USE softuni;
- SELECT name FROM towns ORDER BY name;
- SELECT name FROM departments ORDER BY name;
- SELECT first_name,last_name,job_title,salary FROM employees ORDER BY salary DESC;
- ---------------19
- UPDATE employees
- SET salary=salary*1.1;
- SELECT salary FROM employees;
- ---------------20
- USE hotel;
- UPDATE payments
- SET tax_rate=tax_rate*0.97;
- SELECT tax_rate FROM payments;
- ------------------21
- USE hotel;
- DELETE FROM occupancies;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement