Interview Q and A for Oracle SQL and PLSQL Part - 3

201. Explain implicit cursor attributes.

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.

Implicit Cursor:

1. %ISOPEN: The Oracle engine automatically opens and closed the SQL cursor after executing its associated select, insert, update or delete SQL statement has been processed in case of implicit cursor. Thus the SQL%ISOPEN attribute of an implicit cursor cannot be referenced outside of its SQL statement. As a result, SQL%ISOPEN always evaluates to FALSE.

2. %FOUND: Evaluates to TRUE, if an insert, update or delete affected one or more rows, or a single-row select returned one or more rows. Otherwise it evaluates to FALSE. The syntax for accessing this attribute is SQL%FOUND.

3. %NOTFOUND: It is logical opposite of %FOUND. It evaluates to TRUE, if an insert , update or delete affected no rows, or otherwise it evaluates to FALSE. The syntax for accessing this attribute is SQL%NOTFOUND.

4. %ROWCOUNT: Returns the numbers of rows affected by an insert , update or delete, or select into statement. The syntax for accessing this attribute is SQL%ROWCOUNT.

202. What is 1st normal form?

Each cell must be one and only one value, and that value must be atomic: there can be no repeating groups in a table that satisfies first normal form.

There are no repeating groups; all key attributes are defined; all attributes are dependent on the primary key.

203. What is 2nd normal form?

Every nonkey column must depend on the entire primary key.

It's in 1st normal form and it includes no partial dependencies ( where an attribute is dependent on only a part of a primary key ).

204. What is 3rd normal form?

(another explanation than #1) No nonkey column depends on another nonkey column.

All items are atomic, all tables have a primary key, every row is determined by its primary key, there are no duplicate rows, every column is dependent on ONLY the primary key.

205. What is 4th normal form?

Fourth normal form forbids independent one-to-many relationships between primary key columns and nonkey columns.

206.What is 5th normal form?

Fifth normal form breaks tables into the smallest possible pieces in order to eliminate all redundancy within a table. Tables normalized to this extent consist of little more than the primary key.

207. What are cascading triggers? What is the maximum no of cascading triggers at a time?

Executing one trigger may cause another trigger to also be executed, the triggers are said to be cascading. Max = 32.

208. What is a bind variable and why is it important?

A bind variable is a placeholder in a query. The way the Oracle shared pool (a very important shared memory data structure) operates is predicated on developers using bind variables.

209. What is a mutating table?

A mutating table is a table that is in the process of being modified by an UDPATE, DELETE or INSERT statement. For example, if your trigger contains a select statement or an update statement referencing the table it is triggering off of you will receive the error.

210. What's the difference between an equijoin and a self-join?

An equijoin does an equality test between two fields in two different tables; a self join does the same thing on a copy of the same table.

211. In a Select statement, what is the difference between a & and &&?

The & operator means that the PL SQL block requires user input for a variable. The && operator means that the value of this variable should be the same as inputted by the user previously for this same variable. Both pass in values at runtime, but if the && is used the user will not be bothered with a second prompt for the value.

212. What is the TRANSLATE function?

TRANSLATE is a simple function that does an Orderlycharacter-by-charactersubstitutionina string. The format is TRANSLATE(string,if,then).

Example:  select TRANSLATE (7671234,234567890,'BCDEFGHIJ') from DUAL; The result would be: GFG1BCD.I have found this useful during some data Migrations where special characters needed to be translated.

213. Describe a nested PL/SQL Block?

Declare Begin Begin End; Begin End; End;

214. What is an anonymous block?

A stored procedure without a name.

215. Is PL/SQL truly compiled when stored in the database or is it interpreted?

PL/SQL on the server is run in much the same fashion as Java is run anywhere. PL/SQL is compiled into PCode and the PCode is interpreted at runtime.

216.What does truncating a table do? What else may truncating a table do?

It deletes the data from the table. It can reset the high water mark for a table if the REUSE STORAGE clause is not used.

217. What does the TO_NUMBER function do?

It converts VARCHAR2 values to numbers.

218.What is the default length of the CHAR column?

1.

219. What is the purpose of the SQL*Plus command GET?

Get the contents of a previously saved operating system file into the buffer.

220. What character do you type to execute an anonymous block?

The / character.

221. What operator is used to assign a value to a variable that doesn't have a typical value?

:=

222. What keyword is used to assign a value to a variable that has a typical value?

DEFAULT

223.  How frequently are block declared variables initialized?

Every time a block is executed

224. With which symbol do you prefix a bind variable when you reference it in PL/SQL?

:

225. What are two statements that are true about the INTO clause?

a). You have to specify the same number of variables in the INTO clause as the values returned by the SELECT statement.

