Oracle SQL

Introduction to SQL

Structured Query Language

SQL is a database language used to create, manipulate and control the access to the Database objects. SQL is a non procedural language used to access relational databases. It is a flexible, efficient language with features designed to manipulate and examine relational data.

SQL is only used for definition and manipulation of database objects. It cannot be used for application development like form definitions, creation of procedures etc...For that you need to necessarily have some 3gl languages such as cobol or 4gl languages such as Dbase to provide front-end support to the database.

Key features of SQL are

• Non procedural language
• Unified Language
• Common language for all Relational databases. ( Syntax may change between different RDBMS )

SQL is made of Three sub-languages such as

• Data Definition language (DDL)
• Data Manipulation language (DML)
• Data control language (DCL)

Data Definition Language (DDL)
allows you to define database objects at the conceptual level. It consists of commands to create objects and alter the structure of objects, such as tables, views, indexes etc.. Commonly used DDL statements are CREATE, DROP etc..

If you want to create a table Student,then use the following syntax

CREATE TABLE student(
    student_id INTEGER PRIMARY KEY,
    student_nm VARCHAR(30),
    course_id VARCHAR(15),
    phone VARCHAR(10),
    address VARCHAR(50) 
);

To drop a table from the database

DROP TABLE student;

Data Manipulation language(DML)
Allows you to retrieve or update data within a database. It is used for query, insertion, deletion and updating of information stored in databases. Eg: Select, Insert, Update, Delete.

Select statement
Select statement in SQL language is used to display certain data from the table.For example:- if you want to know what course Jim is taking; Select statement fetches you the information you want,when you use the information you have. So,in the above scenario the information you have is student_nm as Jim and and the information you want is course_id, the intersection of those two columns in that table is what you are looking for.

SELECT (what you want) FROM (which tables) WHERE (what you have )

Now the select statement to know the course_id Jim looks like this:

SELECT course_id
FROM student
WHERE student_nm = 'JIM'

SELECT course_id
FROM student
WHERE student_nm = 'JIM'

You will get the result as:

If you want to see all the rows in the table then your select will be:

SELECT * 
FROM student;

If you would like to show student_nm and address who is attending Oracle course in the form of a report then your select will look like:

SELECT student_nm, address
FROM student
WHERE course_id = 'Oracle'

The result will be

Insert Statement
Insert statement is used to insert a new row into the table. For example:- If a new student DAVE is joining Java course then,use the INSERT SQL statement.

INSERT INTO student(
  student_id, 
  student_nm, 
  course_id, 
  phone, 
  address
  ) 
VALUES
(
  1004, 
  'DAVE', 
  'Java','972-912-4008', 
  '567, Washington Ave, Dallas - 75543' 
);

after executing the insert statement,your table should look like below when you issue a select from student table:

Update Statement
is used to change the existing information in the table.For example:-If DAVE moved to another address then we need to change the ADDRESS column for DAVE's record.If the new address is 146, Dallas Parkway, Dallas - 75240 then your update should be:

UPDATE student 
SET address = '146, Dallas Parkway, Dallas - 75240'
WHERE student_nm = 'DAVE'

In order to make sure you updated the Address column for DAVE issue following SQL

SELECT * 
FROM student 
WHERE student_nm = 'DAVE'

then you should see the following result

Delete Statement
is used to delete a row from the table ie remove records from the table.For example:JAMES moved to different city, and he does not want to take the course.In order to remove JAMES's record from the table we use the DELETE statement

DELETE student
WHERE student_nm = 'JAMES'

once you delete the record and you select all the information from the student table you should see the following information:

If you dont include where clause in delete statment then it will remove all the rows from the table.

Data control language(DCL)
In RDBMS one of the main advantages is the security for the data in the database. You can allow some user to do a specific operation or all operations on certain objects. Examples for DCL statements are GRANT, REVOKE statements.

GRANT is used to Grant a permission to an user so that the user can do that operation. REVOKE is used to take back that permission from that user on that object.

For example we have two users JAMES and DAVID If JAMES created a table called ITEMS then JAMES becomes the owner of that table. DAVID cannot access ITEMS table because he is not the owner of that table. DAVID can access ITEMS if JAMES gives the permission on his table. JAMES can give different types of access like Select, Update, Delete and Insert on ITEMS table to DAVID.

For example
If JAMES wants to provide only Select on ITEMS to DAVID then he can issue:

GRANT SELECT ON ITEMS TO james

If JAMES wants to provide only Select and Insert on ITEMS to DAVID then he can issue: GRANT SELECT, INSERT ON ITEMS TO JAMES

If JAMES wants to provide all the operations on ITEMS to DAVID then he can issue:

GRANT ALL ON ITEMS TO james

Once you provide all permissions on an object to an user then indirectly he becomes the owner and can do any manipulation to the table.

Oracle Datatypes

