SQL Server 2000 for Developers

Monday – The Fundamentals

 

  • Overview of SQL Server 2000
  • Hardware Requirements
  • Architectural Decisions
    • Basic Design
    • Clustering
    • RAID Solutions
    • Some Real-World Examples
  • SQL Server Tools
    • Configuring Network Libraries (Client and Server)
    • A Walkthrough of Enterprise Manager
    • Using Query Analyzer
    • Using SQL Profiler
    • Using OSQL (Command Line Interface)
    • Using Books Online for Help
    • Internet Resources (Mark’s favorite SQL Server Sites)
  • Database Design
    • Creating a Database
    • Creating Tables
    • Defining Relations (Primary and Foreign Keys)
    • SQL Server Datatypes

 

Lab Work:

Students will create a sample database using Enterprise Manager followed by the creation of tables using both Enterprise Manager and Query Analyzer.  In addition, students will define primary/foreign key relationships between the tables using the database diagram and query analyzer.

 

Tuesday – Structured Query Language

 

  • Writing Basic Select Queries to Retrieve Data
    • Using the SELECT Statement
    • Using ORDER BY to Sort Results
    • Using WHERE to Filter Rows
    • How SQL Server Processes Queries
    • Performance Tuning and Real-World Best Practices
  • Using Joins
    • Inner Joins
    • Using Table Aliases
    • Outer Joins
    • Self Joins
    • Real-World Examples
  • Grouping and Summarizing Data
    • Using Aggregate Functions
    • Using GROUP BY with Aggregates
    • Using HAVING clauses
    • Using TOP to list rows
  • Subqueries
  • Using SQL Server’s Intrinsic Functions

 

Lab Work:

Students will write a queries to solve a variety of problems that require all the techniques covered by Tuesday’s lecture.

 

Wednesday – Making Data Modifications and Administration

 

  • Using INSERT
  • Using DELETE
  • Using UPDATE
  • Using INSERT, UPDATE, DELETE with Sub Queries
  • Writing Stored Procedures
  • Writing User Defined Functions
  • Creating Views
  • When Do You Use What?

Adminstration

  • Backing Ups
    • User Databases
    • Master, MSDB, and Model
    • Full, Differential, and Log Backups
  • Restoring Databases
  • Configuring Security
  • Server Level Performance Tuning Recommendations
  • Overview of Replication
  • Using DTS (Data Transformation Services)
  • Creating Jobs, Operators and Alerts

 

Lab Work:

Students will write a queries to INSERT, UPDATE and DELETE data.  Students will create VIEWS, write stored procedures and functions to solve real-world problems. 

Students will create jobs to Backup databases and restore them in a variety of scenarios.  Students will examine the default settings for the server and determine performance changes to make given a variety of scenarios.  Students will create and run simple DTS packages to transfer data between databases and export data into Excel spreadsheets.

  

 

Thursday – .Net Application Programming for SQL Server

 

  • Using ADO .Net
  • Connecting to SQL Server
  • Using Command Objects
  • Using DataReaders
  • Using DataAdapters and DataSets
  • Writing Data Access Components
  • Using ADO .Net and SQL Server with ASP .Net
    • Databinding to Grid Controls
    • Updating Data (High Performance verses Quick and Easy)
  • Using ADO .Net with Windows Forms Programs
  • Using ADO .Net with Web Services
  •  

 

Lab Work:

Students will now leverage their knowledge about writing queries and stored procedures to write data access components which they’ll call from both ASP .Net pages and a simple Windows Forms application to retrive, display and update data. 

 

Friday – Reporting, Mentoring and Review

 

  • Reporting with Crystal Reports (Overview)
  • Using SQL Server Reporting Services (Overview)

 

With most custom courses, I leave Friday as a buffer which is usually needed to cover topics that may take longer than expected during the previous days and to answer questions for clients regarding their current projects.  Covering the overview of reporting will take at least three hours.  The afternoon is a good time for students to finish up lab work and get one on one time with the instructor to cover questions specific to their jobs.