CREATE TABLE Staff (
staff_id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100),
salary INT,
email VARCHAR(100),
birthday DATE,
jobtitle_id INT
);
INSERT INTO Staff(name, salary, email, birthday, jobtitle_id)
VALUES
('ΠΠ²Π°Π½ΠΎΠ² Π‘Π΅ΡΠ³Π΅ΠΉ', 100000, 'test@test.ru', '1990-03-03', 1),
('ΠΠ΅ΡΡΠΎΠ² ΠΡΡΡ', 60000, 'petr@test.ru', '2000-12-01', 7),
('Π‘ΠΈΠ΄ΠΎΡΠΎΠ² ΠΠ°ΡΠΈΠ»ΠΈΠΉ', 80000, 'test@test.ru', '1999-02-04', 6),
('ΠΠ°ΠΊΡΠΈΠΌΠΎΠ² ΠΠ²Π°Π½', 70000, 'ivan.m@test.ru', '1997-10-02', 4),
('ΠΠΎΠΏΠΎΠ² ΠΠ²Π°Π½', 120000, 'popov@test.ru', '2001-04-25', 5);
CREATE TABLE Jobtitles (
jobtitle_id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100)
);
INSERT INTO Jobtitles(name)
VALUES
('Π Π°Π·ΡΠ°Π±ΠΎΡΡΠΈΠΊ'),
('Π‘ΠΈΡΡΠ΅ΠΌΠ½ΡΠΉ Π°Π½Π°Π»ΠΈΡΠΈΠΊ'),
('ΠΠ΅Π½Π΅Π΄ΠΆΠ΅Ρ ΠΏΡΠΎΠ΅ΠΊΡΠΎΠ²'),
('Π‘ΠΈΡΡΠ΅ΠΌΠ½ΡΠΉ Π°Π΄ΠΌΠΈΠ½ΠΈΡΡΡΠ°ΡΠΎΡ'),
('Π ΡΠΊΠΎΠ²ΠΎΠ΄ΠΈΡΠ΅Π»Ρ Π³ΡΡΠΏΠΏΡ'),
('ΠΠ½ΠΆΠΈΠ½Π΅Ρ ΡΠ΅ΡΡΠΈΡΠΎΠ²Π°Π½ΠΈΡ'),
('Π‘ΠΎΡΡΡΠ΄Π½ΠΈΠΊ Π³ΡΡΠΏΠΏΡ ΠΏΠΎΠ΄Π΄Π΅ΡΠΆΠΊΠΈ');
/*ΠΠ°ΠΏΠΈΡΠΈΡΠ΅ Π·Π°ΠΏΡΠΎΡ, Ρ ΠΏΠΎΠΌΠΎΡΡΡ ΠΊΠΎΡΠΎΡΠΎΠ³ΠΎ ΠΌΠΎΠΆΠ½ΠΎ Π½Π°ΠΉΡΠΈ Π΄ΡΠ±Π»ΠΈ Π² ΠΏΠΎΠ»Π΅ email ΠΈΠ· ΡΠ°Π±Π»ΠΈΡΡ Sfaff*/
SELECT email, COUNT(email) AS Cnt
FROM Staff
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 Jobtitles.name
FROM
Staff INNER JOIN Jobtitles
ON Staff.jobtitle_id = Jobtitles.jobtitle_id
WHERE salary < (SELECT MAX(salary)
FROM Staff)
LIMIT 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