CREATE TABLE regions (
region_id INT (11) AUTO_INCREMENT PRIMARY KEY,
region_name VARCHAR (25) DEFAULT NULL
);
CREATE TABLE countries (
country_id CHAR (2) PRIMARY KEY,
country_name VARCHAR (40) DEFAULT NULL,
region_id INT (11) NOT NULL,
FOREIGN KEY (region_id) REFERENCES regions (region_id) ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE locations (
location_id INT (11) AUTO_INCREMENT PRIMARY KEY,
street_address VARCHAR (40) DEFAULT NULL,
postal_code VARCHAR (12) DEFAULT NULL,
city VARCHAR (30) NOT NULL,
state_province VARCHAR (25) DEFAULT NULL,
country_id CHAR (2) NOT NULL,
FOREIGN KEY (country_id) REFERENCES countries (country_id) ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE jobs (
job_id INT (11) AUTO_INCREMENT PRIMARY KEY,
job_title VARCHAR (35) NOT NULL,
min_salary DECIMAL (8, 2) DEFAULT NULL,
max_salary DECIMAL (8, 2) DEFAULT NULL
);
CREATE TABLE departments (
department_id INT (11) AUTO_INCREMENT PRIMARY KEY,
department_name VARCHAR (30) NOT NULL,
location_id INT (11) DEFAULT NULL,
FOREIGN KEY (location_id) REFERENCES locations (location_id) ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE employees (
employee_id INT (11) AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR (20) DEFAULT NULL,
last_name VARCHAR (25) NOT NULL,
email VARCHAR (100) NOT NULL,
phone_number VARCHAR (20) DEFAULT NULL,
hire_date DATE NOT NULL,
job_id INT (11) NOT NULL,
salary DECIMAL (8, 2) NOT NULL,
manager_id INT (11) DEFAULT NULL,
department_id INT (11) DEFAULT NULL,
FOREIGN KEY (job_id) REFERENCES jobs (job_id) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (department_id) REFERENCES departments (department_id) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (manager_id) REFERENCES employees (employee_id)
);
CREATE TABLE dependents (
dependent_id INT (11) AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR (50) NOT NULL,
last_name VARCHAR (50) NOT NULL,
relationship VARCHAR (25) NOT NULL,
employee_id INT (11) NOT NULL,
FOREIGN KEY (employee_id) REFERENCES employees (employee_id) ON DELETE CASCADE ON UPDATE CASCADE
);
/*Data for the table regions */
INSERT INTO regions(region_id,region_name) VALUES (1,'Europe');
INSERT INTO regions(region_id,region_name) VALUES (2,'Americas');
INSERT INTO regions(region_id,region_name) VALUES (3,'Asia');
INSERT INTO regions(region_id,region_name) VALUES (4,'Middle East and Africa');
/*Data for the table countries */
INSERT INTO countries(country_id,country_name,region_id) VALUES ('AR','Argentina',2);
INSERT INTO countries(country_id,country_name,region_id) VALUES ('AU','Australia',3);
INSERT INTO countries(country_id,country_name,region_id) VALUES ('BE','Belgium',1);
INSERT INTO countries(country_id,country_name,region_id) VALUES ('BR','Brazil',2);
INSERT INTO countries(country_id,country_name,region_id) VALUES ('CA','Canada',2);
INSERT INTO countries(country_id,country_name,region_id) VALUES ('CH','Switzerland',1);
INSERT INTO countries(country_id,country_name,region_id) VALUES ('CN','China',3);
INSERT INTO countries(country_id,country_name,region_id) VALUES ('DE','Germany',1);
INSERT INTO countries(country_id,country_name,region_id) VALUES ('DK','Denmark',1);
INSERT INTO countries(country_id,country_name,region_id) VALUES ('EG','Egypt',4);
INSERT INTO countries(country_id,country_name,region_id) VALUES ('FR','France',1);
INSERT INTO countries(country_id,country_name,region_id) VALUES ('HK','HongKong',3);
INSERT INTO countries(country_id,country_name,region_id) VALUES ('IL','Israel',4);
INSERT INTO countries(country_id,country_name,region_id) VALUES ('IN','India',3);
INSERT INTO countries(country_id,country_name,region_id) VALUES ('IT','Italy',1);
INSERT INTO countries(country_id,country_name,region_id) VALUES ('JP','Japan',3);
INSERT INTO countries(country_id,country_name,region_id) VALUES ('KW','Kuwait',4);
INSERT INTO countries(country_id,country_name,region_id) VALUES ('MX','Mexico',2);
INSERT INTO countries(country_id,country_name,region_id) VALUES ('NG','Nigeria',4);
INSERT INTO countries(country_id,country_name,region_id) VALUES ('NL','Netherlands',1);
INSERT INTO countries(country_id,country_name,region_id) VALUES ('SG','Singapore',3);
INSERT INTO countries(country_id,country_name,region_id) VALUES ('UK','United Kingdom',1);
INSERT INTO countries(country_id,country_name,region_id) VALUES ('US','United States of America',2);
INSERT INTO countries(country_id,country_name,region_id) VALUES ('ZM','Zambia',4);
INSERT INTO countries(country_id,country_name,region_id) VALUES ('ZW','Zimbabwe',4);
/*Data for the table locations */
INSERT INTO locations(location_id,street_address,postal_code,city,state_province,country_id) VALUES (1400,'2014 Jabberwocky Rd','26192','Southlake','Texas','US');
INSERT INTO locations(location_id,street_address,postal_code,city,state_province,country_id) VALUES (1500,'2011 Interiors Blvd','99236','South San Francisco','California','US');
INSERT INTO locations(location_id,street_address,postal_code,city,state_province,country_id) VALUES (1700,'2004 Charade Rd','98199','Seattle','Washington','US');
INSERT INTO locations(location_id,street_address,postal_code,city,state_province,country_id) VALUES (1800,'147 Spadina Ave','M5V 2L7','Toronto','Ontario','CA');
INSERT INTO locations(location_id,street_address,postal_code,city,state_province,country_id) VALUES (2400,'8204 Arthur St',NULL,'London',NULL,'UK');
INSERT INTO locations(location_id,street_address,postal_code,city,state_province,country_id) VALUES (2500,'Magdalen Centre, The Oxford Science Park','OX9 9ZB','Oxford','Oxford','UK');
INSERT INTO locations(location_id,street_address,postal_code,city,state_province,country_id) VALUES (2700,'Schwanthalerstr. 7031','80925','Munich','Bavaria','DE');
/*Data for the table jobs */
INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES (1,'Public Accountant',4200.00,9000.00);
INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES (2,'Accounting Manager',8200.00,16000.00);
INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES (3,'Administration Assistant',3000.00,6000.00);
INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES (4,'President',20000.00,40000.00);
INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES (5,'Administration Vice President',15000.00,30000.00);
INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES (6,'Accountant',4200.00,9000.00);
INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES (7,'Finance Manager',8200.00,16000.00);
INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES (8,'Human Resources Representative',4000.00,9000.00);
INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES (9,'Programmer',4000.00,10000.00);
INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES (10,'Marketing Manager',9000.00,15000.00);
INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES (11,'Marketing Representative',4000.00,9000.00);
INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES (12,'Public Relations Representative',4500.00,10500.00);
INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES (13,'Purchasing Clerk',2500.00,5500.00);
INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES (14,'Purchasing Manager',8000.00,15000.00);
INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES (15,'Sales Manager',10000.00,20000.00);
INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES (16,'Sales Representative',6000.00,12000.00);
INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES (17,'Shipping Clerk',2500.00,5500.00);
INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES (18,'Stock Clerk',2000.00,5000.00);
INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES (19,'Stock Manager',5500.00,8500.00);
/*Data for the table departments */
INSERT INTO departments(department_id,department_name,location_id) VALUES (1,'Administration',1700);
INSERT INTO departments(department_id,department_name,location_id) VALUES (2,'Marketing',1800);
INSERT INTO departments(department_id,department_name,location_id) VALUES (3,'Purchasing',1700);
INSERT INTO departments(department_id,department_name,location_id) VALUES (4,'Human Resources',2400);
INSERT INTO departments(department_id,department_name,location_id) VALUES (5,'Shipping',1500);
INSERT INTO departments(department_id,department_name,location_id) VALUES (6,'IT',1400);
INSERT INTO departments(department_id,department_name,location_id) VALUES (7,'Public Relations',2700);
INSERT INTO departments(department_id,department_name,location_id) VALUES (8,'Sales',2500);
INSERT INTO departments(department_id,department_name,location_id) VALUES (9,'Executive',1700);
INSERT INTO departments(department_id,department_name,location_id) VALUES (10,'Finance',1700);
INSERT INTO departments(department_id,department_name,location_id) VALUES (11,'Accounting',1700);
/*Data for the table employees */
INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (100,'Steven','King','[email protected]','515.123.4567','1987-06-17',4,24000.00,NULL,9);
INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (101,'Neena','Kochhar','[email protected]','515.123.4568','1989-09-21',5,17000.00,100,9);
INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (102,'Lex','De Haan','lex.de [email protected]','515.123.4569','1993-01-13',5,17000.00,100,9);
INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (103,'Alexander','Hunold','[email protected]','590.423.4567','1990-01-03',9,9000.00,102,6);
INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (104,'Bruce','Ernst','[email protected]','590.423.4568','1991-05-21',9,6000.00,103,6);
INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (105,'David','Austin','[email protected]','590.423.4569','1997-06-25',9,4800.00,103,6);
INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (106,'Valli','Pataballa','[email protected]','590.423.4560','1998-02-05',9,4800.00,103,6);
INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (107,'Diana','Lorentz','[email protected]','590.423.5567','1999-02-07',9,4200.00,103,6);
INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (108,'Nancy','Greenberg','[email protected]','515.124.4569','1994-08-17',7,12000.00,101,10);
INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (109,'Daniel','Faviet','[email protected]','515.124.4169','1994-08-16',6,9000.00,108,10);
INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (110,'John','Chen','[email protected]','515.124.4269','1997-09-28',6,8200.00,108,10);
INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (111,'Ismael','Sciarra','[email protected]','515.124.4369','1997-09-30',6,7700.00,108,10);
INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (112,'Jose Manuel','Urman','jose [email protected]','515.124.4469','1998-03-07',6,7800.00,108,10);
INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (113,'Luis','Popp','[email protected]','515.124.4567','1999-12-07',6,6900.00,108,10);
INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (114,'Den','Raphaely','[email protected]','515.127.4561','1994-12-07',14,11000.00,100,3);
INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (115,'Alexander','Khoo','[email protected]','515.127.4562','1995-05-18',13,3100.00,114,3);
INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (116,'Shelli','Baida','[email protected]','515.127.4563','1997-12-24',13,2900.00,114,3);
INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (117,'Sigal','Tobias','[email protected]','515.127.4564','1997-07-24',13,2800.00,114,3);
INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (118,'Guy','Himuro','[email protected]','515.127.4565','1998-11-15',13,2600.00,114,3);
INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (119,'Karen','Colmenares','[email protected]','515.127.4566','1999-08-10',13,2500.00,114,3);
INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (120,'Matthew','Weiss','[email protected]','650.123.1234','1996-07-18',19,8000.00,100,5);
INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (121,'Adam','Fripp','[email protected]','650.123.2234','1997-04-10',19,8200.00,100,5);
INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (122,'Payam','Kaufling','[email protected]','650.123.3234','1995-05-01',19,7900.00,100,5);
INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (123,'Shanta','Vollman','[email protected]','650.123.4234','1997-10-10',19,6500.00,100,5);
INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (126,'Irene','Mikkilineni','[email protected]','650.124.1224','1998-09-28',18,2700.00,120,5);
INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (145,'John','Russell','[email protected]',NULL,'1996-10-01',15,14000.00,100,8);
INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (146,'Karen','Partners','[email protected]',NULL,'1997-01-05',15,13500.00,100,8);
INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (176,'Jonathon','Taylor','[email protected]',NULL,'1998-03-24',16,8600.00,100,8);
INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (177,'Jack','Livingston','[email protected]',NULL,'1998-04-23',16,8400.00,100,8);
INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (178,'Kimberely','Grant','[email protected]',NULL,'1999-05-24',16,7000.00,100,8);
INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (179,'Charles','Johnson','[email protected]',NULL,'2000-01-04',16,6200.00,100,8);
INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (192,'Sarah','Bell','[email protected]','650.501.1876','1996-02-04',17,4000.00,123,5);
INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (193,'Britney','Everett','[email protected]','650.501.2876','1997-03-03',17,3900.00,123,5);
INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (200,'Jennifer','Whalen','[email protected]','515.123.4444','1987-09-17',3,4400.00,101,1);
INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (201,'Michael','Hartstein','[email protected]','515.123.5555','1996-02-17',10,13000.00,100,2);
INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (202,'Pat','Fay','[email protected]','603.123.6666','1997-08-17',11,6000.00,201,2);
INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (203,'Susan','Mavris','[email protected]','515.123.7777','1994-06-07',8,6500.00,101,4);
INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (204,'Hermann','Baer','[email protected]','515.123.8888','1994-06-07',12,10000.00,101,7);
INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (205,'Shelley','Higgins','[email protected]','515.123.8080','1994-06-07',2,12000.00,101,11);
INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (206,'William','Gietz','[email protected]','515.123.8181','1994-06-07',1,8300.00,205,11);
/*Data for the table dependents */
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (1,'Penelope','Gietz','Child',206);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (2,'Nick','Higgins','Child',205);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (3,'Ed','Whalen','Child',200);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (4,'Jennifer','King','Child',100);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (5,'Johnny','Kochhar','Child',101);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (6,'Bette','De Haan','Child',102);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (7,'Grace','Faviet','Child',109);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (8,'Matthew','Chen','Child',110);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (9,'Joe','Sciarra','Child',111);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (10,'Christian','Urman','Child',112);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (11,'Zero','Popp','Child',113);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (12,'Karl','Greenberg','Child',108);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (13,'Uma','Mavris','Child',203);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (14,'Vivien','Hunold','Child',103);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (15,'Cuba','Ernst','Child',104);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (16,'Fred','Austin','Child',105);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (17,'Helen','Pataballa','Child',106);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (18,'Dan','Lorentz','Child',107);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (19,'Bob','Hartstein','Child',201);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (20,'Lucille','Fay','Child',202);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (21,'Kirsten','Baer','Child',204);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (22,'Elvis','Khoo','Child',115);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (23,'Sandra','Baida','Child',116);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (24,'Cameron','Tobias','Child',117);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (25,'Kevin','Himuro','Child',118);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (26,'Rip','Colmenares','Child',119);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (27,'Julia','Raphaely','Child',114);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (28,'Woody','Russell','Child',145);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (29,'Alec','Partners','Child',146);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (30,'Sandra','Taylor','Child',176);
-- MySql
-- 1) Таблица Employees. Получить список всех сотрудников из 5го и из 8го отдела (department_id), которых наняли в 1998 году
SELECT *
FROM employees
WHERE (hire_date LIKE '%1998%') AND (department_id BETWEEN 5 AND 8); -- в столбце hire_date выбрали значения, содержащие 1998.
-- И одновременно в department_id выбрали значения между 5 и 8
-- 2)Таблица Employees. Получить список всех сотрудников у которых в имени содержатся минимум 2 буквы 'n'
SELECT * FROM employees
where (length(first_name) - length(REPLACE(first_name, 'n', ''))) >= 2; -- удаляем(заменяем на пустоту) в first_name буквы n, считаем количество получившихся букв.
-- Их меньше ровно на количество удалённых n. Потом из изначального кол-ва букв вычитаем
-- получившееся кол-во букв и получаем ответ
-- 3) Таблица Employees. Получить список всех сотрудников у которых зарплата находится в промежутке от 8000 до 9000 (включительно) и/или кратна 1000
select * from employees
where (salary between 8000 and 9000) or (salary % 1000 = 0);
select * from employees
where (salary between 8000 and 9000) and (salary % 1000 = 0);
-- 4) Таблица Employees. Получить список всех сотрудников у которых длина имени больше 10 букв и/или у которых в имени есть буква 'b' (без учета регистра)
SELECT * FROM employees
where (first_name like '%b%') and (length(first_name) > 10); -- нет таких
SELECT * FROM employees
where (first_name like '%b%') or (length(first_name) > 10);
-- 5) Таблица Employees. Получить первое 3х значное число телефонного номера сотрудника если его номер в формате ХХХ.ХХХ.ХХХХ
SELECT employee_id, SUBSTR(phone_number, 1 , 3) as three FROM employees; -- выведет id сотрудника и первые 3 цифры его номера
-- 6) Таблица Departments. Получить первое слово из имени департамента для тех у кого в названии больше одного слова
-- 1 способ
SELECT SUBSTR(department_name, 1, LOCATE(' ', department_name)) as column1 FROM departments
where LENGTH('department_name') - LENGTH(REPLACE('department_name', ' ', '')) + 1 > 0; -- считаем пробелы. Удаляем пробел, из-за чего длина слова сокращается.
-- потом из изначальной длины слова вычитаем обрезанное, и получаем кол-во пробелов.
-- соответственно если 0, то было только одно слово. Если больше, то слов несколько.
-- 2 способ
SELECT SUBSTR(department_name, 1, LOCATE(' ', department_name)) as column1 FROM departments -- SUBSTR вырезает и возвращает заданное количество символов из строки.
-- Первым параметром функция принимает столбец или строку,
-- вторым параметром - с какой позиции начинать вырезания (нумерация символов начинается с 1),
-- третьем параметром - сколько символов брать(их кол-во).
where LOCATE(' ', department_name) > 0; -- LOCATE ищет поиск подстроки в строке и возвращает позицию ее первого символа. Значит, если слова 2, то точно есть пробел
-- и позиция пробела больше 0. Т.е он возвращает индекс(позицию в строке) пробела.
-- 7) Таблица Employees. Получить список всех сотрудников которые пришли на работу в первый день месяца (любого)
SELECT * FROM employees
where hire_date like '%-01-%'; -- обозначили число 1
-- 8) Таблица Countries. Для каждой страны показать регион в котором он находится: 1-Europe, 2-America, 3-Asia, 4-Africa (без Join)
select *, case
when region_id = 1 then 'Europe'
when region_id = 2 then 'America'
when region_id = 3 then 'Asia'
else 'Africa' end region_name from countries; -- использовали конструкцию case. Выведем всю таблицу + столбец region_name с названиями.
-- 9) Таблица Employees. Получить уровень зарплаты каждого сотрудника: Меньше 5000 считается Low level, Больше или равно 5000 и меньше 10000 считается Normal level, Больше иои равно 10000 считается High level
select *, case
when salary < 5000 then 'Low level'
when salary >= 5000 and salary < 10000 then 'Normal level'
when salary >= 10000 then 'High level'
end region_name from employees; -- взяли столбец region_name из табл regions, и вместо тех значений вывели нужные про уровень зарплат. Выведет табл employees + стобец с уровенем зп
-- 10) Таблица Employees. Получить репорт по department_id с минимальной и максимальной зарплатой, с ранней и поздней датой прихода на работу и с количествов сотрудников. Сорировать по количеству сотрудников (по убыванию)
SELECT MIN(salary) as min_salary, -- 3) в каждомм департаменте определили мин зп
MAX(salary) as max_salary,
min(hire_date) as min_hire_date,
max(hire_date) as max_hire_date,
count(*) as count_of_empl, -- 7) в каждом департаменте определили кол-во сотрудников. Если написать count(1) вместо *, то работать будет быстрее. Но * понятнее.
department_id as department_id
FROM employees -- 1) из табл employees
group by department_id -- 2) сгрупировали по значениям в столбце department_id
order by count_of_empl desc; -- 9) отсортировали в порядке убывания
-- 11) Таблица Employees. Сколько сотрудников которые работают в одном и тоже отделе и получают одинаковую зарплату?
-- Первый вариант.
SELECT department_id, salary, count(salary) as count_salary -- 3) выведем департамент,уникальные зп, кол-во людей получающих эту зп
FROM employees -- 1) из табл employees
group by department_id, salary -- 2) сгруппировали по департаменту и по зарплатам в каждом департаменте. Т.е для каждого департамента выделит все уникальные зп
having count(salary) > 1; -- 4) уже после группировки отфильтруем. Выберем только те, где кол-во людей, получающих уникальную зп, больше 1.
-- В нашем задании нам повезло. Т.к зп в каждом департаменте, которую получают больше 1 человека, всего лишь одна. В 6 департаменте это 4800,
-- её получают 2 человека. И аналогично для департамента 9 это 17000, её получают тоже 2 человека. Но что, если в каждом департаменте больше одной зп,
-- которую получают несколько человек. Например, в 6 департ. это 4800, 5600 и 9100. И зарабатывают их 2, 4,6 человек соответственно. Как тогда посчитать
-- сумму всех людей в 6 департаменте, которые зарабатывают одну и ту же зп? Для этого есть второй вариант:
select department_id, sum(count_salary) -- 3) и в итоге выводим департамент и сумму людей, которые зарабатывают одинаковые зп в конкретном департаменте.
-- Т.е нам не важно какую именно зп зарабатывают каждый из этих людей. Нам важна просто сумма всех людей, зарабатывающих
-- идентичную зп.
from (SELECT department_id, salary, count(salary) as count_salary FROM employees as e
group by department_id, salary
having count(salary) > 1) as m -- 1) из получившейся таблицы, где расписаны департаменты, зарплаты и кол-во человек, зарабатывающих каждую уникальную зп
group by department_id; -- 2) мы группируем по департаментам
-- 12) Таблица Employees. Сколько сотрудников имена которых начинается с одной и той же буквы? Сортировать по количеству. Показывать только те где количество
-- больше 1
SELECT substr(first_name, 1,1) as capital_letter, count(substr(first_name, 1,1)) as count_lettter -- выделяем первую букву имени и считаем кол-во сотрудников с
-- такой буквой
FROM employees
group by capital_letter -- группируем по заглавной букве
having count_lettter > 1 -- фильтруем, чтобы кол-во сотрудников с определённой буквой было больше 1
order by count_lettter; -- сортируем в порядке возрастания
-- 13) Таблица Employees. Получить список department_id и округленную среднюю зарплату работников в каждом департаменте.
select department_id, round(avg(salary)) as avg
FROM employees
group by department_id;
-- 14) Таблица Countries. Получить список region_id сумма длин всех country_name в котором больше 60ти
SELECT region_id, sum(length(country_name)) as len
FROM countries
group by region_id -- сгруппировали по региону
having len > 60;
-- 15) Таблица Employees, Departaments, Locations, Countries, Regions. Получить список регионов и количество сотрудников в каждом регионе
SELECT regions.region_id, count(employee_id) -- 8) вывели названия регионов и кол-во сотрудников
FROM regions -- 1) из табл regions
INNER JOIN countries -- 2) объединяем табл regions и countries
ON regions.region_id = countries.region_id -- 3) по столбцу region_id
INNER JOIN locations -- 4) получившуюся табл объединяем с табл locations
ON countries.country_id = locations.country_id -- 5) по столбцу country_id
INNER JOIN departments -- 6) и т.д.
ON locations.location_id = departments.location_id
INNER JOIN employees
ON departments.department_id = employees.department_id
group by regions.region_id; -- 7) группируем по названию региона(числу)
-- 16) Таблица Employees. Показать всех менеджеров которые имеют в подчинении больше 6ти сотрудников
SELECT tab1.employee_id, tab1.first_name, count(tab2.employee_id) as count_peop -- 5) выводим id менеджера, его имя и кол-во его подчинённых
FROM employees as tab1 join employees as tab2 -- 1) объединяем табл employees и employees. Да, нам надо сопоставить данные из одной и той же табл.
-- написали просто join, значит по умолчанию внутреннее объединение. Если данных из первого столбца нет во втором, то такие значения просто пропускаются
on tab1.employee_id = tab2.manager_id -- 2) сопоставляем значения по столбцу employee_id и manager_id. Они должны быть равны. В первом случае это id менеджера.
-- Во втором подчинённых
group by tab1.employee_id -- 3) группируем по id менеджеров
having count_peop > 6; -- 4) фильтруем, чтобы кол-во сотрудников у менеджера было больше 6
-- 17) Таблица Employees. Показать всех сотрудников у которых нет ни кого в подчинении
SELECT master1.employee_id as product, slaves.employee_id as worker -- 3) выводим только менеджеров, у которых нет подчинённых. По сути, это просто работники
FROM employees as master1 left join employees as slaves -- 1) объединяем табл employees и employees. Да, нам надо сопоставить данные из одной и той же табл.
-- тут используем left join, это значит, что если данных из первого столбца нет во втором, то на месте пропусков будет стоять NULL.
-- Так мы узнаем, у кого нет подчинённых
on master1.employee_id = slaves.manager_id -- 2) сопоставляем менеджеров и их работников.
where slaves.employee_id is NULL;
-- 18) Таблица Employees, Departaments. Показать все департаменты в которых работают больше пяти сотрудников
SELECT departments.department_id, count(employees.employee_id) as count_peop -- выводим номер департамента и кол-во сотрудников в нём
FROM departments left outer join employees -- левое внешнее объединение, значит если данных из первого столбца нет во втором, то на месте пропусков будет
-- стоять NULL
on departments.department_id = employees.department_id -- сопоставляем по департаменту
group by departments.department_id -- группируем по номеру департамента
having count_peop > 5;
-- 19) Таблица Employees. Получить список сотрудников с зарплатой большей средней зарплаты всех сотрудников.
select employee_id, first_name, salary, avg_sal -- 2)вывели номер сотрудника, его имя, зп и среднюю зп всех работников
from employees cross join (SELECT AVG(salary) as avg_sal from employees) as e -- 1)соединили таблицу avg_sal(состоящую из одного значения) с каждым значением
-- из первой таблицы
where salary > avg_sal;
-- 20) Таблица Employees, Departaments. Показать сотрудников которые работают в департаменте IT
SELECT employ.employee_id, employ.first_name, depart.department_id, depart.department_name
FROM employees as employ left outer join departments as depart
on employ.department_id = depart.department_id
group by employ.department_id, employ.employee_id
having depart.department_name = 'IT'; Write, Run & Share MySQL queries online using OneCompiler's MySQL online editor and compiler for free. It's one of the robust, feature-rich online editor and compiler for MySQL. Getting started with the OneCompiler's MySQL editor is really simple and pretty fast. The editor shows sample boilerplate code when you choose language as 'MySQL' and start writing queries to learn and test online without worrying about tedious process of installation.
MySQL is a open-source, free and very popular relational database management system which is developed, distributed and supported by Oracle corporation.
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
....);
CREATE TABLE EMPLOYEE (
empId INTEGER PRIMARY KEY,
name TEXT NOT NULL,
dept TEXT NOT NULL
);
ALTER TABLE Table_name ADD column_name datatype;
INSERT INTO EMPLOYEE VALUES (0001, 'Dave', 'Sales');
TRUNCATE table table_name;
DROP TABLE table_name;
RENAME TABLE table_name1 to new_table_name1;
--Line1;
/* Line1,
Line2 */
INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);
Note: Column names are optional.
INSERT INTO EMPLOYEE VALUES (0001, 'Ava', 'Sales');
SELECT column1, column2, ...
FROM table_name
[where condition];
SELECT * FROM EMPLOYEE where dept ='sales';
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
UPDATE EMPLOYEE SET dept = 'Sales' WHERE empId='0001';
DELETE FROM table_name where condition;
DELETE from EMPLOYEE where empId='0001';
CREATE INDEX index_name on table_name(column_name);
CREATE UNIQUE INDEX index_name on table_name(column_name);
DROP INDEX index_name ON table_name;
Creating a View:
CREATE VIEW View_name AS
Query;
SELECT * FROM View_name;
ALTER View View_name AS
Query;
DROP VIEW View_name;
CREATE TRIGGER trigger_name trigger_time trigger_event
ON tbl_name FOR EACH ROW [trigger_order] trigger_body
/* where
trigger_time: { BEFORE | AFTER }
trigger_event: { INSERT | UPDATE | DELETE }
trigger_order: { FOLLOWS | PRECEDES } */
DROP TRIGGER [IF EXISTS] trigger_name;
CREATE PROCEDURE sp_name(p1 datatype)
BEGIN
/*Stored procedure code*/
END;
CALL sp_name;
DROP PROCEDURE sp_name;
SELECT * FROM TABLE1 INNER JOIN TABLE2 where condition;
SELECT * FROM TABLE1 LEFT JOIN TABLE2 ON condition;
SELECT * FROM TABLE1 RIGHT JOIN TABLE2 ON condition;
SELECT select_list from TABLE1 CROSS JOIN TABLE2;