drop database task1;
create database task1 character set utf8 /*collate utf8_unicode_ci*/;
use task1;


create table Users (
id int auto_increment primary key,
Name varchar(20),
login varchar(20),
email varchar(20),
department ENUM('Производство', 'Поддержка пользователей', 'Управление', 'Администрация'));
describe Users;


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;


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 Tasks;

/*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*/

insert into Tasks (Title, Priority, exec_user_id,creat_user_id) values ('задание', 62, 1,7);
insert into Tasks (Title, Priority, exec_user_id,creat_user_id) values ('еще задание ', 64, 2,7);
insert into Tasks (Title, Priority, exec_user_id) values ('задание2', 73, 3);
insert into Tasks (Title, Priority, exec_user_id) values ('задание3', 1, 1);
insert into Tasks (Title, Priority, exec_user_id,creat_user_id) values ('aas', 32, 4,7);
insert into Tasks (Title, Priority, exec_user_id) values ('xzc', 7, 4);


insert into Tasks (Title, Priority, exec_user_id, creat_date) values ('xzasdc', 7, 1,'2016-01-01');
insert into Tasks (Title, Priority, exec_user_id, creat_date) values ('asdgas', 7, 1,'2016-01-02');
insert into Tasks (Title, Priority, exec_user_id, creat_date) values ('asdfee', 7, 1,'2016-01-03');

insert into Tasks (Title, Priority, exec_user_id, creat_user_id) values ('daf', 1, 2,1);
insert into Tasks (Title, Priority, exec_user_id, creat_user_id) values ('aas', 32, 2,2);
insert into Tasks (Title, Priority, exec_user_id, creat_user_id) values ('xzc', 7, 2,2);
insert into Tasks (Title, Priority, exec_user_id, creat_user_id) values ('qw23e', 62, 1,7);
insert into Tasks (Title, Priority, exec_user_id, creat_user_id) values ('q12we', 3, 2,7);
insert into Tasks (Title, Priority, exec_user_id, creat_user_id) values ('qw12323e', 62, 1,7);
insert into Tasks (Title, Priority, exec_user_id, creat_user_id) values ('qw12323e', 62, 1,7);


/*1-3:*/

/*a*/select * from Tasks;

/*b*/select Name, department from Users;

/*c*/select login, email from Users;

/*d*/select Title from Tasks where Priority>50;

/*e*/select Name from Users INNER JOIN Tasks ON Tasks.exec_user_id = Users.id;

/*f*/(select distinct exec_user_id from Tasks where exec_user_id not in
(select distinct creat_user_id from Tasks)) union (select creat_user_id from Tasks);

/*k*/select * from Tasks where  creat_user_id not in
(select id from Users where Name = 'Петрова София') and exec_user_id in
(select id from Users where Name in ('Иванова Василина','Сидорова','Беркут Алексей'));

/*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'); /* in or = ?*/

/*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;

/*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%';