SQL

DBMS CONCEPTS

Data:

Data is collection of facts and figures. (or) data is raw material unrecognized facts that need to be processed (or) what is actually stored in the database is referred as data.

Information:

When data are processed, organized, structured or presented in a given context so as to make them useful, they are called information (or) the information refers to the meaning of the data as understood by some user.

Database:

A database is a collection of data, typically describing the activates of one or more related organizations.

Database Management System:

Is collection of programs or software, designed to assist in maintaining and utilizing large collection of data.

Introduction to SQL

Structured Query Language (SQL) is the set of statements with which all programs and users access data in an oracle database.

History of SQL

The language, Structured English Query Language (SEQUEL) was developed by IBM Corporation. To use RDBMS model. SEQUEL later became SQL. In 1979 Oracle introduced the first commercially available implementation of SQL.

Tasks provided by SQL

1. Querying data
2. Inserting, updating, and deleting rows in a table
3. Creating, replacing, altering, and dropping objects
4. Controlling access to the database and its objects
5. Guaranteeing database consistency and integrity

SQL*Plus

         

           SQL*Plus is an interactive and batch query tool that is installed with every Oracle Database server or client installation. It has a command-line user interface and a web-based user interface called iSQL*Plus.

To communicate with Oracle, SQL supports the following categories of commands.

Data Definition Language (DDL)

            

             Create

             Alter

             Drop

             Truncate

Data Manipulation Language (DML)

                

             Insert

             Update

             Delete

Data Query Language

             Select

Transaction Control Language

             Commit

             Rollback

             Savepoint

Data Control Language

             Grant

             Revoke

Oracle Internal Data Types

Char(size):

stores fixed-length character data to store alpha-numeric values, with a maximum size of 2000 bytes. Default and minimum size is 1 byte.

Varchar2(size):

stores variable-length character data to store alpha-numeric values, with a maximum size of 4000 bytes. No default size is must.

Nchar(size):

Stores fixed-length character data of length size characters or bytes, depending on the choice of national character set. Maximum size if determined by the number of bytes required storing each character with an upper limit of 2000 bytes. Default and minimum size is 1 character or 1 byte, depending on the character set.

Nvarchar(size):

Stores variable-length character data of length size characters or bytes, depending on the choice of national character set. Maximum size if determined by the number of bytes required storing each character with an upper limit of 4000 bytes.

Long:

Stores variable-length character data up to 2GB. Its length would be restricted bases on memory space available in the computer.

Number[p,s]:

Number having precision p, and scale s. the precision p indicates total number digit varies from 1 to 38. The scale s indicates number of digit in fraction part varies from -84 to 127.

Date:

Stores dates from January 1, 4612 B.C to December 31, 9999 A.D. oracle predefined of Date data type is DD-MON-YYYY. It takes 7 byes memory.

Raw(size):

Stores binary data of length size. Maximum size is 2000 bytes. One must have to specify size with RAW type data, because by default it does not specify any size.

Long raw:

Store binary data of variable length up to 2GB.

Note:

A table can have only one column data type as long or longraw. To overcome this drawback they introduced LOB types.

LOB(Large Objective Types)

CLOB( Character Large Object) it takes maximum size is 4GB.

NCLOB(National Character Large Object) it takes maximum size is 4GB.

BLOB(Binary Large Object) it takes Maximum size is 4GB.

Note:

The advantage of LOB is table can have N no. of LOB columns. LOB data types are called out of line storage types, because they store the actual data somewhere in oracle data base and maintain a pointer to this in the table, but long directly store the information within table only.

Data Definition Language:

The data definition language is used to create an object, alter the structure of an object and also drop already created object. The data definition languages used for table definition can be classified into following.

1. Create table command
2. Alter table command
3. Drop table command

Creation of table

Table is a primary object of database, used to store data in form of rows and column. It is created using following command.

Syntax:

Create table <table name> (column1 data type (size), column2 data type (size), ----------);

Points to be remember while creating a table.

1. The table name can contain maximum 30 character length or minimum of 1 character.
2. A table can have maximum 1000 columns or minimum of 1 column.
3. Table name should be unique in the database.
4. Table name must be start with an alphabet.
5. Column name should not repeat in the same table.
6. Oracle keywords can’t be used as table name.
7. Underscore, numerals and letters are allowed but not blank space or single quotes.

Example:

create table employee (eno number(4),ename varchar2(20), doj date, Sal number(7,2));

Note:

Semicolon is used as a terminator. Every SQL command ends with a semicolon. Without semicolon compiler won’t execute the command.

Desc Command:

This command gives the description of the table structure. It is external command of the oracle database.

SQL > desc employee;

Name   Null?    Type
ENO             NUMBER(4)
ENAME           VARCHAR2(20)
DOJ             DATE
ESAL            NUMBER(7,2)  

Constraints:

       By default the data present in the table is not following any rules and regulations. So, we can insert duplicate values and null values in the table. To avoid this we need to impose set of restriction on the Table.

A restriction can be imposed in the form of integrity constraints.

• Not null
• Unique
• Primary key
• Check
• Foreign key

Oracle allows programmers to define constraints

1. Column level
2. Table level

Column level constraints

       If data constraints are defined along with the column definition when creating or altering a table structure, they are column level constraints. Column level constraints can apply only on one column.

Table level constraints

     If the constraints are defined after the defining all the table column when creating or altering the table structure, it is a table level constraints. Table level constraints mostly used to make composite keys or to apply on multiple columns.

