Pl Sql Practicals

1. I want to display 1 to 10 numbers using one select statement. Select level from dual CONNECT BY LEVEL<=10; LEVEL ---------1 2 3 4 5 6 7 8 9 10 10 rows selected. OR Select rownum from USER_OBJECTS where rownum <= 10 order by rownum ASC; ROWNUM ---------1 2 3 4 5 6 7 8 9 10 10 rows selected. How to delete duplicate rows from a specified table (only single table) how do you know which join is need to be used. DELETE FROM emp a WHERE ROWID> (SELECT MIN (ROWID) FROM emp b WHERE A.empno=b.empno); Why should required for Indexed by table in pl/sql. For good performance Index is required.

What is the diff between Static Queries and Dynamic queries give me some examples. Static queries are the queries we normally use like SELECT * FROM EMPLOYEE. Where as dynamic queries can be built and executed dynamically. sp_executesql system stored procedure is used to execute dynamic sql statements. E.g.: sp_executesql N'select * from employee' Dynamic sql queries will take more time to execute when compared to static queries. What is the basic function for master, msdb, tempdb databases? Master, MSDB and TempDB these are the inbuilt Database which r provided by SQL SERVER. All have their own functionality & responsibility like. 1. Master: Master DB is responsible for storing all information at system level. 2. MSDB: it is Microsoft DB; it creates a Copy of whole Database in it. 3. TempDB: it stores all the log information of Server, it will initialize automatically whenever Server will start, the max. Size allotted fro TempDB is 10MB. I have doubt that any one tell ref cursor comes in sql pl/sql? pls clarify? Ref cursor comes in PL/SQL. You can return a ref cursor in a function. A ref cursor is useful while returning more values. It will use in PL/SQL. Reusability of Cursor is nothing but "REF CURSOR”. These are of 2 types. 1. Weak Ref cursor 2. Strong ref Cursor. If I have a table T with 4 rows & 2 columns A & B. A has values 1,2,3,4. And B has 10,20,30,40. Write an Update SQL query which can Swap the values of A & B for all records. (Do not use a sub-query)? UPDATE T SET A = B, B=A; What is a REF CURSOR? Compare strong and week ref cursor types. For the strong ref cursor the returning columns with data type and length need to be known at compile time. For the weak ref cursor the structure does not need to be known at compile time. TYPE WEAK_REF_CURSOR IS REF CURSOR; TYPE STRONG-_ REF_CURSOR IS REF CURSOR RETURN TABLE_NAME%ROWTYPE;

How one can easily select all even, odd, or n’th rows from a table using SQL queries? Odd number of records: Select * from emp where (rowid, 1) in (select rowid, mod (rownum, 2) from emp); Output:1 3 5 Even number of records: Select * from emp where (rowid, 0) in (select rowid, mod (rownum, 2) from emp); Output:2 4 6 For n’ th number, Example we r considering number n=10 SELECT * FROM emp a WHERE 10 = (SELECT COUNT (rowid) FROM emp b WHERE a.rowid >= b.rowid); What is the difference between the query and corelated query? Select * from emp; --->> query Select * from emp where sal = (select max (sal) from emp); --> Here a sub query is also there Select * from emp outer where sal= (select avg (sal) from emp e Where dept.e=dept.outer) --> corelated query Or Select * from emp;

----- is a query;

And Co-related query is sub one of sub query (sub query means--whose returning values are filtering the condition of the main query) SELECT * FROM EMP E WHERE E.SAL> (SELECT AVG (SAL) FROM EMP F WHERE E.DEPTNO= F.DEPTNO); SO HERE WE R FINDING THE EMPLOYEE WHO HAS GETTING MORE THAN

