SQL interview questions and answers

SQL-Structured Query Language is a language consisting of commands to store, retrieve, maintain & regulate access to your database.

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 are ADD_MONTHS, LAST_DAY, NEXT_DAY, MONTHS_BETWEEN & SYSDATE.

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 are commands to control report headings & footers.

COLUMN command define column headings & format data values.

BREAK command clarify reports by suppressing repeated values, skipping lines & allowing for controlled break points.

command control computations on subsets created by the BREAK command.

SET command changes the system variables affecting the report environment.

SPOOL command creates a print file of the report.

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 a 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 is the product of two or more tables.

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

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

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 can be returned from a Nested Subquery.

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

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.

Where clause, Group By clause, Having clause, Order By clause & Select.

Transaction is defined as all changes made to the database between successive commits.

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 is an event that writes Inserts, Updates & Deletes in the forms to the database but not committing these transactions to the database.

Rollback causes work in the current transaction to be undone.

Savepoint is a point within a particular transaction to which you may rollback without rolling back the entire transaction.

Set Transaction is to establish properties for the current transaction.

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 the are throw with it.

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

CREATE TABLE - creates a new database table
ALTER TABLE - alters (changes) a database table
DROP TABLE - deletes a database table
CREATE INDEX - creates an index (search key)
DROP INDEX - deletes an index

= Equal
or != Not equal
> Greater than
< Less than
>= Greater than or equal
<= Less than or equal
BETWEEN an inclusive range
LIKE Search for a pattern

SELECT * FROM WITHOUTBOOK;

INSERT INTO WITHOUTBOOK(NAME,ADDRESS) VALUES('JOHN','GURGAON, INDIA');

UPDATE WITHOUTBOOK SET ADDRESS='KOLKATA, INDIA' WHERE NAME='JOHN';

DELETE FROM WITHOUTBOOK WHERE NAME='JOHN';

SELECT * FROM WITHOUTBOOK ORDER BY NAME ASC;
SELECT * FROM WITHOUTBOOK ORDER BY NAME DESC;

SELECT * FROM WITHOUTBOOK WHERE NAME IN(SELECT NAME FROM WITHOUTBOOK_BACK WHERE ADDRESS='KOLKATA, INDIA');

SELECT column_name FROM table_name WHERE column_name BETWEEN value1 AND value2 The values can be numbers, text, or dates.

When this clause is used with the DROP command, a parent table can be dropped even when a child table exists.

DROP TABLE &TABLE_NAME; Variable names should start with an alphabet. Here the table name starts with an '&' symbol.

USER_TAB_PRIVS_MADE, USER_TAB_PRIVS_RECD

USER_CONSTRAINTS.

TRUNCATE is a DDL command and cannot be rolled back. All of the memory space is released back to the server.
DELETE is a DML command and can be rolled back.

Both commands accomplish identical tasks (removing all data from a table), but TRUNCATE is much faster.

True.

True.

SELECT REPLACE(TRANSLATE(LTRIM(RTRIM('!! ATHEN !!','!'), '!'), 'AN', '**'),'*','TROUBLE') FROM DUAL;?
TROUBLETHETROUBLE.

This displays the total salary of all employees. The null values in the commission column will be replaced by 0 and added to salary.

The privilege receiver can further grant the privileges he/she has obtained from the owner to any other user.

START or @.

sal = 11000, comm = 1000.

RUN.

REVOKE.

NO.
Explanation : The query checks whether a given string is a numerical digit.

MONTHS_BETWEEN.

LIKE operator.

It is used to drop constraints specified on the table.

IS NULL operator.

Insert, update, delete, select, references, index, execute, alter, all.

FLOOR.

Data Definition Language (DDL).

DESC has two purposes. It is used to describe a schema as well as to retrieve rows from table in descending order.
Explanation :
The query SELECT * FROM EMP ORDER BY ENAME DESC will display the output sorted on ENAME in descending order.

