SQL PL/SQL Question 1
How to display row number with records?Select rownum, ename from emp;
SQL PL/SQL Question 2
How to view version information in Oracle?Select banner from v$version;
SQL PL/SQL Question 3
How to find the second highest salary in emp table?select min(sal) from emp a
where 1 = (select count(*) from emp b where a.sal <>
SQL PL/SQL Question 4
How to delete the duplicate rows from a table?create table t1 ( col1 int, col2 int, col3 char(1) );
insert into t1 values(1,50, ‘a’);
insert into t1 values(1,50, ‘b’);
insert into t1 values(1,89, ‘x’);
insert into t1 values(1,89, ‘y’);
insert into t1 values(1,89, ‘z’);
select * from t1;
delete from T1
where rowid <> ( select max(rowid)
from t1 b
where b.col1 = t1.col1
and b.col2 = t1.col2 ) 3 rows deleted.
select * from t1;
SQL PL/SQL Question 5
How to select a row using indexes?You have to specify the indexed columns in the WHERE clause of query.
SQL PL/SQL Question 6
How to select the first 5 characters of FIRSTNAME column of EMP table?select substr(firstname,1,5) from emp
SQL PL/SQL Question 7
How to concatenate the firstname and lastname from emp table?select firstname ‘ ‘ lastname from emp
SQL PL/SQL Question 8
What's the difference between a primary key and a unique key?Primary key does not allow nulls, Unique key allow nulls.
SQL PL/SQL Question 9
What is a self join?A self join joins a table to itself.
ExampleSELECT a.last_name Employee, b.last_name Manager
FROM employees a, employees b
WHERE b.employee_id = a.manager_id;
SQL PL/SQL Question 10
What is a transaction and ACID?Transaction - A transaction is a logical unit of work. It must be commited or rolled back.
ACID - Atomicity, Consistency, Isolation and Duralbility, these are properties of a transaction.
SQL PL/SQL Question 11
How to add a column to a table?alter table t1 add sal number;
alter table t1 add middle_name varchar(20);
SQL PL/SQL Question 12Is it possible for a table to have more than one foreign key ?
A table can have any number of foreign keys. It can have only one primary key .
SQl PL/SQL Question 13
How to display number value in words?SQL> select sal, (to_char(to_date(sal,'j'), 'jsp')) from emp;
SQL PL/SQL Question 14
What is candidate key, alternate key, composite key.Candidate Key A candidate key is one that can identify each row of a table uniquely. Generally a candidate key becomes the primary key of the table.
Alternate KeyIf the table has more than one candidate key, one of them will become the primary key, and the rest are called alternate keys.
Composite Key: - A key formed by combining at least two or more columns is called composite key.
SQL PL/SQL Question 15
What's the difference between DELETE TABLE and TRUNCATE TABLE commands? Explain drop command.Both Delete and Truncate will leave the structure of the table. Drop will remove the structure also.
ExampleIf tablename is T1.
To remove all the rows from a table t1.
Truncate table t1
Drop table t1.
- Truncate is fast as compared to Delete. DELETE will generate undo information, in case of rollback, but TRUNCATE will not.
Table scan and index fast scan read data blocks up to high water mark
and truncate resets high water mark but delete does not.So full table
scan after Delete will not improve but after truncate it will be fast.
is DML. Because truncate is a DDL, it performs implicit commit. You
cannot rollback a truncate. Any uncommitted DML changes will also be
committed with the TRUNCATE.
- You cannot specify a WHERE clause in the TRUNCATE statement, but you can specify that in Delete.
you truncate a table the storage for the table and all the indexes can
be reset back to its initial size,but a Delete will never shrink the
size of the a table or its indexes.
Dropping a table removes the data and definition of the table. The indexes, constraints, triggers, and privileges on the table are also dropped. The action of dropping a table cannot be undone. The views, materialized views or other stored programs that reference the table are not dropped but they are marked as invalid.
SQL PL/SQL Question 16
Explain the difference between a FUNCTION, PROCEDURE and PACKAGE.Procedures and functions are stored in compiled form in database.
Functions take zero or more parameters and return a value. Procedures take zero or more parameters and return no values.
Both functions and procedures can take or return zero or more values through their parameter lists.
Another difference between procedures and functions, other than the return value, is how they are called. Procedures are called as stand-alone executable statements:
Functions can be called anywhere in an valid expression :
1) IF (tell_salary(empno) < 500 ) THEN … 2) var1 := tell_salary(empno); 3) DECLARE var1 NUMBER DEFAULT tell_salary(empno); BEGIN …
Packages contain function , procedures and other data structures.
There are a number of differences between packaged and non-packaged PL/SQL programs. Package The data in package is persistent for the duration of the user’s session.The data in package thus exists across commits in the session.
If you grant execute privilege on a package, it is for all functions and procedures and data structures in the package specification. You cannot grant privileges on only one procedure or function within a package. You can overload procedures and functions within a package, declaring multiple programs with the same name. The correct program to be called is decided at runtime, based on the number or datatypes of the parameters.
SQL PL/SQL Question 17
Describe the use of %ROWTYPE and %TYPE in PL/SQL%ROWTYPE associates a variable to an entire table row.
The %TYPE associates a variable with a single column type.
SQ PL/SQL Question 18
What are SQLCODE and SQLERRM and why are they important for PL/SQL developers?SQLCODE returns the current database error number. These error numbers are all negative, except NO_DATA_FOUND, which returns +100.
SQLERRM returns the textual error message.. These are used in exception handling.
SQL PL/SQL Question 19
How can you find within a PL/SQL block, if a cursor is open?By the Use of %ISOPEN cursor variable.
SQL PL/SQL Question 20
How do you debug output from PL/SQL?By the use the DBMS_OUTPUT package.
By the use of SHOW ERROR command, but this only shows errors.
The package UTL_FILE can also be used.
SQL PL/SQL Question 21
What are the types of triggers?
SQL PL/SQL Question 22
Explain the usage of WHERE CURRENT OF clause in cursors ?It refers to the latest row fetched from a cursor in an update and delete statement.
SQL PL/SQL Question 23
Name the tables where characteristics of Package, procedure and functions are stored ?User_objects, User_Source and User_error.
SQL PL/SQL Question 24
What are two parts of package ?They consist of package specification, which contains the function headers, procedure headers, and externally visible data structures. The package also contains a package body, which contains the declaration, executable, and exception handling sections of all the bundled procedures and functions.
SQL PL/SQL Question 25
What are two virtual tables available during database trigger execution ?The table columns are referred as OLD.column_name and NEW.column_name.
For INSERT only TRIGGERS NEW.column_name values ARE only available.
For UPDATE only TRIGERS OLD.column_name NEW.column_name values ARE only available.
For DELETE only TRIGGERS OLD.column_name values ARE only available.v
SQL PL/SQL Question 26
What is Overloading of procedures ?REPEATING OF SAME PROCEDURE NAME WITH DIFERENT PARAMETER LIST.
SQL PL/SQL Question 27
What are the return values of functions SQLCODE and SQLERRM ?SQLCODE returns the latest code of the error that has occurred.
SQLERRM returns the relevant error message of the SQLCODE.