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…
SQL>

12. Show the employee name, salary, commission, and the total of salary and commission. Be --sure you evaluate null values correctly.

SQL> COL SAL FORMAT 9,999
SQL> sELECT ENAME, SAL, NVL(TO_CHAR(COMM), 'N/A') COMMISION, (SAL + NVL(COMM,0)) "TOTAL SALARY AND COMMISSION" 2 FROM EMP;

ENAME SAL COMMISION TOTAL SALARY AND COMMISSION
---------- ------ ---------------------------------------- ---------------------------
SMITH 800 N/A 800
ALLEN 1,600 300 1900
WARD 1,250 500 1750
JONES 2,975 N/A 2975
MARTIN 1,250 1400 2650
BLAKE 2,850 N/A 2850
CLARK 2,450 N/A 2450
SCOTT 3,000 N/A 3000
KING 5,000 N/A 5000
TURNER 1,500 0 1500
ADAMS 1,100