A Guide To Sql

by PRATT

• ISBN13: 9780619216740
• ISBN10: 0619216743
• Edition: 7th
• Format: Paperback
• Copyright: 2004-11-22
• Publisher: Course Technology

Summary

This comprehensive reference to SQL teaches basic familiarity with SQL programming using straightforward instruction and hands-on projects. All screenshots and examples have been updated to Oracle 8i to provide the most current coverage possible. Two New appendices provide handy references for Oracle Commands and Tasks.

Table of Contents

Preface	ix
Introduction to Premiere Products, Henry Books, and Alexamara Marina Group	1	(24)
Objectives	1	(1)
Introduction	2	(1)
What is a Database?	2	(1)
The Premiere Products Database	2	(7)
The Henry Books Database	9	(7)
The Alexamara Marina Group Database	16	(5)
Summary	21	(1)
Key Terms	21	(1)
Exercises (Premiere Products)	21	(1)
Exercises (Henry Books)	22	(1)
Exercises (Alexamara Marina Group)	23	(2)
An Introduction to SQL	25	(48)
Objectives	25	(1)
Introduction	26	(1)
Relational Databases	26	(5)
Entities, Attributes, and Relationships	28	(3)
Database Creation	31	(2)
Running SQL Commands	33	(8)
Editing SQL Commands	36	(5)
Dropping a Table	41	(1)
Data Types	42	(1)
Nulls	42	(2)
Implementation of Nulls	43	(1)
Loading a Table with Data	44	(5)
The Insert Command	44	(4)
The Insert Command with Nulls	48	(1)
Viewing Table Data	49	(6)
Correcting Errors in the Database	55	(1)
Saving SQL Commands	56	(2)
Creating the Remaining Database Tables	58	(4)
Describing a Table	62	(3)
Summary	65	(1)
Key Terms	66	(1)
Review Questions	66	(2)
Exercises (Premiere Products)	68	(1)
Exercises (Henry Books)	68	(2)
Exercises (Alexamara Marina Group)	70	(3)
Single-Table Queries	73	(32)
Objectives	73	(1)
Introduction	74	(1)
Constructing Simple Queries	75	(10)
Retrieving Certain Columns and All Rows	75	(1)
Retrieving All Columns and All Rows	76	(1)
Using a Where Clause	77	(1)
Using Compound Conditions	78	(2)
Using the Between Operator	80	(1)
Using Computed Columns	81	(2)
Using the Like Operator	83	(1)
Using the In Operator	84	(1)
Sorting	85	(2)
Using the Order by Clause	85	(1)
Additional Sorting Options	86	(1)
Using Functions	87	(4)
Using the Count Function	87	(1)
Using the Sum Function	88	(1)
Using the Distinct Operator	89	(2)
Nesting Queries	91	(2)
Subqueries	92	(1)
Grouping	93	(2)
Using the Group by Clause	94	(1)
Using a Having Clause	95	(2)
Having vs. Where	95	(2)
Nulls	97	(1)
Summary	98	(1)
Key Terms	99	(1)
Review Questions	100	(1)
Exercises (Premiere Products)	100	(1)
Exercises (Henry Books)	101	(2)
Exercises (Alexamara Marina Group)	103	(2)
Multiple-Table Queries	105	(34)
Objectives	105	(1)
Introduction	106	(1)
Querying Multiple Tables	106	(12)
Joining Two Tables	106	(3)
Comparing Join, in, and Exists	109	(1)
Using in	110	(1)
Using Exists	110	(2)
Using a Subquery within a Subquery	112	(1)
A Comprehensive Example	113	(1)
Using an Alias	114	(1)
Joining a Table to Itself	115	(3)
Using a Self-Join on a Primary Key	118	(3)
Joining Several Tables	119	(2)
Set Operations	121	(4)
All and Any	125	(3)
Special Operations	128	(4)
Inner Join	128	(1)
Outer Join	129	(2)
Product	131	(1)
Summary	132	(1)
Key Terms	133	(1)
Review Questions	134	(1)
Exercises (Premiere Products)	134	(2)
Exercises (Henry Books)	136	(1)
Exercises (Alexamara Marina Group)	137	(2)
Updating Data	139	(20)
Objectives	139	(1)
Introduction	140	(1)
Creating a New Table from an Existing Table	140	(1)
Changing Existing Data in a Table	141	(2)
Adding New Rows to an Existing Table	143	(1)
Commit and Rollback	144	(1)
Transactions	145	(1)
Deleting Existing Rows from a Table	146	(1)
Executing a Rollback	147	(1)
Changing a Value in a Column to Null	147	(1)
Changing a Table's Structure	148	(4)
Making Complex Changes	152	(1)
Dropping a Table	152	(1)
Summary	153	(1)
Key Terms	154	(1)
Review Questions	154	(1)
Exercises (Premiere Products)	155	(1)
Exercises (Henry Books)	156	(1)
Exercises (Alexamara Marina Group)	157	(2)
Database Administration	159	(34)
Objectives	159	(1)
Introduction	160	(1)
Views	160	(10)
Row-and-Column Subsets	166	(1)
Joins	167	(3)
Statistics	170	(1)
Dropping a View	170	(1)
Security	170	(3)
Indexes	173	(5)
Creating an Index	176	(1)
Dropping an Index	177	(1)
Unique Indexes	178	(1)
System Catalog	178	(3)
Integrity Rules in SQL	181	(3)
Summary	184	(1)
Key Terms	185	(1)
Review Questions	186	(1)
Exercises (Premiere Products)	187	(2)
Exercises (Henry Books)	189	(2)
Exercises (Alexamara Marina Group)	191	(2)
Reports	193	(36)
Objectives	193	(1)
Introduction	194	(1)
Using Functions	194	(5)
Character Functions	194	(1)
Number Functions	195	(1)
Working with Dates	196	(3)
Concatenating Columns	199	(2)
Creating and Using Scripts	201	(1)
Running the Query for the Report	202	(2)
Creating the Data for the Report	204	(1)
Changing Column Headings	205	(2)
Changing Column Formats in a Report	207	(2)
Adding a Title to a Report	209	(2)
Grouping Data in a Report	211	(1)
Including Totals and Subtotals in a Report	212	(2)
Sending the Report to a File	214	(2)
Completing the Script to Produce the Report	216	(3)
Summary	219	(2)
Key Terms	221	(1)
Review Questions	221	(1)
Exercises (Premiere Products)	222	(3)
Exercises (Henry Books)	225	(2)
Exercises (Henry Books)	227	(2)
Embedded SQL	229	(32)
Objectives	229	(1)
Introduction	230	(1)
Using Prompt Variables	231	(1)
PL/SQL Programs	232	(7)
Retrieving a Single Row and Column	232	(2)
Using the % Type Attribute	234	(1)
Retrieving a Single Row from a Join	235	(1)
Inserting a Row into a Table	236	(1)
Changing a Single Row in a Table	237	(1)
Deleting Rows from a Table	238	(1)
Deleting Rows from Multiple Tables	238	(1)
Multiple-Row Select	239	(10)
Using Cursors	240	(1)
Opening a Cursor	240	(1)
Fetching Rows from a Cursor	241	(2)
Closing a Cursor	243	(1)
Complete Program Using a Cursor	243	(1)
More Complex Cursors	244	(2)
Advantages of Cursors	246	(1)
Updating Cursors	247	(2)
Error Handling	249	(1)
Using SQL in Microsoft Access Programs	249	(6)
Deleting Rows	250	(1)
Running the Code	251	(1)
Updating Rows	252	(1)
Inserting Rows	253	(1)
Finding Rows	253	(2)
Summary	255	(1)
Key Terms	256	(1)
Review Questions	256	(1)
Exercises (Premiere Products)	257	(1)
Exercises (Henry Books)	258	(1)
Exercises (Alexamara Marina Group)	259	(2)
A SQL Reference	261	(16)
Aliases	262	(1)
Alter Table	262	(1)
Column or Expression List (Select Clause)	262	(1)
Computed Columns	263	(1)
The Distinct Operator	263	(1)
Functions	263	(1)
Commit	263	(1)
Conditions	264	(2)
Simple Conditions	264	(1)
Compound Conditions	264	(1)
Between Conditions	265	(1)
Like Conditions	265	(1)
In Conditions	265	(1)
Exists Conditions	266	(1)
All and Any	266	(1)
Create Index	266	(1)
Create Table	267	(1)
Create View	267	(1)
Data Types	268	(1)
Delete Rows	269	(1)
Drop Index	269	(1)
Drop Table	269	(1)
Drop View	270	(1)
Grant	270	(1)
Insert Into (Query)	271	(1)
Insert Into (Values)	271	(1)
Integrity	272	(1)
Revoke	272	(1)
Rollback	273	(1)
Select	273	(1)
Subqueries	274	(1)
Union, Intersect, and Minus	275	(1)
Update	276	(1)
B ``How Do I'' Reference	277	(4)
C Answers to Odd-Numbered Review Questions	281	(6)
Introduction to Premiere Products, Henry Books, and Alexamara Marina Group	282	(1)
An Introduction to SQL	282	(1)
Single-Table Queries	282	(1)
Multiple-Table Queries	283	(1)
Updating Data	284	(1)
Database Administration	284	(1)
Reports	285	(1)
Embedded SQL	285	(2)
Index	287