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 system variables.
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 &
Date Functions are ADD_MONTHS, LAST_DAY, NEXT_DAY, MONTHS_BETWEEN &
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 & SUM.
TTITLE & BTITLE
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 break points.
command control computations on subsets created by the BREAK command.
SET command changes the system variables affecting the report
SPOOL command creates a print file of the report.
JOIN is the form of SELECT command that combines info from two or more
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 more tables.
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 statement.
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 and Convenience.
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
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.