SQL Programming Language Foundation

The cornerstone of all relational database operations is the SQL Language and it is essential for anyone who develops database applications.This training will help you to learn how to optimize the accessibility and maintenance of data with the SQL programming language, and gain a solid foundation for building, querying, and manipulating databases.

Content


  1. SQL: Overview
    • Outlining SQL as the cornerstone of database activity
    • Applying the ANSI/ISO standards
    • Describing the fundamental building blocks: tables, columns, primary keys and foreign keys
  2. Building the Database Schema
    • Building tables with CREATE TABLE
    • Modifying table structure with ALTER TABLE
    • Adding columns to an existing table
    • Removing tables with DROP TABLE
    • Guaranteeing uniqueness with primary key constraints
    • Enforcing integrity with foreign key constraints
    • Imposing business rules with check constraints
    • Expediting data retrieval with indexes
    • Recommending guidelines for index creation
  3. Manipulating Data
    • Modifying table contents
    • Adding table rows with INSERT
    • Changing row content with UPDATE
    • Removing rows with DELETE
    • Controlling transactions with COMMIT and ROLLBACK
    • Deploying BEGIN TRANSACTION in SQL Server
  4. Working with the SELECT Statement
    • Retrieving data with SELECT
    • Specifying column expressions
    • Sorting the result with ORDER BY
    • Handling NULL values in expressions
    • Testing for equality or inequality
    • Applying wildcard characters
    • Avoiding NULL value pitfalls
  5. Querying Multiple Tables
    • Matching related rows with INNER JOIN
    • Including nonmatched rows with OUTER JOIN
    • Creating a Cartesian product with CROSS JOIN
    • Stacking results with UNION
    • Identifying matching rows with INTERSECT
    • Utilizing EXCEPT to find nonmatching rows
  6. Employing Functions in Data Retrieval
    • Conditional formatting with the CASE expression
    • Utilizing the CASE expression to simulate IF tests
    • Dealing with NULL values
    • 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
  7. Constructing Nested Queries
    • Correlated vs. noncorrelated subqueries
    • Testing the existence of rows
    • Placing subqueries in the column list
    • Creating complex expressions containing subqueries
    • Handling subqueries that return no rows
  8. Developing In-Line and Stored Views
    • Selecting data from a query result set
    • Subqueries in the FROM clause
    • Building reusable code
    • Updateable vs. non-updateable views