iSelfSchooling - Manuscript of SQL (Introduction 02)

“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. In this Hands-On you will learn how to: create a table; column constraints; primary key, foreign keys, unique key, and indexes.
Oracle supports many datatypes. It is very important to have a good understanding of each one.
- 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.
- When your input data is number, use the "NUMBER" datatyp.
- 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.

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.