Supplemental Materials

What is included with this book?

The New copy of this book will include any supplemental materials advertised. Please check the title of the book to determine if it should include any access cards, study guides, lab manuals, CDs, etc.

The Used, Rental and eBook copies of this book are not guaranteed to include any supplemental materials. Typically, only the book itself is included. This is true even if the title states it includes any access cards, study guides, lab manuals, CDs, etc.

Summary

The MySQL® database has become the world's most popular open source database because of its consistent fast performance, high reliability, and ease of use. That’s why best-selling authors Phil Pratt and Mary Last are pleased to introduce their latest database textbook - A Guide to MySQL. This straightforward text utilizes three case databases – Premiere Products, Henry Books, and Alexamara Marina Group – to teach the concepts and techniques of using MySQL. Your students will gain the skills to build professional quality MySQL databases, thanks to this real-world approach. Additional hands-on exercises at the end of each chapter offer students the opportunity to problem-solve based on what they’ve previously learned.

Table of Contents

Preface

xi

Introduction to Premiere Products, Henry Books, and Alexamara Marina Group

1

(24)

What is a Database?

2

(1)

The Premiere Products Database

2

(6)

The Henry Books Database

8

(7)

The Alexamara Marina Group Database

15

(5)

MySQL

20

(1)

Chapter Summary

21

(1)

Key Terms

21

(1)

Exercises

21

(4)

Database Design Fundamentals

25

(36)

Database Concepts

26

(5)

Relational Databases

26

(2)

Entities, Attributes, and Relationships

28

(3)

Functional Dependence

31

(3)

Primary Keys

34

(2)

Database Design

36

(7)

Design Method

36

(2)

Database Design Requirements

38

(1)

Database Design Process Example

38

(5)

Normalization

43

(11)

First Normal Form

44

(1)

Second Normal Form

45

(4)

Third Normal Form

49

(5)

Diagrams for Database Design

54

(2)

Chapter Summary

56

(1)

Key Terms

57

(1)

Review Questions

57

(1)

Exercises

58

(3)

An Introduction to SQL

61

(32)

Introduction to MySQL

62

(4)

Starting MySQL

62

(1)

Obtaining Help in MySQL

63

(1)

Using the MySQL Reference Manual to Get Help

64

(2)

Creating a Database

66

(1)

Changing the Default Database

67

(1)

Creating a Table

67

(2)

Running SQL Commands

69

(3)

Editing SQL Commands

69

(2)

Dropping a Table

71

(1)

Data Types

72

(2)

Nulls

74

(1)

Implementation of Nulls

74

(1)

Adding Rows to a Table

75

(2)

The INSERT Command

75

(1)

The INSERT Command with Nulls

76

(1)

Viewing Table Data

77

(2)

Correcting Errors in the Database

79

(1)

Saving SQL Commands

80

(1)

Creating the Remaining Database Tables

81

(4)

Describing a Table

85

(1)

Chapter Summary

86

(1)

Key Terms

86

(1)

Review Questions

87

(1)

Exercises

87

(6)

Single-Table Queries

93

(36)

Constructing Simple Queries

94

(12)

Retrieving Certain Columns and All Rows

94

(1)

Retrieving All Columns and All Rows

95

(1)

Using a WHERE Clause

96

(2)

Using Compound Conditions

98

(3)

Using the BETWEEN Operator

101

(1)

Using Computed Columns

102

(2)

Using the LIKE Operator

104

(1)

Using the IN Operator

105

(1)

Sorting

106

(2)

Using the ORDER BY Clause

106

(1)

Additional Sorting Options

107

(1)

Using Functions

108

(5)

Using the COUNT Function

109

(1)

Using the SUM Function

109

(1)

Using the AVG, MAX, and MIN Functions

110

(1)

Using the DISTINCT Operator

111

(2)

Nesting Queries

113

(3)

Subqueries

114

(2)

Grouping

116

(5)

Using the GROUP BY Clause

116

(2)

Using a HAVING Clause

118

(1)

HAVING vs. WHERE

119

(2)

Nulls

121

(1)

Summary of SQL Clauses, Functions, and Operators

121

(2)

Chapter Summary

123

(1)

Key Terms

123

(1)

Review Questions

124

(1)

Exercises

125

(4)

Multiple-Table Queries

129

(32)

Querying Multiple Tables

130

(3)

Joining Two Tables

130

(3)

Comparing JOIN, IN, and EXISTS

133

(12)

Using the IN Operator

134

(1)

Using the EXISTS Operator

134

(2)

Using a Subquery Within a Subquery

136

(2)

A Comprehensive Example

138

(1)

Using an Alias

139

(1)

Joining a Table to Itself

140

(2)

Using a Self-Join on a Primary Key

142

(1)

Joining Several Tables

143

(2)

Set Operations

145

(5)

ALL and ANY

150

(2)

Special Operations

152

(4)

Inner Join

152

(1)

Outer Join

153

(1)

Product

154

(2)

Chapter Summary

156

(1)

Key Terms

156

(1)

Review Questions

157

(1)

Exercises

157

(4)

Updating Data

161

(20)

Creating a New Table from an Existing Table

162

