Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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*/exec_user_id
- 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'); /
- select * from Tasks where exec_user_id is null;
- update Tasks set exec_user_id = 2 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%';
- /*2 deadline = 25.03
- 3 deadline = 08.04 */
- /*TASK_2*/
- /*2-1:*/
- select exec_user_id from Tasks
- group by exec_user_id
- order by avg(Priority)
- limit 3;
- select avg(Priority) from Tasks;
- select avg(Priority) from Tasks
- group by id;
- select avg(Priority) from Tasks
- group by null;
- /*2-2:*/
- select concat(count(*),' - ', month(creat_date), ' - ', exec_user_id) from Tasks
- where year(creat_date) = 2016
- group by exec_user_id, month(creat_date);
- /*2-3:*/
- select exec_user_id, sum(Elapsed_time-Evaluation), sum(Evaluation- Elapsed_time)from Tasks
- group by id;
- select
- A.exec_user_id,
- A.summ, B.summ
- from (select exec_user_id, sum(Evaluation- Elapsed_time) as summ
- from Tasks where Evaluation>Elapsed_time
- group by exec_user_id) as A
- join (select exec_user_id, sum( Elapsed_time-Evaluation) as summ
- from Tasks where Evaluation<Elapsed_time
- group by exec_user_id) as B
- on A.exec_user_id = B.exec_user_id;
- /*2-4:*/
- select distinct
- if( creat_user_id<exec_user_id, concat(creat_user_id, ' - ', exec_user_id), concat(exec_user_id, ' - ', creat_user_id) )
- from Tasks;
- /*2-5:*/
- select login from Users
- order by length(login)
- desc limit 1;
- /*2-6:*/
- SET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH';
- drop table test
- Create table test
- (c CHAR(10),
- v VARCHAR(10));
- Insert into test
- values('asd','asdasd');
- select length(c) as 'CHAR', length(v) as 'VARCHAR' from test;
- /*2-7:*/
- select Users.Name, max(Tasks.Priority)
- from Users, Tasks
- where Users.id = Tasks.exec_user_id
- group by Users.id;
- /*2-8:*/
- select creat_user_id, sum(Elapsed_time) from Tasks
- where Priority > (select avg(Priority) from Tasks)
- group by creat_user_id;
- /*2-9:*/
- drop view taskz;
- create view taskz as
- select exec_user_id,
- count(*) as total_tasks
- from Tasks
- group by exec_user_id;
- select * from taskz;
- drop view taskz2;
- create view taskz2 as
- select exec_user_id,
- sum(Evaluation > Elapsed_time) as in_time
- from Tasks
- where Evaluation is not null and Elapsed_time is not null
- group by exec_user_id;
- select * from taskz2;
- drop view taskz3;
- create view taskz3 as
- select exec_user_id,
- sum(Evaluation < Elapsed_time) as not_in_time
- from Tasks
- where Evaluation is not null and Elapsed_time is not null
- group by exec_user_id;
- select * from taskz3;
- drop view taskz4;
- create view taskz4 as
- select exec_user_id,
- sum((task_Condition = 'Переоткрыта') or (task_Condition ='Новая')) as otkrita,
- sum(task_Condition = 'Закрыта') as zakr,
- sum(task_Condition = 'Выполняется') as vipoln
- from Tasks
- group by exec_user_id;
- select * from taskz4;
- drop view taskz4;
- create view taskz4 as
- select exec_user_id,
- sum(Evaluation) as summ_potr_vremya,
- sum(Evaluation - Elapsed_time) as pererab
- from Tasks
- where Evaluation is not null and Elapsed_time is not null
- group by exec_user_id;
- select * from taskz4;
- drop view taskz4;
- create view taskz4 as
- select exec_user_id,
- sum(length(Description)) as descr_lengt,
- avg(Evaluation - Elapsed_time) as avg_pererab,
- max(Elapsed_time) as max_Elapsed_time
- from Tasks
- where Evaluation is not null and Elapsed_time is not null and Description is not null
- group by exec_user_id;
- select * from taskz4;
- /*2-10:*/
- select Name, Title from Users, Tasks where Users.id = exec_user_id;
- select Name,id from Users where Users.id not in (select exec_user_id from Tasks);
- select Name, ifnull((select count(*) from Tasks where creat_user_id = Users.id group by creat_user_id ),0) as num_of_tasks from Users;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement