Set - 2

Question 1 :

Rollback

Answer :

Rollback causes work in the current transaction to be undone.


Question 2 :

Savepoint

Answer :

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


Question 3 :

Set Transaction

Answer :

Set Transaction is to establish properties for the current transaction.


Question 4 :

Locking

Answer :

Locking are mechanisms intended to prevent destructive interaction between users accessing data. Locks are used to achieve.


Question 5 :

Consistency

Answer :

Consistency : Assures users that the data they are changing or viewing is not changed until the are thro' with it.


Question 6 :

Integrity

Answer :

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.


Question 7 :

Deadlock

Answer :

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.


Question 8 :

Mutating Table

Answer :

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.


Question 9 :

SQL*Loader

Answer :

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 .


Question 10 :

The most important DDL statements in SQL are?

Answer :

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


Question 11 :

Operators used in SELECT statements.

Answer :

= 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


Question 12 :

SELECT statements:

Answer :

SELECT column_name(s) FROM table_name
SELECT DISTINCT column_name(s) FROM table_name
SELECT column FROM table WHERE column operator value
SELECT column FROM table WHERE column LIKE pattern
SELECT column,SUM(column) FROM table GROUP BY column
SELECT column,SUM(column) FROM table GROUP BY column HAVING SUM(column) condition value
Note that single quotes around text values and numeric values should not be enclosed in quotes. Double quotes may be acceptable in some databases.


Question 13 :

The SELECT INTO Statement is most often used to create backup copies of tables or for archiving records?

Answer :

SELECT column_name(s) INTO newtable [IN externaldatabase] FROM source
SELECT column_name(s) INTO newtable [IN externaldatabase] FROM source WHERE column_name operator value


Question 14 :

The INSERT INTO Statements:

Answer :

INSERT INTO table_name VALUES (value1, value2,....)
INSERT INTO table_name (column1, column2,...) VALUES (value1, value2,....)


Question 15 :

The Update Statement:

Answer :

UPDATE table_name SET column_name = new_value WHERE column_name = some_value


Question 16 :

The Delete Statements:

Answer :

DELETE FROM table_name WHERE column_name = some_value
Delete All Rows:
DELETE FROM table_name or DELETE * FROM table_name


Question 17 :

Sort the Rows:

Answer :

SELECT column1, column2, ... FROM table_name ORDER BY columnX, columnY, ..
SELECT column1, column2, ... FROM table_name ORDER BY columnX DESC
SELECT column1, column2, ... FROM table_name ORDER BY columnX DESC, columnY ASC


Question 18 :

The IN operator may be used if you know the exact value you want to return for at least one of the columns.

Answer :

SELECT column_name FROM table_name WHERE column_name IN (value1,value2,..)


Question 19 :

BETWEEN ... AND

Answer :

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


Question 20 :

What is the use of CASCADE CONSTRAINTS?

Answer :

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


Question 21 :

Why does the following command give a compilation error?

Answer :

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


Question 22 :

Which system tables contain information on privileges granted and privileges obtained?

Answer :

USER_TAB_PRIVS_MADE, USER_TAB_PRIVS_RECD


Question 23 :

Which system table contains information on constraints on all the tables created?obtained?

Answer :

USER_CONSTRAINTS.


Question 24 :

What is the difference between TRUNCATE and DELETE commands?

Answer :

True


Question 25 :

State true or false. !=, <>, ^= all denote the same operation?

Answer :

True.