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;
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.
Thanks Sir !
ReplyDeleteYou Welcome !
DeleteIt is very useful for me. Thanks...
ReplyDeleteAzure Data Factory Online Training
Azure Data Factory Live Online Training