|
"If your success is
not on your own terms, if it looks good to the world but does
not feel good in your heart, it is not success at all." Anna
Quindlen |
IMPORTANT-READ CAREFULLY
The following are important
definitions to remember:
"DML" stands for
Data Manipulation Language. SELECT, UPDATE, INSERT, and DELETE are the
"DML" statements.
A "SELECT"
statement must consist of a "SELECT" and a "FROM"
clause.
A Mathematical Operation
can be performed on the "SELECT" statement.
The "DUAL" table
would be used when a user does not want to pull data from a table but
rather wants simply to use an arithmetic operation. It contains only
one row and one column.
The "NULL" value
will be used when you don't know the value of the column. Remember
that the Null value means "I don't know;" and any
mathematical operation on the null value will return a null result.
The null value function (NVL)
can convert a null value an assigned value.
A "column
heading" can be used in place of the actual column name. If your
column heading is case sensitive, a reserved word, or contains white
space, it must be enclosed within double quotes.
A "table alias"
can be used in place of the actual table name to make a column a
unique identifier.
Two or more columns or
strings can be concatenated using a double-pipe.
The "ORDER BY"
clause in a select statement will sort the order of a listed table.
The "WHERE"
clause can contain comparison operations linked together.
The "LIKE" clause
can be used for pattern matching.
The "BETWEEN"
clause would be used for a range operation.
The "DECODE"
function will match the column values with appropriate return values.
It continues matching until it has identified all cases. The last
variable is used for the default return value.
A "JOIN" table
is: when a query obtains data from more than one table and merges the
data together. You may join tables together using "inner
join" or "equijoin", "outer join", and
"self join".
"inner join" also
known as equijoin is an equality operation linking the data in the
common columns.
"outer join"
returns data in one table even when there is no match in the other
table.
A "self join" is
based on an equality operation linking the data to itself.
A "Cartesian"
product" is caused by joining "N" number of tables
while you have less than "N-1" join conditions in the query.
An "Anonymous
Column" is caused by joining two tables when they have a common
column name in them. You can use table aliases or table names next to
a column name to avoid causing the "anonymous column."
The "GROUP BY"
clause will assist you in grouping data together.
The "EXISTS"
operation produces a "TRUE" or "FALSE" value based
on the related sub-query data output. You may use the global column
name in your sub-query.
The "IN"
operation produces a "TRUE" or "FALSE" value based
on the related sub-query data output or list of values.
"MIN,"
"MAX," and "SUM" are grouping functions that allow
you to perform operations on data in a column.
You can assign a variable
in a "SELECT" statement at run time with use of a runtime
variable.
A Table is a collection of
records.
Use the
"VARCHAR2" datatype when your input data string varies and
does not exceed more than 2000 characters.
Use the "CHAR"
datatype when your input data string is fixed and does not exceed more
than 2000 characters.
If your input data is
number, use the "NUMBER" datatype.
The "DATE"
datatype should be used when your input data is "date",
"time", or "date and time".
The "RAW"
datatype should be used when your input data contains binary data and
does not exceed more than 2000 bytes.
If your input data contains
text data and does not exceed more than 2 gig, use the
"LONG" datatype.
The "LONG RAW"
datatype is used if your input data is binary and does not exceed more
than 2 Gig.
Use the "ROWID"
datatype when your application references to the "rowid" of
a table.
The "BLOB"
(Binary Large Object) datatype would be used for binary long objects
and can store up to 4 gig.
Use the "CLOB"
(Character Large Object) datatype if you have to store a book in a
column. Its size should not exceed more than 4 gig. Try to use "CLOB"
instead of the "LONG" datatype. It is searchable; also more
than one column can be defined as Large Object in a table.
The "BFILE"
datatype would be used for the large external files. The content of
this column points to system files.
The DATA DICTIONARY is a
repository of all the database objects that were created by different
schemas.
All the information about
the database objects is stored in the data dictionary. You will
retrieve the data dictionary information using the data dictionary
views.
DDL" stands for Data
Definition Language. CREATE TABLE, CREATE USER, DROP TABLE, ALTER
TABLE are examples of the DDL statements.
The "ALTER"
command changes an object.
The "DROP"
command removes an object.
The "TRUNCATE" or
"DELETE" command removes records from an object. When you
use the truncate statement, the "high watermark" will change
to the beginning of the table. The truncate statement is a "DDL"
statement; and on all DDL statements, the commit is implicit. That is
the reason that you can not rollback on the truncate statement. Also,
when a table is removed all its indexes, constraints, and references
will be removed as well.
The Oracle9i ANSI standard
JOIN syntax
You can use Oracle9i ANSI
standard JOIN syntax to join the contents of two or more tables
together in a single result according to the following syntax.
Syntax:
SELECT col1, col2
FROM table1 JOIN table2
ON condition;
The ANSI standard NATURAL
JOIN syntax
A natural join is a join
between two or more tables where Oracle joins the tables according to
the column(s) in the two or more tables sharing the same name with the
following syntax.
Syntax:
SELECT col1, col2
FROM table1 NATURAL JOIN
table2;
The USING clause
You can use Oracle9i ANSI
standard JOIN syntax to join the contents of two or more tables
together in a single result according to the columns in the two tables
sharing the same name and be used in the USING clause with the
following syntax.
Syntax:
SELECT col1, col2
FROM table1 JOIN table2
USING (col);
The ANSI standard CROSS
JOIN syntax
A cross-join is produced
when you use the CROSS keyword in your ANSI/ISO -compliant join query.
You use it when you want to retrieve a Cartesian product.
Syntax:
SELECT col1, col2
FROM table1 CROSS JOIN
table2;
The OUTER JOIN clause
A OUTER JOIN is a join
between two tables where you want to see information from tables even
when no corresponding records exist in the common column. You can have
RIGHT OUTER JOIN, LEFT OUTER JOIN, and FULL OUTER JOIN.
Syntax:
SELECT col1, col2
FROM table1 RIGHT/LEFT/FULL
OUTER JOIN table2;
RIGHT OUTER JOIN
A RIGHT OUTER JOIN is a
join between two tables where you want to see information from table
on the right side even when no corresponding records exist in the
common column.
Syntax:
SELECT col1, col2
FROM table1 RIGHT OUTER
JOIN table2;
LEFT OUTER JOIN
A LEFT OUTER JOIN is a join
between two tables where you want to see information from table on the
left side even when no corresponding records exist in the common
column.
Syntax:
SELECT col1, col2
FROM table1 LEFT OUTER JOIN
table2;
FULL OUTER JOIN
A FULL OUTER JOIN is a join
between two tables where you want to see information from both tables
on the left and right sides even when no corresponding records exist
in the common column.
Syntax:
SELECT col1, col2
FROM table1 FULL OUTER JOIN
table2;
The (WITH name AS)
statement
Oracle9i provides you with
the WITH clause that lets you factor out the sub-query, give it a
name, then reference that name multiple times within the original
complex query.
The (inline view)
A sub-query that appears in
the FROM clause is called an inline view. You must enclose the query
text for the inline view in parentheses and also give a label for the
inline view so that columns in it can be referenced later.
The MERGE statement
Oracle9i provides you with
the MERGE statement so that you can identify a table into which you
would like to update data in an existing row or add new data if the
row does not already exist.
Materialized View
Unlike an ordinary view,
which only contains an SQL statement, a materialized view contains the
rows of data resulting from an SQL query against one or more base
tables. The materialized view can be set up to automatically keep
itself in synch with those base tables.
Materialized view log
Whenever a change is made
to one of the underlying base tables; the database stores a log on
each change.
Using DBMS_SNAPSHOT package
You can use the REFRESH
procedure of the DBMS_SNAPSHOT package to refresh periodically a
snapshot manually.
UNION
The UNION set operator
combines the results of two queries into a single result with no
record duplication.
INTERSECT
The INTERSECT set operator
returns all the rows contained in both tables.
MINUS
The MINUS set operator
returns all the rows in one table minus the rows contained in other
table.
Tablespace
A tablespace is a logical
database structure that is designed to store other logical database
structures. Oracle sees a tablespace as a large area of space into
which Oracle can place new objects. Space in tablespace is allocated
in segments.
Partitioned table
Partitioned tables are just
like regular tables except for an important small feature-they enable
you to reference the individual segments that might support larger
tables directly.
The ROLLUP function
It is simple extension to
the SELECT statement"s GROUP BY clause. It creates subtotals at any
level of aggregation needed, from the most detailed up to a grand
total.
The CUBE function
It is simple extension to
the SELECT statement"s GROUP BY clause. It calculates subtotals for
all the combinations of a group of dimensions. It also calculates a
grand total.
JAVA
JAVA is a language that is
portable, due to the fact that it is run within your environment. This
environment may be a web browser, a database, or an application
server.
Nested Table
If an object is in an
object, it is a nested object. A nested table is a table that is
nested in another table.
The "THE" sub-query
The "THE"
sub-query is used to identify the nested table to insert into. Note
only one row may be inserted into the nested table at once using this
method-as would be the case if you were inserting into any table.
The REF Cursor
REF cursors hold cursors in
the same way that VARCHAR2 variables hold strings. This is an added
feature that comes with PL/SQL v2.2. A REF cursor allows a cursor to
be opened on the server and passed to the client as a unit rather than
one row at a time. One can use a Ref cursor as a target of assignments
and can be passed as parameters to the Program Units. Ref cursors are
opened with an OPEN FOR statement and in all other ways, they are the
same as regular cursors.
Table of Records
A table of records is a new
feature added in PL/SQL v2.3. It is the equivalent of a database table
in memory. If you structure the PL/SQL table of records with a primary
key (an index) you can have array-like access to the rows.
Table of records differ
from arrays in that they are not bound by a fixed lower or higher
limit. Nor do they require consecutive index numbers as arrays do.
Consult a PL/SQL reference manual (version 2.3 or higher) for further
explanation. There are three steps involved in creating a table of
records.
Oracle Server is an
object-relational database management system that provides an open,
comprehensive, and integrated approach to information management. It
consists of an Oracle database and instance.
The Oracle Database
contains all user data information. It has a physical and a logical
structure.
The "Physical Structure"
is determined by the operating system files; such as data files,
control files, and parameter files.
The "Logical Structure"
is determined by the Oracle Database, such as tablespaces, tables, and
segments.
An instance is a
combination of Oracle background processes and memory buffers.
The memory buffer is called
System Global Area (SGA) and is shared by the database users. Every
time a database is started the system global area is allocated and
Oracle background processes are started.
Oracle Background Processes
Server tasks between memory and disk.
A user establishes
connection and then requests information using a client application
from the Oracle Server.
The Listener process waits
for connection requests from a client application, and routes each
client to a server process.
Server processes are
created on behalf of each user"s application to read a user request
and return the results.
The TNSNAMES.ORA and
SQLNET.ORA files establish client session connectivity to a server
using the local naming option.
|
"Of course there is
no formula for success except perhaps an unconditional
acceptance of life and what it brings." Arthur Rubinstein
(1886 - 1982) |
General Questions:
Q: What does the DML stand
for?
Q: What are the examples of
the DML statement?
Q: What should a "SELECT"
statement consist of?
Q: Can you perform a
mathematical operation on a "SELECT" statement?
Q: What is the DUAL table?
Q: When do you use the DUAL
table?
Q: What does the DUAL table
contain?
Q: What is the NULL value?
Q: What is the result of
100+NULL?
Q: What does the NVL
function?
Q: What is a COLUMN
HEADING?
Q: What is a TABLE ALIAS?
Q: How can you concatenate
two columns or strings?
Q: What does the LIKE
clause?
Q: What does the DECODE
function?
Q: How many different types
of JOIN table do you know?
Q: What is an inner join
table?
Q: What is an outer join
table?
Q: What is an equi-join
table?
Q: What is the difference
between an inner join table and an outer join table?
Q: What is a SELF JOIN
table?
Q: What is the Cartesian
product?
Q: How can you avoid having
a Cartesian product?
Q: What is an anonymous
column?
Q: How can you avoid having
an anonymous column?
Q: When do you use the
GROUP BY clause?
Q: What does the EXISTS
operation produce?
Q: What does the IN
operation produce?
Q: What is a runtime
variable?
Q: What is a table?
Q: What is the difference
between a VARCHAR2 datatype and a CHAR datatype?
Q: What is the difference
between a DATE and NUMBER datatypes?
Q: How does a DATE store in
the Oracle database?
Q: What is the difference
between a LONG RAW and BLOB datatypes?
Q: What is the difference
between a LONG and CLOB datatypes?
Q: What is a ROWID of a
record?
Q: What is the BFILE
datatype?
Q: What is a data
dictionary in the Oracle database?
Q: What type of data store
in a data dictionary?
Q: What is a data
dictionary view?
Q: What is DDL?
Q: What does DDL stand for?
Q: What are the differences
between a TRUNCATE and DELETE commands?
Q: What is a high watermark
in the Oracle table?
Q: What is implicit in a
DDL statement?
Q: What is the Oracle9i
ANSI?
Q: What are the differences
between the NATURAL JOIN and JOIN syntaxes?
Q: When do you use the
USING clause in the Oracle ANSI database?
Q: Write and describe all
different types of JOIN in the Oracle ANSI statements?
Q: What is an inline view
in the Oracle database?
Q: What is the materialized
view?
Q: What is the view?
Q: When do you use the
DBMS_SNAPSHOT package?
Q: What is the materialized
view log?
Q: Describe the UNION,
INTERSET, and MINUS set operators in the Oracle SELECT statement?
Q: What is a tablespace in
the Oracle database?
Q: What is a partitioned
table in the Oracle database?
Q: When do you use a
partitioned table in the Oracle database?
Q: What are the differences
between Oracle table and Oracle partitioned table?
Q: What is the ROLLUP
function?
Q: What is the CUBE
function?
Q: What is the Nested Table
in the Oracle database?
Q: What is the logical
structure in the Oracle database?
Q: What is the physical
structure in the Oracle database?
Q: What is an instance in
the Oracle database?
Q: What is SGA?
Q: What are the Oracle
Background Processes?
Q: What is the listener
process in the Oracle database?
|