VISUAL QUICKSTART GUIDE SQL Second Edition [Electronic resources] نسخه متنی

Copyright

Introduction

About SQL

About This Book

What You'll Need

Chapter 1. DBMS Specifics

Running SQL Programs

Microsoft Access

Microsoft SQL Server

Oracle

IBM DB2

MySQL

PostgreSQL

Chapter 2. The Relational Model

Tables, Columns, and Rows

Primary Keys

Foreign Keys

Relationships

Normalization

The Sample Database

Chapter 3. SQL Basics

SQL Syntax

SQL Standards and Conformance

Identifiers

Data Types

Character String Types

Binary Large Object Type

Exact Numeric Types

Approximate Numeric Types

Boolean Type

Datetime Types

Interval Types

Other Data Types

Nulls

Chapter 4. Retrieving Data From a Table

Retrieving Columns with SELECT and FROM

Creating Column Aliases with AS

Eliminating Duplicate Rows with DISTINCT

Sorting Rows with ORDER BY

Filtering Rows with WHERE

Combining and Negating Conditions with AND, OR, and NOT

Matching Patterns with LIKE

Range Filtering with BETWEEN

List Filtering with IN

Testing for Nulls with IS NULL

Chapter 5. Operators and Functions

Creating Derived Columns

Performing Arithmetic Operations

Determining the Order of Evaluation

Concatenating Strings with ||

To concatenate strings

Extracting a Substring with SUBSTRING()

Changing String Case with UPPER() and LOWER()

Trimming Characters with TRIM()

Finding the Length of a String with CHARACTER_LENGTH()

Finding Substrings with POSITION()

Performing Datetime and Interval Arithmetic

Getting the Current Date and Time

Getting User Information

Converting Data Types with CAST()

Evaluating Conditional Values with CASE

Checking for Nulls with COALESCE()

Comparing Expressions with NULLIF()

Chapter 6. Summarizing and Grouping Data

Using Aggregate Functions

Creating Aggregate Expressions

Finding a Minimum with MIN()

Finding a Maximum with MAX()

Calculating a Sum with SUM()

Calculating an Average with AVG()

Counting Rows with COUNT()

Aggregating Distinct Values with DISTINCT

Grouping Rows with GROUP BY

Filtering Groups with HAVING

Chapter 7. Joins

Qualifying Column Names

Creating Table Aliases with AS

Using Joins

Creating Joins with JOIN or WHERE

Creating a Cross Join with CROSS JOIN

Creating a Natural Join with NATURAL JOIN

Creating an Inner Join with INNER JOIN

Creating Outer Joins with OUTER JOIN

Creating a Self-Join

Chapter 8. Subqueries

Understanding Subqueries

Subquery Syntax

Subqueries vs. Joins

Simple and Correlated Subqueries

Qualifying Column Names in Subqueries

Nulls in Subqueries

Using Subqueries as Column Expressions

Comparing a Subquery Value by Using a Comparison Operator

Testing Set Membership with IN

Comparing All Subquery Values with ALL

Comparing Some Subquery Values with ANY

Testing Existence with EXISTS

Comparing Equivalent Queries

Chapter 9. Set Operations

Combining Rows with UNION

Finding Common Rows with INTERSECT

Finding Different Rows with EXCEPT

Calculating Running Statistics

Generating Sequences

Finding Sequences, Runs, and Regions

Limiting the Number of Rows Returned

Assigning Ranks

Calculating a Trimmed Mean

Picking Random Rows

Handling Duplicates

Chapter 10. Inserting, Updating, and Deleting Rows

Displaying Table Definitions

Inserting Rows with INSERT

Updating Rows with UPDATE

Deleting Rows with DELETE

Chapter 11. Creating, Altering, and Dropping Tables

Creating Tables

Understanding Constraints

Creating a New Table with CREATE TABLE

Forbidding Nulls with NOT NULL

Specifying a Default Value with DEFAULT

Specifying a Primary Key with PRIMARY KEY

Specifying a Foreign Key with FOREIGN KEY

Forcing Unique Values with UNIQUE

Adding a Check Constraint with CHECK

Creating a Temporary Table with CREATE TEMPORARY TABLE

Creating a New Table from an Existing One with CREATE TABLE AS

Altering a Table with ALTER TABLE

Dropping a Table with DROP TABLE

Chapter 12. Indexes

Creating an Index with CREATE INDEX

Dropping an Index with DROP INDEX

Chapter 13. Views

Creating a View with CREATE VIEW

Retrieving Data Through a View

Updating Data Through a View

Dropping a View with DROP VIEW

Chapter 14. Transactions

Executing a Transaction

Appendix A. Creating the Sample Database

Creating the Sample Database

Appendix B. SQL Keywords

Index