Building the Database Schema

Creating tables and columns

• Building tables with CREATE TABLE
• Modifying table structure with ALTER TABLE
• Adding columns to an existing table
• Removing tables with DROP TABLE

Protecting data integrity with constraints

• Guaranteeing uniqueness with primary key constraints
• Enforcing integrity with foreign key constraints
• Imposing business rules with check constraints

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

• Controlling transactions with COMMIT and ROLLBACK
• Deploying BEGIN TRANSACTION in SQL Server

Working with the SELECT Statement

Writing Single Table queries

• Retrieving data with SELECT
• Specifying column expressions
• Sorting the result with ORDER BY
• Handling NULL values in expressions

Restricting rows with the WHERE filter

• Testing for equality or inequality
• Applying wildcard characters
• Avoiding NULL value pitfalls

Querying Multiple Tables

Applying the ANSI/ISO standard join syntax

• Matching related rows with INNER JOIN
• Including nonmatched rows with OUTER JOIN
• Creating a Cartesian product with CROSS JOIN

Combining results with set operators

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

Employing Functions in Data Retrieval

Processing data with row functions

• Conditional formatting with the CASE expression
• Utilizing the CASE expression to simulate IF tests
• Dealing with NULL values

Performing analysis with aggregate functions

• Summarizing data using SUM, AVG and COUNT
• Finding the highest/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
• Testing the existence of rows

Including subqueries in expressions

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

Developing In-Line 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
• Updateable vs. non-updateable views