Introduction to SQL Training

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

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

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

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
• Utilising EXCEPT to find nonmatching rows

Processing data with row functions

• 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/lowest values with MAX and MIN
• Defining the summary level with GROUP BY
• Applying filter conditions with HAVING

Applying subqueries in filter conditions

• Creating 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

Breaking down complex problems

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

Creating views in a database

• Building reusable code
• Creating updateable vs. non-updateable views