preview

MIS562 Week 2 Assignment

Satisfactory Essays

MIS562 Week 2 Assignment Homework week 2 Part 1 1. SQL> --1. List all employee information in department 30. SQL> SELECT * FROM EMP WHERE DEPTNO = 30; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 …show more content…

Use a wildcard. SQL> SELECT ENAME FROM EMP WHERE ENAME LIKE '_A%'; ENAME ---------- WARD MARTIN JAMES SQL> 8. List the employee names in alphabetical sequence. SQL> SELECT ENAME FROM EMP; ENAME ---------- SMITH ALLEN WARD JONES MARTIN BLAKE CLARK SCOTT KING TURNER ADAMS JAMES FORD MILLER 14 rows selected. SQL> 9. List the job, salary, and employee name in job order and then salary in descending --order. SQL> SELECT JOB,SAL, ENAME FROM EMP ORDER BY JOB, SAL DESC; JOB SAL ENAME --------- ---------- ---------- ANALYST 3000 FORD ANALYST 3000 SCOTT CLERK 1300 MILLER CLERK 1100 ADAMS CLERK 950 JAMES CLERK 800 SMITH MANAGER 2975 JONES MANAGER 2850 BLAKE MANAGER 2450 CLARK PRESIDENT 5000 KING SALESMAN 1600 ALLEN SALESMAN 1500 TURNER SALESMAN 1250 MARTIN SALESMAN 1250 WARD 14 rows selected. SQL> 10. Show a list of different jobs. Eliminate repeating values. SQL> SELECT DISTINCT(JOB) FROM EMP; JOB --------- CLERK SALESMAN PRESIDENT MANAGER ANALYST SQL> 11. Show employee names, salary that has not earned a commission yet. SQL> SELECT ENAME, SAL FROM EMP WHERE COMM IS NULL; ENAME SAL ---------- ---------- SMITH 800 JONES 2975 BLAKE 2850

Get Access