PLSQL in 21 Hours

Oracle Basic Architecture

noreply@blogger.com (Naveen) — Wed, 21 May 2014 17:36:00 +0000

Oracle Architecture (Basic):

Below is the typical architecture of Oracle 11g. When you fire a SQL from any of the client tools like (SQL Developer/Toad/ or even SQLPLUS) below processes gets triggered at the back end.

Background Processes: It start with the database instance and perform maintenance tasks such as performing instance recovery, cleaning up processes, and writing redo buffers to disk, and so on.

Server Processes: perform work based on a client request.

Credits: docs.oracle.com

Terminologies & Functionality:

1. SGA

System global area. A group of shared memory structures that contain data and control information for one Oracle database instance.

2. PGA

Program global area. A memory buffer that contains data and control information for a server process.

3. Shared pool

Portion of the SGA that contains shared memory constructs such as shared SQL areas.

4. Large pool

Optional area in the SGA that provides large memory allocations for backup and restore operations, I/O server processes, and session memory for the shared server and Oracle XA.

5. Database buffer cache

The portion of the system global area (SGA) that holds copies of data blocks. All client processes concurrently connected to the instance share access to the buffer cache.

6. Redo log buffer

Memory structure in the SGA that stores redo entries—a log of changes made to the database. The database writes the redo entries stored in the redo log buffers to an online redo log file, which is used if instance recovery is necessary.

7. Online redo log

The set of two or more online redo log files that record all changes made to Oracle Database data files and control file. When a change is made to the database, Oracle Database generates a redo record in the redo buffer. log writer (LGWR) writes the contents of the redo buffer to the online redo log.

Redo Log has set of files that protect altered database data in memory that has not been written to the data files. The redo log can consist of two parts: the online redo log and the archived redo log.

8. Shared SQL area:

An area in the shared pool that contains the parse tree and execution plan for a SQL statement. Only one shared SQL area exists for a unique statement.

9. Back Ground Processes:

A process that consolidates functions that would otherwise be handled by multiple Oracle programs running for each client process. The background processes asynchronously perform I/O and monitor other Oracle processes.

1. Mandatory Background Processes

These processes run by default in a database instance started with a minimally configured initialization parameter file

i. Process Monitor Process (PMON) :

PMON is responsible for cleaning up the database buffer cache and freeing resources that the client process was using. PMON also registers information about the instance and dispatcher processes with the Oracle Net listener

ii. System Monitor Process (SMON): is in charge of a variety of system-level cleanup duties like Performing instance recovery, Recovering terminated transactions, cleaning up unused temporary segments, Coalescing contiguous free extents.

iii. Database Writer Process (DBWn): It writes the contents of database buffers to data files. Typically database buffer is the memory area that stores copies of data blocks read from data files.

iv. Log Writer Process (LGWR) :

The log writer process (LGWR) manages the redo log buffer. LGWR writes one contiguous portion of the buffer to the online redo log.

v. Checkpoint Process (CKPT) :

The checkpoint process (CKPT) updates the control file and data file headers with checkpoint information and signals DBWn to write blocks to disk

vi. Manageability Monitor Processes (MMON and MMNL):

The manageability monitor process (MMON) performs many tasks related to the Automatic Workload Repository (AWR). For example, MMON writes when a metric violates its threshold value, taking snapshots, and capturing statistics value for recently modified SQL objects.

The manageability monitor lite process (MMNL) writes statistics from the Active Session History (ASH) buffer in the SGA to disk. MMNL writes to disk when the ASH buffer is full.

vii. Recoverer Process (RECO)

In a distributed database, the Recoverer process (RECO) automatically resolves failures in distributed transactions. The RECO process of a node automatically connects to other databases involved in an in-doubt distributed transaction.

2. Optional Background Processes:

i. Archiver Processes (ARCn):

The Archiver processes (ARCn) copy online redo log files to offline storage after a redo log switch occurs. These processes can also collect transaction redo data and transmit it to standby database destinations. ARCn processes exist only when the database is in ARCHIVELOG mode and automatic archiving is enabled.

ii. Job Queue Processes (CJQ0 and Jnnn):

Oracle Database uses job queue processes to run user jobs, often in batch mode.

iii. Flashback Data Archiver Process (FBDA):

It archives historical rows of tracked tables into Flashback Data Archives. When a transaction containing DML on a tracked table commits, this process stores the pre-image of the rows into the Flashback Data Archive.

iv. Space Management Coordinator Process (SMCO) :

The SMCO process coordinates the execution of various space management related tasks, such as proactive space allocation and space reclamation. SMCO dynamically spawns slave processes (Wnnn) to implement the task.

3. Slave Processes

Slave processes are background processes that perform work on behalf of other processes.

i. I/O Slave Processes:

I/O slave processes (Innn) simulate asynchronous I/O for systems and devices that do not support it. In asynchronous I/O, there is no timing requirement for transmission, enabling other processes to start before the transmission has finished.

ii. Parallel Query Slaves:

In parallel execution or parallel processing, multiple processes work together simultaneously to run a single SQL statement. By dividing the work among multiple processes, Oracle Database can run the statement more quickly. For example, four processes handle four different quarters in a year instead of one process handling all four quarters by itself.

Frequently Used Data Dictionary Queries

noreply@blogger.com (Naveen) — Wed, 21 May 2014 15:30:00 +0000

1.Index Table :

Select INDEX_NAME ,INDEX_TYPE,TABLE_OWNER,TABLE_NAME,NUM_ROWS,

LAST_ANALYZED,PARTITIONED From All_Indexes;

2. Index Columns :

select INDEX_NAME,TABLE_OWNER,TABLE_NAME,COLUMN_NAME,

COLUMN_POSITION, COLUMN_LENGTH,CHAR_LENGTH from ALL_IND_COLUMNS;

3. Table Column Level Stats :

Select TABLE_NAME,COLUMN_NAME,NUM_DISTINCT,LAST_ANALYZED,AVG_COL_LEN from All_Tab_Col_Statistics;

4. Constraints on a table:

select CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,SEARCH_CONDITION,

LAST_CHANGE,INDEX_OWNER,INDEX_NAME from All_Constraints;

5. Text of a View:

select view_name,text,owner from All_Views;

6. Text of a Trigger :

select trigger_name,trigger_type,triggering_event,table_owner,table_name,

description,trigger_body from all_triggers;

7. Locks on Table:

select c.owner,c.object_name,c.object_type,c.object_id,b.sid,b.serial#,

b.status,b.osuser,b.machine,a.LOCKED_MODE from v$locked_object

a ,v$session b,dba_objects c where b.sid = a.session_id and a.object_id =

c.object_id;

Locking Modes Description:

0- none

1 - null (NULL)

2 - row-S (SS)

3 - row-X (SX)

4 - share (S)

5 - S/Row-X (SSX)

6 - exclusive (X)

8. Query to Kill a Session :

alter system kill session(sid,serial no)

9.Find the Actual size of a Database

SELECT SUM (bytes) / 1024 / 1024 / 1024 AS GB FROM dba_data_files;

10.Find the size occupied by Data in a Database or Database usage details

SELECT SUM (bytes) / 1024 / 1024 / 1024 AS GB FROM dba_segments;

11. Last SQL fired by the User on Database

SELECT S.USERNAME || '(' || s.sid || ')-' || s.osuser UNAME, s.program || '-' ||

s.terminal || '(' || s.machine || ')' PROG,s.sid || '/' || s.serial# sid, s.status

"Status",p.spid,sql_text sqltext FROM v$sqltext_with_newlines t,

V$SESSION s, v$process p WHERE t.address = s.sql_address AND

p.addr = s.paddr(+) AND t.hash_value = s.sql_hash_value ORDER BY s.sid,

t.piece;

12. CPU usage of the USER

SELECT ss.username, se.SID, VALUE / 100 cpu_usage_seconds FROM

v$session ss, v$sesstat se, v$statname sn WHERE se.STATISTIC# =

sn.STATISTIC# AND NAME LIKE '%CPU used by this session%' AND se.SID

= ss.SID AND ss.status = 'ACTIVE' AND ss.username IS NOT NULL ORDER

BY VALUE DESC;

13. Long Query progress in database

SELECT a.sid,a.serial#,b.username,opname OPERATION,target

OBJECT,TRUNC (elapsed_seconds, 5) "ET (s)",TO_CHAR (start_time,

'HH24:MI:SS') start_time,ROUND ( (sofar / totalwork) * 100, 2) "COMPLETE

(%)" FROM v$session_longops a, v$session b WHERE a.sid = b.sid AND

b.username NOT IN ('SYS', 'SYSTEM') AND totalwork > 0 ORDER BY

elapsed_seconds;

14.Last DDL SQL Fired from particular Schema or Table:

SELECT CREATED, TIMESTAMP, last_ddl_time FROM all_objects WHERE OWNER = 'MYSCHEMA' AND OBJECT_TYPE = 'TABLE' AND OBJECT_NAME = 'EMPLOYEE_TABLE';

15.Find Top 10 SQL by reads per execution

SELECT * FROM ( SELECT ROWNUM,SUBSTR (a.sql_text, 1, 200)

sql_text,TRUNC (a.disk_reads / DECODE (a.executions, 0, 1, a.executions))

reads_per_execution,a.buffer_gets,a.disk_reads,a.executions,a.sorts,a.address

FROM v$sqlarea a ORDER BY 3 DESC) WHERE ROWNUM < 10;

16.Oracle SQL query that show the opened connections group by the program that opens the connection.

SELECT program application, COUNT (program) Numero_Sesiones FROM

v$session GROUP BY program ORDER BY Numero_Sesiones DESC;

17.Get number of objects per owner

SELECT owner, COUNT (owner) number_of_objects FROM dba_objects

Group By Owner ORDER BY number_of_objects DESC;

18.Oracle SQL query that shows Oracle users connected and the sessions number for user

SELECT username Usuario_Oracle, COUNT (username) Numero_Sesiones

FROM v$session Group By Username ORDER BY Numero_Sesiones DESC;

19. DML Operation Audit

select username ,obj_name ,to_char(timestamp,'dd-mon-yy hh24:mi')

event_time ,substr(ses_actions,4,1) del ,substr(ses_actions,7,1)

ins ,substr(ses_actions,10,1) sel ,substr(ses_actions,11,1) upd from dba_audit_object;

20.SQL Statements with Maximum Wait

select ash.user_id, u.username, s.sql_text, sum(ash.wait_time +

ash.time_waited) ttl_wait_time from v$active_session_history ash,

v$sqlarea s, dba_users u where ash.sample_time between sysdate -

60/2880 and sysdate and ash.sql_id = s.sql_id and ash.user_id = u.user_id

group by ash.user_id,s.sql_text, u.username order by ttl_wait_time ;

21. SQL Text:

select sid, sql_text from v$session s, v$sql q where sid in (* ,*) and

(q.sql_id = s.sql_id or q.sql_id = s.prev_sql_id);

Note:

Provide the Sid for which you wish to see the SQL Text else list will be exhaustive.

22.query to find out which session is currently using the most undo

select s.sid, t.name, s.value from v$sesstat s, v$statname t where

s.statistic# = t.statistic# and t.name = 'undo change vector size' order by

s.value desc;

23. Monitoring Temporary Tablespace Usage

select * from (select a.tablespace_name,sum(a.bytes/1024/1024)

allocated_mb from dba_temp_files a where a.tablespace_name = upper

('&&temp_tsname') group by a.tablespace_name) x, (select sum

(b.bytes_used/1024/1024) used_mb, sum(b.bytes_free/1024/1024)

free_mb from v$temp_space_header b where b.tablespace_name=upper

('&&temp_tsname') group by b.tablespace_name);

24. query to find out which sessions are using space in the temporary tablespace.

select s.sid || ',' || s.serial# sid_serial, s.username, s.osuser,

p.spid,s.module,s.program,sum (o.blocks) * t.block_size / 1024 / 1024

mb_used, o.tablespace,count(*) sorts from v$sort_usage o, v$session s,

dba_tablespaces t, v$process p where o.session_addr = s.saddr and

s.paddr = p.addr and o.tablespace = t.tablespace_name group by s.sid,

s.serial#, s.username, s.osuser, p.spid, s.module, s.program, t.block_size,

o.tablespace order by sid_serial;

Wishing Thanks to My Readers.

noreply@blogger.com (Naveen) — Tue, 07 Jun 2011 12:39:00 +0000

Hi reader’s,

I’m very much happy to see that lot of people from across the globe visited my blog and visitor’s count has crossed 3000 + with in 40 days of starting the blog. I would request my readers to start posting your valuable comments and requests so that I can make the blog more informative & interactive . For viewers information who visit my Informatica blog, to visit my SQL or Data warehousing blogs too as I spent a lot of time posting the information, which is very useful for the job seekers or Data warehousing professionals in your day today activities at work or can also be used as the quick reference guide before taking up any interview.

I’m planning to publish a book on informatica soon which will cover all major concepts of the data warehousing in detail, including the SQL concepts used by an ETL consultants, Basic Unix Commands and Introduction to the Scheduler tools like Autosys, Tivoli and off course Informatica with simple examples.

I would highly appreciate if few people come forward and post the interview questions or the scenarios which they have encountered in there day to day work for discussion at the discussion board.

Oracle Interview Based Queries-4

noreply@blogger.com (Naveen) — Mon, 09 May 2011 07:40:00 +0000

151) My boss has changed his mind. Now he doesn't want to pay more than 10,000.so revoke that salary constraint.

SQL>alter table emp modify constraint chk_001 enable;

152) Add column called as mgr to your emp table;

SQL>alter table emp add(mgr number(5));

153) Oh! This column should be related to empno. Give a command to add this constraint.

SQL>ALTER TABLE EMP ADD CONSTRAINT MGR_DEPT FOREIGN KEY(MGR) REFERENCES EMP(EMPNO)

154) Add deptno column to your emp table;

SQL>alter table emp add(deptno number(5));

155) This deptno column should be related to deptno column of dept table;

SQL>alter table emp add constraint dept_001 foreign key(deptno)

reference dept(deptno) [deptno should be primary key]

156) Give the command to add the constraint.

SQL>alter table <table_name) add constraint <constraint_name>

157) Create table called as newemp. Using single command create this table as well as get data into this table(use create table as);

SQL>create table newemp as select * from emp;

SQL>Create table called as newemp. This table should contain only

empno,ename,dname.

SQL>create table newemp as select empno,ename,dname from emp,dept where 1=2;

158) Delete the rows of employees who are working in the company for more than 2 years.

SQL>delete from emp where (sysdate-hiredate)/365>2;

