| Lesson Titles and Topics
within Lessons |
SQL Server Express Database
Applications |
This page gives two different resources for seeing what the seminar can cover.
A subset of these topics will be presented depending on the interests of
attendees and the number of questions attendees ask. Our general guideline
is to devote the first half of the seminar to SQL Server Express and to cover
Visual Basic Express and Visual Web Developer Express for use with databases in
the second half of the seminar. Primary focus will go to SQL Server
Express databases, but some attention will go to SQL Server 2005 and 2000
databases as well as Access databases.
The following table of contents gives the titles for each of the 23 seminar
lessons. Each title in the table serves as a link that lets you drill down
to the first and second level headings for the content within a lesson. By
using the links in the table and at the end of the detailed content for each
lesson, you can peruse the seminar's content easily in any order that you
prefer.
Lesson 1: Intro to Express suite and VS 2005
 | What is SQL Server Express and who is it for? |
| What other Express suite components are there besides SQL Server Express? |
| How other Express suite components interoperate with SQL Server Express? |
| Comparing and Contrasting SQL Server Express to MSDE and to SQL Server
2005 |
| Installing and Configuring SQL Server Express |
Return to Table of Lessons
Lesson 2: Learning about Graphical and Command-line
query tools
| Learning about SQL Server Management Studio Express
 | Where to download it |