Data in a database is stored in the form of tables. Each table consists of rows and columns to store the data. A particular column in a table must contain the same type of data.For example:

Every column has certain information, PLAYER_NAME is a char column. DATE_OF_BIRTH is a Date column, ROOM_NO is a number column.

Different datatypes available in Oracle database

CHAR: To store character type of data,for example: name of a person (you can save any thing in character field)

VARCHAR: Same as CHAR. The only difference between CHAR and VARCHAR is the way the database saves the data.

To understand the difference better we will take the following example.

CREATE TABLE employee(
  emp_no NUMBER(4), 
  ename CHAR(15)
);

As Ename column defined as CHAR(15) every value you put it that column will occupy all 15 bytes ie CLARK is 5 bytes string,so the database pads 10 spaces.

CREATE TABLE employee(
  emp_no NUMBER(4), 
  ename VARCHAR(15)
);

So what are the advantages and disadvantages?.The thumb rule here is that if you are using a char column as primary key then it better be a char field. If you are using a column to have comments then you must use varchar.

NUMBER: Used to store the numbers, for example:If you want to store employee numbers then you define the column's data type as number. If you want to define a column to store currency then you can define the column as NUMBER(7,2).

DATE: Used to store the date,like Date of birth of a person, join date in a company etc.

LONG: to store the variable char length.

RAW:

LONG RAW: store binary data of variable length.

LOB: Large objects to store binary files. In addition oracle 8 supports CLOB, BLOB and BFILE

CLOB: A table can have multiple columns of this type.

BLOB: can store large binary objects such as graphics, video and sound files.

BFILE: stores file pointers to LOB managed by file systems external to the database.

Constraints

When you bind a business rule to a column in the table then those rules are called the Constraints. Constraints are defined while creating the table. Say for example, you cannot have an employee who does not have a name, then employee name column in employee table should be a NOT NULL column. The NOT NULL is a constraint.

The following table shows the constraint types and short descriptions.

NOT NULL
If we have a business rule saying that all customers should have a name, we cannot have any customer with out a name. So to implement that business rule we can create customer table and specify customer name column as NOT NULL (constraint) **Example

CREATE TABLE employee(
  empno NUMBER(4) PRIMARY KEY, 
  ename VARCHAR(4) NOT NULL
);

CHECK
Check constraint is used where we define a condition on a column. Check constraint consists of the keyword

col_name datatype CHECK (col_name in(value1, value2))

Example
If you have a business rule saying that all employees in the organization should get atleast $500 then we can use CHECK constraint while creating table.

CREATE TABLE employee(
  empno NUMBER(4) PRIMARY KEY, 
  ename VARCHAR(4) NOT NULL, 
  salary NUMBER(7,2) CHECK (salary > 500) 
);

DEFAULT
While inserting a row into a table without giving values for every column, SQL must insert a default value to fill in the excluded columns, or the command will be rejected. The most common default value is NULL. This can be used with columns not defined with a NOT NULL.

Default value assigned to a column while creating the table using CREATE TABLE operation.

Example

CREATE TABLE item(
  item_id NUMBER(4) PRIMARY KEY, 
  item_name VARCHAR(15),
  item_desc VARCHAR(100), 
  qoh NUMBER(4) DEFAULT 100
);

Assigning a default value 0 for numeric columns makes the computation.

PRIMARY KEY
Primary Key in a table is a unique identifier of a row. For example,if you are maintaning the customer profiles, you should assign particular number to each one. So customer_number should be defined as a Primary key in Customer table.

REFERENCES
is a Foreign key. A foreign key column value refers a column in another table to check whether the value exists or not.

UNIQUE
The values entered into a column are unique ie no duplicate values exists.This constraint ensures business that there is no duplicates allowed.

Data Definition Language

It's a part of SQL langugae which creates a database object. Examples of database objects are tables, procedures, functions, packages etc. When you create a table or drop a table you are modifying the structure of the database and that is the reason why it is called data definition language. When you issue a create or alter or drop sql statements database internally does a commit,and that is why we cannot include the DDL as part of the transaction.Following are a few DDL statements.

CREATE TABLE course (
  course_id NOT NULL NUMBER(5) PRIMARY KEY,
  course_name NOT NULL VARCHAR(30),
  start_date DATE
);

ALTER TABLE course MODIFY (start_date NOT NULL DATE);

ALTER TABLE course ADD ( instructor_id NULL );

DROP TABLE course

CREATE TABLE course(
  course_id NOT NULL PRIMARY KEY, 
  course_name VARCHAR(30), 
  start_Date DATE 
) 
TABLESPACE=course_info STORAGE (INITIAL 1024k NEXT 1024 PCTINCREASE=10);

Data Manipulation Language

Data Manipulation in RDBMS means maintaining the data in the database. There are three DML statements:Insert,Update and Delete. INSERT statment is used to insert a new record into a table. The UPDATE statement is used to change the existing information of a table. The DELETE statement is used to remove certain information from the table.

