Skip to main content

Frequent Interview Questions on SQL and PL/SQL #DOTC_Mdb

                    Frequent Interview Questions on SQL and PL/SQL


Today we will discuss Frequent interview questions and answers of SQL and PL/SQL which is mostly asked in interview if you are looking for job in IT industry as Database Developer.

This Blog is important for freshers as well as experience candidates who is preparing for interview in IT company as Database SQL and PL/SQL developer.

Let's start with Interview Questions and Answers . 

Question 1: What is SQL and Classification of SQL?

Answer

SQL is a Structure Query Language which is vastly used in RDBMS database like Oracle, Sybase, DB2 , Microsoft SQL server etc.

 Classification of SQL is as below:

DDL (Data Definition Language):  Commands are  create , alter , drop , truncate etc

DML (Data Manipulation Language) : Commands are  insert,update and delete .

TCL (Transaction Control Language ) : Commands are  Commit , Rollback and Save point.

DCL (Data Control Language) : Commands are Grant , Revoke



Question 2:    What is meant by Joins? What are the types of join?

Answer

Joins are basically used to extract/get data from multiple tables using some common columns or conditions and also by using alias to fulfill the condition.

 There are various types of Joins as listed below:

1 . Self Join.

2 . Equi Join.

3. Cross Join or CARTESIAN PRODUCT.

4. Left Outer Join.

5. Right Outer Join.

6. Full outer Join.

 

Question 3 : What is the Reason behind using inner Join and outer Join?

 Answer

Inner Join we used to get the common records from two or more tables while through outer join we can get all the records from 1 table and can get common records from other table .

 Let’s understand with 1 example suppose we have 2 table employee and department and common record between the 2 tables is 10 so inner join will give you only common record that is 10 , but outer join will give you all the records from left or right table and common records from 1 table.

 So through outer join you can see multiple record with common record as well which is also known as left join and right join . Inner join basically used to get the common records from both the tables.

 Syntax of Inner Join :

select employees.name,employees.address,b.department_name from employees , department where employees.dept_no=department_dept_no;

 Above query will give you common records from both the tables .

 Syntax of Outer Join :

 SELECT

employees.name,

employees.address,

department.department_name

from employees

LEFT JOIN department b ON employees.dept_no= department.dept_no;

Above query will give all the records from employees table and only matching records from department table .

Same we can use with Right Join where we can get all the records from department table and only matching records from employees table.

Hope you understand the difference between inner Join and outer Join .



Question :4 What is the difference between varchar and varchar2?

 Answer

 Varchar and Varchar2 both are the Oracle data types used to store  character string of variable length.

Main Difference :

Varchar can store character upto 2000 bytes whereas Varchar2 can store character upto 4000 bytes. Varchar will hold the space for characters defined during declaration even if all of them are not used , whereas Varchar2 will release the unused space.

Question  :5  How to make Join in more than 2 tables?

Answer 

Suppose we have 3 tables employee , employee_details and department tables you can write below query to get the result as per your requirement.

 SELECT a.emp_no, a.dept_name FROM Employee a JOIN employee_details b ON a.emp_no=b.emp_no JOIN Department c ON b.dept_no=c.dept_no;

 Below is the output for your reference of above query :

 mysql> SELECT a.emp_no, a.dept_name FROM Employee a JOIN employee_details b ON a.emp_no=b.emp_no JOIN Department c ON b.dept_no=c.dept_no;

 

+--------+-----------+

 | emp_no | dept_name |

 +--------+-----------+

 | 10 | IT |

 | 20 | HR |

 | 30 | Sales |

 | 40 | Marketing |

 | 50 | Pre-sales |

 +--------+-----------+

 Question 6 : What Does “Invalid Column Name “  means in SQL ?

 Answer :

 Invalid Column Name if you see while writing any query it means that you write incorrect column name which is not in table.

 Suppose we have a table employee and in table we have column name , address, age and i write the below query :

 select namee,address,age from employee;

After running above query we will get invalid column name error as in table column name is name and we used namee.

 The correct query will be as below.

 select name,address,age from employee;

 it’s common error so make sure if you write any query first see the structure of table then you will not get such message.

 Question :7 What is Cartesian Product in Database?

 Answer

 When we join two or more tables and not put any condition simply select all the data from 2 tables or more than Cartesian Product will display .

