Log In
Or create an account ->
Imperial Library
Home
About
News
Upload
Forum
Help
Login/SignUp
Index
Cover Page
About This eBook
Title Page
Copyright Page
Pearson’s Commitment to Diversity, Equity, and Inclusion
Contents at a Glance
Table of Contents
About the Author
Dedication
Acknowledgments
We Want to Hear from You!
Reader Services
What This Book Is About
What You Need for This Book
Author’s Website
Hour 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
Appendixes
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”
Index
Code Snippets
← Prev
Back
Next →
← Prev
Back
Next →