I.
select e.ename,d.loc "Location",d.dname"Department Name"
from emp e join dept d
on e.deptno=d.deptno;
mysql> select e.ename,d.loc "Location",d.dname"Department Name"
-> from emp e join dept d
-> on e.deptno=d.deptno;
+--------+----------+-----------------+
| ename | Location | Department Name |
+--------+----------+-----------------+
| KING | New York | Accounting |
| Blake | Chicago | Sales |
| Clark | New York | Accounting |
| Jones | Dallas | Research |
| Scott | Dallas | Research |
| Ford | Dallas | Research |
| Smith | Dallas | Research |
| Allen | Chicago | Sales |
| Ward | Chicago | Sales |
| Martin | Chicago | Sales |
| Turner | Chicago | Sales |
| Adams | Dallas | Research |
| James | Chicago | Sales |
| Miller | New York | Accounting |
+--------+----------+-----------------+
14 rows in set (0.00 sec)
II.
mysql> select job,sum(sal) "Total Salary"
-> from emp
-> group by job;
+-----------+--------------+
| job | Total Salary |
+-----------+--------------+
| President | 5000 |
| Manager | 8275 |
| Analyst | 6000 |
| Clerk | 4150 |
| Salesman | 5600 |
+-----------+--------------+
5 rows in set (0.00 sec)
III.
mysql> select dname,deptno
-> from dept
-> where deptno in (
-> select deptno from emp
-> group by deptno
-> having count(*)>3);
+----------+--------+
| dname | deptno |
+----------+--------+
| Research | 20 |
| Sales | 30 |
+----------+--------+
2 rows in set (0.00 sec)
IV
mysql> select * from emp
-> where empno not in(
-> select mgr from emp
-> where mgr is not null);
+-------+--------+----------+------+------------+------+------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+--------+----------+------+------------+------+------+--------+
| 7369 | Smith | Clerk | 7902 | 1980-12-17 | 800 | NULL | 20 |
| 7499 | Allen | Salesman | 7698 | 1981-02-20 | 1600 | 300 | 30 |
| 7521 | Ward | Salesman | 7698 | 1984-02-22 | 1250 | 500 | 30 |
| 7654 | Martin | Salesman | 7698 | 1982-09-28 | 1250 | 1400 | 30 |
| 7844 | Turner | Salesman | 7698 | 1981-09-08 | 1500 | 0 | 30 |
| 7876 | Adams | Clerk | 7788 | 1983-07-17 | 1100 | NULL | 20 |
| 7900 | James | Clerk | 7698 | 1981-12-03 | 950 | NULL | 30 |
| 7934 | Miller | Clerk | 7782 | 1982-01-23 | 1300 | NULL | 10 |
+-------+--------+----------+------+------------+------+------+--------+
8 rows in set (0.00 sec)