Example : If we have 2 tables name as Employee and Department . Employee table contains 10 records and Department table contains 5 records and we write below Join syntax.

select a.name,b.address from employee a , department b ;

then total  result output will be 10*5=50

As In above syntax we haven't put any condition in statement that's why it will give 50 records.

 

Questions 8 : What is Difference Between Truncate and Delete?

Answer

Truncate is a DDL statement while Delete is a DML statement . Truncate can't be rollbacked while delete can be rollback and also we can put condition in delete statement but truncate is DDL so it's worked on definition so we are not allowed to give any condition . Truncate command is mainly used when we want to remove all the records from the table as it is faster than delete .

Question  9:  What is the difference between Primary Key and Unique Key?

 Answer

Main Difference between Primary Key and unique key is , Primary key can't accept null values whereas Unique Key can take null values . Similarity between Primary key and unique key is that they can't accept any Duplicate records.

 Question 10 : What is TCL?

 Answer:

TCL is Transaction Control language which mainly used to manage transaction in database.

TCL commands used in Oracle database are :

Commit , Rollback and Savepoint

Commit Command is used to permanently saved any DML operation in database.Once we use commit we can't Rollback it.

Savepoint is used to temporarily save a transaction so that user can rollback to that point whenever required.

Rollback used to back the original data in table if we delete any records from the table by mistake .

Note : If we delete records by mistake and use the commit command then rollback will not able to get the original data.

 

Question 11: What is the difference between SUBSTR & INSTR functions?

 Answer

SUBSTR function returns the sub-part identified by numeric values from the provided string.

For Example,

Query : [SELECT SUBSTR (‘Hello India’, 1, 4) from dual];

Output will be  “Indi”.

 

INSTR will return the position number of the sub-string within the string.

For Example, [SELECT INSTR (‘Hello India’, ‘o’) from dual] ;

Output will be 5.

 

Question: 12 How does the ON-DELETE-CASCADE statement work?

 Answer

Using ON DELETE CASCADE will automatically delete a record in the child table when the same is deleted from the parent table. This statement can be used with Foreign Keys.

We can add ON DELETE CASCADE option on an existing table using the below set of commands.

Syntax:

 

ALTER TABLE CHILD_T1 ADD CONSTRAINT CHILD_PARENT_FK REFERENCES

PARENT_T1 (COLUMN1) ON DELETE CASCADE;

 

Question 13:  What is an NVL function? How can it be used?

Answer

 NVL is a function  which used to suppress null values and also  helps the user to substitute value if null is encountered for an expression.

It can be used as the below syntax.

NVL (Value_In, Replace_With anything)

By using NVL we can put any value in place of NULL.

Question 14 : When do we use the GROUP BY clause in SQL Query?

 Answer

  GROUP BY clause is used to identify and group the data by one or more columns in the query results. This clause is often used with aggregate functions like COUNT, MAX, MIN, SUM, AVG, etc.

Syntax:

Select sum(amount), name from table_name group by dept_no;

 

 Below query will give you the total sum of amount for particular department number.

Note : we can use any aggregate function in select statement like max,min etc.

 Question 15: What is the quickest way to fetch the data from a table?

 Answer

 ROWID . The quickest way to fetch the data would be to use ROWID in the SQL query.

 ROWID is a Psuedo column and it’s stored all the records in HEXADECIMAL format . For each record we have different ROWID also for the duplicate records.

 

Question 16 : Where do we use DECODE and CASE Statements?

 Answer

 Both DECODE & CASE statements will function like IF-THEN-ELSE statement (Conditional) and they are the alternatives for each other. These functions are used in Oracle to     transform the data values.

  For Example:

DECODE Function 

Select ORDERNUM,

DECODE (STATUS,'O', ‘ORDERED’,'P', ‘PACKED,’S’,’SHIPPED’,’A’,’ARRIVED’)

FROM ORDERS;

CASE Function

Select ORDERNUM

, CASE (WHEN STATUS ='O' then ‘ORDERED’

WHEN STATUS ='P' then PACKED

WHEN STATUS ='S' then ’SHIPPED’

ELSE ’ARRIVED’) END

FROM ORDERS; 

Both the commands will display order numbers with their respective status as per condition matched in table ORDERS.

 If Condition satisfies any output will be against below:

