SQL in 24 Hours, Sams Teach Yourself, 7th Edition

SQL in 24 Hours, Sams Teach Yourself, 7th Edition

by Ryan Stephens
Released September 2021
Publisher(s): Sams
ISBN: 9780137543168

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

In just 24 lessons of one hour or less, Sams Teach Yourself SQL in 24 Hours helps you use SQL to build effective databases, efficiently retrieve data, and manage everything from performance to security.

This Seventh Edition is thoroughly revised and reorganized for faster learning and a deeper understanding of modern SQL development. Based on standardized SQL throughout, it teaches using new sample code based on the free, easy-to-use Oracle Database Express (XE). You’ll find more hands-on examples than ever, culminating in an all-new Bonus Exercises Workshop with even more real-world practice.

This guide’s straightforward, step-by-step approach shows you how to work with database structures, objects, queries, tables, and more. In just hours, you will be applying advanced techniques, from transactions and joins to complex data retrieval using views and subqueries.

Step-by-step instructions carefully walk you through the most common SQL tasks. Practical, hands-on examples show you how to apply what you learn. Quizzes and exercises help you test your knowledge and stretch your skills. Notes and tips point out shortcuts and solutions.

Learn how to...

* Master core relational database concepts, SQL concepts, and language components

* Clearly understand your data

* Set up databases and plan efficient database designs

* Define entities and relationships, establish data structures, and create database objects

* “Normalize” raw databases into logically organized tables

* Edit relational data and tables and manage transactions

* Write effective, well-performing queries

* Categorize, summarize, sort, group, and restructure data

* Work with dates and times

* Join tables in queries, use subqueries, and combine multiple queries

* Optimize performance with indexes and other techniques

* Administer databases and manage users

* Secure databases and data

