pre_interview.sql

------------------ CREATE SAMPLE DATABASE ------------------

CREATE TABLE depts(
dept_id INT PRIMARY KEY,
dept_name VARCHAR(50) NOT NULL
);

CREATE TABLE Employees(
employee_id INT PRIMARY KEY,
dept_id INT NOT NULL,
employee_name VARCHAR(50) NOT NULL, 
salary bigint NOT NULL
);

INSERT INTO depts(dept_id, dept_name) 
VALUES(1, 'dept_1');
INSERT INTO depts(dept_id, dept_name) 
VALUES(2,'dept_2');
INSERT INTO depts(dept_id, dept_name) 
VALUES(3,'dept_3');


INSERT INTO Employees (dept_id, employee_id, employee_name, salary)
VALUES(1, 1, 'name_1', 500);
INSERT INTO Employees (dept_id, employee_id, employee_name, salary)
VALUES(2, 2, 'name_2', 5000);
INSERT INTO Employees (dept_id, employee_id, employee_name, salary)
VALUES(2, 3, 'name_3', 1000);
INSERT INTO Employees (dept_id, employee_id, employee_name, salary)
VALUES(3, 4, 'name_4', 6000);
INSERT INTO Employees (dept_id, employee_id, employee_name, salary)
VALUES(1, 5, 'name_5', 900);
INSERT INTO Employees (dept_id, employee_id, employee_name, salary)
VALUES(1, 6, 'name_6', 800);
INSERT INTO Employees (dept_id, employee_id, employee_name, salary)
VALUES(3, 7, 'name_7', 800);
INSERT INTO Employees (dept_id, employee_id, employee_name, salary)
VALUES(3, 8, 'name_8', 8000);
INSERT INTO Employees (dept_id, employee_id, employee_name, salary)
VALUES(3, 9, 'name_9', 300);

------------------------------------------------------------------------
-- muc luong cao thu 2 cua dept
SELECT MAX(salary) FROM Employees 
WHERE salary NOT IN (select MAX(salary) FROM Employees);

-- muc luong cao nhat cua moi dept va muc luong phai lon hon 1000
SELECT d.dept_id, d.dept_name, MAX(salary) FROM employees e
LEFT JOIN depts d ON d.dept_id = e.dept_id
GROUP BY d.dept_id, d.dept_name
HAVING MAX(salary) > 1000
ORDER BY d.dept_id;