You are previewing SQL: Visual QuickStart Guide.
O'Reilly logo
SQL: Visual QuickStart Guide

Book Description

Database management. It may sound daunting, but it doesn't have to be, even if you've never programmed before. SQL: Visual QuickStart Guide isn't an exhaustive guide to SQL written for aspiring programming experts. It's simply an invaluable resource for those who want a fast, easy way to harness information living in complex databases.

Because author Chris Fehily assumes no prior programming experience on your part (just a familiarity with your OS's file system), he spends the first few chapters laying out the basics, including clear explanations of the relational database model and SQL syntax. Then he jumps into step-by-step tasks designed to get you comfortable using SQL right away. You'll learn how to use SQL's most popular statements and commands to maintain, retrieve, and analyze database information, and to create and edit database objects. Each visually oriented task features the actual code and plenty of screenshots to keep you on track. Fehily takes a software-independent approach to teaching SQL, but includes tips for specific database software. You may not be a programming expert, but with SQL: VQS, who will be able to tell?

Table of Contents

  1. Copyright
  2. Special Thanks to:
  3. Introduction
  4. DBMS Specifics
    1. Running SQL Programs
    2. Microsoft Access
    3. Microsoft SQL Server
    4. Oracle
    5. MySQL
    6. PostgreSQL
  5. The Relational Model
    1. Tables, Columns, and Rows
    2. Primary Keys
    3. Foreign Keys
    4. Relationships
    5. Normalization
    6. The Sample Database
  6. SQL Basics
    1. SQL Syntax
    2. Data Types
    3. Character String Types
    4. Bit String Types
    5. Exact Numeric Types
    6. Approximate Numeric Types
    7. Datetime Types
    8. Interval Types
    9. Nulls
  7. Retrieving Data From a Table
    1. Retrieving Columns with SELECT and FROM
    2. Creating Column Aliases with AS
    3. Eliminating Duplicate Rows with DISTINCT
    4. Sorting Rows with ORDER BY
    5. Filtering Rows with WHERE
    6. Combining and Negating Conditions with AND, OR, and NOT
    7. Matching Patterns with LIKE
    8. Range Filtering with BETWEEN
    9. List Filtering with IN
    10. Testing for Nulls with IS NULL
  8. Operators and Functions
    1. Creating Derived Columns
    2. Performing Arithmetic Operations
    3. Determining the Order of Evaluation
    4. Concatenating Strings with ||
    5. Extracting a Substring with SUBSTRING()
    6. Changing String Case with UPPER() and LOWER()
    7. Trimming Characters with TRIM()
    8. Finding the Length of a String with CHARACTER_LENGTH()
    9. Finding Substrings with POSITION()
    10. Performing Date and Time Operations
    11. Getting the Current Date and Time
    12. Getting User Information
    13. Converting Data Types with CAST()
    14. Evaluating Conditional Values with CASE
    15. Checking for Nulls with COALESCE()
    16. Comparing Expressions with NULLIF()
  9. Summarizing and Grouping Data
    1. Using Aggregate Functions
    2. Finding a Minimum with MIN()
    3. Finding a Maximum with MAX()
    4. Calculating a Sum with SUM()
    5. Finding an Average with AVG()
    6. Counting Rows with COUNT()
    7. Aggregating Distinct Values with DISTINCT
    8. Grouping Rows with GROUP BY
    9. Filtering Groups with HAVING
  10. Retrieving Data From Multiple Tables
    1. Qualifying Column Names
    2. Creating Table Aliases with AS
    3. Using Joins
    4. Creating Joins with JOIN or WHERE
    5. Creating a Cross Join with CROSS JOIN
    6. Creating a Natural Join with NATURAL JOIN
    7. Creating an Inner Join with INNER JOIN
    8. Creating Outer Joins with OUTER JOIN
    9. Creating a Self-Join
    10. Combining Rows with UNION
    11. Finding Common Rows with INTERSECT
    12. Finding Different Rows with EXCEPT
  11. Subqueries
    1. Understanding Subqueries
    2. Subquery Syntax
    3. Subqueries vs. Joins
    4. Simple and Correlated Subqueries
    5. Qualifying Column Names in Subqueries
    6. Nulls in Subqueries
    7. Using Subqueries as Column Expressions
    8. Comparing a Subquery Value by Using a Comparison Operator
    9. Testing Set Membership with IN
    10. Comparing All Subquery Values with ALL
    11. Comparing Some Subquery Values with ANY
    12. Testing Existence with EXISTS
    13. Comparing Equivalent Queries
  12. Inserting, Updating, and Deleting Rows
    1. Displaying a Table’s Column Definitions
    2. Inserting Rows with INSERT
    3. Updating Rows with UPDATE
    4. Deleting Rows with DELETE
  13. Creating, Altering, and Dropping Tables
    1. Creating Tables
    2. Understanding Constraints
    3. Creating a New Table with CREATE TABLE
    4. Forbidding Nulls with NOT NULL
    5. Specifying a Default Value with DEFAULT
    6. Specifying a Primary Key with PRIMARY KEY
    7. Specifying a Foreign Key with FOREIGN KEY
    8. Forcing Unique Values with UNIQUE
    9. Adding a Check Constraint with CHECK
    10. Creating a Temporary Table with CREATE TEMPORARY TABLE
    11. Creating a New Table from an Existing One with SELECT INTO
    12. Altering a Table with ALTER TABLE
    13. Dropping a Table with DROP TABLE
  14. Indexes
    1. Creating an Index with CREATE INDEX
    2. Dropping an Index with DROP INDEX
  15. Views
    1. Creating a View with CREATE VIEW
    2. Retrieving Data Through a View
    3. Updating Data Through a View
    4. Dropping a View with DROP VIEW
  16. Transactions
    1. Executing a Transaction
  17.  
    1. Creating the Sample Database books