SQL Fundamentals

General

No

SQL FUNDAMENTAL:

Structured Query Language (SQL) is language that provides an interface to relational database systems. SQL was developed by IBM in the 1970s for use in System R, and is a de facto standard, as well as an ISO and ANSI standard. SQL is often pronounced SEQUEL. SQL is a simple and powerful language used to create, access and manipulate data and structure in the database. SQL is like plain English easy to understand and to write. Oracle divides SQL statements into various categories, which are:

  • DATA DEFINITION LANGUAGE
  • DATA MANIPULATION LANGUAGE
  • DATA CONTROL LANGUAGE

DATA DEFINITION LANGUAGE: DDL statements are used to define, alter or drop database object. Some of the DDL statements are CREATE, ALTER, DROP AND RENAME.

DATA MANIPULATION LANGUAGE: Once the tables have been created, the DML statements enable users to query or manipulate data in existing schemas objects. DML statements normally the most frequently used command. The some of the DML statements in Oracle are DELETE, INSERT, SELECT AND UPDATE.

DATA CONTROL LANGUAGE: A privilege can either be granted to a user with the help of GRANT statement. The privileges assigned can be SELECT, ALTER, DELETE, EXECUTE, INSERT, INDEX etc. In addition to granting of privileges, you can also revoke it by using REVOKE command.

SELECT STATEMENT

                        The SELECT statement is the most commonly used statement in SQL and is used to retrieve information already stored in the database. To retrieve data, you can either select all the column values or name specific columns in the SELECT clause to retrieve data.

Syntax of SELECT statement

SELECT [distinct] <tablename.columname>

FROM <tablename>

[WHERE <condition>]

[GROUP BY<columnname(s)>]

[HAVING<condition>]

[ORDER BY<expression>];

                        The main components of a SELECT statement are the SELECT clause and the FROM clause. A SELECT clause contains the list of columns or expressions containing data you want to see. The FROM clause tells Oracle which database table is used to retrieve the information. Rest all the clauses are optional. The end of the statement is indicated by the “;”.

SELECT (column_name1, column_name2,……..,column_nameN) FROM tablename;

DUAL TABLE

            DUAL is Oracle data dictionary table with onl one row and one column in it. This table provided so that a developer performing a query or writing a function can guarantee a known result. The DUAL table has one column name ‘DUMMY’ of type ‘VARCHAR2(1) and there is one row in the table with a value x.(check using DESC DUAL).

Since Oracle’s many functions work on both columns and literals( such as 65,’A’ etc), it is possible to demonstrate some of its functioning using just literals or pseudo columns such as sysdate. If you  Select a literal or a pseudocoloumn or an expression from any table, the returned will be returned once for each row of the result set.

QUERY 1:-WAQ TO DISPLAY EMPNO, ENAME, SALARY AND SALARY INCREASE BY 15% LABLE THAT NEW COLUMN AS NEWSAL?

SQL> SELECT EMPNO, SAL, (SAL+(SAL*15/100)) AS NEWSAL FROM EMP;

  EMPNO       SAL         NEWSAL

  ——–      ———         ———

     7369           800              920

     7499         1600            1840

     7521         1250         1437.5

     7566         2975       3421.25

     7654         1250         1437.5

     7698         2850         3277.5

     7782         2450         2817.5

     7788         3000            3450

     7839         5000            5750

     7844         1500            1725

     7876         1100            1265

     7900           950         1092.5

     7902         3000            3450

     7934         1300            1495

14 rows selected.                                               

QUERY 2:- CREATE A QUERY THAT PRODUCES AND DISPLAY IN THE FOLLOWING FORMAT   <Employee name> earns $ <salary> monthly and working as <job>FROM THE EMP TABLE?

SQL> SELECT ENAME||’ EARNS $ ‘||SAL||

  2  ‘ MONTHLY AND WORKING AS ‘||JOB

  3 FROM EMP;

ENAME||’EARNS$’||SAL||’MONTHLYANDWORKINGAS’||JOB

——————————————————————————————–

SMITH EARNS $ 800 MONTHLY AND WORKING AS CLERK

ALLEN EARNS $ 1600 MONTHLY AND WORKING AS SALESMAN

WARD EARNS $ 1250 MONTHLY AND WORKING AS SALESMAN

JONES EARNS $ 2975 MONTHLY AND WORKING AS MANAGER

MARTIN EARNS $ 1250 MONTHLY AND WORKING AS SALESMAN

BLAKE EARNS $ 2850 MONTHLY AND WORKING AS MANAGER

CLARK EARNS $ 2450 MONTHLY AND WORKING AS MANAGER

SCOTT EARNS $ 3000 MONTHLY AND WORKING AS ANALYST

KING EARNS $ 5000 MONTHLY AND WORKING AS PRESIDENT

TURNER EARNS $ 1500 MONTHLY AND WORKING AS SALESMAN

ADAMS EARNS $ 1100 MONTHLY AND WORKING AS CLERK

JAMES EARNS $ 950 MONTHLY AND WORKING AS CLERK

FORD EARNS $ 3000 MONTHLY AND WORKING AS ANALYST

MILLER EARNS $ 1300 MONTHLY AND WORKING AS CLERK

14 rows selected.

QUERY 3:-WAQ TO DISPLAY ENAME, JOB AND SAL WHOSE SAL IS LESS OR EQUAL TO 3000 AND JOB IS MANAGER?

SQL> SELECT ENAME, JOB, SAL FROM EMP WHERE SAL<=3000 AND JOB=’MANAGER’;

ENAME    JOB               SAL

———-     ———          ———

JONES      MANAGER     2975

BLAKE     MANAGER     2850

CLARK     MANAGER     2450

QUERY 4:-WAQ TO DISPLAY ENAME, SAL OF THOSE EMPLOYEE WHO DONOT HAVE THERE SALARY IN RANGE OF 1500 AND 2000?

SQL> SELECT ENAME, SAL FROM EMP

2  WHERE SAL NOT BETWEEN 1500 AND 2000;

ENAME       SAL

———-        ———

SMITH              800

WARD            1250

JONES            2975

MARTIN        1250

BLAKE          2850

CLARK          2450

SCOTT            3000

KING              5000

ADAMS         1100

JAMES             950

FORD             3000

MILLER         1300

12 rows selected.

QUERY 5:-WAQ TO DISPLAY NAME OF EMPLOYEE WHOSE NAME CONTAIN ‘M’ AS FIRST ALPHABET AND ‘L’ AS THIRD ALPHABET?

SQL> SELECT ENAME FROM EMP WHERE ENAME LIKE ‘M_L%’;

ENAME

———-

MILLER

QUERY 6:-WAQ TO DISPLAY ENAME, SAL AND DEPTNO WHO ARE NOT GETTING COMMISSION?

SQL> SELECT ENAME, SAL DEPTNO

  2 FROM EMP

  3 WHERE COMM IS NULL;

ENAME      DEPTNO

———-       ———

SMITH            800

JONES          2975

BLAKE         2850

CLARK         2450

SCOTT          3000

KING            5000

ADAMS       1100

JAMES           950

FORD           3000

MILLER       1300

10 rows selected.

QUERY 7:-WAQ TO DISPLAY NAME, JOB,  SALARY AND HIREDATE OF EMPLOYEES WHOSE SALARY IS BETWEEN 1000 AND 2000 AND ORDER THE QUERY ASCENDING ORDER OF HIRE-DATE?

SQL> SELECT ENAME, JOB, SAL, HIREDATE FROM EMP

  2 WHERE SAL BETWEEN 1000 AND 2000

  3 ORDER BY HIREDATE;

ENAME     JOB                 SAL       HIREDATE

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

ALLEN      SALESMAN      1600    20-FEB-81

WARD       SALESMAN      1250    22-FEB-81

TURNER   SALESMAN      1500    08-SEP-81

MARTIN   SALESMAN      1250    28-SEP-81

MILLER    CLERK              1300    23-JAN-82

ADAMS    CLERK              1100     23-MAY-87

6 rows selected.

QUERY 8:-WAQ TO LIST THE DETAILS OF EMPLOYEE WHO HAVE 4 LETTERED NAME?

SQL> SELECT * FROM EMP WHERE LENGTH (ENAME) = 4;

EMPNO   ENAME   JOB                MGR      HIREDATE SAL     COMM   DEPTNO

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

     7521   WARD     SALESMAN       7698   22-FEB-81      1250       500            30

     7839   KING       PRESIDENT                  17-NOV-81     5000                         10

     7902   FORD      ANALYST          7566    03-DEC-81    3000                         20

QUERY 9:-WAQ TO DISPLAY NAME, JOB TITLE AND SALARY OF EMPLOYEE WHO DO NOT HAVE A MANAGER?

SQL> SELECT ENAME, JOB, SAL FROM EMP WHERE MGR IS NULL;

ENAME     JOB                SAL

———-     ———           ———

KING        PRESIDENT      5000

QUERY 10:-LIST THE DETAILS OF ALL THE CLERKS WHO HAVE NOT BEEN ASSIGNED DEPTNO AS YET?

SQL> SELECT * FROM EMP WHERE JOB=’CLERK’ AND DEPTNO IS NULL;

no rows selected

QUERY 11:-LIST THE DETAILS OF EMPLOYEE WHO EARN MORE COMMISSION THAN THEIR SALARIES?

SQL> SELECT * FROM EMP WHERE COMM > SAL;

