CREATE TABLE employees (
id INTEGER,
name TEXT,
position TEXT,
dob DATE,
city TEXT
);
INSERT INTO employees (id, name, position, dob, city) VALUES
(1203, 'Mike', 'manager', '1984-02-11', 'Moscow'),
(1204, 'Piter', 'SEO', '1987-06-21', 'Pskov'),
(1205, 'Lisa', 'HR', '1982-10-17', 'Moscow'),
(1206, 'Alex', 'manager', '1985-03-03', 'Luga');
CREATE TABLE payments (
name TEXT,
date DATE,
amount NUMERIC
);
INSERT INTO payments (name, date, amount) VALUES
('Mike', '2023-03-30', 70),
('Piter', '2023-03-30', 200),
('Mike', '2023-04-30', 100),
('Lisa', '2023-04-30', 250),
('Piter', '2023-05-31', 200),
('Lisa', '2023-05-31', 300),
('Alex', '2023-05-31', 100);
SELECT * FROM employees WHERE dob >= '1984-01-01';
SELECT position, COUNT(*) as employee_count FROM employees GROUP BY position;
SELECT DISTINCT city FROM employees;
SELECT AVG(p.amount) as average_payment
FROM payments p
JOIN employees e ON p.name = e.name
WHERE e.city = 'Moscow';
Telegram: DB developers channel