Status O= Ordered
Status P= Packed
Status S= Shipped
Status A= Arrived

 Question 17 :  How to Eliminate/Remove duplicate Records in a Table ?

 Answer:

 Delete from table_name where rowid not in (select max(rowid) from table_name group by dept_no);

 Remove Duplicate records in table we can use ROWID as ROWID is a Psuedo column and it’s stored all the records in HEXADECIMAL format . For each record we have different ROWID also for the duplicate records.

  Question 18 : What is meant by RAW datatype?

Answer: RAW datatype is used to store variable-length binary data or byte strings.

The difference between RAW & VARCHAR2 datatype is that PL/SQL does not recognize this data type and hence, cannot do any conversions when RAW data is transferred to different systems. This data type can only be queried or inserted in a table.

 

Question 19: What do you mean by MERGE in Oracle and how can we merge two tables?

 Answer

The MERGE statement is used to merge the data from two tables. It selects the data from the source table and inserts/updates it in the other table based on the condition provided in the MERGE query.

Syntax:

MERGE INTO TARGET_TABLE_1

USING SOURCE_TABLE_1

ON SEARCH_CONDITION

WHEN MATCHED THEN

INSERT (COL_1, COL_2…)

VALUES (VAL_1, VAL_2…)

WHERE <CONDITION>

WHEN NOT MATCHED THEN

UPDATE SET COL_1=VAL_1, COL_2=VAL_2…

WHEN <CONDITION>

 Question 20 : What is the use of Aggregate functions in Oracle?

Answer

 Aggregate functions perform summary operations on a set of values to provide a single value. There are several aggregate functions that we use in our code to perform calculations. Below are the Aggregate functions

AVG

MIN

MAX

COUNT

SUM

STDEV

 

Note: We can use Group by clause in Aggregate Function.

 

Question 21: What is a nested table and how is it different from a normal table?

 Answer:

 A nested table is a database collection object, which can be stored as a column in a table. While creating a normal table, an entire nested table can be referenced in a single column. Nested tables have only one column with no restriction of rows.

For Example:

CREATE TABLE EMP (

EMP_ID NUMBER,

EMP_NAME  TYPE_NAME)

Here, we are creating a normal table as EMP and referring a nested table TYPE_NAME as a column.

 

Question 22: Can we save images in a database ?

 Answer

Yes by using BLOB data type while creating table .BLOB stands for Binary Large Object, which is a data type that is generally used to hold images, audio & video files or some binary executables. This datatype has the capacity of holding data up to 4 GB.

 

Question 23 : What is a View in Oracle?

 Answer

 View is virtual table mainly used for security purpose .View is a user-defined database object that is used to store the results of an SQL query, which can be referenced later. Views do not store this data physically but as a virtual table, hence it can be referred to as a logical table.

View is different from the table as:

A table can hold data but not SQL query results whereas View can save the query results, which can be used in another SQL query as a whole.

The table can be updated or deleted while Views cannot be done so.

 

Types of View :

Simple View

Complex View

Materialized View

  

Question 24: What do you understand by database schema and what does it hold?

 Answer

 Schema is a collection of database objects owned by a database user who can create or manipulate new objects within this schema. The schema can contain any DB objects like table,     view, indexes, clusters, stored procs, functions, etc.

 

Question 25 : What is a data dictionary and how can it be created?

 Answer

 

Whenever a new database is created, a database-specific data dictionary gets created by the system. This dictionary is owned by the SYS user and maintains all the metadata related to the database. It has a set of read-only tables and views and it is physically stored in the  SYSTEM  table space.

 Some data dictionary tables are : USER_OBJECTS, DBA_ALL etc.

 

Question 26 : What is meant by a deadlock situation?

 Answer

Deadlock is a situation when two or more users are simultaneously waiting for the data, which is locked by each other . Also at the same time we hit same table and doing DML operation deadlock will occur. Hence it results in all blocked user sessions.

 

Question 27 : How to find nth number of Salary in a table?

 Answer

 Select * from employee where sal =

(

Select MIN(sal) from employee where sal in

 ( select distinct TOP N sal from employee

     Order by sal DESC

)

)

 Here we can consider N for any number :

 Also we can use substitution variable & to find the nth number of salary by using distinct in different way.

 Question 28 : What is index in oracle?

 Answer

 Index is an object which is basically used for fast retrieval of data. Index created to search the data efficiently within the table. Indexes are usually created on certain columns of the table, which are accessed the most.

 Suppose we write a query as below if index is not created on column location.

 Select * from employee where location =’Madhubani’;

 If index is not created on particular column and millions of records already there in table . Then it will scan whole table i.e FULL Table scan and performance will be slow.

 Disadvantage of Index :

 If record is less in table then no need to use index as index itself is an object and it occupy space in Database.

 Types of Index:

 Cluster index and Non Cluster index.

 

