CREATE TABLE emp empno INT ename VARCHAR50 job VARCHAR50 mgr INT hiredate DATE sal DECIMAL72 comm decimal72 deptno INT ;INSERT INTO emp values7369SMITHCLERK79021980-12-17800NULL20;INSERT INTO e

SELECT dept.deptno, dept.dname, dept.loc, COUNT(emp.empno) AS num_of_employees FROM dept LEFT JOIN emp ON dept.deptno = emp.deptno GROUP BY dept.deptno, dept.dname, dept.loc;

SELECT e.ename, m.ename AS manager_name FROM emp e LEFT JOIN emp m ON e.mgr = m.empno;

SELECT dept.dname, emp.* FROM dept LEFT JOIN emp ON dept.deptno = emp.deptno UNION SELECT dept.dname, NULL, NULL, NULL, NULL, NULL, NULL, dept.deptno FROM dept LEFT JOIN emp ON dept.deptno = emp.deptno WHERE emp.empno IS NULL;

SELECT job, COUNT(*) AS num_of_employees FROM emp WHERE sal > 15000 GROUP BY job;

SELECT emp.ename FROM emp JOIN dept ON emp.deptno = dept.deptno WHERE dept.dname = 'SALES';

SELECT emp.ename, dept.dname FROM emp JOIN dept ON emp.deptno = dept.deptno WHERE emp.job = (SELECT job FROM emp WHERE ename = 'SMITH')