(1)

Changing Existing Data in a Table

163

(2)

Adding New Rows to an Existing Table

165

(1)

Deleting Existing Rows from a Table

166

(1)

Changing a Value in a Column to Null

167

(1)

Changing a Table's Structure

168

(4)

Making Complex Changes

172

(1)

Commit and Rollback

172

(2)

Transactions

174

(1)

Dropping a Table

175

(1)

Chapter Summary

176

(1)

Key Terms

176

(1)

Review Questions

176

(1)

Exercises

177

(4)

Database Administration

181

(32)

Defining and Using Views

182

(6)

Using a View to Update Data

188

(4)

Updating Row-and-Column Subset Views

188

(1)

Updating Views Involving Joins

189

(3)

Updating Views Involving Statistics

192

(1)

Dropping a View

192

(1)

Security

193

(3)

Indexes

196

(4)

Creating an Index

199

(1)

Dropping an Index

200

(1)

Creating Unique Indexes

200

(1)

System Catalog

200

(3)

Integrity Constraints in SQL

203

(3)

Chapter Summary

206

(1)

Key Terms

206

(1)

Review Questions

207

(1)

Exercises

207

(6)

MySQL Special Topics

213

(36)

Importing and Exporting Data

214

(4)

Importing Data into a Database

214

(3)

Exporting Data from a Table

217

(1)

Database Performance Issues

218

(5)

Optimizing the Tables in a Database

219

(2)

Optimizing the Queries in a Database

221

(2)

Using the MySQL Query Browser

223

(13)

Starting and Using the Query Browser

224

(1)

Getting Help in the Query Browser

225

(1)

Using the Database Browser

226

(1)

Using the Syntax Browser

227

(1)

Creating and Executing Queries

228

(2)

Exporting a Resultset

230

(1)

Using the Script Editor

231

(1)

Using the MySQL Table Editor

232

(3)

Analyzing Query Performance

235

(1)

Using the MySQL Administrator

236

(6)

Starting the Administrator

236

(1)

Viewing the Administrator Window

237

(1)

Getting Help in the Administrator

237

(1)

Backing Up a Database

238

(1)

Restoring a Database

239

(1)

Maintaining a Database

240

(2)

Chapter Summary

242

(1)

Key Terms

242

(1)

Review Questions

243

(1)

Exercises

244

(5)

Appendix A SQL Reference

249

(24)

Aliases (Pages 139--143)

249

(1)

Alter Table (Pages 168--170, 200--202)

249

(1)

Analyze Table (Pages 218--219)

250

(1)

Check Table (Pages 219--220)

250

(1)

Column or Expression List (Select Clause) (Pages 94--96)

251

(1)

Computed Columns (Pages 102--104)

251

(1)

The DISTINCT Operator (Pages 111--112)

251

(1)

Functions (Pages 108--113)

252

(1)

Commit (Pages 172--174)

252

(1)

Conditions (Pages 96--102)

252

(2)

Simple Conditions (Pages 96--98)

252

(1)

Compound Conditions (Pages 98--100)

253

(1)

Between Conditions (Pages 101--102)

253

(1)

Like Conditions (Pages 104--105)

253

(1)

In Conditions (Pages 105--106, 114--116, 134)

253

(1)

Exists Conditions (Pages 134--136)

254

(1)

All and Any (Pages 150--152)

254

(1)

Create Database (Page 66)

254

(1)

Create Index (Pages 193--197)

255

(1)

Create Table (Pages 67--68)

255

(1)

Create View (Pages 180--190)

256

(1)

Data Types (Pages 72--73)

257

(1)

Delete (Rows) (Pages 79--80, 166)

258

(1)

Drop Index (Page 197)

258

(1)

Drop Table (Pages 71, 174)

259

(1)

Drop View (Page 190)

259

(1)

Explain (Pages 221--222, 235--236)

259

(1)

Grant (Pages 190--193)

260

(1)

Insert Into (Query) (Pages 162--163)

260

(1)

Insert Into (Values) (Pages 75--77, 165--166)

261

(1)

Integrity (Pages 200--202)

262

(1)

Load Data Infile (Pages 215--217)

262

(1)

Optimize Table (Pages 220--221)

263

(1)

Procedure Analyse()(Pages 222--223)

263

(1)

Repair Table (Page 220)

264

(1)

Revoke (Page 193)

264

(1)

Rollback (Pages 172--174)

265

(1)

Select (Pages 77--78, 94--122, 130--133)

265

(1)

Select Into Outfile (Pages 217--218)

266

(1)

Show Columns (Pages 85, 198--199)

267

(1)

Show Grants (Page 200)

267

(1)

Show Index (Pages 199--200)

267

(1)

Show Tables (Page 198)

268

(1)

Subqueries (Pages 113--116, 134--137)

268

(1)

Union, Intersect, and Minus (Pages 145--149)

268

(2)

Update (Pages 79--80, 163--165)

270

(1)

Use (Page 67)

271

(2)

Appendix B How Do I Reference

273

(4)

Appendix C Answers to Odd-Numbered Review Questions

277

(6)

Introduction to Premiere Products, Henry Books, and Alexamara Marina Group