AVG (SAL) OF RESPECTING DEPTNO. AND CO RELATED SUB QUERY ALWAYS USE ALIAS NAME FOR TABLE, IT SIMILAR LIKE JAIN (SELF JOIN) Use of IN/ANY/ALL IN - used to select multiple rows based on any of the key provided SQL - select distinct employeeid from orders where orderid in (select orderid from order details where discount >= 10) ANY - used in case of relational queries to compare result with any of the key. SQL - select custID from orders where regionID != "E" and discount > any (select discount from orders where regionID = "E" and discount > 3) ALL - used in case of relational queries to compare result with all of the keys. SQL - select custID from orders where regionID != "E" and discount > all (select discount from orders where regionID = "E" and discount > 3) Or IN- It will return the value based on the parameter; E.g. select * from emp where salary in ('1000','3000'); ANY-It will compare with any value that has been returned by the parameter; Select * from emp where salary > any (select salary from emp where deptno=10) The salary will be compared with any value that has been returned by the subquery. ALL-It will compare with max/min value that has been returned by the subquery; Select * from emp where salary > all (select salary from emp where deptno=10) The salary will be compared with the longest value that has been returned by the sub query. What are integrity constraints? Which prevents the user from entering the duplicating into tables or views is called integrity constraint. Ex: primary key constraint, foreign key constraint, unique constraint, checks constraint. Integrity constraint is a rule that restricts a column of a table. Or

Data integrity allows defining certain data quality requirements that the data in the database needs to meet. If a user tries to insert data that doesn't meet these requirements, Oracle will not allow so. Constraint types There are five integrity constraints in Oracle. Not Null A column in a table can be specified not null. It's not possible to insert a null in such a column. The default is null. So, in the following create table statement, a null can be inserted into the column named c. Create table ri_not_null ( a number not null, b number null, c number ); Insert into ri_not_null values ( 1, null, null); Insert into ri_not_null values ( 2, 3, 4); Insert into ri_not_null values (null, 5, 6); The first to records can be inserted; the third cannot, throw a ORA-01400: cannot insert NULL into ("RENE"."RI_NOT_NULL"."A"). The not null/null constraint can be altered with alter table ri_not_null modify a null; After this modification, the column a can contain null values. Unique Key The unique constraint doesn't allow duplicate values in a column. If the unique constraint encompasses two or more columns, no two equal combinations are allowed. Create table ri_unique ( a number unique, b number ); However, if a column is not explicitely defined as not null, nulls can be inserted multiple times: Insert Insert Insert Insert Insert Insert

into into into into into into

ri_unique ri_unique ri_unique ri_unique ri_unique ri_unique

values values values values values values

(4, 5); (2, 1); (9, 8); (6, 9); (null, 9); (null, 9);

Now: trying to insert the number 2 again into a:

Insert into ri_unique values (2, 7); This statement issues a ORA-00001: unique constraint (RENE.SYS_C001463 violated). Every constraint, by the way, has a name. In this case, the name is: RENE.SYS_C001463. In order to remove that constraint, an alter table ... drop constraint ... is needed: Alter table ri_unique drop constraint sys_c001463; Of course, it is also possible to add a unique constraint on an existing table: Alter table ri_unique add constraint uq_ri_b unique (b); A unique constraint can be extended over multiple columns: Create table ri_3 ( a number, b number, c number, Unique (a,b) ); It is possible to name the constraint. The following example creates a unique constraint on the columns a and b and names the constraint uq_ri_3. Create table ri_3 ( a number, b number, c number, Constraint uq_ri_3 unique (a,b) ); Primary Key On a technical level, a primary key combines a unique and a not null constraint. Additionally, a table can have at most one primary key. After creating a primary key, it can be referenced by a foreign key. Create table ri_primary_key ( a number primary key, b number ); Primary keys can explicitly be named. The following create table statement creates a table with a primary key whose name is pk_name. Create table ri_primary_key_1 ( a number, b number, c number, Constraint pk_name primary key (a, b)

); Foreign Key A foreign key constraint (also called referential integrity constraint) on a column ensures that the value in that column is found in the primary key of another table. If a table has a foreign key that references a table, that referenced table can be dropped with a drop table... Cascade constraints. It is not possible to establish a foreign key on a global temporary table. If tried, Oracle issues a ORA-14455: Attempt to create referential integrity constraint on temporary table. Check A check constraint allows stating a minimum requirement for the value in a column. If more complicated requirements are desired, an insert trigger must be used. The following table allows only numbers that are between 0 and 100 in the column a; Create table ri_check_1 ( a number check (a between 0 and 100), b number ); Check constraints can be added after a table had been created: Alter table ri_check_1 Add constraint ch_b check (b > 50); It is also possible to state a check constraint that checks the value of more than one column. The following example makes sure that the value of begin_ is smaller than the value of end_. Create table ri_check_2 ( Begin_ number, End_ number, Value_ number, Check (begin_ < end_) ); Select 1, col1, col2 from table1. Output? SQL> SELECT 1, emp_no, emp_name FROM emp; 1 EMP_NO EMP_NAME --------- --------- ------------------------1 100 SELVA 1 101 RAJ 1 102 A S KALA

