Skip to main content

Interview point of View - Technical - PLSQL

1. Write an SQL statement to delete all duplicate rows in a table.
   Give all possible SQL statements that can be used to delete duplicate rows effectively.

2. Consider the following table

ID Name Role ManagerId
MGR6002 RajeshKumar Manager MGR1001
TM1001 Sridevi Associate MGR6002  
TM1002 John Sr.Associate MGR6002
TM1008 Rexan Associate MGR6002  
MGR6003 Amruta Manager MGR1001
MGR6004 Justin Manager MGR1001  
MGR1001 Ravikumar GPM VCH1001
TM1003 Linton Associate MGR6004  
TM1003 Kavitha Sr.Associate MGR6004
TM1005 Sailu Analyst MGR6002  
TM1006 Sami Sr.Associate MGR6001
 
    Write an SQL to print employee list hierarchically
    Ex output.
Rajeshkumar
John
Rexan
Sailu
Amruta
Justin
Linton
Kavitha

3. Write the SQL statement to obtain nth salary of employees.
4. What are triggers? Breifly explain trigger types.
5. Do you know about RBO, CBO? What is your understanding on them?
6. Explain FILE handling in PlSQL. Tell me the commonly used methods for file operations.
   > You may be asked to write a program to compare two files using file handling.
7. What are indexes? Explain its types.
8. What is your understanding on Oracle memory structure?
9. What are cursors? Explain its types with examples.
10. Differentiate Views, Inline views , materailized views.
11. Differentiate procedure, function, package.
12. What is the output of the following?

a.create table tabletmp(msg varchar(100));
b.insert into tabletmp values('Message 1');
c.insert into tabletmp values('Message 1');
d.insert into tabletmp values('Message 1');
e.insert into tabletmp values('Message 1');
f.insert into tabletmp values('Message 1');
g.create table tabletmp(msg varchar(100));
h.rollback;
i.Select * from tabletmp;

The execution sequence is : a,b,c,d,e,f,g,h,i
What is the output at 'g'?
What is the output at 'i'?

13. What is the maximum number of columns allowed in a table?
14. Each record has a unique key in oracle, even if the table structure has no primary.What is that?
15. Distinguish primary key, indexes.





Comments

Post a Comment

Popular posts from this blog

Design and Operations ( Employee- Model )

-- DB design drop table emp_Salary; drop table emp_Designation; drop table emp; drop table Designations; drop table Departments; drop table Salary_Bands; drop table Addresses; Create table emp( emp_id number, emp_Name varchar2(20), emp_address_id varchar2(10), emp_created_date date, emp_created_by varchar2(20), constraint e_pk primary key(emp_id) ); Create table Departments( dept_id number, dept_name Varchar2(20), constraints dept_pk primary key(dept_id) ); Create table Designations( dept_id number references Departments(dept_id), desg_id number, desg_description varchar2(20), constraints desg_pk primary key(dept_id,desg_id) ); Create table emp_Designation( emp_id number references emp(emp_id), emp_des_dept_id number references departments(dept_...
Post a Comment
Read more

Minimum / Maximum from the given list

File: Execute.java -------------------------------------------------------------------------------------- package org.developersbrain.Solutions; public class Execute { public static int min(int[] arrList){ int minV1=0; int minV2=0; int aLen=arrList.length; minV1=arrList[0]; minV2=arrList[aLen-1]; for(int i=0,j=aLen-1;i<=aLen/2;i++,j--){ if(minV1 > arrList[i]){ minV1=arrList[i]; } if(minV2 > arrList[j]){ minV2=arrList[j]; } } if(minV2<=minV1){ return minV2; }else{ return minV1; } } public static int max(int[] arrList){ int minV1=0; int minV2=0; int aLen=arrList.length; minV1=arrList[0]; minV2=arrList[aLen-1]; for(int i=0,j=aLen-1;i<=aLen/2;i++,j--){ if(minV1 < arrList[i]){ minV1=arrList[i]; } if(minV2 < arrList[j]){ minV2=arrList[j]; } } if(minV2>=minV1){ return minV2; }else{ return minV1; } } ...
Post a Comment
Read more

SQL - Binary Search Tree

Problem Statement You are given a table,   BST , containing two columns:   N  and   P,  where   N   represents the value of a node in   BST , and   P   is the parent of   N . Write a query to find the node type of   BST   ordered by the value of the node. Output one of the following for each node: ·          Root : If node is root node. ·          Leaf : If node is leaf node. ·          Inner : If node is neither root nor leaf node. Sample Input                 N          P 1 2 3 2 6 8 9 8 2 5 8 5 5         null Sample Output 1 Leaf 2 Inner 3 Leaf 5 Root 6 Leaf 8 Inner 9 Leaf Explanation The   BST ...
Post a Comment
Read more