MySQL Coding

Đây là phần 3 bài thi đầu vào trước khi phỏng vấn của bank T đỏ. Chủ đề SQL

Question 1:

You are given access to data from a multinational company with a diverse group of employee in various departments.

Write an SQL query to retrieve all employees whose last name begins with the letter 'S'.

Limit the result to 20 rows.

The query should return 3 columns: emp_no, first_name, and last_name

The result must be ordered by the emp_no column in ascending order.

This is a "MySQL Question". Only "Select * from Where... Queries" will work with this question. Do not use UPDATE, DELETE etc.

Table Schema

employees
emp_no
first_name
last_name
birth_date

Sample Input

employees

emp_no	first_name	last_name	birth_date
101	Austin	Shaun	1960-10-12
102	Sandra	Foote	1991-06-04
103	Jane	Kholes	1952-02-26
104	Williams	Stephen	1976-03-29

Result Set

This is the sample ResultSet. The view may differ from the actual output

Sample Output

emp_no	first_name	last_name
101	Austin	Shaun
104	Williams	Stephen

Cùng phân tích yêu cầu bài toán:

• Lọc last_name bắt đầu bằng 'S'
• Trả về 3 cột: emp_no, first_name, and last_name
• Sắp xếp theo emp_no tăng dần
• Giới hạn 20 dòng

SELECT emp_no, first_name, last_name
FROM employees
WHERE last_name LIKE 'S%'
ORDER BY emp_no ASC
LIMIT 20;

Question 2:

Advanced Subqueries: Departments Salary Variation Analysis

You are given data from a company that has multiple employees

Write a query to list the departments where the difference between the highest and lowest salary is greater than the average salary of all employees.

The query should return 2 columns: dept_no and dept_name

The result must be ordered by the dept_name column in alphabetical order

Table Schema:

• dept_emp: emp_no, dept_no, from_date, to_date
• departments: dept_no, dept_name
• salaries: emp_no, salary, from_date, to_date

Sample Input:

dept_emp

emp_no	dept_no	from_date	to_date
101	d001	21-04-1970	22-11-1996
102	d003	31-01-1984	07-08-1990
103	d001	11-06-1993	01-01-9999
104	d002	07-02-1987	04-08-1991
105	d002	02-11-1981	01-01-9999
106	d003	28-08-1973	01-01-9999
107	d004	26-04-1977	01-01-1984

departments

dept_no	dept_name
d001	Customer Support
d002	Quality
d003	HR
d004	Production
d005	Sales

salaries

emp_no	salary	from_date	to_date
101	50000	21-04-1970	22-11-1996
102	60000	31-01-1984	07-08-1990
103	30000	11-06-1993	01-01-9999
104	40000	07-02-1987	04-08-1991
105	30000	02-11-1981	01-01-9999
106	65000	28-08-1973	01-01-9999
107	50000	26-04-1977	01-01-1984

Sample Output

dept_no	dept_name
d003	HR
d004	Production

Phân tích yêu cầu bài toán:

Cần tìm các phòng ban (department) thỏa điều kiện:

(MAX salary - MIN salary của phòng ban) > AVG salary của toàn công ty. 

1. Tính toán mức lương trung bình toàn công ty: Đây sẽ là một truy vấn con (subquery) đóng vai trò làm mốc so sánh

SELECT AVG(salary) FROM salaries

2. Tính toán độ lệch lương trong từng phòng ban:

MAX(salary) - MIN(salary)

3. Kết hợp bảng (Join): Cần kết nối 3 bảng departments, dept_emp, và salaries để lấy đủ thông tin tên phòng ban và lương của nhân viên
4. Điều kiện lọc (Having): Sử dụng Having sau khi group by để lọc ra các phòng ban có độ lệch lương lớn hơn mức trung bình toàn công ty.
5. Sắp xếp theo dept_name theo thứ tự từ A-Z

SELECT d.dept_no, d.dept_name
FROM departments d
JOIN dept_emp de ON d.dept_no = de.dept_no
JOIN salaries s ON de.emp_no = s.emp_no
GROUP BY d.dept_no, d.dept_name
HAVING (MAX(s.salary) - MIN(s.salary)) > (
    SELECT AVG(salary) FROM salaries
)
ORDER BY d.dept_name ASC;

Question 3:

Managers of Departments

You are given data from a company that has multiple employees.

Write a query to find out the count of managers in the "Development", "Production", and "Research" departments, and keep other departments as "0".

The query should return 3 columns: dept_no, dept_name and count.

The result must be ordered by dept_no in ascending order

Table Schema:

• departments: dept_no, dept_name
• dept_manager: emp_no, dept_no, from_date, to_date

Sample Input

departments

dept_no	dept_name
d001	Finance
d002	Development

dept_manager

emp_no	dept_no	from_date	to_date
101	d001	1985-10-12	1991-04-24
102	d002	1991-06-04	1994-01-12

Sample Output

dept_no	dept_name	count
d001	Finance	0
d002	Development	1

Yêu cầu bài toán:

• Đếm số manager theo từng dept_no
• Nhưng:
  • Chỉ 3 phòng ban: Development, Production, Research -> tính count thật
  • Các phòng ban khác -> trả về 0
• Vẫn phải hiển thị tất cả departments
• Sort theo dept_no ASC

Phân tích yêu cầu:

1. Hiển thị tất cả phòng ban: Vì đề bài yêu cầu hiển thị cả những phòng ban không nằm trong danh sách chỉ định với giá trị bằng "0", chúng ta sử dụng LEFT JOIN từ bảng departments
2. Điều kiện đếm: Như yêu cầu bài toán
3. Hàm đếm (COUNT): Chúng ta sẽ sử dụng cấu trúc CASE WHEN bên trong hàm COUNT hoặc SUM để phân loại các phòng ban thỏa mãn điều kiện.

Áp dụng Count sẽ không tính giá trị Null, nên các giá trị ngoài 3 phòng ban sẽ gán null

4. Sắp xếp

SELECT 
    d.dept_no, 
    d.dept_name,
    COUNT(CASE 
        WHEN d.dept_name IN ('Development', 'Production', 'Research') THEN dm.emp_no 
        ELSE NULL 
    END) AS count
FROM departments d
LEFT JOIN dept_manager dm ON d.dept_no = dm.dept_no
GROUP BY d.dept_no, d.dept_name
ORDER BY d.dept_no ASC;