Question 29 : What is Views and Synonym in Database  ?

 Answer

  View and Synonym :

 View : Views are Virtual/logical table that can be created from existing table. A view can be known as a ‘Stored Query’ or a “Virtual Table”. Views mainly used for security purpose and view used in most places where a table can be used. The table from which view is created are called based table.

 Syntax for creating Views is as below :

 Create view sec_view as select name , address from student where location =’Delhi’ ;

 We can create View from Multiple table as well :

 Create view student_sec as Select student.name , student.id , student_details.address from student a , student_details b where student.id=student_details.id;

 Synonym : Synonym in database is an object which is basically used as alternative name for objects such as tables, views, sequences, stored procedures, and other database objects. We generally use synonyms for granting access to an object from another schema and you don't want the users from which Schema it’s own the object.

 Advantage of using Synonym:

 Hide the name and owner of an object.

 Provide location transparency for remote objects of a distributed database.

 Simple use of SQL statements for database users.

 Create Syntax of Synonym as synonym is of 2 parts public and Private.

 Public Synonym

 Create public synonym dotc.syn_employee

for dotc.employee;

Public Synonym can be accessible everywhere.

Here dotc is schema name .

Private Synonym

 Create synonym dotc.syn1_employee

 for dotc.employee;

 Private Synonym has limitation to its own schema only , but we can give grant access to other user if required like select etc.

 

Question 30 : What is cursor and types of Cursor?

 Answer

A cursor is a pointer to this context area. PL/SQL controls the context area through a cursor. A cursor holds the rows (one or more) returned by a SQL statement. The set of rows the cursor holds is referred to as the active set.

You can name a cursor so that it could be referred to in a program to fetch and process the rows returned by the SQL statement, one at a time.

There are two types of cursors −

Implicit cursors

Explicit cursors

 Implicit Cursor : Implicit cursors are automatically created by Oracle whenever an SQL statement is executed, when there is no explicit cursor for the statement. Programmers cannot control the implicit cursors and the information in it.

 Explicit Cursor :

Explicit cursors are programmer-defined cursors for gaining more control over the context area. An explicit cursor should be defined in the declaration section of the PL/SQL Block. It is created on a SELECT Statement which returns more than one row.

The steps  for creating an explicit cursor is –

 Declaring the cursor

 Open the cursor

 Fetch the records

 Closing the cursor

 Example of Explicit Cursor :

 Declare

C_id customers.id%type;

C_name customer.name%type;

C_addr customer.address%type;

CURSOR C1 is

Select id , name ,address from customers;

BEGIN

OPE C1;

LOOP

FETCH C1 into c_id,c_name,c_addr;

EXIT when C1%notfound;

Dbms_output.put_line(c_id || ' ' ||c_name||' ' ||c_addr);

END   LOOP ;

CLOSE C1;

END;

/

  

Question 31: What is PL/SQL FOR CURSOR LOOP?

 Answer

 CURSOR FOR LOOP is shortcut method and bit easy to use as compared to explicit cursor.

Cursor FOR LOOP Statement. The cursor FOR LOOP statement implicitly declares its loop index as a record variable of the row type that a specified cursor returns and opens a cursor. With each iteration, the cursor FOR LOOP statement fetches a row from the result set into the record.

 

Syntax :

Declare the cursor ---

For i in cursor_name

Loop process _statement;

End loop;

 Shortcut Method of Explicit cursor

 

Question 32: What are the attributes that are found in a CURSOR?

 Answer

 A CURSOR has various attributes as mentioned below:

 1.  %FOUND:

Returns INVALID_CURSOR if the cursor has been declared but closed.

Returns NULL if fetch has not happened but the cursor is open only.

Returns TRUE, if the rows are fetched successfully and FALSE if no rows are returned.

 

2.  NOT FOUND:

Returns INVALID_CURSOR if the cursor has been declared but closed.

Returns NULL if fetch has not happened but the cursor is open only.

Returns FALSE, if rows are fetched successfully and TRUE if no rows are returned

 