159) Provide a commission(10% Comm Of Sal) to employees who are not earning any commission.

SQL>select sal*0.1 from emp where comm is null

160) If any employee has commission his commission should be incremented by 10% of his salary.

SQL>update emp set comm=sal*.1 where comm is not null;

161) Display employee name and department name for each employee.

SQL>select empno,dname from emp,dept where emp.deptno=dept.deptno

162)Display employee number,name and location of the department in which he is working.

SQL>select empno,ename,loc,dname from emp,dept where emp.deptno=dept.deptno;

163) Display ename,dname even if there are no employees working in a particular department(use outer join).

SQL>select ename,dname from emp,dept where emp.deptno=dept.deptno(+)

164) Display employee name and his manager name.

SQL>select p.ename,e.ename from emp e,emp p where e.empno=p.mgr;

165) Display the department name and total number of employees in each department.

SQL>select dname,count(ename) from emp,dept where emp.deptno=dept.deptno group by dname;

166)Display the department name along with total salary in each department.

SQL>select dname,sum(sal) from emp,dept where emp.deptno=dept.deptno group by dname;

167) Display itemname and total sales amount for each item.

SQL>select itemname,sum(amount) from item group by itemname;

168) Write a Query To Delete The Repeated Rows from emp table;

SQL>Delete from emp where rowid not in(select min(rowid)from emp group by ename)

169) TO DISPLAY 5 TO 7 ROWS FROM A TABLE

SQL>select ename from emp where rowid in(select rowid from emp where rownum<=7 minus select rowid from empi where rownum<5)

170) DISPLAY TOP N ROWS FROM TABLE?

SQL>SELECT * FROM (SELECT * FROM EMP ORDER BY ENAME DESC)WHERE ROWNUM <10;

171) DISPLAY TOP 3 SALARIES FROM EMP;

SQL>SELECT SAL FROM ( SELECT * FROM EMP ORDER BY SAL DESC ) WHERE ROWNUM <4

172) DISPLAY 9th FROM THE EMP TABLE?

SQL>SELECT ENAME FROM EMPWHERE ROWID=(SELECT ROWID FROM EMP WHERE ROWNUM<=10 MINUS SELECT ROWID FROM EMP WHERE ROWNUM <10)

173) select second max salary from emp;

select max(sal) fromemp where sal<(select max(sal) from emp);

Oracle Interview Based Queries-3

noreply@blogger.com (Naveen) — Mon, 09 May 2011 07:33:00 +0000

101) Display name and salary of ford if his salary is equal to hisal of his grade.

a)select ename,sal,grade from emp,salgrade where sal between losal and hisal and ename ='FORD' AND HISAL=SAL;

102) Display employee name,job,depart name ,manager name,his grade and make out an under department wise?

SQL>SELECT E.ENAME,E.JOB,DNAME,EMP.ENAME,GRADE FROM EMP,EMP E,SALGRADE,DEPT WHERE EMP.SAL BETWEEN LOSAL AND HISAL AND EMP.EMPNO=E.MGR AND EMP.DEPTNO=DEPT.DEPTNO ORDER BY DNAME

103) List out all employees name,job,salary,grade and depart name for every one in the company except 'CLERK'.Sort on salary display the highest salary?

SQL>SELECT ENAME,JOB,DNAME,SAL,GRADE FROM EMP,SALGRADE,DEPT WHERE SAL BETWEEN LOSAL AND HISAL AND EMP.DEPTNO=DEPT.DEPTNO AND JOB NOT IN('CLERK')ORDER BY SAL ASC;

104) Display the employee name,job and his manager.Display also employee who are without manager?

SQL>select e.ename,e.job,eMP.ename AS Manager from emp,emp e where emp.empno(+)=e.mgr

105) Find out the top 5 earners of company?

SQL>SELECT DISTINCT SAL FROM EMP E WHERE 5>=(SELECT COUNT(DISTINCT SAL) FROM EMP A WHERE A.SAL>=E.SAL)ORDER BY SAL DESC;

106) Display name of those employee who are getting the highest salary?

SQL>select ename from emp where sal=(select max(sal) from emp);

107) Display those employee whose salary is equal to average of maximum and minimum?

SQL>select ename from emp where sal=(select max(sal)+min(sal)/2 from emp);

108) Select count of employee in each department where count greater than 3?

SQL>select count(*) from emp group by deptno having count(deptno)>3

109) Display dname where at least 3 are working and display only department name?

SQL>select distinct d.dname from dept d,emp e where d.deptno=e.deptno and 3>any (select count(deptno) from emp group by deptno)

110) Display name of those managers name whose salary is more than average salary of his company?

SQL>SELECT E.ENAME,EMP.ENAME FROM EMP,EMP E WHERE EMP.EMPNO=E.MGR AND E.SAL>(SELECT AVG(SAL) FROM EMP);

111)Display those managers name whose salary is more than average salary of his employee?

SQL>SELECT DISTINCT EMP.ENAME FROM EMP,EMP E WHERE E.SAL <(SELECT AVG(EMP.SAL) FROM EMP WHERE EMP.EMPNO=E.MGR GROUP BY EMP.ENAME) AND EMP.EMPNO=E.MGR;

112) Display employee name,sal,comm and net pay for those employee whose net pay is greter than or equal to any other employee salary of the company?

SQL>select ename,sal,comm,sal+nvl(comm,0) as NetPay from emp where sal+nvl(comm,0) >any (select sal from emp)

113) Display all employees names with total sal of company with each employee name?

SQL>SELECT ENAME,(SELECT SUM(SAL) FROM EMP) FROM EMP;

114) Find out last 5(least)earners of the company.?

SQL>SELECT DISTINCT SAL FROM EMP E WHERE 5>=(SELECT COUNT(DISTINCT SAL) FROM EMP A WHERE A.SAL<=E.SAL) ORDER BY SAL DESC;

115) Find out the number of employees whose salary is greater than their manager salary?

SQL>SELECT E.ENAME FROM EMP ,EMP E WHERE EMP.EMPNO=E.MGR AND EMP.SAL<E.SAL;

116) Display those department where no employee working?

SQL>select dname from emp,dept where emp.deptno not in(emp.deptno)

117) Display those employee whose salary is ODD value?

SQL>select * from emp where sal<0;

118) Display those employee whose salary contains at least 3 digits?

SQL>select * from emp where length(sal)>=3;

119) Display those employee who joined in the company in the month of Dec?

SQL>select ename from emp where to_char(hiredate,'MON')='DEC';

120) Display those employees whose name contains "A"?

SQL>select ename from emp where instr(ename,'A')>0;

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

121) Display those employee whose deptno is available in salary?

SQL>select emp.ename from emp, emp e where emp.sal=e.deptno;

122) Display those employee whose first 2 characters from hiredate -last 2 characters of salary?

SQL>select ename,SUBSTR(hiredate,1,2)||ENAME||substr(sal,-2,2) from emp

123) Display those employee whose 10% of salary is equal to the year of joining?

SQL>select ename from emp where to_char(hiredate,'YY')=sal*0.1;

124) Display those employee who are working in sales or research?

SQL>SELECT ENAME FROM EMP WHERE DEPTNO IN(SELECT DEPTNO FROM DEPT WHERE DNAME IN('SALES','RESEARCH'));

125) Display the grade of jones?

SQL>SELECT ENAME,GRADE FROM EMP,SALGRADE WHERE SAL BETWEEN LOSAL AND HISAL AND Ename='JONES';

126) Display those employees who joined the company before 15 of the month?

select ename from emp where to_char(hiredate,'DD')<15;

127) Display those employee who has joined before 15th of the month.

a)select ename from emp where to_char(hiredate,'DD')<15;

128) Delete those records where no of employees in a particular department is less than 3.

SQL>delete from emp where deptno=(select deptno from emp group by deptno having count(deptno)<3);

129) Display the name of the department where no employee working.

SQL> SELECT E.ENAME,E.JOB,M.ENAME,M.JOB FROM EMP E,EMP M WHERE E.MGR=M.EMPNO

130) Display those employees who are working as manager.

SQL>SELECT M.ENAME MANAGER FROM EMP M ,EMP E WHERE E.MGR=M.EMPNO GROUP BY M.ENAME

131) Display those employees whose grade is equal to any number of sal but not equal to first number of sal?

SQL> SELECT ENAME,GRADE FROM EMP,SALGRADE WHERE GRADE NOT IN(SELECT SUBSTR(SAL,0,1)FROM EMP)

132) Print the details of all the employees who are Sub-ordinate to BLAKE?

SQL>select emp.ename from emp, emp e where emp.mgr=e.empno and e.ename='BLAKE';

133) Display employee name and his salary whose salary is greater than highest average of department number?

SQL>SELECT SAL FROM EMP WHERE SAL>(SELECT MAX(AVG(SAL)) FROM EMP GROUP BY DEPTNO);

134) Display the 10th record of emp table(without using rowid)

SQL>SELECT * FROM EMP WHERE ROWNUM<11

MINUS

SELECT * FROM EMP WHERE ROWNUM<10

135) Display the half of the ename's in upper case and remaining lowercase?

SQL>SELECT SUBSTR(LOWER(ENAME),1,3)||SUBSTR(UPPER(ENAME),3,LENGTH(ENAME)) FROM EMP;

136) Display the 10th record of emp table without using group by and rowid?

SQL>SELECT * FROM EMP WHERE ROWNUM<11

MINUS

SELECT * FROM EMP WHERE ROWNUM<10

Delete the 10th record of emp table.

SQL>DELETE FROM EMP WHERE EMPNO=(SELECT EMPNO FROM EMP WHERE ROWNUM<11 MINUS SELECT EMPNO FROM EMP WHERE ROWNUM<10)

137) Create a copy of emp table;

SQL>create table new_table as select * from emp where 1=2;

138) Select ename if ename exists more than once.

SQL>select ename from emp e group by ename having count(*)>1;

139) Display all enames in reverse order?(SMITH:HTIMS).

SQL>SELECT REVERSE(ENAME)FROM EMP;

140) Display those employee whose joining of month and grade is equal.

SQL>SELECT ENAME FROM EMP WHERE SAL BETWEEN (SELECT LOSAL FROM SALGRADE WHERE GRADE=TO_CHAR(HIREDATE,'MM')) AND (SELECT HISAL FROM SALGRADE WHERE GRADE=TO_CHAR(HIREDATE,'MM'));

141) Display those employee whose joining DATE is available in deptno.

SQL>SELECT ENAME FROM EMP WHERE TO_CHAR(HIREDATE,'DD')=DEPTNO

142) Display those employees name as follows

A ALLEN

B BLAKE

SQL> SELECT SUBSTR(ENAME,1,1),ENAME FROM EMP;

143) List out the employees ename,sal,PF(20% OF SAL) from emp;

SQL>SELECT ENAME,SAL,SAL*.2 AS PF FROM EMP;

144) Create table emp with only one column empno;

SQL>Create table emp as select empno from emp where 1=2;

145) Add this column to emp table ename varchar2(20).

SQL>alter table emp add(ename varchar2(20));

146) Oops I forgot give the primary key constraint. Add in now.

SQL>alter table emp add primary key(empno);

147) Now increase the length of ename column to 30 characters.

SQL>alter table emp modify(ename varchar2(30));

148) Add salary column to emp table.

SQL>alter table emp add(sal number(10));

149) I want to give a validation saying that salary cannot be greater 10,000 (note give a name to this constraint)

SQL>alter table emp add constraint chk_001 check(sal<=10000)

150) For the time being I have decided that I will not impose this validation.My boss has agreed to pay more than 10,000.

SQL>again alter the table or drop constraint with alter table emp drop constraint chk_001 (or)Disable the constraint by using alter table emp modify constraint chk_001 disable;

Oracle Interview based Queries - part2

noreply@blogger.com (Naveen) — Mon, 09 May 2011 05:44:00 +0000

51) Display the name of the employee who earns highest salary.

SQL>select ename from emp where sal=(select max(sal) from emp);

52) Display the employee number and name for employee working as clerk and earning highest salary among clerks.

SQL>select empno,ename from emp where where job='CLERK' and sal=(select max(sal) from emp where job='CLERK');

53) Display the names of salesman who earns a salary more than the highest salary of any clerk.

SQL>select ename,sal from emp where job='SALESMAN' and sal>(select max(sal) from emp where job='CLERK');

54) Display the names of clerks who earn a salary more than the lowest salary of any salesman.

SQL>select ename from emp where job='CLERK' and sal>(select min(sal) from emp where job='SALESMAN');

Display the names of employees who earn a salary more than that of Jones or that of salary grether than that of scott.

SQL>select ename,sal from emp where sal>(select sal from emp where ename='JONES')and sal> (select sal from emp where ename='SCOTT');

55) Display the names of the employees who earn highest salary in their respective departments.

SQL>select ename,sal,deptno from emp where sal in(select max(sal) from emp group by deptno);

56) Display the names of the employees who earn highest salaries in their respective job groups.

SQL>select ename,sal,job from emp where sal in(select max(sal) from emp

group by job)

57) Display the employee names who are working in accounting department.

SQL>select ename from emp where deptno=(select deptno from dept where dname='ACCOUNTING')

58) Display the employee names who are working in Chicago.

SQL>select ename from emp where deptno=(select deptno from dept where LOC='CHICAGO')

59) Display the Job groups having total salary greater than the maximum salary for managers.

SQL>SELECT JOB,SUM(SAL) FROM EMP GROUP BY JOB HAVING SUM(SAL)>(SELECT MAX(SAL) FROM EMP WHERE JOB='MANAGER');

60) Display the names of employees from department number 10 with salary greater than that of any employee working in other department.

SQL>select ename from emp where deptno=10 and sal>any(select sal from emp where deptno not in 10).

61) Display the names of the employees from department number 10 with salary greater than that of all employee working in other departments.

SQL>select ename from emp where deptno=10 and sal>all(select sal from emp where deptno not in 10).

62) Display the names of the employees in Uppercase.

SQL>select upper(ename)from emp

63) Display the names of the employees in Lower case.

SQL>select lower(ename)from emp

64) Display the names of the employees in Proper case.

SQL>select initcap(ename)from emp;

65) Display the length of Your name using appropriate function.

SQL>select length('name') from dual

66) Display the length of all the employee names.

SQL>select length(ename) from emp;

67) select name of the employee concatenate with employee number.

SQL>select ename||empno from emp;

68) User appropriate function and extract 3 characters starting from 2 characters from the following string 'Oracle'. i.e the out put should be 'ac'.

SQL>select substr('oracle',3,2) from dual

