Oracle Interview Questions
and Answers : SQL
1.
To see current user name
Sql> show user;
Sql> show user;
2.
Change SQL prompt name
SQL> set sqlprompt “Manimara > “
Manimara >
Manimara >
SQL> set sqlprompt “Manimara > “
Manimara >
Manimara >
3.
Switch to DOS prompt
SQL> host
SQL> host
4.
How do I eliminate the duplicate rows ?
SQL> delete from table_name where rowid not in (select max(rowid) from table group by duplicate_values_field_name);
or
SQL> delete duplicate_values_field_name dv from table_name ta where rowid <(select min(rowid) from table_name tb where ta.dv=tb.dv);
Example.
Table Emp
Empno Ename
101 Scott
102 Jiyo
103 Millor
104 Jiyo
105 Smith
delete ename from emp a where rowid < ( select min(rowid) from emp b where a.ename = b.ename);
The output like,
Empno Ename
101 Scott
102 Millor
103 Jiyo
104 Smith
SQL> delete from table_name where rowid not in (select max(rowid) from table group by duplicate_values_field_name);
or
SQL> delete duplicate_values_field_name dv from table_name ta where rowid <(select min(rowid) from table_name tb where ta.dv=tb.dv);
Example.
Table Emp
Empno Ename
101 Scott
102 Jiyo
103 Millor
104 Jiyo
105 Smith
delete ename from emp a where rowid < ( select min(rowid) from emp b where a.ename = b.ename);
The output like,
Empno Ename
101 Scott
102 Millor
103 Jiyo
104 Smith
5.
How do I display row number with records?
To achive this use rownum pseudocolumn with query, like SQL> SQL> select rownum, ename from emp;
Output:
1 Scott
2 Millor
3 Jiyo
4 Smith
To achive this use rownum pseudocolumn with query, like SQL> SQL> select rownum, ename from emp;
Output:
1 Scott
2 Millor
3 Jiyo
4 Smith
6.
Display the records between two range
select rownum, empno, ename from emp where rowid in
(select rowid from emp where rownum <=&upto
minus
select rowid from emp where rownum<&Start);
Enter value for upto: 10
Enter value for Start: 7
select rownum, empno, ename from emp where rowid in
(select rowid from emp where rownum <=&upto
minus
select rowid from emp where rownum<&Start);
Enter value for upto: 10
Enter value for Start: 7
ROWNUM
EMPNO ENAME
--------- --------- ----------
1 7782 CLARK
2 7788 SCOTT
3 7839 KING
4 7844 TURNER
--------- --------- ----------
1 7782 CLARK
2 7788 SCOTT
3 7839 KING
4 7844 TURNER
7.
I know the nvl function only allows the same data type(ie. number or char or
date Nvl(comm, 0)), if commission is null then the text “Not Applicable”
want to display, instead of blank space. How do I write the query?
SQL> select
nvl(to_char(comm.),'NA') from emp;
Output :
NVL(TO_CHAR(COMM),'NA')
-----------------------
NA
300
500
NA
1400
NA
NA
-----------------------
NA
300
500
NA
1400
NA
NA
8.
Oracle cursor : Implicit & Explicit cursors
Oracle uses work areas called private SQL areas to create SQL statements.
PL/SQL construct to identify each and every work are used, is called as Cursor.
For SQL queries returning a single row, PL/SQL declares all implicit cursors.
For queries that returning more than one row, the cursor needs to be explicitly declared.
Oracle uses work areas called private SQL areas to create SQL statements.
PL/SQL construct to identify each and every work are used, is called as Cursor.
For SQL queries returning a single row, PL/SQL declares all implicit cursors.
For queries that returning more than one row, the cursor needs to be explicitly declared.
9.
Explicit Cursor attributes
There are four cursor attributes used in Oracle
cursor_name%Found, cursor_name%NOTFOUND, cursor_name%ROWCOUNT, cursor_name%ISOPEN
There are four cursor attributes used in Oracle
cursor_name%Found, cursor_name%NOTFOUND, cursor_name%ROWCOUNT, cursor_name%ISOPEN
10. Implicit
Cursor attributes
Same as explicit cursor but prefixed by the word SQL
Same as explicit cursor but prefixed by the word SQL
SQL%Found, SQL%NOTFOUND,
SQL%ROWCOUNT, SQL%ISOPEN
Tips : 1. Here SQL%ISOPEN is false,
because oracle automatically closed the implicit cursor after executing SQL
statements.
: 2. All are Boolean attributes.
: 2. All are Boolean attributes.
11. Find out nth
highest salary from emp table
SELECT DISTINCT (a.sal) FROM EMP A WHERE &N = (SELECT COUNT (DISTINCT (b.sal)) FROM EMP B WHERE a.sal<=b.sal);
SELECT DISTINCT (a.sal) FROM EMP A WHERE &N = (SELECT COUNT (DISTINCT (b.sal)) FROM EMP B WHERE a.sal<=b.sal);
Enter value for n: 2
SAL
---------
3700
SAL
---------
3700
12. To view
installed Oracle version information
SQL> select banner from v$version;
SQL> select banner from v$version;
13. Display the
number value in Words
SQL> select sal, (to_char(to_date(sal,'j'), 'jsp'))
from emp;
the output like,
SQL> select sal, (to_char(to_date(sal,'j'), 'jsp'))
from emp;
the output like,
SAL
(TO_CHAR(TO_DATE(SAL,'J'),'JSP'))
--------- -----------------------------------------------------
800 eight hundred
1600 one thousand six hundred
1250 one thousand two hundred fifty
If you want to add some text like,
Rs. Three Thousand only.
SQL> select sal "Salary ",
(' Rs. '|| (to_char(to_date(sal,'j'), 'Jsp'))|| ' only.'))
"Sal in Words" from emp
/
Salary Sal in Words
------- ------------------------------------------------------
800 Rs. Eight Hundred only.
1600 Rs. One Thousand Six Hundred only.
1250 Rs. One Thousand Two Hundred Fifty only.
--------- -----------------------------------------------------
800 eight hundred
1600 one thousand six hundred
1250 one thousand two hundred fifty
If you want to add some text like,
Rs. Three Thousand only.
SQL> select sal "Salary ",
(' Rs. '|| (to_char(to_date(sal,'j'), 'Jsp'))|| ' only.'))
"Sal in Words" from emp
/
Salary Sal in Words
------- ------------------------------------------------------
800 Rs. Eight Hundred only.
1600 Rs. One Thousand Six Hundred only.
1250 Rs. One Thousand Two Hundred Fifty only.
14. Display Odd/
Even number of records
Odd number of records:
select * from emp where (rowid,1) in (select rowid, mod(rownum,2) from emp);
1
3
5
Even number of records:
select * from emp where (rowid,0) in (select rowid, mod(rownum,2) from emp)
2
4
6
Odd number of records:
select * from emp where (rowid,1) in (select rowid, mod(rownum,2) from emp);
1
3
5
Even number of records:
select * from emp where (rowid,0) in (select rowid, mod(rownum,2) from emp)
2
4
6
15. Which date
function returns number value?
months_between
months_between
16. Any three
PL/SQL Exceptions?
Too_many_rows, No_Data_Found, Value_Error, Zero_Error, Others
Too_many_rows, No_Data_Found, Value_Error, Zero_Error, Others
17. What are
PL/SQL Cursor Exceptions?
Cursor_Already_Open, Invalid_Cursor
Cursor_Already_Open, Invalid_Cursor
18. Other way to
replace query result null value with a text
SQL> Set NULL ‘N/A’
to reset SQL> Set NULL ‘’
SQL> Set NULL ‘N/A’
to reset SQL> Set NULL ‘’
19. What
are the more common pseudo-columns?
SYSDATE, USER , UID, CURVAL, NEXTVAL, ROWID, ROWNUM
SYSDATE, USER , UID, CURVAL, NEXTVAL, ROWID, ROWNUM
20. What
is the output of SIGN function?
1 for positive value,
0 for Zero,
-1 for Negative value.
1 for positive value,
0 for Zero,
-1 for Negative value.
Which of the following statements is
true about implicit cursors?
Implicit cursors are used for SQL
statements that are not named.
Developers should use implicit
cursors with great care.
Implicit cursors are used in cursor
for loops to handle data processing.
Implicit cursors are no longer a
feature in Oracle.
Which of the following is not a
feature of a cursor FOR loop?
Record type declaration.
Opening and parsing of SQL
statements.
Fetches records from cursor.
Requires exit condition to be
defined.
A developer would like to use
referential datatype declaration on a variable. The variable name is
EMPLOYEE_LASTNAME, and the corresponding table and column is EMPLOYEE, and
LNAME, respectively. How would the developer define this variable using
referential datatypes?
Use employee.lname%type.
Use employee.lname%rowtype.
Look up datatype for EMPLOYEE column
on LASTNAME table and use that.
Declare it to be type LONG.
Which three of the following are
implicit cursor attributes?
%found
%too_many_rows
%notfound
%rowcount
%rowtype
If left out, which of the following
would cause an infinite loop to occur in a simple loop?
LOOP
END LOOP
IF-THEN
EXIT
Which line in the following
statement will produce an error?
cursor action_cursor is
select name, rate, action
into action_record
from action_table;
There are no errors in this
statement.
The command used to open a CURSOR
FOR loop is
open
fetch
parse
None, cursor for loops handle cursor
opening implicitly.
What happens when rows are found
using a FETCH statement
It causes the cursor to close
It causes the cursor to open
It loads the current row values into
variables
It creates the variables to hold the
current row values
Read the following code:
10.
CREATE OR REPLACE PROCEDURE find_cpt
11.
(v_movie_id {Argument Mode} NUMBER, v_cost_per_ticket
{argument mode} NUMBER)
12.
IS
13.
BEGIN
14.
IF v_cost_per_ticket > 8.5 THEN
15.
SELECT cost_per_ticket
16.
INTO
v_cost_per_ticket
17.
FROM
gross_receipt
18.
WHERE movie_id = v_movie_id;
19.
END IF;
20.
END;
Which mode should be used for
V_COST_PER_TICKET?
IN
OUT
RETURN
IN OUT
Read the following code:
22.
CREATE OR REPLACE TRIGGER update_show_gross
23.
{trigger information}
24.
BEGIN
25.
{additional code}
26.
END;
The trigger code should only execute
when the column, COST_PER_TICKET, is greater than $3. Which trigger
information will you add?
WHEN (new.cost_per_ticket > 3.75)
WHEN (:new.cost_per_ticket > 3.75
WHERE (new.cost_per_ticket >
3.75)
WHERE (:new.cost_per_ticket >
3.75)
What is the maximum number of
handlers processed before the PL/SQL block is exited when an exception occurs?
Only one
All that apply
All referenced
None
For which trigger timing can you
reference the NEW and OLD qualifiers?
Statement and Row
Statement only
Row only
Oracle Forms trigger
Read the following code:
30.
CREATE OR REPLACE FUNCTION get_budget(v_studio_id IN NUMBER)
RETURN number IS
v_yearly_budget NUMBER;
BEGIN
SELECT yearly_budget
INTO
v_yearly_budget
FROM studio
WHERE id = v_studio_id;
RETURN v_yearly_budget;
END;
Which set of statements will
successfully invoke this function within SQL*Plus?
VARIABLE g_yearly_budget NUMBER
EXECUTE g_yearly_budget := GET_BUDGET(11);
EXECUTE g_yearly_budget := GET_BUDGET(11);
VARIABLE g_yearly_budget NUMBER
EXECUTE :g_yearly_budget := GET_BUDGET(11);
EXECUTE :g_yearly_budget := GET_BUDGET(11);
VARIABLE :g_yearly_budget NUMBER
EXECUTE :g_yearly_budget := GET_BUDGET(11);
EXECUTE :g_yearly_budget := GET_BUDGET(11);
VARIABLE g_yearly_budget NUMBER
:g_yearly_budget := GET_BUDGET(11);
:g_yearly_budget := GET_BUDGET(11);
31.
CREATE OR REPLACE PROCEDURE update_theater
32.
(v_name IN VARCHAR v_theater_id IN NUMBER) IS
33.
BEGIN
34.
UPDATE
theater
35.
SET
name = v_name
36.
WHERE
id = v_theater_id;
37.
END update_theater;
When invoking this procedure, you
encounter the error:
ORA-000: Unique
constraint(SCOTT.THEATER_NAME_UK) violated.
How should you modify the function
to handle this error?
An user defined exception must be
declared and associated with the error code and handled in the EXCEPTION
section.
Handle the error in EXCEPTION
section by referencing the error code directly.
Handle the error in the EXCEPTION
section by referencing the UNIQUE_ERROR predefined exception.
Check for success by checking the
value of SQL%FOUND immediately after the UPDATE statement.
Read the following code:
40.
CREATE OR REPLACE PROCEDURE calculate_budget IS
41.
v_budget
studio.yearly_budget%TYPE;
42.
BEGIN
43.
v_budget :=
get_budget(11);
44.
IF v_budget <
30000
45.
THEN
46.
set_budget(11,30000000);
47.
END IF;
48.
END;
You are about to add an argument to
CALCULATE_BUDGET. What effect will this have?
The GET_BUDGET function will be
marked invalid and must be recompiled before the next execution.
The SET_BUDGET function will be
marked invalid and must be recompiled before the next execution.
Only the CALCULATE_BUDGET procedure
needs to be recompiled.
All three procedures are marked
invalid and must be recompiled.
Which procedure can be used to
create a customized error message?
RAISE_ERROR
SQLERRM
RAISE_APPLICATION_ERROR
RAISE_SERVER_ERROR
The CHECK_THEATER trigger of the
THEATER table has been disabled. Which command can you issue to enable this
trigger?
ALTER TRIGGER check_theater ENABLE;
ENABLE TRIGGER check_theater;
ALTER TABLE check_theater ENABLE
check_theater;
ENABLE check_theater;
Examine this database trigger
52.
CREATE OR REPLACE TRIGGER prevent_gross_modification
53.
{additional trigger information}
54.
BEGIN
55.
IF
TO_CHAR(sysdate, DY) = MON
56.
THEN
57.
RAISE_APPLICATION_ERROR(-20000,Gross receipts cannot be deleted on Monday);
58.
END IF;
59.
END;
This trigger must fire before each DELETE
of the GROSS_RECEIPT table. It should fire only once for the entire DELETE
statement. What additional information must you add?
BEFORE DELETE ON gross_receipt
AFTER DELETE ON gross_receipt
BEFORE (gross_receipt DELETE)
FOR EACH ROW DELETED FROM gross_receipt
Examine this function:
61.
CREATE OR REPLACE FUNCTION set_budget
62.
(v_studio_id IN NUMBER, v_new_budget IN NUMBER) IS
63.
BEGIN
64.
UPDATE
studio
65.
SET
yearly_budget = v_new_budget
WHERE id = v_studio_id;
IF SQL%FOUND THEN
RETURN TRUEl;
ELSE
RETURN FALSE;
END IF;
COMMIT;
END;
Which code must be added to
successfully compile this function?
Add RETURN right before the IS keyword.
Add RETURN number right before the
IS keyword.
Add RETURN boolean right after the
IS keyword.
Add RETURN boolean right before the
IS keyword.
Under which circumstance must you
recompile the package body after recompiling the package specification?
Altering the argument list of one of
the package constructs
Any change made to one of the
package constructs
Any SQL statement change made to one
of the package constructs
Removing a local variable from the
DECLARE section of one of the package constructs
Procedure and Functions are
explicitly executed. This is different from a database trigger. When is a
database trigger executed?
When the transaction is committed
During the data manipulation
statement
When an Oracle supplied package
references the trigger
During a data manipulation statement
and when the transaction is committed
Which Oracle supplied package can
you use to output values and messages from database triggers, stored procedures
and functions within SQL*Plus?
DBMS_DISPLAY
DBMS_OUTPUT
DBMS_LIST
DBMS_DESCRIBE
What occurs if a procedure or
function terminates with failure without being handled?
Any DML statements issued by the
construct are still pending and can be committed or rolled back.
Any DML statements issued by the
construct are committed
Unless a GOTO statement is used to
continue processing within the BEGIN section, the construct terminates.
The construct rolls back any DML
statements issued and returns the unhandled exception to the calling
environment.
Examine this code
71.
BEGIN
72.
theater_pck.v_total_seats_sold_overall
:= theater_pck.get_total_for_year;
73.
END;
For this code to be successful, what
must be true?
Both the V_TOTAL_SEATS_SOLD_OVERALL
variable and the GET_TOTAL_FOR_YEAR function must exist only in the body of the
THEATER_PCK package.
Only the GET_TOTAL_FOR_YEAR variable
must exist in the specification of the THEATER_PCK package.
Only the V_TOTAL_SEATS_SOLD_OVERALL
variable must exist in the specification of the THEATER_PCK package.
Both the V_TOTAL_SEATS_SOLD_OVERALL
variable and the GET_TOTAL_FOR_YEAR function must exist in the specification of
the THEATER_PCK package.
A stored function must return a
value based on conditions that are determined at runtime. Therefore, the SELECT
statement cannot be hard-coded and must be created dynamically when the
function is executed. Which Oracle supplied package will enable this feature?
DBMS_DDL
DBMS_DML
DBMS_SYN
1. What is a Cartesian product? What
causes it?
Expected answer:
A Cartesian product is the result of an unrestricted join of two or more tables. The result set of a three table Cartesian product will have x * y * z number of rows where x, y, z correspond to the number of rows in each table involved in the join. It is causes by specifying a table in the FROM clause without joining it to another table.
A Cartesian product is the result of an unrestricted join of two or more tables. The result set of a three table Cartesian product will have x * y * z number of rows where x, y, z correspond to the number of rows in each table involved in the join. It is causes by specifying a table in the FROM clause without joining it to another table.
2. What is an advantage to using a
stored procedure as opposed to passing an SQL query from an application.
Expected answer:
A stored procedure is pre-loaded in memory for faster execution. It allows the DBMS control of permissions for security purposes. It also eliminates the need to recompile components when minor changes occur to the database.
A stored procedure is pre-loaded in memory for faster execution. It allows the DBMS control of permissions for security purposes. It also eliminates the need to recompile components when minor changes occur to the database.
3. What is the difference of a LEFT
JOIN and an INNER JOIN statement?
Expected answer:
A LEFT JOIN will take ALL values from the first declared table and matching values from the second declared table based on the column the join has been declared on. An INNER JOIN will take only matching values from both tables
A LEFT JOIN will take ALL values from the first declared table and matching values from the second declared table based on the column the join has been declared on. An INNER JOIN will take only matching values from both tables
4. When a query is sent to the
database and an index is not being used, what type of execution is taking
place?
Expected answer:
A table scan.
A table scan.
5. What are the pros and cons of
using triggers?
Expected answer:
A trigger is one or more statements of SQL that are being executed in event of data modification in a table to which the trigger belongs.
A trigger is one or more statements of SQL that are being executed in event of data modification in a table to which the trigger belongs.
Triggers enhance the security,
efficiency, and standardization of databases.
Triggers can be beneficial when used:
– to check or modify values before they are actually updated or inserted in the database. This is useful if you need to transform data from the way the user sees it to some internal database format.
– to run other non-database operations coded in user-defined functions
– to update data in other tables. This is useful for maintaining relationships between data or in keeping audit trail information.
– to check against other data in the table or in other tables. This is useful to ensure data integrity when referential integrity constraints aren’t appropriate, or when table check constraints limit checking to the current table only.
Triggers can be beneficial when used:
– to check or modify values before they are actually updated or inserted in the database. This is useful if you need to transform data from the way the user sees it to some internal database format.
– to run other non-database operations coded in user-defined functions
– to update data in other tables. This is useful for maintaining relationships between data or in keeping audit trail information.
– to check against other data in the table or in other tables. This is useful to ensure data integrity when referential integrity constraints aren’t appropriate, or when table check constraints limit checking to the current table only.
No comments:
Post a Comment