Teach Yourself SQL 2nd Edition By Ronald and Stephens

Just now i completed reading Teach Yourself SQL 2nd Edition By Ronald and Stephens.

Nice book written by Ronald R. Plew and Ryan K. Stephens.

I want to share few quotations i found from this book.

1) What Is SQL?

SQL, Structured Query Language, is the standard language used to communicate with a relational database.The prototype was originally developed by IBM using Dr. E.F. Codd's paper ("A Relational Model of Data for Large Shared Data Banks") as a model. In 1979, not long after IBM's prototype, the first SQL product,ORACLE, was released by Relational Software, Incorporated (it was later renamed Oracle Corporation). It is,today, one of the distinguished leaders in relational database technologies. SQL is pronounced either of two ways: as the letters S-Q-L, or as "sequel"; both pronunciations are acceptable.

2) What Is a Database?

In very simple terms, a database is a collection of data. Some like to think of a database as an organized mechanism that has the capability of storing information, through which a user can retrieve stored information in an effective and efficient manner.

3) An Introduction to the Relational Database

A relational database is a database divided into logical units called tables, where tables are related to one another within the database. A relational database allows data to be broken down into logical,smaller, more manageable units, allowing for easier maintenance and providing more optimal database performance according to the level of organization.

4) Defining Database Structures (DDL)

Data Definition Language, DDL, is the part of SQL that allows a database user to create and restructure database objects, such as the creation or the deletion of a table.The main DDL commands discussed during following hours include the following:

CREATE TABLE
ALTER TABLE
DROP TABLE
CREATE INDEX
ALTER INDEX
DROP INDEX

5) Manipulating Data (DML)

Data Manipulation Language, DML, is the part of SQL used to manipulate data within objects of a relational database.

There are three basic DML commands:

INSERT
UPDATE
DELETE

6) Selecting Data (DQL)

Though comprised of only one command, Data Query Language (DQL) is the most concentrated focus of SQL for a relational database user. The command is as follows:

SELECT

This command, accompanied by many options and clauses, is used to compose queries against a relational database. Queries, from simple to complex, from vague to specific, can be easily created

7) Data Control Language (DCL)

Data control commands in SQL allow you to control access to data within the database. These DCL commands are normally used to create objects related to user access and also control the distribution of privileges among users. Some data control commands are as follows:

ALTER PASSWORD
GRANT
REVOKE
CREATE SYNONYM

8) What Is a Schema?

New Term A schema is a collection of database objects (as far as this hour is concerned—tables) associated with one particular database username. This username is called the schema owner, or the owner of the related group of objects. You may have one or multiple schemas in a database. Basically, any user who creates an object has just created his or her own schema. A schema can consist of a single table and has no limits to the number of objects that it may contain, unless restricted by a specific database implementation.

9) Normal form is a way of measuring the levels, or depth, to which a database has been normalized. A database's level of normalization is determined by the normal form. The following are the three most common normal forms in the normalization process:

a) The first normal form
b) The second normal form
c) The third normal form

10) Normalization provides numerous benefits to a database. Some of the major benefits include the following:

a) Greater overall database organization
b) Reduction of redundant data
c) Data consistency within the database
d) A much more flexible database design
e) A better handle on database security

11) A transaction is a unit of work that is performed against a database. Transactions are units or sequences of work accomplished in a logical order, whether in a manual fashion by a user or automatically by some sort of a database program.

12) What Are Aggregate Functions?

Functions are keywords in SQL used to manipulate values within columns for output purposes. A function is a command always used in conjunction with a column name or expression. There are several types of functions in SQL.An aggregate function is used to provide summarization information for an SQL statement, such as counts, totals, and averages.The aggregate functions

COUNT
SUM
MAX
MIN
AVG

13) The HAVING Clause

The HAVING clause, when used in conjunction with the GROUP BY in a SELECT statement, tells GROUP BY which groups to include in the output. HAVING is to GROUP BY as WHERE is to SELECT. In other words, the WHERE clause places conditions on the selected columns, whereas the HAVING clause places conditions on groups created by the GROUP BY clause.