Not null:

       If this constraint is imposed on the column, the column will not allow null values into it. This constraint can be applied only column level. Table can have maximum 1000 no of not null columns.

SQL> create table student(regno number(5)not null,sname varchar2(20),saddress varchar2(20));

     If you try to insert null values into regno then oracle generate following error message

ERROR at line 1:

ORA-01400: cannot insert NULL into ("SCOTT"."STUDENT"."REGNO")

Note :Not null constraint won’t allow the null values but it allows duplicate values.

Unique:

   It is to maintain the distinct values and to restrict duplicate values or records. A table may have many unique keys. This constraint can be applied either table level or column level. If unique constraints are defined in more than one column, it is called as composite unique key. A maximum of 32 columns allowed under one unique key.

SQL > create table bank(bcode number(5) unique,bname varchar2(20),bcity varchar2(20), constraint un_bna_bci unique(bname,bcity))

When you try to insert duplicate values into the bcode column oracle generate following error message

ERROR at line 1:

ORA-00001: unique constraint (SCOTT.SYS_C009771) violated.

       If you don’t specify the constraint name by default oracle will generate predefined error code message i.e SCOTT.SYS_C009771.

       In case of constraint name is specified, and violate the unique constraint the oracle generate following error message.

ERROR at line 1:

ORA-00001: unique constraint (SCOTT.UN_BNA_BCI) violated

Note: unique key won’t allow the duplicate values but it allows N no of null values.

Primary key (not+unique):

The combination of not null + unique is called primary key, a table can have only one primary key. Primary key can be applied either column level or table level. if primary key constraints is defined in more than one column, it is called as composite primary key. A maximum of 32 columns allowed under one primary key.

SQL> create table project_details(pcode number(4),pname varchar2(10),constraint pk_pcode_pname primary key(pcode,pname));

When you violate the primary key, the oracle generate following error message

ERROR at line 1:

ORA-00001: unique constraint (SCOTT.PK_PCODE_PNAME) violated

Referential integrity constraint

In this category there is only one constraint and if is foreign key & references to establish a “parent-child” relationship between two tables having a common column, we make use of referential integrity constraint.

Foreign key represent relationship between tables. A foreign key is a column whose values are derived from the primary key or unique key. The table in which the foreign key is defined is called a foreign table or detail table. The table that defines the primary or unique keys and is referenced by the foreign key is called the primary table or master table.

The master table can be referenced in the foreign key definition by using references keyword .if the column name is not specified, by default, oracle references the primary key in the master table.

The existence of a foreign key implies that the foreign key is related to the master table form which the foreign key is derived .A foreign key must have a corresponding primary key or a unique key value in a master table.

Principles of foreign key constraint

• Rejects an insert or update of a vale in a particular column, if a corresponding value does not exist the master table.
• Deletion of rows form the master table is not possible if detail table having corresponding values.
• Primary key or unique key must in master table.
• Requires that the foreign key column(s) and reference column(s) have same data type.

Foreign key constraint defined at table level

First create master or parent table:

SQL> create table department(deptno number(4) constraint dept_dno primary key, dname varchar2(20), dloc varchar2(20));

Creating child table:

SQL> create table employee(empno number(4) constraint emp_eno primary key, ename varchar2(20), dob date,esal number(7,2), deptno number(4) references department(deptno));

Creating foreign key constraint:

SQL> create table employee(empno number(4) constraint emp_eno primary key, ename varchar2(20), dob date,esal number(7,2), deptno number(4), constraint fore_deptno foreign key references department(deptno));

On delete cascade clause

The on delete cascade option is used with foreign key. When the on delete cascade option is specified in the foreign key definition, if the user deletes a record in the master table, all the corresponding records in the detail table along with the master table record will be deleted.

SQL> create table employee(empno number(4) constraint emp_eno primary key, ename varchar2(20), dob date,esal number(7,2), deptno number(4), constraint fore_deptno foreign key references department(deptno)on delete cascade);

On delete set null

This option is used with the child table, it will allow us to delete record from master table but corresponding child record foreign key changed into null.

Default value:

We can specify a default value for any color table , it we are not specifying a value while inserting this value will be taken into the table.

SQL>create table bank(cid number(5) constraint pk_cid primary key, cname varchar2(20), cdob date, cbalance number(8,2) default 500);

Check constraint:

We use this constraint to specify a value in a column to be equal to a specified value or to be within the range of values.

SQL> create table banks(cid number(5) constraint pk_cid primary key, cname varchar2(20), cdob date, cbalance number(8,2) constraint che_bal check (cbalance> 500));

If try to insert cbalance column < 500 value then oracle will generate the following error message.

ERROR at line 1:

ORA-02290: check constraint (SCOTT.CHE_BAL) violated

Delete table:

Delete command is used to delete the records from the table, if don’t specify any condition then all the records will be deleted. If specify the condition based on the condition the records will be deleted.

Sysntax: delete <table_name>||where <condition>;

SQL> delete emp;

When use the above command all the records will be deleted from the table emp.

SQL> delete emp where ename=’LAKSHMAN’

When use the above command it find the record with the name ‘LAKSHMAN’ in emp table. if its present in the emp table then ‘LAKSHMAN’ record will be deleted from the emp table.

Truncate Table:

Truncating a table is similar to writing the delete statement without any condition. i.e truncate will delete all the records from the table.

