Pl/Sql Interview Questions Level 1

What are the types of SQL Statement ?

SQL statements are divided into five different categories:

Data definition language (DDL) : CREATE, ALTER, DROP, TRUNCATE, RENAME

Data manipulation language (DML) : SELECT, INSERT, UPDATE, DELETE, MERGE

Data Control Language (DCL) : GRANT, REVOKE, ANALYZE, AUDIT, COMMENT

Transaction Control Statement : COMMIT, ROLLBACK, SAVEPOINT, SET TRANSACTION

Session Control Statements (SCS) : ALTER SESSION, SET ROLE

Difference between DDL & DML ?

DML require commit but DDL do not require it.

What is Key and Significance of keys ?

A key is the column or set of columns included in the definition of certain types of integrity constraints. Key allow us to define rules at the column or object level that restrict values in the database.

What is the difference between primary key and unique key ?

A primary key is a column or combination of columns that uniquely defines a row. None of the columns that are part of the primary key can contain a null value. A table can have only one primary key.

A unique key is a column or combination of columns that uniquely defines a row. Some of the columns that are part of the unique key can contain null values as long as the combination of values is unique. A table can have more than one unique key.

Oracle does not permit you to create both a primary key and unique constraint with the same columns.

What is referential integrity ?

Oracle Database can enforce the relationship between the two tables through a foreign key constraint, also called a referential integrity constraint. The constraint requires that for each value in the column on which the constraint is defined, the value in the other specified other table and column must match.

Can we have null in a foreign key ? If yes, how many in a table.

What is delete CASCADE ?

When delete CASCADE used then if a record in the parent table is deleted, then the corresponding records in the child table will automatically be deleted.

What is the use of CASCADE CONSTRAINTS ?

When this clause is used with DROP command, it first deletes all foreign keys that reference the table to be dropped, then drops the table. It is useful when a table need to be dropped even when a child table exists.

What is Union, Intersect, Minus ?

These are Set operators which combine the results of two component queries into a single result.

UNION : All distinct rows selected by either query

INTERSECT : All distinct rows selected by both queries

MINUS : All distinct rows selected by the first query but not the second

What is the difference between UNION and UNION ALL ?

UNION eliminates duplicate rows while UNION ALL include duplicate rows. UNION ALL is faster than UNION.

What are the uses of MERGE ?

MERGE is used to combine multiple DML statements into one.

Syntax : merge into tablename

using(query)

on(join condition)

when not matched then

[insert/update/delete] command

when matched then

[insert/update/delete] command

What is join ? Explain the different types of joins ?

A join is a query that combines rows from two or more tables, views, or materialized views with at least one join condition, either in the FROM clause or in the WHERE clause.

Types of joins are :

• Cartesian Products
• Equijoins
• Self Joins
• Inner Join
• Outer Joins : Left, Right, Full
• Antijoins
• Semijoins

What are the various types of queries ?

Nested subquery

Correlated subquery

Hierarchical Queries

Distributed Queries

What is the difference between nested subquery and correlated Subquery ?

A subquery is nested when you are having a subquery in the where or having clause of another subquery. The innermost subquery will be executed first and then based on its result the next subquery will be executed and based on that result the outer query will be executed.

A Correlated Subquery is one that is executed after the outer query is executed.

The correlated subquery execution is as follows:

-The outer query receives a row.

-For each candidate row of the outer query, the subquery (the correlated subquery) is executed once.

-The results of the correlated subquery are used to determine whether the candidate row should be part of the result set.

-The process is repeated for all rows.

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

• WHERE clause can be used with SELECT,UPDATE and DELETE but HAVING clause can only be used with SELECT query.
• condition specified in WHERE clause is applied on each rows of a table to get filtered data and on the other hand HAVING clause applied later to filter grouped data.
• WHERE clause will perform index scan on table and HAVING will not.

What is the difference between ANY and ALL conditions ?

ANY use OR operator to compare each condition while ALL use AND operator.

What is the difference between delete, drop and truncate operation on a table ?

• Delete removes rows from a table. WHERE condition can be given with delete. Commit or Rollback required after delete. DML operation. Delete Trigger will execute.
• Truncate removes all rows. No WHERE condition. DDL operation so No Commit or Rollback required. No trigger execute. Faster than Delete.
• Drop removes table from database. DDL operation.

What is the order of Query Execution in SQL ?

1) From Clause

2) Where Clause

3) Group By Clause

4) Having Clause

5) Select

6) Order By Clause