SQLize Online / PHPize Online  /  SQLtest Online

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'), ('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 workers.name FROM workers INNER JOIN tasks ON workers.worker_id=tasks.worker_id /* 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;