1 1 1 1 1 1 1 1 1

103 104 105 106 107 108 109 110 111

JESLIN FANTA MALAR ANITA MURUGU SRIVATSAN SARABOOT KARTHI SIR SUDHA MERCHI SAVI

12 rows selected. Does view contain data? No, View is a logical table associated with a query. It will not store any data in it. Only the query with which the view is created is stored in the database. NO, the view is a logical table based on one or more tables or views. A view in practicality contains no data by itself. it contains only select statements. Which will fire first? Trigger or Constraint? Always constraint will fire first. If i perform any operation on views such as insert, delete etc will my base table get affected????? Yes if it is simple view. Error if it is compute view Or It will affect ur base table only when u having a simple view and if you put null in the field which is primary key of the base table then also the base table will not affected. And if you are using the compute view then u r not able to insert or delete the records. How to retrieve only duplicate values in a table. For example have a table called student like below. STUD-NAME SUBJECT --------- -----STUD1 A STUD2 B STUD2 A STUD1 A In this structure 1 row is duplicated in 4’th. So we can fetch the student name using the below query. SELECT stud-name FROM STUDENT GROUP BY stud-name, subject HAVING COUNT > 1.

Or Select count (), fees from Table Name Group by Having count ()>1; Write the Sql query for creating database backup? If you need to take tha backup of the database on ur hard disk then u can use the following query: Exp userid=system/manager@orcl file=location\abc.dmp full=y Log= abc What is TABLE SPACE? Database is logically divided into three tablespaces. Once creates the database then automatically create tablespace is also called SYSTEM tablepace.tablespace contain data dictionary of all data. Or A Table space is a logical group of data files in a database. A typical data base consists at least one table space, and usually two or more. In a database a table space plays a role similar to that of a folder on the hard drive of a computer. What is SGA? System global area (SGA) is a shared memory region allocated by ORACLE that contains data and control information for one ORACLE instance What is pseudo column? A pseudo column is an item of data which does not belong in any particular table but which can be treated as if it did. Any SELECT list of columns can include these pseudo columns. SYSDATE ROWNUM ROWID UID USER

current date and time sequence number assigned to retrieved rows unique identifier for a row number associated with a user userid of current user

Or Pseudo column is a act as an database table column,but its not a actual column in a table, we can fetch value from a pseudo column.

