CREATE TABLE Staff(
staff_id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100),
salary INT,
email VARCHAR(50),
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);
select * from Staff;
CREATE TABLE Jobtitles(
jobtitle_id INT,
name VARCHAR(100)
);
INSERT INTO Jobtitles(jobtitle_id, name)
VALUES
(1, "Π Π°Π·ΡΠ°Π±ΠΎΡΡΠΈΠΊ"),
(2, "Π‘ΠΈΡΡΠ΅ΠΌΠ½ΡΠΉ Π°Π½Π°Π»ΠΈΡΠΈΠΊ"),
(3, "ΠΠ΅Π½Π΅Π΄ΠΆΠ΅Ρ ΠΏΡΠΎΠ΅ΠΊΡΠΎΠ²"),
(4, "Π‘ΠΈΡΡΠ΅ΠΌΠ½ΡΠΉ Π°Π΄ΠΌΠΈΠ½ΠΈΡΡΡΠ°ΡΠΎΡ"),
(5, "Π ΡΠΊΠΎΠ²ΠΎΠ΄ΠΈΡΠ΅Π»Ρ Π³ΡΡΠΏΠΏΡ"),
(6, "ΠΠ½ΠΆΠ΅Π½Π΅Ρ ΡΠ΅ΡΡΠΈΡΠΎΠ²Π°Π½ΠΈΡ"),
(7, "Π‘ΠΎΡΡΡΠ΄Π½ΠΈΠΊ Π³ΡΡΠΏΠΏΡ ΠΏΠΎΠ΄Π΄Π΅ΡΠΆΠΊΠΈ");
select * from Jobtitles;
select s.Staff_id,s.name,s.salary from Staff s
right join Jobtitles on
s.jobtitle_id=Jobtitles.jobtitle_id;
select staff_id,salary from Staff
left join Jobtitles on
Staff.jobtitle_id=Jobtitles.jobtitle_id
where salary <= 100000;
select * from Jobtitles join Staff on
Jobtitles.jobtitle_id=Staff.jobtitle_id;
select * from Staff
inner join (select * from Staff where salary < 100000) Jobtitles
on Staff.jobtitle_id=Jobtitles.jobtitle_id;
create table Details(
staff_id integer,
place text,
age integer );
insert into Details
values(1,"Thrissur",25),(2,"Palakkad",23);
select * from Details;
select upper(name)as Jobtitle from Jobtitles
order by jobtitle_id desc
limit 1;
Select staff_id,name,salary from Staff
where salary between 50000 and 100000
order by salary;
select Staff_id,name,email from Staff
where exists (select salary from Staff where salary <= 100000);
select upper(name) from Staff
where salary in (120000);
Select Staff_id from Staff
where jobtitle_id like 7;
Select name from Staff where jobtitle_id not like 5;
Select staff_id from Staff where salary not like 120000;
Select sum(salary) as Total_Salary from Staff;
Select reverse('Hello') as new_word;
Select insert('Cutepie',4,1,'i') as new_word1;
select left('Cutepie',4)as new_word2;
select distinct name from Jobtitles;
select ltrim (' hello');
select substring('this is sparta world',16,5);
select substring('those days',7,4);
select substring('this is sparta',9,6);
select substring('this is sparta super world',20,5);
/*
ΠΠ°ΠΏΠΈΡΠΈΡΠ΅ Π·Π°ΠΏΡΠΎΡ, Ρ ΠΏΠΎΠΌΠΎΡΡΡ ΠΊΠΎΡΠΎΡΠΎΠ³ΠΎ ΠΌΠΎΠΆΠ½ΠΎ Π½Π°ΠΉΡΠΈ Π΄ΡΠ±Π»ΠΈ Π² ΠΏΠΎΠ»Π΅ email ΠΈΠ· ΡΠ°Π±Π»ΠΈΡΡ Sfaff.
*/
-- SELECT *
-- FROM Staff
-- WHERE email IN (
-- SELECT email FROM Staff GROUP BY 1 HAVING COUNT(*) > 1
-- );
-- /*
-- ΠΠ°ΠΏΠΈΡΠΈΡΠ΅ Π·Π°ΠΏΡΠΎΡ, Ρ ΠΏΠΎΠΌΠΎΡΡΡ ΠΊΠΎΡΠΎΡΠΎΠ³ΠΎ ΠΌΠΎΠΆΠ½ΠΎ ΠΎΠΏΡΠ΅Π΄Π΅Π»ΠΈΡΡ
-- Π²ΠΎΠ·ΡΠ°ΡΡ ΠΊΠ°ΠΆΠ΄ΠΎΠ³ΠΎ ΡΠΎΡΡΡΠ΄Π½ΠΈΠΊΠ° ΠΈΠ· ΡΠ°Π±Π»ΠΈΡΡ Staff Π½Π° ΠΌΠΎΠΌΠ΅Π½Ρ Π·Π°ΠΏΡΠΎΡΠ°.
-- ΠΡΠΈΠΌΠ΅ΡΠ°Π½ΠΈΠ΅: ΡΡΠΈΠ΄ΠΈΡΠ΅ΡΠΊΠΈ Π²ΠΎΠ·ΡΠ°ΡΡ ΡΠ΅Π»ΠΎΠ²Π΅ΠΊΠ° ΡΠ²Π΅Π»ΠΈΡΠΈΠ²Π°Π΅ΡΡΡ Π² ΠΊΠΎΠ½ΡΠ΅ Π΄Π½Ρ ΡΠΎΠΆΠ΄Π΅Π½ΠΈΡ, Ρ.Π΅. ΠΏΡΠΈ Π½Π°ΡΡΡΠΏΠ»Π΅Π½ΠΈΠΈ ΡΠ»Π΅Π΄ΡΡΡΠ΅Π³ΠΎ Π΄Π½Ρ.
-- */
-- SELECT staff_id, name, IF(DAYOFYEAR(NOW()) > DAYOFYEAR(birthday), YEAR(NOW()) - YEAR(birthday), YEAR(NOW()) - YEAR(birthday) -1) AS age
-- FROM Staff;
-- /*
-- ΠΠ°ΠΏΠΈΡΠΈΡΠ΅ Π·Π°ΠΏΡΠΎΡ, Ρ ΠΏΠΎΠΌΠΎΡΡΡ ΠΊΠΎΡΠΎΡΠΎΠ³ΠΎ ΠΌΠΎΠΆΠ½ΠΎ ΠΎΠΏΡΠ΅Π΄Π΅Π»ΠΈΡΡ Π΄ΠΎΠ»ΠΆΠ½ΠΎΡΡΡ (Jobtitles.name)
-- ΡΠΎ Π²ΡΠΎΡΡΠΌ ΠΏΠΎ Π²Π΅Π»ΠΈΡΠΈΠ½Π΅ ΡΡΠΎΠ²Π½Π΅ΠΌ Π·Π°ΡΠΏΠ»Π°ΡΡ.
-- */
SELECT o.email, oc.duplicateCount, o.staff_id
FROM Staff o
inner join (
SELECT email, COUNT(*) AS duplicateCount
FROM Staff
GROUP BY email
HAVING COUNT(*) > 1
) oc on o.email = oc.email;
Telegram: DB developers channel