Mis562 Week two

Mis562 Week 2 29.08.2019
 Mis562 Week 2 Article

MIS562 Week two Assignment

Homework week a couple of

Portion 1

1 . SQL> --1. List all employee data in department 30. SQL> SELECT 5. FROM EMP WHERE DEPTNO = 40;

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 five-hundred 30 7654 MARTIN STORE ASSISTANT 7698 28-SEP-81 1250 1400 30 7698 BLAKE ADMINISTRATOR 7839 01-MAY-81 2850 40 7844 TURNER SALESMAN 7698 08-SEP-81 truck 0 40 7900 WAYNE CLERK 7698 03-DEC-81 950 30

six rows picked.

SQL>

2 . SQL> --2. List employees name, job, and salary this is a manager and has a salary > $1, 000 SQL>

SQL> SELECT ENAME, JOB, SAL FROM EMP

2 WHEREVER EMPNO IN

3 (SELECT e. MGR FROM EMP e EXACTLY WHERE e. MGR IS CERTAINLY NOT NULL)

four AND GRACIA > one thousand;

ENAME JOB SAL

---------- --------- ----------

KIA ANALYST 3 thousands

BLAKE ADMINISTRATOR 2850

CALIFORNIA KING PRESIDENT 5000

JONES ADMINISTRATOR 2975

SCOTT ANALYST 3 thousands

CLARK DIRECTOR 2450

6 rows selected.

SQL>

3. SQL> --3. Repeat exercise two for any staff that is not a manager or perhaps earns a salary > $1, 000 SQL> SELECT ENAME, JOB, SAL FROM EMP WHERE EMPNO NOT IN (SELECT MGR FROM EMP WHERE MGR IS NOT NULL) OR PERHAPS SAL > 1000;

ENAME JOB SAL

---------- --------- ----------

SMITH ATTENDANT 800

ALLEN SALESMAN 1600

WARD SALESMAN 1250

ROBERTS MANAGER 2975

MARTIN STORE ASSISTANT 1250

BLAKE MANAGER 2850

CLARK DIRECTOR 2450

JEFF ANALYST 3000

KING DIRECTOR 5000

TURNER SALESMAN truck

ADAMS CLERK 1100

WAYNE CLERK 950

FORD EXPERT 3000

BURNS CLERK toll free

KIRK CAPTAIN 6000

12-15 rows selected.

SQL>

4. Show all worker names and salary that earn between $1, 000 and $2, 000. Utilize the between agent. SQL> SELECT ENAME, GARBO FROM EMP WHERE GRACIA BETWEEN a thousand AND 2k;

ENAME SAL

---------- ----------

ALLEN 1600

WARD 1250

MARTIN 1250

TURNER 1500

ADAMS 1100

MILLER toll free

6 series selected.

SQL>

your five. Select almost all employees which can be in office 10 and 30. Use the IN operator. SQL> SELECT * VIA EMP WHERE DEPTNO IN (10, 30);

EMPNO ENAME WORK MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 three hundred 30 7521 WARD JEWELER 7698 22-FEB-81 1250 five-hundred 30 7654 MARTIN SALES PERSON 7698 28-SEP-81 1250 1400 30 7698 BLAKE DIRECTOR 7839 01-MAY-81 2850 35 7782 CLARK SIMON MANAGER 7839 09-JUN-81 2450 10 7839 KING LEADER 17-NOV-81 5000 10 7844 TURNER SALESMAN 7698 08-SEP-81 1500 zero 30 7900 JAMES ATTENDANT 7698 03-DEC-81 950 40 7934 BURNS CLERK 7782 23-JAN-82 toll free 10

on the lookout for rows picked.

SQL>

6. Choose all staff names with an " A” in the first location of the staff name. Utilize substring function or a crazy card. SQL> SELECT ENAME FROM EMP WHERE SUBSTR(UPPER(ENAME), 1, 1) =...

ACC 291 Learning Staff Final Composition

News