Bài 1:
1. SELECT DISTINCT CONCAT(e.first_name, e.last_name) AS employee_name,
d.department_name, r.role_name FROM employees e JOIN departments d ON
e.department_id = d.department_id JOIN employeeroles r ON e.role_id = r.role_id;
2. SELECT department_name FROM departments;
3. SELECT DISTINCT CONCAT(e.first_name, e.last_name) AS employee_name,
d.department_name, r.role_name FROM employees e JOIN departments d ON
e.department_id = d.department_id JOIN employeeroles r ON e.role_id = r.role_id
WHERE e.role_id = 3;
4. SELECT DISTINCT CONCAT(e.first_name, e.last_name) AS employee_name,
r.role_name, d.department_name FROM employees e JOIN departments d ON
e.department_id = d.department_id JOIN employeeroles r ON e.role_id = r.role_id
WHERE d.department_name = 'HR';
5. SELECT DISTINCT CONCAT(e.first_name, e.last_name) AS employee_name,
d.department_name FROM employees e JOIN departments d ON e.department_id =
d.department_id JOIN employeeroles r ON e.role_id = r.role_id WHERE r.role_name =
'Manager';
6. SELECT d.department_name, COUNT(e.employee_id) AS employee_count FROM
employees e JOIN departments d ON e.department_id = d.department_id GROUP BY
d.department_name;
7. SELECT DISTINCT CONCAT(e.first_name, e.last_name) AS employee_name,
r.role_name FROM employees e JOIN employeeroles r ON e.role_id = r.role_id
WHERE e.employee_id = 2;
8. SELECT DISTINCT CONCAT(first_name, last_name) AS employee_name FROM
employees WHERE first_name LIKE 'J%';
9. SELECT DISTINCT CONCAT(e.first_name, e.last_name) AS employee_name,
d.department_name FROM employees e JOIN departments d ON e.department_id =
d.department_id JOIN employeeroles r ON e.role_id = r.role_id WHERE r.role_name =
'Manager';
10. SELECT department_id, COUNT(employee_id) AS employee_count FROM employees
GROUP BY department_id ORDER BY employee_count DESC;
11. SELECT DISTINCT CONCAT(e.first_name,' ',e.last_name) AS employee_name,
r.role_name FROM employees e JOIN employeeroles r ON e.role_id = r.role_id
WHERE e.first_name = 'Emily' AND e.last_name = 'Williams';
12. SELECT DISTINCT CONCAT(e.first_name,' ',e.last_name) AS employee_name,
d.department_name FROM employees e JOIN departments d ON e.department_id =
d.department_id WHERE d.department_name LIKE 'M%';
13. SELECT DISTINCT CONCAT(e.first_name, ' ', e.last_name) AS employee_name,
d.department_name, r.role_name FROM employees e JOIN departments d ON
e.department_id = d.department_id JOIN employeeroles r ON e.role_id = r.role_id
WHERE r.role_name = 'Director';
14. SELECT CONCAT(e.first_name,' ', e.last_name) AS employee_name,
d.department_name FROM employees e JOIN departments d ON e.department_id =
d.department_id WHERE d.department_name IN ('IT', 'Finance');
15. SELECT CONCAT(e.first_name,' ', e.last_name) AS employee_name,
d.department_name, department_counts.employee_count FROM employees e JOIN
departments d ON e.department_id = d.department_id
JOIN (
SELECT department_id, COUNT(employee_id) AS employee_count
FROM employees
GROUP BY department_id
ORDER BY employee_count DESC
LIMIT 1)
AS department_counts ON e.department_id = department_counts.department_id;
Bài 2:
1. SELECT b.book_id, b.title, a.author_name, p.publisher_name, b.publication_year
FROM books b JOIN authors a ON b.author_id = a.author_id JOIN publishers p ON
b.publisher_id = p.publisher_id;
2. SELECT author_id, author_name FROM authors;
3. SELECT b.book_id, b.title, a.author_name, p.publisher_name, b.publication_year
FROM books b JOIN authors a ON b.author_id = a.author_id JOIN publishers p ON
b.publisher_id = p.publisher_id WHERE b.title = '1984';
4. SELECT b.book_id, b.title, b.publication_year, p.publisher_name FROM books b JOIN
authors a ON b.author_id = a.author_id JOIN publishers p ON b.publisher_id =
p.publisher_id WHERE a.author_name = 'Harper Lee';
5. SELECT b.book_id, b.title, a.author_name, b.publication_year, p.publisher_name
FROM books b JOIN authors a ON b.author_id = a.author_id JOIN publishers p ON
b.publisher_id = p.publisher_id WHERE p.publisher_name LIKE '%D%';
6. SELECT a.author_name FROM books b JOIN authors a ON b.author_id = a.author_id
WHERE b.title = 'Pride and Prejudice';
7. SELECT b.title, b.publication_year FROM books b JOIN publishers p ON b.publisher_id
= p.publisher_id WHERE p.publisher_name = 'Publisher A';
8. SELECT b.title, a.author_name, p.publisher_name, b.publication_year FROM books b
JOIN authors a ON b.author_id = a.author_id JOIN publishers p ON b.publisher_id =
p.publisher_id WHERE b.genre = 'Science Fiction' AND b. publication_year > 1950;
(trong bảng thiếu phần thể loại)
9. SELECT p.publisher_name, COUNT(b.book_id) AS book_count FROM Books b JOIN
Publishers p ON b.publisher_id = p.publisher_id GROUP BY p.publisher_name;
10. SELECT a.author_name, COUNT(b.book_id) AS book_count FROM Books b JOIN
Authors a ON b.author_id = a.author_id GROUP BY a.author_name ORDER BY
book_count DESC;
11. SELECT a.author_name, COUNT(b.book_id) AS book_count FROM Books b JOIN
Authors a ON b.author_id = a.author_id WHERE b.publication_year > 1900 GROUP BY
a.author_name;
12. SELECT b.title, p.publisher_name FROM Books b JOIN Publishers p ON b.publisher_id
= p.publisher_id WHERE b.title LIKE 'The Great%';
13. SELECT b.title, a.author_name FROM Books b JOIN Authors a ON b.author_id =
a.author_id WHERE b.publication_year > 1950;
14. SELECT b.title, p.publisher_name FROM Books b JOIN Publishers p ON b.publisher_id
= p.publisher_id WHERE b.title LIKE '%Mockingbird';
15. SELECT b.title, a.author_name FROM Books b JOIN Authors a ON b.author_id =
a.author_id WHERE b.publication_year > 2000;
