Lesson 24 : UNION,INTERSECT and MINUS

SET OPERATORS

You can combine multiple queries using the set operators UNION, UNION ALL, INTERSECT, and MINUS. All set operators have equal precedence. If a SQL statement contains multiple set operators, then Oracle Database evaluates them from the left to right unless parentheses explicitly specify another order.

UNION

UNION query is to combine the result sets of two or more SELECT queries. It eliminates duplicate rows between those SELECT query result sets.
Note : Each SELECT quries within the UNION query must have the same number of columns in the result sets with similar data types.

Syntax :

SELECT col1,col2, .... FROM <tablenames> UNION SELECT col1,col2, .... FROM <tablenames>;

Examples :

SQL> SELECT * FROM emp where dept=10 UNION SELECT * FROM emp where dept=20;

SQL> select * from hr.emp UNION select * from sales.emp;

Here, it combines emp tables from hr and sales schemas. Note that, need adequate privileages to
select records from other schemas.

Example to cobines 3 select queries.

SQL> select name from hr.emp UNION select name from sales.emp UNION select name from purchase.emp;

UNION ALL

The difference between UNION and UNION ALL is,

UNION - Eliminates duplicate rows exists between SELECT querie, And also sorts the records.
UNION ALL - Do NOT eliminates dublicate rows.


INTERSECT

INTERSECT query also combines result set of two or more SELECT quries but retuns only COMMON rows amoung result sets.

Note : Each SELECT quries within the INTERSEC query must have the same number of columns in the result sets with similar data types.

Syntax :

SELECT col1,col2, .... FROM <tablenames> INTERSECT SELECT col1,col2, .... FROM <tablenames>;

Examples :

SQL> SELECT * FROM emp where dept=10 INTERSECT SELECT * FROM emp where dept=20;

SQL> select * from hr.emp INTERSECT select * from sales.emp;

Here, it combines emp tables from hr and sales schemas but returns only COMMON records between two tables.

Note that, need adequate privileages to select records from other schemas.

Example with 3 select quuries.

SQL> select name from hr.emp INTERSECT select name from sales.emp INTERSECT select name from purchase.emp;

MINUS

MINUS returns all rows in the first query that are not returned by the second query.

Note : Each SELECT quries within the MINUS query must have the same number of columns in the result sets with similar data types.

Syntax :

SELECT col1,col2, .... FROM <tablenames> UNION SELECT col1,col2, .... FROM <tablenames>;

Examples :

SQL> SELECT * FROM emp where dept=10 MINUS SELECT * FROM emp where dept=20;

SQL> select * from hr.emp MINUS select * from sales.emp;

Restrictions on the Set Operators The set operators are subject to the following restrictions:

• The set operators are not valid on columns of type BLOB, CLOB, BFILE, VARRAY, or nested
table.

• The UNION, INTERSECT, and MINUS operators are not valid on LONG columns.

• If the select list preceding the set operator contains an expression, then you must provide a column
alias for the expression in order to refer to it in the order_by_clause.

• You cannot also specify the for_update_clause with the set operators.

• You cannot specify the order_by_clause in the subquery of these operators.

• You cannot use these operators in SELECT statements containing TABLE collection expressions.

Lesson 23 : VIEWS

Views

Views are virtual tables and also called as logical tables. It consists  the data of one of more tables which are called base tables. Views can be based on actual tables or another view also.

When we perform INSERT,UPDATE,DELETE operations on views, it actually affect the base table of the view. You can treat views same as any other table.

Note : It does not consume any space in the database as it always refers its base tables.

Syntax :

CREATE  OR REPLACE VIEW <viewname>  AS  <select query>;

Example :

SQL> create view sample_view as select * from emp where location=’NEW YORK’ ;

Here the sample_view is created which refers all employee data from emp table with their location is NewYork.

Example 2 :

SQL> create view cs_students as select * from students_master where branch='COMPUTER SCIENCE'

DROP VIEW

Syntax :

DROP VIEW <view name>;

Example :

SQL> drop view sample_view;

The following are the restrictions on performing DML operations in any view,

If a view is defined by a query that contains SET or DISTINCT operators, a GROUP BY clause, or a group function, then rows cannot be inserted into, updated in, or deleted from the base tables using the view.

If a view is defined with WITH CHECK OPTION, then a row cannot be inserted into, or updated in, the base table (using the view), if the view cannot select the row from the base table.
If a NOT NULL column that does not have a DEFAULT clause is omitted from the view, then a row cannot be inserted into the base table using the view.
If the view iscreated by using an expression, such as DECODE, then rows cannot be inserted into or updated in the base table using the view.

Examples :

The following are views inwhich we can not perform DML operations

SQL> Create view trans_view as select trans_id, count(*) from master group by trans_id;

SQL>  create view  atm_list as select distinct atm_details from bank_master;
 

Lesson 22 : Synonym and public synonym

A synonym is an alternative name for any existing  objects such as tables, views, sequences,stored functions, stored procedures, packages and other existing database objects.

