Topic : SQL Related

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


Answer :

WRAP

Show Answer Discuss

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.

Show Answer Discuss

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.

Show Answer Discuss

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


Answer :

SHOW ERRORS

Show Answer Discuss

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.
Answer2
In 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 function
Declare @mydate datetime
Set @mydate = getdate()
?user function (where the user has already coded the function)
Declare @My_area
Set @My_area = dbo.fn_getMy_area(15;20)
Answer3
1.?where? is used to filter records returned by ?Select?
2.?where? appears before group by clause
3.In ?where? we cannot use aggregate functions like where count(*) >2 etc
4.?having? appears after group by clause
5.?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

Show Answer Discuss