SQL PL/SQL Interview Questions and Answers

What is the difference between a "where" clause and a "having" clause?

"Where" is a kind of restiriction statement. You use where clause to restrict all

the data from DB.Where clause is using before result retrieving. But Having clause is

using after retrieving the data. Having clause is a kind of filtering command.

You should always wear formal dresses for an interview - please check Why you should wear formal for an interview

Formal dresses for men

Formal dresses for women

What is the basic form of a SQL statement to read data out of a table?

The basic form to read data out of table is ‘SELECT * FROM table_name; ‘ An

answer: ‘SELECT * FROM table_name WHERE xyz= ‘whatever’;’ cannot be called

basic form because of WHERE clause.

What structure can you implement for the database to speed up table reads?

Follow the rules of DB tuning we have to:

 Properly use indexes ( different types of indexes)

 properly locate different DB objects across different tablespaces, files and

so on

 create a special space (tablespace) to locate some of the data with special

datatype ( for example CLOB, LOB and …)

What are the tradeoffs with having indexes?

 Faster selects, slower updates.

 Extra storage space to store indexes. Updates are slower because in

addition to updating the table you have to update the index.

What is a "join"?

‘Join’ used to connect two or more tables logically with or without common

field.

What is "normalization"? "Denormalization"? Why do you sometimes want to denormalize?

Normalizing data means eliminating redundant information from a table and

organizing the data so that future changes to the table are easier. Denormalization

means allowing redundancy in a table. The main benefit of denormalization is

improved performance with simplified data retrieval and manipulation. This is done

by reduction in the number of joins needed for data processing.

What is a "constraint"?

A constraint allows you to apply simple referential integrity checks to a table.

There are four primary types of constraints that are currently supported by SQL

Server: PRIMARY/UNIQUE - enforces uniqueness of a particular table column.

SQL Interview Questions

http://marancollects.blogspot.com 2/14

DEFAULT - specifies a default value for a column in case an insert operation does not

provide one. FOREIGN KEY - validates that every value in a column exists in a

column of another table. CHECK - checks that every value stored in a column is in

some specified list. Each type of constraint performs a specific type of action. Default

is not a constraint. NOT NULL is one more constraint which does not allow values in

the specific column to be null. And also it is the only constraint which is not a table

level constraint.

What types of index data structures can you have?

An index helps to faster search values in tables. The three most commonly

used index-types are: - B-Tree: builds a tree of possible values with a list of row IDs

that have the leaf value. Needs a lot of space and is the default index type for most

databases. - Bitmap: string of bits for each possible value of the column. Each bit

