A Guide to SQL / Edition 10

by Mark Shellman, Hassan Afyouni, Philip J. Pratt, Mary Z. Last

Overview

Master today's SQL programming principles and learn how to most effectively apply your SQL skills with Shellman/Afyouni/Pratt/Last's A GUIDE TO SQL, Tenth Edition. This edition's leading approach combines straightforward instruction and extensive hands-on exercises with a strong foundation and emphasis on today's actual business applications. This edition introduces basic SQL commands in the context of an intriguing, ongoing case in which a business uses SQL to manage orders, items, customers and sales reps. This edition emphasizes how to apply fundamental principles and strengthen your programming practices, regardless of the database environment. You work with examples from the latest versions of MySQL, the world's most popular open source, free database, as well as Oracle and Microsoft SQL Server. In addition, MindTap digital learning solution is available to help you strengthen your SQL skills.

Product Details

ISBN-13:	9780357361689
Publisher:	Cengage Learning
Publication date:	08/04/2020
Series:	MindTap Course List
Edition description:	10th ed.
Pages:	320
Product dimensions:	7.30(w) x 9.10(h) x 0.60(d)

About the Author

Dr. Mark Shellman is an instructor and the Chair of the Information Technology Department at Gaston College in Dallas, North Carolina. Dr. Mark, as his students refer to him, prides himself on being student-centered and loves learning himself. His favorite subjects in the information technology realm include databases and programming languages. Dr. Mark has been teaching information technology for more than thirty years and has co-authored several texts in the New Perspectives Series on Microsoft Access databases.

Dr. Hassan Afyouni has been working in the information technology field for more than thirty years. He is a database expert, Oracle specialist, an enterprise architect, technical advisor and educator. He has been an instructor for several colleges and universities in Canada, the United States and Lebanon. He is a respected author of several leading books in the database field.

Philip J. Pratt is Professor Emeritus of Mathematics and Computer Science at Grand Valley State University, where he taught for 33 years. His teaching interests include database management, systems analysis, complex analysis and discrete mathematics. He has authored more than 75 textbooks and has co-authored three levels of Microsoft Office Access books for the popular Shelly Cashman Series, in addition to the popular A GUIDE TO SQL.

Mary Z. Last has taught computer information systems since 1984. She retired from the University of Mary Hardin-Baylor, Belton, Texas, where she was an associate professor and the Director of the Center for Effectiveness in Learning and Teaching. Ms. Last is actively involved in the Computing Educator's Oral History Project that encourages young women to pursue careers in math and science. She has been a contributing author to the Shelly Cashman Series since 1992. She also authors many instructor resources for leading database texts.

Table of Contents

Preface ix

Module 1 Introduction to Kim Tay Pet Supplies and Stay Well Student Accommodation Databases 1

Introduction 1

What Is a Database? 1

The KimTay Pet Supplies Database 2

StayWell Student Accommodation Database 8

Module Summary 12

Key Term 12

Case Exercises 12

KimTay Pet Supplies 12

StayWell Student Accommodation 13

Module 2 Database Design Fundamentals 15

Introduction 15

Database Concepts 16

Relational Databases 16

Entities, Attributes, and Relationships 17

Functional Dependence 20

Primary Keys 23

Database Design 25

Design Method 25

Database Design Requirements 26

Database Design Process Example 27

Normalization 33

First Normal Form 33

Second Normal Form 35

Third Normal Form 38

Diagrams for Database Design 43

Module Summary 45

Key Terms 46

Review Questions 46

Case Exercises 47

KimTay Pet Supplies 47

StayWell Student Accommodation 48

Module 3 Creating Tables 51

Introduction 51

Creating and Running SQL Commands 52

Using MySQL Workbench 52

Entering Commands 54

Creating a Database 57

Changing the Default Database 59

Creating a Table 60

Correcting Errors in SQL Commands 65

Dropping a Table 66

Using Data Types 67

Using Nulls 68

Adding Rows to a Table 69

The INSERT Command 69

Inserting a Row that Contains Nulls 73

Viewing Table Data 74

Correcting Errors in a Table 76

Saving SQL Commands 79

Creating the Remaining Database Tables 83

Describing a Table 88

Module Summary 92

Key Terms 92

Review Questions 92

Case Exercises 93

KimTay Pet Supplies 94

StayWell Student Accommodation 94

Module 4 Single-Table Queries 97

Introduction 97

Constructing Simple Queries 97

Retrieving Certain Columns and All Rows 98

Retrieving All Columns and All Rows 99

Using a WHERE Clause 100

Using Compound Conditions 102

Using the BETWEEN Operator 105

Using Computed Columns 107

Using the LIKE Operator 109

Using the IN Operator 111

Sorting 111

Using the ORDER BY Clause 112

Additional Sorting Options 112

Using Functions 113

Using the COUNT Function 114

Using the SUM Function 115

