sql lab record 5

1. SELECT NAME, SALARY, CITY, JDATE FROM EMPLOYEE;
+---------+--------+-------------+------------+
| NAME    | SALARY | CITY        | JDATE      |
+---------+--------+-------------+------------+
| MARY    |  40000 | HOWARD      | 1976-10-12 |
| SARAH   |  30000 | PARIS       | 1974-09-14 |
| HENRY   |  20100 | NEW DELHI   | 1980-01-25 |
| PETER   |  23000 | NEW YORK    | 1982-09-22 |
| ROBERT  |  12000 | BOSTON      | 1988-10-27 |
| MANILA  |  15000 | LOS ANGELES | 1973-09-26 |
| SENAINA |  21000 | MUMBAI      | 1990-02-28 |
| SHABEER |  12000 | CHENNAI     | 2006-12-16 |
+---------+--------+-------------+------------+

2. SELECT * FROM EMPLOYEE ORDER BY NAME ASC;
+-----+---------+-------------+--------+-----------+------+------------+-------+
| ENO | NAME    | CITY        | SALARY | JOB       | DNO  | JDATE      | MGRID |
+-----+---------+-------------+--------+-----------+------+------------+-------+
| 107 | HENRY   | NEW DELHI   |  20100 | DIRECTOR  |  103 | 1980-01-25 |  NULL |
| 114 | MANILA  | LOS ANGELES |  15000 | CLERK     |  101 | 1973-09-26 |  3421 |
|  10 | MARY    | HOWARD      |  40000 | CLERK     |  100 | 1976-10-12 |  2345 |
| 110 | PETER   | NEW YORK    |  23000 | SALESMAN  |  101 | 1982-09-22 |  3421 |
| 113 | ROBERT  | BOSTON      |  12000 | CLERK     |  102 | 1988-10-27 |  2345 |
| 105 | SARAH   | PARIS       |  30000 | ANALYST   |  101 | 1974-09-14 |  5432 |
| 118 | SENAINA | MUMBAI      |  21000 | SALESMAN  |  103 | 1990-02-28 |  5432 |
| 120 | SHABEER | CHENNAI     |  12000 | ASST. DIR |  104 | 2006-12-16 |  2345 |
+-----+---------+-------------+--------+-----------+------+------------+-------+

3. SELECT NAME, DNAME FROM DEPT, EMPLOYEE WHERE EMPLOYEE.DNO = DEPT.DEPTNO;
+---------+-----------+
| NAME    | DNAME     |
+---------+-----------+
| MARY    | SALES     |
| SARAH   | MARKETING |
| HENRY   | IT        |
| PETER   | MARKETING |
| ROBERT  | FINANCE   |
| MANILA  | MARKETING |
| SENAINA | IT        |
| SHABEER | ACCOUNTS  |
+---------+-----------+

4. SELECT NAME FROM EMPLOYEE WHERE MGRID IS NULL;
+-------+
| NAME  |
+-------+
| HENRY |
+-------+

5. SELECT MGRID, COUNT(*) FROM EMPLOYEE GROUP BY MGRID;
+-------+----------+
| MGRID | COUNT(*) |
+-------+----------+
|  NULL |        1 |
|  2345 |        3 |
|  3421 |        2 |
|  5432 |        2 |
+-------+----------+

6. SELECT JOB, MIN(SALARY), MAX(SALARY) FROM EMPLOYEE, DEPT  WHERE EMPLOYEE.DNO = DEPT.DEPTNO GROUP BY DNAME;
+-----------+-------------+-------------+
| JOB       | MIN(SALARY) | MAX(SALARY) |
+-----------+-------------+-------------+
| ASST. DIR |       12000 |       12000 |
| CLERK     |       12000 |       12000 |
| DIRECTOR  |       20100 |       21000 |
| ANALYST   |       15000 |       30000 |
| CLERK     |       40000 |       40000 |
+-----------+-------------+-------------+

7. SELECT MAX(SALARY) FROM EMPLOYEE WHERE JOB IN ('CLERK', 'ANALYST');
+-------------+
| MAX(SALARY) |
+-------------+
|       40000 |
+-------------+

8. SELECT DNAME, COUNT(*) FROM DEPT, EMPLOYEE WHERE DEPT.DEPTNO = 100 AND EMPLOYEE.DNO = DEPT.DEPTNO;
+-------+----------+
| DNAME | COUNT(*) |
+-------+----------+
| SALES |        1 |
+-------+----------+

