Oracle SQL-PLSQL

Oracle SQL/PLSQL Interview Questions with Answers

 

 What are joins and Types of join?

Ans. We need retrive data from two or more tables to make our result complete. We need to perform a join.

INNER JOIN
This join returns rows when there is at least one match in both the tables.

OUTER JOIN
There are three different Outer Join methods.
LEFT OUTER JOIN
This join returns all the rows from the left table with the matching rows from the right table. If there are no field matching in the right table then it returns NULL values
RIGHT OUTER JOIN
Right outer join returns all the rows from the right table with the matching rows from the left table. If there are no field matching in the left table then it returns NULL values
FULL OUTER JOIN
Full outer join merge left outer join and right outer join.  this returns row from either table when the conditions are met and returns null value when there is no match
CROSS JOIN
Corss join is  does not necessary any condition to join. The output result contains records that are multiplication of record  from both the tables.
What is DIFFERENCE BETWEEN LEFT, RIGHT OUTER JOIN?
Ans:If there r any values in one table that do not have corresponding values in the other,in an equi join that row will not be selected.Such rows can be forcefully selected by using outer join symbol(+) on either of the sides(left or right)  based on the requirement.
WHAT ARE SET OPERATORS?
Ans: UNION, INTERSECT or MINUS is called SET OPERATORS.
What are different datatypes supported by sql in oracle?
Ans: Char (size), Nchar (size), Varchar2 (size), Nvarchar2 (size) data types for character values, Number (precision, scale), Number, Number (n), Float, Float (binary precision) data types for numerical values, Date data type for date values, Long, Raw (size),  Long Raw,  Clob, Blob, Nclob, Bfile for large objects.
What is difference between long and lob datatypes?
Ans:LOB
1) The maximum size is 4GB. 2) LOBs (except NCLOB) can be attributes of an object type. 3) LOBs support random access to data. 4) Multiple LOB columns per table or LOB attributes in an object type.
LONG
1) The maximum size is 2GB.  2) LONGs cannot.    3) LONGs support only sequential access. 4) Only one LONG column was allowed in a table
How much memory is allocated for date datatype? What is default date format in oracle?
Ans: For Date data type oracle allocates 7 bytes Memory.   Default Date Format is: DD-MON-YY.
What is range for each datatype of sql?
Ans: Datatype Range Char  Varchar2  Number    Float     LONG, RAW, LONGRAW  Large Objects (LOB’s) 2000 bytes  4000 bytes  Precision
1 to 38 Scale -84 to 127  Precision 38 decimals Or 122 binary precision   2 GB  4GB
What is a constraint? What are its various levels?
Ans: Constraint: Constraints are representators of the column to enforce data entity and consistency.There r two levels
1)Column-level constraints 2)Table-level constraints.
List out all the constraints supported by oracle
Primary Key , Foreign Key or Referential Integrity, Not Null, Unique, Check.

 

Below are three tables (

product				sale				cutomer
product_id	Product name	sale_price		product_id	cust_id			cutomer_id	Name
a001	mobiles	6000		a001	11			11	imran
a002	camera	1000		a002	22			22	Ayesha
a003	batrries	800		a003	22			33	Umar
a004	toys	550		a002	44			44	Ali
a005		3000		a002	11

 

Select 3 product which having highest  sale price

 SELECT * FROM ( SELECT * FROM product ORDER BY sales_price DESC) WHERE rownum <= 3

 

Delete the records from product which having null description

delete from product where product_name is null

 

Display detail of product which having maximum sale 

 SELECT * FROM
( SELECT product_id, count(product_id)as cnt FROM sales group by product_id order by cnt desc) WHERE rownum = 1  

Select customer details and produtct details of cutomer imran

Select p.product_name, c.cutomer_name, p.sale_price from product p, cutomer c, sale s

where p.product_id=s.product_id and c.cutomer_id=s.ccustomer_id and customer_name=’imran’

 

Difference between DELETE & TRUNCATE statement
Ans. Delete is a DML command. Truncate is a DDL command.
In Delete statement we can use where clause But we can’t use where clause in truncate statement.
Delete activates trigger. Truncate does not activate trigger.
We can rollback delete command. We can not rollback truncate command. Delete does not reset identity of table. Truncate resets identity of table.

 Difference between Primary key and Unique Key

Ans. Primary key and Unique key enforce uniqueness of the column on which they are defined. But by default, the primary key creates a clustered index on the column, where as unique key creates a non-clustered index by default. Another major difference is that primary key does not allow NULL value, but unique key allows one NULL value only.

 

What are  oracle number, character, date, conversion, other
functions.

Ans.
Oracle Number Functions –
Round (m, [n]),
Trunc (m, [n]),
Power (m, n),
Sqrt,
Abs (m),
Ceil (m),
Floor (m),
Mod (m, n)
 
Oracle Character Functions-
Chr (x)
Concert (string1, string2)
Lower (string)
Upper (string)
Substr (string, from_str, to_str)
ASCII (string)
Length (string)
Initcap (string).
 
Oracle Date Functions-
sysdate
Months between (d1, d2)
To_char (d, format)
Last day (d)
Next_day (d, day).

Oracle Conversion Functions-
To_char
To_date
To_number
What is syntax of PL/SQL BLOCK
Ans. DECLARE

 BEGIN
   
 EXCEPTION
   
 END;

 

What are different types of oracle PL/SQL BLOCKS?

Ans:

Oracle PL/SQL DECLARE BLOCK – In DECLARE BLOCK all the declarations of the variable used in the program is made. If no variables are used this block will become optional.
Oracle PL/SQL BEGIN BLOCK -  In BEGIN BLOCK all the executable statements are placed. This block is Mandatory.
Oracle EXCEPTION BLOCK – In EXCEPTION BLOCK all the exceptions are handled. this block is optional.

 what is a Oracle PL/SQL cursor? and how to create cursor syntax?
Ans: Cursor is Private SQL area in PL/SQL.
     Declare the Cursor,
     Open the Cursor,
     Fetch values from SQL into the local Variables,
     Close the Cursor.
Type of cursors are supported by oracle pl/sql?
Ans.  There are two types of cursors namely Implicit Cursor, Explicit Cursor.
What is a cursor for loop?
Ans: Cursor For Loop is shortcut process for Explicit Cursors because the Cursor is Open, Rows are fetched once for each iteration and the cursor is closed automatically when all the rows have been processed.
What are cursor attributes?
Ans:  %Found, %NotFound,   %IsOpen, %RowCount are the cursor attributes.
 Use of cursor with “for update of” clause?
Ans: This Clause stop accessing of other users on the particular columns used by the cursor until the COMMIT is issued.
How Exception is different from error?
Ans: Whenever an error occurs Exception raises. Error is a bug whereas the Exception is a warning or error condition.