The following is the position of the HAVING clause in a query:
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY

The HAVING clause must follow the GROUP BY clause in a query and must also precede the ORDER BY clause if used.

The following is the syntax of the SELECT statement, including the HAVING clause:

SELECT COLUMN1, COLUMN2
FROM TABLE1, TABLE2
WHERE CONDITIONS
GROUP BY COLUMN1, COLUMN2
HAVING CONDITIONS
ORDER BY COLUMN1, COLUMN2

14) Types of Joins

While different implementations have many ways of joining tables, you concentrate on the most common joins in this lesson. The types of joins that you learn are

EQUIJOINS
NATURAL JOINS
NON-EQUIJOINS
OUTER JOINS
SELF JOINS

15) Outer Joins

An OUTER JOIN is used to return all rows that exist in one table, even though corresponding rows do not exist in the joined table. The (+) symbol is used to denote an OUTER JOIN in a query. The (+) is placed at the end of the table name in the WHERE clause. The table with the (+) should be the table that does not have
matching rows. In many implementations, the OUTER JOIN is broken down into joins called LEFT OUTER JOIN, RIGHT OUTER JOIN, and FULL OUTER JOIN. The OUTER JOIN in these implementations is normally optional.

16) What Is a Subquery?
A subquery is a query embedded within the WHERE clause of another query to further restrict data returned by the query. A subquery is a query within another query, also known as a nested query. A subquery is used to return data that will be used in the main query as a condition to further restrict the data to be retrieved. Subqueries are used with the SELECT, INSERT, UPDATE, and DELETE statements.

A subquery can be used in some cases in place of a join operation by indirectly linking data between the tables based on one or more conditions. When a subquery is used in a query, the subquery is resolved first, and then the main query is resolved according to the condition(s) as resolved by the subquery. The results of the subquery are used to process expressions in the WHERE clause of the main query. The
subquery can either be used in the WHERE clause or the HAVING clause of the main query. Logical and relational operators, such as =, >, <, <>, IN, NOT IN, AND, OR, and so on, can be used within the subquery as well to evaluate a subquery in the WHERE or HAVING clause.

17) Correlated Subqueries

Correlated subqueries are common in many SQL implementations. The concept of correlated subqueries is discussed as an ANSI standard SQL topic and is covered briefly in this hour. A correlated subquery is a subquery that is dependent upon information in the main query.

18) View is a virtual table. That is, a view looks like a table and acts like a table as far as a user is concerned.A view is actually a composition of a table in the form of a predefined query. For example, a view can be created from the EMPLOYEE_TBL table that contains only the employee's name and address, instead of all
columns in the EMPLOYEE_TBL table. A view can contain all rows of a table or select rows from a table. A view can be created from one or many tables.

About the Author

Ronald R. Plew is vice president and CIO for Perpetual Technologies, Inc., in Indianapolis, Indiana. Ron is a Certified Oracle Professional, and his duties include Oracle database consulting and training. Ron is an adjunct professor at Indiana University-Purdue University in Indianapolis, where he teaches SQL and various database courses. He holds a Bachelor of Science degree in Business Management/Administration from Indiana Institute of Technology, Fort Wayne, Indiana. Ron also serves in the Indiana Army National Guard, where he is the programmer/analyst for the 433rd Personnel Detachment. Ron's hobbies include golf, chess, and collecting Indianapolis 500 racing memorabilia. He shares ownership of Plew's Indy 500 Museum with his brothers, Mark and Dennis; his sister, Arleen; and mother, Betty. Ron lives in Indianapolis with his wife Linda. Ron and Linda have four children and ten grandchildren.

Ryan K. Stephens currently works for Unisys Federal Systems, where he is a database administrator and is responsible for government-owned databases throughout the United States. In addition to his full-time job, Ryan teaches SQL and various database classes at Indiana University-Purdue University Indianapolis. He also serves part-time as a programmer for the Indiana Army National Guard.