SQL Pocket Guide

by Jonathan Gennick

Overview

SQL is the language of databases. It's used to create and maintain database objects, place data into those objects, query the data, modify the data, and, finally, delete data that is no longer needed. Databases lie at the heart of many, if not most business applications. Chances are very good that if you're involved with software development, you're using SQL to some degree. And if you're using SQL, you should own a good reference or two.

Now available in an updated second edition, our very popular SQL Pocket Guide is a major help to programmers, database administrators, and everyone who uses SQL in their day-to-day work. The SQL Pocket Guide is a concise reference to frequently used SQL statements and commonly used SQL functions. Not just an endless collection of syntax diagrams, this portable guide addresses the language's complexity head on and leads by example. The information in this edition has been updated to reflect the latest versions of the most commonly used SQL variants including:

• Oracle Database 10g, Release 2 (includingthe free Oracle Database 10g Express Edition (XE))
• Microsoft SQL Server 2005
• MySQL 5
• IBM DB2 8.2
• PostreSQL 8.1 database

Product Details

ISBN-13:	9780596554538
Publisher:	O'Reilly Media, Incorporated
Publication date:	04/17/2006
Series:	Pocket Reference (O'Reilly)
Sold by:	Barnes & Noble
Format:	eBook
Pages:	192
File size:	4 MB

About the Author

Jonathan Gennick is an SQL aficionado who writes extensively on the SQL language and other database topics. He is the author of several books, and also a number of articles in Oracle Magazine. He is currently a book editor focusing on database titles, primarily involving Oracle and SQL Server.

Table of Contents

	Introduction	1
	Organization of This Book	2
	Feedback Needed!	2
	Conventions	3
	Acknowledgments	4
	Example Data	4
	Analytic Functions	6
	CASE Expressions	6
	Simple CASE Expressions	6
	Searched CASE Expressions	6
	Datatypes	7
	Character String Types	7
	Decimal Types	8
	Binary Integer Types	8
	Datetime Types	9
	Dataype Conversion	11
	Standard CAST Function	1
	Standard EXTRACT Function	12
	Datetime Conversions (Oracle)	12
	Numeric Conversions (Oracle)	16
	Datetime Conversions (DB2)	17
	Numeric Conversions (DB2)	20
	Datetime Conversions (SQL Server)	21
	Numeric Conversions (SQL Server)	25
	Datetime Conversions (MySQL)	26
	Numeric Conversions (MySQL)	30
	Datetime Conversions (PostgreSQL)	31
	Numeric Conversions (PostgreSQL)	33
	Deleting Data	35
	Deleting in Order (MySQL)	35
	Deleting All Rows	36
	Deleting from Views and Subqueries	37
	Returning Deleted Data (Oracle, DB2)	37
	Double-FROM (SQL Server)	38
	Functions	39
	Datetime Functions (Oracle)	39
	Datetime Functions (DB2)	42
	Datetime Functions (SQL Server)	43
	Datetime Functions (MySQL)	44
	Datetime Functions (PostgreSQL)	45
	Numeric and Math Functions (All Platforms)	48
	Trigonometric Functions (All Platforms)	50
	String Functions	50
	Greatest and Least (Oracle, PostgreSQL)	55
	Grouping and Summarizing	56
	Aggregate Functions	56
	Group by	57
	Useful Group by Techniques	59
	Having	60
	Group by Extensions (Oracle, DB2)	61
	Group by Extensions (SQL Server)	64
	Hierarchical Queries	65
	Recursive With (SQL Server, DB2)	66
	Connect by (Oracle)	68
	Indexes, Creating	74
	Indexes, Removing	74
	Inserting Data	75
	Single-Row Inserts	75
	Multi-Row Inserts (DB2, MySQL)	76
	Insert Targets	76
	Subquery Inserts	76
	Returning Inserted Values (Oracle, DB2)	77
	Multi-Table Inserts (Oracle)	78
	Joining Tables	80
	The Concept of a Join	80
	Cross Joins	81
	Inner Joins	82
	The Using Clause (Oracle, MySQL, PostgreSQL)	83
	Natural Joins (Oracle, MySQL, PostgreSQL)	84
	Non-Equi-Joins	85
	Outer Joins	86
	Literals	89
	Text Literals	90
	Numeric Literals	92
	Datetime Literals (Oracle, MySQL, PostgreSQL)	92
	Datetime Interval Literals	93
	Merging Data (Oracle, DB2)	93
	Nulls	96
	Predicates for Nulls	96
	Using CASE with Nulls	96
	Using the Coalesce Function	97
	Functions for Nulls (Oracle)	97
	Functions for Nulls (DB2)	98
	Functions for Nulls (SQL Server)	98
	Functions for Nulls (MySQL)	99
	Functions for Nulls (PostgreSQL)	100
	OLAP Functions	100
	Predicates	100
	Exists Predicates	101
	In Predicates	102
	Between Predicates	102
	Like Predicates	103
	Recursive Queries	104
	Regular Expressions	104
	Regular Expressions (Oracle)	104
	Regular Expressions (SQL Server)	108
	Regular Expressions (MySQL)	108
	Regular Expressions (PostgreSQL)	110
	Selecting Data	115
	The Select Clause	115
	All and Distinct	120
	The From Clause	121
	The Where Clause	124
	The Group by Clause	124
	The Having Clause	124
	The Order by Clause	125
	Subqueries	126
	The With Clause (Oracle, SQL Server, DB2)	127
	Tables, Creating	130
	Creating a Table (Oracle)	130
	Creating a Table (DB2)	132
	Creating a Table (SQL Server)	133
	Creating a Table (MySQL)	133
	Creating a Table (PostgreSQL)	134
	Tables, Modifying	135
	Modifying a Table (Oracle)	135
	Modifying a Table (DB2)	136
	Modifying a Table (SQL Server)	137
	Modifying a Table (MySQL)	138
	Modifying a Table (PostgreSQL)	139
	Tables, Removing	140
	Transaction Management	141
	Autocommit Mode (SQL Server, MySQL, PostgreSQL)	141
	Starting a Transaction	142
	Ending a Transaction	146
	Aborting a Transaction	147
	Aborting to a Transaction Savepoint	148
	Union Queries	148
	Union and Union All	149
	Order by in Union Queries	150
	Names and Datatypes in a Union	150
	Order of Evaluation	151
	Except (or Minus)	152
	Intersect	153
	Updating Data	155
	Simple Updates	155
	New Values from a Subquery	156
	Updating Views and Subqueries	157
	Update From Clause (SQL Server, PostgreSQL)	157
	Returning Updated Data (Oracle, DB2)	158
	Window Functions	158
	Defining a Summary Window (Oracle, DB2, SQL Server)	159
	Ordering and Ranking Within a Window (Oracle, DB2, SQL Server)	160
	Comparing Values Across Rows (Oracle)	162
	Summarizing over a Moving Window (Oracle, DB2)	164
	Window Function Syntax (Oracle)	164
	Window Function Syntax (DB2)	165
	Window Function Evaluation and Placement	166
	Index	167