SQL JOB INTERVIEW. EPISODE 4.

Here comes a test from MGTS.

Incredibly simple and linear, enjoy!

SQL:
1. У вас есть таблица с колонками «Обращение», «Дата» и «Приоритет», в которой лежит приоритет обращения на заданную дату. С помощью SQL выберите из таблицы приоритет заданного обращения на текущую дату.
2. В базе данных Oracle есть две таблицы с одинаковым набором колонок. Выведите данные, которые есть в одной таблице, но отсутствуют в другой.
3. Как ограничить кол-во выводимых строк в разных БД (MySQL, MSSQL, pl/sql, др.)?
4. Для таблицы, которая начинается так:
id	name	Type
1	1	1
2	2	1
3	2	3
4	3	1
5	3	2
6	1	2
7	3	null
8	4	null
Написать запрос, позволяющий выбрать все значения, удовлетворяющие условиям:
1)   name не равен 1 и 2
2)   name не равен 3 и type не пустой
4) Что не так с запросом и как его переделать чтобы он работал корректно?
SELECT thing, AVG(value) FROM things
WHERE AVG(value) > 10
GROUP BY thing;

  

The answers:

За основу взята база данных postgresql v10.0

Задание 1:

Создаётся таблица:

create table request_tbl

(

  request varchar(4000)

  ,request_date date

  ,priority integer

);

 INSERT INTO request_tbl VALUES

    ('12301c275ff60e1fc11e68cea', '2017-06-11', 1);

 INSERT INTO request_tbl VALUES

    ('12301c275ff60e1fc11e68cea', '2017-07-15', 2);

  INSERT INTO request_tbl VALUES

    ('12301c275ff60e1fc11e68cea', '2017-08-19', 3);

Допустим сегодняшняя дата 2017-08-19, тогда скрипт:

select priority from request_tbl

where 1=1

and request='12301c275ff60e1fc11e68cea'

and request_date='2017-08-19'

Если требуется автоматизация в части системной даты на сегодня, то можно использовать функцию, выводящую системную дату на сегодня. В PG это будет функция current_date:

 select priority from request_tbl

where 1=1

and request='12301c275ff60e1fc11e68cea'

and request_date= current_date

Задание 2:

Создаются две таблицы:

create table request_tbl_1

(

  request varchar(4000)

  ,request_date date

  ,priority integer

);

INSERT INTO request_tbl_1 VALUES ('12301c275ff60e1fc11e68cea', '2017-06-11', 1);

INSERT INTO request_tbl_1 VALUES ('12301c275ff60e1fc11e68cea', '2017-07-15', 2);

INSERT INTO request_tbl_1 VALUES ('12301c275ff60e1fc11e68cea', '2017-08-19', 3);

INSERT INTO request_tbl_1 VALUES ('45601c275ff60e1fc11e68cea', '2018-06-11', 4);

INSERT INTO request_tbl_1 VALUES ('45601c275ff60e1fc11e68cea', '2018-07-15', 5);

INSERT INTO request_tbl_1 VALUES ('45601c275ff60e1fc11e68cea', '2018-08-19', 6);

create table request_tbl_2

(

  request varchar(4000)

  ,request_date date

  ,priority integer

);

INSERT INTO request_tbl_2 VALUES ('45601c275ff60e1fc11e68cea', '2018-06-11', 4);

INSERT INTO request_tbl_2 VALUES ('45601c275ff60e1fc11e68cea', '2018-07-15', 5);

INSERT INTO request_tbl_2 VALUES ('45601c275ff60e1fc11e68cea', '2018-08-19', 6);

Задачу можно решить разными путями, к примеру, через связку IN clause и subquery:

select * from request_tbl_1 as t1

where 1=1

and t1.request not in (select request from request_tbl_2)

Либо через оператор EXCEPT (аналог в Oracle MINUS):

select

t1.*

from request_tbl_1 as t1

EXCEPT ALL

select

t2.*

from request_tbl_2 as t2

Задание 3:

В зависимости от поставленной задачи есть разные пути. Универсальный метод (по разным системам БД) это использовать функцию ROW_NUMBER() OVER(). К примеру, данный скрипт:

select

t1.*

,ROW_NUMBER() over(order by request desc)

from request_tbl_1 as t1

Выведет столбец с нумерацией строк (по заданному критерию партиции/сортировки), чтобы затем в WHERE clause можно было выставить нужный ограничитель.

Задание 4:

Создается таблица:

create table example

(

  id integer

  ,name integer

  ,type integer

);

INSERT INTO example VALUES (1, 1, 1);

INSERT INTO example VALUES (2, 2, 1);

INSERT INTO example VALUES (3, 2, 3);

INSERT INTO example VALUES (4, 3, 1);

INSERT INTO example VALUES (5, 3, 2);

INSERT INTO example VALUES (6, 1, 2);

INSERT INTO example VALUES (7, 3, null);

INSERT INTO example VALUES (8, 4, null);

4.1 name не равен 1 и 2:

select * from example

where 1=1

and name not in (1,2)

4.2 name не равен 3 и type не пустой:

select * from example

where 1=1

and name not in (3)

and type is not null

Задание 5:

Ошибка:

aggregate functions are not allowed in WHERE

То есть, WHERE clause не принимает агрегатные функции. Это можно исправить с помощью HAVING clause.

Мой пример:

SELECT name, AVG(type) FROM example

WHERE 1=1

GROUP BY name

having AVG(type) > 1

Пример из описания (скорректированный):

SELECT thing, AVG(value) FROM things

WHERE 1=1

GROUP BY thing

having AVG(value) > 10