b). The data types of the variables specified in the INTO clause need to correspond with the values returned by the SELECT statement.

226. What keyword is used when you populate a host variable from the SQL prompt?

The VARIABLE keyword

227. How do you end each SQL statement in a PL/SQL block?

With a ;

228. Can you have more than one transaction in a PL/SQL block? 

Yes

229. The primary key of a PL/SQL table must be of what data type?

scalar

230. What is the term used for the rows produced by a query?

Active set

231. What does it mean when the cursor attribute SQL%FOUND returns the result TRUE?

The most recent SQL statement issued affects one or more rows.

232. What are two true statements concerning the index in a FOR loop?

a). You can't reference it outside the loop.

b). You can use an expression to reference its existing value within the loop.

233. Do PL/SQL records have a predefined data type?

 No.

234. How long does the Oracle server wait if it cannot acquire the locks on the rows it needs in a SELECT FOR UPDATE?

indefinitely

235. Name two things true about cursor FOR loops?

a). They process rows in an explicit cursor.

b). They automate processing as the cursor is automatically opened and the rows fetched for each iteration in the loop, and the cursor is closed when all the rows have been processed.

236. What exception occurs when the conversion of a character string to a number fails?

INVALID_NUMBER

237. Name three things that are true about explicit cursors?

a). They are manipulated through specific statements in the block's executable actions.

b). They individually process each row returned by a multi row SELECT statement.

c). They need to be declared and named before they can be used.

238. Name three things about using cursors with parameters?

a). You can use parameters to pass values to a cursor when it is open.

b). Parameters are used in a query when it executes.

c). In the OPEN statement, each formal parameter in the cursor declaration must have a corresponding real parameter.

239.Name three things true about trapping exceptions?

a).when an exception occurs, PL/SQL processes only one handler before leaving the block.

b).If you use the OTHERS clause, it should be placed last of all the exception-handling clauses.

C). Exceptions cannot appear in assignment statements or SQL Statements.

240. Describe two aspects about exceptions?

a). Once an Oracle error occurs, the associated exception is raised automatically.

b). You can raise an exception explicitly by issuing the RAISE statement within the block.

241. Name three things about user-defined exceptions.?

a).When defining your own exceptions, you need to declare them in the DECLARE section of a PL/SQL block.

b).They are raised explicitly with RAISE statements.

c).You need to reference your declared exception within the corresponding exception-handling routine.

242. What is a pseudo-column?

A pseudo column behaves like a table column, but is not actually stored in the table.  You can select from pseudo columns, but you cannot insert, update, or delete their values.  These are:  CURRVAL, NEXTVAL, LEVEL, ROWID, ROWNUM.

243. What are the more common pseudo-columns?

sequence.CurrVal, sequence.NextVal, RowID, RowNum, SysDate, UID, User

244. What is the difference between call and execute sqlplus commands.?

The CALL statement is SQL(and only understands SQL types). EXEC is really shorthand for begin/end;.

245. What is another name for ref cursors?

cursor variables

246. What data type column can not be used with INTERSECT?

LONG

247.What is SQLCODE?

A predefined symbol that contains the Oracle error status of the previously executed PL/SQL statement. If a SQL statement executes without errors, SQLCODE is equal to 0.

248. What is SQLERRM?

