: SQL Study Notes
SQL is an English like
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
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
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
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
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
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
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
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
Mslabel Binary format of an OS label. Used primarily with Trusted Oracle.
of SQL statement execution
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
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 thro?
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
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
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.