| Connecting to SQL Server instances |
| Using designers to create database objects |
| Designing, running, saving, and rerunning SQL scripts |
|
| Learning about the Sqlcmd Command-line Utility
| Connecting to SQL Server instances |
| Running manually entered statements |
| Running statements from a file |
| Saving output to a file |
| Dynamically running and batching sqlcmd statements |
|
Return to Table of Lessons
Lesson 3: Creating, Learning about, Backing up, and
Restoring Databases
| Introduction to Databases
| Types of databases |
| Meta data lets you learn about a databases |
| Using system catalog views to get meta data |
|
| Creating a Database
| Using the CREATE DATABASE statement |
| Detaching, Copying, and attaching database files |
| The Auto-close feature and XCopy deployment |
|
| Backing Up and Restoring Databases
| Overview of Database Recovery Models |
| Performing a Full Data Backup and Restore |
| Performing a Differential Backup and Restore |
|
Return to Table of Lessons
Lesson 4: Using the Right SQL Server Data Types to
Hold Your Data
| Why data types are important |
| Numbers and Dates
| Bit Data Type |
| Integer Data Types |
| Currency Data Types |
| Date and Time Data Types |
| Decimal and Numeric Data Types |
| Approximate Data Types |
|
| Character and Binary Byte Streams
| Character Data Types |
| Unicode Character Data Types |
| Binary Strings |
|
| Miscellaneous
| Timestamp |
| Uniqueidentifier |
| Cursor |
| Sql-variant |
| Xml |
| Table |
| Alias |
|
Return to Table of Lessons
Lesson 5: Creating Tables and Constraining Their
Column Values
| Creating and Populating Tables
| Using the CREATE TABLE statement |
| Adding data to a table |
|
| Managing Data Integrity with Column Properties
| Inserting data for a subset of columns |
| Not allowing Null values in a column |
| Setting Default Column Values |
| Setting a Column as a Primary Key |
|
| Managing Data Integrity with Constraints
| Specifying Check Constraints |
| Specifying Multicolumn Constraints |
| Specifying Foreign Key Constraints |
|
Return to Table of Lessons
Lesson 6: Selecting, Filtering, and Grouping from a
Single Data Source
| Specifying SELECT List Items
| Designating list items |
| Using the Current Database or a Different Database |
| Running Queries on Another Server |
|
| Filtering Grouping and Aggregating
| Filtering for Exact and Approximate Character Matches |
| Filtering for Values in a Range |
| Grouping and Aggregating |
|
Return to Table of Lessons
Lesson 7: Querying Multiple Data Sources with Joins
| Inner Joins |
| Outer Joins |
| Cross Joins |
| Self Joins |
| Joins for More than Two Data Sources |
Return to Table of Lessons
Lesson 8: Getting the Precise Data that You Want with
Subqueries
| Including a Subquery in a SELECT List |
| Including a Suquery in WHERE Clause |
| Including a Correlated Subquery in a WHERE Clause |
Return to Table of Lessons
Lesson 9: Ordering and Ranking Result Sets
| Using the ORDER BY Clause
| Sorting Rows Based on a Single Column's Values |
| Sorting Rows Based on More Than One Column's Values |
| Listing Top or Bottom Rows |
|
| Ranking Result Set Rows
| Assigning Row_Number Values to Rows |
| Assigning Ranks to Rows |
| Assigning NTile Numbers to Rows |
|
Return to Table of Lessons
Lesson 10: Reusing Result Sets
| Saving a Result Set with the INTO Clause
| Making a Copy of a Table |
| Copying Selected Rows from a Table |
|
| Appending Result Sets to One Another
| Combining Result Sets with or without duplicates |
| Tracking Input Data Sources in a Combined Result Set |
| Reusing a Combined Result Set from a Table |
|
| Reusing Queries with Common Table Expressions
| Looking Up Values Based on CTE Column Values |
| Using a CTE instead of a Correlated Subquery |
| Mapping Hierarchical Relationship with a CTE |
|
| Converting Between Relational and Cross-Tabulated Tables
| Pivoting Relational Data to a Cross Tab |
| Saving a Cross Tab |
| Unpivoting a Cross Tab |
|
Return to Table of Lessons
Lesson 11: Creating and Using Views, User-defined
Functions
| Creating and Using Views
| Performing Data Access with a View |
| Performing Data Modification with a View |
| Processing Meta Data with System Views |
|
| Creating and Using User-defined Functions
| Types of User-defined Functions |
| Creating and Using FN User-defined Functions |
| Creating and Using IF User-defined Functions |
|
Return to Table of Lessons
Lesson 12: Creating and Using Stored Procedures and
Triggers
| Creating and Using Stored Procedures
| Returning Result Sets without Parameters |
| Returning Result Sets and Input Parameters |
| Returning Scalar Values with Output Parameters |
| Processing Return Status Values |
| Performing Data Manipulation |
|
| Creating and Using Triggers
| Referencing Inserted and Deleted Tables |
| Rolling Back in an AFTER Trigger |
| Archiving Changes to a Table |
| Using an INSTEAD OF Trigger with a View |
|
Return to Table of Lessons
Lesson 13: Securing Your SQL Server Express Data
| Exploring and Creating Principals
| What's a Principal |
| Exploring Principals |
| Creating Principals |
|
| Assigning Permissions to Principals
| Overview of Securables and Permissions |
| Assigning Permissions via Fixed Server Roles |
| Assigning Permissions via Fixed Database Roles |
| Granting, Denying, and Revoking Permissions |
|
| Creating and Using Schemas
| Creating and Owning Schemas |
| Creating Objects in Owned Schemas |
| Creating Objects in Any Schema |
|
Return to Table of Lessons
Lesson 14: Encryption and Impersonation
| Introducing Encryption Keys and Certificates
| Encrypting Data |
| Signing Data |
|
| Impersonating Principals
| Impersonating principals in Scripts |
| Impersonating users in Code Modules within a Database |
| Impersonating Users across Databases |
|
Return to Table of Lessons
Lesson 15: Creating and Programming a Windows
Application with Visual Basic Express
| Starting, Saving, and Exploring a Solution
| Starting and Saving a New Solution's Project |
| Adding Items to a Solution |
| Exploring a Solution |
|
| Using Data Types in VBE
| Thinking of Data Types as Objects |
| Surveying Data Types |
| Declaring Variables and Assigning Values |
|
| Building a Calculator Application
| Populating a Windows Form with Controls |
| Adding Button Click Event Procedures to a Form |
| Testing the Code behind a Form |
|
Return to Table of Lessons
Lesson 16: Using the File System and Exception
Handling in a Windows Application
| Using the File System
| Exploring Folders and Viewing Files |
| Reading Fixed-width Reports |
| Sidebar: Drilling down on the My Namespace |
|
| Handling Exceptions
| Syntax for the Try...Catch Statement |
| Inline Use of the Try...Catch Statement |
| Invoking a Function Procedure with a Try...Catch Statement |
| Dynamically Adapting to Runtime Errors |
|
Return to Table of Lessons
Lesson 17: Creating and Using Web Pages with Built-in
and Stand-alone Web Servers
| Reflecting on the Heritage of ASP.NET Solutions |
| Starting and Exploring ASP.NET Projects
| Starting an ASP.NET Project |
| Exploring an ASP.NET Project |
|
| Creating Pages for an ASP.NET Project
| Changing a Page by Clicking a Button |
| Returning a Formatted Date from a Calendar Control |
| Programming a Message-for-the-day Application |
| Creating and Linking HTML Pages |
|
| Creating a Server-Based Website and Copying Files to It |
| Saying Hello Dynamically to a Web Form User |
| Implementing a Guest Book Sample |
Return to Table of Lessons
Lesson 18: Managing Membership Applications with
Login Controls
| Configuring a Website and Web Server for Login Controls
| Running the Setup Wizard |
| Configuring SMTP E-mail Settings |
|
| Granting Access by User Name and Password |
| Defining Custom Roles |
| Customizing the Operation of Login Controls
| Making the Content of a Page Dynamic |
| Modifying the Operation of a LoginStatus Control |
| Securing Page Content and Redirecting Access |
|
| Changing and Recovering Passwords with Restricted-access Folders
| Restricting Folder Access |
| Changing Passwords |
| Recovering Passwords |
|
Return to Table of Lessons
Lesson 19: Programming ADO.NET Connection, Command
and DataReader Objects
| Overview of ADO.NET |
| Programming Connection Objects
| Opening and Closing a Connection to a SQL Server Database |
| Getting Help with Constructing a Connection String |
| Connecting to SQL Server Express and Access Files |
|
| Programming Command Objects
| Returning a Scalar Value with a Command Object |
| Performing Database Maintenance and Creating Database Objects |
|
| Programming DataReader Objects
| Working with Forward-only, Read-only Rowsets |
| Reading Column Values Returned by a Single SELECT Statement |
| Reading Column Values Returned by Multiple SELECT Statements |
| Populating a ListBox Control |
|
Return to Table of Lessons
Lesson 20: Programming DataAdapters and DataSets
| Creating, Populating and Using DataSets
| Creating, Populating, and Iterating through DataSets |
| Assigning DataTable Values to a ListBox Control |
|
| Sorting and Filtering DataTables and DataViews
| Setting up to Use DataViews |
| Changing the Sort Order of Rows in a DataGridView |
| Dynamically Filtering ListBox Items |
|
| Inserting, Updating, and Deleting Rows
| Adding a Row to a Database Table through a DataTable |
| Updating a Column Value |
| Handling Multiple Data Manipulation Tasks in a Single Update |
|
| Bulk Loading Data From Excel
| Creating a Database Table for an Excel Worksheet |
| Using the SqlBulkCopy Class |
|
Return to Table of Lessons
Lesson 21: Using the Visual Database Tools in Windows
Applications
| Dialog Boxes for Creating a Connection |
| Exploring a SQL Server Express Database |
| Adding Objects to a SQL Server Express Database |
| Migrating and Revising an MSDE Database |
| Adding a Stored Procedure |
Return to Table of Lessons
Lesson 22: Browsing and Manipulating Data from
Windows Forms with the Data Sources Window
| Creating a Data Source for the Data Sources Window |
| Dragging Data Source Items to a Windows Form |
| Modifying Control Behavior with Smart Tags |
| Creating and Using Interactive Data Displays |
Return to Table of Lessons
Lesson 23: Using Visual Database Tools and Data
Sources in Web Applications
| Making Database Connections |
| Viewing Data and Modifying Data |
| Displaying Data on a Web Form with a GridView Control |
| Performing Lookups on a Web Form Page |
| Updating Data on a Web Form |
Return to Table of Lessons
Seminar
home page Site home Page
|