Using the AVG, MAX, and MIN Functions 115

Using the DISTINCT Operator 117

Nesting Queries 119

Subqueries 121

Grouping 123

Using the GROUP BY Clause 124

Using a HAVING Clause 125

HAVING vs. WHERE 126

Nulls 128

Summary of SQL Clauses, Functions, and Operators 129

Module Summary 130

Key Terms 131

Review Questions 131

Case Exercises 132

KimTay Pet Supplies 132

StayWell Student Accommodation 133

Module 5 Multiple-Table Queries 135

Introduction 135

Querying Multiple Tables 135

Joining Two Tables 135

Comparing Joins, IN, and EXISTS 139

Using the IN Operator 139

Using the EXISTS Operator 140

Using a Subquery Within a Subquery 142

A Comprehensive Example 144

Using an Alias 145

Joining a Table to Itself 146

Using a Self-Join on a Primary Key Column 148

Joining Several Tables 150

Set Operations 153

ALL and ANY 159

Special Operations 162

Inner Join 162

Outer Join 163

Product 165

Module Summary 167

Key Terms 168

Review Questions 168

Case Exercises 169

KimTay Pet Supplies 169

StayWell Student Accommodation 170

Module 6 Updating Data 173

Introduction 173

Creating a New Table from an Existing Table 173

Changing Existing Data in a Table 176

Adding New Rows to an Existing Table 179

Autocommit, Commit, and Rollback 180

Transactions 183

Changing and Deleting Existing Rows 184

Executing a Rollback 186

Changing a Value in a Column to Null 187

Changing a Table's Structure 189

Making Complex Changes 196

Dropping a Table 196

Module Summary 197

Key Terms 197

Review Questions 197

Case Exercises 198

KimTay Pet Supplies 198

StayWell Student Accommodation 199

Module 7 Database Administration 201

Introduction 201

Creating and using Views 201

Using a View to Update Data 210

Updating Row-and-Column Subset Views 210

Updating Views Involving Joins 212

Updating Views Involving Statistics 216

Dropping a View 216

Security 217

Indexes 219

Creating an Index 222

Dropping an Index 223

Creating Unique Indexes 225

System Catalog 225

Update of the System Catalog 228

Integrity Constraints in SQL 229

Module Summary 233

Key Terms 233

Review Questions 234

Case Exercises 235

KimTay Pet Supplies 235

StayWell Student Accommodation 237

Module 8 Functions, Procedures, and Triggers 239

Introduction 239

Using SQL in a Programming Environment 239

Using Functions 240

Character Functions 241

Number Functions 242

Working with Dates 243

Concatenating Columns 248

Stored Procedures Using MySQL 252

Retrieving a Single Row and Column 253

Error Handling 255

Using Update Procedures 259

Changing Data with a Procedure 259

Deleting Data with a Procedure 260

Selecting Multiple Rows with a Procedure 262

Using a Cursor 262

Opening a Cursor 263

Fetching Rows from a Cursor 263

Closing a Cursor 265

Writing a Complete Procedure Using a Cursor 265

Using More Complex Cursors 265

Advantages of Cursors 268

Using PL/SQL in Oracle 268

Error Handling in PL/SQL 270

Writing a Complete Procedure Using a Cursor in PL/SQL 271

Using T-SQL in SQL Server 272

Retrieving a Single Row and Column in T-SQL 272

Changing Data with a Stored Procedure in T-SQL 273

Deleting Data with a Stored Procedure in T-SQL 273

Using a Cursor in T-SQL 274

Using More Complex Cursors in T-SQL 275

Using a Trigger 275

Module Summary 282

Key Terms 283

Review Questions 283

Case Exercises 284

KimTay Pet Supplies 284

StayWell Student Accommodation 286

Appendix A SQL Reference A.1

Aliases A.1

Alter Table A.1

Column or Expression List (SELECT Clause) A.2

Computed Columns A.2

The DISTINCT Operator A.2

Functions A.3

COMMIT A.3

Conditions A.3

Simple Conditions A.3

Compound Conditions A.4

BETWEEN Conditions A.4

LIKE Conditions A.4

IN Conditions A.4

EXISTS Conditions A.5

ALL and ANY A.5

CREATE INDEX A.6

CREATE TABLE A.6

CREATE VIEW A.6

Data Types A.7

DELETE Rows A.8

DESCRIBE A.8

DROP INDEX A.8

DROP TABLE A.9

DROP VIEW A.9

GRANT A.9

INSERT INTO (Query) A.10

INSERT INTO (Values) A.10

Integrity A.10

REVOKE A.11

ROLLBACK A.11

SELECT A.12

Subqueries A.13

UNION, INTERSECT, and MINUS A.13

UPDATE A.14

Appendix B How Do I Reference A.15

Appendix C The 10 Commandments of Writing Queries A.19

Index I.1