SQL Power! The Comprehensive Guide

by Hess, Kenneth

• ISBN13: 9781598632125
• ISBN10: 1598632124
• Edition: 1st
• Format: Paperback
• Copyright: 2006-06-16
• Publisher: Cengage Learning PTR

Summary

If you're an aspiring database administrator, you need to learn SQL quickly to advance in your career. SQLPower! The Comprehensive Guide provides just the data you need to go from novice to master by immersingyou in real-life database design, implementation, and administration situations. The book is equally useful forthose already experienced with databases and SQL who want to increase their knowledge without having tosift through a tome. Beginning with an overview of SQL and the concept of relational databases, the book goes on to cover all the major topics, including tables, primary keys, relationships, foreign keys, MySQL, and database software, as well as the data definition, data manipulation, and data control languages. Later chapters cover higher-level topics such as advanced queries, virtual tables, normalization, and embedded SQL. The book's examples build upon one another, with end-ofchapter exercises that test your knowledge as you go. All of the source code used in the book is available for download at www.courseptr.com/downloads.

Table of Contents

SQL: The Language	p. 1
A Bit of History	p. 1
SQL Defined	p. 2
SQL-environments	p. 2
SQL-agents	p. 3
SQL-implementations	p. 3
SQL-clients	p. 3
SQL-servers	p. 3
SQL-client modules	p. 3
User Identifiers	p. 4
Catalogs	p. 4
SQL-schemas	p. 4
Information Schema	p. 4
Definition Schema	p. 4
SQL-data	p. 4
Tables	p. 4
SQL as a Language	p. 5
SQL Standards	p. 6
SQL92	p. 6
SQL99	p. 8
SQL2003	p. 10
Speculating on the Future of SQL	p. 11
Database Technology	p. 11
The SQL Language	p. 12
SQL Standards	p. 13
Database Vendors	p. 13
Open Source	p. 14
Summary	p. 14
Chapter Review	p. 15
Multiple Choice	p. 15
Concepts	p. 17
Relational Databases	p. 19
The Relational Data Model	p. 20
Tables	p. 22
Primary Keys	p. 25
Indexes	p. 26
Summary	p. 31
Chapter Review	p. 31
Multiple Choice	p. 31
Concepts	p. 33
Database Software	p. 35
Sales	p. 35
ACID Compliance	p. 36
Database Software	p. 38
Microsoft SQL Server	p. 40
Microsoft Access	p. 41
Oracle	p. 43
Informix	p. 44
Sybase	p. 45
DB2	p. 46
MySQL	p. 47
PostgreSQL	p. 49
SQLite	p. 51
Teradata	p. 53
Other RDBMSs	p. 54
Summary	p. 55
Chapter Review	p. 56
Multiple Choice	p. 56
Concepts	p. 58
Data Definition Language	p. 59
Preparing	p. 60
CREATE	p. 61
CREATE DATABASE	p. 61
CREATE TABLE	p. 63
CREATE TABLE AS	p. 67
CREATE INDEX	p. 68
DROP	p. 70
DROP DATABASE	p. 70
DROP TABLE	p. 71
DROP INDEX	p. 72
ALTER	p. 74
ALTER DATABASE	p. 74
ALTER TABLE	p. 74
RENAME TABLE	p. 79
Summary	p. 79
Chapter Review	p. 80
Multiple Choice	p. 80
MySQL	p. 83
MySQL AB	p. 83
The MySQL Database Management System	p. 85
Tables	p. 86
Permanent Tables	p. 86
Virtual Tables (Views)	p. 87
Temporary Tables	p. 87
MySQL Table Types	p. 88
MyISAM	p. 89
InnoDB	p. 90
Federated	p. 93
Memory (Heap)	p. 94
Switching Storage Engines	p. 95
Locking	p. 95
Summary	p. 97
Chapter Review	p. 97
Multiple Choice	p. 97
Concepts	p. 99
Data Manipulation Language	p. 101
INSERT	p. 101
Objective	p. 102
Objective	p. 104
SELECT	p. 105
Objective	p. 106
Objective	p. 106
Objective	p. 107
Objective	p. 108
Objective	p. 109
Objective	p. 110
Objective	p. 110
Objective	p. 111
Objective	p. 111
Objective	p. 112
Objective	p. 112
Objective	p. 114
Objective	p. 114
Objective	p. 115
Objective	p. 115
Objective	p. 116
UPDATE	p. 117
Objective	p. 117
Objective	p. 119
DELETE	p. 120
Objective	p. 122
Objective	p. 122
Miscellaneous Data-Manipulation Commands	p. 123
TRUNCATE	p. 123
REPLACE	p. 123
Summary	p. 124
Chapter Review	p. 124
Multiple Choice	p. 124
Concepts	p. 126
Data Control Language	p. 127
Access Control	p. 127
GRANT	p. 128
Limiting Resources	p. 135
REVOKE	p. 136
Transaction Control	p. 140
AUTOCOMMIT	p. 140
SET TRANSACTION	p. 140
BEGIN	p. 141
COMMIT	p. 141
ROLLBACK	p. 141
SAVEPOINT	p. 142
Summary	p. 145
Chapter Review	p. 146
Multiple Choice	p. 146
Concepts	p. 149
Advanced Queries	p. 151
Multiple-Table Queries	p. 154
Objective	p. 155
Subqueries	p. 157
JOIN	p. 160
CROSS JOIN	p. 161
NATURAL JOIN	p. 163
INNER JOIN	p. 164
OUTER JOIN	p. 165
UNION	p. 167
Summary Queries	p. 169
Column Functions	p. 169
GROUP BY	p. 170
HAVING	p. 171
Batch Data Extraction	p. 172
Batch Data Loading	p. 174
Summary	p. 178
Chapter Review	p. 178
Multiple Choice	p. 178
Concepts	p. 180
Views	p. 181
CREATE VIEW	p. 181
Objective	p. 185
ALTER VIEW	p. 188
Objective	p. 188
Objective	p. 189
DROP VIEW	p. 189
SHOW CREATE VIEW	p. 190
Updating Views	p. 190
Limitations of Views	p. 192
View Security	p. 193
Advanced View Information	p. 193
Summary	p. 194
Chapter Review	p. 195
Multiple Choice	p. 195
Concepts	p. 197
SQL Optimization	p. 199
Hardware Optimization	p. 199
Disk I/O	p. 200
CPU	p. 202
Memory	p. 203
Network	p. 204
Database Software	p. 207
Operating System	p. 207
Database Design	p. 207
Indexes	p. 210
Query Optimizer	p. 211
Data Selection	p. 214
SELECT * FROM Table_Name	p. 214
Only Select Data Needed for Query	p. 214
Full Table Scans	p. 215
EXPLAIN	p. 215
JOIN TYPES	p. 219
Examining the Query	p. 222
Summary	p. 223
Chapter Review	p. 223
Multiple Choice	p. 223
Concepts	p. 225
Normalization	p. 227
First Normal Form	p. 229
Second Normal Form	p. 232
Third Normal Form	p. 233
Boyce-Codd Normal Form	p. 234
Advanced Normal Forms	p. 235
Denormalization	p. 235
A Database Design How-To	p. 236
Sketch It Out	p. 236
Check for Redundancies	p. 237
Assign Primary Keys	p. 239
Assign Foreign Keys	p. 239
Summary	p. 241
Chapter Review	p. 242
Multiple Choice	p. 242
Concepts	p. 244
Embedded SQL	p. 245
PHP for the Web	p. 245
Testing	p. 246
Embedded SQL Examples	p. 248
Command-Line PHP	p. 253
Command-Line PHP Examples	p. 253
Objective	p. 255
Other Languages	p. 258
Perl	p. 258
Perl Resources	p. 260
Perl Example	p. 260
Python	p. 261
Python Resource	p. 261
Python Example	p. 261
Ruby	p. 262
Ruby Resources	p. 262
Ruby Example	p. 262
Summary	p. 264
Chapter Review	p. 264
Multiple Choice	p. 264
Stored Procedures	p. 267
DELIMITER	p. 268
CREATE PROCEDURE	p. 269
Objective	p. 269
CALL	p. 270
SHOW CREATE PROCEDURE	p. 271
ALTER PROCEDURE	p. 271
Objective	p. 271
DROP PROCEDURE	p. 272
BEGIN...END	p. 272
DECLARE	p. 273
SHOW PROCEDURE STATUS	p. 273
Programming Constructs	p. 273
Branching	p. 273
Looping	p. 275
Summary	p. 276
Chapter Review	p. 277
Multiple Choice	p. 277
Concepts	p. 279
Data Warehousing	p. 281
OLTP	p. 281
OLAP	p. 282
The Operational Database	p. 282
The Data Warehouse	p. 282
Star Schema	p. 283
Snowflake Schema	p. 283
The Data Mart	p. 283
Building a Data Warehouse	p. 284
Summary	p. 286
Chapter Review	p. 286
Multiple Choice	p. 286
Concepts	p. 288
Database Administrator Utilities	p. 289
MySQL Maintenance	p. 295
MySQL Installation	p. 319
Orca Installation	p. 331
PHP Installation	p. 349
Functions	p. 363
Answers to Chapter Review Questions	p. 439
Table of Contents provided by Ingram. All Rights Reserved.