Simply SQL
The Fun and Easy Way to Learn Best-Practice SQL
Published on 28. December 2008
318 pages
978-1-4571-9273-9 (ISBN)
Description
Packed with examples, Simply SQL is a step-by-step introduction to learning SQL. You'll discover how easy it is to use SQL to interact with best-practice, robust databases. Rather than bore you with theory, it focuses on the practical use of SQL with common databases and uses plenty of diagrams, easy-to-read text, and examples to help make learning SQL easy and fun. Step through the basic SQL syntax Learn how to use best practices in database design Master advanced syntax like inner joins, groups, and subqueries Understand the SQL datatypes And much more...
More details
Other editions
Additional editions
Rudy Limeback
Simply SQL
Book
11/2008
SitePoint Pty Ltd
€35.50
Article exhausted; check different version
Content
- Simply SQL
- Table of Contents
- Preface
- Who Should Read This Book?
- The Challenges to Learning SQL
- What's in This Book?
- How to Gain Help
- The SitePoint Forums
- The Book's Web Site
- The Code Archive
- Updates and Errata
- The SitePoint Newsletters
- Your Feedback
- Conventions Used in This Book
- Code Samples
- Tips, Notes, and Warnings
- Acknowledgments
- An Introduction to SQL
- SQL Statement Overview
- Keywords, Identifiers, and Constants
- Clauses
- Syntax
- Data Definition Language
- CREATE, ALTER, and DROP
- The CREATE Statement
- The ALTER Statements
- The DROP Statement
- Starting Over
- Data Manipulation Language
- INSERT, UPDATE, and DELETE
- The INSERT Statement
- The UPDATE Statement
- The DELETE Statement
- The SELECT Statement
- The SELECT Retrieves Data
- The SELECT Statement Produces a Tabular Result Set
- Standard SQL
- Read The Fine Manual
- Wrapping Up: an Introduction to SQL
- An Overview of the SELECT Statement
- The SELECT Statement
- The SELECT and FROM Clauses
- Content Management System
- The entries Table
- The WHERE Clause
- The GROUP BY and HAVING Clauses
- The ORDER BY Clause
- Wrapping Up: the SELECT Statement
- The FROM Clause
- Why Start with the FROM Clause?
- Parsing an SQL Statement
- FROM One Table
- FROM More than One Table Using JOINs
- Types of Join
- The Inner Join
- Outer Joins
- The Left Outer Join
- The Right Outer Join
- The Full Outer Join
- The Cross Join
- Real World Joins
- Inner Join: Categories and Entries
- All Columns Are Available after a Join
- When a Join is Executed in a Query
- Qualifying Column Names
- Table Aliases
- Left Outer Join: Categories and Entries
- Outer Joins Produce NULLs
- Right Outer Join: Entries and Categories
- Right Outer Join: Categories and Entries
- Full Outer Join: Categories and Entries
- UNION Queries
- Views
- Views in Web Development
- Subqueries and Derived Tables
- Wrapping Up: the FROM Clause
- The WHERE Clause
- Conditions
- Conditions that are True
- When "Not True" is Preferable
- Shopping Carts
- Conditions that Evaluate as UNKNOWN
- Operators
- Comparison Operators
- The LIKE Operator
- The BETWEEN Operator
- BETWEEN: It haz a flavr
- Compound Conditions with AND and OR
- Truth Tables
- Combining AND and OR
- IN Conditions
- IN with Subqueries
- Correlated Subqueries
- EXISTS Conditions
- NOT IN or NOT EXISTS?
- A Left Outer Join with an IS NULL Test
- WHERE Clause Performance
- Indexes
- Wrapping Up: the WHERE Clause
- The GROUP BY Clause
- Grouping is More than Sequencing
- Out of Many, One
- Drill-down SQL
- GROUP BY in Context
- How GROUP BY Works
- Group Rows
- Rules for GROUP BY
- Columns with Certain Large Data Types
- Wrapping Up: the GROUP BY
- The HAVING Clause
- HAVING Filters Group Rows
- HAVING without a GROUP BY Clause
- Threshold Alert
- Are Thresholds Database or Application Logic?
- Wrapping Up: the HAVING Clause
- The SELECT Clause
- SELECT in the Sequence of Execution
- Which Columns Can Be Selected?
- Detail Rows
- Group Rows
- The Discussion Forum Application
- The forums Table
- The members Table
- The threads Table
- The posts Table
- Functions
- Aggregate Functions
- Aggregate Functions without GROUP BY
- Aggregate Functions Ignore NULLs
- COUNT(DISTINCT)
- COUNT(*)
- Scalar Functions
- The SUBSTRING Function
- The COALESCE Function
- The CASE Function
- EXTRACT
- CHAR_LENGTH
- The CAST Function
- The NULLIF Function
- Operators
- Numeric Operators
- The Concatenation Operator
- Temporal Operators
- The Dreaded, Evil Select Star
- SELECT DISTINCT
- Wrapping Up: the SELECT Clause
- The ORDER BY Clause
- ORDER BY Syntax
- How ORDER BY Works
- ASC and DESC
- ORDER BY Clause Performance
- When ORDER BY Seems Unnecessary
- The Sequence of Values
- NULLs Usually Sort First
- The Scope of ORDER BY
- Using ORDER BY with GROUP BY
- ORDER BY Expressions
- Special Sequencing
- ORDER BY with UNION Queries
- Wrapping Up: the ORDER BY Clause
- SQL Data Types
- An Overview of Data Types
- Numeric Data Types
- Integers
- Decimals
- Example: Latitude and Longitude
- Floating-point Numbers
- FLOAT, REAL, and DOUBLE PRECISION
- Conversions in Numeric Calculations
- Numeric Functions
- Character Data Types
- CHAR
- VARCHAR
- Numeric or Character?
- NCHAR and NVARCHAR
- CLOB and BLOB
- String Functions
- Temporal Data Types
- DATE
- Input Format, Storage Format, and Display Format
- TIME
- Times as Duration
- Times as Points in Time
- TIMESTAMP
- Intervals
- Date Functions
- Column Constraints
- NULL or NOT NULL
- DEFAULT
- CHECK Constraints
- Wrapping Up: SQL Data Types
- Relational Integrity
- Identity
- Data Modelling
- Entities and Attributes
- Example: Forums, Threads, Posts, and Members
- Entities and Relationships
- Entity-Relationship Diagrams
- Primary Keys
- UNIQUE Constraints
- Foreign Keys
- How Foreign Keys Work
- Using Foreign Keys
- The Foreign Key Goes in the Many Table
- The Foreign Key Must Reference a Key
- Foreign Keys May Be NULL
- ON DELETE and ON UPDATE
- Natural versus Surrogate Keys
- Autonumbers
- Wrapping Up: Relational Integrity
- Special Structures
- Joining to a Table Twice
- Joining a Table to Itself
- Implementing a Many-to-many Relationship: Keywords
- Wrapping Up: Special Structures
- Appendix A: Testing Environment
- Download Your Database System Software
- Bookmark or Download the SQL Reference
- Connect to the Database System
- Command Line
- Front-end Applications
- SQL Script Library
- Performance Problems and Obtaining Help
- Obtaining the Execution Plan
- Seeking Help
- Indexing
- Appendix B: Sample Applications
- Data Model Diagrams
- Teams and Games
- Content Management System
- Discussion Forums
- Shopping Carts
- Appendix C: Sample Scripts
- Teams and Games
- The teams Table
- The games Table
- Content Management System
- The entries Table
- The categories Table
- The entries_with_category View
- The contents Table
- The comments Table
- The entrykeywords Table
- Discussion Forums
- The forums Table
- The members Table
- The threads Table
- The posts Table
- Shopping Carts
- The items Table
- The customers Table
- The carts Table
- The cartitems Table
- The vendors Table
- Appendix D: SQL Keywords
- Index