We will take an example here:If you are running an apartment complex where you rent apartments,the day to day record maintenance would look like this.

INSERT Statement

If a person named JAMES rented an apartment,we need to add his information into the table. We have to do an INSERT because the information does not exist in the table as of now.The following information has to be entered into the database:-name = JAMES aptno = 891, home_phone as 676-789-9011, work_phone as 777-567-1234, apt_rent = 880 and no_of_pets as 1.

So now how we can write the INSERT statement.

INSERT into TENANT(
  tenant_id, 
  aptno, 
  tenant_name, 
  home_phone, 
  work_phone, 
  apt_rent, 
  no_of_pets
  )
VALUES
(
  1003, 
  891, 
  'JAMES',
  '676-789-9011',
  '777-567-1234', 
  880, 
  1 
);

After executing the insert statement the table now should have four rows as shown below

Following shown are the different syntaxes available INSERT SQL syntaxes.

Syntax1

INSERT INTO table_name (col1, col2, col3....) VALUES (value1, value2, value3.....)

In the syntax 1 we need to specify the column names of a table and values respectively. In the application development its more recommened to use this syntax while doing inserts into the table, reason being if you added a column in the table then it wont give an error except the value for that column wont be supplied and program will run fine.

Syntax2

INSERT INTO table_name VALUES ( value1, value2.....)

In the Syntax 2 we wont specify the column names and pass all the values to the columns respectively.

Syntax3

INSERT INTO table_name (col1, col2, col3...) SELECT col1, col2, col3........ FROM table

In the Syntax 3 we can insert multiple rows using one INSERT into statement where as in Syntax 1 and Syntax 2 you can insert only one row at a time.

UPDATE Statement

Now we will go the next DML statement UPDATE. Update is used to change the existing value in a column of a table. As JAMES work_phone number changed to 765-123-9087 from 777-567-1234 then we need to change that information in JAMES record in the table.

UPDATE tenant 
SET work_phone = '765-123-9087'
WHERE tenant_name = 'JAMES'.

After executing theUPDATE statement the table now should have four rows as shown below.

Syntax

UPDATE (table_name)
SET (colname1 = Value1, colname2 = Value2.......)
[WHERE clause]

If you wont include WHERE clause in your UPDATE statement then it will update all the rows in the table, so you should be very careful when you are writing UPDATE statements in work.

DELETE Statement

SMITH moves out of the apartment complex, so now we do not need to have his information in the table. You can use DELETE Sql statement.

DELETE tenant
WHERE tenant_name = 'SMITH'

After executing the DELETE statement the table now should have three rows as shown below.

Syntax

DELETE FROM (table_name) [WHERE clause]

If you wont include WHERE clause in your DELETE statement then you delete all the rows in the table, so you should be very careful when you are writing DELETE statements in work.

Some of the examples of INSERT, UPDATE and DELETE statements.

Insert SQL examples

Example 1

INSERT INTO books( 
  book_id, 
  book_nm, 
  author, 
  price 
) 
VALUES(
  234, 
  'Oracle', 
  'Smith', 
  45 
);

Example 2

INSERT INTO books 
VALUES ( 235, 'C++','Austin', 50);

Example 3

INSERT into books(
  book_id, 
  book_nm, 
  author, 
  price 
)
SELECT book_no, book_name, author_name, book_price FROM legacy_books
WHERE author_name = 'BILL';

Update SQL Examples

Example 1

UPDATE books SET book_nm = 'C++ for Experts'

Example 2**

UPDATE books 
SET book_nm = 'Oracle'
WHERE book_no = 103

Example 3

UPDATE BOOKS 
SET price = price - 5
WHERE author IN ( SELECT author FROM authors WHERE state = 'CA')

Example 4

UPDATE BOOKS 
SET price = price - 2
WHERE EXISTS ( SELECT author FROM auhtors WHERE books.author = author.author )

DELETE SQL Examples

Example 1

DELETE books

Example2

DELETE books 
WHERE book_no = 235

Example 3

DELETE books 
WHERE author IN ( SELECT author FROM authors WHERE state = 'TX')

Create a table called PATIENT so that we can do Data manipulation like INSERT, UPDATE and DELETE statements.

Patient_id Number(4) Primary Key Patient_name Varchar(35) Not Null, Primary_doctor Number(4) Foreign Key, Patient_dob Date Not Null, Patient_phone Char(10) NULL

Using INSERT statements insert the following rows into PATIENT table.

PATIENT _ID PATIENT_NAME PRIMARY_DOC PATIENT_DOB PATIENT_PHONE 1500 SMITH ABDUL 10/10/1964 312-896-9632 1501 KTMAN JON 02/02/1960 312-666-1478 1502 WATER ABDUL 03/03/1955 312-885-9632 1503 MARINO JON 09/02/1975 312-555-7412 1504 DAWKINS DUPOINT 05/07/1978 312-951-7532 Change the patient name SMITH to RODMAN whose dob is 10/10/1964 and primary doctor is ABDUL.