A PL/SQL symbol that contains the error message associated with SQLCODE. If a SQL statement executes successfully, SQLCODE is equal to 0 and SQLERRM contains the string ORA-0000: normal, successful completion

249. What is ROWNUM?

A pseudocolumn that indicates the order of the retrieved row. The ROWNUM for the first returned row is 1, ROWNUM can limit the number of rows that are returned by a query.

250. What are the benefits of using the PLS_INTEGER Datatype in PL/SQL?

If you have a whole-number counter, for example in a loop or record counter, consider using a datatype of PLS_INTEGER  instead of INTEGER or NUMBER.When declaring an integer variable, PLS_INTEGER is the most efficient numeric datatype because its values require less storage than INTEGER or NUMBER values, which are represented internally as 22-byte Oracle numbers. Also, PLS_INTEGER operations use machine arithmetic, so they are faster than BINARY_INTEGER, INTEGER, or NUMBER operations, which use library arithmetic.

251. Does ROLLUP work with multiple columns?

The ROLLUP feature can in fact be applied to multiple columns. The result is multiple levels of rollup, as illustrated here:

select deptno, job, count(*), grouping(deptno),grouping(job) from emp group by rollup(deptno, job);

DEPTNO JOB COUNT(*) GROUPING(DEPTNO) GROUPING(JOB) ---- ---- ---- ---- ---- 10 CLERK 1 0 0 10 MANAGER 1 0 0 10 PRESIDENT 1 0 0 10 3 0 1 20 ANALYST 2 0 0 20 CLERK 2 0 0 20 MANAGER 1 0 0 20 5 0 1 30 CLERK 1 0 0 30 MANAGER 1 0 0 30 SALESMAN 4 0 0 30 6 0 1 14 1 1 As shown in this example, we're able to count the employees by

1) department and job;

2) department; and

3) grand total.

252. What is an inline view?

A subquery in the from clause of your main query.

A subquery in a from clause used for defining an intermediate result set to query from.

An inline view is just a subquery in place of a table in an INSERT, UPDATE, DELETE, or SELECT.  If you could have issued a "create view as <subquery>" and then inserted/updated/deleted/selected from it, you can skip the create view and just inline it in the DML.

253. Give an example of an inline view and Top-N Query.

SELECT ename, job, sal, rownum FROM (SELECT ename, job, sal FROM emp ORDER BY sal) WHERE rownum <= 3;

SMITH CLERK 800 1 JAMES CLERK 950 2 ADAMS CLERK 1100 3

254. What SQL*Plus command is useful for determining whether the "N rows selected" message will appear?

Feedback

255. This phrase describes a query that feeds one row of results to a parent query for the purpose of selection when the exact where clause criteria is not known?

Single-row subquery.

256. What is a single-row subquery?

The main query expects the subquery to return only one value.

257. Does the order of stored procedures in a package matter?

It does if one procedure calls another; if that happens, the calling procedure must be the earlier of the two.

258. When having clause is used?

The having clause is used to restrict the result set returned by the GROUP BY clause. When you use GROUP BY with the HAVING clause, the GROUP BY clause divides the rows into sets of grouped rows and aggregates their values, and then the HAVING clause eliminates undesired  aggregated groups (filter the rows that a group by statement returns).

SQL> select * from students;

NAME       DATE_OF_B G STUDENT_ID EXAMS     GPA

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

Smith      04-JUL-65 F 2          2         5.8

Green      04-JUL-65 F 3          2         3.3

Taylor     01-JAN-77 F 4          1         0.8

Bane       01-JAN-55 M 5          4         4

SQL> select gender, count(*) from students group by gender;

You should see the following result.

G COUNT(*)

- ---------

F 3

M 1

Select gender from students group by gender having avg(gpa) > 3.9;

You should see the following result.

SQL> select gender from students group by gender having avg(gpa) > 3.9;

G

-

M

259. Is it mandatory to have a group by clause if , having exists ?

Yes.

260. Why are recursive relationships bad? How do you resolve them?

