SQL

TRUNCATE is a DDL command whereas DELETE is a DML command. Hence DELETE operation can be rolled back, but TRUNCATE operation cannot be rolled back. WHERE clause can be used with DELETE and not with TRUNCATE.

SELECT SAL + NVL(COMM,0) FROM EMP;?

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

True.

START or @.

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.

FLOOR.

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

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

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.

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

CHAR,

VARCHAR2,

NUMBER,

DATE,

RAW,

LONG and

LONG RAW.

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.

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

The integrity constraints are stored in USER_CONSTRAINTS.

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

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.

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.

1. Record type declaration.

2. Opening and parsing of SQL statements.

3. Fetches records from cursor.

4. Requires exit condition to be defined.

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