69) Find the First occurance of character 'a' from the following string i.e 'Computer Maintenance Corporation'.

SQL>SELECT INSTR('Computer Maintenance Corporation','a',1) FROM DUAL

70) Replace every occurance of alphabhet A with B in the string Allens(use translate function)

SQL>select translate('Allens','A','B') from dual

71) Display the informaction from emp table.Where job manager is found it should be displayed as boos(Use replace function).

SQL>select replace(JOB,'MANAGER','BOSS') FROM EMP;

72) Display empno,ename,deptno from emp table.Instead of display department numbers display the related department name(Use decode function).

SQL>select empno,ename,decode(deptno,10,'ACCOUNTING',20,'RESEARCH',30,'SALES',40,'OPRATIONS') from emp;

73) Display your age in days.

SQL>select to_date(sysdate)-to_date('10-sep-77')from dual

74) Display your age in months.

SQL>select months_between(sysdate,'10-sep-77') from dual

75) Display the current date as 15th August Friday Nineteen Ninety Seven.

SQL>select to_char(sysdate,'ddth Month day year') from dual

76) Display the following output for each row from emp table.scott has joined the company on Wednesday 13th August nineteen ninety.

SQL>select ENAME||' HAS JOINED THE COMPANY ON '||to_char(HIREDATE,'day ddth Month year') from EMP;

77) Find the date for nearest saturday after current date.

SQL>SELECT NEXT_DAY(SYSDATE,'SATURDAY')FROM DUAL;

78) Display current time.

SQL>select to_char(sysdate,'hh:MM:ss') from dual.

79) Display the date three months Before the current date.

SQL>select add_months(sysdate,3) from dual;

80) Display the common jobs from department number 10 and 20.

SQL>select job from emp where deptno=10 and job in(select job from emp

where deptno=20);

81) Display the jobs found in department 10 and 20 Eliminate duplicate jobs.

SQL>select distinct(job) from emp where deptno=10 or deptno=20

(or)

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

82) Display the jobs which are unique to department 10.

SQL>select distinct(job) from emp where deptno=10

83) Display the details of those who do not have any person working under them.

SQL>select e.ename from emp,emp e where emp.mgr=e.empno group by e.ename having count(*)=1;

84) Display the details of those employees who are in sales department and grade is 3.

SQL>select * from emp where deptno=(select deptno from dept where dname='SALES')and sal between(select losal from salgrade where grade=3)and (select hisal from salgrade where grade=3);

85) Display those who are not managers and who are managers any one.

i)display the managers names

SQL>select distinct(m.ename) from emp e,emp m where m.empno=e.mgr;

ii)display the who are not managers

SQL>select ename from emp where ename not in(select distinct(m.ename) from emp e,emp m where m.empno=e.mgr);

86) Display those employee whose name contains not less than 4 characters.

SQL>select ename from emp where length(ename)>4;

87) Display those department whose name start with "S" while the location name ends with "K".

SQL>select dname from dept where dname like 'S%' and loc like '%K';

88) Display those employees whose manager name is JONES.

SQL>select p.ename from emp e,emp p where e.empno=p.mgr and e.ename='JONES';

89) Display those employees whose salary is more than 3000 after giving 20% increment.

SQL>select ename,sal from emp where (sal+sal*.2)>3000;

90) Display all employees while their dept names;

SQL>select ename,dname from emp,dept where emp.deptno=dept.deptno

91) Display ename who are working in sales dept.

SQL>select ename from emp where deptno=(select deptno from dept where dname='SALES');

92) Display employee name,deptname,salary and comm for those sal in between 2000 to 5000 while location is chicago.

SQL>select ename,dname,sal,comm from emp,dept where sal between 2000 and 5000 and loc='CHICAGO' and emp.deptno=dept.deptno;

93)Display those employees whose salary greter than his manager salary.

SQL>select p.ename from emp e,emp p where e.empno=p.mgr and p.sal>e.sal

94) Display those employees who are working in the same dept where his manager is work.

SQL>select p.ename from emp e,emp p where e.empno=p.mgr and p.deptno=e.deptno;

95) Display those employees who are not working under any manager.

SQL>select ename from emp where mgr is null

96) Display grade and employees name for the dept no 10 or 30 but grade is not 4 while joined the company before 31-dec-82.

SQL>select ename,grade from emp,salgrade where sal between losal and hisal and deptno in(10,30) and grade<>4 and hiredate<'31-DEC-82';

97) Update the salary of each employee by 10% increment who are not eligible for commission.

SQL>update emp set sal=sal+sal*10/100 where comm is null;

98) SELECT those employee who joined the company before 31-dec-82 while their dept location is newyork or Chicago.

SQL>SELECT EMPNO,ENAME,HIREDATE,DNAME,LOC FROM EMP,DEPTWHERE (EMP.DEPTNO=DEPT.DEPTNO)AND HIREDATE <'31-DEC-82' AND DEPT.LOC IN('CHICAGO','NEW YORK');

99) DISPLAY EMPLOYEE NAME,JOB,DEPARTMENT,LOCATION FOR ALL WHO ARE WORKING AS MANAGER?

SQL>select ename,JOB,DNAME,LOCATION from emp,DEPT where mgr is not null;

100) DISPLAY THOSE EMPLOYEES WHOSE MANAGER NAME IS JONES? --[AND ALSO DISPLAY THEIR MANAGER NAME]?

SQL> SELECT P.ENAME FROM EMP E, EMP P WHERE E.EMPNO=P.MGR AND E.ENAME='JONES';

Oracle interview based Queries- 1

noreply@blogger.com (Naveen) — Mon, 09 May 2011 05:35:00 +0000

It is pre-assumed that one has Emp,Dept tables in their Database.All one needs to do is to copy and paste the following queries and execute them.

1) Display the details of all employees

SQL>Select * from emp;

2) Display the depart information from department table

SQL>select * from dept;

3) Display the name and job for all the employees

SQL>select ename,job from emp;

4) Display the name and salary for all the employees

SQL>select ename,sal from emp;

5) Display the employee no and totalsalary for all the employees

SQL>select empno,ename,sal,comm, sal+nvl(comm,0) as"total salary" from

emp

6) Display the employee name and annual salary for all employees.

SQL>select ename, 12*(sal+nvl(comm,0)) as "annual Sal" from emp

7) Display the names of all the employees who are working in depart number 10.

SQL>select emame from emp where deptno=10;

8) Display the names of all the employees who are working as clerks and drawing a salary more than 3000.

SQL>select ename from emp where job='CLERK' and sal>3000;

9) Display the employee number and name who are earning comm.

SQL>select empno,ename from emp where comm is not null;

10) Display the employee number and name who do not earn any comm.

SQL>select empno,ename from emp where comm is null;

11) Display the names of employees who are working as clerks,salesman or analyst and drawing a salary more than 3000.

SQL>select ename from emp where job='CLERK' OR JOB='SALESMAN'

OR JOB='ANALYST' AND SAL>3000;

12) Display the names of the employees who are working in the company for the past 5 years;

SQL>select ename from emp where to_char(sysdate,'YYYY')-to_char(hiredate,'YYYY')>=5;

13) Display the list of employees who have joined the company before

30-JUN-90 or after 31-DEC-90.

a)select ename from emp where hiredate < '30-JUN-1990' or hiredate >

'31-DEC-90';

14) Display current Date.

SQL>select sysdate from dual;

15) Display the list of all users in your database(use catalog table).

SQL>select username from all_users;

16) Display the names of all tables from current user;

SQL>select tname from tab;

17) Display the name of the current user.

SQL>show user

18) Display the names of employees working in depart number 10 or 20 or 40 or employees working as CLERKS,SALESMAN or ANALYST.

SQL>select ename from emp where deptno in(10,20,40) or job

in('CLERKS','SALESMAN','ANALYST');

19) Display the names of employees whose name starts with alaphabet S.

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

20) Display the Employee names for employees whose name ends with alaphabet S.

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

21) Display the names of employees whose names have second alphabet A in their names.

SQL>select ename from emp where ename like '_A%';

22) select the names of the employee whose names is exactly five characters in length.

SQL>select ename from emp where length(ename)=5;

23) Display the names of the employee who are not working as MANAGERS.

SQL>select ename from emp where job not in('MANAGER');

24) Display the names of the employee who are not working as SALESMAN OR CLERK OR ANALYST.

SQL>select ename from emp where job not in ('SALESMAN','CLERK','ANALYST');

25) Display all rows from emp table.The system should wait after every

screen full of informaction.

SQL>set pause on

26) Display the total number of employee working in the company.

SQL>select count(*) from emp;

27) Display the total salary beiging paid to all employees.

SQL>select sum(sal) from emp;

28) Display the maximum salary from emp table.

SQL>select max(sal) from emp;

29) Display the minimum salary from emp table.

SQL>select min(sal) from emp;

30) Display the average salary from emp table.

SQL>select avg(sal) from emp;

31) Display the maximum salary being paid to CLERK.

SQL>select max(sal) from emp where job='CLERK';

32) Display the maximum salary being paid to depart number 20.

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

33) Display the minimum salary being paid to any SALESMAN.

SQL>select min(sal) from emp where job='SALESMAN';

34) Display the average salary drawn by MANAGERS.

SQL>select avg(sal) from emp where job='MANAGER';

35) Display the total salary drawn by ANALYST working in depart number 40.

SQL>select sum(sal) from emp where job='ANALYST' and deptno=40;

36) Display the names of the employee in order of salary i.e the name of the employee earning lowest salary should salary appear first.

SQL>select ename from emp order by sal;

37) Display the names of the employee in descending order of salary.

a)select ename from emp order by sal desc;

38) Display the names of the employee in order of employee name.

a)select ename from emp order by ename;

39) Display empno,ename,deptno,sal sort the output first base on name and within name by deptno and with in deptno by sal.

SQL>select empno,ename,deptno,sal from emp order by

40) Display the name of the employee along with their annual salary(sal*12).The name of the employee earning highest annual salary should apper first.

SQL>select ename,sal*12 from emp order by sal desc;

41) Display name,salary,hra,pf,da,total salary for each employee. The output should be in the order of total salary,hra 15% of salary,da 10% of salary,pf 5% salary,total salary will be(salary+hra+da)-pf.

SQL>select ename,sal,sal/100*15 as hra,sal/100*5 as pf,sal/100*10 as

da, sal+sal/100*15+sal/100*10-sal/100*5 as total from emp;

42) Display depart numbers and total number of employees working in each department.

SQL>select deptno,count(deptno)from emp group by deptno;

43) Display the various jobs and total number of employees within each job group.

SQL>select job,count(job)from emp group by job;

44) Display the depart numbers and total salary for each department.

SQL>select deptno,sum(sal) from emp group by deptno;

45) Display the depart numbers and max salary for each department.

SQL>select deptno,max(sal) from emp group by deptno;

46) Display the various jobs and total salary for each job

SQL>select job,sum(sal) from emp group by job;

47) Display the various jobs and total salary for each job

SQL>select job,min(sal) from emp group by job;

48) Display the depart numbers with more than three employees in each dept.

SQL>select deptno,count(deptno) from emp group by deptno having

count(*)>3;

49) Display the various jobs along with total salary for each of the jobs

where total salary is greater than 40000.

SQL>select job,sum(sal) from emp group by job having sum(sal)>40000;

50) Display the various jobs along with total number of employees in each job.The output should contain only those jobs with more than three employees.

SQL>select job,count(empno) from emp group by job having count(job)>3

Oracle Query Tuning

noreply@blogger.com (Naveen) — Thu, 05 May 2011 12:59:00 +0000

If query taking long time then First will run the query in Explain Plan, The explain plan process stores data in the PLAN_TABLE.

it will give us execution plan of the query like whether the query is using the relevant indexes on the joining columns or indexes to support the query are missing.

If joining columns doesn’t have index then it will do the full table scan if it is full table scan the cost will be more then will create the indexes on the joining columns and will run the query it should give better performance and also needs to analyze the tables if analyzation happened long back. The ANALYZE statement can be used to gather statistics for a specific table, index or cluster using

ANALYZE TABLE employees COMPUTE STATISTICS;

If still have performance issue then will use HINTS, hint is nothing but a clue. We can use hints like

ALL_ROWS
One of the hints that 'invokes' the Cost based optimizer
ALL_ROWS is usually used for batch processing or data warehousing systems.

(/*+ ALL_ROWS */)

FIRST_ROWS
One of the hints that 'invokes' the Cost based optimizer
FIRST_ROWS is usually used for OLTP systems.

(/*+ FIRST_ROWS */)