Syntax :

CREATE SYNONYM <synonym name> FOR schema.objectname ;

Example :

SQL> CREATE SYNONYM emp FOR  scott.employee_master ;

With REPLACE option

Syntax :

CREATE OR REPLACE SYNONYM <synonym name> FOR schema.objectname ;

REPLACE option allows you to recreate the existing synonym  without using DROP synonym command.

Example :

SQL> CREATE  OR  REPLACE SYNONYM emp FOR  scott.employee_master ;

PUBLIC SYNONYM

Public synonyms are available to all users or schemas in the database.

Syntax :

CREATE OR REPLACE PUBLIC SYNONYM <synonym name> FOR schema.objectname ;

Example :

SQL> create public synonym compute_tax for emp.tax_calculation;

Here, compute_tax is synonym for the procedure tax_calculation which is available under emp schema.

DROP SYNONYM

To elimanate existing synonym from database.

Syntax :

DROP SYNONYM <synonym name>;

Example :

SQL> drop synonym compute_tax ;

Note : It just drops synonym but not the referenced object.

Lesson 21 - Sequence

sequence

A sequence is a database object from which multiple users can generate unique sequence of numbers.

Syntax :

CREATE SEQUENCE <sequence name>  INCREMENT BY <integer>  START WITH <starting integer> MAXVALUE <upper limit integer> (or NOMAXVALUE)  MINVALUE <integer> (or NOMINVALUE)  CYCLE or NOCYCLE CACHE <integer> (or NOCACHE) ORDER or NOORDER

Sequence name - is the name of the sequence to be created.

INCREMENT BY – This optional parameter, specifies the interval between sequence numbers. This value can be any positive or negative, but it cannot be zero. If this value is negative, then the sequence descends. If the increment is positive, then the sequence ascends. If you omit this clause, the interval is 1.

START WITH - specifies the first sequence number to be generated.

CYCLE - specifies that the sequence continues to generate values after reaching maximum value. This is optional parameter.

CACHE - specifies how many values of the sequence to be kept in memory. It ensures  faster access. This is optional parameter.

ORDER - guarantees that sequence numbers are generated in order of request. You may want to use this option if you are using the sequence numbers as timestamps This is optional parameter.

We can  retrieve sequence values in SQL statements with following pseudo columns :

CURRVAL - Teturns the current value of the sequence

NEXTVAL - increments the sequence and returns the new value.

Example

         CREATE SEQUENCE sampleseq

              INCREMENT BY 2 START WITH 10

 

SQL>SELECT sampleseq.nextval from dual;

 

   NEXTVAL

----------

        10

 

SQL>SELECT sampleseq.nextval from dual;

 

   NEXTVAL

----------

        12

 

SQL>SELECT sampleseq.currval from dual;

 

   CURRVAL

----------

        12

 

SQL>SELECT sampleseq.currval from dual;

 

   CURRVAL

----------

        12

 

SQL>SELECT sampleseq.nextval from dual;

 

   NEXTVAL

----------

        14

 

 

DROP SEQUENCE

 

-         To remove sequence from database.

 

Syntax :

 

DROP SEQUENCE <sequencename> ;

 

Example :

 

 

SQL>drop sequence sampleseq;

 

Sequence dropped.

 

Constraints

Constraints:

Constraints are useful for preventing the invalid data and also for data security. Constraints are as follows…

• Primary Key Constraint.
• Foreign key ( aka. reference key)
• Not NULL
• Unique
• Check

Not NULL:

As name says, this constraint will not allow the column as empty column. If any column define as NOT NULL constraint that column should contain the data. If we try to insert NULL. It will throw an error that states “Integrity constraint error: NOT NULL constraint violated”.

We can add this constraint my modifying the table (alter table modify….) but, that column should not have null values.

Note: Space is NOT a null value.

Unique:

Unique constraint will not allow duplicate value in the column. But, it will allow null values. (‘N’ number of null values).

We can add this constraint my modifying the table (alter table modify….) but, that column should not have duplicate values.

Check

While inserting the record, using check constraint we can check the data before insert. It will easy to understand in example.

 

Primary key

Primary key is a combination of NOT NULL and UNIQUE constraint key. This also called as parent key. The column defined as PRIMARY will not allow NULL values and also duplicate values.

We can add this constraint my modifying the table (alter table modify….) but, that column should not have null and duplicate values.

 

We can’t drop the primary column or table contains primary column before disable or drop foreign key constraint column or table contains foreign key defined table.

If we tried to drop the table or column we will get the error.

 

Foreign key

Foreign key is same like as unique constraint. It will not allow the duplicate values but, will allow the null values. This also called as a reference key. The data which are in the reference key column should have the same value as PRIMARY key. We can’t insert the values which are not in primary column.

We can add this constraint my modifying the table (alter table modify….) but, that column should not have duplicate values and also data should be available in primary key.

Characteristics:

Any time we can “enable” or “disable” the constraints but, whenever we are doing that, all criteria should be valid for the constraints.

Example for creating table:

