Course Introduction

MS SQL Server is a relational database management system (RDBMS) developed by Microsoft. This product is built for the basic function of storing retrieving data as required by other applications. It can be run either on the same computer or on another across a network.

 

A job is a specified series of operations performed sequentially by SQL Server Agent. A job can perform a wide range of activities, including running Transact-SQL scripts, command prompt applications, Microsoft ActiveX scripts, Integration Services packages, Analysis Services commands and queries, or Replication tasks.


Course Syllabus:

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

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
  •    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
  •      Enabling and disabling constraints
  •     Removing constraints with ALTER TABLE

Improving performance with indexes

Manipulating Data

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

Applying transactions

Writing Single-Table Queries

  •     Retrieving data with SELECT
  •     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 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

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

Leave A Message

There are no any courses offered by this institute...!