3.  %ISOPEN: Returns TRUE, if the cursor is OPEN else FALSE

 4. %ROWCOUNT: Returns the count of fetched rows.

 Question 33 :Why do we use %ROWTYPE & %TYPE in PLSQL?

 Answer

 %ROWTYPE & %TYPE are the attributes in PL/SQL that can inherit the datatypes of a table defined in a database. The purpose of using these attributes is to provide data independence and integrity.

If any of the datatypes or precision gets changed in the database, PL/SQL code gets updated automatically with the changed data type.

%TYPE is used for declaring a variable that needs to have the same data type as of a table column.

While %ROWTYPE will be used to define a complete row of records having a structure similar to the structure of a table.

 

Question 34: What is Difference Between Stored Procedure and Stored Function ?

 Answer

Stored Procedure and Stored Function both are object in Database .Stored Function must return a value whether condition is True or false and while Stored procedure may or may not  return a value if condition is true it will return multiple records.

Differences :

Stored Procedure can include DML operation like insert,update and delete while we can’t do DML operation in Function

Stored Procedure can call function while function can’t call Stored Procedure

Stored Procedure can use exceptional handling by using try/catch block while Function can’t use try/catch block

Stored Procedure can’t be call in Select Statement while function can be called in select statement.

 

Question 35 : What are the parameters that we can pass through a stored procedure?

Answer: We can pass IN, OUT & INOUT parameters through a stored procedure and they should be defined while declaring the procedure itself.

 Question 36: How will you distinguish a global variable with a local variable in PL/SQL?

 Answer

Global variable is the one, which is defined at the beginning of the program and survives until the end. It can be accessed by any methods or procedures within the program, while the access to the local variable is limited to the procedure or method where it is declared.

 

Question 37 :What are the packages in PL SQL?

 Answer:

 A package is a group of related database objects like stored procs, functions, types, triggers, cursors, etc. that are stored in the Oracle database. It is a kind of library of related objects which can be accessed by multiple applications if permitted.

PL/SQL Package structure consists of 2 parts: package specification & package body.

Benefits of using Package : Hide the information from the user.

 Question 38: What is a trigger and what are its types?

Answer

 A trigger is a stored program which is written in such a way that it gets executed automatically when some event occurs. This event can be any DML or a DDL operation.

PL/SQL supports two types of triggers:

Row Level and Statement Level Trigger

 

Question 39: What is the difference between Stored Procedure and Trigger?

Answer

 We need to call Stored Procedure manually to perform sql operation while Trigger Automatically fires for any DML operation by using BEFORE/AFTER clause.

 Question 40 : What is Conditional and Decision making in PL/SQL block and  Types of Conditional statement?

 Answer

 PL/SQL condition is vastly used in sub-programs were programmer can specify one or more condition evaluated by the program , along with a statement or statements to be executed if the condition is determined to be true it will execute that statement  optionally we have option to execute  other statements if condition  false. In other word we can say Decision Making is just like condition based where if it satisfy the condition it will give the result else we can execute other statement in a program. Condition in PL/SQL is similar to other programming language like C++, Java etc. 

Types of Decision Making Statements in PL/SQL:

 

IF-THEN

IF-THEN-ELSE

IF-THEN-ELSIF

NESTED-IF

CASE

 Question 41 : What is Order By and Distinct clause in SQL ?

 Answer

 Order by Clause: SQL ORDER BY clause is used to sort the records in the result set for a SELECT statement. Order by clause is used to sort the result-set in ascending or descending order. The Order By clause sorts the records in ascending order by default. To sort the records in descending order, use the DESC command in SQL.

 Syntax :

 SELECT *

FROM employee

ORDER BY name ASC;

 Above query will give the name from table in ascending order.

 SELECT *

FROM employee

ORDER BY name DESC;

 Above query will give the name from table in descending order.

 DISTINCT CLAUSE :  In SQL DISTINCT clause is mainly used to remove duplicates from the result set from SELECT statement. In other word we can say we use distinct clause to see the filtered record which is not duplicate.

 Syntax and example:

 SELECT DISTINCT address

 FROM employee

 ORDER BY address;

 Above query will give unique address only.

 

