Beginning SQL

Beginning SQL

by Paul Wilton, John W. Colby
Released March 2005
Publisher(s): Wrox
ISBN: 9780764577321

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

  • Taking readers through the basics of the language, right up to some more advanced topics, this book is a practical, hands-on resource and aims to keep the reader involved at all times

  • Focuses on the SQL standard and is loaded with detailed examples and code; each chapter includes practice exercises that readers can challenge themselves with before looking at the sample solutions in the appendix

  • Paul Wilton is a successful Wrox "Beginning" book author and is an ideal author to write for those who want a firm grasp of standard SQL before learning the details specific to a particular database product

  • SQL is an international standard for manipulating data in databases and is used by database programmers in all major database systems: Microsoft, IBM, Oracle, MySQL, and many others

Table of contents

  1. Copyright
  2. About the Authors
  3. Credits
  4. Acknowledgments
  5. Introduction
    1. Who This Book Is For
    2. What This Book Covers
    3. How This Book Is Structured
    4. What You Need to Use This Book
    5. Conventions
        1. How It Works
    6. Source Code
    7. Errata
    8. p2p.wrox.com
  6. 1. Introduction to SQL
    1. 1.1. A Brief History of Databases
      1. 1.1.1. Identifying Databases
        1. 1.1.1.1. Why and When to Use a Database
        2. 1.1.1.2. Database Management Systems Used in This Book
    2. 1.2. Structured Query Language (SQL)
      1. 1.2.1. Introducing SQL Queries
      2. 1.2.2. Comparing SQL to Other Programming Languages
      3. 1.2.3. Understanding SQL Standards
    3. 1.3. Database Creation
      1. 1.3.1. Organizing a Relational Database
        1. 1.3.1.1. SQL Syntax
        2. 1.3.1.2. Creating a Database
        3. 1.3.1.3. Understanding Data Types
          1. 1.3.1.3.1. Characters
          2. 1.3.1.3.2. Numerical Data
          3. 1.3.1.3.3. Date and Time
    4. 1.4. Creating, Altering, and Deleting Tables
      1. 1.4.1. Creating a Table
      2. 1.4.2. Altering an Existing Table
      3. 1.4.3. Deleting an Existing Table
    5. 1.5. Good Database Design
      1. 1.5.1. Obtaining and Analyzing Your Data Needs
      2. 1.5.2. Dividing Data Logically
      3. 1.5.3. Selecting Correct Data Types
      4. 1.5.4. Using a Primary Key
    6. 1.6. Creating the Example Database
    7. 1.7. Summary
    8. 1.8. Exercises
  7. 2. Entering Information
    1. 2.1. Inserting New Data
    2. 2.2. Inserting Data into the Case Study Database
    3. 2.3. Updating Data
      1. 2.3.1. The WHERE Clause
        1. 2.3.1.1. The Logical Operators AND and OR
        2. 2.3.1.2. How It Works
    4. 2.4. Deleting Data
    5. 2.5. Summary
    6. 2.6. Exercises
  8. 3. Extracting Information
    1. 3.1. The SELECT Statement
      1. 3.1.1. Returning Only Distinct Rows
      2. 3.1.2. Using Aliases
      3. 3.1.3. Filtering Results with the WHERE Clause
        1. 3.1.3.1. How It Works
    2. 3.2. Logical Operators and Operator Precedence
      1. 3.2.1. Introducing Operator Precedence
        1. 3.2.1.1. How It Works
      2. 3.2.2. Using Logical Operators
        1. 3.2.2.1. NOT Operator
        2. 3.2.2.2. BETWEEN Operator
        3. 3.2.2.3. How It Works
        4. 3.2.2.4. LIKE Operator
        5. 3.2.2.5. How It Works
        6. 3.2.2.6. IN Operator
        7. 3.2.2.7. How It Works
    3. 3.3. Ordering Results with ORDER BY
      1. 3.3.1.
        1. 3.3.1.1. How It Works
      2. 3.3.2. Joining Columns—Concatenation
        1. 3.3.2.1. MS SQL Server and MS Access
        2. 3.3.2.2. Oracle and IBM DB2
        3. 3.3.2.3. MySQL
    4. 3.4. Selecting Data from More Than One Table
      1. 3.4.1. Using Brackets around Inner Joins in MS Access
      2. 3.4.2. SQL Is Set-Based
        1. 3.4.2.1. How It Works
    5. 3.5. Introducing NULL Data
    6. 3.6. Summary
    7. 3.7. Exercises
  9. 4. Advanced Database Design
    1. 4.1. Normalization
      1. 4.1.1. First Normal Form
      2. 4.1.2. Second Normal Form
      3. 4.1.3. Third Normal Form
    2. 4.2. Ensuring Data Validity with Constraints
      1. 4.2.1. NOT NULL Constraint
      2. 4.2.2. UNIQUE Constraint
      3. 4.2.3. CHECK Constraint
        1. 4.2.3.1. How It Works
      4. 4.2.4. Primary Key and PRIMARY KEY Constraint
        1. 4.2.4.1. How It Works
      5. 4.2.5. Foreign Key
        1. 4.2.5.1. How It Works
    3. 4.3. Speeding Up Results with Indexes
      1. 4.3.1. Improving the Design of the Film Club Database
        1. 4.3.1.1. Reexamining the Film Club Database Structure
      2. 4.3.2. Improving Data Validation and Efficiency
      3. 4.3.3. Tips for Designing a Better Database
    4. 4.4. Summary
    5. 4.5. Exercises
  10. 5. Manipulating Data
    1. 5.1. Understanding SQL Arithmetic
      1. 5.1.1. Basic Math Operators
      2. 5.1.2. Common Math Functions
        1. 5.1.2.1. The ABS() Function
        2. 5.1.2.2. The POWER() Function
        3. 5.1.2.3. The SQRT() Function
        4. 5.1.2.4. The RAND() Function
      3. 5.1.3. Rounding Numbers
        1. 5.1.3.1. The CEILING() Function
        2. 5.1.3.2. The FLOOR() Function
        3. 5.1.3.3. The ROUND() Function
    2. 5.2. Introducing String Functions
      1. 5.2.1. The SUBSTRING() Function
      2. 5.2.2. Case Conversion Functions
      3. 5.2.3. The REVERSE() Function
      4. 5.2.4. The TRIM() Functions
      5. 5.2.5. The LENGTH() Function
      6. 5.2.6. The SOUNDEX() and DIFFERENCE() Functions
      7. 5.2.7. Date Functions
    3. 5.3. Converting Different Data Types
    4. 5.4. Re-examining NULL
      1. 5.4.1. NULLs and Math
      2. 5.4.2. NULLs and Strings
      3. 5.4.3. The COALESCE() Function
    5. 5.5. Using INSERT INTO with the SELECT Statement
    6. 5.6. Summary
    7. 5.7. Exercises
  11. 6. Grouping and Aggregating Data
    1. 6.1. Grouping Results
    2. 6.2. Summarizing and Aggregating Data
      1. 6.2.1. Counting Results
        1. 6.2.1.1. How It Works
      2. 6.2.2. Adding Results
        1. 6.2.2.1. How It Works
      3. 6.2.3. Averaging Results
        1. 6.2.3.1. How It Works
      4. 6.2.4. MAX() and MIN() in Results
        1. 6.2.4.1. How It Works
    3. 6.3. Using the HAVING Clause with GROUP BY Statements
      1. 6.3.1.
        1. 6.3.1.1. How It Works
    4. 6.4. Summary
    5. 6.5. Exercises
  12. 7. Selecting Data from Different Tables
    1. 7.1. Joins Revisited
      1. 7.1.1. Inner Joins: An In-Depth Look
        1. 7.1.1.1. Equijoins and Non-equijoins
        2. 7.1.1.2. Multiple Joins and Multiple Conditions
        3. 7.1.1.3. Cross Joins
        4. 7.1.1.4. Self-Joins
      2. 7.1.2. Outer Joins
        1. 7.1.2.1. Left Outer Join
        2. 7.1.2.2. Right Outer Join
        3. 7.1.2.3. Full Outer Join
    2. 7.2. Combining Results Sets with the UNION Operator
    3. 7.3. Summary
    4. 7.4. Exercises
  13. 8. Queries within Queries
    1. 8.1. Subquery Terminology
    2. 8.2. Subqueries in a SELECT List
    3. 8.3. Subqueries in the WHERE Clause
    4. 8.4. Operators in Subqueries
      1. 8.4.1. Revisiting the IN Operator
      2. 8.4.2. Using the ANY, SOME, and ALL Operators
        1. 8.4.2.1. ANY and SOME Operators
        2. 8.4.2.2. ALL Operator
      3. 8.4.3. Using the EXISTS Operator
        1. 8.4.3.1. How It Works
      4. 8.4.4. Using the HAVING Clause with Subqueries
    5. 8.5. Correlated Subquery
    6. 8.6. Subqueries Used with Other Statements
      1. 8.6.1. Using Subqueries with the INSERT Statement
        1. 8.6.1.1. How It Works
      2. 8.6.2. Using Subqueries with the UPDATE Statement
      3. 8.6.3. Using Subqueries with the DELETE FROM Statement
    7. 8.7. Summary
    8. 8.8. Exercises
  14. 9. Advanced Queries
    1. 9.1. Updating the Database
    2. 9.2. Tackling Difficult Queries
      1. 9.2.1. Work Out What You Want, What You Really, Really Want
        1. 9.2.1.1. Choosing the SELECT Column List
        2. 9.2.1.2. Creating the FROM Clause
        3. 9.2.1.3. How It Works
    3. 9.3. Top Tips for Efficient Queries
    4. 9.4. Summary
    5. 9.5. Exercises
  15. 10. Views
    1. 10.1. Introducing Views
      1. 10.1.1. Creating Views
        1. 10.1.1.1. How It Works
      2. 10.1.2. Types of Views
        1. 10.1.2.1. Table Join Views
        2. 10.1.2.2. Base View
        3. 10.1.2.3. Row Views
        4. 10.1.2.4. Field Views
        5. 10.1.2.5. Filtered Windowed Views
        6. 10.1.2.6. Summary Views
      3. 10.1.3. Updating Views
        1. 10.1.3.1. Update Restrictions
        2. 10.1.3.2. Check Option
      4. 10.1.4. Dropping Views
    2. 10.2. Summary
    3. 10.3. Exercises
  16. 11. Transactions
    1. 11.1. Introducing Transactions
      1. 11.1.1. Example Data
        1. 11.1.1.1. How It Works
      2. 11.1.2. ANSI Model
        1. 11.1.2.1. COMMIT
        2. 11.1.2.2. ROLLBACK
      3. 11.1.3. Transact-SQL
        1. 11.1.3.1. BEGIN TRANSACTION
        2. 11.1.3.2. COMMIT TRANSACTION
        3. 11.1.3.3. SAVE TRANSACTION
        4. 11.1.3.4. ROLLBACK TRANSACTION
    2. 11.2. Transaction Logs
    3. 11.3. Locks
      1. 11.3.1. Locking Granularity
        1. 11.3.1.1. Database
        2. 11.3.1.2. Table
        3. 11.3.1.3. Page
        4. 11.3.1.4. Row
        5. 11.3.1.5. Column
      2. 11.3.2. Locking Levels
        1. 11.3.2.1. Shared
        2. 11.3.2.2. Exclusive
        3. 11.3.2.3. Deadlocks
      3. 11.3.3. Setting Lock Parameters
        1. 11.3.3.1. Lock Size
        2. 11.3.3.2. Number of Locks
        3. 11.3.3.3. Escalation
        4. 11.3.3.4. Timeout
    4. 11.4. Isolation Levels
      1. 11.4.1. SET TRANSACTION
      2. 11.4.2. SERIALIZABLE
      3. 11.4.3. REPEATABLE READ
      4. 11.4.4. READ COMMITTED
      5. 11.4.5. READ UNCOMMITTED
      6. 11.4.6. Versioning
    5. 11.5. Problem Examples
      1. 11.5.1. The Lost Update
      2. 11.5.2. The Uncommitted Data
      3. 11.5.3. The Inconsistent Data
      4. 11.5.4. The Phantom Insert
    6. 11.6. Revisiting the Example Code
      1. 11.6.1.
        1. 11.6.1.1. How It Works
    7. 11.7. Summary
    8. 11.8. Exercises
  17. 12. SQL Security
    1. 12.1. Security Concepts
      1. 12.1.1. User IDs
        1. 12.1.1.1. Creating User IDs
        2. 12.1.1.2. Alter User
        3. 12.1.1.3. Drop User
        4. 12.1.1.4. How It Works
      2. 12.1.2. Group IDs (Roles)
        1. 12.1.2.1. How It Works
      3. 12.1.3. Objects
      4. 12.1.4. Privileges
        1. 12.1.4.1. Extended Privileges
        2. 12.1.4.2. The USAGE Privilege
        3. 12.1.4.3. Ownership
      5. 12.1.5. Views and Security
        1. 12.1.5.1. Vertical and Horizontal Views
        2. 12.1.5.2. Grouped Views
        3. 12.1.5.3. Limitations on Views
      6. 12.1.6. Granting Privileges
        1. 12.1.6.1. Tables and Views
        2. 12.1.6.2. How It Works
        3. 12.1.6.3. Columns
        4. 12.1.6.4. The GRANT OPTION Clause
      7. 12.1.7. Revoking Privileges
        1. 12.1.7.1. The REVOKE Statement
        2. 12.1.7.2. Revoking GRANT
        3. 12.1.7.3. The CASCADE and RESTRICT Options
    2. 12.2. Summary
    3. 12.3. Exercises
  18. 13. Database Tuning
    1. 13.1. Tuning Hardware
      1. 13.1.1. Workstations
      2. 13.1.2. Database Files
      3. 13.1.3. Processors
      4. 13.1.4. Gigahertz Networks or Compartmentalized Networks
      5. 13.1.5. Caches
        1. 13.1.5.1. Processor Cache
        2. 13.1.5.2. Hard Disk Cache
        3. 13.1.5.3. Database Cache
    2. 13.2. Tuning SQL
      1. 13.2.1. What Does SQL Tuning Mean?
      2. 13.2.2. Why Do You Do It?
      3. 13.2.3. How Do You Do It?
        1. 13.2.3.1. Indexes—What Are They?
        2. 13.2.3.2. How It Works
        3. 13.2.3.3. Indexes—When They Help, Hurt, or Don't Matter
        4. 13.2.3.4. Table Scans—What Are They?
        5. 13.2.3.5. When Table Scans Help, Hurt, or Don't Matter
    3. 13.3. Tuning Tips
    4. 13.4. Summary
    5. 13.5. Exercises
  19. A. Exercise Answers
    1. A.1. Chapter 1
      1. A.1.1. Exercise 1 Solution
      2. A.1.2. Exercise 2 Solution
    2. A.2. Chapter 2
      1. A.2.1. Exercise 1 Solution
        1. A.2.1.1. Non-Oracle Solution
        2. A.2.1.2. Oracle Solution
      2. A.2.2. Exercise 2 Solution
      3. A.2.3. Exercise 3 Solution
    3. A.3. Chapter 3
      1. A.3.1. Exercise 1 Solution
      2. A.3.2. Exercise 2 Solution
      3. A.3.3. Exercise 3 Solution
      4. A.3.4. Exercise 4 Solution
    4. A.4. Chapter 4
      1. A.4.1. Exercise 1 Solution
      2. A.4.2. Exercise 2 Solution
    5. A.5. Chapter 5
      1. A.5.1. Exercise 1 Solution
      2. A.5.2. Exercise 2 Solution
    6. A.6. Chapter 6
      1. A.6.1. Exercise 1 Solution
      2. A.6.2. Exercise 2 Solution
    7. A.7. Chapter 7
      1. A.7.1. Exercise 1 Solution
      2. A.7.2. Exercise 2 Solution
    8. A.8. Chapter 8
      1. A.8.1. Exercise 1 Solution
      2. A.8.2. Exercise 2 Solution
    9. A.9. Chapter 9
      1. A.9.1. Exercise 1 Solution
    10. A.10. Chapter 10
      1. A.10.1. Exercise 1 Solution
      2. A.10.2. Exercise 2 Solution
      3. A.10.3. Exercise 3 Solution
      4. A.10.4. Exercise 4 Solution
    11. A.11. Chapter 11
      1. A.11.1. Exercise 1 Solution
      2. A.11.2. Exercise 2 Solution
      3. A.11.3. Exercise 3 Solution
    12. A.12. Chapter 12
      1. A.12.1. Exercise 1 Solution
      2. A.12.2. Exercise 2 Solution
      3. A.12.3. Exercise 3 Solution
      4. A.12.4. Exercise 4 Solution
      5. A.12.5. Exercise 5 Solution
      6. A.12.6. Exercise 6 Solution
    13. A.13. Chapter 13
      1. A.13.1. Exercise 1 Solution
      2. A.13.2. Exercise 2 Solution
      3. A.13.3. Exercise 3 Solution
      4. A.13.4. Exercise 4 Solution
      5. A.13.5. Exercise 5 Solution
  20. B. Setting Up and Using the Five Database Systems
    1. B.1. Installing and Using MySQL Version 4.1
      1. B.1.1. Installing the MySQL Tools
    2. B.2. Installing and Using IBM DB2
      1. B.2.1. Using the Command Center Tool
    3. B.3. Installing and Using Microsoft Access XP
    4. B.4. Installing and Using Microsoft SQL Server 2000
      1. B.4.1. Using SQL Server Tools
    5. B.5. Installing and Using Oracle 10g
      1. B.5.1. Using Oracle's SQL*Plus Tool
  21. C. Initial Data Setup
    1. C.1. Category Table
    2. C.2. MemberDetails Table
      1. C.2.1. Database Systems Other Than Oracle
      2. C.2.2. Oracle
    3. C.3. Location Table
    4. C.4. FavCategory Table
    5. C.5. Films Table
    6. C.6. Attendance Table
      1. C.6.1. Database Systems Other Than Oracle
      2. C.6.2. Oracle

Product information

  • Title: Beginning SQL
  • Author(s): Paul Wilton, John W. Colby
  • Release date: March 2005
  • Publisher(s): Wrox
  • ISBN: 9780764577321