Special Sub-Queries & User Input

Multiple Row Sub-Query:

IN, ANY, ALL

 

IN:

Student Exercise:

Find the employees who earn the same salary as the minimum salary of departments.

 

Output

ENAME         DEPTNO       SAL

———- ——— ———

SMITH             20       800

JAMES             30       950

MILLER            10      1300

 

ANY:

Example:

 

SQL> SELECT empno, ename, job

2  FROM EMP

3  WHERE sal < ANY (SELECT sal

4                   FROM EMP

5                   WHERE job=’CLERK’)

6  AND job <> ‘CLERK’;

 

Output

EMPNO ENAME      JOB

——— ———- ———

7521 WARD       SALESMAN

7654 MARTIN     SALESMAN

 

 

NOTES

  • This example displays employees whose salary is less than any clerk and who are not clerks.
  • For example if the minimum salary is 1300 this statement displays employees who are not clerks but earn less than 1300.

 

RULES

  • “ < ANY “ means less than Maximum value returned.
  • “ > ANY “ means more than Minimum value returned.
  • “ = ANY “ equivalent to “IN”.

 

 

 

ALL:

  • “ > ALL “ means that more than the maximum.
  • “ < ALL “ means that less than the minimum.

 

 

 

User Input:

  • The Oracle SQL accept inputs by the use of substitution variables. The     “ & “ symbol is use for inputting the values from the user.

 

 

Example:

SQL> SELECT empno, ename, sal, deptno

2  FROM EMP

3  WHERE empno = &employee_num;

 

 

 

 

Substitution variables

 

Output

Enter value for employee_num: 7839

old   3: WHERE empno = &employee_num

new   3: WHERE empno = 7839

 

EMPNO ENAME            SAL    DEPTNO

——— ———- ——— ———

7839 KING            5000        10

 

 

 

 

Student Exercise1:

Write SQL that accepts a wildcard ename from the user and displays the employee name and department number of the employee.

 

 

 

Scripts:

  • Scripts run at the command line interface of the Oracle.
  • We can use the get command or “@@” command.
  • Oracle scripts is file of SQL statements that runs in batch mode.
  • Scripts files have an extension of .SQL.

 

&&:

The double & variable accepts only ones and then reuse the value.

 

 

Example:

SQL> SELECT empno, ename, job, &&Col_Nam

2  FROM EMP

3  ORDER BY &Col_Nam;

 

Output

Enter value for col_nam: deptno

old   1: SELECT empno, ename, job, &&Col_Nam

new   1: SELECT empno, ename, job, deptno

old   3: ORDER BY &Col_Nam

new   3: ORDER BY deptno

 

EMPNO ENAME      JOB          DEPTNO

——— ———- ——— ———

7782 CLARK      MANAGER          10

7902 FORD       ANALYST          20

7499 ALLEN      SALESMAN         30

………..

Accept:

  • Accept command is use to get an input and display a user prompt by using the “PROMPT” keyword.
  • Accept only be use within a script.

 

Example:

ACCEPT dept PROMPT Department_name?

SELECT *

FROM DEPT

WHERE dname=UPPER(‘&dept’);

 

  • Save upper SQL as a script, name a.sql.

 

Output

SQL> @ a

Department_name?SALES

old   3: WHERE dname=UPPER(‘&dept’)

new   3: WHERE dname=UPPER(‘SALES’)

 

DEPTNO DNAME          LOC

——— ————– ————-

30 SALES          CHICAGO

 

 

 

DML (DATA MANUPULATION LANGUAGE):

  • A DML statement is executed when you;
  • Add new rows to a table.
  • Modify existing rows in a table
  • Remove existing rows from a table. Eg. DELETE Operator

 

 

Transaction:

  • A transaction is a collection of DML statement that form a logical unit of work. Eg. E-Commerce

 

STOCK                                  CUS_ORDER

                             DEL Operator                     Add or INSERT Operator

 

 

COMMIT:

  • Commit makes the changes permanent which are made in DATABASE.

 

 

 

INSERT:

  • Insert the row of data into a table.

 

 

Example:

SQL> INSERT INTO dept(deptno, dname, loc)

2  VALUES (50,’DEVELOPMENT’,’DETROIT’);

 

Output

1 row created.

 

 

SQL> INSERT INTO dept(deptno,loc)

2  VALUES (60,’KARACHI’);

 

Output

1 row created.

 

  • In upper query case dname will be NULL

 

 

Student Exercise2:

Write a script that takes input from the user the fields are department number, department name, and location and enters into department table.

 

Output

Enter value for deptno: 70

Enter value for dname: RESEARCH

Enter value for loc: DALLAS

old   2: VALUES (&deptno, ‘&dname’, ‘&loc’)

new   2: VALUES (70, ‘RESEARCH’, ‘DALLAS’)

 

1 row created.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s