A recursive relationship defines when or where a table relates to itself.  It is considered as bad when it is a hard relationship (i.e. neither side is a "may" both are "must") as this can result in it not being possible to put in a top or perhaps a bottom of the table. For example in the EMPLOYEE table you could not put in the PRESIDENT of the company because he has no boss, or the junior janitor because he has no subordinates. These type of relationships are usually resolved by adding a small intersection entity.

261. What does a hard one-to-one relationship mean (one where the relationship on both ends is "must")?

This means the two entities should probably be made into one entity.

262. How should a many-to-many relationship be handled?

By adding an intersection entity table

263. What is an artificial (derived) primary key? When should an artificial (or derived) primary key be used?

A derived key comes from a sequence. Usually it is used when a concatenated key becomes too cumbersome to use as a foreign key.

264. When should you consider de-normalization?

Whenever performance analysis indicates it would be beneficial to do so without compromising data integrity.

265. How many maximum number of columns can be part of primary key in a table in 9i and 10g.

You can set primary key in a single table up to 16 columns of table in oracle 9i and 10g.

266. When you use WHERE clause and when you use HAVING clause?

HAVING clause is used when you want to specify a condition for a group function and it is written after GROUP BY clause The WHERE clause is used when you want to specify a condition for columns, single row functions except group functions and it is written before GROUP BY clause if it is used.

267. What is the difference between Views and Materialized Views in Oracle?

Views evaluate the data in the tables underlying the view definition at the time the view is queried. It is a logical view of your tables, with no data stored anywhere else. The upside of a view is that it will always return the latest data to you. The downside of a view is that its performance depends on how good a select statement the view is based on. If the select statement used by the view joins many tables, or uses joins based on non-indexed columns, the view could perform poorly.

Materialized views are similar to regular views, in that they are a logical view of your data (based on a select statement), however, the underlying query result set has been saved to a table. The upside of this is that when you query a materialized view, you are querying a table, which may also be indexed. Materialized views having several other advantages over simple view.

268. Differentiate Row level and statement level Trigger?

Row Level Trigger is fired each time row is affected by Insert, Update or Delete command. If statement doesn’t affect any row then no trigger action happens whereas Statement level trigger fires when a SQL statement affects the rows of the table. The trigger activates and performs its activity irrespective of number of rows affected due to SQL statement. They get fired once for each triggering statement.

269. Differentiate between co-related sub-query and nested query?

Co-related sub query is one in which inner query is evaluated only once and from that result your outer query is evaluated where as Nested query is one in which Inner query is evaluated for multiple times for getting one row of that outer query.

Example: Query used with IN() clause is Co-related query.

SELECT EMPLOYEE_NUMBER, LOAN_CODE, DOCUMENT_NUMBER, LOAN_AMOUNT

FROM PAY_LOAN_TRANS

WHERE EMPLOYEE_NUMBER IN (SELECT EMPLOYEE_NUMBER

FROM PAY_EMPLOYEE_PERSONAL_INFO

WHERE EMPLOYEE_NUMBER BETWEEN 1 AND 100);

Example: Query used with = operator is Nested query

SELECT * FROM PARTIAL_PAYMENT_SEQUENCE

WHERE SEQCOD = (SELECT MAX(SEQCOD) FROM PARTIAL_PAYMENT_SEQUENCE);

270. What is WITH CHECK OPTION?

The WITH CHECK option clause specifies check level to be done in DML statements. It is used to prevent changes to a view that would produce results that are not included in the sub query.

The concepts are different than previous concept in fact. In that case you can access the some of the concept in your mind to achieve the target.

271. Define Null in term of SQL?

A null is a value that is unassigned, unknown or inapplicable. A null is not the same as Zero. Arithmetic expression containing null value evaluate to null.

272. How many types of Queries Types in SQL?

Selection, Projection, Join table

273. What is Constraints?

Constraints are used to restrict or bound inefficient data. Basically two types  of constraints are provided : column constraints are associated with a single column

Where as table constraints are typically associated with more than one column.

