SQL Visual QuickStart Guide

by Fehily, Chris

• ISBN13: 9780321334176
• ISBN10: 0321334175
• Edition: 2nd
• Format: Paperback
• Copyright: 2005-05-12
• Publisher: Peachpit Pr

Summary

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..

Author Biography

Chris Fehily is a writer and consultant living in San Francisco.

Table of Contents

Introduction	xi
DBMS Specifics	1	(20)
Running SQL Programs	2	(2)
Microsoft Access	4	(3)
Microsoft SQL Server	7	(3)
Oracle	10	(3)
IBM DB2	13	(4)
MySQL	17	(2)
PostgreSQL	19	(2)
The Relational Model	21	(24)
Tables, Columns, and Rows	22	(4)
Primary Keys	26	(2)
Foreign Keys	28	(2)
Relationships	30	(3)
Normalization	33	(6)
The Sample Database	39	(6)
SQL Basics	45	(24)
SQL Syntax	46	(3)
SQL Standards and Conformance	49	(1)
Identifiers	50	(2)
Data Types	52	(2)
Character String Types	54	(2)
Binary Large Object Type	56	(1)
Exact Numeric Types	57	(2)
Approximate Numeric Types	59	(1)
Boolean Type	60	(1)
Datetime Types	61	(3)
Interval Types	64	(2)
Other Data Types	66	(1)
Nulls	67	(2)
Retrieving Data from a Table	69	(40)
Retrieving Columns with SELECT and FROM	70	(3)
Creating Column Aliases with AS	73	(2)
Eliminating Duplicate Rows with DISTINCT	75	(2)
Sorting Rows with ORDER BY	77	(6)
Filtering Rows with WHERE	83	(4)
Combining and Negating Conditions with AND, OR, and NOT	87	(9)
Matching Patterns with LIKE	96	(4)
Range Filtering with BETWEEN	100	(3)
List Filtering with IN	103	(3)
Testing for Nulls with IS NULL	106	(3)
Operators and Functions	109	(42)
Creating Derived Columns	110	(2)
Performing Arithmetic Operations	112	(3)
Determining the Order of Evaluation	115	(1)
Concatenating Strings with II	116	(3)
Extracting a Substring with SUBSTRING()	119	(3)
Changing String Case with UPPER() and LOWER()	122	(2)
Trimming Characters with TRIM()	124	(5)
Finding the Length of a String with CHARACTER_LENGTH()	129	(2)
Finding Substrings with POSITION()	131	(3)
Performing Datetime and Interval Arithmetic	134	(2)
Getting the Current Date and Time	136	(2)
Getting User Information	138	(1)
Converting Data Types with CAST()	139	(4)
Evaluating Conditional Values with CASE	143	(4)
Checking for Nulls with COALESCE()	147	(2)
Comparing Expressions with NULLIF()	149	(2)
Summarizing and Grouping Data	151	(24)
Using Aggregate Functions	152	(1)
Creating Aggregate Expressions	153	(1)
Finding a Minimum with MIN()	154	(1)
Finding a Maximum with MAX()	155	(1)
Calculating a Sum with SUM()	156	(1)
Calculating an Average with AVG()	157	(3)
Counting Rows with COUNT()	160	(1)
Aggregating Distinct Values with DISTINCT	161	(4)
Grouping Rows with GROUP BY	165	(7)
Filtering Groups with HAVING	172	(3)
Joins	175	(62)
Qualifying Column Names	176	(2)
Creating Table Aliases with AS	178	(2)
Using Joins	180	(2)
Creating Joins with JOIN or WHERE	182	(4)
Creating a Cross Join with CROSS JOIN	186	(2)
Creating a Natural Join with NATURAL JOIN	188	(4)
Creating an Inner Join with INNER JOIN	192	(25)
Creating Outer Joins with OUTER JOIN	217	(12)
Creating a Self-Join	229	(8)
Subqueries	237	(50)
Understanding Subqueries	238	(2)
Subquery Syntax	240	(1)
Subqueries vs. Joins	241	(5)
Simple and Correlated Subqueries	246	(5)
Qualifying Column Names in Subqueries	251	(1)
Nulls in Subqueries	252	(2)
Using Subqueries as Column Expressions	254	(5)
Comparing a Subquery Value by Using a Comparison Operator	259	(6)
Testing Set Membership with IN	265	(7)
Comparing All Subquery Values with ALL	272	(3)
Comparing Some Subquery Values with ANY	275	(3)
Testing Existence with EXISTS	278	(7)
Comparing Equivalent Queries	285	(2)
Set Operations	287	(44)
Combining Rows with UNION	288	(6)
Finding Common Rows with INTERSECT	294	(2)
Finding Different Rows with EXCEPT	296	(2)
Calculating Running Statistics	298	(3)
Generating Sequences	301	(6)
Finding Sequences, Runs, and Regions	307	(6)
Limiting the Number of Rows Returned	313	(9)
Assigning Ranks	322	(2)
Calculating a Trimmed Mean	324	(1)
Picking Random Rows	325	(2)
Handling Duplicates	327	(4)
Inserting, Updating, and Deleting Rows	331	(22)
Displaying Table Definitions	332	(3)
Inserting Rows with INSERT	335	(8)
Updating Rows with UPDATE	343	(6)
Deleting Rows with DELETE	349	(4)
Creating, Altering, and Dropping Tables	353	(40)
Creating Tables	354	(1)
Understanding Constraints	355	(2)
Creating a New Table with CREATE TABLE	357	(2)
Forbidding Nulls with NOT NULL	359	(3)
Specifying a Default Value with DEFAULT	362	(4)
Specifying a Primary Key with PRIMARY KEY	366	(3)
Specifying a Foreign Key with FOREIGN KEY	369	(6)
Forcing Unique Values with UNIQUE	375	(4)
Adding a Check Constraint with CHECK	379	(3)
Creating a Temporary Table with Create Temporary Table	382	(3)
Creating a New Table from an Existing One with Create Table as	385	(4)
Altering a Table with ALTER TABLE	389	(3)
Dropping a Table with DROP TABLE	392	(1)
Indexes	393	(8)
Creating an Index with CREATE INDEX	394	(5)
Dropping an Index with DROP INDEX	399	(2)
Views	401	(16)
Creating a View with CREATE VIEW	402	(5)
Retrieving Data Through a View	407	(3)
Updating Data Through a View	410	(5)
Dropping a View with DROP VIEW	415	(2)
Transactions	417	(6)
Executing a Transaction	418	(5)
Appendix A: Creating the Sample Database	423	(16)
Creating the Sample Database	424	(15)
Appendix B: SQL Keywords	439	(6)
Index	445