CREATE TABLE tasks (
tasks_id INT PRIMARY KEY AUTO_INCREMENT,
task_id TEXT,
worker_id TEXT
);
INSERT INTO tasks(task_id, worker_id)
VALUES
('string', 'string'),
('1294984e-69a1-4b2a-a731-0985faa81671','203dc32d986b1362a6d6df77cef74273'),
('a901c18e-535a-438f-b954-05fd3a408d41','fc95f7ad1d81553881e8ee28259867ca'),
('55d73f22-9e89-43f0-982c-8d697125efa3','7364411a8ddfc5d3de3fd526021e522f'),
('001ec379-6667-476d-b639-9f90f19f1f80','ae13a896b810fd8bbb02ef8488e22166'),
('f9f7bc20-fbe2-4d84-9927-4943a1e93715','b0b09eca5878a3c50591c36e2eb881dd'),
('e786edba-62d2-4fcf-81c5-778f3b7874d6','6ac3e0979593b7a52bec8bde1cb7ca32'),
('d3aae38c-2f13-4c3e-9056-0850c5c2b52f','cfbeaac0a8d523fdde6173b475ef0377'),
('27778b70-d6ca-4586-8f3d-d359600f74fb','7364411a8ddfc5d3de3fd526021e522f'),
('a8176c85-3483-4d68-81c7-bce881e66bf8','6ac3e0979593b7a52bec8bde1cb7ca32'),
('74e42c84-b27b-49f3-92f8-a3ca69cedc7e','6ac3e0979593b7a52bec8bde1cb7ca32'),
('74e42c84-b27b-49f3-92f8-a3ca69cedc7e','6ac3e0979593b7a52bec8bde1cb7ca32'),
('898a0e0e-b11f-4586-95de-909c42b3ae24','59ee0ca817910fa3072a756c4fb68b9d');
CREATE TABLE workers (
workers_id INT PRIMARY KEY AUTO_INCREMENT,
worker_id TEXT,
name TEXT
);
INSERT INTO workers(worker_id, name)
VALUES
('string','string'),
('7364411a8ddfc5d3de3fd526021e522f','Paula'),
('49bc3c2953bb92929c2b76f55ef6f385','Maria'),
('b6a8d11009ab4a00b47852cd23cd11d6','Barbara'),
('ae13a896b810fd8bbb02ef8488e22166','William'),
('0b3be7ee062ed5f28f47e29517d44b9d','Sherry'),
('3cca5cabe5aa49b30e2225488a957825','Sean'),
('b0b09eca5878a3c50591c36e2eb881dd','Dorothy'),
('cfbeaac0a8d523fdde6173b475ef0377','Gene'),
('bfca963705b25c533fc33021bea05315','William'),
('6ac3e0979593b7a52bec8bde1cb7ca32','Bonnie'),
('fc95f7ad1d81553881e8ee28259867ca','Kimberly'),
('203dc32d986b1362a6d6df77cef74273','Erica'),
('a8c81055043701da6a9db21e509b2c44','Virginia'),
('ce640a7ccf6c239a0dbee48655c546fd','Jacqueline'),
('e5cb1032864e8bff24ab307480dcf181','Carl'),
('59ee0ca817910fa3072a756c4fb68b9d','Joseph'),
('eb43596d12ad5635bfa95c54a6d04052','Turner'),
('8af1e55fbaaf359854aea4641d8c1ecf','Vaughn'),
('a4eefb6c4e2aa18eedb3a831c23f4545','Hansen');
/*Напишите запрос, с помощью которого можно найти дубли в поле email из таблицы Sfaff*/
/*
SELECT name
FROM workers
GROUP BY email
HAVING Cnt > 1;
/*Напишите запрос,с помощью которого можно определить возраст каждого сотрудника из таблицы Staff на момент запроса*/
/*
SELECT name, (YEAR(CURRENT_DATE)-YEAR(birthday))-(RIGHT(CURRENT_DATE,5)<RIGHT(birthday, 5)) AS Age
FROM Staff
ORDER BY name;
/*Напишите запрос, с помощью которого можно определить должность (Jobtitles.name) со вторым по величине уровнем зарплаты.*/
/*
SELECT workers.name
FROM
workers INNER JOIN tasks
ON workers.worker_id = tasks.worker_id
WHERE worker_id IS NULL
*/
SELECT name
FROM workers
WHERE workers.worker_id NOT IN(
SELECT worker_id
FROM tasks)
and workers.worker_id in (
SELECT top (3) worker_id
FROM tasks)
/*
SELECT count(task_id) AS tasks
FROM (
SELECT task_id FROM tasks
GROUP BY task_id HAVING COUNT(task_id) > 1
) AS t
/*
SELECT name
FROM workers INNER JOIN tasks
ON workers.worker_id = tasks.worker_id
WHERE workers.worker_id NOT IN( SELECT worker_id FROM tasks)
WITH t
AS (SELECT count(task_id) AS tasks
FROM (
SELECT task_id FROM tasks
GROUP BY task_id HAVING COUNT(task_id) > 1)
)
/*
SELECT *, IF(DAYOFYEAR(NOW()) > DAYOFYEAR(birthday), YEAR(NOW()) - YEAR(birthday), YEAR(NOW()) - YEAR(birthday) -1) AS age
--FROM Staff;
/*
Напишите запрос, с помощью которого можно определить должность (Jobtitles.name)
со вторым по величине уровнем зарплаты.
*/
/*SELECT MAX(salary) FROM Staff;
/*
sdvvvvvvvvНапишитеdssssssssssssssssss запрос, с помощью которого можно определить должность (Jobtitles.name)
со вторым по величине уровнем зарплаты.
*/
/*SELECT Jobtitles.name
FROM
Staff INNER JOIN Jobtitles
ON Staff.jobtitle_id = Jobtitles.jobtitle_id
WHERE salary < (SELECT MAX(salary)
FROM Staff)
SELECT Jobtitles.name
FROM
Staff INNER JOIN Jobtitles
ON Staff.jobtitle_id = Jobtitles.jobtitle_id
WHERE salary = (SELECT MAX(salary)
FROM (SELECT DISTINCT TOP (2) salary
FROM Staff
ORDER BY salary DESC) T);
WITH T
AS (SELECT *,
DENSE_RANK() OVER (ORDER BY salary DESC) AS Rnk
FROM Staff)
SELECT name
FROM T
WHERE Rnk = 2;
Telegram: DB developers channel