Create table Arun (Id number (10) constraint pk_id Primary key,

Name Varchar2 (32) constraint NOT NULL,

age number (2) constraint age_check check (age>18) ,

contact_number number (12) constraint unique);

Create table Vars (Id number(2) constraint fk_id references arun(id));

Lesson : 19 - Character type Functions

1) ASCII

Syntax :

ASCII( string)

Returns ASCII values of first character of given string. ASCII stands for Americal Standard Code For Information Interchange.

Examples :

SQL>select ASCII('A') from dual;

65

SQL>select ASCII('America') from dual;

65

SQL>select ASCII(5) from dual;

53

2) CHR

Syntax :

CHR(ASCII value)

Retuns character equivalent to given ASCII values.

Exmaples :

SQL>select CHR(65) from dual;

A

SQL>select CHR(75) from dual;

K

3) CONCAT

Syntax :

CONCAT(string1, string2)

CONCAT returns String1 concatenated with String2. Both String1 and String2 can be any of the data types CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. The string returned is in the same character set as char1. Its data type depends on the data types of the arguments.

If one of the arguments is a national data type, then the returned value is a national data type. For example:

•CONCAT(CLOB, NCLOB) returns NCLOB

•CONCAT(NCLOB, NCHAR) returns NCLOB

•CONCAT(NCLOB, CHAR) returns NCLOB

•CONCAT(NCHAR, CLOB) returns NCLOB

Examples :

SQL>select concat('Good','Work') from dual;

CONCAT('

--------

GoodWork

SQL>select concat('Employee name is : ', First_name) "Example " from emp;

Example

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

Employee name is : Arun

Employee name is : Sri

4) INITCAP

Syntax :

INITCAP (char type data)

Returns first letter of each word in uppercase, all other letters in lowercase.

Examples :

SQL>select initcap('This is nothing but testing') from dual;

INITCAP('THISISNOTHINGBUTTE

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

This Is Nothing But Testing

5) LOWER

Syntax :

Lower(char type data)

Returns lower case of given string.

Examples :

SQL>select lower('TESTING LOWER') from dual;

LOWER('TESTIN

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

testing lower

SQL>select lower('Lary Wills') from dual;

LOWER('LAR

----------

lary wills

SQL>select lower(first_name) from emp;

LOWER(FIRST_NAME)

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

arun

sri

kamesh

6) INSTR

Syntax :

INSTR(string1, substring, starting position, nth occurence)

Search the substring in string1 and returns the starting position of substrong in the string1.

3rd parameter is optional, if it is specified, it denotes search starting position in the string1

4th parameter is also optional, if it mentioned, it denotes nth occurance , where n is positive number.

Examples :

SQL> select instr('Welcome to world of oracle','o') from dual;

5

SQL> select instr('Welcome to world of oracle','o',1) from dual;

5

SQL> select instr('Welcome to world of oracle','o',7) from dual;

10

SQL> select instr('Welcome to world of oracle','o',1,2) from dual;

10

SQL> select instr('Welcome to world of oracle','o',1,3) from dual;

13

7) LENGTH

Syntax :

LENGTH (char type data)

Returns length(number of characters) in given string.

Examples :

SQL> select length('oracle') from dual;

6

SQL> select length('oracle test') from dual;

11

Note : It counts space also.

8) LPAD

Syntax :

LPAD( string1, length, string to use pad )

It pads left side of the string1 with third parameter. 2nd parameter is the total length.

3rd parameter is optional and default is space.

Examples :

SQL> select lpad('Oracle',3) from dual;

Ora

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

Oracle

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

****Oracle

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

*$*$Oracle

SQL> select lpad(12345,10,'#') from dual;

#####12345

9) LTRIM

Syntax :

LTRIM( string1, string2 )

- Removes space in the left side of string1 if 2nd parameter is not specified.

string1 is the string to trim the characters from the left-hand side.

string2 is optional paramers, is the string that will be removed from the left-hand side of string1. If this parameter is omitted, the ltrim function will remove all leading spaces from string1.

Example :

SQL> select ltrim(' SQl*Plus') from dual;

SQl*Plus

SQL> select ltrim(' SQl*Plus','*') from dual;

SQl*Plus

SQL> select ltrim('*****SQl*Plus','*') from dual;

SQl*Plus

SQL> select ltrim('$234.50','$') from dual;

234.50

10) REPLACE

Replaces a sequence of characters in a string with another set of characters

Syntax :

REPLACE( string1, string_to_replace, string used for replace )

3rd parameter is optional.

Example :

SQL> select replace('abcdabcd','b') from dual;

acdacd

SQL> select replace('abcdabcd','b','x') from dual;

axcdaxcd

11) RPAD

Syntax :

LPAD( string1, length, string to use pad )

It pads right side of the string1 with third parameter. 2nd parameter is the total length.

3rd parameter is optional and default is space.

SQL> select rpad('Oracle',10) from dual;

Oracle

SQL> select rpad('Oracle',10,'*') from dual;

Oracle****