Change the phone number of WATER from 312-666-1478 to 312-567-8988.

Delete patient SMITH from the PATIENT table.

NULL Values

According to CODD's rule any RDBMS should support NULL value.

What is a null value?

Its a unknown value or an undefined value. How you will insert a NULL value into table.

For example if you have a table called APT_ENQUIRY with the following structure.

If you see the address2 column for MARK there is no value, that is NULL value. How you will insert a null value when its undefined. If you omit the column name in your insert statement while inserting a row then that column will have a NULL value, you cannot omit the not null column from the insert statement.

Considerations while dealing with NULL's.

NULL value is different from simply assigning a column the value 0 or a blank.

It cannot compared using the relational or logical operators.

select * from apt_enquiry where address2 = null - This is wont fetch any rows.

select * from apt_enquiry where address2 is null - This is right.

select * from apt_enquiry where address2 != null - This is wrong.

select * from apt_enquiry where address2 is not null - This is right

Data Retrieval I

Select Statement

is the powerful SQL Command we use the most in the database activity. Select statement is used to retrieve the data from the tables.

Employee Table with data (Following examples and selects based on the following table (EMP))

Syntax

SELECT col_name, col_name.................
FROM table_name
WHERE condition

Selecting all columns

We can select all the columns from a table using * operator in SELECT statement.

SELECT * \
FROM emp;

Displays all the rows from the emp table. Usually we can write this sort of select in the development environment, we should not write this sort of select in the production environment.

Selecting particular columns.

We can select particular columns from a table. Suppose if we want to select empno, ename and sal column values from the EMP table then we can write the SELECT as follows.

SELECT empno, ename, sal 
FROM emp;

Column Aliases

Usually if we select a column from a table then the column heading is same as the column name, if we want to change the column header for display purpose then we have to use Aliases for the column names. If the alias includes the space in it then we should include with in the double quotes.

SELECT empno "Employee Number", ename "Employee Name", sal Salary
FROM emp

Specific Rows

If we want to display all employee numbers and names who works in deptno 10 then how we should write the select. Here we need to display empno, ename so the columns in SELECT clause is empno, ename. In the FROM clause we need to specify the table name ie EMP. What is the condition? needs to display the employees works in deptno 10. So we need to write the WHERE clause in the SELECT. Here we are selecting specific rows with in the table. So our Select statement will be

SELECT empno, ename
FROM emp
WHERE deptno = 10;

Ordering Rows

If we want to display the result set in an order then we include the ORDER BY CLAUSE in the Select statement. Display the employee names, salary information and sort the employee names alphabetically.

SELECT ename, sal
FROM emp
ORDER BY ename;

Suppose we want to display the result set by salary in descending order then

SELECT ename, sal
FROM emp
ORDER BY sal DESC;

By default the order by is ASC ie asending.

Expressions in Select statement

In order to get the sum of salary and commission we need to add two columns ie sal and comm. So you can manipulate in the Select statement itself.

SELECT ename, sal, comm, sal + comm "Total"
FROM emp;

If comm column is null then if we add sal to it, it ends up with a null value. So we can use NVL function.

SELECT ename, sal, comm, sal + nvl(comm,0) "Total"
FROM emp;

If you want to display all the employees who has their employee numbers as even number.

SELECT empno
FROM emp
WHERE mod(empno,2) = 0

Concatinating Strings

Suppose if we want to display the employee name and salary information as int the follwing format JONES works in deptno 10

then in the above shown format JONES is ename column and 10 is deptno column from emp table. In the JONES works in deptno 10, the highlighted text should get repeated for all the rows then we should concatenate the ename information with the deptno value. To concate the two values in SQL you can use || or CONCAT function.

SELECT ename || ' works in deptno ' || deptno
FROM emp

or

SELECT ename CONCAT 'works in deptno' CONCAT deptno
FROM emp

Understanding Joins

SIMPLE JOINS

To demonstrate the Joins between two tables in SQL, we are going to take the following two tables called STATE and CITY. In STATE table STATE_CD is the primary key and in CITY table STATE_CD and CITY_NAME makes the primary key.

STATE table

CITY table

We can join two tables if there is a common column exists in both. In the above example we have a common column called STATE_CD, so we can percorm join on these tables.

Say you want to display the CITY_NM, POPULATION, STATE_DESC from the above two tables.

Here we go, CITY_NM and POPULATION columns exists in CITY table, STATE_DESC column is in STATE table. We are going to show information from two different tables, so in the FROM clause of SELECT statement we should include two tables.

SELECT CITY_NM, POPULATION, STATE_DESC

FROM CITY, STATE

