SQL Overview

• Outlining the importance of SQL as the cornerstone of all database activity
• Applying the ANSI/ISO standards
• Describing the fundamental building blocks: tables, columns, primary keys and foreign keys
• Defining terminology

Building the Database Schema

Creating tables and columns

• Comparing data types across platforms
• Building tables with CREATE TABLE
• Modifying table structure with ALTER TABLE
• Adding columns to an existing table
• Increasing column size
• Removing tables with DROP TABLE

Protecting data integrity with constraints

• Defining constraint types
• Guaranteeing uniqueness with primary key constraints
• Enforcing referential integrity with foreign key constraints
• Imposing business rules with check constraints
• Enabling and disabling constraints
• Removing constraints with ALTER TABLE

Improving performance with indexes

• Expediting data retrieval with indexes
• Recommending guidelines for index creation

Manipulating Data

Modifying table contents

• Adding table rows with INSERT
• Changing row content with UPDATE
• Removing rows with DELETE

Applying transactions

• Atomic Consistent Isolated Durable (ACID) rules
• Controlling transactions with COMMIT and ROLLBACK

Writing Single Table Queries

• Retrieving data with SELECT
• Including columns and expressions in query results
• Restricting rows with the WHERE filter
• Sorting the result with ORDER BY
• Handling NULL values in expressions
• Avoiding NULL value pitfalls in filter conditions

Querying Multiple Tables

Applying the ANSI/ISO standard join syntax

• Matching related rows with INNER JOIN
• Including non-matched rows with OUTER JOIN
• Creating a Cartesian product with CROSS JOIN
• Joining a table to itself

Combining results with set operators

• Stacking results with UNION
• Identifying matching rows with INTERSECT
• Utilising EXCEPT to find nonmatching rows

Employing Functions in Data Retrieval

Processing data with row functions

• Solving mathematical problems with functions
• Manipulating text strings
• Converting date/time presentation
• Conditional formatting with the CASE expression
• Utilising the CASE expression to simulate IF tests
• Dealing with NULL values

Performing analysis with aggregate functions

• Summarising data using SUM, AVG and COUNT
• Finding the highest and lowest values with MAX and MIN
• Defining the summary level with GROUP BY
• Applying filter conditions with HAVING

Constructing Nested Queries

Applying subqueries in filter conditions

• Correlated vs. noncorrelated subqueries
• Embedding subqueries in several levels
• Testing the existence of rows
• Single row vs. multirow subqueries

Including subqueries in expressions

• Placing subqueries in the column list
• Creating complex expressions containing subqueries
• Handling subqueries that return no rows

Developing Inline and Stored Views

Breaking down complex problems

• Selecting data from a query result set
• Subqueries in the FROM clause

Creating views in a database

• Building reusable code
• Refining user access privileges