9. SELECT DNAME FROM EMPLOYEE, DEPT WHERE JOB = 'CLERK' AND EMPLOYEE.DNO = DEPT.DEPTNO;
+-----------+
| DNAME     |
+-----------+
| SALES     |
| FINANCE   |
| MARKETING |
+-----------+

10. SELECT DNO FROM EMPLOYEE GROUP BY DNO HAVING COUNT(*) < 2;
+------+
| DNO  |
+------+
|  100 |
|  102 |
|  104 |
+------+

11. SELECT NAME, CITY, DNAME FROM EMPLOYEE, DEPT WHERE DEPT.DEPTNO = EMPLOYEE.DNO;
+---------+-------------+-----------+
| NAME    | CITY        | DNAME     |
+---------+-------------+-----------+
| MARY    | HOWARD      | SALES     |
| SARAH   | PARIS       | MARKETING |
| HENRY   | NEW DELHI   | IT        |
| PETER   | NEW YORK    | MARKETING |
| ROBERT  | BOSTON      | FINANCE   |
| MANILA  | LOS ANGELES | MARKETING |
| SENAINA | MUMBAI      | IT        |
| SHABEER | CHENNAI     | ACCOUNTS  |
+---------+-------------+-----------+

12. SELECT NAME, ENO, JOB FROM EMPLOYEE WHERE JOB LIKE '_n%';
+-------+------+---------+
| NAME  | ENO  | JOB     |
+-------+------+---------+
| SARAH |  105 | ANALYST |
+-------+------+---------+

13. SELECT ENO, NAME, JOB FROM EMPLOYEE WHERE NAME LIKE 's%';
+-----+---------+-----------+
| ENO | NAME    | JOB       |
+-----+---------+-----------+
| 105 | SARAH   | ANALYST   |
| 118 | SENAINA | SALESMAN  |
| 120 | SHABEER | ASST. DIR |
+-----+---------+-----------+

14. SELECT NAME, DNAME, LOC FROM EMPLOYEE, DEPT WHERE EMPLOYEE.DNO = DEPT.DEPTNO;
+---------+-----------+------------+
| NAME    | DNAME     | LOC        |
+---------+-----------+------------+
| MARY    | SALES     | PARIS      |
| SARAH   | MARKETING | NEW YORK   |
| HENRY   | IT        | WASHINGTON |
| PETER   | MARKETING | NEW YORK   |
| ROBERT  | FINANCE   | BOSTON     |
| MANILA  | MARKETING | NEW YORK   |
| SENAINA | IT        | WASHINGTON |
| SHABEER | ACCOUNTS  | CANADA     |
+---------+-----------+------------+

15. SELECT COUNT(*) FROM EMPLOYEE;
+----------+
| COUNT(*) |
+----------+
|        8 |
+----------+

16. SELECT DNAME, COUNT(*), EMPLOYEE.DNO FROM EMPLOYEE, DEPT WHERE EMPLOYEE.DNO = DEPT.DEPTNO GROUP BY DNAME;
+-----------+----------+------+
| DNAME     | COUNT(*) | DNO  |
+-----------+----------+------+
| ACCOUNTS  |        1 |  104 |
| FINANCE   |        1 |  102 |
| IT        |        2 |  103 |
| MARKETING |        3 |  101 |
| SALES     |        1 |  100 |
+-----------+----------+------+

17. SELECT MGRID, DEPT.DEPTNO, DNAME FROM DEPT, EMPLOYEE WHERE DEPT.DEPTNO = EMPLOYEE.DNO;
+-------+--------+-----------+
| MGRID | DEPTNO | DNAME     |
+-------+--------+-----------+
|  2345 |    100 | SALES     |
|  5432 |    101 | MARKETING |
|  NULL |    103 | IT        |
|  3421 |    101 | MARKETING |
|  2345 |    102 | FINANCE   |
|  3421 |    101 | MARKETING |
|  5432 |    103 | IT        |
|  2345 |    104 | ACCOUNTS  |
+-------+--------+-----------+

18. SELECT SUBSTRING(NAME, 2, 3) FROM EMPLOYEE;
+-----------------------+
| SUBSTRING(NAME, 2, 4) |
+-----------------------+
| ARY                   |
| ARAH                  |
| ENRY                  |
| ETER                  |
| OBER                  |
| ANIL                  |
| ENAI                  |
| HABE                  |
+-----------------------+

19. SELECT UCASE(NAME) FROM EMPLOYEE;
+-------------+
| UCASE(NAME) |
+-------------+
| MARY        |
| SARAH       |
| HENRY       |
| PETER       |
| ROBERT      |
| MANILA      |
| SENAINA     |
| SHABEER     |
+-------------+