This two day seminar is designed to familiarize students with basic to somewhat advanced select statements (queries) using Microsoft SQL Server. Four sample databases (and stored procedures that reference them) are provided for discussion and practice. Unlike many sample databases available, each sample database has enough complexity to provide useful practice, but is not too complex to be understood by a beginner.
SQL Server Benefits
SQL Server is MUCH MUCH more powerful than either Microsoft Query or Microsoft Access. SQL Server allows for much more complex manipulations, and the queries are easier to write. SQL can perform very complex manipulations on numerical, datetime, and text data "in bulk". You write the formula one time, and SQL does the manipulation/calculation for all selected records.
- If you use Microsoft Query to pull and manipulate data from MS Excel, you know it is quite limited.
- If you use MS Access for reporting, you probably have many subqueries that must be joined into a main query (such as qryMonthly_FinalResults, qryMonthly_sub1, qryMonthly_sub2, qryMonthly_sub3, qryMonthly_sub4, qryMonthly_sub5, etc.) SQL Server can consolidate these subqueries into just one stored procedure (query) using either Unions or Correlated Subqueries.
- Union queries are much easier to create in SQL than in Access. Joins are easier to understand. Table variables (tables created in memory) can be made then updated to manipulate data into very complex information easily.
- SQL Server's "Case" statement replaces nested If statements, and are incredibly simpler to write.
- Stored Procedures with input parameters allow the reuse of a single query for multiple sets of criteria (where salesDate Between @startDate and @endDate), so you can execute the stored procedure at any time with different dates.
- Creating Tables
- Primary Keys and Unique Indexes
- Table Relationships
- Select Statements
- Joins - Inner, Left, Right
- Converting Dates
- Left and Right functions
- Case statements
- Stored Procedures
- Aggregates (SUM, COUNT, MIN, MAX) and Group By
- Correlated Subqueries
- IF statements
- GOTO and RETURN
- Exists / Not Exists
As time permits, the following topics might be covered:
- Insert Statements
- Table Variables
- Update Statements
- While Loops
Note: The use of Microsoft Access for linking to SQL Server and linking from Excel will be covered. Making MS Access reports is not covered; regsister for our Microsoft Access user level course to learn that skill. Using SSIS (SQL Server Integration Service) for importing in bulk directly from Excel will also be covered.
Prospective students should be familiar with the following topics in MS Excel: Data Types (formats), Naming Rules, Data Validation, Naming Cell Ranges
This class is taught in a computer lab with desktop computers. If you want to use your laptop instead of our machines, the following requirements apply:
- The "Pro" version of your Microsoft operating system. The "Home" version of any OS will not run SQL Server. You will need at least XP Pro.
- SQL Server 2005 or greater (SQL Server 2005 Express is fine). Both SQL Server 2005 Express and SQL Server 2005 Mangement Studio Express, and directions for installation are available for download on MTI's FTP server. Once you are registered for the course, you will get a username and password.
- The installation disc for your operating system will be required to add some features.
- To add features and programs, you must be an administrator on the laptop.