EMPNO   ENAME     JOB               MGR     HIREDATE   SAL     COMM   DEPTNO

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

     7654    MARTIN   SALESMAN       7698 28-SEP-81         1250      1400          30

QUERY 12:-CALCULATE 13 * 15?

SQL> SELECT 13*15 FROM DUAL;

    13*15

———

      195

QUERY 13:-WAQ TO DISPLAY ENAME, JOB AND SALARY WHOSE JOB IS EITHER MANAGER, SALESMAN OR ANALYST (NO USE OF LOGICAL OPERATOR)?

SQL> SELECT ENAME, JOB, SAL FROM EMP

  2 WHERE JOB IN (‘MANAGER’,’SALESMAN’,’ANALYST’);

ENAME     JOB                 SAL

———-      ———            ———

ALLEN      SALESMAN       1600

WARD       SALESMAN       1250

JONES       MANAGER         2975

MARTIN    SALESMAN       1250

BLAKE      MANAGER         2850

CLARK      MANAGER        2450

SCOTT       ANALYST          3000

TURNER   SALESMAN       1500

FORD        ANALYST           3000

9 rows selected.

QUERY 14:- WAQ TO DISPLAY THE DETAILS OF ALL THE EMPLOYEES WHO ARE HIRED AFTER 1981?

SQL> SELECT * FROM EMP

  2  WHERE HIREDATE>’31-DEC-1981′;

EMPNO ENAME     JOB             MGR      HIREDATE   SAL     COMM   DEPTNO

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

     7788 SCOTT       ANALYST      7566   19-APR-87         3000                       20

     7876 ADAMS     CLERK            7788   23-MAY-87       1100                       20

     7934 MILLER     CLERK           7782   23-JAN-82         1300                        10

QUERY 15:-WAQ TO DISPLAY DETAILS OF EMPLOYEE WHO HAS ATLEAST 5 CHARACTERS IN THEIR NAMES?

SQL> SELECT * FROM EMP WHERE LENGTH (ENAME)>=5;

EMPNO ENAME   JOB                MGR       HIREDATE    SAL      COMM   DEPTNO

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

     7369 SMITH      CLERK             7902    17-DEC-80          800                         20

     7499 ALLEN      SALESMAN    7698    20-FEB-81         1600         300          30

     7566 JONES       MANAGER      7839    02-APR-81        2975                         20

     7654 MARTIN   SALESMAN     7698    28-SEP-81         1250       1400          30

     7698 BLAKE      MANAGER      7839    01-MAY-81      2850                         30

     7782 CLARK      MANAGER      7839    09-JUN-81        2450                         10

     7788 SCOTT       ANALYST       7566    19-APR-87        3000                         20

     7844 TURNER    SALESMAN    7698    08-SEP-81         1500             0          30

     7876 ADAMS     CLERK             7788    23-MAY-87      1100                          20

     7900 JAMES       CLERK             7698    03-DEC-81         950                          30

     7934 MILLER     CLERK             7782    23-JAN-82        1300                          10

11 rows selected.

QUERY 16:-WAQ TO DISPLAY ENAME JOINED WITH JOB WITH HEADING EMPLOYEE, SAL*12 AS TOTAL_SALARY FROM EMP TABLE?

SQL> SELECT ENAME||’ ‘||JOB AS Employee,

  2  SAL*12 AS TOTAL_SALARY FROM EMP;

EMPLOYEE                  TOTAL_SALARY

——————–             ————

SMITH CLERK                     9600

ALLEN SALESMAN          19200

WARD SALESMAN           15000

JONES MANAGER             35700

MARTIN SALESMAN        15000

BLAKE MANAGER            34200

CLARK MANAGER            29400

SCOTT ANALYST              36000

KING PRESIDENT              60000

TURNER SALESMAN        18000

ADAMS CLERK                  13200

JAMES CLERK                    11400

FORD ANALYST                36000

MILLER CLERK                 15600

14 rows selected.

QUERY 17:-WAQ TO CONCATINATE NAME & EMP_CODE FOR EMPLOYEES HAVING EMPLOYEE CODE 7900 AND 7788?

SQL> SELECT CONCAT (ENAME, EMPNO) FROM EMP

  2 WHERE EMPNO=7900 OR EMPNO=7788;

CONCAT (ENAME, EMPNO)

————————————————–

SCOTT7788

JAMES7900

QUERY 18:-WAQ TO DISPLAY JOB FOR EMPLOYEE 7900 IN FORMAT

              <NAME> IS A <JOB>?

SQL> SELECT ENAME||’ IS A ‘||JOB FROM EMP WHERE EMPNO=7900;

ENAME||’ISA’||JOB

————————-

JAMES IS A CLERK

QUERY 19:-WAQ TO DISPLAY NAME MR.JONES & MRS.ANE INTO LOWER CASE?

SQL> SELECT LOWER (‘MR.JONES MRS.ANE’) FROM DUAL;