SYNTAX: truncate table <table_name>

SQL>truncate table employee;

Note: Delete command can be rollback where truncate can’t be rollback.

Alteration of Table:

Once simple table is created, if there is a need to change the structure of a table at that time alter command is used.

• Increasing the width of the datatype
• Change null to not null or not null to null
• Change the default values of a column
• Add a column or drop column
• Mark a column as unused
• Drop unused columns
• Rename a column or constraints
• Adding or drop a constraints
• Disable/enable constraints
• Renaming the constraints

ALTER SYNTAX

ALTER TABLE <TABLE_NAME> MODIFY <CNAME> <DATATYPE> [ WIDTH] [NULL | NOT NULL] [DEFAULT <VALUE>

Increasing the width of the data type:

SQL> alter table employee modify eno number(6);

Changing from one data type to another data type

SQL> alter table employee modify esal number(7);

Making not null column to null and null column to not null

SYNTAX:

ALTER TABLE <TNAME> MODIFY <COLUMN>NOT NULL;

Example:

SQL> alter table employee modify ename not null;

Note: if you want to make null column to not null, first we need to drop the null values in the column.

Adding a new column

SQL> alter table employee add dob date;

Adding multiple columns to table

SQL> alter table empoyee add(mgr number(4),comm number(4));

Drop a column:

SYNTAX:

                ALTER TABLE <TNAME> DROP COLUMN <CNAME>

Example:

SQL> alter table employee drop column mgr;

Drop multiple columns

SYNTAX:

ALTER TABLE <TNAME> DROP (COL1,COL2----);

Example:

SQL> alter table employee drop(comm, dob);

Marking as unused column

SYNTAX: ALTER TABLE <TNAME> SET UNUSED(COL1,COL2…..);

Example:

SQL> alter table employee set unused(eno,doj);

Note: if mark column as unused that column won’t be displayed in select statement.

Drop unused columns

SYNTAX: ALTER TABLE <TNAME> DROP UNUSED COLUMNS;

SQL> alter table employee drop unused columns;

Renaming the column

SYNTAX:

ALTER TABLE <TNAME> COLUMN <COLNAME> TO <NEW NAME>;

Example:

SQL> ALTER TABLE EMP_COPY RENAME COLUMN JOB TO EJOB;

Adding constraint:

Syntax:

Alter table <tname> constraint <constraint_name> constraint type(columnlist)

Example:

SQL> alter table employee add constraint pri_eno primary key(empno);

Renaming the constraint name:

Syntax:

Alter table <tname> rename constraint <constraint_name> to <new_constraint_name>

Example:

SQL> alter table employee rename constraint pri_eno to pri_empno;

Dropping a constraints:

Syntax:

Alter table <tname> drop constraint <constraint_name>primary key/unique(<collist>)

Dropping primary key constraint:

Example:

SQL> alter table employee drop primary key;

Note: primary key constraint can be drop without constraint name, by just giving primary key. Because every table contains only one primary key.

Dropping unique key constraint:

Example:

SQL> alter table employee drop unique(ename);

Adding foreign key:

SQL> alter table emps add constraint for_dept foreign key(deptno) references dept(deptno);

Dropping the referential integrity constraint:

When you try to delete the parent table records, if exists any dependent child record then oracle gives child records are exists you can’t drop the parents table record. To drop parent table records first you need to drop the relation between two tables.

SQL> alter table depts drop constraint primary key cascade.

Operators in oracle

In oracle there 5 typs of operators

Arithmetic : +, -, *, /

Relational operators: = ,> ,>=,<,<=,in ,between, like, is null

Negation: <> or != or ^=, not in, not between, not like, is not null.

Logical operators: and , or , not.

Set operators: union, union all, intersect, minus

Boolean operators: exists, not exists.

1. Write a query to display all the employees who’s designation is manager

Example:

SQL> select * from emp where job='MANAGER';

2. Write a query to display the details employee who’s name ‘SCOTT’

Example:

SQL> select * from emp where ename=’SCOTT’;

3. Write a query to display the employee who’s working deptno is 20

SQL> Select * from emp where deptno=20;

Note: oracle is not a case sensitive but where clause is case sensitive.

4. Write a query display all the employees who’s salary is grater than 2500

       SQL> select * from emp where sal>=2500;

5. Write query to display all the employee who joined after 1980.

SQL> select * from emp where hiredate >'31-dec-1980';

In operator:

It is used to define a list. Always the elements of the list are enclosed within the parenthesis and separated by comma.

1. Write query to display the employees who is working for deptno 10 or 20

             SQL> select * from emp where deptno in(10,20);

2. Write query to display the employees who’s salaries in list 5000,3000,800

            SQL> select * from emp where sal in(3000,5000,800);

3. Write a query to display ‘SCOTT’,’BLAKE’,’ADAMS’ details

            SQL> select * from emp where ename in('SCOTT','BLAKE','ADMAS');

4. Write a query to display employees who’s hiredates ’17-dec-1981’,’3-dec—1981’,’21-jan-1981’

            SQL> select * from emp where hiredate in('17-dec-1981','3-dec-1981','21-jan-1981')

Between operator:

         It is used to define the range. It will with both numbers and characters.

        Syntax: <colname> between [start_range] and [end_range]

1. Write a query to display the employees who is getting salary grater than or equal to 2000 and less than or equal to 5000.

SQL> select * from emp where sal between 2000 and 5000;

2. Write a query to display the all the employees who is joinded on ’01-jan-1981’ to ’31-aug-1981’ from emp.

SQL> select * from emp where hiredate between '01-jan-1981' and '31-aug-1981';

Like operator:

It is used to match a pattern of character or sequence of characters. The delimeter that are used along with like operator are

1. Underscore(_) : to represent a single character
2. Percentage(%): to represent a group of characters.

1. Write a query display details employees who’s name is starts with ‘S’

SQL> select * from emp where ename like '%S';

2. Display all the employees who’s naming 4 character length

SQL> select * from emp where name like '____';

3. Write a query to display all the employees who’s name in the middle contain letter ‘A’

SQL> select * from emp where ename like '%A%';

4. Display all the employees joined in the month starting character ‘A’

SQL> select * from emp where hiredate like '%-A%-%';

5. Display employees who’s joining date is first 9 days of any of the month

       SQL> select * from emp where hiredate like '0%'

Is null:

1. Write a query to display the employees who is not receiving any commission

SQL> select * from emp where comm is null;

And:

1. Write a query to display all the employees who’s job is ‘MANAGER’ and working for deptno 20.

            SQL> select * from emp where job='MANAGER' and deptno=20;

2. Write a query to display the details employee, who’s name starts with ‘J’ and sal > 2500 and working for deptno=20;

            SQL> select * from emp where ename like 'J%' and deptno =20 and sal >2500;

Or operator Exmpales:

1. Write a query to display the details employee, who’s name starts with ‘J’ or sal > 2500 or working for deptno=20;

      SQL> select * from emp where ename like 'J%' or deptno =20 or sal >2500;

2. Write a query to display the details of all the employees who’s job is ‘MANAGER’ or ‘CLERK’

      SQL> select * from emp where job='MANAGER' or job='CLERK';

Functions:

Oracle functions server the purpose of manipulating data items and returning a result. Functions are also capable of accepting user-supplied variables or constants and operation on them. Such variable and constants are called arguments.

Functions are classified into

1. Single row functions
2. Group functions.

Single row functions:

A function which process only one row at a time and return the value is single-row function. This single row function is called as individual function or scalar functions.

Group function:

A function which processes a set of rows at a time and returns a single value is group or aggregate function.

Single row function and group function are divided into following types.

1. Numeric function (or) Arithmetic function
2. Character (or) string function
3. Date function (or) Data manipulation
4. Data conversion (or)type cast functions

Arithmetic functions:

1. abs() 2.floor() 3.sing() 4.round() 5.sqrt() 6.greatest() 7.trunc() 8.power 9.least() 10.ceil() 11.mod()

ABS()

This function takes as an argument any numeric datatype or any nonnumeric datatype that can be implicitly converted to a numeric datatype. The function returns the same datatype as the numeric datatype of the argument.

Examples

The following example returns the absolute value of -15:

SQL> SELECT ABS(-15) "Absolute" FROM DUAL;

Absolute

----------

                15

Ceil()

CEIL returns smallest integer greater than or equal to n.

This function takes as an argument any numeric datatype or any nonnumeric datatype that can be implicitly converted to a numeric datatype. The function returns the same datatype as the numeric datatype of the argument.

SQL> select ceil(1234.46) from dual;

CEIL(1234.46)

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

                             1235

Union

Union is a set operator to combine the output of two different quires. Union operator the rows returned by different quires, and restrict the duplicate rows.

SQL> select * from emp where deptno=20 union (select * from emp where sal>2000);

1. Write a query to display the employees who’s sal is same as either ‘FORD’ or ‘JAMES’

SQL> select * from emp where sal in(select sal from emp where ename='FORD' union (select sal from emp where ename=('JAMES')))

To select second last row

SQL> select s.* from(select a.*,max(rn) over() maxr from (select e.*,rownum rn from emp e order by rownum desc) a)s where rn=maxr-1;

SQL> select * from(select rownum rn,empno,ename,sal,deptno from emp) where rn>( select max(rownum)-2 from emp)

                         minus

SQL> select * from(select rownum rn,empno,ename,sal,deptno from emp) where rn>( select max(rownum)-1 from emp)

SQL> select * from(select empno,ename,sal,rownum rn from emp) where rn=(select max(rownum)-2 from emp)

Deleting The Duplicate Records

SQL> delete from emps e where rowid not in(select min(rowid) from emps s where e.empno=s.empno);

String Functions:

Upper(): this function is used to convert the string into uppercase.

SQL> select upper('alex')from dual;

Lower():this function is used to convert the string into uppercase.

SQL> select lower('PRAVEEN')from dual;

Ascii(): it return the ascii value of character data type

SQL> select ascii('a')from dual;

Chr(<number>): to find the equivalent ascii character for given ascii code

SQL> select chr(97)from dual;

Initcap(<string>): to convert the initial letter of each word of given string to upper case and rest of characters to lower case.

SQL> select initcap('miani ites edu')from dual;

Substr(<string>,starting position[no of characters]): this function to select some portion of string from specified position upto no of chars from given string.

SQL> select substr('lakshmana',6,3)from dual;

Instr(<string>,’Search character’, search position,occurrence):

To find the position of string from specified portion upto no of character from given strings.

SQL> select instr('alex is good boy and he is lazy','is',3,1) from dual;

INSTR('ALEXISGOODBOYANDHEISLAZY','IS',3,1)

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

                                             6

SQL> select instr('alex is good boy and he is lazy','is',3,2) from dual;

INSTR('ALEXISGOODBOYANDHEISLAZY','IS',3,2)

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

                             25

Length(<string>): to find the length of given string. Here space also counted as single character.

SQL> select length('miani ites edu')from dual;

LENGTH('MIANIITESEDU')

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

                          14

Lpad(<string>,lengh,padding character): to add the specified character to left side of the given string upto specified lenth.

SQL> select lpad('oracle',10,'*')from dual;

            LPAD('ORAC

              ----------

              ****oracle

Ltrim(<sgring>,[,string]): this function is used to remove the specified characters from entrance i.e left side of the given string.

SQL> select ltrim('ALEX','A')from dual;

SQL> select ltrim('MCA0501','MCA05')from dual;

Trim(): this function is used to remove white spaces from the both ends of given string.

SQL> select length(' raj kumar ' )from dual;

                 LENGTH('RAJKUMAR')

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

                           16

SQL> select length(trim(' raj kumar ') )from dual;

LENGTH(TRIM('RAJKUMAR'))

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

           9

Concat(<string1>,<string2>):

     This function is used to join two strings.

SQL> select concat(concat('miani','ites'),'edu')from dual;

              CONCAT(CONCA

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

                    Mianiitesedu

Translate(‘String’,’set1’,’set2’): to replace each character of set1 with corresponding character to set2 in a given string.

SQL> select translate('lakshman','as','mx')from dual;

                     TRANSLAT

                         --------

                    Lmkxhmmn

Replace(‘string’,’set1’,’set2’): this function used to replace set1 completely with set2 in given string.

SQL> select replace('alex','al','hello')from dual;

                REPLACE

                  -------

               Helloex

Decode():

Decode(<columname>,<val>,<rval>,<val>,<rval>,---------[<colname>])

Decode acts as a multiple decision-making statement of any procedural language. This function is similar to nested if of procedural language.

                 SQL> select job, decode(job,'CLERK','C','MANAGER','MA')FROM EMP;

Soundex()

This function is ued convert or find values based pronunciation of the word. The function has to apply both the sides.

SQL> SELECT * FROM EMP WHERE SOUNDEX(ENAME) =SOUNDEX('SMATH');

Distinct Clause

To prevent the selection of distinct rows, we can include distinct clause with select command.

SQL> select distinct deptno from emp;

NVL(exp1,exp2)

If expression1 not null it returns expression1 only. If expression1 is null then it returns expression2.

SQL>select empno,ename,sal+nvl(comm,0) totalsal from emp;

Case:

Syntax:

Case <exp> when <match>then <result>;

SQL>select empno,ename,(case job

when 'PRESIDENT' then 'BIG BOSS'

when 'MANAGER' then 'BOSS'

else 'EMPLOYEE' end) as comments from emp;

Group Functions:

A group functions returns a result based on a group of rows. Some of these are just purely mathematical functions.

The group functions supported by Oracle are summarized below:

1) Avg (Average): This function will return the average of values of the column specified in the argument of the column.

SQL>select avg(sal) from emp where deptno=20;

2) Min (Minimum): The function will give the least of all values of the column present in the argument.

SQL>select min(sal)from emp where deptno=10;

3) Max (Maximum): To perform an operation, which gives the maximum of a set of values the max, function can be made use of.

