SUBQUERY & JOIN

Cari Gaji Rata-Rata Per Departemen

• SELECT d.dept_name, AVG(s.salary) AS avg_salary

• FROM departments d JOIN dept_emp de

• JOIN salaries s ON de.emp_no = s.emp_no

• GROUP BY d.dept_name;

• ON d.dept_no = de.dept_no

Answer explanation

SELECT d.dept_name, AVG(s.salary) AS avg_salary

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_name;

Temukan Karyawan dengan Jabatan Tertentu di Departemen

• de.dept_no = d.dept_no JOIN titles t

• ON e.emp_no = de.emp_no JOIN departments d ON

• SELECT e.first_name, d.dept_name, t.title FROM employees e JOIN dept_emp de

• ON e.emp_no = t.emp_no

• WHERE t.title = 'Senior Engineer';

Answer explanation

SELECT e.first_name, d.dept_name, t.title

FROM employees e

JOIN dept_emp de ON e.emp_no = de.emp_no

JOIN departments d ON de.dept_no = d.dept_no

JOIN titles t ON e.emp_no = t.emp_no

WHERE t.title = 'Senior Engineer';

Hitung Total Karyawan di Setiap Departemen

• FROM employees e

• COUNT(*) AS total_employees

• JOIN dept_emp de ON e.emp_no = de.emp_no

• SELECT d.dept_name,

• JOIN departments d ON de.dept_no = d.dept_no GROUP BY d.dept_name;

Answer explanation

SELECT d.dept_name, COUNT(*) AS total_employees

FROM employees e

JOIN dept_emp de ON e.emp_no = de.emp_no

JOIN departments d ON de.dept_no = d.dept_no

GROUP BY d.dept_name;

Cari Karyawan yang Pernah Bekerja di Beberapa Departemen

• GROUP BY e.emp_no

• FROM employees e

• JOIN dept_emp de ON e.emp_no = de.emp_no

• HAVING COUNT(de.dept_no) > 1;

• SELECT e.first_name, e.last_name

Answer explanation

SELECT e.first_name, e.last_name

FROM employees e

JOIN dept_emp de ON e.emp_no = de.emp_no

GROUP BY e.emp_no

HAVING COUNT(de.dept_no) > 1;

Temukan Manager dengan Gaji Tertinggi di Departemennya

• JOIN salaries s ON t.emp_no = s.emp_no

• FROM departments d JOIN dept_emp de ON d.dept_no = de.dept_no JOIN titles t ON de.emp_no = t.emp_no

• ORDER BY MAX(s.salary) DESC;

• SELECT d.dept_name, MAX(s.salary) AS highest_salary

• WHERE t.title = 'Manager' GROUP BY d.dept_name

Answer explanation

SELECT d.dept_name, MAX(s.salary) AS highest_salary

FROM departments d

JOIN dept_emp de ON d.dept_no = de.dept_no

JOIN titles t ON de.emp_no = t.emp_no

JOIN salaries s ON t.emp_no = s.emp_no

WHERE t.title = 'Manager'

GROUP BY d.dept_name

ORDER BY MAX(s.salary) DESC;

Cari Karyawan yang Gajinya di Atas Rata-Rata Departemennya

• SELECT e.first_name, e.last_name, s.salary FROM employees e

• JOIN departments d ON de.dept_no = d.dept_no WHERE d.dept_name = d.dept_name );

• SELECT AVG(s.salary) FROM salaries s JOIN dept_emp de ON s.emp_no = de.emp_no

• WHERE s.salary > (

• JOIN dept_emp de ON e.emp_no = de.emp_no JOIN departments d ON de.dept_no = d.dept_no JOIN salaries s ON e.emp_no = s.emp_no

Answer explanation

SELECT e.first_name, e.last_name, s.salary

FROM employees e

JOIN dept_emp de ON e.emp_no = de.emp_no

JOIN departments d ON de.dept_no = d.dept_no

JOIN salaries s ON e.emp_no = s.emp_no

WHERE s.salary > (

SELECT AVG(s.salary)

FROM salaries s

JOIN dept_emp de ON s.emp_no = de.emp_no

JOIN departments d ON de.dept_no = d.dept_no

WHERE d.dept_name = d.dept_name

);

Temukan nama karyawan yang gajinya lebih tinggi dari rata-rata gaji seluruh karyawan.

SELECT e.first_name, e.last_name, s.salary

FROM employees e

JOIN salaries s ON e.emp_no = s.emp_no

WHERE s.salary > ( _______ );

Hitung jumlah karyawan yang bekerja di setiap departemen dan tampilkan nama departemen beserta jumlah karyawan.

SELECT d.dept_name, COUNT(*) AS total_employees

FROM departments d

JOIN dept_emp de ON d.dept_no = de.dept_no

GROUP BY _______;

Tampilkan nama karyawan yang pernah bekerja di lebih dari satu departemen.

SELECT e.first_name, e.last_name

FROM employees e

JOIN dept_emp de ON e.emp_no = de.emp_no

GROUP BY e.emp_no

HAVING COUNT de.dept_no (_____);

Cari nama karyawan dengan gaji tertinggi di departemen 'HR'.

SELECT e.first_name, e.last_name, s.salary

FROM employees e

JOIN dept_emp de ON e.emp_no = de.emp_no

JOIN salaries s ON e.emp_no = s.emp_no

JOIN departments d ON de.dept_no = d.dept_no

WHERE d.dept_name = 'HR'

ORDER BY _______ DESC

LIMIT 1;