|
More Resources by
Google: |
|
|
|
|
IMPORTANT-READ
CAREFULLY
Before going
through the Hands-On SQL experiences, lets take note of the following important
definitions to remember:
-
"DML" stands for Data
Manipulation Language. SELECT,
UPDATE, INSERT, and DELETE are the "DML" statements.
-
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 is a collection of
records. In this Hands-On you
will learn how to: create a table; column constraints; primary key, foreign
keys, unique key, and indexes.
-
Use the "CHAR" datatype
when your input data string is fixed and does not exceed more than 2000
characters.
-
When your input data is number,
use the "NUMBER" datatyp.
-
DDL" stands for Data
Definition Language. CREATE
TABLE, CREATE USER, DROP TABLE, ALTER TABLE are examples of the DDL
statements.
-
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.
We strongly advise
you before writing any "SQL" statement, to study first your table and
have a good understanding of the table's attributes and its data structure.
Now, let's have a
good look at the employee table. We
abbreviated the employee table to "EMP."
The columns in this table are: Employee
number, name, job, manager's id, hire date, salary, commission, and department
number.
Since this table
does not contain any records, let's insert some records into it and analyze its
data.
Notice that the
manager id column references to the employee number.
A manager is an employee of his/her company.
On this type of table, you can establish a "selfjoin"
condition.
Before writing any
query spend time to understand the structure of the table and its data.
A table contains information that describes an entity. It has ROWS and COLUMNS.
A row is a
collection of information about a sub-entity. Here, for example, this table
contains the company's employee information.
A table may have a
primary key. In this table, the first column (employee number) is a
primary key. A primary key is a
unique identifier for each individual employee.
A table can have a foreign key. Here,
the last column (department number) is a foreign key.
A foreign key of a table always references to a primary key of another
table. In this table, the foreign
key references to the primary key of the department table.
A table can have unique keys, composite keys, and index keys.
Avoid having too many indexes in a table.
Notice, the
commission column and manager id column have "null values."
The employee number should not have a "null value", since it is
a primary key. Notice that the
manager id column refers to the employee number in the employee table.
Once you understand
the nature of your data in a table, you are ready to write a good query against
that table.
Now, you should
first read your case study, and try to solve the questions.
Then play the Hands-On training movie until you become familiar with the
subject. For more information about
the subject, you are encouraged to read from a wide selection of available
books.
Good Luck. |