What
is SQL?
SQL stands for
structured query language. It is a database language used for database
creation, deletion, fetching rows and modifying rows etc. sometimes it is
pronounced as se-qwell.
When SQL appeared?
It appeared in
1974.
What are the usages of SQL?
To execute queries against a database
To retrieve data from a database
To inserts records in a database
To updates records in a database
To delete records from a database
To create new databases
To create new tables in a database
To create views in a database
Does
SQL support programming?
No, SQL
doesn't have loop or Conditional statement. It is used like commanding language
to access databases.
What
are the subsets of SQL?
Data definition language
(DDL)
Data manipulation language
(DML)
Data control language (DCL)
What
is data definition language?
Data
definition language(DDL) allows you to CREATE, ALTER and DELETE database
objects such as schema, tables, view, sequence etc.
What is data
manipulation language?
Data manipulation language makes user able to access and
manipulate data. It is used to perform following operations.
- Insert data into database
- Retrieve data from the database
- Update data in the database
- Delete data from the database
What is data control
language?
Data control language allows you to control access to the
database. It includes two commands GRANT and REVOKE.
GRANT: to grant specific user to perform specific task.
REVOKE: to cancel previously denied or granted permissions.
What are the type of
operators available in SQL?
1. Arithmetic
operators
2. Logical
operators
3. Comparison
operator
What is the SQL
query to display current date?
There
is a built in function in SQL called sysdate
which is used to return current timestamp.
Which types of join
is used in SQL widely?
The
knowledge of JOIN is very necessary for an interviewee. Mostly used join is
INNER JOIN and (left/right) OUTER JOIN.
What is
"TRIGGER" in SQL?
Trigger
allows you to execute a batch of SQL code when an insert, update or delete
command is executed against a specific table.
Actually
triggers are special type of stored procedures that are defined to execute
automatically in place or after data modifications.
What is self join
and what is the requirement of self join?
Self join is often very useful to convert a hierarchical
structure to a flat structure. It is used to join a table to itself as like if
that is the second table.
What are set
operators in SQL?
Union, intersect or minus operators
are called set operators.
What is a
constraint? Tell me about its various levels.
Constraints
are representators of a column to enforce data entity and consistency. There
are two levels :
·
column
level constraint
·
table
level constraint
Write an SQL query
to find names of employee start with 'A'?
SELECT * FROM Employees WHERE EmpName like 'A%'
Write an SQL query
to get third maximum salary of an employee from a table named employee_table
1.
SELECT TOP 1 salary
2.
FROM (
3.
SELECT TOP 3 salary
4.
FROM employee_table
5.
ORDER BY salary DESC ) AS emp
6.
ORDER BY salary ASC;
What is the
difference between DELETE and TRUNCATE statement in SQL?
The main
differences between SQL DELETE and TRUNCATE statements are given below:
No.
|
DELETE
|
TRUNCATE
|
1)
|
DELETE is a DML command.
|
TRUNCATE is a DDL command.
|
2)
|
We can use WHERE clause in
DELETE command.
|
We cannot use WHERE clause with
TRUNCATE
|
3)
|
DELETE statement is used to delete a
row from a table
|
TRUNCATE statement is used to remove
all the rows from a table.
|
4)
|
DELETE is slower than TRUNCATE
statement.
|
TRUNCATE statement
is faster than DELETE statement.
|
5)
|
You can rollback data after
using DELETE statement.
|
It is not possible to
rollback after using TRUNCATE statement
|
What is PL/SQL?
PL/SQL stands for procedural language extension to SQL. It supports
procedural features of programming language and SQL both. It was developed by
Oracle Corporation in early of 90's to enhance the capabilities of SQL.
PL/SQL is a procedural language that has both
interactive SQL and procedural programming language constructs such as
iteration, conditional branching.
What is PL/SQL table? Why it is used?
Objects of type tables are called PL/SQL tables that are modeled as
database table. We can also say that PL/SQL tables are a way to providing
arrays. Arrays are like temporary tables in memory that are processed very
quickly. PL/SQL tables are used to move bulk data. They simplifies moving
collections of data.
What are the datatypes available in PL/SQL?
There are two types of datatypes in PL/SQL:
1. Scalar
datatypes Example are NUMBER, VARCHAR2, DATE, CHAR, LONG, BOOLEAN etc.
2. Composite
datatypes Example are RECORD, TABLE etc.
What is the basic structure of PL/SQL?
PL/SQL
uses BLOCK structure as its basic structure. Each PL/SQL program consists of
SQL and PL/SQL statement which form a PL/SQL block.
PL/SQL
block contains 3 sections.
The Declaration Section (optional)
The Execution Section (mandatory)
The Exception handling Section (optional)
What is the difference between FUNCTION, PROCEDURE AND PACKAGE in
PL/SQL?
Function: The
main purpose of a PL/SQL function is generally to compute and return a single value.
A function has a return type in its specification and must return a value
specified in that type.
Procedure: A
procedure does not have a return type and should not return any value but it
can have a return statement that simply stops its execution and returns to the
caller. A procedure is used to return multiple values otherwise it is generally
similar to a function.
Package: A
package is schema object which groups logically related PL/SQL types , items
and subprograms. You can also say that it is a group of functions, procedure,
variables and record type statement. It provides modularity, due to this
facility it aids application development. It is used to hide information from
unauthorized users.
What is exception? What are the types of exceptions?
Exception is an error handling part of PL/SQL. There are two type of
exceptions: pre_defined exception and user_defined exception.
How exception is different from error?
Whenever an Error occurs Exception arises. Error is a bug whereas
exception is a warning or error condition.
What is the main reason behind using an index?
Faster access of data blocks in the table.
What are PL/SQL exceptions? Tell me any three.
1. Too_many_rows
2. No_Data_Found
3. Value_error
4. Zero_error
etc.
What is the maximum number of triggers, you can apply on a single
table?
12 triggers.
How many types of triggers exist in PL/SQL?
There are 12 types of triggers in PL/SQL that
contains the combination of BEFORE, AFTER, ROW, TABLE, INSERT, UPDATE, DELETE
and ALL keywords.
- BEFORE
ALL ROW INSERT
- AFTER
ALL ROW INSERT
- BEFORE
INSERT
- AFTER
INSERT etc.
What is stored Procedure?
A stored procedure is a sequence of statement or a named PL/SQL block
which performs one or more specific functions. It is similar to a procedure in
other programming languages. It is stored in the database and can be repeatedly
executed. It is stored as schema object. It can be nested, invoked and
parameterized.
What are the advantages of stored procedure?
Modularity, extensibility, reusability, Maintainability and one time
compilation.
What are the cursor attributes used in PL/SQL?
%ISOPEN: it checks whether
the cursor is open or not.
%ROWCOUNT: returns the number
of rows affected by DML operations: INSERT,DELETE,UPDATE,SELECT.
%FOUND: it checks whether
cursor has fetched any row. If yes - TRUE.
%NOTFOUND: it checks whether
cursor has fetched any row. If no - TRUE.
What is consistency?
Consistency simply means that each user
sees the consistent view of the data.
Consider an example: there are two users A
and B. A transfers money to B's account. Here the changes are updated in A's
account (debit) but until it will be updated to B's account (credit), till then
other users can't see the debit of A's account. After the debit of A and credit
of B, one can see the updates. That?s consistency.
What is cursor and why it is required?
A cursor is a temporary
work area created
in a system memory when an SQL statement is executed.
A cursor contains information on a select
statement and the row of data accessed by it. This temporary work area stores
the data retrieved from the database and manipulate this data. A cursor can
hold more than one row, but can process only one row at a time. Cursor are
required to process rows individually for queries.
How many types of cursors are available in PL/SQL?
There are two types of cursors
in PL/SQL.
1. Implicit
cursor, and
2. explicit
cursor
What is the difference between
%ROWTYPE and %TYPE and what is the main advantage to using these?
The %ROWTYPE allows the coder to indirectly represent a full or
partial row of a database table or view, whereas the %TYPE allows for the coder
to indirectly represent the data type from a previously declared variable or
column. Basically, %ROWTYPE works on a full object whereas %TYPE works on a
single column. The advantage to using either of these enables the coder to
maintain data type declarations without ever having to know or change the data
type for the items that use these. Below is an example of how the %TYPE allows
for a layer of abstraction between names; allowing the coder to just change the
first occurrence of the data type.
DECLARE
name VARCHAR(50);
fname name%TYPE;
lname name%TYPE;
city name%TYPE;
country name%TYPE;
BEGIN
Execution section;
END;
/
How might you display compile
time warnings for PL/SQL code?
There are actually two methods to show compile time warnings.
While both 'SHOW ERRORS' and the *_errors views (USER_ERRORS used here) show
basically the same information; I tend to like the SHOW ERRORS command as it
seems quicker to type. The advantage to using the *_errors views is that you
can actually monitor every developer's current errors when using a view such as
DBA_ERRORS, as there is an additional column for OWNER that will tell you the
user encountering errors.
Define 'scope' and 'visibility'
for PL/SQL variables.
The definition of scope and visibility for a variable is actually quite
close with the only difference being if you have to qualify the variable. The
scope of a variable refers to the region (breadth) of code where the variable
can be referenced. The visibility refers to the region of code you can
reference the variable without qualifying it. So, hopefully you can see,
visibility is a subset of the scope and requires the variable to be qualified
(told where it comes from) in order to use. An example is clearly the best
option here to help explain. Consider the PL/SQL code:
What are two virtual
tables available during database trigger execution?
The table columns are referred as OLD.column_name and
NEW.column_name.
For triggers related to INSERT only NEW.column_name values
only available.
For triggers related to UPDATE only OLD.column_name
NEW.column_name values only available.
For triggers related to DELETE only OLD.column_name values
only available.
Is it possible to
use Transaction control Statements such a ROLLBACK or COMMIT in Database
Trigger? Why?
It is not possible. As triggers are defined for each table,
if you use COMMIT of ROLLBACK in a trigger, it affects logical transaction
processing.
What is Overloading
of procedures?
The Same
procedure name is repeated with parameters of different datatypes and
parameters in different positions, varying number of parameters is called
overloading of procedures.
e.g.
DBMS_OUTPUT.put_line
What
is a package? What are the advantages of packages?
Package is a
database object that groups logically related procedures. The advantages of
packages are Modularity, Easier Applicaton Design, and Information hiding, Reusability
and Better Performance.
What
are two parts of package?
The two parts
of package are PACKAGE SPECIFICATION & PACKAGE BODY. Package Specification
contains declarations that are global to the packages and local to the schema. Package
Body contains actual procedures and local declaration of the procedures and
cursor declarations.
What
is difference between a Cursor declared in a procedure and Cursor declared in a
package specification?
A cursor
declared in a package specification is global and can be accessed by other
procedures or procedures in a package.
A cursor declared in a procedure is local to the procedure that cannot be
accessed by other procedures.
Rank:
In Oracle
PL/SQL, RANK function is a built in analytic
function which is used to rank a record within a group of rows. Its return type
is number and serves for both aggregate and analytic purpose in SQL.
SQL of ROW_NUMBER, RANK & DENSE_RANK:
select last_name,department_id,salary,
row_number() over ( order by salary
desc)
Row_Num,
rank() over ( order by salary desc) Rank,
dense_rank() over ( order by salary
desc)
Dense_RAnk
from EMPLOYEES
SQL using LEAD & LAG function:
SELECT emp_ID,
LAST_NAME,
job_ID,
salARY,
LEAD(salARY, 1, 0) OVER (ORDER BY salARY)
AS sal_next,
LAG (salARY, 1, 0) OVER (ORDER BY salARY) AS sal_prev,
FROM emPLOYEES;
