SQL Simplified: Learn to Read and Write Structured Query Language

by Cecelia L Allison

Overview

SQL Simplified: Learn To Read and Write Structured Query Language is a low cost book that focuses extensively on the implementation of Structured Query Language (SQL) rather than on database design or on the Database Management Systems (DBMSs) that implement SQL.

This book simplifies the concepts of SQL, enabling anyone to quickly grasp the fundamentals of SQL. Each chapter includes examples, important notes and key terms. Additionally, this book highlights many key differences in SQL script used in a number of different database management system platforms and tests your comprehension of each chapter using quizzes and assignments. After completion of this book, you should feel confident using SQL in any relational database environment.

About the Author:
Cecelia L. Allison is originally from St. Petersburg, Florida. She is a freelance Web master and also works as an author/facilitator instructing students on Structured Query Language (SQL) over the Internet through universities, colleges, and other educational facilities around the world.

Cecelia has been using SQL for many years. Through her past and present employment she has gained extensive experience in writing and implementing SQL. She holds a Bachelor of Science degree in Finance and a Master of Science degree in Computer Information Systems.

Product Details

ISBN-13:	9781410729743
Publisher:	AuthorHouse
Publication date:	08/15/2003
Pages:	212
Product dimensions:	6.08(w) x 9.20(h) x 0.52(d)

About the Author

Dr. Cecelia Allison is an author, technical writer, and on-line professor. Her background as a software tester and technical support specialist has given her extensive hands-on experience using Structured Query Language (SQL). She wrote and facilitates on-line SQL courses titled "Introduction to SQL", "Intermediate SQL" and "Advanced SQL" which are offered through hundreds of educational facilities worldwide, and also teaches on-line technical courses for Park University. She has been using SQL for over 20 years and has taught over 100,000 students SQL worldwide. Dr. Allison holds a Bachelor of Science in finance, a Master of Science in information systems, and a Doctor of Business Administration in management information systems.

Read an Excerpt

CREATE TABLE Employees

Example 1

The following SQL script creates a table named Employees with seven columns (SocialSecNum, Firstname, Lastname, Address, Zipcode, Areacode, PhoneNumber).

CREATE TABLE Employees (
SocialSecNum CHAR (11) NOT NULL PRIMARY KEY,
Firstname CHAR (50) NOT NULL,
Lastname CHAR (50) NOT NULL,
Address CHAR (50) NOT NULL,
Zipcode CHAR (10) NOT NULL,
Areacode CHAR (3) NULL,
PhoneNumber CHAR (8) NULL
);

The preceding script includes several keywords. The keywords are typed in all caps so that they can be easily identified. Although it is not necessary to capitalize keywords, it is good practice since it causes the keywords to stand out. This makes SQL script easier to read. Remember, keywords are reserved words used to interact with the database.

Notice the spacing in the CREATE TABLE script. You must separate all keywords and column names with a space. Additionally, some DBMSs require a closing semicolon at the end of SQL script to indicate where the script ends.

The CREATE TABLE keywords tell the database management system that you want to create a new table. The name (Employees) of the table must be typed directly after the CREATE TABLE keywords.

Each column (SocialSecNum, Firstname, Lastname, Address, Zipcode, Areacode and PhoneNumber) must contain a datatype and a field size. Some datatypes do not require a field size.

A datatype specifies the type of data a column can store. The field size specifies the maximum number of characters that can be entered into a cell within a column. For example, the datatype and field size for the first column (SocialSecNum) is CHAR (11). This means that the SocialSecNum column can only store alphanumeric data no more than eleven characters long.

Following the datatype and field size, specify either NULL or NOT NULL. The NULL keyword indicates that a column can be left blank when entering data in the table. The NOT NULL keywords indicate that a column cannot be left blank. The DBMS will generate an error message whenever a NOT NULL field is left blank.

Note: In Microsoft Access, when you do not state NULL or NOT NULL the column is automatically set to NULL.

The primary key is usually specified after NULL | NOT NULL. In figure 2.1, the SocialSecNum column is the primary key column. In some versions of Microsoft Access, the primary key is set as follows:

SocialSecNum CHAR (11) NOT NULL CONSTRAINT PriKey
Primary Key

This method uses the CONSTRAINT keyword. You will learn more about constraints in chapter 10.

Table of Contents

Chapter 1. The Relational Database and Structured Query Language
The Relational Database Structure
Structured Query Language
Chapter 2. Creating Tables and Working with Data
Table Creation
Inserting Data Into a Table
Transferring Data from One Table to an Existing Table
Transferring Data from One Table to a New Table
Updating Data in a Table
Deleting Data in a Table
Chapter 3. Selecting and Retrieving Data
The SELECT Statement
Creating a SELECT Statement to Retrieve Multiple Columns
Creating a SELECT Statement to Retrieve Every Column
Using DISTINCT to display Unique Values in a Column
Using the AS Keyword to Create an Alternate Name for a Column
Merging Columns
Uniting Queries to Compare Records in Two Separate Tables
Chapter 4. Filter Retrieved Data
The WHERE Clause
Using a Comparison Operator to Match a Condition
Using a Character Operator to Match a Condition
Using a Logical Operator to Match a Condition
Using a Logical Operator to Match a Condition Opposite of the One Defined
Using the IN Operator to Match a Condition
Using the BETWEEN Operator to Match a Condition
Chapter 5. Creating Calculated Fields
Calculated Fields
Concatenation
Arithmetic Operators
Functions
Chapter 6. Using Additional Clauses in Structured Query Language Clauses
Using the ORDER BY Clause
Using the GROUP BY Clause
Using the GROUP BY and WHERE Clause
Using the HAVING Clause
Chapter 7. Creating Table Joins
Table Joins
Qualification
Self Join
Natural Join
Outer Join
Chapter 8. Creating Subqueries
Subqueries
Using the IN keyword to link Queries
Using the EXISTS keyword to link Queries
Using the ANY keyword to link Queries
Using the ALL keyword to link Queries
Creating Subqueries to Query Three Tables
Chapter 9. Creating Views
Views
Creating a View on One Table
Creating a View on Multiple Tables
Filtering Table Data Using a View
Updating and Inserting Data into Tables Using Views
Deleting a View
Chapter 10. Advanced Table Creation and Table Management
Table Creation
Constraints
Indexes
Table Management
Adding a Column
Deleting a Column
Deleting a Table
Chapter 11. Advanced Topics: Transaction Processing, Stored Procedures, Triggers and Cursors Transaction Processing
Stored Procedures
Triggers
Cursors

Appendix A
Answers to Quizzes and Assignments
Appendix B
SQL Script for the Tables Used in the Book
Appendix C
SQL Command Syntax
Appendix D
Instructions on Where to Type SQL Script in Microsoft Access and Microsoft SQL Server
Index