SQL Pocket Guide

206 Pages·2010·1.539 MB·English

by  Jonathan Gennick

SQL
Pocket Guide

THIRD EDITION
SQL
Pocket Guide
Jonathan Gennick
Beijing • Cambridge • Farnham • Köln • Sebastopol • Tokyo
SQL Pocket Guide, Third Edition
by Jonathan Gennick
Copyright © 2011 Jonathan Gennick. All rights reserved.
Printed in the United States of America.
Published by O’Reilly Media, Inc., 1005 Gravenstein Highway North,
Sebastopol, CA 95472.
O’Reilly books may be purchased for educational, business, or sales promo-
tional use. Online editions are also available for most titles (http://my.safari
booksonline.com). For more information, contact our corporate/institutional
sales department: [phone] or [email protected].
Editor: Julie Steele
Copyeditor: Teresa Elsey
Production Editor: Teresa Elsey
Proofreader: Emily Quill
Indexer: Ellen Troutman Zaig
Cover Designer: Karen Montgomery
Interior Designer: David Futato
Illustrator: Robert Romano
Printing History:
March 2004: First Edition.
April 2006: Second Edition.
November 2010: Third Edition.
Nutshell Handbook, the Nutshell Handbook logo, and the O’Reilly logo are
registered trademarks of O’Reilly Media, Inc. The Pocket Guide series desig-
nations, SQL Pocket Guide, the image of a chameleon, and related trade dress
are trademarks of O’Reilly Media, Inc.
Many of the designations used by manufacturers and sellers to distinguish
their products are claimed as trademarks. Where those designations appear
in this book, and O’Reilly Media, Inc., was aware of a trademark claim, the
designations have been printed in caps or initial caps.
While every precaution has been taken in the preparation of this book, the
publisher and author assume no responsibility for errors or omissions, or for
damages resulting from the use of the information contained herein.
ISBN: 978-1-449-39409-7
[TG]
1288815268
Contents
SQL Pocket Guide 1
Introduction 1
Analytic Functions 7
CASE Expressions: Simple 7
CASE Expressions: Searched 7
CAST Function 8
CONNECT BY Queries 8
Data Type Conversion 15
Data Types: Binary Integer 15
Data Types: Character String 15
Data Types: Datetime 16
Data Types: Decimal 19
Datetime Conversions: DB2 21
Datetime Conversions: MySQL 24
Datetime Conversions: Oracle 28
Datetime Conversions: PostgreSQL 31
Datetime Conversions: SQL Server 34
Datetime Functions: DB2 38
Datetime Functions: MySQL 39
Datetime Functions: Oracle 40
Datetime Functions: PostgreSQL 43
Datetime Functions: SQL Server 45
v
Deleting Data 47
EXTRACT Function 51
GREATEST 52
Grouping and Summarizing 52
Hierarchical Queries 62
Indexes, Creating 66
Indexes, Removing 67
Inserting Data 67
Joining Tables 72
LEAST 82
Literals 82
Merging Data 86
Nulls 88
Numeric Conversions: DB2 93
Numeric Conversions: MySQL 95
Numeric Conversions: Oracle 95
Numeric Conversions: PostgreSQL 97
Numeric Conversions: SQL Server 98
Numeric/Math Functions 99
OLAP Functions 101
Pivoting and Unpivoting 101
Predicates 109
Recursive Queries 112
Regular Expressions 113
Selecting Data 124
String Functions 134
Subqueries 139
Tables, Creating 143
Tables, Dropping 148
Tables, Modifying 149
Transaction Management 154
Union Queries 162
vi | Table of Contents
Updating Data 168
Window Functions 173
Index 181
Table of Contents | vii