SQL in 24 Hours, Sams Teach Yourself, Sixth Edition

SQL in 24 Hours, Sams Teach Yourself, Sixth Edition

Released December 2015
Publisher(s): Sams
ISBN: 9780134305905

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

Includes Coverage of Oracle and Microsoft SQL Implementations


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

This book’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, including views, transactions, web connections, and powerful Oracle and SQL Server extensions. Every lesson builds on what you’ve already learned, giving you a rock-solid foundation for real-world success.

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…
• Define efficient database structures and objects
• “Normalize” raw databases into logically organized tables
• Edit relational data and tables with DML
• 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
• Master powerful query optimization techniques
• Administer databases and manage users
• Secure databases and protect data
• Use views, synonyms, and the system catalog
• Extend SQL to the enterprise and Internet
• Master important Oracle and Microsoft extensions to ANSI SQL

Register your product at informit.com/register for convenient access to downloads, updates, and corrections as they become available.

Table of contents

  1. About This E-Book
  2. Title Page
  3. Copyright Page
  4. Contents at a Glance
  5. Table of Contents
  6. About the Authors
  7. Dedication
  8. Acknowledgments
  9. We Want to Hear from You!
  10. Reader Services
  11. Part I: An SQL Concepts Overview
    1. Hour 1. Welcome to the World of SQL
      1. SQL Definition and History
        1. What Is SQL?
        2. What Is ANSI SQL?
        3. The Current Standard: SQL-2011
        4. What Is a Database?
        5. The Relational Database
        6. Client/Server Technology
        7. Web-Based Database Systems
        8. Popular Database Vendors
      2. SQL Sessions
        1. CONNECT
        2. DISCONNECT and EXIT
      3. Types of SQL Commands
        1. Defining Database Structures
        2. Manipulating Data
        3. Selecting Data
        4. Data Control Language
        5. Data Administration Commands
        6. Transactional Control Commands
      4. Canary Airlines: The Database Used in This Book
        1. Table-Naming Standards
        2. A Look at Sample Data
        3. Examples and Exercises
      5. Summary
      6. Q&A
      7. Workshop
        1. Quiz
        2. Exercises
  12. Part II: Building Your Database
    1. Hour 2. Defining Data Structures
      1. What Is Data?
      2. Basic Data Types
        1. Fixed-Length Strings
        2. Varying-Length Strings
        3. Large Object Types
        4. Numeric Types
        5. Decimal Types
        6. Integers
        7. Floating-Point Decimals
        8. Date and Time Types
        9. Literal Strings
        10. NULL Data Types
        11. BOOLEAN Values
        12. User-Defined Types
        13. Domains
      3. Summary
      4. Q&A
      5. Workshop
        1. Quiz
        2. Exercises
    2. Hour 3. Managing Database Objects
      1. Database Objects and Schema
      2. Tables: The Primary Storage for Data
        1. Columns
        2. Rows
        3. The CREATE TABLE Statement
        4. Naming Conventions
        5. Creating a Table from an Existing Table
        6. Dropping Tables
      3. Integrity Constraints
        1. Primary Key Constraints
        2. Unique Constraints
        3. Foreign Key Constraints
        4. NOT NULL Constraints
        5. Check Constraints
        6. Dropping Constraints
      4. Summary
      5. Q&A
      6. Workshop
        1. Quiz
        2. Exercises
    3. Hour 4. The Normalization Process
      1. Normalizing a Database
        1. The Raw Database
        2. Logical Database Design
        3. Normal Forms
        4. Naming Conventions
        5. Benefits of Normalization
        6. Drawbacks of Normalization
      2. Denormalizing a Database
      3. Summary
      4. Q&A
      5. Workshop
        1. Quiz
        2. Exercises
    4. Hour 5. Manipulating Data
      1. Overview of Data Manipulation
      2. Populating Tables with New Data
        1. Inserting Data into a Table
        2. Inserting Data into Limited Columns of a Table
        3. Inserting Data from Another Table
        4. Inserting NULL Values
      3. Updating Existing Data
        1. Updating the Value of a Single Column
        2. Updating Multiple Columns in One or More Records
      4. Deleting Data from Tables
      5. Summary
      6. Q&A
      7. Workshop
        1. Quiz
        2. Exercises
    5. Hour 6. Managing Database Transactions
      1. What Is a Transaction?
      2. Controlling Transactions
        1. The COMMIT Command
        2. The ROLLBACK Command
        3. The SAVEPOINT Command
        4. The SET TRANSACTION Command
      3. Poor Transactional Control
      4. Summary
      5. Q&A
      6. Workshop
        1. Quiz
        2. Exercises
  13. Part III: Getting Effective Results from Queries
    1. Hour 7. Introduction to Database Queries
      1. The SELECT Statement
        1. The SELECT Clause
        2. The FROM Clause
        3. The WHERE Clause
        4. The ORDER BY Clause
      2. Case-Sensitivity
      3. Fundamentals of Query Writing
        1. Counting the Records in a Table
        2. Selecting Data from Another User’s Table
        3. Using Column Aliases
      4. Summary
      5. Q&A
      6. Workshop
        1. Quiz
        2. Exercises
    2. Hour 8. Using Operators to Categorize Data
      1. What Is an Operator in SQL?
      2. Comparison Operators
        1. Equality
        2. Non-Equality
        3. Less Than and Greater Than
        4. Combinations of Comparison Operators
      3. Logical Operators
        1. IS NULL
        2. BETWEEN
        3. IN
        4. LIKE
        5. EXISTS
        6. ALL, SOME, and ANY
      4. Conjunctive Operators
        1. AND
        2. OR
      5. Negative Operators
        1. NOT EQUAL
        2. NOT BETWEEN
        3. NOT IN
        4. NOT LIKE
        5. IS NOT NULL
        6. NOT EXISTS
      6. Arithmetic Operators
        1. Addition
        2. Subtraction
        3. Multiplication
        4. Division
        5. Arithmetic Operator Combinations
      7. Summary
      8. Q&A
      9. Workshop
        1. Quiz
        2. Exercises
    3. Hour 9. Summarizing Data Results from a Query
      1. Aggregate Functions
        1. COUNT
        2. SUM
        3. AVG
        4. MAX
        5. MIN
      2. Summary
      3. Q&A
      4. Workshop
        1. Quiz
        2. Exercises
    4. Hour 10. Sorting and Grouping Data
      1. Why Group Data?
      2. The GROUP BY Clause
        1. Group Functions
        2. Grouping Selected Data
        3. Creating Groups and Using Aggregate Functions
      3. GROUP BY Versus ORDER BY
      4. CUBE and ROLLUP Expressions
      5. The HAVING Clause
      6. Summary
      7. Q&A
      8. Workshop
        1. Quiz
        2. Exercises
    5. Hour 11. Restructuring the Appearance of Data
      1. ANSI Character Functions
      2. Common Character Functions
        1. The CONCAT Function
        2. The UPPER Function
        3. The LOWER Function
        4. The SUBSTR Function
        5. The TRANSLATE Function
        6. The REPLACE Function
        7. The LTRIM Function
        8. The RTRIM Function
      3. Miscellaneous Character Functions
        1. The LENGTH Function
        2. The ISNULL Function (NULL Value Checker)
        3. The COALESCE Function
        4. The LPAD Function
        5. The RPAD Function
        6. The ASCII Function
      4. Mathematical Functions
      5. Conversion Functions
        1. Converting Character Strings to Numbers
        2. Converting Numbers to Character Strings
      6. Combining Character Functions
      7. Summary
      8. Q&A
      9. Workshop
        1. Quiz
        2. Exercises
    6. Hour 12. Understanding Dates and Times
      1. How Is a Date Stored?
        1. Standard Data Types for the Date and Time
        2. DATETIME Elements
        3. Implementation-Specific Data Types
      2. Date Functions
        1. The Current Date
        2. Time Zones
        3. Adding Time to Dates
        4. Miscellaneous Date Functions
      3. Date Conversions
        1. Date Pictures
        2. Converting Dates to Character Strings
        3. Converting Character Strings to Dates
      4. Summary
      5. Q&A
      6. Workshop
        1. Quiz
        2. Exercises
  14. Part IV: Building Sophisticated Database Queries
    1. Hour 13. Joining Tables in Queries
      1. Selecting Data from Multiple Tables
      2. Understanding Joins
        1. Joins of Equality
        2. Using Table Aliases
        3. Joins of Non-Equality
        4. Outer Joins
        5. Self Joins
        6. Joining on Multiple Keys
      3. Join Considerations
        1. Using a Base Table
        2. The Cartesian Product
      4. Summary
      5. Q&A
      6. Workshop
        1. Quiz
        2. Exercises
    2. Hour 14. Using Subqueries to Define Unknown Data
      1. What Is a Subquery?
        1. Subqueries with the SELECT Statement
        2. Subqueries with the INSERT Statement
        3. Subqueries with the UPDATE Statement
        4. Subqueries with the DELETE Statement
      2. Embedded Subqueries
      3. Correlated Subqueries
      4. Subquery Performance
      5. Summary
      6. Q&A
      7. Workshop
        1. Quiz
        2. Exercises
    3. Hour 15. Combining Multiple Queries into One
      1. Single Queries Versus Compound Queries
      2. Compound Query Operators
        1. The UNION Operator
        2. The UNION ALL Operator
        3. The INTERSECT Operator
        4. The EXCEPT Operator
      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
        1. Quiz
        2. Exercises
  15. Part V: SQL Performance Tuning
    1. Hour 16. Using Indexes to Improve Performance
      1. What Is an Index?
      2. How Do Indexes Work?
      3. The CREATE INDEX Command
      4. Types of Indexes
        1. Single-Column Indexes
        2. Unique Indexes
        3. Composite Indexes
        4. Implicit Indexes
      5. When Should Indexes Be Considered?
      6. When Should Indexes Be Avoided?
      7. Altering an Index
      8. Dropping an Index
      9. Summary
      10. Q&A
      11. Workshop
        1. Quiz
        2. Exercises
    2. Hour 17. Improving Database Performance
      1. What Is SQL Statement Tuning?
      2. Database Tuning Versus SQL Statement Tuning
      3. Formatting Your SQL Statement
        1. Formatting a Statement for Readability
        2. Arranging Tables in the FROM Clause
        3. Ordering Join Conditions
        4. The Most Restrictive Condition
      4. Full Table Scans
      5. Other Performance Considerations
        1. Using the LIKE Operator and Wildcards
        2. Avoiding the OR Operator
        3. Avoiding the HAVING Clause
        4. Avoiding Large Sort Operations
        5. Using Stored Procedures
        6. Disabling Indexes During Batch Loads
      6. Cost-Based Optimization
      7. Summary
      8. Q&A
      9. Workshop
        1. Quiz
        2. Exercises
  16. Part VI: Using SQL to Manage Users and Security
    1. Hour 18. Managing Database Users
      1. User Management in the Database
        1. Types of Users
        2. Who Manages Users?
        3. The User’s Place in the Database
        4. How Does a User Differ from a Schema?
      2. The Management Process
        1. Creating Users
        2. Creating Schemas
        3. Dropping a Schema
        4. Altering Users
        5. User Sessions
        6. Removing User Access
      3. Tools Utilized by Database Users
      4. Summary
      5. Q&A
      6. Workshop
        1. Quiz
        2. Exercises
    2. Hour 19. Managing Database Security
      1. What Is Database Security?
      2. What Are Privileges?
        1. System Privileges
        2. Object Privileges
        3. Who Grants and Revokes Privileges?
      3. Controlling User Access
        1. The GRANT Command
        2. The REVOKE Command
        3. Controlling Access on Individual Columns
        4. The PUBLIC Database Account
        5. Groups of Privileges
      4. Controlling Privileges Through Roles
        1. The CREATE ROLE Statement
        2. The DROP ROLE Statement
        3. The SET ROLE Statement
      5. Summary
      6. Q&A
      7. Workshop
        1. Quiz
        2. Exercises
  17. Part VII: Summarized Data Structures
    1. Hour 20. Creating and Using Views and Synonyms
      1. What Is a View?
        1. Utilizing Views to Simplify Data Access
        2. Utilizing Views as a Form of Security
        3. Utilizing Views to Maintain Summarized Data
      2. Creating Views
        1. Creating a View from a Single Table
        2. Creating a View from Multiple Tables
        3. Creating a View from a View
        4. WITH CHECK OPTION
        5. Creating a Table from a View
        6. Views and the ORDER BY Clause
      3. Updating Data Through a View
      4. Dropping a View
      5. Performance Impact of Nested Views
      6. What Is a Synonym?
        1. Creating Synonyms
        2. Dropping Synonyms
      7. Summary
      8. Q&A
      9. Workshop
        1. Quiz
        2. Exercises
    2. Hour 21. Working with the System Catalog
      1. What Is the System Catalog?
      2. How Is the System Catalog Created?
      3. What Is Contained in the System Catalog?
        1. User Data
        2. Security Information
        3. Database Design Information
        4. Performance Statistics
      4. System Catalog Tables by Implementation
      5. Querying the System Catalog
      6. Updating System Catalog Objects
      7. Summary
      8. Q&A
      9. Workshop
        1. Quiz
        2. Exercises
  18. Part VIII: Applying SQL Fundamentals in Today’s World
    1. Hour 22. Advanced SQL Topics
      1. Cursors
        1. Opening a Cursor
        2. Fetching Data from a Cursor
        3. Closing a Cursor
      2. Stored Procedures and Functions
      3. Triggers
        1. The CREATE TRIGGER Statement
        2. The DROP TRIGGER Statement
      4. Dynamic SQL
      5. Call-Level Interface
      6. Using SQL to Generate SQL
      7. Direct Versus Embedded SQL
      8. Windowed Table Functions
      9. Working with XML
      10. Summary
      11. Q&A
      12. Workshop
        1. Quiz
        2. Exercises
    2. Hour 23. Extending SQL to the Enterprise, the Internet, and the Intranet
      1. SQL and the Enterprise
        1. The Back-End Application
        2. The Front-End Application
      2. Accessing a Remote Database
        1. ODBC
        2. JDBC
        3. OLE DB
        4. Vendor Connectivity Products
        5. Web Interface
      3. SQL and the Internet
        1. Making Data Available to Customers Worldwide
        2. Making Data Available to Employees and Privileged Customers
      4. SQL and the Intranet
      5. Summary
      6. Q&A
      7. Workshop
        1. Quiz
        2. Exercises
    3. Hour 24. Extensions to Standard SQL
      1. Various Implementations
        1. Differences Between Implementations
        2. Compliance with ANSI SQL
        3. Extensions to SQL
      2. Example Extensions
        1. Transact-SQL
        2. PL/SQL
        3. MySQL
      3. Interactive SQL Statements
      4. Summary
      5. Q&A
      6. Workshop
        1. Quiz
        2. Exercises
  19. Part IX: Appendixes
    1. Appendix A. Common SQL Commands
      1. SQL Statements
        1. ALTER TABLE
        2. COMMIT
        3. CREATE INDEX
        4. CREATE ROLE
        5. CREATE TABLE
        6. CREATE TABLE AS
        7. CREATE TYPE
        8. CREATE USER
        9. CREATE VIEW
        10. DELETE
        11. DROP INDEX
        12. DROP TABLE
        13. DROP USER
        14. DROP VIEW
        15. GRANT
        16. INSERT
        17. INSERT...SELECT
        18. REVOKE
        19. ROLLBACK
        20. SAVEPOINT
        21. SELECT
        22. UPDATE
      2. SQL Clauses
        1. SELECT
        2. FROM
        3. WHERE
        4. GROUP BY
        5. HAVING
        6. ORDER BY
    2. Appendix B. Installing Oracle and Microsoft SQL
      1. Windows Installation Instructions for Oracle
      2. Windows Installation Instructions for Microsoft SQL Server
    3. Appendix C. Answers to Quizzes and Exercises
      1. Hour 1, “Welcome to the World of SQL”
        1. Quiz Answers
        2. Exercise Answers
      2. Hour 2, “Defining Data Structures”
        1. Quiz Answers
        2. Exercise Answers
      3. Hour 3, “Managing Database Objects”
        1. Quiz Answers
        2. Exercise Answers
      4. Hour 4, “The Normalization Process”
        1. Quiz Answers
        2. Exercise Answers
      5. Hour 5, “Manipulating Data”
        1. Quiz Answers
        2. Exercise Answers
      6. Hour 6, “Managing Database Transactions”
        1. Quiz Answers
        2. Exercise Answers
      7. Hour 7, “Introduction to the Database Queries”
        1. Quiz Answers
        2. Exercise Answers
      8. Hour 8, “Using Operators to Categorize Data”
        1. Quiz Answers
        2. Exercise Answers
      9. Hour 9, “Summarizing Data Results from a Query”
        1. Quiz Answers
        2. Exercise Answers
      10. Hour 10, “Sorting and Grouping Data”
        1. Quiz Answers
        2. Exercises
      11. Hour 11, “Restructuring the Appearance of Data”
        1. Quiz Answers
        2. Exercise Answers
      12. Hour 12, “Understanding Dates and Times”
        1. Quiz Answers
        2. Exercise Answers
      13. Hour 13, “Joining Tables in Queries”
        1. Quiz Answers
        2. Exercise Answers
      14. Hour 14, “Using Subqueries to Define Unknown Data”
        1. Quiz Answers
        2. Exercise Answers
      15. Hour 15, “Combining Multiple Queries into One”
        1. Quiz Answers
        2. Exercise Answers
      16. Hour 16, “Using Indexes to Improve Performance”
        1. Quiz Answers
        2. Exercise Answers
      17. Hour 17, “Improving Database Performance”
        1. Quiz Answers
        2. Exercise Answers
      18. Hour 18, “Managing Database Users”
        1. Quiz Answers
        2. Exercise Answers
      19. Hour 19, “Managing Database Security”
        1. Quiz Answers
        2. Exercise Answers
      20. Hour 20, “Creating and Using Views and Synonyms”
        1. Quiz Answers
        2. Exercise Answers
      21. Hour 21, “Working with the System Catalog”
        1. Quiz Answers
        2. Exercise Answers
      22. Hour 22, “Advanced SQL Topics”
        1. Quiz Answers
        2. Exercise Answers
      23. Hour 23, “Extending SQL to the Enterprise, the Internet, and the Intranet”
        1. Quiz Answers
        2. Exercise Answers
      24. Hour 24, “Extensions to Standard SQL”
        1. Quiz Answers
        2. Exercise Answers
    4. Appendix D. Bonus Exercises
    5. Appendix E. Glossary
  20. Index
  21. Code Snippets

Product information

  • Title: SQL in 24 Hours, Sams Teach Yourself, Sixth Edition
  • Author(s):
  • Release date: December 2015
  • Publisher(s): Sams
  • ISBN: 9780134305905