CREATE TABLE .. AS SELECT command
Explanation:
To copy only the structure, the WHERE clause of the SELECT command should contain a FALSE statement as in the following.
CREATE TABLE NEWTABLE AS SELECT * FROM EXISTINGTABLE WHERE 1=2;
If the WHERE condition is true, then all the rows or rows satisfying the condition will be copied to the new table.

Both will result in deleting all the rows in the table EMP..  

1200.

_ for single character substitution and % for multi-character substitution.

&

SQL Injection is when form data contains an SQL escape sequence and injects a new SQL query to be run.

TRUNCATE commits after deleting entire table i.e., cannot be rolled back. Database triggers do not fire on TRUNCATE
DELETE allows the filtered deletion. Deleted records can be rolled back or committed. Database triggers fire on DELETE.

Join is a query, which retrieves related columns or rows from multiple tables.
Self Join - Joining the table with itself.
Equi Join - Joining two tables by equating two common columns.
Non-Equi Join - Joining two tables by equating two common columns.
Outer Join - Joining two tables in such a way that query can also retrieve rows that do not have corresponding join value in the other table.

Sub-query is a query whose return values are used in filtering conditions of the main query.

Correlated sub-query is a sub-query, which has reference to the main query.

Retrieves rows in hierarchical order eg.
select empno, ename from emp where.

