DBA Interview Question

SQL Related

Topic : SQL Related

What command would you use to encrypt a PL/SQL application?

Explain the difference between a FUNCTION; PROCEDURE and PACKAGE.

Answer :

A function and procedure are the same in that they are intended to be a collection of PL/SQL code that carries a single task. While a procedure does not have to return any values to the calling application; a function will return a single value. A package on the other hand is a collection of functions and procedures that are grouped together based on their commonality to a business function or application.

Explain the use of table functions.

Answer :

Table functions are designed to return a set of rows through PL/SQL logic but are intended to be used as a normal table or view in a SQL statement. They are also used to pipeline information in an ETL process.

You have just compiled a PL/SQL package but got errors; how would you view the errors?

Diffrence between a ?where? clause and a ?having? claus

Answer :

The order of the clauses in a query syntax using a GROUP BY clause is as follows:select ?where..group by?having?order by?Where filters; group by arranges into groups; having applies based on group by clause. Having is applied with group by clause.Answer2In SQL Server; procedures and functions can return values. (In Oracle; procedures cannot directly return a value).The major difference with a function is that it can be used in a value assignment. Such as:?system functionDeclare @mydate datetimeSet @mydate = getdate()?user function (where the user has already coded the function)Declare @My_areaSet @My_area = dbo.fn_getMy_area(15;20)Answer31.?where? is used to filter records returned by ?Select?2.?where? appears before group by clause3.In ?where? we cannot use aggregate functions like where count(*) >2 etc4.?having? appears after group by clause5.?having? is used to filter records returned by ?Group by?<6.In?Having? we can use aggregate functions like where count(*) >2 etc there are two more