4) Sum(): The sum function can be used to obtain the sum of a range of values of a record set.

5) Count(): This function is used to count number rows. It can take three different arguments, which mentioned below.

Syntax:

Count(*)

Count(column name)

Count(distinct column name)

Count (*): This will count all the rows, including duplicates and nulls.

Example:

SQL>Select count(*) from emp;

Count (Column name) : It counts the number of values present in the column without including nulls.

Example:

SQL> select count(comm) from emp_master;

Count (distinct column name) : It is similar to count(column name) but eliminates duplicate values while counting.

Example:

SQL>Select count(distinct deptno) from emp_master;

Set Operators

Set operators combine the results of two queries into a single one. The following set operators are available in SQL.

• Union
• Union All
• Intersect
• Minus

While we are using set operators the following points must be keep in mind The queries, which are related by a set operator should have the same number of columns and the corresponding columns, must be of the data types.

Such a query should not contain any columns of long data type.

The labels under which the rows are displayed are those from the first select statement.

Table 1:

Table2

Union: The union operator returns all distinct rows selected by two or more queries.

SQL> select * from emp where ename like 'S%' union(select * from emp_20 where ename like'%S' or ename like 'S%');

The employees ‘SMITH’ and ‘SCOTT’ are present in the both the tables emp_20 and emp table when we use union it restrict the duplicate records and gives the unique records only.

