SQL Fundamentals - A. V. Powertech (Knowledge is Power) SQL Fundamentals Triggers Stored Procedures Dual Table functions cursors

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)

——————————–

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

MANAGER 0

SALESMAN 3

MANAGER 0

ANALYST 0

PRESIDENT 0

SALESMAN 3

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

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;

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

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

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
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.
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.
LOWER(<C>) :- Where c is character string. This function returns the character c with all characters in lowercase. It frequently appears in WHERE clauses.
UPPER(<C>) :- Where c is character string. This function returns the character c with all characters in uppercase. It frequently appears in WHERE clauses.
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.
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.
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.
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.
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.

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 2^ND 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

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

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)

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

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.

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

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 -10²⁶ for descending sequence.
MAXVALUE : It specifies the highest number that sequence can generate NOMAXVALUE is the default value which is 10²⁷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.

Post Views: 1,169

Related Posts

संतुलित आहार चार्ट – इसके फायदे और जरूरी तत्व – Balance Diet Chart, Its Benefits and Components in Hindi

WhatsApp to be Unavailable on Microsoft Store starting July 1, Here’s Everything You Should Know

Pair your phone with WhatsApp Desktop

Leave a Reply Cancel reply