|
Sql Interview Questions and Answers
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.
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
Consistency : Assures users that the data they are changing or viewing
is not changed until the 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 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 <options>.
The most important DDL statements in SQL are:
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
Page Numbers
:
1
2
3
4
5
6
7
8
9
10
11
Have a Question ?
post your questions here. It
will be answered as soon as possible.
Check
HTML Interview
Questions for more HTML Interview Questions with Answers
Check
Job Interview Questions
for more Interview Questions with Answers
|