Union All :

The union all operators returns all rows selected by either query including duplicates. The following example combines the result with the aid of union all operator, which does not eliminates duplicate rows.

SQL> select * from emp where ename like 'S%' union all (select * from emp_20 where ename like'%S' or ename like 'S%');

Intersect :

The intersect operator outputs only rows produced by both the queries intersected i.e. the output in an intersect clause will include only those rows that are retrieved by both the queries.

SQL>select * from emp where ename like 'S%' intersect (select * from emp_20 where ename like'%S' or ename like 'S%');

 



  Minus :

           The Minus operator outputs the rows produced by the first query, after filtering the rows retrieved by the second query.

SQL>select * from emp_20 where ename like'%S' or ename like 'S%' minus(select * from emp where ename like '%S');

Joins

Sometimes we require to treat multiple tables as though they were a single entity. Then a single SQL sentence can manipulate data from all the tables. To achieve this, we have to join tables. The purpose of join is to combine the data spread across tables. A join is actually performed by the ‘where’ clause which combines the specified rows of tables.

Syntax for joining tables

select columns from table1, table2, ... where logical expression;

Inner join:

It is to retrieve the rows from the joining tables those are having matching values.

Types of Inner Join:

1. Equi join

2. Non- equi join.

Equi join:

If the relationsip between the columns of different table is established by using and oprator '=' .