INSTR (String1, String2 (n, (m)),
INSTR returns the position of the m-th occurrence of the string 2 in string1. The search begins from nth position of string1.
SUBSTR (String1 n, m)
SUBSTR returns a character string of size m in string1, starting from n-th position of string1.

INTERSECT - returns all distinct rows selected by both queries. MINUS - returns all distinct rows selected by the first query but not by the second. UNION - returns all distinct rows selected by either query UNION ALL - returns all rows selected by either query, including all duplicates.

ROWID is a pseudo column attached to each row of a table. It is 18 characters long, blockno, rownumber are the components of ROWID.

Using ROWID.
CONSTRAINTS

Integrity constraint is a rule that restricts values to a column in a table.

Maintaining data integrity through a set of rules that restrict the values of one or more columns of the tables based on the values of primary key or unique key of the referenced table.

SAVEPOINTS are used to subdivide a transaction into smaller parts. It enables rolling back part of a transaction. Maximum of five save points are allowed.

When ON DELETE CASCADE is specified Oracle maintains referential integrity by automatically removing dependent foreign key values if a referenced primary or unique key value is removed.

CHAR, VARCHAR2, NUMBER, DATE, RAW, LONG and LONG RAW.

CHAR pads blank spaces to the maximum length.
VARCHAR2 does not pad blank spaces.
For CHAR the maximum length is 255 and 2000 for VARCHAR2.

Only one LONG column is allowed. It is not possible to use LONG column in WHERE or ORDER BY clause.

To modify the datatype of a column the column must be empty.
- To add a column with NOT NULL constrain, the table must be empty.

The integrity constraints are stored in USER_CONSTRAINTS.

The integrity constraints can be enabled or disabled by ALTER TABLE ENABLE CONSTRAINT / DISABLE CONSTRAINT.

It won't, Because SYSDATE format contains time attached with it.

Database link is a named path through which a remote database can be accessed.

Sequence name CURRVAL, sequence name NEXTVAL. It is not possible. Only if you access next value in the session, current value can be accessed.

CYCLE specifies that the sequence continue to generate values after reaching either maximum or minimum value. After pan-ascending sequence reaches its maximum value, it generates its minimum value. After a descending sequence reaches its minimum, it generates its maximum.
NO CYCLE specifies that the sequence cannot generate more values after reaching its maximum or minimum value.

To protect some of the columns of a table from other users.
- To hide complexity of a query.
- To hide complexity of calculations.

A View can be updated/deleted/inserted if it has only one base table if the view is based on columns from one or more tables then insert, update and delete is not possible.

If changes are made to the tables and these tables are the base tables of a view, then the changes will be reference on the view.

1. Implicit cursors are used for SQL statements that are not named.
2. Developers should use implicit cursors with great care.
3. Implicit cursors are used in cursor for loops to handle data processing.
4. Implicit cursors are no longer a feature in Oracle.

1. Record type declaration.
2. Opening and parsing of SQL statements.
3. Fetches records from cursor.
4. Requires exit condition to be defined.

1. Use employee.lname%type.
2. Use employee.lname%rowtype.
3. Look up datatype for EMPLOYEE column on LASTNAME table and use that.
4. Declare it to be type LONG.

1. %found
2. %too_many_rows
3. %notfound
4. %rowcount
5. %rowtype

1. LOOP
2. END LOOP
3. IF-THEN
4. EXIT

1. cursor action_cursor is
2. select name, rate, action
3. into action_record
4. from action_table;
5. There are no errors in this statement.

1. open
2. fetch
3. parse
4. None, cursor for loops handle cursor opening implicitly.

The answer is: It loads the current row values into variables.

1. Altering the argument list of one of the package constructs
2. Any change made to one of the package constructs
3. Any SQL statement change made to one of the package constructs
4. Removing a local variable from the DECLARE section of one of the package constructs

1. When the transaction is committed
2. During the data manipulation statement
3. When an Oracle supplied package references the trigger
4. During a data manipulation statement and when the transaction is committed

1. DBMS_DISPLAY
2. DBMS_OUTPUT
3. DBMS_LIST
4. DBMS_DESCRIBE

1. Both the V_TOTAL_SEATS_SOLD_OVERALL variable and the GET_TOTAL_FOR_YEAR function must exist only in the body of the THEATER_PCK package.
2. Only the GET_TOTAL_FOR_YEAR variable must exist in the specification of the THEATER_PCK package.
3. Only the V_TOTAL_SEATS_SOLD_OVERALL variable must exist in the specification of the THEATER_PCK package.
4. Both the V_TOTAL_SEATS_SOLD_OVERALL variable and the GET_TOTAL_FOR_YEAR function must exist in the specification of the THEATER_PCK package.

1. DBMS_DDL
2. DBMS_DML
3. DBMS_SYN
4. DBMS_SQL

Method 1:

Select length (translate (trim (column_name),' +-.0123456789',' ')) from dual ;

Will give you a zero if it is a number or greater than zero if not numeric (actually gives the count of non numeric characters)

Method 2:

select instr(translate('wwww',
'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'),'X')
FROM dual;

It returns 0 if it is a number, 1 if it is not.

select * from (select rownum a, CLASS_CODE,CLASS_DESC from clm)
where a > ( select (max(rownum)-10) from clm)

Here N = 10

The following query has a Problem of performance in the execution of the following query where the table ter.ter_master have 22231 records. So the results are obtained after hours.

Cursor rem_master(brepno VARCHAR2) IS
select a.* from ter.ter_master a
where NOT a.repno in (select repno from ermast) and
(brepno = 'ALL' or a.repno > brepno)
Order by a.repno

Have an index on TER_MASTER.REPNO and one on ERMAST.REPNO

-Be sure to get familiar with EXPLAIN PLAN. This can help you determine the execution path that Oracle takes. If you are using Cost Based Optimizer mode, then be sure that your statistics on TER_MASTER are up-to-date. -Also, you can change your SQL to:

SELECT a.*
FROM ter.ter_master a
WHERE NOT EXISTS (SELECT b.repno FROM ermast b
WHERE a.repno=b.repno) AND
(a.brepno = 'ALL' or a.repno > a.brepno)
ORDER BY a.repno;

DELETE removes one or more records in a table, checking referential Constraints (to see if there are dependent child records) and firing any DELETE triggers. In the order you are deleting (child first then parent) There will be no problems.
TRUNCATE removes ALL records in a table. It does not execute any triggers. Also, it only checks for the existence (and status) of another foreign key Pointing to the table. If one exists and is enabled, then you will get The following error. This is true even if you do the child tables first.
ORA-02266: unique/primary keys in table referenced by enabled foreign keys
You should disable the foreign key constraints in the child tables before issuing the TRUNCATE command, then re-enable them afterwards.

Preemptive refers to the fact that each task is alloted fixed time slots and at the end of that time slot the next task is started.

OLTP stands for On Line Transaction Processing

OLTP requires real time response.

The client deals with the user interface part of the system.

To reduce the network traffic and for application sharing and implementing business rules.

Applications that have outgrown their environment are re-engineered to run in a larger environment. This is upsizing.

With rightsizing, one would move applications to the most appropriate server platforms.

A host based application is re-engineered to run in smaller or LAN based environment.

An event trigger, a segment of code which is associated with each event and is fired when the event occurs.

When a stored procedure is called, only the procedure call is sent to the server and not the statements that the procedure contains.

Foreground process and Background process.

An event handler is a routine that is written to respond to a particular event.

An integrity constraint allows the definition of certain restrictions, at the table level, on the data that is entered into a table.

Database triggers can be used to enforce business rules, to maintain derived values and perform value-based auditing.

A transaction is a set of operations that begin when the first DML is issued and end when a commit or rollback is issued. BEGIN COMMIT/ROLLBACK are the boundries of a transaction.

Because it is easier to define an integrity constraint than a database trigger.

Because an integrity constraint is automatically checked while data is inserted into a table. A stored has to be specifically invoked.

A Client,
A Server and
A Network/Communication software.

Flexibility of the system, scalability, cost saving, centralised control and implementation of business rules, increase of developers productivity, portability, improved network and resource utilization.

Heterogeneity of the system results in reduced reliability. May not be suitable for all applications. Managing and tuning networks becomes difficult.

Star,
Bus,
Ring.

A client process at first establishes connection with the Server.

1. Manage resources optimally across multiple clients.
2. Controlling database access and security.
3. Protecting the database and recovering it from crashes.
4. Enforcing integrity rules globally.

An API, in a Client/Server context, is a specification of a set of functions for communication between the client and the server.

Open Database Connectivity (ODBC),
Integrated Database Application Programming Interface (IDAPI),
XOpen
SQL/CLI

The application can be connected to any back end server that is supported by the API.

The application cannot use any special features of the backend server.

Because an event driven program is always waiting for something to happen before processing.

1. System Events.
2. Control Events
3. User Events
4. Other Events.

A file server just transfers all the data requested by all its client and the client processes the data while a database server runs the query and sends only the query output.

Inheritance is a method by which properties and methods of an existing object are automatically passed to any object derived from it.

1. An ODBC manager/administrator and
2. ODBC driver.

The ODBC Manager manages all the data sources that exists in the system.

The ODBC Driver allows the developer to talk to the back end database.

The name of the DBMS, the location of the source and the database dependent information.

A stored procedure is a set of statements/commands which reside in the database. The stored procedure is pre-compiled and saves the database the effort of parsing and compiling sql statements every time a query is run. Each database has its own stored procedure language, usually a variant of C with a SQL preproceesor. Newer versions of db's support writing stored procedures in Java and Perl too. Before the advent of 3-tier/n-tier architecture it was pretty common for stored procs to implement the business logic( A lot of systems still do it). The biggest advantage is of course speed. Also certain kind of data manipulations are not achieved in SQL. Stored procs provide a mechanism to do these manipulations. Stored procs are also useful when you want to do Batch updates/exports/houseKeeping kind of stuff on the db. The overhead of a JDBC Connection may be significant in these cases.

SQLWarning objects are a subclass of SQLException that deal with database access warnings. Warnings do not stop the execution of an application, as exceptions do; they simply alert the user that something did not happen as planned. A warning can be reported on a Connection object, a Statement object (including PreparedStatement and CallableStatement objects), or a ResultSet object. Each of these classes has a getWarnings method, which you must invoke in order to see the first warning reported on the calling object




SQLWarning warning = stmt.getWarnings();

if (warning != null)

{

while (warning != null)

{

System.out.println("Message: " + warning.getMessage());

System.out.println("SQLState: " + warning.getSQLState());

System.out.print("Vendor error code: ");

System.out.println(warning.getErrorCode());

warning = warning.getNextWarning();

}

}