string has one bit for each row. Needs only few space and is very fast.(however,

domain of value cannot be large, e.g. SEX(m,f); degree(BS,MS,PHD) - Hash: A

hashing algorithm is used to assign a set of characters to represent a text string

such as a composite of keys or partial keys, and compresses the underlying data.

Takes longer to build and is supported by relatively few databases.

What is a "primary key"?

A PRIMARY INDEX or PRIMARY KEY is something which comes mainly from

database theory. From its behavior is almost the same as an UNIQUE INDEX, i.e.

there may only be one of each value in this column. If you call such an INDEX

PRIMARY instead of UNIQUE, you say something about your table design, which I am

not able to explain in few words. Primary Key is a type of a constraint enforcing

uniqueness and data integrity for each row of a table. All columns participating in a

primary key constraint must possess the NOT NULL property.

What is a "functional dependency"? How does it relate to database table design?

Functional dependency relates to how one object depends upon the other in

the database. for example, procedure/function sp2 may be called by procedure sp1.

Then we say that sp1 has functional dependency on sp2.

What is a "trigger"?

Triggers are stored procedures created in order to enforce integrity rules in a

database. A trigger is executed every time a data-modification operation occurs (i.e.,

insert, update or delete). Triggers are executed automatically on occurance of one of

the data-modification operations. A trigger is a database object directly associated

SQL Interview Questions

http://marancollects.blogspot.com 3/14

with a particular table. It fires whenever a specific statement/type of statement is

issued against that table. The types of statements are insert,update,delete and query

statements. Basically, trigger is a set of SQL statements A trigger is a solution to the

restrictions of a constraint. For instance: 1.A database column cannot carry PSEUDO

columns as criteria where a trigger can. 2. A database constraint cannot refer old

and new values for a row where a trigger can.

Why can a "group by" or "order by" clause be expensive to process?

Processing of "group by" or "order by" clause often requires creation of

temporary tables to process the results of the query, which is depending of the

resultset can be very expensive.

What is "index covering" of a query?

Index covering means that "Data can be found only using indexes, without

touching the tables"

What is a SQL view?

An output of a query can be stored as a view. View acts like small table which

meets our criterion. View is a precomplied SQL query which is used to select data

from one or more tables. A view is like a table but it doesn’t physically take any

space. View is a good way to present data in a particular format if you use that query

quite often. View can also be used to restrict users from accessing the tables

directly.

SQL:

SQL is an English like language consisting of commands to store, retrieve,

maintain & regulate access to your database.

SQL*Plus:

SQL*Plus is an application that recognizes & executes SQL commands &

specialized SQL*Plus commands that can customize reports, provide help & edit

facility & maintain system variables.

NVL:

Null value function converts a null value to a non-null value for the purpose of

evaluating an expression. Numeric Functions accept numeric I/P & return numeric

values. They are MOD, SQRT, ROUND, TRUNC & POWER.

Date Functions:

Date Functions are ADD_MONTHS, LAST_DAY, NEXT_DAY,

MONTHS_BETWEEN & SYSDATE.

Character Functions:

SQL Interview Questions

http://marancollects.blogspot.com 4/14

Character Functions are INITCAP, UPPER, LOWER, SUBSTR & LENGTH.

Additional functions are GREATEST & LEAST. Group Functions returns results based

upon groups of rows rather than one result per row, use group functions. They are

AVG, COUNT, MAX, MIN & SUM.

TTITLE & BTITLE:

TTITLE & BTITLE are commands to control report headings & footers.

COLUMN:

COLUMN command define column headings & format data values.

BREAK:

BREAK command clarify reports by suppressing repeated values, skipping

lines & allowing for controlled break points.

COMPUTE:

Command control computations on subsets created by the BREAK command.

SET:

SET command changes the system variables affecting the report

environment.

SPOOL:

SPOOL command creates a print file of the report.

JOIN:

JOIN is the form of SELECT command that combines info from two or more

tables. Types of Joins are Simple (Equijoin & Non-Equijoin), Outer & Self join.

Equijoin returns rows from two or more tables joined together based upon an

equality condition in the WHERE clause. Non-Equijoin returns rows from two or more

tables based upon a relationship other than the equality condition in the WHERE

clause. Outer Join combines two or more tables returning those rows from one table

that have no direct match in the other table. Self Join joins a table to itself as though

it were two separate tables.

Union:

Union is the product of two or more tables.

Intersect:

Intersect is the product of two tables listing only the matching rows.

Minus:

Minus is the product of two tables listing only the non-matching rows.

Correlated Subquery:

SQL Interview Questions

http://marancollects.blogspot.com 5/14

Correlated Subquery is a subquery that is evaluated once for each row

processed by the parent statement. Parent statement can be Select, Update or

Delete. Use CRSQ to answer multipart questions whose answer depends on the value

in each row processed by parent statement.

Multiple columns:

Multiple columns can be returned from a Nested Subquery.

Sequences:

Sequences are used for generating sequence numbers without any overhead

of locking. Drawback is that after generating a sequence number if the transaction is

rolled back, then that sequence number is lost.

Synonyms:

Synonyms is the alias name for table, views, sequences & procedures and are

created for reasons of Security and Convenience. Two levels are Public - created by

DBA & accessible to all the users. Private - Accessible to creator only. Advantages

are referencing without specifying the owner and Flexibility to customize a more

meaningful naming convention.

Indexes:

Indexes are optional structures associated with tables used to speed query

execution and/or guarantee uniqueness. Create an index if there are frequent

retrieval of fewer than 10-15% of the rows in a large table and columns are

referenced frequently in the WHERE clause. Implied tradeoff is query speed vs.

update speed. Oracle automatically update indexes. Concatenated index max. is 16

columns.

Data types:

Max. columns in a table is 255. Max. Char size is 255, Long is 64K & Number

is 38 digits. Cannot Query on a long column. Char, Varchar2 Max. size is 2000 &

default is 1 byte. Number(p,s) p is precision range 1 to 38, s is scale -84 to 127.

Long Character data of variable length upto 2GB. Date Range from Jan 4712 BC to

Dec 4712 AD. Raw Stores Binary data (Graphics Image & Digitized Sound). Max. is

255 bytes. Mslabel Binary format of an OS label. Used primarily with Trusted Oracle.

Order of SQL statement execution Where clause, Group By clause, Having clause,

Order By clause & Select.

Transaction:

Transaction is defined as all changes made to the database between

successive commits.

SQL Interview Questions

http://marancollects.blogspot.com 6/14

Commit:

Commit is an event that attempts to make data in the database identical to

the data in the form. It involves writing or posting data to the database and

committing data to the database. Forms check the validity of the data in fields and

records during a commit. Validity check are uniqueness, consistency and db

restrictions.

Posting:

Posting is an event that writes Inserts, Updates & Deletes in the forms to the

database but not committing these transactions to the database.

Rollback:

Rollback causes work in the current transaction to be undone.

Savepoint:

Savepoint is a point within a particular transaction to which you may rollback

without rolling back the entire transaction.

Set Transaction:

Set Transaction is to establish properties for the current transaction.

Locking:

Locking are mechanisms intended to prevent destructive interaction between

users accessing data. Locks are used to achieve.

Consistency:

Assures users that the data they are changing or viewing is not changed until

they are thro’ with it.

Integrity:

Assures database data and structures reflects all changes made to them in

the correct sequence. Locks ensure data integrity and maximum concurrent access

to data. Commit statement releases all locks. Types of locks are given below.

Data Locks protects data i.e. Table or Row lock. Dictionary Locks protects the

structure of database object i.e. ensures table’s structure does not change for the

duration of the transaction. Internal Locks & Latches protects the internal database

structures. They are automatic. Exclusive Lock allows queries on locked table but no

other activity is allowed. Share Lock allows concurrent queries but prohibits updates

to the locked tables. Row Share allows concurrent access to the locked table but

prohibits for a exclusive table lock. Row Exclusive same as Row Share but prohibits

locking in shared mode. Shared Row Exclusive locks the whole table and allows users

SQL Interview Questions

http://marancollects.blogspot.com 7/14

to look at rows in the table but prohibit others from locking the table in share or

updating them. Share Update are synonymous with Row Share.

Deadlock:

Deadlock is a unique situation in a multi user system that causes two or more

users to wait indefinitely for a locked resource. First user needs a resource locked by

the second user and the second user needs a resource locked by the first user. To

avoid dead locks, avoid using exclusive table lock and if using, use it in the same

sequence and use Commit frequently to release locks.

Mutating Table:

Mutating Table is a table that is currently being modified by an Insert, Update

or Delete statement. Constraining Table is a table that a triggering statement might

need to read either directly for a SQL statement or indirectly for a declarative

Referential Integrity constraints. Pseudo Columns behaves like a column in a table

but are not actually stored in the table. E.g. Currval, Nextval, Rowid, Rownum, Level

etc.

SQL*Loader:

SQL*Loader is a product for moving data in external files into tables in an

Oracle database. To load data from external files into an Oracle database, two types

of input must be provided to SQL*Loader : the data itself and the control file. The

control file describes the data to be loaded. It describes the Names and format of the

data files, Specifications for loading data and the Data to be loaded (optional).

Invoking the loader sqlload username/password controlfilename.

Explain MySQL architecture.

The front layer takes care of network connections and security

authentications, the middle layer does the SQL query parsing, and then the query is

handled off to the storage engine. A storage engine could be either a default one

supplied with MySQL (MyISAM) or a commercial one supplied by a third-party vendor

(ScaleDB, InnoDB, etc.)

Explain MySQL locks.

Table-level locks allow the user to lock the entire table, page-level locks allow

locking of certain portions of the tables (those portions are referred to as tables),

row-level locks are the most granular and allow locking of specific rows.

Explain multi-version concurrency control in MySQL.

Each row has two additional columns associated with it - creation time and

deletion time, but instead of storing timestamps, MySQL stores version numbers.

SQL Interview Questions

http://marancollects.blogspot.com 8/14

What are MySQL transactions?

A set of instructions/queries that should be executed or rolled back as a single

atomic unit.

What’s ACID?

Automicity - transactions are atomic and should be treated as one in case of

rollback. Consistency - the database should be in consistent state between multiple

states in transaction. Isolation - no other queries can access the data modified by a

running transaction. Durability - system crashes should not lose the data.

Which storage engines support transactions in MySQL?

Berkeley DB and InnoDB.

How do you convert to a different table type?

ALTER TABLE customers TYPE = InnoDB

How do you index just the first four bytes of the column?

ALTER TABLE customers ADD INDEX (business_name(4))

What’s the difference between PRIMARY KEY and UNIQUE in MyISAM?

PRIMARY KEY cannot be null, so essentially PRIMARY KEY is equivalent to

UNIQUE NOT NULL.

How do you prevent MySQL from caching a query?

SELECT SQL_NO_CACHE …

1.62 What’s the difference between query_cache_type 1 and 2?

The second one is on-demand and can be retrieved via SELECT SQL_CACHE …

If you’re worried about the SQL portability to other servers, you can use SELECT /*

SQL_CACHE */ id FROM … - MySQL will interpret the code inside comments, while

other servers will ignore it.

What is DDL, DML and DCL?

If you look at the large variety of SQL commands, they can be divided into

three large subgroups. Data Definition Language deals with database schemas and

descriptions of how the data should reside in the database, therefore language

statements like CREATE TABLE or ALTER TABLE belong to DDL. DML deals with data

manipulation, and therefore includes most common SQL statements such SELECT,

INSERT, etc. Data Control Language includes commands such as GRANT, and mostly

concerns with rights, permissions and other controls of the database system.

How do you get the number of rows affected by query?

SELECT COUNT (user_id) FROM users would only return the number of

user_id’s.

SQL Interview Questions

http://marancollects.blogspot.com 9/14

If the value in the column is repeatable, how do you find out the unique

values?

Use DISTINCT in the query, such as SELECT DISTINCT user_firstname FROM

users; You can also ask for a number of distinct values by saying SELECT COUNT

(DISTINCT user_firstname) FROM users;

How do you return the a hundred books starting from 25th?

SELECT book_title FROM books LIMIT 25, 100. The first number in LIMIT is

the offset, the second is the number.

You wrote a search engine that should retrieve 10 results at a time, but at the same time you’d like to know how many rows there’re total. How do you display that to the user?

SELECT SQL_CALC_FOUND_ROWS page_title FROM web_pages LIMIT 1,10;

SELECT FOUND_ROWS(); The second query (not that COUNT() is never used) will

tell you how many results there’re total, so you can display a phrase "Found

13,450,600 results, displaying 1-10". Note that FOUND_ROWS does not pay

attention to the LIMITs you specified and always returns the total number of rows

affected by query.

How would you write a query to select all teams that won either 2, 4, 6 or 8 games?

SELECT team_name FROM teams WHERE team_won IN (2, 4, 6, 8)

How would you select all the users, whose phone number is null?

SELECT user_name FROM users WHERE ISNULL(user_phonenumber);

What does this query mean: SELECT user_name, user_isp FROM users LEFT JOIN isps USING (user_id)

It’s equivalent to saying SELECT user_name, user_isp FROM users LEFT JOIN

isps WHERE users.user_id=isps.user_id

How do you find out which auto increment was assigned on the last insert?

SELECT LAST_INSERT_ID() will return the last value assigned by the

auto_increment function. Note that you don’t have to specify the table name.

What does –i-am-a-dummy flag to do when starting MySQL?

Makes the MySQL engine refuse UPDATE and DELETE commands where the

WHERE clause is not present.

On executing the DELETE statement I keep getting the error about foreign

key constraint failing. What do I do?

SQL Interview Questions

http://marancollects.blogspot.com 10/14

What it means is that so of the data that you’re trying to delete is still alive in

another table. Like if you have a table for universities and a table for students, which

contains the ID of the university they go to, running a delete on a university table

will fail if the students table still contains people enrolled at that university. Proper

way to do it would be to delete the offending data first, and then delete the

university in question. Quick way would involve running SET foreign_key_checks=0

before the DELETE command, and setting the parameter back to 1 after the DELETE

is done. If your foreign key was formulated with ON DELETE CASCADE, the data in

dependent tables will be removed automatically.

When would you use ORDER BY in DELETE statement?

When you’re not deleting by row ID. Such as in DELETE FROM

techinterviews_com_questions ORDER BY timestamp LIMIT 1. This will delete the

most recently posted question in the table techinterviews_com_questions.

How can you see all indexes defined for a table?

SHOW INDEX FROM techinterviews_questions;

How would you change a column from VARCHAR(10) to VARCHAR(50)?

ALTER TABLE techinterviews_questions CHANGE techinterviews_content

techinterviews_CONTENT VARCHAR(50).

How would you delete a column?

ALTER TABLE techinterviews_answers DROP answer_user_id.

How would you change a table to InnoDB?

ALTER TABLE techinterviews_questions ENGINE innodb;

When you create a table, and then run SHOW CREATE TABLE on it, you

occasionally get different results than what you typed in. What does MySQL

modify in your newly created tables?

VARCHARs with length less than 4 become CHARs.

CHARs with length more than 3 become VARCHARs.

NOT NULL gets added to the columns declared as PRIMARY KEYs.

Default values such as NULL are specified for each column

How do I find out all databases starting with ‘tech’ to which I have access to?

SHOW DATABASES LIKE ‘tech%’;

How do you concatenate strings in MySQL?

CONCAT (string1, string2, string3)

How do you get a portion of a string?

SQL Interview Questions

http://marancollects.blogspot.com 11/14

SELECT SUBSTR(title, 1, 10) from techinterviews_questions;

What’s the difference between CHAR_LENGTH and LENGTH?

The first is, naturally, the character count. The second is byte count. For the

Latin characters the numbers are the same, but they’re not the same for Unicode

and other encodings.

How do you convert a string to UTF-8?

SELECT (techinterviews_question USING utf8);

What do % and _ mean inside LIKE statement?

% corresponds to 0 or more characters, _ is exactly one character.

How do you get the month from a timestamp?

SELECT MONTH(techinterviews_timestamp) from techinterviews_questions;

How do you offload the time/date handling to MySQL?

SELECT DATE_FORMAT(techinterviews_timestamp, ‘%Y-%m-%d’) from

techinterviews_questions; A similar TIME_FORMAT function deals with time.

How do you add three minutes to a date?

ADDDATE(techinterviews_publication_date, INTERVAL 3 MINUTE)

What’s the difference between Unix timestamps and MySQL timestamps?

Internally Unix timestamps are stored as 32-bit integers, while MySQL

timestamps are stored in a similar manner, but represented in readable YYYY-MM-DD

HH:MM:SS format.

How do you convert between Unix timestamps and MySQL timestamps?

UNIX_TIMESTAMP converts from MySQL timestamp to Unix timestamp,

FROM_UNIXTIME converts from Unix timestamp to MySQL timestamp.

What are ENUMs used for in MySQL?

You can limit the possible values that go into the table. CREATE TABLE

months (month ENUM ‘January’, ‘February’, ‘March’,…); INSERT months VALUES

(’April’);

How are ENUMs and SETs represented internally?

As unique integers representing the powers of two, due to storage

optimizations.

How do you start and stop MySQL on Windows?

net start MySQL, net stop MySQL

How do you start MySQL on Linux?

SQL Interview Questions

http://marancollects.blogspot.com 12/14

/etc/init.d/mysql start

Explain the difference between mysql and mysqli interfaces in PHP?

mysqli is the object-oriented version of mysql library functions.

What’s the default port for MySQL Server?

3306

What does tee command do in MySQL?

tee followed by a filename turns on MySQL logging to a specified file. It can

be stopped by command notee.

Can you save your connection settings to a conf file?

Yes, and name it ~/.my.conf. You might want to change the permissions on

the file to 600, so that it’s not readable by others.

How do you change a password for an existing user via mysqladmin?

mysqladmin -u root -p password "newpassword"

Use mysqldump to create a copy of the database?

mysqldump -h mysqlhost -u username -p mydatabasename > dbdump.sql

Have you ever used MySQL Administrator and MySQL Query Browser?

Describe the tasks you accomplished with these tools.

What are some good ideas regarding user security in MySQL?

There is no user without a password. There is no user without a user name.

There is no user whose Host column contains % (which here indicates that the user

can log in from anywhere in the network or the Internet). There are as few users as

possible (in the ideal case only root) who have unrestricted access.

Explain the difference between MyISAM Static and MyISAM Dynamic.

In MyISAM static all the fields have fixed width. The Dynamic MyISAM table

would include fields such as TEXT, BLOB, etc. to accommodate the data types with

various lengths. MyISAM Static would be easier to restore in case of corruption, since

even though you might lose some data, you know exactly where to look for the

beginning of the next record.

What does myisamchk do?

It compressed the MyISAM tables, which reduces their disk usage.

Explain advantages of InnoDB over MyISAM?

Row-level locking, transactions, foreign key constraints and crash recovery.

Explain advantages of MyISAM over InnoDB?

Much more conservative approach to disk space management - each MyISAM

table is stored in a separate file, which could be compressed then with myisamchk if

SQL Interview Questions

http://marancollects.blogspot.com 13/14

needed. With InnoDB the tables are stored in tablespace, and not much further

optimization is possible. All data except for TEXT and BLOB can occupy 8,000 bytes

at most. No full text indexing is available for InnoDB. TRhe COUNT(*)s execute

slower than in MyISAM due to tablespace complexity.

What are HEAP tables in MySQL?

HEAP tables are in-memory. They are usually used for high-speed temporary

storage. No TEXT or BLOB fields are allowed within HEAP tables. You can only use

the comparison operators = and <=>. HEAP tables do not support

AUTO_INCREMENT. Indexes must be NOT NULL.

How do you control the max size of a HEAP table?

MySQL config variable max_heap_table_size.

What are CSV tables?

Those are the special tables, data for which is saved into comma-separated

values files. They cannot be indexed.

Explain federated tables.

Introduced in MySQL 5.0, federated tables allow access to the tables located

on other databases on other servers.

What is SERIAL data type in MySQL?

BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT

What happens when the column is set to AUTO INCREMENT and you reach the maximum value for that table?

It stops incrementing. It does not overflow to 0 to prevent data losses, but

further inserts are going to produce an error, since the key has been used already.

Explain the difference between BOOL, TINYINT and BIT.

Prior to MySQL 5.0.3: those are all synonyms. After MySQL 5.0.3: BIT data

type can store 8 bytes of data asnd should be used for binary data.

Explain the difference between FLOAT, DOUBLE and REAL.

FLOATs store floating point numbers with 8 place accuracy and take up 4

bytes. DOUBLEs store floating point numbers with 16 place accuracy and take up 8

bytes. REAL is a synonym of FLOAT for now.

If you specify the data type as DECIMAL (5,2), what’s the range of values that can go in this table?

999.99 to -99.99. Note that with the negative number the minus sign is

considered one of the digits.

What happens if a table has one column defined as TIMESTAMP?

SQL Interview Questions

http://marancollects.blogspot.com 14/14

That field gets the current timestamp whenever the row gets altered.

But what if you really want to store the timestamp data, such as the

publication date of the article?

Create two columns of type TIMESTAMP and use the second one for your real

data.

Explain data type TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE

CURRENT_TIMESTAMP

The column exhibits the same behavior as a single timestamp column in a

table with no other timestamp columns.

What does TIMESTAMP ON UPDATE CURRENT_TIMESTAMP data type do?

On initialization places a zero in that column, on future updates puts the

current value of the timestamp in.

Explain TIMESTAMP DEFAULT ‘2006:09:02 17:38:44′ ON UPDATE CURRENT_TIMESTAMP.

A default value is used on initialization, a current timestamp is inserted on

update of the row.

If I created a column with data type VARCHAR(3), what would I expect to see in MySQL table?

CHAR(3), since MySQL automatically adjusted the data type.

What is the difference between a "where" clause and a "having" clause?

"Where" is a kind of restiriction statement. You use where clause to restrict all

the data from DB.Where clause is using before result retrieving. But Having clause is

using after retrieving the data. Having clause is a kind of filtering command.

What is the difference between a "where" clause and a "having" clause?

"Where" is a kind of restiriction statement. You use where clause to restrict all

the data from DB.Where clause is using before result retrieving. But Having clause is

using after retrieving the data.Having clause is a kind of filtering command.

What is the basic form of a SQL statement to read data out of a table?

The basic form to read data out of table is ‘SELECT * FROM table_name; ‘ An

answer: ‘SELECT * FROM table_name WHERE xyz= ‘whatever’;’ cannot be called

basic form because of WHERE clause.

How to debug the procedure ?

Answer : You can use DBMS_OUTPUT oracle supplied package or DBMS_DEBUG pasckage.

What is trigger,cursor,functions in pl-sql and we need sample programs about it?

Answer : Trigger is an event driven PL/SQL block. Event may be any DML transaction.

Cursor is a stored select statement for that current session. It will not be stored in the database, it is a logical component.

Function is a set of PL/SQL statements or a PL/SQL block, which performs an operation and must return a value.

Can Commit,Rollback ,Savepoint be used in Database Triggers?If yes than HOW? If no Why?With Reasons In pl/sql functions what is use of out parameter even though we have return statement.

Answer : With out parameters you can get the more than one out values in the calling program. It is recommended not to use out parameters in functions. If you need more than one out values then use procedures instead of functions.

How we can create a table through procedure ?

Answer: You can create table from procedure using Execute immediate command.

create procedure p1 is

begin

EXECUTE IMMEDIATE 'CREATE TABLE temp AS

SELECT * FROM emp ' ;

END;

What is ref cursor.

Ref Cursor is cursor variable. It is a pointer to a result set. It is useful in scenarios when result set is created in one program and the processing of the same in some other, might be written in different language, e.g. firing the select is done in PL/SQL and the processing will be done in java. Its a run time query binding with the cursor variable. Normal cursors are static cursors becaz they get acquited of query at the compile time.

What is pl/sql?what are the advantages of pl/sql?

PL/SQL(a product of Oracle) is the 'programming language' extension of sql.

It is a full-fledged language although it is specially designed for database centric activities.

PL/SQL is Very Usefully Language & Tools of Oracle to Manipulate,Restrict,Validate & Control the Unauthorized Access of Data From the Database.

We Can easily show multiple records of the multiple table at same time.

And Using control statement like Loops & If else & Select case we control the database.

State the advantage and disadvantage of Cursor?

Answer : Advantage :

In pl/sql if you want perform some actions more than one records you should user these cursors only. bye using these cursors you process the query records. you can easily move the records and you can exit from procedure when you required by using cursor attributes.

disadvantage:

using implicit/explicit cursors are depended by sutiation. if the result set is les than 50 or 100 records it is better to go for implicit cursors. if the result set is large then you should use exlicit cursors. other wise it will put burdon on cpu.

State the difference between implicit and explicit cursor's.

Answer : Implicit Cursor are declared and used by the oracle internally. whereas the explicit cursors are declared and used by the user. more over implicitly cursors are no need to declare oracle creates and process and closes autometically. the explicit cursor should be declared and closed by the user.

Implict cursor can be used to handle single record (i.e) the select query used should not yield more than one row.if u have handle more than one record then Explict cursor should be used.

What is difference between stored procedures and application procedures,stored function and application function?

Answer : Stored procedures are sub programs stored in the database and can be called & execute multiple times where in an application procedure is the one being used for a particular application same is the way for function

Stored Procedure/Function is a compiled database object, which is used for fast response from Oracle Engine.Difference is Stored Procedure must return multiple value and function must return single value .

Explian rowid,rownum?What are the pseduocolumns we have?

Answer : ROWID - Hexa decimal number each and every row having unique.Used in searching

ROWNUM - It is a integer number also unique for sorting Normally TOP N Analysys.

Other Psudo Column are

NEXTVAL,CURRVAL Of sequence are some exampls

ROWID : It gives the hexadecimal string representing the address of a row.

It gives the location in database where row is physically stored.

ROWNUM: It gives a sequence number in which rows are retrieved from the database.

1What is the starting "oracle error number"?What is meant by forward declaration in functions?

Answer : One must declare an identifier before referencing it. Once it is declared it can be referred even before defining it in the PL/SQL. This rule applies to function and procedures also

ORACLE ERROR NO starts with ORA 00001

In a Distributed Database System Can we execute two queries simultaneously ? Justify ?

Answer : As Distributed database system based on 2 phase commit,one query is independent of 2 nd query so of course we can run.

How we can create a table in PL/SQL block. insert records into it??? is it possible by some procedure or function?? please give example...

Answer : CREATE OR REPLACE PROCEDURE ddl_create_proc (p_table_name IN VARCHAR2)

l_stmt VARCHAR2(200);

BEGIN

DBMS_OUTPUT.put_line('STARTING ');

l_stmt := 'create table '|| p_table_name || ' as (select * from emp )';

execute IMMEDIATE l_stmt;

DBMS_OUTPUT.put_line('end ');

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.put_line('exception '||SQLERRM || 'message'||sqlcode);

END;

We can create table in procedure as explained in above case. but we can't create or perform any DDL in functions.

Question : How to avoid using cursors? What to use instead of cursor and in what cases to do so?

Answer : just use subquery in for clause

ex:

for emprec in (select * from emp)

loop

dbms_output.put_line(emprec.empno);

end loop;

no exit statement needed

implicit open,fetch,close occurs

We have avoided declaration of cursor.the given example will create cursor with some system generated name.

How to disable multiple triggers of a table at at a time?

Answer : ALTER TABLE<TABLE NAME> DISABLE ALL TRIGGER

ALTER TABLE TT_DCB DISABLE ALL TRIGGERS

can we declare a column having number data type and its scale is larger than pricesion

ex: column_name NUMBER(10,100),

column_name NUMBAER(10,-84)

Ans:

Yes,

we can declare a column with above condition.

table created successfully.

We can create the table like

create table as1(id number(10,11))

but we cant insert the data into the table.

it will give error

What are the Restrictions on Cursor Variables?

Answer : Currently, cursor variables are subject to the following restrictions:You cannot declare cursor variables in a package spec. For example, the following declaration is not allowed:CREATE PACKAGE emp_stuff AS TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE; emp_cv EmpCurTyp; -- not allowedEND emp_stuff;You cannot pass cursor variables to a procedure that is called through a database link.If you pass a host cursor variable to PL/SQL, you cannot fetch from it on the server side unless you also open it there on the same server call.You cannot use comparison operators to test cursor variables for equality, inequality, or nullity.You cannot assign nulls to a cursor variable.Database columns cannot store the values of cursor variables. There is no equivalent type to use in a CREATE TABLE statement.You cannot store cursor variables in an associative array, nested table, or varray.Cursors and cursor variables are not interoperable; that is, you cannot use one where the other is expected. For example, you cannot reference a cursor variable in a cursor FOR loop

What will the Output for this Coding>

Declare

Cursor c1 is select * from emp FORUPDATE;

Z c1%rowtype;

Begin

Open C1;

Fetch c1 into Z;

Commit;

Fetch c1 in to Z;

end;

Answer : By declaring this cursor we can update the table emp through z,means wo not need to write table name for updation,it may be only by "z".

selecting in FOR UPDATE mode locks the result set of rows in update mode, which means that row cannot be updated or deleted until a commit or rollback is issued which will release the row(s).

What is PL/SQL ?

Answer : PL/SQL is a procedural language that has both interactive SQL and procedural programming language constructs such as iteration, conditional branching.

What is the basic structure of PL/SQL ?

Answer : PL/SQL uses block structure as its basic structure. Anonymous blocks or nested blocks can be used in PL/SQL.

DECLARE

--all the variables u use in ur program should be declared here

---

BEGIN

--application logic goes here

EXCEPTION HANDLING

--very imp

END

What are the components of a PL/SQL block ?

Answer : A set of related declarations and procedural statements is called block.

What are the components of a PL/SQL Block ?

Answer : Declarative part, Executable part and Exception part.

Datatypes PL/SQL

What are the datatypes a available in PL/SQL ?

Some scalar data types such as NUMBER, VARCHAR2, DATE, CHAR, LONG, BOOLEAN. Some composite data types such as RECORD & TABLE.

What are % TYPE and % ROWTYPE ? What are the advantages of using these over datatypes?

Ans: % TYPE provides the data type of a variable or a database column to that variable.

% ROWTYPE provides the record type that represents a entire row of a table or view or columns selected in the cursor.

The advantages are : I. Need not know about variable's data type

ii. If the database definition of a column in a table changes, the data type of a variable changes accordingly.

%TYPE provides datatypes of the particular column of the table.

%ROWTYPE attribute is useful

When we need to fetch entire row from the table.

secondly if we don't know the data type of some column %rowtype is useful for that.

finally, if we change any datatypes of the column,%rowtype automatically change the datatypes for the variable which is created by user

What is difference between % ROWTYPE and TYPE RECORD ?

Answer : % ROWTYPE is to be used whenever query returns a entire row of a table or view.

TYPE rec RECORD is to be used whenever query returns columns of different

table or views and variables.

E.g. TYPE r_emp is RECORD (eno emp.empno% type,ename emp ename %type

);

e_rec emp% ROWTYPE

cursor c1 is select empno,deptno from emp;

e_rec c1 %ROWTYPE.

What is PL/SQL table ?

Answer : Objects of type TABLE are called "PL/SQL tables", which are modeled as (but not the same as) database tables, PL/SQL tables use a primary PL/SQL tables can have one column and a primary key.

Cursors

TYPE tab IS TABLE OF VARCHAR2(30);

This way we can make declaration of PL/SQL tables. They are also reffed as Nested Table and are pat of PLSQL collections. They are used for bulk data processing.

What is a cursor ? Why Cursor is required ?

Cursor is a named private SQL area from where information can be accessed. Cursors are required to process rows individually for queries returning multiple rows.

The oracle server uses works areas

called private sql area.Here all the DML statement is executed and to processing statement.basically

it's a implicit cursor.

there are two types of cursor

1.Implicit cursor.

2.Explicit cursor.

Implicit cursor is open for all DML statement.after execute the statement cursor is atomatically closed.

Explicit cursor is created by programmer.

explicit cursor is needed when

query returns more than one rows.

In that case,programmer creates

explicit cursor.open the cursor.

then fetch the value from the active set.

after fetching all the value,

cursor is closed by programmer.

Explain the two type of Cursors ?

Answer : There are two types of cursors, Implicit Cursor and Explicit Cursor.

PL/SQL uses Implicit Cursors for queries. User defined cursors are called Explicit Cursors. They can be declared and used.

there are two types of cursor

1.implicit cursor

2. Explicit cursor

What are the PL/SQL Statements used in cursor processing ?

Ans: DECLARE CURSOR cursor name, OPEN cursor name, FETCH cursor name INTO or Record types, CLOSE cursor name.

What are the cursor attributes used in PL/SQL ?

%ISOPEN - to check whether cursor is open or not

% ROWCOUNT - number of rows fetched/updated/deleted.

% FOUND - to check whether cursor has fetched any row. True if rows are fetched.

% NOT FOUND - to check whether cursor has fetched any row. True if no rows are featched.

These attributes are proceeded with SQL for Implicit Cursors and with Cursor name for Explicit Cursors.

What is a cursor for loop ?

Answer : Cursor for loop implicitly declares %ROWTYPE as loop index,opens a cursor, fetches rows of values from active set into fields in the record and closes when all the records have been processed.

eg. FOR emp_rec IN C1 LOOP

salary_total := salary_total +emp_rec sal;

END LOOP;

Cursor for loop implicitly declares %ROWTYPE as loop index,opens a cursor, fetches rows of values from active set into fields in the record and closes when all the records have been processed.

eg. FOR emp_rec IN C1 LOOP

salary_total := salary_total +emp_rec sal;

END LOOP;

Question : What will happen after commit statement ?

Answer : Cursor C1 is

Select empno,

ename from emp;

Begin

open C1; loop

Fetch C1 into

eno.ename;

Exit When

C1 %notfound;-----

commit;

end loop;

end;

The cursor having query as SELECT .... FOR UPDATE gets closed after COMMIT/ROLLBACK.

The cursor having query as SELECT.... does not get closed even after COMMIT/ROLLBACK.

After commit statement,all the transaction will be ended.

all the changes data are parmanently stored in the database.

Question : Explain the usage of WHERE CURRENT OF clause in cursors ?

WHERE CURRENT OF clause in an UPDATE,DELETE statement refers to the latest row fetched from a cursor. Database Triggers

Where CURRENT OF clause means

cursor

points to present row of

the cursor

What is a database trigger ? Name some usages of database trigger ?

Answer : Database trigger is stored PL/SQL program unit associated with a specific database table. Usages are Audit data modifications, Log events transparently, Enforce complex business rules Derive column values automatically, Implement complex security authorizations. Maintain replicate tables.

A database triggers is stored PL/SQL program unit associated with a specific database table or view. The code in the trigger defines the action the database needs to perform whenever some database manipulation (INSERT, UPDATE, DELETE) takes place.

Unlike the stored procedure and functions, which have to be called explicitly, the database triggers are fires (executed) or called implicitly whenever the table is affected by any of the above said DML operations.

Till oracle 7.0 only 12 triggers could be associated with a given table, but in higher versions of Oracle there is no such limitation. A database trigger fires with the privileges of owner not that of user

A database trigger has three parts

1. A triggering event

2. A trigger constraint (Optional)

3. Trigger action

A triggering event can be an insert, update, or delete statement or a instance shutdown or startup etc. The trigger fires automatically when any of these events occur A trigger constraint specifies a Boolean expression that must be true for the trigger to fire. This condition is specified using the WHEN clause. The trigger action is a procedure that contains the code to be executed when the trigger fires.

How many types of database triggers can be specified on a table ? What are they ?

Insert Update Delete

Before Row o.k. o.k. o.k.

After Row o.k. o.k. o.k.

Before Statement o.k. o.k. o.k.

After Statement o.k. o.k. o.k.

If FOR EACH ROW clause is specified, then the trigger for each Row affected by the statement.

If WHEN clause is specified, the trigger fires according to the returned Boolean value.

Row level trigger,Statement level trigger,Instead of trigger(created on views)

Is it possible to use Transaction control Statements such a ROLLBACK or COMMIT in Database Trigger ? Why ?

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

we can use TCL commands in trigger by using autonomous transactions feature of oracle.

What are two virtual tables available during database trigger execution ?

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

What happens if a procedure that updates a column of table X is called in a database trigger of the same table ?

Answer : Mutation of table occurs.

Write the order of precedence for validation of a column in a table ?

I. done using Database triggers.

ii. done using Integarity Constraints.

Answer : I & ii.

Exception :

What is an Exception ? What are types of Exception ?

Ans: Exception is the error handling part of PL/SQL block. The types are Predefined and user defined. Some of Predefined exceptions are.

CURSOR_ALREADY_OPEN

DUP_VAL_ON_INDEX

NO_DATA_FOUND

TOO_MANY_ROWS

INVALID_CURSOR

INVALID_NUMBER

LOGON_DENIED

NOT_LOGGED_ON

PROGRAM-ERROR

STORAGE_ERROR

TIMEOUT_ON_RESOURCE

VALUE_ERROR

ZERO_DIVIDE

OTHERS.

An exception is an identifier/error

that is handle by pl/sql block.

Exception is two types

1.Predefind exception

2.Userdefined exception

predefind exceptions are

1.TOO_MANY_ROWS

2.INVALID_CURSOR

3.NO_DATA_FOUND

What is Pragma EXECPTION_INIT ? Explain the usage ?

Answer : The PRAGMA EXECPTION_INIT tells the complier to associate an exception with an oracle error. To get an error message of a specific oracle error.

e.g. PRAGMA EXCEPTION_INIT (exception name, oracle error number)

The PRAGMA_EXCEPTION_INIT

tells the compiler to assosiate

an exception with an oracle error.

What is Raise_application_error ?

Answer : Raise_application_error is a procedure of package DBMS_STANDARD which allows to issue an user_defined error messages from stored sub-program or database

trigger.

RAISE_APPLICATION_ERROR is a

procedure of package DBMS which

allows to issue user_defined error

message.

What are the return values of functions SQLCODE and SQLERRM ?

Answer : SQLCODE returns the latest code of the error that has occurred.

SQLERRM returns the relevant error message of the SQLCODE.

SQLCODE returns the latest code of the error that has occured.

SQLERRM returns relevant massege

of the error code

Where the Pre_defined_exceptions are stored ?

Answer : In the standard package.

Procedures, Functions & Packages ;

What is a stored procedure ?

Answer : A stored procedure is a sequence of statements that perform specific function.

A stored procedure is a named pl/sql block which performs an action.It is stored in the database as a schema object and can be repeatedly executed.It can be invoked, parameterised and nested.

What is difference between a PROCEDURE & FUNCTION ?

Answer : A FUNCTION is always returns a value using the return statement.

A PROCEDURE may return one or more values through parameters or may not return at all.

A function can be called from sql statements and queries while procedure can be called in a begin end block only. In case of function,it must have

return type.

In case of procedure,it may or may n't have return type.

In case of function, only it takes IN parameters

IN case of procedure

only it take IN,OUT,INOUT parameters..

What are advantages fo Stored Procedures

Answer : Extensibility,Modularity, Reusability, Maintainability and one time compilation. Question : What are the modes of parameters that can be passed to a procedure ?

Answer : IN,OUT,IN-OUT parameters.

What are the two parts of a procedure ?

Ans: Procedure Specification and Procedure Body.

Give the structure of the procedure ?

PROCEDURE name (parameter list.....)

local variable declarations

BEGIN

Executable statements.

Exception.

exception handlers

end;

basically procedure has three

parts

1.variable declaretion(optional)

2.body(mandetory)

3.Exception(optional)

suppose ex

CREATE OR REPLACEPROCEDURE emp_pro( p_id IN employees.employee_id%TYPE)

v_name employees.last_name%TYPE;

v_mail employees.email%TYPE;

BEGIN

SELECT last_name,email INTO v_name,v_mail FROM employees

WHERE employee_id:=p_id;

DBMS_OUTPUT.PUT_LINE('NAME:'||v_name ||'MAILID:'||v_mail);

END;

Give the structure of the function ?

Structure of the fuction same as procedure

it has three parts

1.variable declaration(optional)

2.function body(mandetory)

3.Exception part(optional)

FUNCTION name (argument list .....) Return datatype is

local variable declarations

Begin

executable statements

Exception

execution handlers

End;

create or replace function <name>(arg1,arg2,....)

return datatype

....

variable declaration

begin

...

program code

...

return <variable>

exception

exception statement

end;

Question : Explain how procedures and functions are called in a PL/SQL block ?

Function is called as part of an expression.

sal := calculate_sal ('a822');

procedure is called as a PL/SQL statement

calculate_bonus ('A822');

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 ?

Overloading of procedure

name of the procedure is

same but the number of parameters should be different.In that case,procedure will be overloaded.

2. if the number of parameters are same in that case,data type should be different.

if the two rules are satisfied in that case procedure will be overloaded.

What are two parts of package ?

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

package has two parts

1.Package specification

2.Package body

In the specification,where we declare variable,function,procedure

that is global to the package and local to the schema.

package body contains the defination of the function,procedure.we can also declare private function and procedure which is not accessble

out side the package.

What is difference between a Cursor declared in a procedure and Cursor declared in a package specification ?

A cursor declare in the package

specification that can be accessed

in the other procedure or procedures of the package.

A cursor declare in the procedure

that can't be accessed by other procedure.Answer : 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 can not be accessed by other procedures.

One more differene is cursor declared in a package specification must have RETURN type

How packaged procedures and functions are called from the following?

a. Stored procedure or anonymous block

b. an application program such a PRC *C, PRO* COBOL

c. SQL *PLUS

Answer : a. PACKAGE NAME.PROCEDURE NAME (parameters);

variable := PACKAGE NAME.FUNCTION NAME (arguments);

EXEC SQL EXECUTE

BEGIN

PACKAGE NAME.PROCEDURE NAME (parameters)

variable := PACKAGE NAME.FUNCTION NAME (arguments);

END;

END EXEC;

c. EXECUTE PACKAGE NAME.PROCEDURE if the procedures does not have any out/in-out parameters. A function cannot be called.

Name the tables where characteristics of Package, procedure and functions are stored ?

Answer : User_objects, User_Source and User_error.

Concept of keys

1.183 Consider the table “employee”

Eid ename age address DOB

1 Nali 23 b’lore 13/03/1986

2 Tulu 23 b’lore 13/03/1986

3 Tulu 24 BBSR 14/03/03

Keys: Keys uniquely identify records in the table; it can be more than one attribute

Primary key: A primary key is an attribute that can be used to identify a unique row in a table. Attributes are associated with it. It cannot contain ‘null’ values.

Unique Key: It is same as primary key; the only difference is that it can contain null values.

Composite key: It is a key which is a combination of more than one attributes

Candidate key: This is all the possible sets of key

Super Key: It is the super set of candidate key. We can make super by adding any attribute to the candidate key.

In the above table we can say the super key is

a. Eid,ename

b. Eid, ename, age, address, dob,

c. Ename, dob, age

In the above table we can say the keys are

a. Key1=eid

b. Key2=ename,dob

c. Key3=ename,address

In the above table we can say the Primary key is ’eid’

In the above table we can say the Composite key is ’ename,dob’

In the above table we can say the Candidate key is {eid,(ename,dob), (ename,address)}

Explain different types of join with example

A join is query that combines rows from two or more tables, views or Materialized views.

Join is of following types

1. Equijoins (Simple join, Inner join)

2. Non-equijoins

3. Self join

4. Cross join

5. Natural join

6. Cartesian product

7. Outerjoin

a. Left outerjoin

b. Right join

c. Full outer join

1. Equijoins (Simple join or Inner join)

An equijoin is a join with a join condition containing an equality operator

Select * from emp, dept where emp.deptno=dept.deptno;

Select * from emp JOIN dept using (deptno);

2. Non-Equijoins

It is a join condition that is executed when no column in one table corresponds directly to a column in the other table.

The data in the tables is directly not related but indirectly or logically related through proper values

Select e.ename, e.sal,s.grade from emp e, salgrade s where e.sal BETWEEN s.losalAND s.hisal;

3. Self join

It is a join of a table to itself

Select e1.ename “EmpName”, e2.ename “Manager” from emp e1, emp e2 where e1.mgr=e2.empno;

4. Cross join:

Select * from emp CROSS JOIN dept;

5. Natural join

Select * from emp NATURAL JOIN dept;

6. Cartesian Product

Cartesian product is a join query that has no join codition

Select ename, job, dnam from emp, dept;

7. Outer join

An outer join extends the result of a simple join.

An outer join returns all rows that satisfy the join condition and also those rows from one table for which no rows from the other satisfy the join condition

a. Left outer join

Select * from emp, dept where emp.deptno=(+) dept.deptno;

b. Right outer join

Select * from emp, dept where emp.deptno(+)= dept.deptno;

c. Full outer join

Select MGR from emp where eneme=’ADAMS’)

)

calculator