Non Equi join:

If the relationship between the columns of different tab les is established using an operator other than =, like operators <,>, between, like.

1. Display the details of all the employees working in 'ACCOUNTING' dept.

SQL> select e.*,d.dname from emp e,dept d where d.dname='ACCOUNTING' and e.deptno = d.deptno;

2. Display the details of all the salesman of sales dept receiving commission.

SQL>select e.*,d.dname from emp e,dept d where d.dname='SALES' and e.job ='SALESMAN' and e.comm is not null and e.deptno=d.deptno;

3. Display all the managers having exp not less than 20 years and working in the location 'NEW YORK', 'DALLAS' and working under some manager.

SQL> select e.* from emp e,dept d where e.job='MANAGER' and e.mgr is not null and (sysdate-e.hiredate)/365>=20 and d.loc IN('DALLAS','NEW YORK') and e.deptno = d.deptno;

4. Display the grades of the employee 'FORD' with details

SQL>select e.*,s.grade from emp e,salgrade s where e.ename='FORD' and e.sal between s.losal and s.hisal;

5. Display the details of grade 4,5 Managers

SQL>select e.*,s.grade from emp e,salgrade s where e.job='MANAGER' and e.sal between s.losal and s.hisal and s.grade(4,5);

Inner join

An inner join (sometimes called a simple join) is a join of two or more tables that returns only those rows that satisfy the join condition.

SQL> select e.ename,e.sal,e.deptno,d.dname,d.loc from emp e inner join dept d on e.deptno=d.deptno;

SQL>select e.empno,e.ename,s.grade,e.sal from emp e inner join salgrade s on e.sal between s.losal and s.hisal;

1. Display empno,ename,sal,grade,loc of all the employees

SQL>select e.empno,e.ename,e.sal,s.grade,d.loc,d.dname from (emp e inner join salgrade s on e.sal between s.losal and s.hisal) inner join dept d on e.deptno =d.deptno;

2. Display all the empno,ename,dname of all the employees of 'ACCOUNTING' dept.

SQL>select e.empno,e.ename,d.dname from emp e inner join dept d on e.deptno = d.deptno and d.dname='ACCOUNTING';

3. Display all the grade 4 and 5 MANAGERS of ACCOUNTING and SALES dept.

SQL> select e.* ,s.grade from (emp e inner join salgrade s on e.sal between s.losal s.hisal) inner join dept d on e.deptno=d.deptno where d.dname in ('ACCOUNTING', 'SALES') and s.grade in(4,5);

Self join:

A table which is joined to itself is called a self join. self join must require table name aliases because in self join the same table is used as multiple copies and they require a join called as self join.

1. Write a query to list the details of all the emps belong to dept of 'ADAMS'?

SQL> select b.* from emp a, emp b where a.ename='ADAMS' and b.deptno =a.deptno;

2. Display the details of all employees those who are senior to their own manager?

SQL> select a.* from emp a,emp b where a.mgr=b.empno and a.hiredate <b.hiredate;

Outer Join :

Outer join extends the result of simple join. An outer join returns all the rows returned by simple join as well as those rows from one table that do not match any row from the other table. This cannot be with a simple join. The outer join is represented by (+) sign.

To write a query that performs an outer join of tables A and B and returns all rows from B (a right outer join), use the RIGHT [OUTER] JOIN syntax in the FROM clause, or apply the outer join operator (+) to all columns of A in the join condition in the WHERE clause. For all rows in B that have no matching rows in A, Oracle returns null for any select list expressions containing columns of A.

1. Display the details if all the depts with or without empoyees if emp exists display empno,ename also.

SQL> select e1.empno,e1.mgr,e2.empno from emp e1,emp e2 where e1.mgr(+) = e2. empn ;

SQL>select d.*,e.empno,e.ename from dept d, emp e where e.deptno(+)=d.deptno;

SQL>select d.*,e.empno,e.ename from emp e right outer join dept d on e.deptno = d.deptno;

To write a query that performs an outer join of tables A and B and returns all rows from A (a left outer join), use the LEFT [OUTER] JOIN syntax in the FROM clause, or apply the outer join operator (+) to all columns of B in the join condition in the WHERE clause. For all rows in A that have no matching rows in B, Oracle Database returns null for any select list expressions containing columns of B.

SQL>select d.*,e.empno,e.ename from emp e, dept d where e.deptno=d.deptno(+);

SQL> select d.*,e.empno,e.ename from emp e left outer join dept d on e.deptno = d.deptno;

To write a query that performs an outer join and returns all rows from A and B, extended with nulls if they do not satisfy the join condition (a full outer join), use the FULL [OUTER] JOIN syntax in the FROM clause.

SQL>select d.*,e.empno,e.ename from emp e full outer join dept d on e.deptno =d.deptno;

Natural join:

