Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- drop database task1;
- create database task1;
- use task1
- drop table Users;
- create table Users (
- id int auto_increment primary key,
- Name varchar(15),
- login varchar(20),
- email varchar(20),
- department ENUM('Производство', 'Поддержка пользователей', 'Управление', 'Администрация'));
- describe Users;
- drop table Project;
- create table Project (
- id int auto_increment primary key,
- Name varchar(15),
- Description text not null,
- Date_open date not null,
- Date_close date);
- describe Project;
- drop table Tasks;
- create table Tasks (
- id int auto_increment primary key,
- Project_id int,
- Title varchar(15),
- Priority int,
- Description text,
- task_Condition ENUM('Новая','Переоткрыта','Выполняется','Закрыта'), /*Condition reserved*/
- Evaluation int,
- Elapsed_time int,
- exec_user_id int,
- creat_user_id int not null,
- creat_date date,
- );
- describe Project;
- /*1-2:*/
- insert into Project (Name, Date_open) values ('РТК', '2016-01-31');
- insert into Project (Name, Date_open, Date_close) values ('СС.Коннект', '2016-01-31', '2016-12-31');
- insert into Project (Name, Date_open, Date_close) values ('Демо-Сибирь', '2015-02-23', '2016-01-31');
- insert into Project (Name, Date_open, Date_close) values ('МВД-Онлайн', '2016-05-22','2016-01-31');
- insert into Project (Name, Date_open) values ('Поддержка', '2016-06-07')
- insert into Users (name, login, email, department) values ('Касаткин Артем', 'kasatkin', 'kasatka@gmail.com', 'Администрация');
- insert into Users (name, login, email, department) values ('Петрова София', 'petrova', 'petrova@gmail.com', 'Бухгалтерия');
- insert into Users (name, login, email, department) values ('Дроздов Федр', 'drosdov', 'drosdov@gmail.com', 'Поддержка пользователей');
- insert into Users (name, login, email, department) values ('Иванова', 'Василина', 'ivanova', 'ivanova@gmail.com', 'Бухгалтерия');
- insert into Users (name, login, email, department) values ('Беркут Алексей', 'aerkut', 'berkut@gmail.com', 'Управление');
- insert into Users (name, login, email, department) values ('Белова Вера', 'velova', 'belova@gmail.com', 'Управление');
- insert into Users (name, login, email, department) values ('Макенрой Алексей', 'makenroy', 'makenroy@gmail.com', 'Управление');
- /*insert Tasks*//*TODO*/
- /*1-3:*/
- select * from Tasks;
- select Name, department from Users;
- select login, email from Users;
- /*d*/select Title from Tasks where Priority>50;
- select Name from Users INNER JOIN Tasks ON Tasks.exec_user_id = Users.id; /* */
- /*select distinct exec_user_id,create_user_id from Tasks INNER JOIN Task ON exec_user_id!=create_user_id union select distinct exec_user_id,create_user_id from Tasks INNER JOIN Task ON exec_user_id=create_user_id; */
- (select distinct exec_user_id from Tasks where exec_user_id not in (select distinct created_by from tasks)) union (select created_by from tasks);
- /*1-4:*/
- select * from Tasks where exec_user_id = (select id from Users where name = 'Касаткин Артем') and creat_date in ('2016-01-01','2016-01-02','2016-01-03');
- /*1-5:*/
- select * from Tasks where exec_user_id = (select id from Users where name = 'Петрова София') and creat_user_id in (select id from Users where department in ('Администрация', 'Бухгалтерия', 'Развитие'));
- /*1-6:*/
- /*b:creat_user_id int not null */
- insert into Tasks (creat_user_id) values ('1'); /*TODO*/
- select * from Tasks where exec_user_id is null;
- update Tasks set exec_user_id = (select id from Users where name = 'Петрова София') where exec_user_id is null;
- /*1-7:*/
- create table Tasks2 like Tasks;
- insert Tasks2 select * from Tasks; /*id:umvr*/
- /*1-8:*/
- select * from Users where Name not like '%а' and Name not like '%a %';
- select * from Users where login like 'p%' and login like '%r%';
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement