SQL: Visual Quickstart Guide, Second Edition

SQL: Visual Quickstart Guide, Second Edition

by Chris Fehily
Released May 2005
Publisher(s): Peachpit Press
ISBN: 9780321334176

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

As dynamic, database-driven Web sites become the standard, more and more nonprogrammers just like yourself are being forced to work with database information and create and edit database objects. With SQL and this task-based guide to it, you can do it too--no programming experience required!

After going over the relational database model and SQL syntax in the first few chapters, veteran author Chris Fehily launches into the tasks that will get you comfortable with SQL fast. In addition to explaining SQL basics, this updated reference covers the ANSI SQL:2003 standard and contains a wealth of brand-new information, including a new chapter on set operations and common tasks, well-placed optimization tips to make your queries run fast, sidebars on advanced topics, and added IBM DB2 coverage.

Best of all, the book's examples were tested on the latest versions of Microsoft Access, Microsoft SQL Server, Oracle, IBM DB2, MySQL, and PostgreSQL. On the companion Web site, you can download the SQL scripts and sample database for all these systems and put your knowledge to work immediately on a real database..

Table of contents

  1. Copyright
    1. Dedication
    2. Special thanks to…
  2. Introduction
    1. About SQL
    2. About This Book
      1. Audience
      2. Typographic conventions
      3. Syntax conventions
      4. Using SQL with a specific DBMS
    3. What You'll Need
  3. 1. DBMS Specifics
    1. Running SQL Programs
    2. Microsoft Access
      1. To turn on ANSI-92 SQL query mode for a database
      2. To run an SQL statement
    3. Microsoft SQL Server
      1. To use SQL Query Analyzer
      2. To use the osql command-line utility interactively
      3. To use the osql command-line utility in script mode
      4. To exit the osql command-line utility
      5. To show osql command-line options
    4. Oracle
      1. To use SQL*Plus
      2. To use the sqlplus command-line utility interactively
      3. To use the sqlplus command-line utility in script mode
      4. To exit the sqlplus command-line utility
      5. To show sqlplus command-line options
    5. IBM DB2
      1. To use Command Center
      2. To start the db2 command-line processor in Windows
      3. To use the db2 command-line processor interactively
      4. To use the db2 command-line processor in script mode
      5. To exit the db2 command-line utility
      6. To show db2 command-line options
    6. MySQL
      1. To use the mysql command-line utility interactively
      2. To use the mysql command-line utility in script mode
      3. To exit the mysql command-line utility
      4. To show mysql command-line options
    7. PostgreSQL
      1. To use the psql command-line utility interactively
      2. To use the psql command-line utility in script mode
      3. To exit the psql command-line utility
      4. To show psql command-line options
  4. 2. The Relational Model
    1. Tables, Columns, and Rows
      1. Tables
      2. Columns
      3. Rows
    2. Primary Keys
    3. Foreign Keys
    4. Relationships
      1. One-to-one
      2. One-to-many
      3. Many-to-many
    5. Normalization
      1. First normal form
      2. Second normal form
      3. Third normal form
      4. Other normal forms
    6. The Sample Database
      1. The table authors
      2. The table publishers
      3. The table titles
      4. The table title_authors
      5. The table royalties
  5. 3. SQL Basics
    1. SQL Syntax
    2. SQL Standards and Conformance
    3. Identifiers
    4. Data Types
    5. Character String Types
    6. Binary Large Object Type
    7. Exact Numeric Types
    8. Approximate Numeric Types
    9. Boolean Type
    10. Datetime Types
    11. Interval Types
    12. Other Data Types
    13. Nulls
  6. 4. Retrieving Data From a Table
    1. Retrieving Columns with SELECT and FROM
      1. To retrieve a column from a table
      2. To retrieve multiple columns from a table
      3. To retrieve all columns from a table
    2. Creating Column Aliases with AS
      1. To create column aliases
    3. Eliminating Duplicate Rows with DISTINCT
      1. To eliminate duplicate rows
    4. Sorting Rows with ORDER BY
      1. To sort by a column
      2. To sort by multiple columns
      3. To sort by relative column positions
    5. Filtering Rows with WHERE
      1. To filter rows by making a comparison
    6. Combining and Negating Conditions with AND, OR, and NOT
      1. The AND operator
      2. The OR operator
      3. The NOT operator
      4. Using AND, OR, and NOT together
    7. Matching Patterns with LIKE
      1. To filter rows by matching a pattern
      2. To match a wildcard character
    8. Range Filtering with BETWEEN
      1. To filter rows by using a range
    9. List Filtering with IN
      1. To filter rows by using a list
    10. Testing for Nulls with IS NULL
      1. To retrieve rows with nulls or non-null values
  7. 5. Operators and Functions
    1. Creating Derived Columns
    2. Performing Arithmetic Operations
      1. To change the sign of a number
      2. To add, subtract, multiply, or divide
    3. Determining the Order of Evaluation
    4. Concatenating Strings with ||
    5. To concatenate strings
    6. Extracting a Substring with SUBSTRING()
      1. To extract a substring
    7. Changing String Case with UPPER() and LOWER()
      1. To convert a string to uppercase or lowercase
    8. Trimming Characters with TRIM()
      1. To trim spaces from a string
      2. To trim characters from a string
    9. Finding the Length of a String with CHARACTER_LENGTH()
      1. To find the length of a string
    10. Finding Substrings with POSITION()
      1. To find a substring
    11. Performing Datetime and Interval Arithmetic
      1. To extract part of a datetime or interval
    12. Getting the Current Date and Time
      1. To get the current date and time
    13. Getting User Information
      1. To get the current user
    14. Converting Data Types with CAST()
      1. To convert one data type to another
    15. Evaluating Conditional Values with CASE
      1. To use a simple CASE expression
      2. To use a searched CASE expression
    16. Checking for Nulls with COALESCE()
      1. To return the first non-null value
    17. Comparing Expressions with NULLIF()
      1. To return a null if two expressions are equivalent
  8. 6. Summarizing and Grouping Data
    1. Using Aggregate Functions
    2. Creating Aggregate Expressions
    3. Finding a Minimum with MIN()
      1. To find the minimum of a set of values
    4. Finding a Maximum with MAX()
      1. To find the maximum of a set of values
    5. Calculating a Sum with SUM()
      1. To calculate the sum of a set of values
    6. Calculating an Average with AVG()
      1. To calculate the average of a set of values
    7. Counting Rows with COUNT()
      1. To count non-null rows
      2. To count all rows, including nulls
    8. Aggregating Distinct Values with DISTINCT
      1. To calculate the sum of a set of distinct values
      2. To calculate the average of a set of distinct values
      3. To count distinct non-null rows
    9. Grouping Rows with GROUP BY
      1. To group rows
    10. Filtering Groups with HAVING
      1. To filter groups
  9. 7. Joins
    1. Qualifying Column Names
      1. To qualify a column name
    2. Creating Table Aliases with AS
      1. To create a table alias
    3. Using Joins
    4. Creating Joins with JOIN or WHERE
      1. To create a join by using JOIN
      2. To create a join by using WHERE
    5. Creating a Cross Join with CROSS JOIN
      1. To create a cross join
    6. Creating a Natural Join with NATURAL JOIN
      1. To create a natural join
    7. Creating an Inner Join with INNER JOIN
      1. To create an inner join
    8. Creating Outer Joins with OUTER JOIN
      1. To create a left outer join
      2. To create a right outer join
      3. To create a full outer join
    9. Creating a Self-Join
      1. To create a self-join
  10. 8. Subqueries
    1. Understanding Subqueries
    2. Subquery Syntax
    3. Subqueries vs. Joins
    4. Simple and Correlated Subqueries
      1. Simple subqueries
      2. 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
      1. To compare a subquery value
    9. Testing Set Membership with IN
      1. To test set membership
    10. Comparing All Subquery Values with ALL
      1. To compare all subquery values
    11. Comparing Some Subquery Values with ANY
      1. To compare some subquery values
    12. Testing Existence with EXISTS
      1. To test existence
    13. Comparing Equivalent Queries
  11. 9. Set Operations
    1. Combining Rows with UNION
      1. To combine rows
    2. Finding Common Rows with INTERSECT
      1. To find common rows
    3. Finding Different Rows with EXCEPT
      1. To find different rows
    4. Calculating Running Statistics
    5. Generating Sequences
      1. To define a sequence generator
    6. Finding Sequences, Runs, and Regions
    7. Limiting the Number of Rows Returned
      1. Microsoft Access
      2. Microsoft SQL Server
      3. Oracle
      4. DB2
      5. MySQL
      6. PostgreSQL
    8. Assigning Ranks
    9. Calculating a Trimmed Mean
    10. Picking Random Rows
    11. Handling Duplicates
  12. 10. Inserting, Updating, and Deleting Rows
    1. Displaying Table Definitions
      1. To display table definitions in Microsoft Access
      2. To display table definitions in Microsoft SQL Server
      3. To display table definitions in Oracle
      4. To display table definitions in IBM DB2
      5. To display table definitions in MySQL
      6. To display table definitions in PostgreSQL
    2. Inserting Rows with INSERT
      1. To insert a row by using column positions
      2. To insert a row by using column names
      3. To insert rows from one table into another table
    3. Updating Rows with UPDATE
      1. To update rows
    4. Deleting Rows with DELETE
      1. To delete rows
  13. 11. Creating, Altering, and Dropping Tables
    1. Creating Tables
    2. Understanding Constraints
      1. To name a constraint
    3. Creating a New Table with CREATE TABLE
      1. To create a new table
    4. Forbidding Nulls with NOT NULL
      1. To specify a column's nullability
    5. Specifying a Default Value with DEFAULT
      1. To specify a column's default value
    6. Specifying a Primary Key with PRIMARY KEY
      1. To specify a simple primary key
      2. To specify a composite primary key
    7. Specifying a Foreign Key with FOREIGN KEY
      1. To specify a simple foreign key
      2. To specify a composite foreign key
    8. Forcing Unique Values with UNIQUE
      1. To specify a simple unique constraint
      2. To specify a composite unique constraint
    9. Adding a Check Constraint with CHECK
      1. To add a check constraint
    10. Creating a Temporary Table with CREATE TEMPORARY TABLE
      1. To create a temporary table
    11. Creating a New Table from an Existing One with CREATE TABLE AS
      1. To create a new table from an existing table
    12. Altering a Table with ALTER TABLE
      1. To alter a table
      2. To re-create and repopulate a table
    13. Dropping a Table with DROP TABLE
      1. To drop a table
  14. 12. Indexes
    1. Creating an Index with CREATE INDEX
      1. To create an index
    2. Dropping an Index with DROP INDEX
      1. To drop an index in Microsoft Access or MySQL
      2. To drop an index in Microsoft SQL Server
      3. To drop an index in Oracle, DB2, or PostgreSQL
  15. 13. Views
    1. Creating a View with CREATE VIEW
      1. To create a view
    2. Retrieving Data Through a View
      1. To retrieve data through a view
    3. Updating Data Through a View
      1. Inserting a row through a view
      2. Updating a row through a view
      3. Deleting a row through a view
    4. Dropping a View with DROP VIEW
      1. To drop a view
  16. 14. Transactions
    1. Executing a Transaction
      1. To start a transaction explicitly
      2. To commit a transaction
      3. To roll back a transaction
  17. A. Creating the Sample Database
    1. Creating the Sample Database
      1. To open books in Microsoft Access
      2. To create books in Microsoft SQL Server
      3. To create books in Oracle
      4. To create books in IBM DB2
      5. To create books in MySQL
      6. To create books in PostgreSQL
  18. B. SQL Keywords

Product information

  • Title: SQL: Visual Quickstart Guide, Second Edition
  • Author(s): Chris Fehily
  • Release date: May 2005
  • Publisher(s): Peachpit Press
  • ISBN: 9780321334176