Table of contents

  1. Cover Page
  2. About This eBook
  3. Title Page
  4. Copyright Page
  5. Pearson’s Commitment to Diversity, Equity, and Inclusion
  6. Contents at a Glance
  7. Table of Contents
  8. About the Author
  9. Dedication
  10. Acknowledgments
  11. We Want to Hear from You!
  12. Reader Services
  13. What This Book Is About
  14. What You Need for This Book
  15. Author’s Website
  16. Hour 1: Understanding the Relational Database and SQL
    1. Thriving in a Data-Driven World
    2. Understanding the Relational Database
    3. The Relational Database Continues to Lead the Way
    4. Examples and Exercises
    5. Summary
    6. Q&A
    7. Workshop
  17. Hour 2: Exploring the Components of the SQL Language
    1. SQL Definition and History
    2. SQL: The Standard Language
    3. SQL Sessions
    4. Types of SQL Commands
    5. Summary
    6. Q&A
    7. Workshop
  18. Hour 3: Getting to Know Your Data
    1. The BIRD Database: Examples and Exercises in This Book
    2. How to Talk About the Data
    3. Entity Relationship Diagrams
    4. Examples and Exercises
    5. Summary
    6. Q&A
    7. Workshop
  19. Hour 4: Setting Up Your Database
    1. Locating the Files You Need
    2. Getting Set Up for Hands-on Exercises
    3. List of Data by Table
    4. Summary
    5. Q&A
    6. Workshop
  20. Hour 5: Understanding the Basics of Relational (SQL) Database Design
    1. Understanding What Database Design Has to Do with SQL
    2. The Database Design Process
    3. Choosing a Database Design Methodology
    4. Using a Simple Process to Think Through the Design of the BIRDS Database
    5. Logical Model vs. Physical Design
    6. Database Life Cycle
    7. Summary
    8. Q&A
    9. Workshop
  21. Hour 6: Defining Entities and Relationships
    1. Creating a Data Model Based on Your Data
    2. Defining Relationships
    3. Employing Referential Integrity
    4. Creating an Entity Relationship
    5. Summary
    6. Q&A
    7. Workshop
  22. Hour 7: Normalizing Your Database
    1. Defining Normalization
    2. Exploring the Most Common Normal Forms of the Normalization Process
    3. Denormalizing a Database
    4. Applying Normalization to Your Database
    5. Summary
    6. Q&A
    7. Workshop
  23. Hour 8: Defining Data Structures
    1. Defining Data
    2. Understanding Basic Data Types
    3. Using Data Types in the BIRDS Database
    4. Summary
    5. Q&A
    6. Workshop
  24. Hour 9: Creating and Managing Database Objects
    1. Database Objects and Schemas
    2. Tables: The Primary Storage for Data
    3. Integrity Constraints
    4. Summary
    5. Q&A
    6. Workshop
  25. Hour 10: Manipulating Data
    1. Getting an Overview of Data Manipulation
    2. Populating Tables with New Data
    3. Updating Existing Data
    4. Deleting Data from Tables
    5. Summary
    6. Q&A
    7. Workshop
  26. Hour 11: Managing Database Transactions
    1. Defining Transactions
    2. Controlling Transactions
    3. Dealing with Poor Transactional Control
    4. Summary
    5. Q&A
    6. Workshop
  27. Hour 12: Introduction to Database Queries
    1. Using the SELECT Statement
    2. Case Sensitivity
    3. Fundamentals of Query Writing
    4. Summary
    5. Q&A
    6. Workshop
  28. Hour 13: Using Operators to Categorize Data
    1. Defining an Operator in SQL
    2. Using Comparison Operators
    3. Using Logical Operators
    4. Using Conjunctive Operators
    5. Using Negative Operators
    6. Using Arithmetic Operators
    7. Summary
    8. Q&A
    9. Workshop
  29. Hour 14: Joining Tables in Queries
    1. Selecting Data from Multiple Tables
    2. Understanding Joins
    3. Join Considerations
    4. Summary
    5. Q&A
    6. Workshop
  30. Hour 15: Restructuring the Appearance of Data
    1. ANSI Character Functions
    2. Common Character Functions
    3. Miscellaneous Character Functions
    4. Mathematical Functions
    5. Conversion Functions
    6. Combined Character Functions
    7. Summary
    8. Q&A
    9. Workshop
  31. Hour 16: Understanding Dates and Times
    1. Understanding How a Date Is Stored
    2. Using Date Functions
    3. Converting Dates
    4. Summary
    5. Q&A
    6. Workshop
  32. Hour 17: Summarizing Data Results from a Query
    1. Using Aggregate Functions
    2. Grouping Data
    3. Using the GROUP BY Clause
    4. Understanding the Difference Between GROUP BY and ORDER BY
    5. Using CUBE and ROLLUP Expressions
    6. Using the HAVING Clause
    7. Summary
    8. Q&A
    9. Workshop
  33. Hour 18: Using Subqueries to Define Unknown Data
    1. Defining Subqueries
    2. Embedded Subqueries
    3. Using Correlated Subqueries
    4. Summary
    5. Q&A
    6. Workshop
  34. Hour 19: Combining Multiple Queries into One
    1. Differentiating Single Queries and Compound Queries
    2. Using Compound Query Operators
    3. Using ORDER BY with a Compound Query
    4. Using GROUP BY with a Compound Query
    5. Retrieving Accurate Data
    6. Summary
    7. Q&A
    8. Workshop
  35. Hour 20: Creating and Using Views and Synonyms
    1. Defining Views
    2. Creating Views
    3. Updating Data Through a View
    4. Dropping a View
    5. Understanding the Performance Impact of Nested Views
    6. Defining Synonyms
    7. Summary
    8. Q&A
    9. Workshop
  36. Hour 21: Managing Database Users and Security
    1. Managing Users in the Database
    2. Understanding the Management Process
    3. Maximizing Tools Utilized by Database Users
    4. Understanding Database Security
    5. Assigning Privileges
    6. Controlling User Access
    7. Controlling Privileges Through Roles
    8. Summary
    9. Q&A
    10. Workshop
  37. Hour 22: Using Indexes to Improve Performance
    1. Defining an Index
    2. Understanding How Indexes Work
    3. Using the CREATE INDEX Command
    4. Identifying Types of Indexes
    5. Knowing When to Consider Using an Index
    6. Knowing When to Avoid Indexes
    7. Altering an Index
    8. Dropping an Index
    9. Summary
    10. Q&A
    11. Workshop
  38. Hour 23: Improving Database Performance
    1. Defining SQL Statement Tuning
    2. Comparing Database Tuning and SQL Statement Tuning
    3. Formatting Your SQL Statement
    4. Running Full Table Scans
    5. Identifying Other Performance Considerations
    6. Using Cost-Based Optimization
    7. Summary
    8. Q&A
    9. Workshop
  39. Hour 24: Working with the System Catalog
    1. Defining the System Catalog
    2. Creating the System Catalog
    3. Determining What Is Contained in the System Catalog
    4. Identifying System Catalog Tables by Implementation
    5. Querying the System Catalog
    6. Updating System Catalog Objects
    7. Summary
    8. Q&A
    9. Workshop
  40. Hour 25: Bonus Workshop for the Road
    1. The BIRDS Database
    2. Predators of Birds
    3. Photographers of Birds
    4. Creating the New Tables
    5. Workshop: Describing Your Tables
    6. Workshop: Basic Queries
    7. Workshop: Adding Tables
    8. Workshop: Manipulating Data
    9. Workshop: Joining Tables
    10. Workshop: Comparison Operators
    11. Workshop: Logical Operators
    12. Workshop: Conjunctive Operators
    13. Workshop: Arithmetic Operators
    14. Workshop: Character Functions
    15. Workshop: Aggregating Data
    16. Workshop: GROUP BY and HAVING
    17. Workshop: Composite Queries
    18. Workshop: Creating Tables from Existing Tables
    19. Workshop: Inserting Data into a Table from Another Table
    20. Workshop: Creating Views
    21. Workshop: Embedding Subqueries
    22. Workshop: Creating Views from Subqueries
    23. Workshop: Generating SQL Code from a SQL Statement
    24. Summary
    25. Workshop
  41. Appendixes
    1. Appendix A: Common SQL Commands
      1. SQL Statements
      2. SQL Query Clauses
    2. Appendix B: Popular Vendor RDBMS Implementations
      1. Installing the Oracle Database Software Used for Examples and Hands-On Exercises
    3. Appendix C: Answers to Quizzes and Exercises
      1. Hour 1, “Understanding the Relational Database and SQL”
      2. Hour 2, “Exploring the Components of the SQL Language”
      3. Hour 3, “Getting to Know Your Data”
      4. Hour 4, “Setting Up Your Database”
      5. Hour 5, “Understanding the Basics of Relational (SQL) Database Design”
      6. Hour 6, “Defining Entities and Relationships”
      7. Hour 7, “Normalizing Your Database”
      8. Hour 8, “Defining Data Structures”
      9. Hour 9, “Creating and Managing Database Objects”
      10. Hour 10, “Manipulating Data”
      11. Hour 11, “Managing Database Transactions”
      12. Hour 12, “Introduction to Database Queries”
      13. Hour 13, “Using Operators to Categorize Data”
      14. Hour 14, “Joining Tables in Queries”
      15. Hour 15, “Restructuring the Appearance of Data”
      16. Hour 16, “Understanding Dates and Times”
      17. Hour 17, “Summarizing Data Results from a Query”
      18. Hour 18, “Using Subqueries to Define Unknown Data”
      19. Hour 19, “Combining Multiple Queries into One”
      20. Hour 20, “Creating and Using Views and Synonyms”
      21. Hour 21, “Managing Database Users and Security”
      22. Hour 22, “Using Indexes to Improve Performance”
      23. Hour 23, “Improving Database Performance”
      24. Hour 24, “Working with the System Catalog”
      25. Hour 25, “Bonus Workshop for the Road”
  42. Index
  43. Code Snippets

Product information

  • Title: SQL in 24 Hours, Sams Teach Yourself, 7th Edition
  • Author(s): Ryan Stephens
  • Release date: September 2021
  • Publisher(s): Sams
  • ISBN: 9780137543168