WHERE CITY.STATE_CD = STATE.STATE_CD;

The result for the above query

How this JOIN works. Each database engine chooses the different ways to process the result set. To understand the JOIN concept better we will take the above example. In the FROM clause we have CITY, STATE tables, so it merges the rows from two tables and makes it as one temporary table in the server memory area, so it will be like

Now the WHERE clause applies on the above shown temporary table. According to the WHERE clause in our select statement state_cd in city table should be equal to state_cd in state table ie (CITY.STATE_CD = STATE.STATE_CD). To show the result set we have to select those records in which state_cd is equal. So in the following table all the records we marked in black is selected and those one marked in reds are discarded as the state_cd is different. This is the way it will filter the un wanted records when you do a join.

So after filtered the un wanted records the work table looks like

What are the columns we have in our SELECT statement those are the only columns that will be in our result set, all other columns will get discarded at this final time.

That is how the result set will get processed inside the database engine.

Data Retrieval II

Querying Multiples Tables

Joins are used to combine columns from different tables. With joins, the information from any number of tables can be related. In a join, the tables are listed in the FROM clause, separeated by commas.The condition of the query can refer to any column of any table joined. The connection between tables is established through the WHERE clause. Based on the condition specified in WHERE clause, the required rows are retrived.

Following are the different types of joins

• Equi Joins
• Cartesian Joins
• Outer Joins
• Self Joins

Equi Joins

When two tables are joined together using equality of values in one or more columns, they make and equi join. Table prefixes are utilized to prevent ambiguity and the WHERE clause specifies the columns being joined.

Example
List the employee number, employee name, department number and department name.

See the information we want in this example. We can get Employee number, Employee name, Department information from employee table but department name exists in department table, so to get all the information in one Select we should join two tables and join with a common column between two tables(where clause), here deptno column is the common column between emp and dept tables.

SELECT empno, ename, emp.deptno, dname
FROM emp, dept
WHERE emp.deptno = dept.deptno

Cartesian Joins

If you are selecting information from more than on table and if you did not specify the where clause, each row of one table matches every row of the other table ie Cartesian Join.

If you have a table TAB1 which has 25 rows, TAB2 which has 10 rows then, if you join these two tables with out where cluase then you get 25 * 10 ( 250 ) rows as the result set.

Cartesian products is useful in finding out all the possible combination of columns from different tables.

Outer Joins

If there are any values in one table that do not have corresponding values in the other, in an equijoin that row will not be selected. Such rows can be forcefully selected by using the outer join symbol (+). The corresponding columns for that row will have NULLs.

Where you will use the Outer Join. For example we have employee and department tables. In department table deptno is the primary key, in employee table deptno exists and its a foreign key. By rule you cannot have a deptno in employee table if it does not exists in dept table, ie the primary and foreign key concept. So we can have a department record and there is no employee in the related department.

In the emp table, no record of the employees belonging to the department 40 is present. Therefore, in the example above for equi join, the row of department 40 from the dept table will not be displayed

Display the list of employees working in each department. Display department information even if no employee exists in that department.

SELECT empno, ename, dept.deptno, dname, loc
FROM emp, dept
WHERE emp.deptno( + ) = dept.deptno

The outer join symbol (+) can not be used both the sides

Self Join

To join a table to itself means that each row of the tables is combined with itself and every other row of the table. The self join can be viewed as a join of two copies of same table. The table is not actually copied, but SQL performs the command as though it were.

SELECT a.ename, b.name
FROM emp a, emp b
WHERE a.mgr = b.empno

SQL Functions

Built-in Database Functions

Character Functions

• LOWER ( char variable) - Used to show the string in lower case
• UPPER (char variable) - Used to show the string in Upper case
• LTRIM (char variable) - Remove the spaces " " in left side of the string
• RTRIM (char variable) - Remove the spaces " " in right side of the string
• SUBSTR(char variable, m) - Gets you the part of a string
• LENGTH(char variable) - Gives the length of the string
• INSTR(string variable, char) - Gives the position of the char you are searching for
• LPAD
• RPAD
• INITCAP(char variable) - Every first letter in the passed string becomes upper case

Examples for Character Functions

SELECT LOWER('EXAMPLE FOR LOWERCASE') FROM dual;

SELECT LOWER('EXAMPLE FOR LOWERCASE') FROM dual;

SELECT UPPER('example for upper case') FROM dual;

SELECT LTRIM(' left trim example') FROM dual;

SELECT RTRIM('right trim example ') FROM dual;

SELECT SUBSTR('you are correct', 1,7) FROM dual;

SELECT LENGTH('you are correct') FROM dual;

SELECT INSTR('you are correct', 'correct') FROM dual;

SELECT INITCAP('you are correct') FROM dual;

Arithmetic Functions

• ABS (numeric)
• CEIL (numeric)
• FLOOR (numeric)
• MOD
• POWER
• SIGN
• SQRT
• TRUNC
• ROUND

