SQL

by Forrest Houlette

Overview

This work approaches teaching Structured Query Language the way most people learn it - by focusing on critical tasks first.

Product Details

ISBN-13:	9780072130966
Publisher:	McGraw-Hill/Osborne Media
Publication date:	12/20/2000
Series:	Beginner's Guides (Osborne)
Edition description:	Older Edition
Pages:	452
Product dimensions:	7.50(w) x 9.25(h) x 0.91(d)

Table of Contents

	Acknowledgments	xvii
	Introduction	xix
Part I	Learning the Essentials
1	Accessing the Database	3
	Connecting to the Database	5
	Using a Query Tool	8
	Using ODBC	10
	Using OLE DB	19
	Connecting from a Program	21
	Using a Connection Library	22
	Using DAO	28
	Using ADO	30
Project 1-1	Create a Connection	32
	Mastery Check	33
2	Retrieving Data	35
	Using Select	37
	Adding a Where Clause	44
	Adding Order By	49
	Adding Summaries	50
	Adding a Having Clause	54
	Joining Tables	56
	Combining Fields and Creating Columns	62
Project 2-1	Presenting Data	64
	Mastery Check	67
3	Inserting Data	69
	Using Insert	70
	Using Select with Insert	81
	Adding a Where Clause	83
	Adding a Having Clause	84
	Using Select Into	85
Project 3-1	Data Entry	87
	Mastery Check	92
4	Updating Data	93
	Using Update	94
Project 4-1	Data Validation	95
	Adding a Where Clause	98
	Adding a Having Clause	101
	Using Multiple Tables	104
Project 4-2	Update Data	107
	Mastery Check	113
5	Deleting Data	115
	Deleting Data	116
	Adding a Where Clause	117
	Using a Query to Control Deletion	119
	Using Transactions	120
Project 5-1	Data Deletion	124
	Mastery Check	126
6	Creating Tables	129
	Using Create Table	130
	Planning Keys, Primary and Foreign	135
	Normalizing Data	137
	Adding Indexes	141
	Adding Constraints	143
	Altering Tables	147
	Deleting Tables	148
Project 6-1	Fix the Recordset Handling Bugs	149
	Mastery Check	151
7	Creating Databases	153
	Using Create Database	154
	Planning Tables	157
	Normalizing Tables	159
Project 7-1	Create the Portfolio Database	166
	Planning Security	170
Project 7-2	Fix Bugs	173
	Mastery Check	175
Part II	Building Skills
8	Using Operators	179
	Using Relational and Boolean Operators	180
	Finding What Exists	183
	Finding Something in a Set	184
	Finding Items In Between	185
	Finding In, Any, All, or Some	185
	Using Like and Is Null	187
Project 8-1	Build an Administrative Tool	190
	Mastery Check	194
9	Using Functions	197
	Aggregating Data	198
	AVG	198
	COUNT	199
	MAX	199
	MIN	200
	SUM	200
	STDEV	201
	STDEVP	202
	VAR	202
	VARP	203
Project 9-1	Fix the Bug	204
	Handling Dates and Times	204
	DATEADD	205
	DATEDIFF	205
	DATENAME	206
	DATEPART	206
	DAY	207
	GETDATE	207
	MONTH	208
	YEAR	208
Project 9-2	Test Functions	209
	Using Math Functions	210
	ABS	210
	ACOS	210
	ASIN	211
	ATAN	211
	ATN2	212
	CEILING	212
	COS	212
	COT	213
	DEGREES	213
	EXP	214
	FLOOR	214
	LOG	215
	LOG10	215
	PI	216
	POWER	216
	RADIANS	216
	RAND	217
	ROUND	217
	SIGN	218
	SIN	218
	SQUARE	219
	SQRT	219
	TAN	220
	Using String Functions	221
	ASCII	221
	CHAR	221
	CHARINDEX	222
	DIFFERENCE	222
	LEFT	223
	LEN	223
	LOWER	224
	LTRIM	224
	NCHAR	225
	PATINDEX	225
	QUOTENAME	226
	REPLACE	226
	REPLICATE	227
	REVERSE	227
	RIGHT	228
	RTRIM	228
	SOUNDEX	229
	SPACE	229
	STR	230
	STUFF	230
	SUBSTR	231
	UNICODE	231
	UPPER	232
	Using Critical System Functions	233
	CASE	233
	CAST	234
	CONVERT	234
	ISDATE	235
	ISNULL	236
	ISNUMBERIC	236