Ex. User, UserID RowID, RowNum Level CurrVal, NextVal Sysdate How to retrive only second row from table? SELECT * FROM emp a WHERE 2 = (SELECT COUNT (rowid) FROM emp b WHERE a.rowid >= b.rowid); Find the 2’nd Highest Salary?? SELECT * FROM EMPLOYEES A WHERE 2=(SELECT COUNT(DISTINCT SALARY) FROM EMPLOYEES B WHERE B.SALARY>=A.SALARY); Delete duplicate records in the emp table. DELETE FROM EMP E WHERE E.ROWID <> (SELECT MIN (ROWID) FROM EMP E2 WHERE E.EMP_NO=E2.EMPNO); SELECT * FROM EE E1 WHERE E1.ROWID<>(SELECT MAX(ROWID) FROM EE E2 WHERE E1.ENO=E2.ENO); Or Delete from emp where rowid not in (select min (rowid) from emp group by empno; Delete the emps whose salaries are lowest sals of their own dept. Delete from emp where (depno, nvl2 (sal, sal, 0)) in (Select depno,min(nvl2(sal,sal,0)) from emp group by depno ) Define a variable representing the expression used to calculate on emps total annual remuneration.use the variable in a statement which finds all emps who can earn 30000 a year or more. Select Empno,Annual_Salary=sum(Month_Salary) from Employee_Salary Group by Empno having sum (Month_Salary)>30000 Or

Example: Declare Cursor cl is select empname,salary,(salary*12)annual renumeration Begin For i in cl Loop if i.annual renumeration > 30000 then dbmsoutput.putline(empname); End if End; Check whether all the emp numbers are indeed unique. Select empno from emp where empno is not null group by empno Having count (*) >1; Can a table have two primary keys? Yes, a table may have more than 1 primary keys but, of them we only choose 1 column as identifiable column called primary key. The other columns are called surrogate keys or candidate key. Create table emp(empno number(5), ename varchar2(20), deptno number(5) add primary key Pk_constraint( empno, deptno)); What is purge command explain about oracle performance tuning Use the PURGE statement to remove a table or index from your recycle bin and release all of the space associated with the object, or to remove the entire recycle bin, or to remove part of all of a dropped tablespace from the recycle bin. PURGE RECYCLEBIN; Or Purge command is used to clear the recycle bin. It can also be used with drop command Ex. Drop table purge; This command will clear away the table from database as well as from the recycle bin. After firing of purge command you cannot retrive the table using flashback query.

What is the difference between RDBMS and DBMS?

1) DBMS permits only one person to access the database at a given time.But RDBMS gives the multiple accesses the database at a given time. 2) DBMS organised the data in any formmate but RDBMS allows only in row and column format. 3) In DBMS we can not create the the relationshs but in RDBMS we can not create the relationship between the tables Write a simple program on cursors Declare Cursor cur_t is select * from test; var_t test%rowtype; Begin Open cur_t; Loop Fetch cur_t into var_t; Exit when cur_t%notfound; dbms_output.put_line('a: ' || var_t.a || ', b: ' || var_t.b); End loop; End; / Difference between views and materialized views? View: View is a virtual table, a query attached to it. Actually it has not stored query results. It will execute and returns rows. 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 views, which store data based on remote tables are also, know as snapshots. A materialized view can query tables, views, and other materialized views. Collectively these are called master tables (a replication term) or detail tables (a data warehouse term). I want to display the employees who have joined in last two months. (It should be executed randomly means If I execute the query in March it should display Jan and Feb joined employees. Same query if i execute in Feb, 2007 it should display dec, 2006 and Jan 2007 joined employees. Select * from emp Where trunc(months_between(sysdate, hiredate))=2;

OR

Select * from af where months_between(sysdate,hiredate)<=2 OR Select * from emp where hiredate between add_months(trunc(sysdate,'Month'),-2) And trunk (sysdate,'Month')-1 This works for sure. 100 % How to retrieve Duplicate Rows only in a Table? Suppose if a Table Name is "Education". It consists of multiple columns. Then if we insert rows into this table with duplicate records then how can we retrieve only duplicate records from that table? select * from emp a ,(select empno,count(*) from emp group by empno having count(*) > 1) b where a.empno = b.empno; Differentiate between %type and %rowtype attribute in Oracle PL/AQL programming? ROWTYPE is declared at the record level and the TYPE is declared for the column level. (TABLE Level). %type and %rowtype provides data independence, reduces maintenance cost and allows programs to adapt as the database changes due to new business requirements. %rowtype is used to declare a record with the same types as found in the specified database table, view or a cursor. %Rowtype is used to declare a record that represents a particular row of a table. %type is used to declare a field with the same type as found in the specified table's column. (COLUMN Level). %Type is used to change the size of any datatype but only for one record. SQL> select * from empn; EMP_ID FIRST_NAME LAST_NAME HIREDATE --------- ------------------------- ------------------------- --------- 101 SELVA RAJ 20-MAY-85 102 NANDHINI DEVI 15-APR-87 103 JESLIN SHANTHAMALAR 25-MAR-84 104 MERCY JAYANTHI 26-MAY-85 105 SATHISH KUMAR 27-JAN-90 110 A S KALA AS 12-SEP-03 121 MALAR SANTHA 02-FEB-01 126 PADMA SHEELA 21-JAN-05 8 rows selected. SQL> declare

2 3 4 5 6 7 8 9 10 11 12 13 14 15

101 102 103 104 105 110 121

v_EMP_ID empn.EMP_ID%type; v_FIRST_NAME empn.FIRST_NAME%type; cursor empn_cursor is select EMP_ID,FIRST_NAME from empn; begin open empn_cursor; loop fetch empn_cursor into v_EMP_ID,v_FIRST_NAME; exit when empn_cursor%rowcount>7; dbms_output.put_line(to_char(v_EMP_ID)||' '||v_FIRST_NAME); end loop; close empn_cursor; end; /

SELVA NANDHINI JESLIN MERCY SATHISH A S KALA MALAR

What is a constraint? Types of constraints? Constraint is a restriction on table.There is two types of constraints, 1. 2. 1. 2. 3. 4. 5. 6.

Ananymous constraint and named constraint. Under named and anonymous constraints there are Primary key constraint Foreign key constraint Not null constraint Check constraint Unique constraint. Default.

What is user defined stored procedures? Stored procedures are basically set of sql statements. User defined stored procedures are created by users in which they can use functions, procedures to get the desired result from database. What is an index and types of indexes? How many numbers of indexes can be used per table? Indexes are used for the faster retrieval of data. Two types of indexes: Cluster index, unclustered index We can have one cluster index and upto 249 cluster indexes for a table What is normalization?

Normalization is a rule applied on the database table in order to remove redundancy of data. Normalization is the process of organizing the table to remove redundancy. Normalization is a process to remove data redundancy Normalization is the process of breaking of data and storing it into different tables to reduce the redundency and to avoid nulls. 1NF Eliminate Repeating Groups - Make a separate table for each set of related attributes, and give each table a primary key. 2NF Eliminate Redundant Data - If an attribute depends on only part of a multivalued key, remove it to a separate table. 3NF Eliminate Columns Not Dependent on Key - If attributes do not contribute to a description of the key removes them to a separate table. BCNF Boyce-Codd Normal Form - If there are non-trivial dependencies between candidate key attributes, separate them out into distinct tables. 4NF Isolate Independent Multiple Relationships - No table may contain two or more 1:n or n:m relationships that are not directly related. 5NF Isolate Semantically Related Multiple Relationships - There may be practical constrains on information that justify separating logically related many-to-many relationships. ONF Optimal Normal Form - a model limited to only simple (elemental) facts, as expressed in Object Role Model notation. DKNF

Domain-Key Normal Form - a model free from all modification anomalies.

What is INSTEAD OF trigger? Instead of trigger is used to update the database tables associated with the view instead of updating the view directly. How to retrieve the top 3 salaries of the table using rownum Select salary_column from salary_table where rownum<4 order by salary_column desc; Or Select e.ename,e.sal from emp e where 3>(select count (distinct(b.sal)) from emp b where b.sal>e.sal)

Or

Select empname,sal from (select empname,sal from dh1 order by sal desc) where rownum < =4 order by sal desc Or Select sal from emp a where 3> (select Count (distinct (sal)) from emp b where a.sal
substr('hello',2,3) will return ' el l'

INSTR is used to find the position of any particular character in a word which returns numeric value. instr('hello','e') - will return the position of 'e' as 2 SELECT instr('hello','e') FROM dual; SELECT substr('hello',2,3) FROM dual; What is ON DELETE CASCADE? The main use of ON DELETE CASCADE is to delete the child records automatically from a table when the related records from master table are deleted. Assign the ON DELETE CASCADE clause while creating the foreign key

If a View on a single base table is manipulated will the changes be reflected on the base table? Yes it will be reflected in the base table. Yes, definitely. Since the view is based on a single table and falls within the category of simple view. Any change / Modification on the records will be reflected in the base table. What are CYCLE/NO CYCLE in a Sequence? NO CYCLE is default when sequence is created CYCLE statement is given when the sequence has to repeat its number generation between START WITH and MAX VALUE. How to access the current value and next value from a sequence? Select sequencename.currval from dual Select sequencename.nextval from dual Explain Connect by Prior? Retrieves rows in hierarchical order. E.g.:

select empno, ename from emp where

The start with... connect by clause can be used to select Data that has a hierarchical relationship (usually some sort of parent->child (boss->employee or thing->parts). Select a.*, level from emp a Start with mgr is null Connect by mgr = prior empno; What is an Integrity Constraint? Integrity Constraints is used by oracle to check data Integrity while performing operations into database. Like columns is defined as not null. User cannot insert null Values into this column. Constraints: 1. 2. 3. 4. 5.

Primary Key Unique Key Not Null Referential Integrity (Foreign Key) Check Constraints

Integrity Constraints prevent invalid data entry into the Table. These constraints set a range and any violation takes Place oracle prevents the user from performing manipulation on the table. What are the data types allowed in a table? 1) Varchar2 () 2) Date 3) TIMESTAMP [(precision)] default precision is 6 we can mention from 0...9 4) Long 5) Integer 6) Number 7) Char 8) clob 9) Raw () 10) pls_integer 11)

