Is your data dragging you down? Are your tables all tangled up? Well we've got the tools to teach you just how to wrangle your databases into submission. Using the latest research in neurobiology, cognitive science, and learning theory to craft a multi-sensory SQL learning experience, Head First SQL has a visually rich format designed for the way your brain works, not a text-heavy approach that puts you to sleep. Maybe you've written some simple SQL queries to interact with databases. But now you want more, you want to really dig into those databases and work with your data.

The Art of SQL offers best practices that teach experienced SQL users to focus on strategy rather than specifics. Faroult’s approach takes a page from Sun Tzu’s classic treatise by viewing database design as a military campaign.

You need knowledge, skills, and talent. Talent can’t be taught, but every strategist from Sun Tzu to modern-day generals believed that it can be nurtured through the experience of others.

This page is a introductory tutorial of the Structured Query Language (also known as SQL) and is a pioneering effort on the World Wide Web, as this is the first comprehensive SQL tutorial available on the Internet.

SQL allows users to access data in relational database management systems, such as Oracle, Sybase, Informix, Microsoft SQL Server, Access, and others, by allowing users to describe the data the user wishes to see. SQL also allows users to define the data in a database, and manipulate that data.

The history of SQL begins in an IBM laboratory in San Jose, California, where SQL was developed in the late 1970s. The initials stand for Structured Query Language, and the language itself is often referred to as “sequel.” It was originally developed for IBM’s DB2 product (a relational database management system, or RDBMS, that can still be bought today for various platforms and environments). In fact, SQL makes an RDBMS possible.

Most business data are stored in relational database systems, and SQL (Structured Query Language) is used for data retrieval and manipulation. With XML (Extensible Markup Language) rapidly becoming the de facto standard for retrieving and exchanging data, new functionality is expected from traditional databases.

Existing SQL applications will evolve to retrieve relational data as XML data using database or SQL extensions for XML. New XML data will be stored, searched, and manipulated in the database as a “first class” citizen along with existing relational data.

Table of Contents
1. SQL Syntax

Lexical Structure

Identifiers and Key Words
Constants
Operators and Special Characters
Comments

Lexical Precedence
Columns
Arrays
Value Expressions

Column References
Positional Parameters
Function Calls
Aggregate Expressions

Queries

Table Expressions
Select Lists
Sorting Rows
LIMIT and OFFSET
Combining Queries

Indices

Introduction to Indices
Index Types

Table of Contents
Basics of the SELECT Statement
Conditional Selection
Relational Operators
Compound Conditions
IN & BETWEEN
Using LIKE

Joins
Keys
Performing a Join
Eliminating Duplicates
Aliases & In/Subqueries

Aggregate Functions
Views
Creating New Tables
Altering Tables
Adding Data
Deleting Data
Updating Data

Indexes
GROUP BY & HAVING
More Subqueries
EXISTS & ALL
UNION & Outer Joins
Embedded SQL
Common SQL Questions

Table of Contents

1. Introduction (brief intro to Z and to SQL)
2. Specific facts (relations and functions in Z)
3. Sets (set extension, comprehension, power sets, product sets)
4. Relations (database relations - Z style)
5. Introducing SQL
6. SQL retrieval (select from where)
7. SQL modularization (group by, views)
8. Facts and relations (conceptual schema modelling)
9. Uncovering facts (brief methodology)
10. Fact-based analysis (case-study)
11. Entity-relationship modeling

Contents:

* Legend, definitions, and notes
* Features
o Views
o Join types/features
* The SELECT statement
o Ordering result sets
o Limiting result sets (RANK() / ROW_NUMBER() / LIMIT / TOP / FETCH FIRST)
+ Simple limit
+ Top-n (quota-queries)
+ Limit—with offset, including a note about the importance of sorting on unique values
* The INSERT statement
o Inserting several rows at a time
* Data types
o BOOLEAN
o CHAR
o Date and time types
+ TIMESTAMP

Tutorial Section

1) SELECT:
In which we query the BBC country profile table.
2a) More practice with SELECT:
Additional practice of the basic features using a table of Nobel Prize winners.
2b) SELECT in SELECT:
In which we form queries using other queries.
3) SUM and COUNT:
In which we apply aggregate functions.
4) The JOIN operation:
In which we join two tables; ttms and country. (previously music tutorial)
5) More JOIN operations:
In which we join actors to movies in the Movie Database.