Examples for Arithmetic Functions

SELECT ABS(-9) FROM dual;

SELECT MOD(5,2) FROM dual;

Date Functions

• SYSDATE - Gives the current date.
• ADD_MONTHS ( date variable, number of months to be added to that date )
• MONTHS_BETWEEN ( date variable d1, date variable d2)
• TO_DATE( char variable, date variable)
• LAST_DAY( date variable )
• NEXT_DAY (date variable, day )
• TO_CHAR( date variable, to what format you want )

Examples for Date Functions

SELECT SYSDATE FROM dual;

SELECT TO_DATE('1997 09 24', 'yyyy mm dd') FROM dual;

SELECT MONTHS_BETWEEN( sysdate, to_date('10-24-1994','MM-DD-YYYY') FROM dual;

SELECT ADD_MONTHS( sysdate, 4) FROM dual;

SELECT LAST_DAY( sysdate ) FROM dual;

SELECT NEXT_DAY( sysdate,'monday') FROM dual;

SELECT TO_CHAR(sysdate,'day-month-yyyy') FROM dual;

Group Functions

Group By is used mostly with functions in which the functions produces value for each group.

• Avg
• Sum
• Count
• Max
• Min

Views

Views are logical tables derived from columns of one or more base tables. Data does not actually exist in a view. Data is dynamically derived from the base tables when the view is referenced. A view can extract any subset of rows and columns from one or more underlying tables.

View is a virtual table:- a table that does not exist in its own right but looks to the user as if it did. By contrast, a base table is a real table ,in the sense that for each row of such a table,there really is some stored counterpart of that row in physical storage.

Syntax

CREATE VIEW viewname [(column name,....)] AS subquery

eg:

CREATE VIEW view1(
  author_id,
  last_name,
  first_name
) 
AS 
SELECT au_id, au_lname, au_fname FROM authors

The effect of this statement is to create a new view called view1 with three columns-author_id,last_name,first_name corresponding to au_id,au_fname,au_lname from authors table.If column names are not specified explicitly in the CREATE VIEW, then the view inherits the column names from the source tables of the view.Column names must be explicitly specified for all columns of the view if

1. any column of the view is derived from a function,an operational expression or a constant, and has no name that can be inherited (or)

2. Two or more columns of the view would otherwise have the same name.

Eg:

CREATE VIEW totsales(
  title_id,totqty
) 
AS 
SELECT title_id,sum(qty) FROM titles group by title_id;

CREATE VIEW view2 AS SELECT title_id,qty FROM sales WHERE qty > 100.

Retrieval Operations:

Using SELECT statement, Contents of the view can be viewed.

Eg:

SELECT * FROM view2

SELECT totqty,title_id FROM totsales

Modification of views

You can add a record to the view by inserting a record in the base table. For example, you can insert a record into view2 by adding a record to the table Sales. Take another example.

Create a table table1 with two fields col1 and col2.col1 allows not null and col2 allows null. Create a view view4which will have only col1.Insert a record into view4.Use select statement to display the contents of table1 and view4.

You will find out that table1 will have a new record with a null value of col2.View4 will also include this new record. If you are inserting a new record into the view columns other than those in the table should allow for null values. If they do not allow for null values, then inserting a record to the view is not possible.

If you want to delete a record from the view, you can do so by deleting it from the base table. Similarly, updation of view is possible only through base tables.

Synonyms

Synonyms is nothing but another name for a table in the current database or in other database. Its easier

Per example If user DAVID created a table called ACT_MASTER. If user JON wants to select the values from ACT_MASTER which was created by DAVID then DAVID should grant the permissions to JON. Even after DAVID gives a SELECT permission on the ACT_MASTER to JON, JON cal write the SELECT like SELECT * FROM DAVID.ACT_MASTER

Imaging once this application is used by 500 users then DAVID cannot give all the permissions to each and every one so ORACLE has an object called Synonyms. The use of synonym being we can use just table name instead of username.tablename like DAVID.ACT_MASTER.

Syntax

CREATE SYNONYM name FOR dblink.user.tablename

CREATE PUBLIC SYNONYM name FOR dblink.user.tablename

Synonym Example

CREATE SYNONYM act_master FOR david.act_master

GRANT SELECT ON act_master TO jon

Public Synonym

CREATE PUBLIC SYNONYM act_master FOR david.act_master

GRANT SELECT ON act_master TO PUBLIC

Once you create a Public Synonym we use that in the application programs. In the Application its better to use the synonyms instead of user_name.table_name.

Sequences

Sequence is an object which generates the sequence numbers, first time when you get a value you get 1, next time you get 2, next you get 3..............

Where exactly we use Seq numbers, In the IT industry we can use sequential numbers in many places, for example customer table's primary key is Customer number. We cannot have duplicates on Primary key. So when ever you insert a new row into the customer table we read maximum customer number from the customer table then add 1 to it so that we get next number, using the added number insert a row into the customer table. This way you can get the sequence numbers. Customer Table

In Single User Environment
Lets assume the maximum value in the Customer table is 250. MARK reads maximum value he gets 250. Add 1 to the maximum value (250 + 1) maximum value now is 251 insert a new row into customer so that new customer number is 251.

Every thing is fine, we are good to go.

If the system is a Multi User system then more than one user can do the same process at the same time. Read the following scenario, User1 and User2 were adding new customers.

Lets assume maximum value in Customer table is 300

At 10.00 A.M User 1 is inserting a new customer User 1 reads maximum value (300) User 1 adds 1 to the maximum (300 + 1) maximum value is now 301 User 1 gets information from customer thriugh phone User 1 inserts row at 10.02 A.M (Success insert)

At 10.01 A.M User 2 is inserting a new customer User 2 reads maximum value (300) User 2 adds 1 to the maximum (300 + 1) maximum value is now 301 User 2 gets information from customer thriugh phone User 2 inserts row at 10.03 A.M (fails because of Primary Key violation)

As both users has 301 as the value which they are going to insert into the table, the first one who goes to the server inserts fine the second one will get failed as we are trying to violate the Primary Key concept. In order to make it work we should make sure that we wont send any duplicate values for the primary key column. Sequence provides such a feature, every time user asks the next value it gives the right one.

CREATE SEQUENCE customer_seq
START WITH 100
INCREMENT BY 1
NOCYCLE
CACHE 50

SELECT customer_seq.NEXTVAL FROM dual;

to drop a Sequence

DROP SEQUENCE customer_seq

ALTER SEQUENCE customer_seq
RECYCLE
CACHE 100

Whats this CACHE parameter does is it generates and keeps the next so many numbers in the SGA (System Global Area) so that it dispatches the number to the user fast. A sequence is created through the CREATE SEQUENCE command,

CREATE SEQUENCE seq_name [INCREMENT BY n] START WITH m

seq_name is the name of the sequence. n is the increment specified by the user. The default for INCREMENT BY is 1;the user can specify the increments START WITH is the number with which the sequence will begin seq_name CURRVAL :Returns the current value of sequence. seq_name NEXTVAL :Returns the next value of the sequence.Also increments the value. Examples:

CREATE SEQUENCE empnumber INCREMENT BY 1 START WITH 1190

This command creates a sequence 'empnumber' that can be accessed by insert and update.

Insert into emp(empno,ename,sal) values(empnumber.NEXTVAL,'Hans',6000)

This will insert 1191 into the column empno,as the sequence starts with 1190.After this, the CURRVA will contain 1191.

Insert All

Multi-Table Inserts:

The INSERT ALL statement in Oracle 9i can be used to insert one or more tables with one statement fired. This is explained in the following examples.

Example 1:

INSERT ALL

INTO dept

VALUES (50,`Operations ´,`Bangalore ´)

INTO emp (empno, ename, sal,comm, deptno)

VALUES (1009, ’Sam’, 12000, NULL, 50)

SELECT * FROM dual;

Here, the tables dept and emp are inserted with a row each. For this statement to perform insert, the select statement which is a part of the insert all is mandatory because an insert is performed for each row sel ected. In this example, as we’re selecting from DUAL, both the inserts (into emp and dept) are performed only once.

Example 2:

INSERT ALL

WHEN MOD (deptno, 100) = 0 THEN

INTO dept_log

VALUES (deptno, dname, loc)

WHEN MOD (deptno, 10) = 0 THEN

INTO dept_new

VALUES (deptno, dname, loc)

WHEN MOD (deptno, 10) != 0 THEN

INTO dept_history

VALUES (deptno, dname, loc)

SELECT deptno, dname, loc FROM dept;

Here, the rows are inserted based on the condition specified in the WHEN clause. However, all the conditions here are checked. Inserts are performed for all the WHEN conditions satisfied. If, for a particular row returned by the select, more than one when conditions are satisfied, then the corresponding inserts are performed. So for the value 100 for deptno a row is inserted into dept_log as well as dept_new.

Example 3:

INSERT FIRST

WHEN MOD (deptno, 100) = 0 THEN

INTO dept_log

VALUES (deptno, dname, loc)

WHEN MOD (deptno, 10) = 0 THEN

INTO dept_new

VALUES (deptno, dname, loc)

WHEN MOD (deptno, 10) != 0 THEN

INTO dept_history

VALUES (deptno, dname, loc)

SELECT deptno, dname, loc FROM dept;

Here, INSERT FIRST is used instead of INSTEAD ALL, which, unlike INSTEAD ALL, executes the INTO part only for the first WHEN condition that is TRUE for every row returned by the select.

Example 4:

INSERT ALL

WHEN MOD (deptno, 10) = 0 THEN

INTO dept_new

VALUES (deptno, dname, loc)

ELSE

INTO dept_hist

VALUES (seq.NEXTVAL, dname, loc)

SELECT deptno, dname, loc FROM dept;

As shown above, ELSE can be used in INSERT ALL and in INSERT FIRST too. The ELSE part is executed when no when clauses are satisfied. A sequence may also be used as shown to avoid unique key violations in some cases.

Materialized views

A materialized view is a database object that contains the results of a query.

A materialized view can query tables, views, and other materialized views.

They are also known as snapshots.

Syntax:

CREATE MATERIALIZED VIEW materialized_view_name
[BUILD [DEFERRED|IMMEDIATE]
[REFRESH [FAST|COMPLETE|FORCE]
[ON DEMAND | COMMIT]
[START WITH DATE] [NEXT DATE]
[WITH PRIMARY KEY|ROWID]]
AS select_statement;

[ ] - Optional

Methods:

Oracle provides 2 methods to build the materialized views.

• Immediate
• Deferred

Immediate:

Create the materialized view and then populate it with data.

Eg:

CREATE MATERIALIZED VIEW emp_mat_view
BUILD IMMEDIATE
AS 
SELECT * FROM emp;

Deferred:

Create the materialized view definition but do not populate it with data.

Eg:

CREATE MATERIALIZED VIEW emp_mat_view
BUILD DEFERRED
AS 
SELECT * FROM emp;

Types:

Oracle provides 3 methods to refresh the materialized views.

• Complete
• Fast
• Force

Complete:

When complete refresh occurs in the materialized view, it is first truncated and all the data are reloaded.

Complete refresh is a good technique to use when

• The number of new rows to be inserted is more than 50% of the cardinality of the tables on which the materialized view is based.

• The time required to perform a fast refresh is longer than a complete refresh.

Eg:

CREATE MATERIALIZED VIEW emp_mat_view
REFRESH COMPLETE
AS 
SELECT * FROM emp;

Fast:

The new data loaded into any table in the warehouse is identified and any materialized view referencing that table is automatically updated with the new data.

To identify the new data loaded into the master tables, you need to create the materialized view log on the master tables.

Before creating the materialized views the log should be created first.

Syntax:

1. Create materialized view log on table_name;
2. Create materialized view log on emp;
3. The log should be created based up on the master table.

Eg:

CREATE MATERIALIZED VIEW emp_mat_view
REFRESH FAST
AS 
SELECT * FROM emp;

Force:
When force refresh occurs in the materialized view, will do a fast refresh if it possible (only when the data is changed) otherwise performs a complete refresh. Force refresh is the default.

Eg:

CREATE MATERIALIZED VIEW emp_mat_view
REFRESH FORCE
AS 
SELECT * FROM emp;

Operations:

There are two operations available to refresh the materialized view.

• Commit
• Demand

Commit:

The materialized view will be automatically refreshed or updated or reflected the data once the commit is done.

Eg:

CREATE MATERIALIZED VIEW emp_mat_view
REFRESH FAST ON COMMIT
AS 
SELECT * FROM emp;

Demand:

The materialized view will be refreshed manually by calling the following procedures which is listed below.

Eg:

CREATE MATERIALIZED VIEW emp_mat_view
REFRESH FAST ON DEMAND
AS 
SELECT * FROM emp;

DBMS_MVIEW.REFRESH: For specific materialized views.

DBMS_MVIEW.REFRESH_DEPENDENT: For those materialized view dependent on a table.

DBMS_MVIEW.REFRESH_ALL_MVIEWS: For all materialized views

Timing the Refresh:

The start clause informs the database when to refresh the materialized view.

The next clause specifies the interval when to refresh the materialized view again.

Eg:

CREATE MATERIALIZED VIEW emp_mat_view
REFRESH FAST 
START WITH sysdate
NEXT sysdate + 2
AS 
SELECT * FROM emp;

In the above example materialized view will get refreshed once in two days.
If it gets refreshed on today then the next refresh will occur after 2 days.

With Primary Key/Rowid:

Materialized view cannot be created until and unless the table contains the primary key.

If you need to create the materialized view on the tables which contains no primary key

you need to specify the keyword with rowid in the create statement. Primary key is the default.

Eg:

With Primary key [optional]

CREATE MATERIALIZED VIEW emp_mat_view
WITH PRIMARY KEY
AS 
SELECT * FROM emp;

OR

CREATE MATERIALIZED VIEW emp_mat_view
AS 
SELECT * FROM emp;

With Rowid

CREATE MATERIALIZED VIEW emp_mat_view
WITH rowid
AS 
SELECT * FROM emp;

Benefits of Materialized views

• Less physical reads
• Decrease the CPU consumption
• Faster response time
• Indexes can be created

Eg:

CREATE INDEX mat_view_index
ON emp_mat_view (empno);