LOWER (‘MR.JONESM

—————-

mr.jones mrs.ane

QUERY 20:-WAQ TO DISPLAY NAME OF EMPLOYEE 7788 IN LOWER AND UPPER CASE?

SQL> SELECT LOWER (ENAME), UPPER (ENAME) FROM EMP

  2 WHERE EMPNO=7788;

LOWER (ENAM UPPER (ENAM

———- ———-

scott      SCOTT

QUERY 21:-WAQ TO LPAD THE STRING HELLO WITH CHAR ‘*’ TOTAL LENGTH SHOULD BE 15?

SQL> SELECT LPAD (‘HELLO’, 15, ‘*’)

  2 FROM DUAL;

LPAD (‘HELLO’, 15

—————

**********HELLO

QUERY 22:-WAQ TO DISPLAY 4 CHARACTERS EXTRACTED FROM 3rd LEFT CHARACTER ONWARDS FROM STRING ‘ABCDEFG’?

SQL> SELECT SUBSTR (‘ABCDEFG’, 4, 4)

  2 FROM DUAL;

SUBS

—-

DEFG

QUERY 23:-WAQ TO DISPLAY FIRST 3 CHARACTERS EXTRACTED FROM JOB OF EMPLOYEE 7788 & 7900?

SQL> SELECT SUBSTR (JOB,1,3) FROM EMP

  2  WHERE EMPNO=7788 OR EMPNO=7900;

SUB

CLE

ANA

QUERY 24:- WAQ TO REMOVE CHARACTERS IN STRING ‘NAT’ FROM LEFT OF STRING ‘NATASHA’?

SQL> SELECT SUBSTR (‘NATASHA’,4) FROM DUAL;

SUBS

—-

ASHA

QUERY 25:- WAQ TO DISPLAY THE POSITION OF SECOND OCCURRENCE OF STRING ‘OR’ IN STRING ‘CORPORATE FLOOR’ THIRD CHARACTER ONWARDS?

SQL> SELECT INSTR(‘CORPORATE FLOOR’,’OR’,3,2) FROM DUAL;

INSTR (‘CORPORATEFLOOR’,’OR’,3,2)

——————————–

                              14

QUERY 26:- WAQ TO DISPLAY THE POSITION OF STRING ‘LE’ IN FIELD JOB OF TABLE EMP?

SQL> SELECT JOB,INSTR (JOB,’LE’,1,1) FROM EMP;

JOB             INSTR (JOB,’LE’,1,1)

———          ——————-

CLERK                             2

SALESMAN                     3

SALESMAN                     3

MANAGER                      0

SALESMAN                     3

MANAGER                      0

MANAGER                      0

ANALYST                        0

PRESIDENT                     0

SALESMAN                     3

CLERK                             2

CLERK                             2

ANALYST                       0

CLERK                             2

14 rows selected.

QUERY 27:- WAQ TO DISPLAY THE ENAME AND ENAME LENGTHS OF 7788 AND 7900?

SQL> SELECT ENAME, LENGTH (ENAME) FROM EMP

  2  WHERE EMPNO=7788 OR EMPNO=7900;

ENAME    LENGTH (ENAME)

———-     ————-

JAMES                   5

SCOTT                   5

USING OF CALUSES

  1. USING IS NULL CLAUSE :- It will be used to show only empty columns of a table. By using this clause it will display only that column which contains null value. Null is a term used to describe something that is undefined. A NULL columns means that there is no value assigned to a column. Null does not mean 0(zero) or unknown, it cannot be compared using comparison or logical operators. So null cannot be equal to Null.

Syntax:- SELECT * FROM <TABLE NAME> WHERE <COL.NAME> IS NULL;

  1. ORDER BY CLAUSE :- Oracle does not gurantee sort order for your queries unless you explicitly use an ‘order by’ clause. It will make or sort your column in ascending or descending order. For use the keyword ‘desc’ the order the rows in descending order. If we want to sort the data in ascending order than the syntax will be used as follows:-

Syntax:- SELECT <COL.NAME1>,<COL.NAME2> FROM <TABLE NAME>

ORDER BY <COLUMN NAME>;

If we want to sort the data in descending order than the syntax will be different.

Syntax:- SELECT <COL.NAME1>,<COL.NAME2> FROM <TABLE NAME>

               ORDER BY <COL.NAME> DESC;

USE OF DIFFERENT OPERATORS WITH SELECT STATEMENT

  1. USE OF LIKE OPERATOR:- Like comparison operator is the pattern matchin operator used to search through the rows of a column for values that look like a pattern you describe. It is mostly used if you are not sure of the exact spelling for that value, you are interested in finding. It was two special characters percent(%) sign known wild card and a underscore( _ ) known as position marker. A percent sign(%) in the pattern can match zero, one or more characters in the value. For example we consider.

Select ename,sal,job from emp where ename like ‘A%;

  • USE OF ‘IN’ OPERATOR:- The IN operator compares the value of a column or expression with a list of values with in a set. The IN comparison operator return a Boolean value which is either TRUE or FALSE. If expression is equal to one of the values in the set, then it returns TRUE otherwise FALSE. Let us consider the following examples.

Select ename,sal,deptno from emp where deptno in (10,20);

  • USE OF ‘NOT IN’ OPERATOR:- This operator is used a Boolean value. To retrieve the ename,sal,deptno of all employee from emp table having deptno neither 10 nor 30. Execute the query.

Select ename,sal,deptno from emp where deptno not in (10,30);

  • USE OF BETWEEN OPERATOR:- The BETWEEN operator is used to test whether the value is within the range or not. It is an inclusive operator, as it includes the value with in the range as well as boundary values. It works with numeric, string and data values. To retrieve the enam,sal and deptno of an employee from emp table whose deptno is between 10 and 30, we will write.

Select ename, sal, deptno from emp where deptno between 10 and 30;

SQL BUILT-IN FUNCTIONS

            Oracle provides an extensive set of built-in functions and operators for the conversion and manipulation of strings, dates and  numbers. Functions are similar to operators in that they manipulate data items and return a result But they differ from operators in the format in which they appear with their arguments. The number of arguments that we specified in the function may vary from zero, one, two or more arguments. The true power of these functions is realized by nesting these functions within each other.

Oracle functions are divided into two categories…….

  •   Single-Row or Scalar functions
  • Group or Aggregate functions
  • Single-Row or Scalar functions

The Single-Row function acts on each row of the table and return a single value for each row selected. It is also known as scalar functions. The Single-Row functions can appear in the <select_list> if the SELECT statement does not contain a GROUP BY clause, WHERE clause etc.

                        The Single-Row functions are categorized into the following categories.

  • Number functions
  • Character functions
  • Date functions
  • Conversion functions
  • Miscellaneous functions
  • CHARACTER FUNCTION IN SQL
  1. CONCAT(<C1,C2>) :- Where c1 and c2 are character strings. This function returns c2 appended to c1. If c1 is null, then c2 is returned. If c2 is null, then c1 is returned. If both c1 and c2 are null, then null is returned. Concat returns the same results as using the concatenation operator. C1||C2
  2. INITCAP(<C1) :- Where c1 is a character string. This function returns c1 with the first character of each word in uppercase and all other in lowercase.
  3. LENGTH(<C>) :- Where ‘c’ is a character string. This function returns the numeric length in characters of c. if c is null, a null is returned.
  4. LOWER(<C>) :- Where c is character string. This function returns the character c with all characters in lowercase. It frequently appears in WHERE clauses.
  5. UPPER(<C>) :- Where c is character string. This function returns the character c with all characters in uppercase. It frequently appears in WHERE clauses.
  6. LPAD(<C1>,<i>,<C2>) :- Where c1 and c2 are character strings and ‘i’ is an integer. This function returns the character string c1 expanded in length to ‘i’ characters using c2 to fill in space as needed on the left hand side of c1. If c1 is over ‘i’ characters, it is truncated to ‘i’ characters. C2 defaults to a single space.
  7. RPAD(<C1>,<i>,<C2>) :- Where c1 and c2 are character strings and ‘i’ is an integer. This function returns the character string c1 expanded in length to ‘i’ characters using c2 to fill in space as needed on the right hand side of c1. If c1 is over ‘i’ characters, it is truncated to ‘i’ characters. C2 defaults to a single space.
  8. RTRIM(<C1>,<C2>) :- Where c1 and c2 are character strings. This function returns c1 without any trailing characters that appear in c1. If no c2 characters are trailing characters in c1, then c1 is returned unchanged. C2 defaults to a single space.
  9. LTRIM(<C1>,<C2>) :- Where c1 and c2 are character strings. This function returns c1 without any leading characters that appear in c1. If no c2 characters are leading characters in c1, then c1 is returned unchanged. C2 defaults to a single space.
  10. SUBSTR(<C1>,<i>,<j>) :- Where c1 is a character string and both I and j are integers. This function returns the portion of c1 that is j characters long, beginning at position i. If j is negative, the position is counted backwards i.e right to left. This function returns NULL if I is 0 or negative, j defaults to 1.
  • DATE FUNCTIONS
  •  

In order to process and manipulate dates, Oracle provides a number of functions that operate on various date related data types on Oracle. The default date format in Oracle is DD-MON-YY HH:MI:SS

FUNCTION NAME PURPOSE
ADD_MONTHS Adds or subtracts a number of months to/from a date vale
LAST_DAY It returns the date of  the last day of the month containing the date parameter.
MONTHS_BETWEEN It returns number of months between two dates.
NEXT_DAY It returns a date value of the first occurrence of the day.
ROUND Rounds off a date/time value formatted to the next highest part of date.
SYSDATE Returns the current date/time.
TRUNC It returns a date value truncated to the value optionally specified in the format parameter.
  • CONVERSION FUNCTIONS

                          The conversion functions convert a value from one data type to another. These functions just return the converted value and does not makes any changes in the database. The data type conversion can be done automatically or implicitly done by the Oracle or explicitly by the user.

  • IMPLICIT DATA CONVERSION

The oracle automatiocally converts one data type into another when used in the assignments

  • To have Oracle automatically convert one data type to another the source data type must already look like the target data type it is being converted to. The following are the guidelines to convert.
  •  Any number of dates can be converted to a character string.
  • A char or varchar2 will be converted to a number if it contains only numbers, a decimal    point or minus sign on the left.
  • A char or varchar2 will be converted to date only if it is in the format DD-MON-YY.
  • A date will not be converted to a number and vice-versa.
  • EXPLICIT DATA TYPE CONVERSION FUNCTIONS
  • TO_CHAR(date conversion) :- This date function is used for transforming a DATE value into a VARCHAR2 value, optionally using a specified date format. Syntax :- TO_CHAR(date_value,[date_format])
  • TO_CHAR(number conversion) :- This number based function is used for transforming numeric value into a varchar2 value optionally using a specified number format.

Syntax :- TO_CHAR(n,[num_format])

Where ‘num_format’ is optional, it can be 9999.

  • TO_DATE :- The TO_DATE function is used for converting a character string to its date equivalent. A date format string can be provided to tell Oracle how t interpret a character string. The syntax is

TO_DATE(char_value,[for_string]

  • TO_NUMBER :- The TO_NUMBER is used to convert a character value to a number value.
  • MISCELLANEOUS FUNCTIONS
  • DECODE :- DECODE is a comparison function used for comparing a base value against upto 255 evaluation values in a single function call. Optionally a default value can be passed at the end of the function to be returned in the case where the base value does not equal any of the evaluation values. The syntax is

DECODE (base_value,eva_value1,ret_value1,

                  [eva_value2,ret_value2]…..);

Where base_value represents any column in a table regardless of data type or any result of the computation. The ‘base_value’ is tested for each row, if ‘base_value’ equals ‘eva_value1’ then the result of decode is ‘ret_value1’; if ‘base_value’ equals ‘eva_value2’ then the result of decode as ret_value2 and so on. If ‘base_value’ is not equal to any of ‘eva_value’(s) then result of DECODE is default value.

  • NVL :- The NVL function is used for evaluating an expression and returning a given value. If the expression evaluates to NULL. The syntax is

NVL(expression,sub_value);

If the expression is NULL then the NVL function returned the ‘sub_value’ and if it is NOT NULL then it returns the expression. The ‘sub_value’ can be literal, another column or an expression but must be same data type as expression.

AGGREGATE FUNCTIONS OF GROUP FUNCTIONS

The functions are used to produce summarized results. They are applied on set of rows to give you single value as a result. An aggregate function allows you to perform a data operation on several values in a column of data as though the column was one collective group of data. These functions are called group functions also. Because they are often used in a special clause of SELECT statements called a group by clause.

  1. AVG (X):- This functions returns the average of the values for the column x. this function is applied on columns having numeric datatype and it returns the numeric value. It ignores the null values in the column x.

Syntax :- avg([distinct|all]column name);

  • COUNT(X) :- This function returns the number of rows or non-null values for column x. when we use * in place of x, it returns the total number of rows in the table.

Syntax :- count([distinct|all]column name);

  • SUM(X) :- This function returns the sum of values for the column x. This function is applied on columns having numeric datatype and it returns the numeric value.

Syntax :-sum([distinct|all]column name);

  • MIN(X) :- This function returns the minimum of values for the column x for all the rows. This function can be applied on any datatype.

Syntax :- min([distinct|all]column name);

  • MAX(X) :- This function returns the maximum of values for the column x for all the rows. This function can be applied on any data type.

Syntax :- max([distinct|all]column name);

            GROUP DATA WITH GROUP BY

               GROUP BY clause is used to group or categorize the data. In other words it divide rows in a table into smaller groups. We can then use the group functions to return summary information for each group.

               If no GROUP BY clause is specified, then the default grouping is the entire result set. When the query executes and the data is fetched, it is grouped based on the GROUP BY clause and the group function is applied.

Syntax:- SELECT column,group_function(column) from table

[WHERE condition]

[GROUP BY group_by_expression]

[ORDER BY column];

Here, group_by_expression specifies columns whose values determine the basis for grouping rows.

HAVING CLAUSE

                        In addition to the GROUP BY clause, the HAVING clause can be used in conjunction with it to further restricts the retrieved rows. The HAVING clause works very much like a WHERE clause except that its logic is only related to result of group functions as opposed to columns or expressions for individual rows, which can still be selected by a WHERE clause.

QUERY 28:- WAQ TO DISPLAY NAME, JOB WHERE JOB IS NOT EITHER CLERK NOR ANALYST.

SQL> SELECT ENAME,JOB FROM EMP

  2  WHERE JOB NOT IN(‘CLERK’,’ANALYST’);

ENAME      JOB

———-       ———

ALLEN       SALESMAN

WARD        SALESMAN

JONES        MANAGER

MARTIN    SALESMAN

BLAKE      MANAGER

CLARK      MANAGER

KING          PRESIDENT

TURNER    SALESMAN

8 rows selected.

QUERY 29:- WAQ TO DISPLAY ENAME, EMPNO, And MGR FROM EMP WHERE EMPNO RANGES FROM 7800 TO 7900.

SQL> SELECT ENAME, EMPNO, MGR FROM EMP

  2  WHERE EMPNO BETWEEN 7800 AND 7900;

ENAME      EMPNO    MGR

———-       ———      ———

KING              7839

TURNER        7844          7698

ADAMS         7876           7788

JAMES           7900           7698

QUERY 30:- WAQ TO DISPLAY NAME AND DEPARTMENT NO. OF THOSE EMPLOYEE WHOSE NAME 2ND CHARACTER IS EITHER ‘L’ OR ‘A’.

SQL> SELECT ENAME, DEPTNO FROM EMP

  2  WHERE ENAME LIKE ‘_L%’ OR

  3  ENAME LIKE ‘_A%’;

ENAME    DEPTNO

———-      ———

ALLEN              30

WARD               30

MARTIN           30

BLAKE              30

CLARK              10

JAMES               30

6 rows selected.

QUERY 31:- WAQ TO RETRIEVE EMPNO, ENAME, and DEPTNO FOR ‘ALLEN’, ‘BLAKE’, ‘WARD’, ‘CLARK’, ‘JAMES’.

SQL> SELECT ENAME, DEPTNO FROM EMP

  2  WHERE ENAME IN

  3  (‘ALLEN’,’BLAKE’,’WARD’,’CLARK’,’JAMES’);

ENAME         DEPTNO

———-           ———

ALLEN                   30

WARD                    30

BLAKE                   30

CLARK                   10

JAMES                    30

QUERY 32:- CALCULATE TOTAL COMMISSION AND TOTAL SALARY FOR EACH DEPARTMENT AND DISPLAY THOSE WHO ARE HAVING COMM GREATER THAN 750 AND SALARAY GREATER THAN 2500.

SQL> SELECT SUM (COMM) TOTAL_COMM,

  2  SUM (SAL)TOTAL_SAL FROM EMP

  3  GROUP BY DEPTNO

  4  HAVING SUM(COMM)>750 AND SUM(SAL)>2500;

TOTAL_COMM   TOTAL_SAL

———-                   ———

  •                  9400

QUERY 33:- DISPLAY HIREDATE OF EMPLOYEE 7369 IN THE FORMAT

                        <JANUARY 22,2007>

SQL> SELECT TO_CHAR (HIREDATE,’MONTH DD,YYYY’) HIREDATE

  2  FROM EMP

  3  WHERE EMPNO=7369;

HIREDATE

—————–

DECEMBER  17,1980

QUERY 34:- DISPLAY HIREDATE OF EMPLOYEE 7369 IN THE FORMAT

                        <jan.22,07>

SQL> SELECT TO_CHAR (HIREDATE,’mon.dd,yy’)HIREDATE

  2  FROM EMP

  3  WHERE EMPNO=7369;

HIREDATE

———

dec.17,80

QUERY 35:- DISPLAY HIREDATE OF EMPLOYEE 7369 IN THE FORMAT             <WED-JAN.22,07>

SQL> SELECT TO_CHAR (HIREDATE,’DY-MON.DD,YY’)

  2  HIREDATE FROM EMP

  3  WHERE EMPNO=7369;

HIREDATE

————-

WED-DEC.17,80

QUERY 36:- DISPLAY HIREDATE OF EMPLOYEE 7369 IN THE FORMAT

                        <Wednesday jan 22nd,07,9:45:00 am>

SQL> SELECT TO_CHAR(HIREDATE,’Day mon ddth,yy,hh:mi:ss am’)

  2  hiredate from emp

  3  where empno=7369;

HIREDATE

———————————

Wednesday dec 17th,80,12:00:00 am

QUERY 37:- WAQ TO DISPLAY NAME, JOB, SAL, HIREDATE OF EMP WHO ARE HIRED BETWEEN FEB 20 1981 AND MAY 1 1981 ORDER IN ASCENDING ORDER OF HIREDATE.

SQL> SELECT ENAME, JOB, SAL, HIREDATE

  2  FROM EMP

  3  WHERE HIREDATE BETWEEN

  4  ’20-FEB-1981′ AND ‘1-MAY-1981’

  5  ORDER BY HIREDATE;

ENAME      JOB                 SAL       HIREDATE

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

ALLEN      SALESMAN       1600    20-FEB-81

WARD       SALESMAN       1250    22-FEB-81

JONES      MANAGER        2975     02-APR-81

BLAKE      MANAGER        2850    01-MAY-81

QUERY 38:-PRINT ‘NOT APPLICABLE’ IN PLACE OF NULL COMM FOR EMPLOYEE OF DEPARTMENT 30.

SQL> SELECT ENAME, DEPTNO,

  2  NVL (TO_CHAR(COMM),’NOT APPLICABLE’)

  3  FROM EMP

 4  WHERE DEPTNO=30 AND COMM IS NULL;

ENAME       DEPTNO    NVL(TO_CHAR(COMM),’NOTAPPLICABLE’)

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

BLAKE               30         NOT APPLICABLE

JAMES                30         NOT APPLICABLE

QUERY 39:-CALCULATE NEWSAL FOR EMPLOYEE ‘BLAKE’ BY ADDING 100.00 TO IT.

SQL> SELECT ENAME, JOB, SAL,

  2  DECODE (ENAME,’BLAKE’,SAL+100.00)NEWSAL

  3  FROM EMP

  4  WHERE ENAME = ‘BLAKE’;

ENAME      JOB             SAL          NEWSAL

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

BLAKE      MANAGER      2850         2950

QUERY 40:- DECODE THE VALUE OF DEPTNO FIELD IN THE TABLE EMPLOYEE AS PER THE FOLLOWING SPECIFICATION…..

IF DEPTNO 10 THE EXPRESSION EVALUATES TO ‘ACCOUNTING’ IF IT IS 20 IT IS ‘RESEARCH’ AND SO ON. IF DEPTNO NOT 10, 20, 30 AND 40. THE EXPRESSION RETURNS ‘NONE’.

SQL> SELECT ENAME, DEPTNO,

  2  DECODE (DEPTNO,10,’ACCOUNTING’,20,’RESEARCH’,

  3  30,’SALES’,40,’OPERATION’,’NONE’)D_NAME

  4  FROM EMP;

ENAME    DEPTNO   D_NAME

———-     ———       ———-

SMITH              20        RESEARCHES

ALLEN             30        SALES

WARD              30        SALES

JONES              20        RESEARCHES

MARTIN          30        SALES

BLAKE             30        SALES

CLARK             10       ACCOUNTING

SCOTT              20        RESEARCH

KING                10        ACCOUNTING

TURNER          30       SALES

ADAMS           20        RESEARCH

JAMES             30        SALES

FORD               20        RESEARCHES

MILLER          10         ACCOUNTING

14 rows selected.

QUERY 41:- FIND OUT WHEN ‘SMITH’ WILL COMPLETE ONE MONTH IN THE COMPANY.

SQL> SELECT EMPNO, ENAME, JOB, HIREDATE,

  2  ADD_MONTHS(HIREDATE,1) FROM EMP

  3  WHERE ENAME= ‘SMITH’;

EMPNO   ENAME     JOB       HIREDATE   ADD_MONTH

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

     7369    SMITH      CLERK   17-DEC-80    17-JAN-81

QUERY 42:- FIND OUT THE DATE AFTER TWO MONTHS OF THE DATE 30-DEC-81.

SQL> SELECT ADD_MONTHS (’30-DEC-81′,2)

  2  FROM DUAL;

ADD_MONTH

———

28-FEB-82

QUERY 43:-FIND OUT WHAT WAS THE LAST DAY OF THE MONTH OF DATE            

18-FEB-2000.

SQL> SELECT TO_CHAR (LAST_DAY(’18-FEB-2000′),’DD-MON-YYYY’)

  2  FROM DUAL;

TO_CHAR (LA

———–

29-FEB-2000

QUERY 44:-FIND OUT WHAT WILL BE THE LAST DAY OF THIS MONTH AND HOW MANY DAYS ARE LEFT TO REACH THE END OF THE MONTH.

SQL> SELECT LAST_DAY (SYSDATE),

  2  LAST_DAY(SYSDATE)-SYSDATE

  3  FROM DUAL;

LAST_DAY    ( LAST_DAY(SYSDATE)-SYSDATE

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

31-AUG-07                                 12

QUERY 45:- FIND OUT WHEN EMPLOYEE ‘MARTIN’ WILL COMPLETE 5 MONTHS IN THE COMPANY.

SQL> SELECT ENAME, JOB, HIREDATE,

  2  ADD_MONTHS(HIREDATE,5)

  3  FROM EMP

  4  WHERE ENAME=’MARTIN’;

ENAME     JOB                HIREDATE    ADD_MONTH

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

MARTIN   SALESMAN   28-SEP-81      28-FEB-82

QUERY 46:- FIND OUT HOW MANY MONTHS HAS PASSED SINCE 02-DEC-2002 TILL 22-JAN-2003.

SQL> SELECT MONTHS_BETWEEN

  2  (’02-DEC-2002′,’22-JAN-2003′)

  3  FROM DUAL;

MONTHS_BETWEEN (’02-DEC-2002′,’22-JAN-2003′)

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

                                  -1.645161

QUERY 47:-FIND OUT THE DATE OF NEXT ‘TUESDAY’ AFTER ’15-MAY-2003’.

SQL> SELECT NEXT_DAY (’15-MAY-2003′,’TUESDAY’)

  2  FROM DUAL;

NEXT_DAY

———

20-MAY-03

QUERY 48:-ROUND OF THE CURRENT DATE TO

            I.) NEAREST YEAR

SQL> SELECT ROUND (SYSDATE,’YEAR’) FROM DUAL;

ROUND (S

———

01-JAN-08

            II.) NEAREST MONTH

SQL> SELECT ROUND (SYSDATE,’MONTH’) FROM DUAL;

ROUND (SYS

———

01-SEP-07

            III.) TRUNCATE THE CURRENT DATE TO NEAREST YEAR.

SQL> SELECT TRUNC(SYSDATE,’YEAR’) FROM DUAL;

TRUNC(SYS

———

01-JAN-07

QUERY 49 :- WAQ TO DISPLAY THE JOBS WHERE THE NUMBER OF EMPLOYEES IS LESS THAN THREE FOR EACH JOB.

SQL> SELECT JOB, COUNT (JOB)

  2  FROM EMP

  3  GROUP BY JOB

  4  HAVING COUNT(JOB)<3;

JOB            COUNT (JOB)

———         ———-

ANALYST            2

PRESIDENT          1

QUERY 50:- CALCULATE THE AVG SALARY AND AVG COMMISSION FOR EACH DEPARTMENT AND DISPLAY THOSE WHO HAVE AVG COMMISSION GREATER THAN 750 AND AVG SALARY GREATER THAT 2500.

SQL> SELECT DEPTNO, AVG(NVL(COMM,0)),AVG(SAL) FROM EMP

  2  GROUP BY DEPTNO

  3  HAVING AVG(NVL(COMM,0))>750 OR

  4  AVG(SAL)>2500;

DEPTNO        AVG(NVL(COMM,0))    AVG(SAL)

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

  1.                       0                     2916.6667

QUERY 51:- WAQ TO DISPLAY TH NUMBER OF EMPLOYEES WITH SAME JOB.

SQL> SELECT JOB, COUNT(JOB)

  2  FROM EMP

  3  GROUP BY JOB;

JOB                  COUNT(JOB)

———              ———-

ANALYST                  2

CLERK                        4

MANAGER                3

PRESIDENT               1

SALESMAN               4

QUERY 52:- WAQ TO DISPLAY THE DIFFERENCE OF HIGHEST AND LOWEST SAL OF EACH DEPARTMENT HAVING MAX SAL GREATER THAN 4000.

SQL> SELECT DEPTNO, MAX(SAL)-MIN(SAL)

  2  FROM EMP

  3  GROUP BY DEPTNO

  4  HAVING MAX(SAL)>4000;

   DEPTNO          MAX(SAL)-MIN(SAL)

———                 —————–

       10                                 3700

JOINS

               A join  is mechanism that allows tables to be related to one another. The rows retrieved after joining two tables are based on the condition that a column in the first table which is defined as a foreign key must match to the column in second table which is defined as a primary key referenced by the foreign key. For example : In DEPT table, the DEPTNO column act as a primary key and in the EMP table DEPTNO acts as a foreign key referencing to primary key of DEPT table. So these two tables i.e. DEPT and EMP can be joined on the common column DEPTNO to retrieve information from both the related tables. The general syntax for the SELECT statement that enables you to join more than two tables is

            SELECT <select_list>

            FROM <table1>,<table2>,…..,<table N>

            WHERE table1.column1=table2.column2 and

            …………………

            Additional conditions

The various types of JOINS are

  • EQUI-JOIN
  • CARTESION JOIN
  • OUTER JOIN
  • SELF JOIN
  • EQUI-JOIN :- The EQUIJOIN or INNER JOIN is a join in which join condion contains an equality operator(=). It combine rows that have equivalent values for the specified columns.
  • CARTESIAN PRODUCT :- If the join condition is omitted from the join query, then the result is a Cartesian Product. In the Cartesian product each row of one table is joined to every row of another table.
  • OUTER JOIN :- A OUTER join simply extends the results of an INNER JOIN. While using the EQUIJOIN we have seen that if there exists certain records in one table which do not have corresponding values in the second, then those rows will not be selected. We can forcefully select such rows by using OUTER JOIN. The results of an OUTER JOIN will be all those rows that satisfy the join condition, along with rows from one tale that do not have corresponding rows in the other table to satisfy join condition. The OUTERJOIN is accomplished with a PLUS (+) operator within parantheses.
  • SELF JOIN :- Another form of join is the SELF JOIN which is a join of table to itself. For SELFJOIN we have the need to open two copies of the same table. Since the table names are same so to avoid confusion we use aliasing that qualify the column names in the join condition. This type of join is used when a table has a foreign key that references its own primary key.

QUERY 53 :- DISPLAY DETAILS LIKE DEPTNO, DNAME, ENAME, EMPNO, JOB AND SALARY ORDER THE ROWS BY EMPNO WITH DEPTNO.

SQL> SELECT D.DEPTNO,D.DNAME,E.ENAME,E.EMPNO,E.JOB,E.SAL

  2  FROM DEPT D,EMP E

  3  WHERE D.DEPTNO=E.DEPTNO

  4  ORDER BY EMPNO,DEPTNO;

DEPTNO    DNAME              ENAME       EMPNO   JOB               SAL

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

       20         RESEARCH       SMITH             7369   CLERK                 800

       30         SALES                ALLEN            7499 SALESMAN       1600

       30         SALES               WARD              7521 SALESMAN       1250

       20         RESEARCH       JONES             7566 MANAGER        2975

       30         SALES                MARTIN         7654   SALESMAN       1250

       30         SALES                BLAKE           7698   MANAGER        2850

       10         ACCOUNTING  CLARK           7782 MANAGER        2450

       20         RESEARCH        SCOTT           7788   ANALYST          3000

       10         ACCOUNTING  KING              7839 PRESIDENT       5000

       30         SALES                TURNER        7844   SALESMAN       1500

       20         RESEARCH       ADAMS          7876   CLERK                1100

       30         SALES                JAMES           7900    CLERK                  950

       20         RESEARCH       FORD              7902   ANALYST           3000

       10         ACCOUNTING  MILLER         7934   CLERK                 1300

QUERY 54 :- DISPLAY DETAILS LIKE DEPTNO, DNAME, ENAME, JOB FOR EMPLOYEES OF SALES DEPARTMENT.

SQL> SELECT D.DEPTNO,D.DNAME,E.ENAME,E.JOB

  2  FROM EMP E,DEPT D

  3  WHERE D.DEPTNO=E.DEPTNO AND D.DNAME=’SALES’;

DEPTNO      DNAME       ENAME     JOB

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

       30             SALES          ALLEN       SALESMAN

       30             SALES          WARD        SALESMAN

       30             SALES         MARTIN    SALESMAN

       30             SALES          BLAKE       MANAGER

       30             SALES          TURNER    SALESMAN

       30             SALES          JAMES       CLERK

6 rows selected.

QUERY 55 :- DISPLAY DETAILS LIKE DEPTNO, DNAME, ENAME, EMPNO, JOB AND SALARY ORDER BY EMPNO WITH DEPTNO THESE DETAILS ONLY FOR EMPLOYEES EARNING ATLEAST RS. 1500 AND OF SALES DEPARTMENT.

SQL> SELECT D.DEPTNO,D.DNAME,

  2  E.ENAME,E.EMPNO,E.JOB,E.SAL

  3  FROM DEPT D,EMP E

  4  WHERE D.DEPTNO=E.DEPTNO

  5  AND D.DNAME=’SALES’ AND E.SAL>=1500

  6  ORDER BY EMPNO,DEPTNO;

DEPTNO        DNAME       ENAME      EMPNO   JOB               SAL

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

       30             SALES          ALLEN           7499     SALESMAN      1600

       30             SALES          BLAKE           7698    MANAGER        2850

       30             SALES          TURNER        7844    SALESMAN       1500

QUERY 56 :- DISPLAY THE EMPLOYEE DETAILS OF THE FOLLOWING FORMAT FROM 3 TABLES EMP,DEPT,SALGRADE.

                        <ENAME       DNAME         JOB     GRADE>

SQL> SELECT E.ENAME,D.DNAME,E.JOB,S.GRADE

  2  FROM EMP E,DEPT D,SALGRADE S

  3  WHERE E.DEPTNO=D.DEPTNO

  4  AND E.SAL BETWEEN S.LOSAL AND S.HISAL;

ENAME          DNAME              JOB             GRADE

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

SMITH            RESEARCH       CLERK                   1

ADAMS         RESEARCH       CLERK                   1

JAMES           SALES                CLERK                   1

WARD            SALES                SALESMAN          2

MARTIN        SALES                SALESMAN          2

MILLER         ACCOUNTING  CLERK                  2

ALLEN           SALES                SALESMAN          3

TURNER        SALES                SALESMAN          3

JONES             RESEARCH       MANAGER          4

BLAKE          SALES                 MANAGER          4

CLARK          ACCOUNTING   MANAGER          4

SCOTT            RESEARCH        ANALYST            4

FORD             RESEARCH        ANALYST            4

KING              ACCOUNTING   PRESIDENT         5

14 rows selected.

QUERY 57 :- DISPLAY EMPLOYEE DETAILS FOR ANALYST IN THE FOLLOWING FORMAT

                        <ENAME       JOB     SAL    GRADE>

SQL> SELECT E.ENAME,E.JOB,E.SAL,S.GRADE

  2  FROM EMP E,SALGRADE S

  3  WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL

  4  AND E.JOB=’ANALYST’;

ENAME          JOB                 SAL        GRADE

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

SCOTT            ANALYST        3000              4

FORD             ANALYST        3000              4

QUERY 58 :- DISPLAY ENAME,JOB,DEPTNO AND DNAME . ALL THE DNAME SHOULD BE DISPLAYED WHETHER THERE HAVE EMPLOYEES OR NOT.

SQL> SELECT E.ENAME,E.JOB,E.DEPTNO,D.DNAME

  2  FROM EMP E,DEPT D

  3  WHERE E.DEPTNO(+)=D.DEPTNO;

ENAME          JOB                 DEPTNO        DNAME

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

CLARK           MANAGER          10            ACCOUNTING

KING              PRESIDENT          10            ACCOUNTING

MILLER         CLERK                  10             ACCOUNTING

SMITH            CLERK                  20             RESEARCH

ADAMS         CLERK                  20             RESEARCH

FORD             ANALYST            20                         RESEARCH

SCOTT            ANALYST            20                         RESEARCH

JONES             MANAGER         20             RESEARCH

ALLEN            SALESMAN        30             SALES

BLAKE          MANAGER          30             SALES

MARTIN        SALESMAN         30             SALES

JAMES           CLERK                 30              SALES

TURNER        SALESMAN         30                         SALES

WARD            SALESMAN         30                         SALES

                                                                        OPERATIONS

15 rows selected.

QUERY 59 :- WAQ TO DISPLAY ENAME, DNAME AND LOCATION OF ALL EMPLOYEES WHO HAVE MANGAER NO. BETWEEN 7500 AND 7900.

SQL> SELECT E.ENAME,D.DNAME,D.LOC

  2  FROM EMP E,DEPT D

  3  WHERE E.DEPTNO=D.DEPTNO

  4  AND MGR BETWEEN 7500 AND 7900;

ENAME        DNAME              LOC

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

ALLEN        SALES                 CHICAGO

WARD         SALES                 CHICAGO

JONES         RESEARCH         DALLAS

MARTIN     SALES                  CHICAGO

BLAKE       SALES                  CHICAGO

CLARK      ACCOUNTING    NEW YORK

SCOTT       RESEARCH         DALLAS

TURNER    SALES                 CHICAGO

ADAMS     RESEARCH         DALLAS

JAMES       SALES                 CHICAGO

FORD         RESEARCH         DALLAS

MILLER     ACCOUNTING   NEW YORK

12 rows selected.

QUERY 60 :- WAQ TO DISPLAY EMPNO, ENAME AND MANAGER NAME WITH THEIR MANAGER NUMBER.

SQL> SELECT E.EMPNO,E.ENAME,E.MGR,M.ENAME

  2  FROM EMP E, EMP M

  3  WHERE E.MGR=M.EMPNO;

EMPNO     ENAME    MGR          ENAME

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

     7369     SMITH           7902      FORD

     7499    ALLEN           7698      BLAKE

     7521    WARD            7698      BLAKE

     7566    JONES            7839      KING

     7654    MARTIN        7698      BLAKE

     7698    BLAKE          7839      KING

     7782    CLARK          7839      KING

     7788    SCOTT           7566      JONES

     7844    TURNER        7698     BLAKE

     7876    ADAMS          7788    SCOTT

     7900    JAMES           7698     BLAKE

     7902    FORD             7566     JONES

     7934    MILLER         7782    CLARK

13 rows selected.

NESTED QUERIES

So far we have only concentrated on simple comparison conditions in a WHERE clause of a SELECT statement i.e. we have compared a column with a constant or we have compared two columns. As we have already seen for the INSERT statement queries can be used for assignments to columns. A query result can also be used in a condition of a WHERE clause. In such a case a query is called a subquery and complete SELECT statement is called a nested query.

THE FOLLOWING ARE THE TYPES OF SUB QUERIES

  • SINGLE ROW SUBQUERIES
  • MULTIPLE ROW SUBQUERIES
  • MULTIPLE COLUMN SUBQUERIES
  • SINGLE ROW SUBQUERIES :- A single row subquery returns only a single row from the inner nested query. These types of subqueries uses single row operators (>,<>,<=,>=,<,=). Suppose a user wishes to retrieve the ENAME,JOB,SAL of the employee’s whose salaries re less than that of an employee whose EMPNO=7521 from the EMP table.                                       The above data can be retrieved on running two queries. Using the first query we will retrieve the salary of employee whose EMPNO=7521.

SQL> SELECT ENAME,JOB,SAL FROM EMP

           WHERE SAL<(SELECT SAL FROM EMP WHERE EMPNO=7521);

  • MULTIPLE ROW SUBQUERIES:- The subqueries that return more than one row are called Multiple row subqueries. The following operators are used in the Multiple row subqueries.
  • IN operator
  • ANY operator
  • ALL operator

IN operator :- The IN operator compares a value with every value returned from the subquery. The IN operator is equivalent to (=ANY). You can also use NOT IN operator in subqueries which is equivalent to !=ALL and it equivalent to FALSE if any member of the set is NULL.

ANY operator :- The ANY operator compares a value to each value returned by a subquery. It evaluates to TRUE if there exists atleast one row selected by the subquery for which the comparison holds. It evaluates to FALSE if the query returns no rows. It must be preceded by =,!=,>,<,>= and <=. Its synonym is SOME operator.

ALL operator :- The all comparison operator compares a value to every value returned by a query. It evaluates to TRUE if for all rows selected by the subquery the comparison holds. Also, the condition evaluates to TRUE if subquery doesn’t yield any row or the value. All operator must be proceeded by =,!=,>,<,>=,<=.

  • MULTIPLE-COLUMN SUBQUERIES :- The types of subqueries discussed so far (i.e. single-row and multiple-row) compares only one column in the WHERE clause or HAVING clause of SELECT statement. So, if multiple columns i.e. two or more columns need to be compared, then we use compound WHERE clause using logical operators.                                   The multiple-column subqueries returns more than one column from the subquery. Thus enabling users to combine duplicate WHERE conditions into single WHERE clause.
  • CORRELATED QUERIES :-An important class of subqueries are those in which the subqueries are those in which the subquery reference a column from a table referred to in the parent statement. A subquery is evaluated once for each row processed by the parent statement. Such types of queries are called correlated subquery.
  • EXISTS OPERATOR :- The EXISTS operator is useful in the situations in which you are not interested in the column values returned by the subqueries. The operator returns TRUE if subquery returns at least one row and false if no rows are returned by the subquery.

RELATED QUERIES

QUERY 61:- WRITE A QUERY TO DISPLAY POSITIONS WHICH ARE PAID HIGHER THAN AVERAGE SALARY.

SQL> select distinct job  from emp

  2  where sal>(select avg(sal) from emp);

JOB

————-

ANALYST

MANAGER

PRESIDENT

QUERY 62:- WRITE A QUERY TO FIND OUT WHO WORKS IN SAME DEPARTMENT AS THAT OF EMPLOYEE SCOTT.

SQL> select empno, ename   from emp

  2  where deptno=(select deptno from emp where ename = ‘SCOTT’);

    EMPNO   ENAME

    ———     ———-

        7369    SMITH

        7566    JONES

        7788    SCOTT

        7876    ADAMS

        7902    FORD

QUERY 63:- WRITE A QUERY TO FIND OUT WHOI EARN THE LEAST.

SQL> select empno, ename   from emp

  2   where sal=(select min(sal) from emp);

 EMPNO   ENAME

 ———     ———-

     7369     SMITH

QUERY 64:- WRITE A QUERY TO DISPLAY THE DETAILS OF THOSE EMPLOYEES THAT HAVE SALARY EQUAL TO ANY EMPLOYEE OF DEPARTMENT NUMBER 30.

SQL>  select *  from emp

  2   where sal =any  (select sal from emp where deptno=30);

    EMPNO   ENAME       JOB               MGR     HIREDATE     SAL    COMM   DEPTNO

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

        7900     JAMES       CLERK              7698     03-DEC-81      950                        30

        7521     WARD        SALESMAN     7698    22-FEB-81      1250       500           30

        7654     MARTIN    SALESMAN     7698     28-SEP-81      1250      1400          30

        7844     TURNER    SALESMAN     7698     08-SEP-81      1500         0             30

        7499     ALLEN       SALESMAN     7698     20-FEB-81      1600       300          30

        7698     BLAKE      MANAGER       7839     01-MAY-81    2850                       30

QUERY65:- WRITE A QUERY TO DISPLAY DETAILS OF THOSE EMPLOYEE THAT HAVE SALARY MORE THAN OR EQUAL TO AVERAGE SALARIES OF ALL JOBS TYPES.

SQL> select * from emp where

  2  sal >=any (select avg(sal) from emp group by job);

    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

     7566     JONES      MANAGER       7839     02-APR-81      2975                  20

     7654     MARTIN   SALESMAN      7698     28-SEP-81      1250     1400      30

     7698     BLAKE     MANAGER       7839     01-MAY-81      2850                30

     7782    CLARK      MANAGER       7839     09-JUN-81      2450                  10

     7788    SCOTT       ANALYST         7566    19-APR-87      3000                  20

     7839    KING         PRESIDENT                   17-NOV-81      5000                10

     7844    TURNER    SALESMAN      7698     08-SEP-81      1500         0       30

     7876    ADAMS     CLERK               7788     23-MAY-87      1100                20

     7902    FORD         ANALYST         7566     03-DEC-81      3000                  20

     7934    MILLER     CLERK              7782     23-JAN-82      1300                  10

12 rows selected.

QUERY 66:- WRITE A QUERY TO DISPLAY DETAILS OF THOSE DEPARTMENTS THAT HAVE ATLEAST ONE EMPLOYEE.

SQL> select * from dept where

  2  exists (select * from emp where emp.deptno=dept.deptno );

   DEPTNO DNAME               LOC

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

    10          ACCOUNTING     NEW YORK

    20          RESEARCH           DALLAS

    30          SALES                    CHICAGO

QUERY 67:- WRITE A QUERY TO DISPLAY DETAILS OF THOSE DEPARTMENTS THAT DO NOT HAVE ATLEAST ONE EMPLOYEE.

SQL> select * from dept where

  2  not exists (select * from emp where emp.deptno=dept.deptno );

   DEPTNO  DNAME               LOC

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

   40             OPERATIONS     BOSTON

QUERY 68:- WRITE A QUERY TO DISPLAY THOSE EMPLOYEES WHO EARN MORE THAN ANY I.E ATLEAST ONE EMPLOYEE IN DEPARTMENT NUMBER 30.

SQL> select * from emp where

  2  sal > any (select sal 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

7566     JONES       MANAGER        7839     02-APR-81      2975                      20

7654     MARTIN   SALESMAN       7698     28-SEP-81      1250      1400         30

7698     BLAKE     MANAGER         7839     01-MAY-81    2850                       30

7782     CLARK     MANAGER         7839  09-JUN-81      2450                       10

7788     SCOTT      ANALYST          7566    19-APR-87       3000                       20

7839     KING         PRESIDENT                  17-NOV-81      5000                       10

7844     TURNER   SALESMAN       7698    08-SEP-81       1500         0            30

7876     ADAMS     CLERK               7788   23-MAY-87     1100                        20

7902     FORD         ANALYST          7566 03-DEC-81      3000                        20

7934    MILLER     CLERK                7782  23-JAN-82      1300                        10

12 rows selected.

QUERY 69:- WRITE A QUERY TO DISPLAY EMPLOYEE NAME AND HIRE DATE OF EMPLOYEES WHO ARE EMPLOYED AFTER EMPLOYEE BLAKE.

SQL> select ename, hiredate 

   2 from emp

   3 where hiredate>(select hiredate from emp where ename=’BLAKE’);

ENAME      HIREDATE

———-        ———

MARTIN     28-SEP-81

CLARK       09-JUN-81

SCOTT        19-APR-87

KING          17-NOV-81

TURNER     08-SEP-81

ADAMS      23-MAY-87

JAMES        03-DEC-81

FORD          03-DEC-81

MILLER     23-JAN-82

VIEWS :- A view is a query of one or more tables that provides another way of presenting information. In layman terms, a view is a stored query. It can be used just as if it was a table. But the main difference is that it doesn’t actually contain or store data. You can select from it, join to it and you can also perform insert, update and delete from it in some cases. So a view can be also be considered as a virtual table. Views do not actually store data rather they derive their data from the tables on which they are based, referred to as the base tables of the views. Base tables themselves may be tables or views. All operations performed on view actually affect the base tables of the view.

The syntax for creating a view is

CREATE|OR REPLACE|VIEW<viewname>

AS <SELECT statement>

Where <viewname> is the name of  VIEW and REPLACE option recreates a view if it already exists.

Indexes :- An Index is an Oracle object that contain the values that exist in one or more columns in a table. An index is used by DBMS engine to find a row in a table quickly. The index consists of a key value i.e. a column in a Row and the rowed. The DBMS engine uses ROWID to find exactly where the row is physically located in the database.

The Syntax for creating an index is

CREATE|UNIQUE|INDEX< index_name> ON <table_name>(col_1,……col_n)

Where <index_name> is the name to be given to index,<table_name is the table on which the index is created and (col_1,…….col_n) are the columns to be used in creating index.

TYPES OF INDEXES

Oracle provides two types of table indexes.

  • Unique Index: They guarantee that no two rows of a table have duplicate values in the columns that define the index. i.e. it enforce primary key and unique constraints.
  • Non Unique Index: It does not restrict the duplicate column values from being stored in the table. It can improve query performance.

CREATION OF UNIQUE INDEX

The Unique Index does not allows duplicate values for the Indexed columns. So to create a Unique index on Ename column of the Emp table we write

SQL> CREATE UNIQUE INDEX ENAM_UN_INDEX ON EMP(ENAME);

COMPOSITE INDEXES

Whenever index is created on multiple columns in a table, then the index created is known as composit index column in the composit index can appear in any order and need not be adjacent in the table.

            So to create a composit index on the ENAME and SAL columns of the EMP table we write.

SQL> CREATE INDEX COM_EMP_INDEX ON

            EMP (ENAME,SAL);

DROPPING EXISTING INDEXES

When index is no longer required or not providing anticipated performance improvements for queries issued against the associate table then you might remove the index using DROP INDEX command. The syntax for dropping an index is

DROP INDEX <index_name>;

SEQUENCES :- A sequence is a special database object that is used to generate unique integer values. The sequences are generally used to automatically generate primary key values. They can also be use to generate random numbers. They are most commonly accessed by INSERT and UPDATE statement and less commonly by SELECT statement. The syntax for creating a sequence is

CREATE SEQUENCE <sequence_name>

[INCREMENT BY <integer_value>]

[START WITH <integer_value>]

[MINVALUE <integer_value>/NOMINVALUE]

[MAXVALUE <integer_value>/NOMAXVALUE]

[CYCLE/NOCYCLE]

[CACHE<integer_value>/NOCACHE]

[ORDER/NOORDER]

  • INCREMENT BY : It specifies interval between the sequence numbers which are integer values which can be any positive or negative integer for the ascending/descending sequences but cannot be zero. The default value is 1.
  • START WITH : It is number with which the sequence will begin. The default START WITH is MAXVALUE for descending sequence and MINVALUE for ascending sequence.
  • MINVALUE : It is the minimum value that the sequence will generate. The value specified in MINVALUE must be greater than or equal to START WITH and must be less than MAXVALUE. NOMINVALUE is the default. Which is equal to 1 for ascending sequence and -1026 for descending sequence.
  • MAXVALUE : It specifies the highest number that sequence can generate NOMAXVALUE is the default value which is 1027 for ascending sequence and -1 for descending sequence.
  • CYCLE : It indicates that the sequence continue to generate values after reaching its maximum or minimum value. NOCYCLE is the default which indicates that it cannot generate values after reaching its max or min values.
  • CACHE : It specifies how many value of the sequence Oracle preallocates and keeps in memory for faster access. The minimum value must be 2. The default value is 20 numbers. NOCACHE specifies that values of sequence are not preallocated.
  • ORDER : It guarantees that sequence number will be assigned to instances requesting them in the order in which requests are received. NOORDER is the default. It specifies that sequence numbers are not generated in order of request.
  • CURRVAL : It returns the current value of the sequence. To use it qualify the CURRVAL with the name of the sequence. For example, EMP_ID.CURRVAL
  • NEXTVAL : It increments the sequence and returns that next value. To use it qualify NEXTVAL with the name of the sequence. For example, EMP_ID.NEXTVAL.

ALTER SEQUENCE

Like tables you can also modify a sequence, it is possible to alter the MINVALUE, MAXVALUE, INCREMENT BY, CYCLE, NOCYCLE, ORDER parameters for a sequence. These changes take effect immediately. This command will not effect the sequence number created earlier. Only upcoming sequence numbers are affected. Its syntax is :

ALTER SEQUENCE <sequence_name>

[INCREMENT BY <integer_value>]

[MINVALUE <integer_value>/NOMINVALUE]

[MAXVALUE <integer_value>/NOMAXVALUE]

[CYCLE/NOCYCLE]

[CACHE<integer_value>/NOCACHE]

[ORDER/NOORDER]

DROPPING A SEQUENCE

It is possible to drop a sequence whenever it is no longer required. The Syntax is…

DROP SEQUENCE <sequence_name>

CURSOR :- A cursor is a type of pointer built into PL/SQL for querying the database, retrieving a set of records and allowing a developer to access the active data set, a row at a time. This allows the programmers to accomplish tasks that require procedural code to be performed on each record in a result set individually.

            Oracle engine uses the work area for it internal processing in order to execute a SQL statement. This work area is private to SQL’s operations and is called a cursor. The data that is stored in the cursor is called the ‘Active Data Set’. When a cursor is loaded with multiple rows using a query, the Oracle engine opens and maintains a row pointer into the active data set. The row pointer will be moved within the active data set depending on the user’s requests.

NAME DESCRIPTION
%FOUND Returns TRUE if record was fetched successfully, FALSE otherwise.
%NOTFOUND Returns TRUE if record was not fetched successfully, FALSE otherwise
%ROWCOUNT Returns number of records fetched from cursor at that point in time.
%ISOPEN Returns TRUE if cursor is open, FALSE otherwise.

Types of Cursor

  • Implicit Cursors
  • Explicit Cursors
  • IMPLICIT CURSOR : PL/SQL declares and manages an implicit cursor every time you execute a SQL DML statement such as Insert, Update, Delete or a SELECT INTO statement that returns a single row from the database. The kind of cursor is called implicit because Oracle automatically or implicitly handles many of the cursor related operations such as
  • Reserving area in memory.
  • Populating this area with appropriate data.
  • Processing the data in memory area.
  • Releasing the memory area when processing is complete.
  • EXPLICIT CURSORS : An explicit cursor is SELECT statement that is explicitly defined in the declaration section of you code and is also assigned a name. we cannot use EXPLICIT cursors for UPDATE, DELETE and INSERT statements. You can control the different PL/SQL steps involved in fetching information from the database using explicit cursors. Explicit cursor handling involves the following steps :
  • Declaring the cursor
  • Opening the cursor
  • Fetching rows from the cursor one at a time.
  • Closing the cursor

PROCEDURES :- A procedure is a subprogram that performs one or more actions. Procedures are key building blocks of modular code, allowing you to both consolidate and reuse your program logic. There are two types of procedures.

  • Local procedures
  • Stored procedures

LOCAL PROCEDURE

A local procedure is procedure which is defined in the declaration section of the PL/SQL block. The PL/SQL block may be named or anonymous blocks. This procedure is considered local because it is defined within the parent PL/SQL block. It cannot be called by any other PL/SQL blocks defined outside that enclosing block. Its syntax is

DECLARE

–declaration of Global variables

Procedure <prodname>

[(parameter{IN|OUT|INOUT}datatype,….)]

{IS|AS}

[local_declaration]

BEGIN

Execution section;

[EXCEPTION exception section;]

END[<prodname>];

BEGIN

–Executable code and call to procedure

[EXCEPTION]

[–exception section]

END;

STORED PROCEDURE

A Stored Procedure is a named PL/SQL code block that has been compiled and stored in one of the Oracle’s engines system table. Before the procedure is stored, the Oracle engine parses and compiles the procedure. To display the errors occurred in the procedure use the SHOW_ERRORS command at the SQL prompt. After storing it in the database it can be called by another application or PL/SQL subprogram.

            To create the stored procedure, you must have the create procedure privilege. Its Syntax is….

CREATE [OR REPLACE]PROCEDURE<prodname>

[(parameter{IN|OUT|INOUT}datatype,….)] {IS|AS}

[local_declaration];

BEGIN

Execution_section;

[EXCEPTION exception_section;]

END[<prodname>];

FUNCTIONS

Functions like procedures are the named PL/SQL blocks stored in the database. The same rules are followed for creating them. Security on them and parameter passing is also similar.

Functions are also of two types:

  • Local functions
  • Stored functions

LOCAL FUNCTIONS

A local function is the function that is defined in the declaration section of the PL/SQL block. This function cannot be called by any PL/SQL block defined outside that enclosing block. The Syntax to define a local function is…..

DECLARE

–declaration of Global variables

Function <functname>

[(parameter{IN}datatype,….)]

Return datatype{IS|AS}

[local_declaration]

BEGIN

Execution section;

[EXCEPTION exception section;]

END[<functname>];

BEGIN

–Executable code and call to procedure

[EXCEPTION]

[–exception section]

END;

STORED FUNCTIONS

A stored function is a named PL/SQL block that has been compiled and stored in one of the Oracle’s engine system table. Its Syntax is…..

CREATE [OR REPLACE]FUNCTION<functname>

[(parameter{IN}datatype,….)]

RETURN datatype{IS|AS}

[local_declaration];

BEGIN

Execution_section;

[EXCEPTION exception_section;]

END[<functname>];

It is similar to that of a stored procedure except here it returns a value.

PACKAGES

Packages are constructs that allow you to logically  group procedures, functions, variables, constants, cursors and exceptions. It is compiled and then stored in the database’s data dictionary. A common use for packages is to pack together all procedures, functions and other related objects that performs similar tasks.

The package has usually two components

  • Package Specification
  • Package Body(optional)

Procedures and functions defined within the specification can be executed, variables can be referenced, cursors can be opened and so on. The specification is like a declaration section. It does  not contain any PL/SQL block or executable code.

The syntax for package specification is….

CREATE |OR REPLACE|PACKAGE<pack_name>

{IS|AS}                                        

[package body object declaration]

END<pack_name>;

And the syntax for package body is….

CREATE |OR REPLACE|PACKAGE<pack_name>

{IS|AS}                                        

[package body object declaration]

END<pack_name>;

After the package is written, debugged, compiled and stored in the database, applications can call its subprograms, use its cursors, raise its exceptions and reference its types.

TRIGGERS

Database Triggers are the named PL/SQL blocks that are executed automatically in response to events such as INSERT,UPDATE and DELETE when performed on the database table.

CREATING A TRIGGER

To create a trigger on a table, the user must have a ‘create trigger’ privilege. In addition to it the user must have ALTER ANY TABLE system privilege or must be the owner of the table so that he may able to alter that table. The syntax for creating a Trigger is….

CREATER|OR REPLACE|TRIGGER <trigger_name>

{BEFORE|AFTER}

{INSERT|DELETE|UPDATE|UPDATE OF column list}

ON <tablename>

[{REFERENCING {OLD as old, NEW as new}]

[FOR EACH ROW[WHEN tigg_condition]]

[DECLARATION………]

BEGIN

[EXCEPTION……..]

END[trigger_name];

TRIGGER NAME

Like every object in a database has a name similarly that trigger has a name. this is usually defined in the CREATE or REPLACE trigger statement.

CREATER|OR REPLACE|TRIGGER <trigger_name>

In this statement OR REPLACE clause is optional. If the trigger with the same name exists and replace is not specified then an attempt to create a trigger will result in an error.

TRIGGERING STATEMENT OR EVENT

The triggering statement is the event that will cause Oracle to execute the trigger. This event can be DML statements (Insert, Update, Delete) on tables, DDL statements (Create, Alter, Drop) on any schema objects etc.

{BEFORE|AFTER}

{INSERT|DELETE|UPDATE|UPDATE OF column list}

ON <tablename>

[{REFERENCING {OLD as old, NEW as new}]

[FOR EACH ROW]

[Before|After] clause specifies if the trigger is to fire before or after the row of the given table is processed.

TRIGGER RESTRICTION

A Trigger Restriction specifies a Boolean expression that must be TRUE for a trigger to fire. It is specified using the optional WHEN clause, that allows you to specify logic to avoid unnecessary execution of the trigger.

When <trigg-condition>

Where <trigg-condition> is the Boolean expression.

TRIGGER ACTION

The trigger action is the body of the trigger. Oracle will execute this block when correct triggering statement is issued and trigger restriction if present evaluates to TRUE.

[DECLARE……..]

BEGIN

–executable statements

[EXCEPTION…..]

END[trigger_name];

Here the declare section is the optional section which is used to declare the local variables and constants. The executable section of the trigger is required and must contain atleast one statements.

TYPES OF TRIGGERS

Oracle has different types of triggers to enable the developer to accomplish a variety of tasks.

  • Row and Statement triggers
  • Before and After triggers
  • Instead-of triggers

ROW AND STATEMENT TRIGGER

The Row trigger is fired each time when a row is affected by the execution of the DML operation. These triggers are the most common types of triggers which are quite often used in data auditing applications. A row level triggers is identified by the FOR EACH ROW clause in the CREATE TRIGGER command.

            A statement trigger is the default type of triggers created and are identified by omitting the FOR EACH ROW clause in CREATE TRIGGER command.

BEFORE AND AFTER TRIGGER

Before Triggers fires the trigger action before the triggering statement is executed. These type of triggers are commonly used when trigger action should determine whether or not the triggering statement should be allowed to complete or not.

            After Trigger fires the trigger action after the triggering statement is executed. These type of triggers are commonly used when the triggering statement should complete before executing the trigger action.

INSTEAD-OF TRIGGERS

            Instead-of Triggers are essentially alternatives to DML triggers. They fire when INSERT’s, UPDATE’s and DELETE’s are about to occur. You code specifies what to do in place of these DML operations. These triggers control operations on views not tables. They can be used to make non updatable views updatable and to overwrite the behavior of views that are updatable.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.