dbjoin

4.SELECT fname, lname, deptname, mgrdate
FROM employee inner join department
ON employee.deptcode = department.deptcode
WHERE empno = mgrempno
ORDER BY fname;

5.SELECT employee.deptcode, deptname, COUNT(*) currentemploy
FROM employee inner join department
ON employee.deptcode = department.deptcode
WHERE workstatus = 'W' AND sex = 'M' AND salary > 35000
GROUP BY employee.deptcode, deptname
ORDER BY currentemploy DESC, deptcode;

6.SELECT deptname, totalemp, AVG(salary)
FROM employee inner join department
ON employee.deptcode = department.deptcode
WHERE totalemp BETWEEN 2 AND 3
GROUP BY totalemp, deptname
ORDER BY deptname;

7.SELECT employee.deptcode, deptname, fname, totalemp

currentemp
FROM employee left join department
ON employee.deptcode = department.deptcode
WHERE empno = mgrempno AND workstatus = 'W' AND mgrempno IS NOT NULL
ORDER BY deptname;

8.SELECT employee.deptcode, deptname, fname, totalemp

currentemp
FROM department right join employee
ON employee.deptcode = department.deptcode
WHERE empno = mgrempno AND workstatus = 'W' AND totalemp > 3
ORDER BY deptname;

9.SELECT scttname, sprtname, spvtname, employee.deptcode, deptname, empno, fname, salary
FROM employee, department, setpart, setcountry, setprovince
WHERE (employee.deptcode = department.deptcode) AND
(setprovince.spvcode = employee.spvcode) AND
(setpart.sprsctcode = setcountry.sctcode) AND
(setprovince.spvsprcode = setpart.sprcode) AND
workstatus = 'W'
ORDER BY 1, 2, 3, 4, 6;

10.SELECT employee.deptcode, deptname, sex, totalemp, AVG(SYSDATE - startdate)
FROM  employee inner join department
ON employee.deptcode = department.deptcode
GROUP BY employee.deptcode, deptname, sex, totalemp
ORDER BY totalemp DESC,deptname;