Question 42 : What is Varray in PL/SQL?

 Answer

 VARRAY is known as Variable sized array , A VARRAY is single-dimensional collections of elements with the same data type. A VARRAY always has a fixed number of elements(bounded) and never has gaps between the elements (not sparse). In other word we can say PL/SQL programming Language provides a data structure that is known as VARRAY, which is used to store a fixed size sequential collection of elements of the same type. All vaarays consist of contigious memory location . The lowest address represent the first element and the highest address represent to the last element. Varray only accept integer value.

Syntax for creating Varray in PL/SQL

 

Create Or Replace  TYPE dotcarray as VARRAY(3) OF VARCHAR2(15);

 TYPE marks is VARRAY(5) OF INTEGER;

  

Question 43 : What is Index-by Table or Associative Arrays?

 Answer

 Index-by Table (Associative Arrays)

 Index-by-table is a collection in which the array size is not fixed. In Index –by Table each key is unique and is used to locate the corresponding value. Key can be  an integer or a string. Unlike the other collection types like Varray which we discussed in previous blog where key accept subscript type is integer.

 

In Index – by Table the subscript can of integer or strings. At the time of creating the collection, the subscript needs be mentioned. Also index by table not store data sequentially.

Example of creating Index-by Table (Associative Arrays)  by  below Syntax :

 TYPE type_name IS TABLE OF element_type [NOT NULL] INDEX BY subscript_type;

 table_name  type_name;

 

Question 44 : What is Nested Table ?

 

Answer

Nested Table : Nested table is a collection in which the size of the array is not fixed. Nested table has the integer subscript type but in Index by Table subscribt type can be both integer and String . Nested table is like a one-dimensional array with an arbitrary number of elements.

 

Syntax for Creating Nested Table :

 

TYPE type_name IS TABLE OF element_type [NOT NULL];

 table_name type_name;

 

Question 45 : What is Exceptional Handling in PL/SQL?

 Answer :

Exception is an error condition during a program execution , In other word we can say exception is an error which disrupts the normal flow of program instructions.

 

PL/SQL supports developer to catch such errors/condition using EXCEPTION block in the PL/SQL program and can trace action against the error condition.

 

There are Two Types of Exception in PL/SQL

 

1.     System-Defined Exception

2.     User- Defined Exception

Question 46 : What is System Defined and User defined Exception in PL/SQL?

Answer

System- Defined Exceptions are predefined in PL/SQL which get raised WHEN certain database rule is violated. While in User-defined Exception users can create their own exceptions according to the need and to raise these exceptions explicitly by using raise command.

Note: PL/SQL doesn’t provide name for some system exceptions that is known as unnamed system exceptions. This type of exceptions don’t occur frequently and exceptions have two parts code with associate message.

The way to handle to these exceptions is to assign name to them using Pragma EXCEPTION_INIT

 

Syntax:

 PRAGMA EXCEPTION_INIT(exception_name, -error_number);

 Syntax for Exception Handling in PL/SQL Block:

 

In System Defined exception exceptions handled by when others and then  . Syntax in simple we can write as below :

 

WHEN exception THEN

 

    statement;

 

User- Defined Exception in PL/SQL.

 

PL/SQL allows programmer to define own exception as per need in the  program. User –defined Exception must be declared and then raised explicitily by using Raise Statement or the the Procedure RAISE_APPLICATION _ERROR.

 

Note : RAISE_APPLICATION_ERROR: It is used to display user-defined error messages with error number whose range is in between -20000 and -20999. When RAISE_APPLICATION_ERROR executes it returns error message and error code which looks same as Oracle built-in error.

 

Syntax for User-Defined exception is as below:

 Declare

 my-exception EXCEPTION;

 Here my-exception is the name of EXCEPTION.

  

Question 47 : What is Operator and Different Type of Logical Operator in SQL ?

 

Answer

 

 

 

 

 

SQL operators are reserved keywords which basically used in the WHERE clause of a SQL Statement to perform arithmetic, logical and comparison operations.

Since, there are different types of operators in SQL, let us understand different type of operator and its uses in SQL.

 

Logical Operators used in SQL:

 

Below are the Logical operators present in SQL

 

AND

OR

NOT

BETWEEN

LIKE

IN

EXIST

ALL

ANY

 

Question 48:  What is the Advantage of PL/SQL ?

 Answer

 Advantage of PL/SQL :

Procedure in PL/SQL is database object which is mainly used for re usability in other word Procedure is as subprogram unit/module that performs a particular task. These subprograms are combined to form larger programs. This is basically called the 'Modular design'. Procedures can be invoked by another subprogram or program which is called the calling program.