It represent full outer join only. Natural join automatically indetifies the joining column or the common column and apply full outer join. In natural join on clause is not allowed. At same time usage of qualifiers like e. or d. to the columns are not allowed.

SQL> select ename,sal,deptno,dname,loc from emp natrual join dept using(deptno)

Subquery:

A subquery is a query within a query. In Oracle, you can create subqueries within your SQL statements. These subqueries can reside in the WHERE clause, the FROM clause, or the SELECT clause.

Oracle allows maximums of 255 subqueries. Innermost query is executed first, outer most query is executed last. The subquery provides input to the conditional clause of main query.

1. Display the details of employees whose department is located in 'NEW YORK'

select * from emp where deptno =(select deptno from dept where loc='NEW YORK');

2. Display the details of emps whose salary is more than ‘JAMES’ salary.

select * from emp where sal>(select sal from emp where ename='JAMES');

3. display the details of all the emp’s whose job is same as ‘JAMES’

select * from emp where job=(select job from emp where ename='JAMES')

4. Display the details of all the emp’s who is senior to ‘JAMES’

select * from emp where hiredate>(select hiredate from emp where ename='JAMES')

5. display the details of all the emp’s who is belong to ‘SCOTT’ department except details of ‘SCOTT’

select * from emp where deptno=(select deptno from emp where ename='SCOTT') and ename !='SCOTT'

6. Display the details of the employees whose jobs are same as the jobs of employee of ‘SALES’ department.

select * from emp where job in(select e.job from emp e,dept d where d.dname='SALES' and e.deptno=d.deptno)

7. Display the details of all grade 3 employees whose dept is same as ‘SCOTT’ dept.

select e.*,s.grade from emp e,salgrade s where deptno=(select deptno from emp where ename='SCOTT') and e.sal between s.losal and s.hisal and s.grade=3;

8. display the details of all the emps of ‘NEW YORK’ and ‘DALLAS’ and whose job is same as the job of any employee of grade 2 except ‘SALESMAN’

select e.* from emp e,dept d where d.loc in('NEW YORK','DALLAS') and e.job in (select e.job from emp e,salgrade s where e.job!='SALESMAN' and s.grade=2 and e.sal between s.losal and s.hisal) and e.deptno=d.deptno;

9. display the details of all the employees whose sal is more than ‘SMITH’ but less than ‘BLAKE’ salary and should not be equal to ‘SCOTT’ or ‘FORD’

select * from emp where sal >(select sal from emp where ename='SMITH') and sal<(select sal from emp where ename='BLAKE') and sal not in(select sal from emp where ename in('FORD','SCOTT'))

10. display the details of all employees who are managers to others.

select * from emp where empno in(select mgr from emp)

11. display the details of all the employees belong to dept where ‘BLAKE’ manager is working.

select * from emp where deptno in (select deptno from emp where empno=(select mgr from emp where ename='BLAKE'))

12. Display the details of all the employees irrespective of dept if there is nay employee working the deptno 40.

select * from emp where exists (select * from emp where deptno=40)

Filters

1. Any        2. All

These tow predicates of SQL are to filter the values these are returned by subquery or from the given list. These two predicates are totally operator dependent. i.e depending upon the operator used in selection criteria.

1. Display the details of employees whose salaries are more than the salaries of the any employee of deptno 10.

select * from emp where sal > any (select sal from emp where deptno=10)

2. Display the details of all the employees of deptno 10 whose sal is more than all the salaries of deptno 20.

select * from emp where deptno=10 and sal>all(select sal from emp where deptno=20)

Correlated Sub-queries:

A correlated subquery is one way of reading every row in a table and comparing values in each row against related data. It is used whenever a subquery must return a different result for each candidate row considered by the parent query.

1. Display the details of the n ^th height salary employee.

select * from emp where &n=(select count(distinct(sal))from emp e where emp.sal<=e.sal)

2. Display the highest paid emp of each dept.

select * from emp e where sal=(select max(sal) from emp f where f.deptno=e.deptno)

3 . dsplay the senior most employee of each department.

select * from emp e where hiredate=(select min(hiredate) from emp f where f.deptno=e.deptno)

Sequences

The quickest way to retrieve data from a table is to have a column in the table whose data uniquely identifies a row. By using this column and a specific value, in the Where condition of a Select sentence the Oracle engine will be able to identify and retrieve the row fastest.

To achieve this, a constraint is attached to a specific column in the table that ensures that the column is never left empty and that the data values in the column are unique. Since data entry is done by human beings it is quite likely that duplicate value will be entered, which violates the constraint and the entire row is rejected.

If the value to be entered into this column is machine generated, it will always fulfill the constraint and the row will always be accepted for storage.

ORACLE provides an object called a Sequence that can generate numeric values. The value generated can have a maximum of 38 digits. A sequence can be defined to

Generate numbers in ascending or descending

Provide intervals between numbers

Caching of sequence numbers in memory etc.

A sequence is an independent object and can be used with any table that requires its output.

Creating Sequences:

The minimum information required for generating numbers using a sequence is:

• The starting number
• The maximum number that can be generated by a sequence
• The increment value for generating the next number.

This information is provided to Oracle at the time of sequence creation. The SQL statement used for creating a sequence is:

Syntax:

Create Sequence <Sequence_name>

[Increment By integervalue

Start With integervalue

Maxvalue integervalue/Nomaxvalue

Minvalue integervalue/Nominvalue

Cycle/NoCycle

Cache integervalue/NoCache

Order/NoOrder]

