SQL Pocket Guide by Gennick, Jonathan -- Read -- Imperial Library of Trantor

Index

SQL Pocket Guide SPECIAL OFFER: Upgrade this ebook with O’Reilly A Note Regarding Supplemental Files 1. SQL Pocket Guide

Introduction

Organization of This Book

Platform notes

Conventions Example Data Using Code Examples How to Contact Us Safari® Books Online Acknowledgments

Analytic Functions CASE Expressions: Simple CASE Expressions: Searched CAST Function CONNECT BY Queries

Core CONNECT BY Syntax Creative CONNECT BY WHERE Clauses with CONNECT BY Joins with CONNECT BY Sorting CONNECT BY Results Loops in Hierarchical Data Supporting Functions and Operators

Data Type Conversion Data Types: Binary Integer Data Types: Character String Data Types: Datetime

DB2 MySQL Oracle PostgreSQL SQL Server

Data Types: Decimal

DB2’s DECFLOAT Type DECIMAL/NUMBER Type

Datetime Conversions: DB2 Datetime Conversions: MySQL

Date and Time Elements TO_DAYS and FROM_DAYS Unix Timestamp Support Seconds in the Day DATE_FORMAT and TIME_FORMAT

Datetime Conversions: Oracle Datetime Conversions: PostgreSQL Datetime Conversions: SQL Server

CAST and SET DATEFORMAT CONVERT DATENAME and DATEPART DAY, MONTH, and YEAR

Datetime Functions: DB2 Datetime Functions: MySQL Datetime Functions: Oracle

Getting Current Date and Time Rounding and Truncating Other Oracle Datetime Functions

Datetime Functions: PostgreSQL

Getting Current Date and Time Rounding and Truncating Other PostgreSQL Datetime Functions

Datetime Functions: SQL Server Deleting Data

Deleting in Order Deleting All Rows Deleting from Views and Subqueries Returning Deleted Data: DB2 Returning Deleted Data: Oracle Returning Deleted Data: SQL Server Double-FROM

EXTRACT Function GREATEST Grouping and Summarizing

Aggregate Functions GROUP BY Listing the Detail Values Reducing the GROUP BY List Grouping Before the Join HAVING ROLLUP CUBE GROUPING SETS Related Functions

Hierarchical Queries

Recursive WITH Tracking Your Depth Breadth-First Versus Depth-First Sorting Detecting Recursive Loops

Indexes, Creating Indexes, Removing Inserting Data

Single-Row Inserts Multirow Inserts Insert Targets Subquery Inserts Returning Inserted Values: DB2 Returning Inserted Values: Oracle Returning Inserted Data: SQL Server Multitable Inserts

Unconditional multitable insert Conditional multitable insert ALL versus FIRST

Joining Tables

The Concept of a Join Cross Joins Inner Joins The USING Clause Natural Joins Non-Equi-Joins Outer Joins

Left outer joins Interpreting nulls in an outer join Right outer joins Full outer joins Vendor-specific outer join syntax

LEAST Literals

Text Literals Numeric Literals Datetime Literals Datetime Interval Literals

Merging Data Nulls

Predicates for Nulls Using CASE with Nulls Using the COALESCE Function Functions for Nulls: DB2 Functions for Nulls: MySQL Functions for Nulls: Oracle Functions for Nulls: PostgreSQL Functions for Nulls: SQL Server

Numeric Conversions: DB2 Numeric Conversions: MySQL Numeric Conversions: Oracle Numeric Conversions: PostgreSQL Numeric Conversions: SQL Server Numeric/Math Functions OLAP Functions Pivoting and Unpivoting

Pivoting: The Concept Pivoting: Oracle Pivoting: SQL Server Unpivoting: The Concept Unpivoting: Oracle Unpivoting: SQL Server

Predicates

EXISTS Predicates IN Predicates BETWEEN Predicates LIKE Predicates

Recursive Queries Regular Expressions

Regular Expressions: MySQL Regular Expressions: Oracle Regular Expressions: PostgreSQL Regular Expressions: SQL Server

Selecting Data

The SELECT Clause

Listing the columns to retrieve Taking shortcuts with the asterisk Writing expressions Specifying result-set column names Dealing with case and punctuation in names Using subqueries in a SELECT list Qualifying column names

ALL and DISTINCT The FROM Clause

Table aliases in the FROM clause Subqueries in the FROM clause Generating tables through the VALUES clause

The WHERE Clause The GROUP BY Clause The HAVING Clause The ORDER BY Clause

String Functions

Searching a String Replacing Text in a String Extracting a Substring Finding the Length of a String Concatenating Strings Trimming Unwanted Characters Changing the Case of a String

Subqueries

Subqueries in the FROM Clause Subqueries in the WITH Clause

Tables, Creating

Creating a Table: DB2 Creating a Table: MySQL Creating a Table: Oracle Creating a Table: PostgreSQL Creating a Table: SQL Server

Tables, Dropping Tables, Modifying

Modifying a Table: DB2 Modifying a Table: MySQL Modifying a Table: Oracle Modifying a Table: PostgreSQL Modifying a Table: SQL Server

Transaction Management

Autocommit Mode Starting a Transaction: DB2 Starting a Transaction: MySQL Starting a Transaction: Oracle Starting a Transaction: PostgreSQL Starting a Transaction: SQL Server Ending a Transaction Aborting a Transaction Aborting to a Savepoint

Union Queries

UNION and UNION ALL

UNION UNION ALL

ORDER BY in Union Queries Names and Data Types in a Union Order of Evaluation EXCEPT (or MINUS) and EXCEPT ALL

EXCEPT (MINUS in Oracle) EXCEPT ALL

INTERSECT and INTERSECT ALL

INTERSECT INTERSECT ALL

Updating Data

Simple Updates New Values from a Subquery Updating Views and Subqueries UPDATE FROM Clause Returning Updated Data: DB2 Returning Updated Data: Oracle Returning Updated Data: SQL Server

Window Functions

Defining a Summary Window Ordering and Ranking Within a Window Comparing Values Across Rows Summarizing over a Moving Window Window Function Evaluation and Placement

Index About the Author SPECIAL OFFER: Upgrade this ebook with O’Reilly Copyright

← Prev
Back
Next →

← Prev
Back
Next →