Description
Book SynopsisRyan Stephens is an entrepreneur who has built his career and multiple IT companies around SQL, data, and relational databases. He has shared his knowledge and experience with organizations, students, and IT professionals all over the world. Two of the companies he has co-founded, Perpetual Technologies, Inc. (PTI), and Indy Data Partners, have provided expert database and IT services to large-scale government and commercial clients for more than 25 years. Ryan has authored several books for Pearson, including
Sams Teach Yourself SQL in 24 Hours, 6th Edition; some of his books have been translated and published internationally. Additionally, Ryan has worked for large organizations and has consulted within the areas of SQL, database design, database management, and project management. He designed and taught a database management program for Indiana University-Purdue University in Indianapolis and currently teaches online SQL and database classes for Pearson Education.
Table of ContentsHour 1: Understanding the Relational Database and SQL
Thriving in a Data-Driven World
Understanding the Relational Database
The Relational Database Continues to Lead the Way
Examples and Exercises
Summary
Q&A
Workshop
Hour 2: Exploring the Components of the SQL Language
SQL Definition and History
SQL: The Standard Language
SQL Sessions
Types of SQL Commands
Summary
Q&A
Workshop
Hour 3: Getting to Know Your Data
The BIRD Database: Examples and Exercises in This Book
How to Talk About the Data
Entity Relationship Diagrams
Examples and Exercises
Summary
Q&A
Workshop
Hour 4: Setting Up Your Database
Locating the Files You Need
Getting Set Up for Hands-on Exercises
List of Data by Table
Summary
Q&A
Workshop
Hour 5: Understanding the Basics of Relational (SQL) Database Design
Understanding What Database Design Has to Do with SQL
The Database Design Process
Choosing a Database Design Methodology
Using a Simple Process to Think Through the Design of the BIRDS Database
Logical Model vs. Physical Design
Database Life Cycle
Summary
Q&A
Workshop
Hour 6: Defining Entities and Relationships
Creating a Data Model Based on Your Data
Defining Relationships
Employing Referential Integrity
Creating an Entity Relationship
Summary
Q&A
Workshop
Hour 7: Normalizing Your Database
Defining Normalization
Exploring the Most Common Normal Forms of the Normalization Process
Denormalizing a Database
Applying Normalization to Your Database
Summary
Q&A
Workshop
Hour 8: Defining Data Structures
Defining Data
Understanding Basic Data Types
Using Data Types in the BIRDS Database
Summary
Q&A
Workshop
Hour 9: Creating and Managing Database Objects
Database Objects and Schemas
Tables: The Primary Storage for Data
Integrity Constraints
Summary
Q&A
Workshop
Hour 10: Manipulating Data
Getting an Overview of Data Manipulation
Populating Tables with New Data
Updating Existing Data
Deleting Data from Tables
Summary
Q&A
Workshop
Hour 11: Managing Database Transactions
Defining Transactions
Controlling Transactions
Dealing with Poor Transactional Control
Summary
Q&A
Workshop
Hour 12: Introduction to Database Queries
Using the SELECT Statement
Case Sensitivity
Fundamentals of Query Writing
Summary
Q&A
Workshop
Hour 13: Using Operators to Categorize Data
Defining an Operator in SQL
Using Comparison Operators
Using Logical Operators
Using Conjunctive Operators
Using Negative Operators
Using Arithmetic Operators
Summary
Q&A
Workshop
Hour 14: Joining Tables in Queries
Selecting Data from Multiple Tables
Understanding Joins
Join Considerations
Summary
Q&A
Workshop
Hour 15: Restructuring the Appearance of Data
ANSI Character Functions
Common Character Functions
Miscellaneous Character Functions
Mathematical Functions
Conversion Functions
Combined Character Functions
Summary
Q&A
Workshop
Hour 16: Understanding Dates and Times
Understanding How a Date Is Stored
Using Date Functions
Converting Dates
Summary
Q&A
Workshop
Hour 17: Summarizing Data Results from a Query
Using Aggregate Functions
Grouping Data
Using the GROUP BY Clause
Understanding the Difference Between GROUP BY and ORDER BY
Using CUBE and ROLLUP Expressions
Using the HAVING Clause
Summary
Q&A
Workshop
Hour 18: Using Subqueries to Define Unknown Data
Defining Subqueries
Embedded Subqueries
Using Correlated Subqueries
Summary
Q&A
Workshop
Hour 19: Combining Multiple Queries into One
Differentiating Single Queries and Compound Queries
Using Compound Query Operators
Using ORDER BY with a Compound Query
Using GROUP BY with a Compound Query
Retrieving Accurate Data
Summary
Q&A
Workshop
Hour 20: Creating and Using Views and Synonyms
Defining Views
Creating Views
Updating Data Through a View
Dropping a View
Understanding the Performance Impact of Nested Views
Defining Synonyms
Summary
Q&A
Workshop
Hour 21: Managing Database Users and Security
Managing Users in the Database
Understanding the Management Process
Maximizing Tools Utilized by Database Users
Understanding Database Security
Assigning Privileges
Controlling User Access
Controlling Privileges Through Roles
Summary
Q&A
Workshop
Hour 22: Using Indexes to Improve Performance
Defining an Index
Understanding How Indexes Work
Using the CREATE INDEX Command
Identifying Types of Indexes
Knowing When to Consider Using an Index
Knowing When to Avoid Indexes
Altering an Index
Dropping an Index
Summary
Q&A
Workshop
Hour 23: Improving Database Performance
Defining SQL Statement Tuning
Comparing Database Tuning and SQL Statement Tuning
Formatting Your SQL Statement
Running Full Table Scans
Identifying Other Performance Considerations
Using Cost-Based Optimization
Summary
Q&A
Workshop
Hour 24: Working with the System Catalog
Defining the System Catalog
Creating the System Catalog
Determining What Is Contained in the System Catalog
Identifying System Catalog Tables by Implementation
Querying the System Catalog
Updating System Catalog Objects
Summary
Q&A
Workshop
Hour 25: Bonus Workshop for the Road
The BIRDS Database
Predators of Birds
Photographers of Birds
Creating the New Tables
Workshop: Describing Your Tables
Workshop: Basic Queries
Workshop: Adding Tables
Workshop: Manipulating Data
Workshop: Joining Tables
Workshop: Comparison Operators
Workshop: Logical Operators
Workshop: Conjunctive Operators
Workshop: Arithmetic Operators
Workshop: Character Functions
Workshop: Aggregating Data
Workshop: GROUP BY and HAVING
Workshop: Composite Queries
Workshop: Creating Tables from Existing Tables
Workshop: Inserting Data into a Table from Another Table
Workshop: Creating Views
Workshop: Embedding Subqueries
Workshop: Creating Views from Subqueries
Workshop: Generating SQL Code from a SQL Statement
Summary
Workshop
Appendix A: Common SQL Commands
SQL Statements
SQL Query Clauses
Appendix B: Popular Vendor RDBMS Implementations
Installing the Oracle Database Software Used for Examples and Hands-On Exercises
Appendix C: Answers to Quizzes and Exercises
Hour 1, "Understanding the Relational Database and SQL"
Hour 2, "Exploring the Components of the SQL Language"
Hour 3, "Getting to Know Your Data"
Hour 4, "Setting Up Your Database"
Hour 5, "Understanding the Basics of Relational (SQL) Database Design"
Hour 6, "Defining Entities and Relationships"
Hour 7, "Normalizing Your Database"
Hour 8, "Defining Data Structures"
Hour 9, "Creating and Managing Database Objects"
Hour 10, "Manipulating Data"
Hour 11, "Managing Database Transactions"
Hour 12, "Introduction to Database Queries"
Hour 13, "Using Operators to Categorize Data"
Hour 14, "Joining Tables in Queries"
Hour 15, "Restructuring the Appearance of Data"
Hour 16, "Understanding Dates and Times"
Hour 17, "Summarizing Data Results from a Query"
Hour 18, "Using Subqueries to Define Unknown Data"
Hour 19, "Combining Multiple Queries into One"
Hour 20, "Creating and Using Views and Synonyms"
Hour 21, "Managing Database Users and Security"
Hour 22, "Using Indexes to Improve Performance"
Hour 23, "Improving Database Performance"
Hour 24, "Working with the System Catalog"
Hour 25, "Bonus Workshop for the Road"
9780137543120 TOC 11/8/2021