binary_integer

12) Nvarchar2 () 13) bfile What are the advantages of VIEW? 1. Data security: complex view can not be modified i.e. base tables cannot be visible. 2. It provides easy way to query data from different data sources like a single table. 3. It is very useful when developing complex reports basing on multiple tables. Please find the advantages as given below:a) Data security - no need to give permission on the table, intact a view can be created, having only selected number of columns in its definition. So user will only be able to see those columns. b) Simplicity - a very complicated query can be saved as a View definition. When needed can be called by its view name. c) Removes dependency - Can be very helpful to remove the Dependency from the underlying tables. Suppose a view is created by joining several tables. After some

time, there are some changes on the tables, so only definition of view can be changed and there is no need to change all the code where view is used. d) No space - takes no space (except materialized view) How to perform a loop through all tables in pl/sql? We can use user_tables view of data dictionary. Create a cursor based on query Select * from user_tables and then use use this cursor in for loop How to find out second largest value in the table. SELECT * FROM PRODUCT A WHERE 2=(SELECT COUNT(DISTINCT B.ROWID) FROM PRODUCT B WHERE B.ROWID>=A.ROWID); PROD_I PROD_NAME -------- -------------MO123 MONITOR

PROD_PRICE -------------25000

DUPLICATE? SELECT COUNT (*) FROM REGIST_093_MANUAL WHERE ROWID IN (SELECT MAX (ROWID) FROM REGIST_093_MANUAL GROUP BY REGNNUMB, SUBJUNCD, SUBJCODE, REGISTER HAVING COUNT (*)>1) AND REGNNUMB='90202107037'; AGE CALCULATION IN PL/SQL: DECLARE SD DATE; ED DATE DEFAULT SYSDATE; Y NUMBER; M NUMBER; D NUMBER; BEGIN SD:='&SD'; Y:=trunc( months_between( ED, SD ) /12 ); M:=mod( trunc( months_between( ED, SD ) ), 12 ); D:=ED - add_months(SD,trunc( months_between( ED, SD ) )); DBMS_OUTPUT.PUT_LINE(Y || ' Years'||',' || M || ' Months' ||','|| D || ' Days'); END; OUTPUT : Enter value for sd: 11-AUG-1983 26 Years,1 Months,10.41363425925925925925925925925925925926 Days