Tricky question an Interviewer can ask you in PLSQL

Question:

I am writing a block of code. I am inserting the statement 'Select * from employee' in PLSQL block.
Is this possible? If yes, how can you do it? If no, What is the error thrown during compilation?

Answer:

Answer is both YES and NO. Dont get confused.

If you are to say 'YES', go with the below answer.
Yes. It can be done using DYNAMIC SQL concept.
You can either use DBMS_SQL or EXECUTE IMMEDIATE

set serveroutput on
begin
execute immediate 'Select * from dual';
end;
/

If you are to say 'NO', justify as below.
No. Because a Select statement should have INTO clause with statement structure.

set serveroutput on
begin
Select * from dual;
end;
/

Error report:
ORA-06550: line 2, column 1:
PLS-00428: an INTO clause is expected in this SELECT statement
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:

Comments

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_...

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; } } ...

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 ...

SQL ,PLSQL , Unix

Search This Blog