Integrity Constraints1:

1.           Primary Key. [Unique + Not Null]

2.           Not Null.[Cannot be Null]

3.           Unique Key[Unique can be Null]

274. Difference Commit or Internal Commit?

As long as the user has not issued the commit statement, it is possible to undo all modifications since the last commit. To undo modifications, one has to issue the statement rollback; Note that any data definition command such as create table results in an internal commit. A commit is also implicitly executed when the user terminates an Oracle session.

275. Difference between Equijoin and Non-Equijoin?

Equijoin is possible when same column name appears in more than one table thus there is a need of primary key or foreign key where as in case of Non equijoin there is no need of same column in both the table.

Equijoin possible for more than 2 table where as limit for only 2 table.

276. What is Natural Join?

It based on all columns in two tables that have the same name or if the column having the same name but its data type is different then it returns an error.

277. What is Using Clause join?

If the column have the same name but data type is different incase “using clause” is implemented. It can match only for one column incase more than one column are same.

278. Difference Inner vs Outer Join?

The join of two tables that return only matched result is known as inner join

Where as outer join returns the inner matched result of inner join as well as unmatched return of left and right table.

279. Define SQL function? Differentiate between Single row Function vs Multiple Row Function?

Multiple functions can manipulate on group of rows to give one result per group of rows where as a single row function can operate on a single row only and return one result per row. Single row function is used to manipulate data item. They accept one or more data item and return one value for each row. The single row function can be nested. Nesting can be evaluated from deeper to higher. Types of Single Row functions

1. Character Functions

a) Case Manipulation Function

LOWER, UPPER, INITCAP

b) Character Manipulation Function

CONCAT,SUBSTR,LENGTH,INSTR,LPAD, RPAD,TRIM,REPLACE

2. Number Functions

ROUND, TRUNC, MOD

3. Date Functions

SYSDATE,MONTHS_BETWEEN,ADD_MONTHS,NEXT_DAY,LAST_DAY,ROUND,TRUNC

4. Conversion

a) Implicit Data type Conversion

VARCHAR OR CHAR TO NUMBER,VARCHAR OR CHAR TO DATE

b) Explicit Data type Conversion

TO_NUMBER,TO_CHAR,TO_DATE

5. General Functions

NVL,NVL2,NULLIF,COALESCE,DECODE,CASE

NVL FUNCTION

The NVL function is used to convert null values to actual values, data type must match incase of NVL function. The date, char and number data type can be used in case of NVL.

NVL2 FUNCTION

The NVL2 function examines the first expression, if the first expression is not null then the NV2 function returns the second expression. If the first expression is null then it return the third expression.

NULLIF FUNCTION

The NULLIF function compares the two expression if they are equal then return null otherwise it return the first expression.

COALESCE FUNCTION

The COALESCE FUNCTION takes n number of arguments. It returns the 1st expression if it is not null. It returns the 2ndexpression if the first expression is null and 2nd expression is not null. It returns the nth expression if all of the above expression is null and last expression is not null.

CASE FUNCTION

CASE Expression facilitates conditional inquiries by doing the work of an IF THEN ELSE statement. It compares the expression if the condition is matched return that expression. If none of then match the condition it return the else part. This function worked on ANSI standard. This feature introduce in oracle 9i.

DECODE FUNCTION

Decode function is the similar to CASE function only difference of syntax.

It also worked as IF then else statement.

280. 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);

Enter value for n: 2

SAL

---------

3700

281. Display the number value in Words

SQL> select sal, (to_char(to_date(sal,'j'), 'jsp')) from emp;

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.

282.Find Odd number of records?

select * from emp where (rowid,1) in (select rowid, mod(rownum,2) from emp);

283. Find Even number of records?

select * from emp where (rowid,0) in (select rowid, mod(rownum,2) from emp)

284. What are PL/SQL Cursor Exceptions?

Cursor_Already_Open, Invalid_Cursor

285. What is the maximum number of triggers, can apply to a single table?

There are 12 types of triggers, they are combination of :