Keywords and Parameters:

INCREMENT BY:

Specifies the interval between sequence numbers. It can be any positive or negative value but not zero. If this clause is omitted, the default value is 1.

MINVALUE:

Specifies the sequence minimum value.

NOMINVALUE:

Specifies a minimum value of 1 for an ascending sequence and –(10)^26 for a descending sequence.

MAXVALUE:

Specifies the maximum value that a sequence can generate.

NOMAXVALUE:

Specifies a maximum of 10^27 for an ascending sequence or –1 for a descending sequence. This is the default clause.

START WITH:

Specifies the first sequence number to be generated. The default for an ascending sequence is the sequence minimum value (1) and for a descending sequence, it is the maximum value (-1).

CYCLE:

Specifies that the sequence continues to generate repeat values after reaching either its maximum value.

NOCYCLE:

Specifies that a sequence cannot generate more values after reaching the maximum value. By default, sequence is nocycle.

CACHE:

Specifies how many values of a sequence ORACLE pre-allocates and keeps in memory for faster access. The minimum value for this parameter is two.

NOCACHE:

Specifies that values of a sequence are not pre-allocated. By default, cache of sequence is 20.

ORDER:

This guarantees that sequence numbers are generated in the order of request. This is only necessary if you are using Parallel Server in Parallel mode option. In exclusive mode option, a sequence always generates numbers in order.

NOORDER:

This does not guarantee sequence numbers are generated in order of request. This is only necessary if you are using Parallel Server in Parallel mode option. If the ORDER / NOORDER clause is omitted, a sequence takes the NOORDER clause by default.

Dropping Sequence:

The DROP SEQUENCE command is used to remove the sequence from the database.

Syntax:

Drop Sequence <Sequence_Name>;

Views

After a table is created and populated with data, it may become necessary to prevent all users from accessing all columns of a table, for data security reasons. This would mean creating several tables having the appropriate number of columns and assigning specific users to each table, as required. This will answer data security requirements very well but will give rise to a great deal of redundant data being resident in tables, in the database.

To reduce redundant data to the minimum possible, Oracle allows the creation of an object called a View A View is mapped, to a SELECT sentence. The table on which the view is based is described in the FROM clause of the SELECT statement. The SELECT clause consists of a sub-set of the columns of the table. Thus a View, which is mapped to a table, will in effect have a sub-set of the actual columns of the table from which it is built. This technique offers a simple, effective way of hiding columns of a table.

An interesting fact about a View is that it is stored only as a definition in Oracle’s system catalogue. When a reference is made to a View, its definition is scanned, the base table is opened and the View created on top of the base table. Hence, a View holds no data at all, very large extent. When a View is used to manipulate table data, the underlying base table will be invisible. This will give the level of data security required.

The Oracle engine treats a View just as though it was a base table. Hence, a View can be queried exactly as though it was a base table. However, a query fired on a view will run slower that a query fired on a base table. This is because the View definition has to be retrieved from Oracle’s system catalogue, the base table has to be identified and opened in memory and then the View has to be constructed on top of the base table, suitably masking table columns. Only then, will the query actually execute and return the active data set.

Some View’s are used only for looking at table data. Other View’s can be used to Insert, Update and Delete table data as well as View data. If a View is used to only look at table data and nothing else, the View is called a Read-Only view. A View that is used to Look at table data as well as Insert, Update and Delete table data is called an Updateable View.

The reasons why views are created are:

1. When Data security is required
2. When Data redundancy is to be kept to the minimum while maintaining data securiry

Lets spend some time in learning how a View is

1. Created

2. Used for only viewing and/or manipulating table data i.e. a read-only or updateable view

3. Destroyed.

Syntax:

CREATE [OR REPLACE] VIEW [{FORCE | NOFORCE}] VIEW view_name
[(alias_name[, alias_name...])] AS subquery[WITH {CHECK OPTION | READ ONLY} CONSTRAINT constraint_name];

1. OR REPLACE specifies the view is to replace an existing view if present.
2. FORCE specifies the view is to be created even if the base tables don't exist.
3. NOFORCE specifies the view is not to be created if the base tables don't exist; NOFORCE is the default.
4. alias_name specifies the name of an alias for an expression in the subquery.
5. There must be the same number of aliases as there are expressions in the subquery.
6. subquery specifies the subquery that retrieves from the base tables.
7. If you've supplied aliases, you can use those aliases in the list after the SELECT clause.
8. WITH CHECK OPTION specifies that only the rows that would be retrieved by the subquery can be inserted, updated, or deleted.
9. By default, rows are not checked that they are retrievable by the subquery before they are inserted, updated, or deleted.
10. constraint_name specifies the name of the WITH CHECK OPTION or READ ONLY constraint.
11. WITH READ ONLY specifies that rows may only read from the base tables

1. Create view with the employee who is working in the deptno 20.

create view dept20 as select * from emp where deptno=20;

2. Create view with empno,ename,sal of the employees.

create view emp_data as select empno,ename,sal from emp;

3. Create view with empno,ename,sal of the employees with the annual salary.

create view emp_annual(eno,ename,sal,a_sal) as select empno,ename,sal,sal*12 from emp;

Creating a View with a CHECK OPTION Constraint

create view emp_sal as select empno,ename,job,sal from emp where sal>=3000 with check option constraint emp_sal;