Project 9-3	Enter Your Own Database Connections	238
	Mastery Check	241
10	Building Subqueries	243
	Selecting an Item	246
	Setting a Condition	247
	Substituting for Joins	249
	Deleting, Updating, and Inserting	251
Project 10-1	Delete Records	254
	Mastery Check	257
11	Building Views	259
	Rationalizing the Use of Views	261
	Create the Query	263
	Create the View	265
Project 11-1	Create a View	266
Project 11-2	Add Forms	268
	Mastery Check	284
12	Building Stored Procedures	285
	Editing the Procedure	288
	Using Create Procedure	290
Project 12-1	Convert Queries	293
	Calling the Procedure	294
	Using the Results	295
Project 12-2	Convert Queries to Stored Procedures	296
	Mastery Check	297
13	Programming in Stored Procedures	299
	Using Variables	301
	Using Flow Control	303
	Returning Values	308
	Raising Errors	310
Project 13-1	Assemble the Stored Procedure	313
	Mastery Check	314
14	Using Parameters	315
	Creating Parameters	319
	Passing Values	321
Project 14-1	Add Parameters	323
	Using the Results	323
	Mastery Check	327
15	Using Cursors	329
	Declaring Cursors	331
	Manipulating Cursors	333
	Updating and Searching Cursors	336
	Closing and Deallocating Cursors	339
Project 15-1	Assemble a Stored Procedure	339
	Mastery Check	342
16	Building Unions	343
	Using the Union Operator	345
	Adhering to Union Constraints	346
	Building Practical Unions	348
Project 16-1	Build a Union	350
	Mastery Check	353
17	Winding Down the Portfolio Project	355
	The Client's Structure	357
	The Registration Process	363
Project 17-1	Write the Lookups	364
	Planning Required Insert Procedures	365
Project 17-2	Write the INSERTS	366
	Planning Required Unregister Procedures	368
Project 17-3	Write the Unregister Procedures	369
	Planning Required Checkout Procedures	370
Project 17-4	Write the Checkout Procedure	371
	Planning Required Check-In Procedures	371
Project 17-5	Write the Check-In Procedure	371
	Planning Required Delete Procedures	372
Project 17-6	Write the Delete Procedure	373
	A Client Architecture	373
	Mastery Check	377
A	Answers to Mastery Checks	379
Module 1	Accessing the Database	380
Module 2	Retrieving Data	380
Module 3	Inserting Data	381
Module 4	Updating Data	383
Module 5	Deleting Data	384
Module 6	Creating Tables	385
Module 7	Creating Databases	386
Module 8	Using Operators	387
Module 9	Using Functions	388
Module 10	Building Subqueries	389
Module 11	Building Views	390
Module 12	Building Stored Procedures	391
Module 13	Programming in Stored Procedures	393
Module 14	Using Parameters	394
Module 15	Using Cursors	395
Module 16	Building Unions	396
Module 17	Winding Down the Portfolio Project	397
B	A Little SQL Theory	399
	Declarative, Not Procedural	400
	A Relational Model	401
	The Structure of the Language	403
	Data Definition Language	403
	Data Manipulation Language	404
	Data Control Language	404
	Dependence on Transactions	405
	Focus on Integrity	406
	Conclusion	407
	Index	433

Editorial Reviews

Tutorials and examples in this book teach the basics of Structured Query Language (SQL) programming and show how to use SQL to work with data in a database, develop database objects, create subqueries, build views, write stored procedures, and accomplish complex selects and updates. Learning features include examples that walk readers through real-world database tasks, question and answer sections, example code annotated with commentary that points to particular techniques illustrated, and coding exercises. Houlette is president of a firm specializing in software development and training. Annotation c. Book News, Inc., Portland, OR (booknews.com)