CHOOSE One of the hints that 'invokes' the Cost based optimizer
This hint lets the server choose (between ALL_ROWS and FIRST_ROWS, based on statistics gathered.
HASH Hashes one table (full scan) and creates a hash index for that table. Then hashes other table and uses hash index to find corresponding records. Therefore not suitable for < or > join conditions.

/*+ use_hash */

Hints are most useful to optimize the query performance.

Explain Plan

noreply@blogger.com (Naveen) — Wed, 23 Mar 2011 14:25:00 +0000

Explain Plan : It is a statement that allows you to have oracle generate execution plan for any sql statement with out actually executing it.You will be able to examine the execution plan by querying the plan table.

Plan Table : A Plan table holds execution plans generated by the explain plan statements.Create Plan table by running utlxplan.sql located in

$oracle_home/rdbms/admin.

Explain Plan Syntax :

Explain plan [set statement_id=<string in single quotes>]

[into <plan table name>]

for

<sql statements>;

Example to Explain Plan:

Sql> Explain plan set statement_id=’demo’ for

select a.customer_name,a.customer_number,b.invoice_number,

b.invoice_type,b.invoice_data,b.total_amount,

c.line_number,c.part_number,c.quantity,c.unit_cost

from customers a, invoices b, invoice_items c

where c.invoice_id=:b1

and c.line_number=:b2

and b.invoice_id=c.invoice_id

and a.customer_id=b.customer_id

Sql> @ explain.sql

Enter statement_id : Demo

SQL Loader

noreply@blogger.com (Naveen) — Wed, 23 Mar 2011 14:09:00 +0000

SQL*Loader (sqlldr ) is the utility to use for high performance data loads. The data can be loaded from any text file and inserted into the database.

During processing, SQL*Loader writes messages to the log file, bad rows to the bad file, and discarded rows to the discard file.

The Control File

The SQL*Loader control file contains information that describes how the data will be loaded. It contains the table name, column data types, field delimiters, etc. It simply provides the guts for all SQL*Loader processing.

SQL*Loader Options

SQL*Loader provides the following options, which can be specified either on the command line or within a parameter file:

bad – A file that is created when at least one record from the input file is rejected. The rejected data records are placed in this file. A record could be rejected for many reasons, including a non-unique key or a required column being null.
bindsize – [256000] The size of the bind array in bytes.
columnarrayrows – [5000] Specifies the number of rows to allocate for direct path column arrays.
control – The name of the control file. This file specifies the format of the data to be loaded.
data – The name of the file that contains the data to load.
direct – [FALSE] Specifies whether or not to use a direct path load or conventional.
discard – The name of the file that contains the discarded rows. Discarded rows are those that fail the WHEN clause condition when selectively loading records.
discardmax – [ALL] The maximum number of discards to allow.
errors – [50] The number of errors to allow on the load.
external_table – [NOT_USED] Determines whether or not any data will be loaded using external tables. The other valid options include GENERATE_ONLY and EXECUTE.
file – Used only with parallel loads, this parameter specifies the file to allocate extents from.
load – [ALL] The number of logical records to load.
log – The name of the file used by SQL*Loader to log results.
multithreading – The default is TRUE on multiple CPU systems and FALSE on single CPU systems.
parfile – [Y] The name of the file that contains the parameter options for SQL*Loader.
parallel – [FALSE] Specifies a filename that contains index creation statements.
readsize – The size of the buffer used by SQL*Loader when reading data from the input file. This value should match that of bindsize.
resumable – [N] Enables and disables resumable space allocation. When “Y”, the parameters resumable_name and resumable_timeout are utilized.
resumable_name – User defined string that helps identify a resumable statement that has been suspended. This parameter is ignored unless resumable = Y.
resumable_timeout – [7200 seconds] The time period in which an error must be fixed. This parameter is ignored unless resumable = Y.
rows – [64] The number of rows to load before a commit is issued (conventional path only). For direct path loads, rows are the number of rows to read from the data file before saving the data in the datafiles.
silent – Suppress errors during data load. A value of ALL will suppress all load messages. Other options include DISCARDS, ERRORS, FEEDBACK, HEADER, and PARTITIONS.
skip – [0] Allows the skipping of the specified number of logical records.
skip_unusable_indexes – [FALSE] Determines whether SQL*Loader skips the building of indexes that are in an unusable state.
skip_index_maintenance – [FALSE] Stops index maintenance for direct path loads only.
streamsize – [256000] Specifies the size of direct path streams in bytes.
userid – The Oracle username and password.

To drive the concept of the SQL loader home.., let us assume that we have text file which is the usual emp table.
Now open the cmd prompt and type sqlldr.
later the things should look some thing like this.

sqlldr# userid=username/pwd@ora

load data

in file ‘C:\emp.txt’

into table emp_oracle

fields terminated by “,”optionally enclosed by’ ” ’

(empno,ename,job,mgr,sal,comm,deptno)

Materialized view

noreply@blogger.com (Naveen) — Wed, 23 Mar 2011 13:54:00 +0000

A materialized view is a database object that contains the results of a query. They are local copies of data located remotely, or are used to create summary tables based on aggregations of a table's data.Materialized view and the Query rewrite feature is added from ORACLE 8i.

A materialized view log is a schema object that records changes to a master table's data so that a materialized view defined on the master table can be refreshed incrementally.

If you delete any record from your Materialized view it is goanna impact your Source table once it is refreshed.

Examples to the Simple Materialized view’s is given below .

Eg 1 :Create materialized_view MV refresh as select * from emp;

Execute dbms_mview.refresh(‘MV’);

Refresh Complete : To perform a complete refresh of a materialized view, the server that manages the materialized view executes the materialized view's defining query, which essentially recreates the materialized view. To refresh the materialized view, the result set of the query replaces the existing materialized view data. Oracle can perform a complete refresh for any materialized view. Depending on the amount of data that satisfies the defining query, a complete refresh can take a substantially longer amount of time to perform than a fast refresh.

Create Materialized_view MV Refresh complete as select * from emp;

execute DBMS_mview.refresh(List=>’MV’,Method=>’c’);

Refresh Fast :To perform a fast refresh, the master that manages the materialized view first identifies the changes that occurred in the master since the most recent refresh of the materialized view and then applies these changes to the materialized view. Fast refreshes are more efficient than complete refreshes when there are few changes to the master because the participating server and network replicate a smaller amount of data.

Create Materialized_view MV Refresh fast as select * from emp;

execute DBMS_mview.refresh(list=>’MV’,Method=>’F’);

Primary Key Materialized Views :

The following statement creates the primary-key materialized view on the table emp located on a remote database.

SQL> 	CREATE MATERIALIZED VIEW mv_emp_pk
   	REFRESH FAST START WITH SYSDATE 
	NEXT  SYSDATE + 1/48
	WITH PRIMARY KEY 
	AS SELECT * FROM emp@remote_db;

Note: When you create a materialized view using the FAST option you will need to create a view log on the master tables(s) as shown below:

SQL> CREATE MATERIALIZED VIEW LOG ON emp;Materialized view log created.

Rowid Materialized Views :

The following statement creates the rowid materialized view on table emp located on a remote database:

SQL>CREATE MATERIALIZED VIEW mv_emp_rowid REFRESH WITH ROWID AS SELECT * FROM emp@remote_db;

Materialized view log created.

Creating Materialized Aggregate Views :

CREATE MATERIALIZED VIEW sales_mv BUILD IMMEDIATE REFRESH FAST ON COMMIT AS SELECT t.calendar_year, p.prod_id, SUM(s.amount_sold) AS sum_sales FROM times t, products p, sales s WHERE t.time_id = s.time_id AND p.prod_id = s.prod_id GROUP BY t.calendar_year, p.prod_id;

Creating Materialized Join Views :

CREATE MATERIALIZED VIEW sales_by_month_by_state
     TABLESPACE example
     PARALLEL 4
     BUILD IMMEDIATE
     REFRESH COMPLETE
     ENABLE QUERY REWRITE
     AS SELECT t.calendar_month_desc, c.cust_state_province,
        SUM(s.amount_sold) AS sum_sales
        FROM times t, sales s, customers c
        WHERE s.time_id = t.time_id AND s.cust_id = c.cust_id
        GROUP BY t.calendar_month_desc, c.cust_state_province;

Periodic Refresh of Materialized Views:

CREATE MATERIALIZED VIEW emp_data 
   PCTFREE 5 PCTUSED 60 
   TABLESPACE example 
   STORAGE (INITIAL 50K NEXT 50K)
   REFRESH FAST NEXT sysdate + 7 
   AS SELECT * FROM employees;

Automatic Refresh Times for Materialized Views

CREATE MATERIALIZED VIEW all_customers
   PCTFREE 5 PCTUSED 60 
   TABLESPACE example 
   STORAGE (INITIAL 50K NEXT 50K) 
   USING INDEX STORAGE (INITIAL 25K NEXT 25K)
   REFRESH START WITH ROUND(SYSDATE + 1) + 11/24 
   NEXT NEXT_DAY(TRUNC(SYSDATE), 'MONDAY') + 15/24 
   AS SELECT * FROM sh.customers@remote 
         UNION
      SELECT * FROM sh.customers@local;

Function

noreply@blogger.com (Naveen) — Wed, 23 Mar 2011 13:06:00 +0000

A function is a sub program that executes a set of statements and returns a value to the main program. The basic difference between Functions and procedures is, function returns a value to the calling program or main program where as a procedure does not return any value

Syntax of A Function

Create [ Or Replace ] Function < Function Name > ( Argument1 [Mode] <Data type>, Argument2 [Mode] <data type>,---) return data type

Is / As

Local Variables Declarations;

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

Begin

Executable Statements;

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

[Exception

Exception Handling; ]

End;

Example : 1

Write a program to find sum of two numbers

Sol :

ed function1

Create or replace function addition(x integer, y integer) return integer

Begin

Return x+y;

End;

mainfunction1

Declare

A integer :=&a;

B integer :=&b;

C integer;

Begin

C:=addition(a,b);

Dbms_output.put_line('sum of two numbers is '|| c );

End;

Example : 2

wirte a function which accepts item number and quantity and then return the total amount by fetching rate from the item table

create or replace function itembill(q in number,r in number) return number

begin

return q*r;

end;

ed mainfunction2

declare

q1 number:=&quantity;

r1 number;

res number;

begin

select rate into r1 from item where itno=&itno;

res:=itembill(q1,r1);

dbms_output.put_line(' Total Amount is '||res);

exception

when no_data_found then

dbms_output.put_line(' No Such Item Exits');

end;

Stored Procedure

noreply@blogger.com (Naveen) — Wed, 23 Mar 2011 13:05:00 +0000

A procedure is a set of instructions(usually combining sql and plsql commands) saved for calling and repeated execution

A procedure is a sub program that performs a specific action. A procedure can be called from any pl/sql block. A procedure has two parts one is Specification and Other is Body

The procedure specification begins with the keyword procedure followed by procedure name and an option list of arguments enclosed with in the parenthesis.

The procedure body begins with is or as and ends with an end statement.

It consists of three parts

1. Declarative Part

2. Execution Part

3. Exception Handling Part

Syntax of Procedure

Create [ Or Replace ] Procedure < Procedure Name > ( Argument1 [Mode] <Data type>, Argument2 [Mode] <data type>,---)

Is / As

Local Variables Declarations;

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

Begin

Executable Statements;

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

[Exception

Exception Handling; ]

End;

where Mode refers to the type of arguments such as In , Out or InOut

Modes In Procedures And Functions

IN : the In parameter lets the user to pass values to the called sub programs with in the sub program, the IN parameter acts like a constant. There fore it cannot be modified.

OUT : The Out parameter lets the user returned values to the calling block. Inside the sub program the out parameter acts like a un initialized variable. Its value can not be assigned to another variable or itself

INOUT : the INOUT parameter lets the user pass initial values to the called sub program and return updated values to the calling block

Note : the default mode of an argument is "IN"

Show Errors :

This Command is used to show the errors that are occurred during the procedure creation

Ex : when ever we create a procedure and executed, if it show procedure created with compilation errors, the we can see those errors using the following statement

show Errors

Example :1

Write a procedure to show a simple message

Sol. Steps

Write the sub Program

Ed subprocedure1

Create or replace procedure disp is

Begin

Dbms_output.put_line('This is a Sub Program');

End;

Next, Compile the above procedure to find errors in the procedure, with the following statement

@ subprocedure1

Next, Write the main program

Begin

Dbms_output.put_line('This is Main Program');

Disp;

Dbms_output.put_line('Again Continuing the Main Program');

End;

Next, Run the above main procedure with the following statement

@mainprogram1

output :

SQL> @ mainprogram1

This is Main Program

This is a Sub Program

Again Continuing the Main Program

PL/SQL procedure successfully completed.

Example :2

Write a procedure to show a message when updated any row in a particular table

Sol. Steps

Write the sub Program

Ed subprocedure2

Create or replace procedure upd(d number)

Begin

update employee set sal=sal+(sal*0.2) where empno=d;

dbms_output.put_line(sql%rowcount||' Record Updated...');

End;

Next, Compile the above procedure to find errors in the procedure, with the following statement

@ subprocedure2

Next, Write the main program

Begin

upd(&empno);

End;

Next, Run the above main procedure with the following statement

@mainprogram2

output :

SQL> @ mainprogram2

Enter value for empno: 7369

old 2: upd(&empno);

new 2: upd(7369);

1 Record Updated...

PL/SQL procedure successfully completed.

Example 3:

Create a procedure which adds the given three numbers using “in” and “out” parameters

Sol. Steps

Write the sub Program

Ed subprocedure3

Create or replace procedure sum_numbers(n1 in number, n2 in

number, res out number)

Begin

res:=n1+n2;

End;

Next, Compile the above procedure to find errors in the procedure, with the following statement

@ subprocedure3

Next, Write the main program

Declare

a number;

b number;

c number;

Begin

a:=&a;

b:=&b;

sum_numbers(a,b,c);

dbms_output.put_line('sum of three numbers is '||c);

End;

Next, Run the above main procedure with the following statement

@mainprogram3

output :

SQL> @ mainprogram3

Enter value for a: 10

old 6: a:=&a;

new 6: a:=10;

Enter value for b: 20

old 7: b:=&b;

new 7: b:=20;

sum of three numbers is30

PL/SQL procedure successfully completed.

Example 4:

Create a procedure which Updates the sal with the increment value that you give according to the given emp number

Sol. Steps

Write the sub Program

Ed subprocedure4

Create or replace procedure incr(eno employee.empno%type,s

out number, i number)

Begin

Update employee set sal=sal+i where empno=eno;

Dbms_output.put_line('Record Updated----');

End;

Next, Compile the above procedure to find errors in the procedure, with the following statement

@ subprocedure4

Next, Write the main program

Declare

e number:=&empno;

Incr_value number:=&increment;

s employee.sal%type;

Begin

Select sal into s from employee where empno=e;

Incr(e,s,incr_value);

End;

Next, Run the above main procedure with the following statement

@mainprogram4

output :

SQL> @ mainprogram4

Enter value for empno: 7369

old 2: e number:=&empno;

new 2: e number:=7369;

Enter value for increment: 48

old 3: incr_value number:=&increment;

new 3: incr_value number:=48;

Record Updated----

PL/SQL procedure successfully completed.

Trigger

noreply@blogger.com (Naveen) — Wed, 23 Mar 2011 13:02:00 +0000

Trigger is a database object ie used to execute an action basing on an even. Triggers are event-based programs, which are executed when an event occurs or raises or fires

Types of Triggers

Trigger Type is defined by the type of triggering transaction and by the level at which the trigger is executed

Triggers Are Of Two Types

1. Row Level Triggers

2. Statement Level Triggers

Row Level Triggers

A row trigger is fired each time a row in the table is affected by the triggering statement. For example, if an UPDATE statement updates multiple rows of a table, a row trigger is fired once for each row affected by the update statement. If the triggering statement affects no rows, the trigger is not executed at all. Row triggers should be used when some processing is required whenever a triggering statement affects a single row in a table.

Row level triggers are created using the "For Each Row" Clause in the Create Trigger Command

Statement triggers

A statement trigger is fried once on behalf of the triggering statement, independent of the number of rows the triggering statement affects (even if not rows are affected) statement triggers should be used when a triggering statement affects rows in a table but the processing required is completely independent of the number of rows affected

Statement level triggers are the default type of trigger created via Create Trigger Command

Syntax:

create or replace trigger <trigger_name>

{before/after/instead of}

{insert/update/delete}

[ of <column name> on <table name> ]

[ for each row [when <condition>] ]

Declare

variables declarations

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

begin

Executable statements

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

Exception

Exception statements

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

end;

Syntax Explanation :

or replace : Recreates the trigger if it already exists. this option can be used to change the definition of an existing trigger without requiring the user to drop the trigger first

Trigger Name : is the name of the trigger to be created

Before : Indicates that oracle fires the trigger before executing the trigger statement

After : Indicates that oracle fires the trigger After executing the trigger statement

Insert : Indicates that oracle fires the trigger whenever an INSERT statement adds a row to a table.

Delete : Indicates that oracle fires the trigger whenever a DELETE statement removes a row from the table.

Update : Indicates that oracle fires the trigger whenever an UPDATE statement changes a value in one of the columns specified in the OF clause. if the OF clause is omitted, the oracle fires the trigger whenever an UPDATE statement changes a value in any column of the table.

for Each Row : Designates the trigger to be a row trigger. the oracle engine fires a row trigger once for each row that is affected by the triggering statement and meets the optional trigger constraint defined in the When clause. if this clause is omitted the trigger is a statement trigger.

When : specifies the trigger restriction. the trigger restriction contains a SQL condition that must be satisfied for the oracle to fire the trigger.

Basing on the above 2 types of triggers, they are further classified into 3 types

1. DML Triggers

2. DDL Triggers and

3. Instead of Triggers

1. DML Triggers

These triggers are executed before or after. we apply any dml operations on a table.

When we create a table. the trigger definition is stored in the database, which is identified with the trigger name. the code in the trigger is processed when we apply any command on the database or table

Examples :

Steps for Creating a Trigger

1. First Create a trigger, next set the server on with the following statement (set Serveroutput on)

2. Run that Trigger with the following statement

@ <trigger name>

3. perform some action (ie either insert or update or delete etc)

Statement Level Triggers

1. Create A Trigger, Which Displays A Message When Ever You Insert A New Row In To Sample1 Table

Create or replace trigger instrig1 before insert on sample1

Begin

dbms_output.put_line('one record inserted successfully.....');

End;

2. Create A Trigger, Which Displays A Message When Ever You Update An Existing Row In The Table Sample1

Create or replace trigger updtrig1 before update on sample1

Begin

dbms_output.put_line('one record updated successfully.....');

End;

Create A Trigger, Which Displays A Message When Ever You Delete A Row From The Table Sample1

Create or replace trigger deltrig1 before delete on sample1

Begin

dbms_output.put_line('record(s) deleted successfully.....');

End;

Row Level Triggers

1. Create A Trigger, Which Displays A Message When Ever You Insert A New Row Into A Table Sample1

Create or replace trigger instrig2 before insert on sample1

for each row

Begin

dbms_output.put_line(:new.sno||' record inserted successfully.....');

End;

Create a trigger, which displays a message when ever you update a row in the table sample1

Create or replace trigger updtrig2 before update on sample1 for each row

Begin

dbms_output.put_line(:old.sno||' record updated to '||:new.sno);

End;

Create A Trigger, Which Displays A Message When Ever You Delete A Row From The Table Sample1

Create or replace trigger deltrig2 after delete on sample1 for each row

Begin

dbms_output.put_line(:old.sno||' record deleted successfully.....');

End;

DDL TRIGGERS

Create A Trigger, Which Displays An Error Message When Ever You Create a New Table which starts with Letter ‘A’

Create or replace trigger ctrig1 before create on scott.schema

Begin

if dictionary_obj_name like 'a%' then

raise_application_error(-20001,'object name can not start with a');

End if;

End;

Create A Trigger, Which Displays An Error Message When Ever You try to drop any Table

Create or replace trigger prevent_drop after drop on scott.schema

Begin

if ora_dict_obj_type='table' then

raise_application_error(-20001,'object can not be dropped');

End if;

End;

Create A Trigger, Which Displays An Error Message When Ever You try to Alter any Table

create or replace trigger prevent_alter before alter on scott.schema

begin

if ora_dict_obj_type='TABLE' then

Raise_Application_Error(-20001,'Object Can not be altered');

end if;

end;

INSTEAD OF TRIGGER

Create a trigger, which inserts the given values in to the relevant table through a Composite view

Create or replace trigger instrig instead of

Insert on empdept for each row

Begin

if :new.deptno is not null and :new.dname is not null then

insert into department(deptno,dname) values (:new.deptno,:new.dname);

end if;

if :new.empno is not null and :new.ename is not null then

insert into employee(empno,ename) values (:new.empno,:new.ename);

end if;

End;

Exception Handling

noreply@blogger.com (Naveen) — Wed, 23 Mar 2011 12:57:00 +0000

Types of Exceptions

1. Pre Defined Exceptions

2. User Defined Exceptions

Pre Defined Exceptions

These exceptions are used to handle some logical errors known to the system are pre defined. The following are some of the important pre defined Exceptions

no_data_found

This Exception raises when there is no rows to be retrieved from a table

according to given condition

dup_val_on_index

This Exception raises when ever you try to store duplicate values into a

table, which has been indexed (unique indexed)

cur_already_open

This Exception raises when ever your program attempts to open an already

opened cursor.

A cursor must be closed before it can be re opened. A cursor for loop

automatically opens the cursor to which it refers. So Your program can not open that cursor inside the loop

invalid_cursor

This Exception raises when ever your program attempts an illegal cursor operation, such as closing an un opened cursor

zero_divide

This Exception raises when ever your program attempts to divide a number

by zero

program_error

This Exception raises when ever PL/SQL has internal problem

storage_error

This Exception raises when ever PL/SQL runs out of memory

too_many_rows

This Exception raises when ever a select statement returns more than one

row

login_denied

This Exception raises when ever your program attempts to logon to oracle

with an invalid user name and/or password

value_error

This Exception raises when ever an arithmetic conversion or size constraint error occurs

For ex, when your program selects a column value into a variable. If the value is longer than the declared length of the variable, pl/sql abords the assignment and raise the exception value_error

Programs On Exceptions

1. Write a program for handling an error when we try to open an already opened cursor

declare

cursor c1 is select * from emp;

x emp%rowtype;

begin

open c1;

fetch c1 into x;

dbms_output.put_line(x.empno||' '||x.ename);

--open c1;

close c1;

exception

when cursor_already_open then

dbms_output.put_line(' sorry friend, cursor already opened');

end;

2. Write a program to display a message when no data found in the main table

declare

e emp%rowtype;

begin

select * into e from emp where empno=&empno;

dbms_output.put_line('Empno Ename job');

dbms_output.put_line(e.empno||' '||e.ename||' '||e.job);

exception

when No_data_found then

dbms_output.put_line(' sorry friend, no data found');

end;

3. Write a program for handling an error when we insert a duplicate value into a column.

declare

n sample%rowtype;

begin

insert into sample values(&sno,'&sname','&class');

dbms_output.put_line('Record Inserted....');

exception

when dup_val_on_index then

dbms_output.put_line(' sorry friend, you entered a duplicate value');

end;

Note : create a table with unique constraint, for dup_val_on_index,

create table sample(sno number(3),sname varchar2(10));

CREATE UNIQUE INDEX IDX_SAM ON SAMPLE(SNO);

4. Write a program for handling an error when we try to close an un opened cursor

declare

cursor c1 is select * from emp;

begin

--open c1;

dbms_output.put_line(' cursor is Opened');

close c1;

exception

when invalid_cursor then

dbms_output.put_line(' sorry friend, No such cursor is opened');

end;

Note : if u remove comment for open c1 then output is cursor is opened, other wise the you will get “sorry friend, no such cursor is opened ..”, since you r trying to close an unopened cursor

5. Write a program for handling an error when we try to divide a value with zero

declare

a number:=&a;

b number:=&b;

c number;

begin

c:=a/b;

dbms_output.put_line(c);

exception

when zero_divide then

dbms_output.put_line('Zero Divide error');

end;

6. Write a program for handling an error when ever the program retrieves too many rows

declare

e emp%rowtype;

begin

select * into e from emp;

exception

when too_many_rows then

dbms_output.put_line(' sorry friend, too many rows are selected');

end;

User Defined Exceptions

A user can define or create exceptions, which must be raised automatically, because the system does not knows when to execute the user defined errors. There are 3 steps while working with user defined exceptions

1. Creating Exception

2. Raising Exception

3. Handling Exception

The main purpose of user defined exceptions is, we can display a message in predefined error format in the instances not known to the system or that are not predefined

Syntax :

Declare

<Exception Name> Exception;

begin

Raise Exception;

Exception

When <Exception name> then

Message

End;

Raise_Application_Error

This is a keyword, which is used to generate an error message in pre defined error format for the given user defined error

Syntax :

Raise_Application_Error(Error number, ‘ message ‘);

Example :

Raise_Application_Error(-20173, ‘ Some Error ‘);

Note : Error Number for (user defined Exceptions) must be in between –20001 to -20999

1. Write a program for creating an exception and raising it when the basic is <3000, while inserting rows into a table and also handle it to perform any other action

Declare

invalid_salary exception;

s employee.sal%type:=&sal;

begin

if s<3000 then

raise invalid_salary;

else

insert into employee(sal) values (s);

dbms_output.put_line(' Record Inserted.....');

end if;

exception

when invalid_salary then

dbms_output.put_line(' salary must be >3000 ');

end;

2. Write a program for creating an exception and raising it when ever you try to insert any negative number into a table

Declare

invalid_number exception;

e employee.empno%type:=&empno;

begin

if e<0 then

raise invalid_number;

else

insert into employee(empno) values (e);

dbms_output.put_line(' Record Inserted.....');

end if;

exception

when invalid_number then

dbms_output.put_line(' Number must be positive ');

end;

Cursor

noreply@blogger.com (Naveen) — Wed, 23 Mar 2011 12:53:00 +0000

Cursor is a location in the memory where the SQL statement is processed. That is when ever we send an SQL statement; the system allocates some memory by giving a name. After processing the statement it automatically cleans that memory. There are four steps, which are processed by the system whenever we send an SQL statement they are

1.Creating cursor : This step allocates memory

2.Opening cursor :

In this step process the sql statement

3.Fetching cursor:

The values satisfied by the sql statement are fetched from the

table into cursor row by row

4.Closing cursor : This statement close the memory allocated for the cursor

Types Of Cursors

There Are Two Types Of Cursors

1. Implicit cursors or simple cursors

2. Explicit Cursors

Implicit Cursors

Oracle implicitly opens a cursor to process each sql statement not associated with explicitly declared cursor. Pl/sql lets you refer to the most recent implicit cursors as the sql cursor.

Implicit cursors have four attributes, you can use in your program

sql%rowcount

It returns the number of rows affected by an insert, update or delete

sql%found

This attributes evaluates to true, if an insert or update or delete

affected to one or more rows. It evaluates to false if no row is affected

sql%notfound

This attributes is logical opposite to sql%found. It evaluates to true,

if an insert or update or delete does not affected to any row. It evaluates to

false if any one row is affected

%isopen

It checks whether the cursor has been opened or not

Example : 1

write a program to check whether there is atleast one row satisfying the given select statement or not using implicit cursors

Declare

e emp%rowtype;

Begin

select * into e from emp where empno=&empno;

if sql%found then

dbms_output.put_line('~~~~~~~~~~~~~~~~~~~~');

dbms_output.put_line(' Record Found ');

dbms_output.put_line('~~~~~~~~~~~~~~~~~~~~');

End if;

End;

Example : 2

write a program to change the ename column values to upper case

using implicit cursors

begin

update employee set ename=upper(ename);

if sql%found then

dbms_output.put_line('~~~~~~~~~~~~~~~~~~~~');

dbms_output.put_line(sql%rowcount || ' Records Updated ');

dbms_output.put_line('~~~~~~~~~~~~~~~~~~~~');

else

dbms_output.put_line('~~~~~~~~~~~~~~~~~~~~');

dbms_output.put_line(' Sorry Changes Are not Made ');

end if;

end;

Example : 3

write a program to update the salary of those employees to 20% whose salary is greater than the given salary, using implicit cursors

begin

update employee set sal=sal+(sal*20)/100 where sal>&sal;

if sql%found then

dbms_output.put_line('~~~~~~~~~~~~~~~~~~~~');

dbms_output.put_line(sql%rowcount || ' Records Updated ');

dbms_output.put_line('~~~~~~~~~~~~~~~~~~~~');

end if;

if sql%notfound then

dbms_output.put_line('~~~~~~~~~~~~~~~~~~~~');

dbms_output.put_line(' Sorry No Record is Satisfied ');

end if;

end;

Explicit Cursors

The user creates these cursors for applying 2 or more DML operations on a table. using a cursor we can fetch all the row values from the table into a variable for printing them or performing some action with them

In an explicit cursor, there are four steps related to execute cursors by the user. It is also used to compare a variable value with all values of the column.

The four steps are

1. Declaring a Cursor

2. Opening a Cursor

3. Fetching a Cursor

4. Closing a Cursor

Declaring a Cursor

A cursor is defined in the declarative part of the PL/SQL block by naming it and associating it with a query

Syntax :

Declare

Cursor <cursor_name> is <select Statement>

Opening a Cursor

The cursor must be initialized or opened with the open statement. Opening the cursor excutes the query and identifies the result set which consists of all the rows that meets the query search criteria

Syntax :

Declare

Cursor <cursor_name> is <select Statement>

Begin

Open <cursor Name>

-----------

End;

Fetching With a Cursor

This statement is used to retrieve the cursors current row. Fetching can be excuted repeatedly until all rows have been retrieved

Syntax :

Declare

Cursor <cursor_name> is <select Statement>

Begin

Open <cursor Name>

Fetch <cursor Name> into <variable1>,<variable2>,----

-----------

End;

Closing a Cursor

When the last row has been processed, the cursor is closed with the close statement. The close statement disable the cursor

Syntax :

Declare

Cursor <cursor_name> is <select Statement>

Begin

Open <cursor Name>

Fetch <cursor Name> into <variable1>,<variable2>,----

-----------

Close <cursor Name>

End;

Examples

1. Write a program using an explicit cursor, which displays all the rows from the table

Declare

Cursor c1 is select * from emp;

e emp%rowtype;

Begin

Open c1;

Loop

Fetch c1 into e;

Dbms_output.put_line(e.empno||' '||e.ename||' '||e.sal);

Exit when c1%notfound;

End loop;

Close c1;

End;

2.Write a program using an explicit cursor, to insert values into a table empc from emp table, whose experience is >=23 years

Declare

cursor c2 is select * from emp where (sysdate-hiredate)/365>=23;

e emp%rowtype;

x number:=0;

begin

open c2;

loop

fetch c2 into e;

exit when c2%notfound;

insert into empc values(e.empno,e.ename,e.job,e.sal,e.hiredate);

x:=x+sql%rowcount;

end loop;

dbms_output.put_line(x||' Records inserted Successfully....');

close c2;

end;

3.Write a program using an explicit cursor, to display first 5 records

Declare

cursor c3 is select * from emp;

e emp%rowtype;

x number;

begin

open c3;

loop

fetch c3 into e;

exit when c3%rowcount>5;

dbms_output.put_line(e.empno||' '||e.ename||' '||e.sal);

end loop;

close c3;

end;

Cursor For Loop

A cursor for loop implicitly declares its loop index as a %rowtype, opens a cursor, repeatedly fetches the rows of values from the result set into fields in the records and closes the cursor when all rows have been processed or exit command is encountered

Syntax :

Declare

Cursor <cursor_name> is <select statement>

Begin

For <index> in <cursor name> loop

---------

end loop;

end;

Example : 1

Write a program to display all employee’s details working in the accepted department number using cursor for loop

declare

cursor c1 is select * from emp where deptno=&deptno;

e c1%rowtype;

begin

dbms_output.put_line('****************');

for e in c1 loop

dbms_output.put_line(e.empno||' '||e.ename||' '||e.job||' '||e.sal);

end loop;

dbms_output.put_line('****************');

end;

Example : 2

Write a program to display all employee’s details working in the accepted department number and also display all department details for the given department number using cursor for loops

declare

cursor c1 is select * from emp where deptno=&deptno;

e c1%rowtype;

cursor c2 is select * from dept where deptno=&deptno;

d c2%rowtype;

begin

dbms_output.put_line(' ****Employees Details ***** ');

for e in c1 loop

dbms_output.put_line(e.empno||' '|| e.ename||' '||e.job||' '||e.sal);

end loop;

dbms_output.put_line(' ****Department Details ***** ');

for d in c2 loop

dbms_output.put_line(d.dname||' '||d.loc

||' '||d.deptno);

end loop;

end;

Control structures

noreply@blogger.com (Naveen) — Wed, 23 Mar 2011 12:46:00 +0000

Control structures

Control structures are used to control the flow of programs execution. The control structures are classified into the following categories:

Conditional Control
Iterative Control

Conditional Control

PL/SQL supports the following types of Conditional control statements or Selection Statements

Simple if statement
If...else statement
If ...elsif statement

1.Simple If

The simple if statement is used to check a simple condition, if it is true it will execute the statements that are present between "if "and "end if". Otherwise it will not execute those statements.

Syntax :

If ( Condition ) then

Statements ;

------------- ;

End If;

2.If...Else Statement

The if …else statement is used to define two blocks of statements, in order to execute only one block.

Syntax :

If (Condition ) then

True Block Statements ;

------------------------- ;

Else

False Block Statements ;

-------------------------- ;

End if;

if the condition is true, it will execute the True Block statements otherwise it will execute the false block statements.

3.If...ElsIf Statement

The if …elsIf statement is used to define Several blocks of statements, in order to execute only one block.

Syntax :

If (condition 1) then

statements 1 ;

--------------- ;

Elsif (condition 2) then

statements 2;

------------- ;

Elsif (condition 3) then

statements 3;

------------;

Elsif (condition n) then

statements n;

------------;

Else

statements;

------------;

End if;

if condition1 is true, it will execute statements1 otherwise it will check Condition2, if it is true it will execute statements2. if not it will go to condition3, like this the process continues until the condition matches. If no condition matches, it will execute the else block statements.

Eg 1. Write a program to check whether the given number is a positive number of a negative number using simple if statement

declare

n number:=&n;

begin

if(n>=0) then

dbms_output.put_line(n||' is a Positive Number');

end if;

if(n<0) then

dbms_output.put_line(n ||' is a Negative Number');

end if;

end;

Branching Statements

declare

x integer:=&x;

y integer:=&y;

p char(1):='&Operator';

r number;

begin

if p='+' then

goto Addition;

elsif p='-' then

goto Subtraction;

elsif p='*' then

goto Multiplication;

elsif p='/' then

goto Division;

else

goto Expt;

end if;

<<Addition>>

r:=x+y;

dbms_output.put_line(r);

return;

<<Subtraction>>

r:=x-y;

dbms_output.put_line(r);

return;

<<Multiplication>>

r:=x*y;

dbms_output.put_line(r);

return;

<<Division>>

r:=x/y;

dbms_output.put_line(r);

return;

<<Expt>>

dbms_output.put_line(sqlerrm);

end;

Loops

PL/SQL supports, the following types of loops

1. Simple Loop

2. While Loop

3. For Loop

1.Simple Loop

This loop is used to execute a series of statements as long as the condition is false. If the condition becomes true, it will exit from that loop

Syntax :

Loop

Statements;

-----------

Exit When <condition>;

Increment/decrement Operations

End Loop;

Eg 2 :Write a program to print first 25 natural numbers using simple loop

Declare

N number:=1;

Begin

Loop

Exit When N>25;

Dbms_output.put_line(N);

N:=N+1;

End loop;

End;

2.While Loop

The while Loop is used to execute a series of statements as long as the condition is True. If the condition becomes false, it will exit from that loop

Syntax :

While <condition> Loop

Statements;

-----------

[ Exit When <condition>; ]

Increment/decrement Operations

End Loop;

Eg 3: Write a program to print first 25 natural numbers using While loop

Declare

N number:=1;

Begin

While N<=25 Loop

Dbms_output.put_line(N);

N:=N+1;

End loop;

End;

3.For Loop

The For loop is used to execute a set of statements as long as the condition is false. If the condition becomes true, it will exit from that loop

Syntax :

For <variable> in [reverse] <start value> .. <end Value> Loop

Statements;

-----------

End Loop;

Eg 4: Write a program to print first 25 natural numbers using For loop

Declare

N number:=1;

Begin

For N in 1..25 Loop

Dbms_output.put_line(N);

End loop;

End;

Eg 5 :Write a program to print first 25 natural numbers using For loop in Reverse order

Declare

N number:=1;

Begin

For N in Reverse 1..25 Loop

Dbms_output.put_line(N);

End loop;

End;

Eg 6: Write a program to print multiplication table for the given number

Declare

i number:=1;

r number:=&number;

Begin

While i<=10 loop

dbms_output.put_line(r||'*'|| i ||'='||r*i);

i:=i+1;

End loop;

End;

Partitions

noreply@blogger.com (Naveen) — Wed, 23 Mar 2011 12:32:00 +0000

Partitions

Partition is a data base object, it allows VLDB objects (Very Large Databases such as tables, indexes etc). By splitting large database objects into smaller, more manageable units. You can divide the rows of a single table into different parts called as partitioning a table

Advantages of Using Partitions

1.Reducing data base Retrieve time

By splitting the large data base objects into smaller, more manageable partitions, allows you to perform any type of action on individual units of the database

2.Backup and Recovery

Partitioning improves performance of Backup and Recovery operations. Smaller size of partitions offers more options for backing up and recovering from the partitions

3.Query Performance

Partitions can be created on ranges of certain columns. This focus reduce the search time, as a queries require a full table scan to search for the given criteria. This can be resolved by using partitions on a table

4.Partition Transparency

Partition implementation is transparent to the end users and developers. They do not need to be aware of the physical implementation of the database objects

Syntax :

Create table <table name> (columns specifications,-------)

Partition by Range (column name)

(partition <partition name> values less than (some value),

partition <partition name> values less than (some value),

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

partition <partition name> values less than (max value));

Example :

create table partitiondemo(sno number(3),sname varchar2(10))

partition by range (sno)

(partition p1 values less than(200),

partition p2 values less than(300),

partition p3 values less than(600),

partition p4 values less than(999))

1. select * from partitiondemo;

SNO SNAME

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

100 Nithya

105 Saloni

109 Namitha

199 Aruna

204 Priya

250 Mahesh

306 Nandhini

450 Pooja

560 Uma

328 Janshi

900 Rani

904 Kavya

2. select * from partitiondemo partition(p1);

SNO SNAME

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

100 Nithya

105 Saloni

109 Namitha

199 Aruna

3. select * from partitiondemo partition(p2);

SNO SNAME

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

204 Priya

250 Mahesh

4. select * from partitiondemo partition(p3);

SNO SNAME

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

306 Nandhini

450 Pooja

560 Uma

328 Janshi

900 Rani

904 Kavya

5. select * from partitiondemo partition(p3);

SNO SNAME

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

900 Rani

904 Kavya

Now, insert some values again

insert into partitiondemo values(125,'Sound');

select * from partitiondemo partition(p1);

SNO SNAME

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

100 Nithya

105 Saloni

109 Namitha

199 Aruna

125 Sound

noreply@blogger.com (Naveen) — Wed, 23 Mar 2011 12:30:00 +0000

Clusters

Tables that are frequently accessed together may be physically stored together. Clustering is a method of storing tables that are closely related and are often joined together, into the same area on the disk.

A cluster is created to hold the tables. A cluster contains the values of a table according to the column that we define. When ever we insert values into the table the same values are automatically stored in the database object name cluster.

Cluster can be created by using create cluster command. To create a cluster use the following syntax

Syntax :

Create cluster <cluster name> (columns specifications,------)

To create any cluster

First create a cluster with the column names that you want
Next create an index on that cluster. Otherwise the cluster will not work properly
Next create a table with the cluster specification and list of columns
Now insert some values into that table, if you see the cluster the same values will be available from the cluster also

Note :

We can not insert or update or delete any row from the cluster
We can not even drop the cluster until the source table is present
To drop the cluster, first we have to drop the source table and then we can drop that particular cluster

Creation of cluster for a table

First create the cluster,

create cluster sampleclust (sno number(3), sname varchar2(10));

Next create the index,

create index idx_sampleclust on cluster sampleclust;

Attaching the cluster to the table,

create table sample2 (sno number(3),sname varchar2(10)) cluster

sampleclust(sno,sname)

Example :

To display details of the Table

select * from sample2;

SNO SNAME

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

100 Nithya

101 Saloni

102 Aruna

To display details of the Table

select * from cluster sampleclust;

SNO SNAME

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

100 Nithya

101 Saloni

102 Aruna

Even if you delete any row in the table, that will not affected to the cluster
1. delete from sample2 where sno=100;
2. select * from sample2;

SNO SNAME

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

101 Saloni

102 Aruna

3. select * from cluster sampleclust;

SNO SNAME

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

100 Nithya

101 Saloni

102 Aruna

4. delete from sampleclust where sno=102

ERROR at line 1:

ORA-00942: table or view does not exist

Note : We can not delete any data from the cluster

Drop cluster

This command is used to drop any cluster

Syntax : drop Cluster <cluster name>

To drop any cluster

Drop table sample2;

Drop cluster sampleclust;

Indexes

noreply@blogger.com (Naveen) — Wed, 23 Mar 2011 12:28:00 +0000

Indexes

It is a database object used to sort the values of a column for retrieving data fastly from a table. By default the rows in a table are identified with their row id’s

When ever you create an index on a table, the system gives index id’s which are stored in the index. If a table is having index then the system retrieves the rows basing on the index id’s else it retrieves value using row id’s

Types of Index

There are two types of indexes

1. Simple Index

2. Composite Index

There are two ways to create an index. They are

Creating index on a single column
Creating index on multiple columns

Simple Index

Any index created on a single table is called a simple index

Syntax : create index <index name> on <table name>(column);

Creating Index On A Single Column

When you create an index on a single column,

Create Table temporary(sno number(3), sname varchar2(10));

NOTE :

Index name must be unique in the database and it is user choice. Since index is a data base object, system allocates some memory in the database. The index stores all index id’s allocated for a column.

Note : we can not create any index on views.

SQL> select * from temporary;

SNO NAME

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

110 Nithya

111 Saloni

112 Mahesh

113 Priya

114 Prasad

115 Aruna

creating an index

create index idx_temp on temporary(sno);

Note : We can not see the Details of Index file

select * from idx_temp;

ERROR at line 1:

ORA-00942: table or view does not exist

Creating Unique indexes on a Single column

Syntax :

Create unique index <index name> on <table name> (<column>)

Note : Unique index can be created on any table, except it does not contain any duplicate values on the column for which you are creating a unique index

Example :

create unique index idx_temp on sample1(sno);

insert into sample1 values(&sno,’&sname’);

enter any value for sno: 1

enter any value for sname : Mahesh

enter any value for sno: 2

enter any value for sname : Prasad

enter any value for sno: 1

enter any value for sname : Nithya

Error :

Ora-00001: unique constraint (scott.idx_temp) violated

Note :

Once if u create any index on a particular column, then that column can not be re indexed. To re index on that column, first remove that existing index using drop index command and then create new index on that column
If you create an index on an existing index, it will show an error message

create unique index idx_temp1 on temporary(sno)

ERROR at line 1:

ORA-01408: such column list already indexed

Dropping an Index

Drop Index : This Command Is Used To Drop Any Index

Syntax : Drop Index <Index Name>

Example : drop index idx_temp;

Composite index

An index created on 2 or more columns in a table is called composite index.

Syntax :

Create index <index name> on

<table name> (<column1>, <column2>,-------)

first create a table

create table bank(accno number(3),accna varchar2(10),ddno number(6),

amt number(6));

next Create the index on that table

create index idx_bank on bank (accno,ddno);

next Insert some values into that table

insert into bank values (100,'Prasad',11111,2343)
insert into bank values (102,'Mahesh',22222,4334)
insert into bank values (102,'Nithya',22222,4334)

select * from bank;

ACCNO ACCNA DDNO AMT

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

100 Prasad 11111 2343

102 Mahesh 22222 4334

102 Nithya 22222 4334

100 Saloni 11111 3433

Composite unique index

We can create unique index on two or more columns also using composite unique index

Syntax :

Create unique index <index name> on <table name> (<column1>,<column2>,-------)

create unique index idx_bank1 on bank1(accno,ddno);

insert into bank values (100,'Priya',11111,3445)
insert into bank values (102,'Mahi',22222,4334)
insert into bank values (102,'Sound',22222,4334)

ERROR at line 1:

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

Note :

1. There is a problem in the above index, the index will give an error only when both columns value are given duplicate values. If any one is different the index will accept those values.

2. Once a column is indexed already, it is not possible to reindex that column to any other index file

Drop index

This command is used to drop any index

Syntax : Drop index <index name>

Ex : Drop Index Idx_Sample;

There are two built in objects, which gives information about the indexs that are in the data base.

user_indexes : contains all user defined index’s information

Example :

select * from user_indexes;

select index_name, table_name, table_owner from user_indexes;

All_indexes : contains all user defined index’s and pre defined index’s information

Example :

select * from all_indexes;

select index_name, table_name, table_owner from all_indexes;

Sequence

noreply@blogger.com (Naveen) — Wed, 23 Mar 2011 12:24:00 +0000

Sequences

The sequence is a database object, which is used to generate unique integers for use as primary key for a column. There are two steps while working with a sequence

Creating Sequence
Attaching or Binding Sequence

Creating Sequence

Since a sequence is a database object, it contains the values with which it generates starting, increment value and maximum value etc.

Syntax :

Create sequence <sequence name >Start with <value>

Increment by <value>Max value / [no max value] <value>

Min value / [no min value] <value>Cycle/no cycle

Cache/no cache <value>Order/no order

Start with : it is a keyword to specifying the starting value in the sequence creation

Increment by : this parameter accepts the number by which we want to increment the value in the sequence. If you want to decrement you must specify the negative value. By default the increment value is ‘1’ (not zero)

Max value : specifies the maximum value or limit that the sequence can generate

No max value : if you cannot specify the maximum limit, use this parameter. The default value is No max value.(not compulsory)

Min value : specifies the sequences minimum value or limit. It represent the number upto which you want to decrement the value.

No Min value : The default value is No Max value.(not compulsory)

Cycle : specifies that the sequence continues to generate value after reaching either its maximum or minimum value.

No cycle : it represents that the sequence should stop generation of numbers when the max value is reached. This is the default one.

Cache : it specifies how many values of the sequence oracle pre allocates and keeps in the memory for the fast access. This keyword generates ‘n’ numbers immediately after creating sequence. Where ‘n’ is the number followed by cache. Default is ‘20’

No cache : this is the default one.

Order : generates the sequences in the order of request

No order : this is the default one

Attaching Sequence

If sequence is created it can be attached to the table for storing the value of the sequence into the table or for generating the column values according to the parameters in the sequence.

There are two ways the sequence can be attached

Insert Command
Update Command

currval : specifies sequence current value

nextval : specifies sequence next value

Example :

Using Insert Command

Syntax :

Insert into <table name>(columns list) values(sequencename.nextval,……);

First create a dummy table

create table temporary(sno number(3), name varchar2(10));

Next, create a sequence with the following way

create sequence myseq start with 100 increment by 1 maxvalue 110 min value 100;
insert into temporary values(myseq.nextval,'&sname');

similarly insert values in to the temporary table, when the sequence.next Val reaches to 110, it automatically gives an error message.

ERROR at line 1:

ORA-08004: sequence MYSEQ.NEXTVAL exceeds MAXVALUE and cannot be instantiated

select * from temporary;

SNO NAME

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

100 one

101 two

102 three

103 four

104 five

105 six

106 seven

107 eight

108 nine

109 ten

110 eleven

Using Update Command

Syntax :

Update <table name> set <column Name>=<sequencename.nextval>, ------;

create a sequence with the following way

create sequence myseq1 start with 1 increment by 1 maxvalue 10

min value 1;

Updating only one value

update sample set sno=myseq1.nextval where sname=’kk’;

Updating Multiple Values

update sample set sno=myseq1.nextval;

select * from sample;

EMPNO ENAME SAL

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

1 Nithya 5600

2 Saloni 5400

3 Pooja 5400

4 Aruna 7600

5 Sound 5600

6 Mahesh 4500

7 Nandhini 3500

Note :

If you are updating a table using a sequence, which contains more rows than the sequence can generate, we will get an error message
For example, if a table contains 12 rows, but the sequence can generate maximum of 11 rows only. So we get error message

Altering a Sequence

A sequence can be altered using the Alter Command. But we can not alter the start with value.

Syntax :

Alter sequence <sequence name > Increment by <value>

Maxvalue /NoMaxValue <value>Minvalue /NoMinValue <value>

Cycle / NoCycle Cache/NoCache <value>

Order/noorder

Example

create sequence myseq2 start with 100 increment by 1 maxvalue 110

minvalue 100;

we altered the above sequence

Alter sequence myseq2 increment by 2 maxvalue 120;

Update sample set sno=myseq2.nextval;

select * from sample;

EMPNO ENAME SAL

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

100 Nithya 5600

102 Saloni 5400

104 Pooja 5400

106 Aruna 7600

108 Sound 5600

110 Mahesh 4500

112 Nandhini 3500

Again Altering the above sequence

Alter sequence myseq1 nomaxvalue;

update sample set sno=myseq1.nextval;

now you can update the values as many times as you want, since there is no maximum limit to stop the generation of numbers

Drop sequence :

This command is used to drop any sequence

Syntax : drop sequence <sequence name>

Example : drop sequence myseq1;

Note :

Even though u altered the sequence, it will generate numbers from where it has left before altering the sequence and continues according to ur altered form

RowID:

When ever we insert a row, the system automatically generates Rowid’s and appends the rowid column and stores the values in it. each rowid is unique from one another.

To see Rowid’s of emp table, give the following command

Select rowid from emp;

Select rowid from dept;

To delete any Duplicate Rows (Using Row id’s)

If any duplicate rows are present in any table, then with the help of rowid, we can able to delete that particular records

select rowid, eno, ename, sal from sample;

ROWID SNO NAME

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

AAAAcUAACAAAAEbAAA 1 Nithya

AAAAcUAACAAAAEbAAB 2 Saloni

AAAAcUAACAAAAEbAAC 1 Mahesh

AAAAcUAACAAAAEbAAD 4 Aruna

AAAAcUAACAAAAEbAAE 2 Saloni

AAAAcUAACAAAAEbAAF 2 Saloni

Delete from customer where rowid=’ AAAAcUAACAAAAEbAAF’;

Delete from customer where rowid=’ AAAAcUAACAAAAEbAAE’

Views

noreply@blogger.com (Naveen) — Wed, 23 Mar 2011 12:19:00 +0000

Views

A view is like a window through which data on a table can be viewed or changed. It is a virtual table that is – it does not have any data of its own, but derives the data from the table it is associated with. It manipulates data in the underlying base table.

Syntax :

Create [or Replace] View <view name>

[ ( column1, column2,----)] as Select <select list>

Replace Option :

This option is used to replace the definition of existing view with new definition. It can be replaced with another view only, but not with a table.

Advantages of Views

Views restrict access to the database. Selecting from a view can display a restricted portion of the data base
Views allows users to make simple queries to retrieve the results from complicated queries
As a view does not store any data, the redundancy problem does not arise
One view can be used to transparently retrieve data from several tables.
Views also allow the same data to be seen by different users in different ways

Types of View

There are two types of view

1. Simple View

2. Composite View

Simple View

When a view is create basing on a single table then it is called a simple view

Syntax :

Create [or replace ] view <view name> as Select <select list> from <table1>;

Examples :

Create a view, which shows all employees details whose salary is between 2000 and 3000

Create view empsal as select * from emp where sal>=2000 and sal<=3000;

To see the details of a view, give the following command

Select * from <view name>

Ex : Select * from sample;

2. write a query which Creates a view, that shows all employees details who are belonging to dept 20

Create or replace view empsal as select * from emp where deptno=20

Ex : Select * from empsal;

3. Create a view, which shows all employees details whose salary is between 2000 and 3000 and who are belonging to dept 20

Create or replace view empsal as select * from emp where deptno=20 and Sal between 2000 and 3000;

We can change column names in a view at the time of creation

Create a view, which shows all employees details who belong to dept 20

create or replace view emp20 as select * from emp where deptno=20
create or replace view dept20(emp_no, emp_name, salary) as select empno, ename, sal from emp;

select * from dept20;

EMP_NO EMP_NAME SALARY

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

7369 SMITH 800

7499 ALLEN 1600

7521 WARD 1250

7566 JONES 2975

7654 MARTIN 1250

We can Create a view, using another view

create or replace view emp21 (ename,sal) as select empname,basic from emp20;

select * from emp21;

ENAME SAL

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

SMITH 800

JONES 2975

Mahesh 3000

ADAMS 1100

FORD 3000

Note :

If column names are not given then it defaultly takes all the given column names in the table
Number of columns in view ie in Bracket must be equal to number of columns in the select statement

Manipulating the Base Tables Through Views

The insert, update and delete commands can also be used with views. Using these commands with views is an indirect way of manipulating tables. Since a view nothing but a virtual table. Any manipulations ie deletion, updation will automatically affects the main table.

Example :

Create a view, which shows All students details Whose avg marks is >=60

Create or replace view studview as select * from students where average>=60;
select * from studview
Update students set roll=100 where roll=1;
Select * from studview;
Insert into students values(101,'Nayana Tara');
Select * from students;
delete from studview where roll=101;
select * from studview;
Select * from students;

Note : What ever operations you perform on views will be affected to the table that is associated with the view

To Create Read Only Views

We can create read only view. When a view is created with any arithmetic expression or with any group by functions then it cannot be modified or changed with insert or update or delete statements. So this can be called as a Read Only Views

Example :

Creates a view, with all department numbers and number of Employee’s working under each department

create view deptgroup as select deptno,count(*) "no of emps" from emp group by deptno
select * from deptgroup;

EPTNO no of emps

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

10 3

20 5

30 6

Note : When Ever We Apply Any Dml Operation On This View, We Will Get An Error Message

insert into deptgroup values(40,2);

*ERROR at line 1:

ORA-01732: data manipulation operation not legal on this view

Delete from deptgroup where deptno=10

ERROR at line 1:

ORA-01732: data manipulation operation not legal on this view

Composite View

A view created by retrieving data from 2 or more tables. A composite view can not be updated or we can not apply any DML operations except select statement. Because it contains different columns from different tables.

Syntax :

Create or replace view <view name> as select <fields list> from <table1>,<table2>,……..

1. create view which should show all columns in emp and dept table whose deptno in emp is equal to deptno in dept table

create or replace view empdept as select empno, ename, sal, dname, loc, dept.deptno from emp, dept where emp.deptno=dept.deptno

select * from empdept;

insert into empdept values(1000,'hello',5000,'Production','tirupati',20)

ERROR at line 1:

ORA-01776: cannot modify more than one base table through a join view

Note : Since here two table values are included. So at a time we cann’t insert or modify two tables data at a time

But if you give like this ,

update empdept set empno=1000 where empno=8900;

update empdept set empno=2000,ename='jj',sal=1000 where empno=1000

update empdept set dname='cmp',loc='tpt',deptno=30 where empno=2000

Note : Records will be updated, since you are updating values only on a single table related columns, if you apply any operations on both table columns then can not perform any operation

select * from empdept;

Dropping a view

Drop View : This command is used to drop any view from the database

Drop view <view name>

Example : Drop View empdept;

Note :

To show all Views details (both user defined and pre defined views)

Select * from all_Views;

To show all User Defined Views details

Select * from user_Views;
Select view_name from all_Views;

Joins

noreply@blogger.com (Naveen) — Wed, 23 Mar 2011 12:12:00 +0000

Joins

One of the most important features of SQL is the ability to define relationships between multiple tables and draw information from them in terms of these relationships, all within a single command.

With joins we can combine columns from different tables. The connection between tables is established through the WHERE clause

Table and column alias names

The full name of a column of a table actually consists of the table name followed by a dot and then the column name e.g. emp.empno, emp.ename etc

User can omit the table names if one is querying only single table at a time. Even when querying on multiple tables one can still be able to omit the table names provided that all the column names are different.

Types of Joins

There are five types of joins, they are

1. Equi Join ( = )

2. Non Equi Join ( < , > , >=, <=, !=)

3. Outer Join ( + )

4. Self Join

5. Cross Join (Introduced in Orace 9i)

General Syntax :

Select < select list > from <table1>,<table2>,-----, <table N>

Where <table1.column1>=<table2.column2> and ------

Equi Join

When two tables joined together using equality operator, then it is called Equi Join. Table prefixes are utilized to prevent ambiguity and the Where clause specifies the columns being joined

Example :

List the employee numbers, names, department numbers and dept names

Select empno,ename,emp.deptno,dname from emp,dept

where emp.deptno=dept.deptno;

Here, the deptno column exists in both the tables. To avoid ambiguity, the column name should be qualified with the table name ( or with an alias of table name)

Both the table names need to be specified(emp and dept) the where clause defines the joining condition ie joining the deptno of emp table to the deptno of dept table. Here, it checks for the equality of values in these columns

Using Table Aliases

It can be very tedious to type table names repeatedly. Temporary labels ( or aliases) can be used in the FROM clause. These temporary names are valid only for the current select statement. Table aliases should also be specified in the select clause. Table aliases can be up to 30 characters in length, but the shorter they are the better.

Example : List Employee Numbers, Names, Department Numbers, And Department Names From Emp And Dept Tables Using Aliases

select e.empno, e.ename, e.deptno, d.dname from emp e, dept d

where e.deptno=d.deptno;

Non Equi Join

Using this we can retrieve data from two or more tables by specifying a condition on the common column with any non-equi join operators (>, <, >=, <=,!=)

Example :

Write a query to display those employees details whose salary of emp1 table >= salary of emp table employees

select eno,ena,emp1.sal,emp.sal from emp1,emp where emp1.sal>=emp.sal

Outer Join

It is used to retrieve the common data from both tables and all values from the table having outer join operator (+)

There are 2 types of outer joins

Left Outer Join
Right Outer Join

Left Outer Join

It is used to retrieve all rows from the table having the outer join

operator which is left side to the outer join and common values from another

table

Example :

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

select empno,ename,sal,emp.deptno,dname,loc from emp, dept where

emp.deptno(+)=dept.deptno

Right Outer Join

It is used to retrieve all rows from the table having the outer join

operator which is right side to the outer join and common values from another

table

Example :

Display the list of employees working in each department. Display the employee information even if no such department belongs to the dept table

select empno,ename,sal,emp.deptno,dname,loc from emp, dept where

emp.deptno=dept.deptno(+)

if the symbol (+) is placed on the other side of the equation then all the employee details with no corresponding department name and location , will be displayed with NULL values in Dname and Loc columns

Rules to place ( + ) Operator

1. The outer join symbol (+) can not be on both the sides

2. We can not outer join the same table to more than one other table in a single Select statement

3. A condition involving an outer join may not use the IN operator or be linked to another condition by the OR operator

Self Join

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

Example : list out the names of the manager with the employees in the emp table

Select worker.ename, manager.ename ‘Manager’ from emp worker, emp manager where worker.mgr=manager.empno;

Cross Join (oracle 9i concept)

A cross joins returns what’s known as a Cartesian product. This means that the join combines every row from the left table with every row in the right table. This type of join can be used in situations where it is desired, to select all possible combinations of rows and columns from both tables. This kind of join is usually not preferred as it may run for a long time and produce a huge result set that may not be useful

Syntax

Select <select list > from table1 alias cross join table2 alias

Example :

Display the list of employees working in each department. Display the employee information even if no such department belongs to the dept table and also display the department details even if no employee belongs to that department

select empno,ename,sal,emp.deptno,dname,loc from emp Cross join dept

Sub Queries Or Nested Queries

noreply@blogger.com (Naveen) — Tue, 22 Mar 2011 17:44:00 +0000

Sub Queries Or Nested Queries

A query within another query is called a sub query. We can define any number of sub queries with in a query. But the system executes the inner most query first. If we are using relational operators between the queries then the sub query must return a single value.

The Following Points should be kept in Mind While using SubQueries

The inner Query must be enclosed in parentheses
The Inner query must be on the right hand side of the condition
The sub query may not have an order by clause
The Order By clause appears at the end of the main select statement
Sub queries are always executed from the most deeply nested to the least deeply nested

Note : If we use operators “in” and “not in” then the sub query followed by the operators can return more than one value.

Example Queries

1.List the employee’s Details who belong to the department of “Ward”

In general, first we have to know; what the department of “Ward” and then we can display all the employee’s details belonging to “Ward”

select * from emp where ename='WARD';

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

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

select * from emp where deptno=30;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

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

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

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

7698 BLAKE manager 7839 01-MAY-81 2850 30

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

Using Sub Queries,

select * from emp where deptno=(select deptno from emp where ename='WARD');

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

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

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

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

7698 BLAKE manager 7839 01-MAY-81 2850 30

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

2.List the employee’s details whose salary is greater than the average salary of all the employees

In general, first we have to know; average sal of all employee’s and then we can display all the employee’s details whose salary is > average salary of all employees

select avg(sal) from emp;

AVG(SAL)

---------

2161.3636

select * from emp where sal>=2161;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

7566 JONES Manager 7839 02-APR-81 2975 20

7698 BLAKE Manager 7839 01-MAY-81 2850 30

7782 CLARK Manager 7839 09-JUN-81 2450 10

7788 SCOTT Analyst 7566 19-APR-87 3000 20

7839 KING President 17-NOV-81 5000 10

Using Sub Queries,

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

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

7566 JONES Manager 7839 02-APR-81 2975 20

7698 BLAKE Manager 7839 01-MAY-81 2850 30

7782 CLARK Manager 7839 09-JUN-81 2450 10

7788 Mahesh Analyst 7566 14-FEB-78 3000 20

7839 KING President 17-NOV-81 5000 10

3.Write a Query to find the details of the department whose manager’s empcode ‘7698’

In general, first we have to know; department number whose manager empcode is “7698” and then we can display all the department details of that employee

select * from dept;

DEPTNO DNAME LOC

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

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

select deptno from emp where mgr='7698'

DEPTNO

---------

Using sub Query,

select * from dept where deptno=(select deptno from emp where mgr='7698')

ERROR at line 1:

ORA-01427: single-row subquery returns more than one row

select * from dept where deptno in (select deptno from emp where mgr='7698')

DEPTNO DNAME LOC

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

30 SALES CHICAGO

or you can give, like this

select * from dept where deptno=(select distinct deptno from emp where mgr='7698');

DEPTNO DNAME LOC

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

30 SALES CHICAGO

4.List the details of all the employee’s who earn lowest salary in each department

select * from emp where sal in(select min(sal) from emp group by deptno)

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

7369 SMITH Clerk 7902 17-DEC-80 800 20

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

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

7782 CLARK Manager 7839 09-JUN-81 2450 10

Co related Sub query:

A sub query is executed once for the parent statement whereas the correlated sub query is executed once for each row of the parent query.

Example:

Find all employees who earn more than the average salary in their department.

SELECT last-named, salary, department_id FROM employees A WHERE salary > (SELECT AVG (salary) FROM employees B WHERE B.department_id =A.department_id Group by B.department_id)

Constraints

noreply@blogger.com (Naveen) — Tue, 22 Mar 2011 17:41:00 +0000

Constraints

These are the conditions or rules that we impose on any column for entering valid data into the table. Constraints are a part of the table definition that are used to limit the values entered into its columns.

Note : Constraints can be imposed in two ways

(i) Table Level :

Imposing constraints on a table level by specifying the constraint type at the end of the columns is called Table Level Constraints. In Table Level, the Constraint key word followed by Constraint name must be defined otherwise error comes.

(ii) Column Level :

Imposing constraints on a column by specifying the constraint type beside the column name is called Column Level Constraints

Constraints can be defined in two ways

1. In the Table Definition itself

2. Using Alter Command

The following are a list of constraints

NOT NULL CONSTRAINT
UNIQUE CONSTRAINT
PRIMARY KEY CONSTRAINT
CHECK CONSTRAINT
DEFAULT
REFERENCES (FOREIGN KEY CONSTRAINT )

NOT NULL

The Not Null constraint specifies that a column can not contain Nulls. To satisfy this constraint every row in the table must contain a value for the column.

if you do not specify not null, the column can contain nulls by default

Syntax:

Not Null in Column Level

Ø Create table <Table Name> ( Column <Data type><size> Not Null,

Column2 <Data Type><size> ,-------);

Adding Not Null Using Alter Command for Existing Table

Ø Alter Table <Table Name>

Modify (<Column Name > <data type> (size) not null)

Note :

Column must be empty to impose the not Null constraint with alter table statement.
Table Level is not applicable for Not Null constraint

Example :

Create table sample1(sno number(3), sname varchar2(20) not null);

Ø insert into sample1 values(&sno,'&sna');

Enter value for sno: 100

Enter value for sna: Mahesh

Enter value for sno: 101

Enter value for sna: (nothing is entered)

Error Comes

mandatory (NOT NULL) column is missing or NULL during insert

Unique Constraint

This constraint does not allow duplicate values into any column.

Syntax: (For Column Level)

Create Table <Table Name>(Column1 <Data Type><Size> Unique,

Column2 <Data Type><Size>,--------);

Syntax: (For Table Level)

Create Table <Table Name>(Column1 <Data Type><Size>,

Column2 <Data Type><Size>,--------,

Constraint <Constraint Name> Unique <Column Name>);

( Or )

Example : Imposing unique constraint Table Level

create table customer(cno number(3),cname varchar2(20),

constraint con_un unique(cno));

Ø insert into customer values(&cno,'&cname');

Enter value for cno: 100

Enter value for cname: Mahesh

Enter value for cn: 100

Enter value for c: Nithya

Error Comes

unique constraint (SCOTT.CON_UN) violated

Example : Imposing unique constraint Column Level

create table customer1(cno number(3) unique ,cname varchar2(20));

Ø insert into customer1 values(&cno, '&cname');

Enter value for cno: 100

Enter value for cname: Mahi

Enter value for cn: 100

Enter value for c: Prasad

Error Comes

unique constraint (SCOTT.SYS_C00385) violated

Default Constraint :

The default constraint is used to specify a default value for the column, when you want to given default values. You can give default value as NULL or some other value.

Note : Only Column level definition can be applied to “Default constraint”

Syntax : for column level

Create Table <Table Name>(Column1 <Data Type><Size> default <value>,

Column2 <Data Type><Size>,--------);

Example :

Ø create table sample2(sno number(3) default 10,sname varchar2(20), phone

varchar2(10) default null)

Ø insert into sample2 values(1,'Mahesh','11111')

Ø insert into sample2 values(2,'Prasad','22222')

Ø insert into sample2(sno, sname) values (3,'Nithya')

Ø insert into sample2(sno, sname) values (4,'Saloni')

Ø select * from sample2;

SNO SNAME PHONE

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

1 Mahesh 11111

2 Prasad 22222

3 Nithya

4 Saloni

Example : 2

create table sample3(sno number(3) default 100, sname varchar2(10) not null);

Ø insert into sample3 values(200,'Nithya')

Ø insert into sample3 values(201,'Aruna')

Ø insert into sample3(sname) values('Mahesh')

Ø insert into sample3(sname) values('Saloni')

select * from sample3;

SNO SNAME

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

200 Nithya

201 Aruna

100 Mahesh

100 Saloni

Check Constraint

The check constraint explicitly defines a condition. It will put restrictions on the range of the column values and the values to be accepted into the column. The condition of a check constraint can refer to any column in the table. But it can not refer to any column in other tables.

Syntax : (column Level)

Create table <table name> (<column1> <datatype> [size] check(condition),

<column2><data type> <size>,-----);

Example:

Create table items(itemno number(3) check(itemno>=100),itemname varchar2(10));

Ø Insert into items values (100,'Rice')

Ø insert into items values(101,'Paste')

Ø insert into items values(99,'Chacolate') à Error comes

Syntax : (Table Level)

Create table <table name>

(<column1> <datatype> [size],

<column2><data type> <size>,

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

constraint <constraint name> check(<column name> with condition));

Example :

create table item1(itno number(3), Itname varchar2(10),

Constraint con_check check (itno>=100));

Ø Insert into item1 values (100,'Rice')

Ø insert into item1 values(101,'Paste')

Ø insert into item1 values(99,'Chacolate') à Error comes

Primary Key Constraint

It avoids null values and also it does not allows duplicate values. Ie A Primary key is nothing but combination of not null and unique constraints

To Impose Primary Key On Any Column, You Can Use Either Table Level Or Column Level

Syntax: Table Level

create table <tablename>(column1 <datatype><size>,

column2 <datatype><size>,--------,

constraint <constraint name> primary key <column name>);

Ex:

create table items1(itemno number(3),itemname varchar2(20),

constraint con_pk primary key(itemno));

Ø insert into items1 values(100,’Rice’);

Ø insert into items1 values(100,’paste’);

Error Comes

unique constraint (SCOTT.CON_PK) violated

Syntax : Column Level

create table <tablename>(column1 <datatype><size> primary key ,

column2 <datatype><size>,--------);

Example :

create table items2(itemno number(3) primary key, itemname varchar2(20));

Ø insert into items2 values(100 ,'Wheat');

Ø insert into items2 values(100 ,'Rice');

Error Comes

unique constraint (SCOTT.CON_PK) violated

Example 2

Ø insert into item values(null ,'Wheat');

Error Comes

mandatory (NOT NULL) column is missing or NULL during insert

Foreign Key Constraint

This is used to establish relationship between 2 or more tables for inserting common or similar values into the related columns of the table.

The table from which we are establishing relation is called child table. Because the related column in the child table depends on another table for its values.

The table on which other tables are depending on the table, to which we are establishing a relation is called Mother table.

If two tables are having this relationship then the system checks whether the new value that we are inserting is related column of child table exists in the mother table or not.

There are five major restrictions related to foreign key

The Mother table column related must have primary key
We can not drop the mother table with out child table provided.
The data type and the size of the related columns in both the tables must be same and name can be different
We can establish relation to any number of tables
We can not impose foreign key which are having values.

Syntax : (Column Level)

Create Table <Table Name>

(<column1><data type><size>

References <Mother Table>(<column name>),

<column2><data type><size>,

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

<columnN><data type><size>);

Syntax : (Table Level)

Create Table <Table Name>

(<column1><data type><size>

<column2><data type><size>,

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

<columnN><data type><size>,

constraint <constraint Name>

foreign key(child column)

References <Mother Table> (<column name>));

Making relationships between two tables

1. First Create the Mother Table or Parent Table

create table bankmast

(accno number(3) primary key,

accna varchar2(10) not null,

balance number(8,2));

2. Next Create the Child Table

create table banktrans

(acno number(3) references bankmast(accno),

trmode char(1),trdate date,

amount number(8,2));

3. Next insert the values in to mother table first,

Ø insert into bankmast values(&accno,'&accna',&bal);

select * from bankmast;

ACCNO ACCNA BALANCE

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

100 Priya 5000

101 Mahesh 6500

102 Saryu 4500

103 Nandhini 7600

4. Next insert values into child table

Ø insert into banktrans values(&acno,'&trmode','&trdate',&amt);

Ø insert into banktrans values(100,'D','5-May-05',5000);

Ø insert into banktrans values(102,'W','15-Apr-05',2000);

Ø insert into banktrans values(104,'D','10-Feb-05',10000);

ERROR : (since there is no such account number present in the mother table)

ORA-02291: integrity constraint (SCOTT.SYS_C00674) violated - parent key not found

SQL> select * from banktrans;

ACNO T TRDATE AMOUNT

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

100 d 05-MAY-05 5000

102 w 01-JUN-05 2000

Dropping Relationship Tables

Note :

if you try to drop the mother table when child table is present, it will give an error message

drop table bankmast;

ERROR :

ORA-02449: unique/primary keys in table referenced by foreign keys

To drop the mother table, First Drop the Child table and then drop the mother table

Drop table banktrans;

Drop table bankmast;

PLSQL in 21 Hours

Oracle Basic Architecture

Frequently Used Data Dictionary Queries

Wishing Thanks to My Readers.

Oracle Interview Based Queries-4

Oracle Interview Based Queries-3

Oracle Interview based Queries - part2

Oracle interview based Queries- 1

Oracle Query Tuning

Explain Plan

SQL Loader

Materialized view

Function

Stored Procedure

Syntax of Procedure

Trigger

DDL TRIGGERS

INSTEAD OF TRIGGER

Exception Handling

Cursor

Control structures

Loops

Partitions

Partitions

Clusters

Creation of cluster for a table

To drop any cluster

Indexes

Indexes

Types of Index

Note :

1. There is a problem in the above index, the index will give an error only when both columns value are given duplicate values. If any one is different the index will accept those values.

2. Once a column is indexed already, it is not possible to reindex that column to any other index file

Sequence

Sequences

Creating Sequence

Using Insert Command

Using Update Command

Altering a Sequence

Example

Syntax : drop sequence <sequence name>

To delete any Duplicate Rows (Using Row id’s)

Views

Views

Create or replace view studview as select * from students where average>=60;

But if you give like this ,

Joins

Joins

Sub Queries Or Nested Queries

Sub Queries Or Nested Queries

ORA-01427: single-row subquery returns more than one row

Constraints

Constraints

Error Comes

Example : Imposing unique constraint Table Level

Error Comes

Error Comes

Error Comes

2. Next Create the Child Table

3. Next insert the values in to mother table first,

101 Mahesh 6500