PL/SQL also follows OOPS concepts in Oracle Database and below benefits by using PL/SQL package.

 

Hiding information

Modularity

Easier Application Design

Reusability

Added Functionality

Better Performance.

 

Question 49 : How do you access a table from another schema in Oracle?

Answer

If you want to access table from one schema to another schema you need to give schemaname.table_name.

Suppose you are in schema A and want to access employee table from Schema B .

Below is the command.

Select * from B.employee;

But accessing other schema you should have select privilege in Schema B.

 

GRANT SELECT ON B.employee to A.

Question 50 : What is Pseudo Column in Oracle and types of Pseudo column?

Answer

Pseudo column: A pseudo-column is dynamic in Oracle as it behaves like a table column but is not stored in the table. You can select from pseudo-columns, but you cannot do DML (insert, update and delete) operation. A pseudo-column is also similar to a function without arguments.

 

Below are the Pseudo Column commonly used in Oracle Database:

 

ROWID

ROWNUM

LEVEL

CURVAL

NEXTVAL

 

Above are the Frequent Top 50 interview questions and Answers of SQL and PL/SQL which is mostly asked in interview. Go through each question and Answers as all the important questions and Answers are covered in this topic which frequently asked in Interview. Please let me know if you have any doubts on questions.

 

For More Clarification of Answers on any questions with example you can see my below Blogs where you will able to find all the topics in archive folder with examples also written in Program. Please feel free to reach me anytime for any clarification.

dheeraj60.blogspot.com

 Thanks and All the best for your Interview.






 


Comments

Post a Comment

Popular posts from this blog

SQL and Classification of SQL in Oracle Database #Diksha Oracle Training Centre

  SQL and Classification of SQL in Oracle Database.   SQL is Structured Query Language , which is used for storing, manipulating and retrieving data stored in a relational database .SQL is the standard language for RDBMS. All the Relational Database Management Systems (RDMS) like Oracle, MySQL, Sybase, Informix, IBM DB2 and Microsoft SQL Server use SQL as their standard database language. Oracle is one of the more secured database as compared to other databases. Importance of   SQL : SQL and PL/SQL is a backend process where all data is stored and retrieved in GUI which created either by any programming languages like Java, C++, PHP etc. so we need to have very secure database so that there will be no impact for users. SQL allows users to access data in the relational database management systems. SQL is used to communicate with a database.SQL and PL/SQL allows users to create and drop databases tables , views , stored procedures , functions , packages , trigger etc. SQL allows

Materialized View uses and Benefits in Database #DOTC_Mdb

Materialized View uses and Benefits in Database. Hello Everyone, Today we will discuss about Materialized view as it’s play important role in database. We already discussed about Simple Views and complex views in my previous blog. Before Materialized view go through my previous blog which related to simple view. https://dheeraj60.blogspot.com/2020/05/benefits-of-creating-and-using-view-in.html As we know View is not a database object and not like table which is stored in database, but view can be created from base table a view is an SQL statement that’s stored in the database. This statement, or view, has a name.A view looks and acts a lot like a table. It has columns and rows, and can be included in SELECT queries just like a table. In other word we can say View is a virtual/logical table which is basically used for security purpose. Let’s understand   about   Materialized view : A materialized view is a view that stores the results of the view’s query. Whenever you query the ma

Top 50 Interview Questions On SQL and PL/SQL #DOTC_Mdb

                    Top 50 Interview Questions On SQL and PL/SQL. Today we will Discuss Top 50 interview questions and answers of SQL and PL/SQL which is frequently asked in interview.     Question 1: What is SQL and Classification of SQL? Answer SQL is a Structure Query Language which is vastly used in RDBMS database like Oracle, Sybase, DB2 , Microsoft SQL server etc.   Classification of SQL is as below: DDL (Data Definition Language):  Commands are  create , alter , drop , truncate etc DML (Data Manipulation Language) : Commands are  insert,update and delete . TCL (Transaction Control Language ) : Commands are  Commit , Rollback and Save point. DCL (Data Control Language) : Commands are Grant , Revoke Question 2:    What is meant by Joins? What are the types of join? Answer Joins are basically used to extract/get data from multiple tables using some common columns or conditions and also by using alias to fulfill the condition.   There are various types of Joins as li