Insert, Delete and Update Triggers.

Before and After Triggers.

Row and Statement Triggers.

(3*2*2=12)

286. How do you implement one-to-one, one-to-many and many-to-many relationships while designing tables?

One-to-One relationship can be implemented as a single table and rarely as two tables with primary and foreign key relationships.

One-to-Many relationships are implemented by splitting the data into two tables with primary key and foreign key relationships.

 Many-to-Many relationships are implemented using a junction table with the keys from both the tables forming the composite primary key of the junction table.

287. Define candidate key, alternate key and Composite 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. If the table has more than one candidate key, one of them will become the primary key, and the rest are called alternate keys. A key formed by combining at least two or more columns is called composite key.

288. Other way to replace query result null value with a text

SQL> Set NULL ‘N/A’

to reset SQL> Set NULL ‘’

289.What are object data types? Explain the use of object data types.
Object data types are user defined data types. Both column and row can represent an object type. Object types instance can be stored in the database. Object datatypes make it easier to work with complex data, such as images, audio, and video. Object types provide higher-level ways to organize and access data in the database.The SQL attributes of Select into clause, i.e. SQL % Not found, SQL % found, SQL % Isopen, SQL %Rowcount.
1.% Not found: True if no rows returned
E.g. If SQL%NOTFOUND then return some_value

2.% found: True if at least one or more rows returned
E.g. If SQL%FOUND then return some_value

3.%Isopen: True if the SQL cursor is open. Will always be false, because the database opens and closes the implicit cursor used to retrieve the data

4.%Rowcount: Number of rows returned. Equals 0 if no rows were found (but the exception is raised) and a 1, if one or more rows are found (if more than one an exception is raised).

290. Display  employee name, department id , salary and max salary of department  for all employees who earn less than the maximum salary in their department.

Select a.last_name ,a.salary,a.department_id,b.maxsal

From employees a , (select department_id, max(salary)maxsal

From employees group by department_id)b

Where a.department_id=b.department_id

And a.salary< b.maxsal;

291. Display  the top three earner names and salaries from the employees table

SELECT ROWNUM as RANK, last_name, salary

FROM (SELECT last_name,salary FROM employees

ORDER BY salary desc) WHERE ROWNUM <= 3;

292. Display  the less three earner names and salaries from the employees table

SELECT ROWNUM as RANK, last_name, salary

FROM (SELECT last_name,salary FROM employees

ORDER BY salary asc) WHERE ROWNUM <= 3;

293. Display the current & previous job details of all employees. Display each employee only once.

select employee_id,job_id from employees

union

select employee_id,job_id from job_history;

OR

select employee_id,job_id,department_id from employees

union

select employee_id,job_id,department_id from job_history;

294. Display the employees id and job id of employees who currently have a job title that they  held before beginning their tenure with the company

select employee_id,job_id from employees

intersect

select employee_id,job_id from job_history;

295. Display the employee id of those employees who have not changed  their jobs even once .

select employee_id,job_id from employees

minus

select employee_id,job_id from job_history;

296. produce an English sentence using union operators

column a_dummy noprint

select ‘sing’ as “My Dream”, 3 a_dummy

from dual

union

select ‘I’’d like to teach’, 1 from dual

union

select ‘ the world to’, 2

from dual order by 2 ;

297. Display a date  that is one year two months after the hire date for the employees working in the department with the department_id 20.

select hire_date, hire_date + to_yminterval(’01-02’) as hire_date_yminterval from employees where department_id=20;

298. Display the subtotal / superaggregate of salary

select department_id,job_id,sum(salary) from employees

group by rollup (department_id, job_id);

299. Display grand total salary by using group by

select null,null,sum(salary)

from employees group by();

300. Display  the details of the employees who are managed by the same manager and work in the same department as the employees with employee_id 178 or 174.

select employee_id,manager_id,department_id from employees

where (manager_id,department_id) in

( select manager_id, department_id from employees

where employee_id in(178,174))

and employee_id not in (178,174);