MSSQL - ANSI SQL Using Microsoft SQL Server
| Categories |
Database Training, Programming Training, SQL Training |
| Duration |
2 days (14 hours) |
| Pre-requisites |
Familiarity with database terminology. No programming experience required. |
|
|
|
| Courseware |
MSSQL-BS-01 (141 pages) |
| Price |
$790 |
| Notes |
This course is suitable for anyone working with an SQL-based Client/Server system. Although Microsoft SQL Server is used to demonstrate SQL, the course is focused primarily on ANSI-SQL, and makes a clear distinction between ANSI and MS-SQL Server specific commands.
|
This SQL training class covers ANSI-SQL and Local SQL as it is used in Microsoft SQL Server. Using SQL Server as a tool to model SQL statements, the user is introduced to all of the basic SQL commands, including SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER and DROP. Also covers GROUP BY, JOIN, HAVING, Aggregation, Functions and Expressions. This class covers both the data manipulation language (DML) and data definition language (DDL) aspects of SQL, with lots of hands-on examples.
"[The instructor] is an excellent speaker and teacher. I learned a LOT from him during these two days. Having taken another SQL course through [local University] Extension (where I felt I didn't learn what I needed to learn), I can heartily recommend this course to those that I work with, as a "compact" yet thorough education in SQL.." -- Toni F. from Santee, CA
"I enjoyed the teacher's presentation. He used real business applications for the various applications presented in the course. This made the course interesting, and will help me relate the skills I learned to on-the-job problems. This course is a good investment. I would like to mention that the classroom environment was conducive to learning, and all of the staff memeber at Blue Star were helpful and friendly.." -- Karen K. from San Diego, CA
"Having the course taught by Ted was wonderful as he is so experienced -- really provided valuable, real-world insight into SQL usage. He is clear, articulate, and reads the class well.." -- Jean O. from San Diego, CA
"Outstanding!! I highly recommend it to others. [The instructor] was exceptional. He is very knowledgeable in this field.." -- Daniel M. from San Diego, CA
"[The instructor] is an excellent teacher.. He really knows his stuff!. (This is) the best class I have attended in a long time..." -- Zeta G. from San Diego, CA
Relational Database Objects
Database
Tables
Columns
Rows/Records
Keys
Primary Keys
Foreign Keys
Indexes
Structured Query Language
Data Manipulation Language
Result Sets
Data Definition Language
Using the Query Analyzer
The SELECT Statement
Writing Queries with SELECT
Terminating an SQL Statement
Column and Table Specification
Operators and Expressions
Operator Precedence
Arithmetic Operators
The Modulo Operator
The Unary Operators
Using Arithmetic Operators
String Concatenation
Column Name Aliasing
The ORDER BY Clause
Decending Order
Ordering on Multiple Columns
Using WHERE Clauses
Conditions
Comparison Operators
NULL
Equals (=)
Relative Comparisons
Greater Than (>) and Greater Than or Equal To (>=)
Less Than (<) and Less Than or Equal To (<=)
Inequalities (<> or !=)
Character Operators
LIKE
BETWEEN
Functions
Aggregate Functions
COUNT()
SUM()
AVG()
MIN()
MAX()
Other Aggregate Functions
Arithmetic Functions
ABS()
CEILING() and FLOOR()
ROUND()
Character Functions
UPPER() and LOWER()
SUBSTRING()
LTRIM() and RTRIM()
Other Character Functions
Date and Time Functions
Date Parts
DATEDIFF()
DATENAME()
GETDATE()
DAY(), MONTH() and YEAR()
Combining Functions
Grouping
The GROUP BY Clause
Grouping Guidelines
Using WHERE with GROUP BY
The ALL Clause
The HAVING Clause
The CUBE and ROLLUP Clauses
Joining Tables
Cartesian Joins
Simple Joins (Equi-Joins)
Equi-Join Guidelines
Table Name Aliasing
Joins with Row Selection Criteria
Joining Three or More Tables
Self-joins
Non-Equi-Joins
Inner Joins
Outer Joins
Subqueries
Building a Subquery
Guidelines for Subquries
EXISTS
Conditional Subqueries
Correlated Subqueries
ANY and SOME
ALL
Testing for Set Inclusion
Testing without IN
Testing with IN
Testing with a Subquery
Adding Data with INSERT
The INSERT Statement
Inserting Multiple Records
Modifying Data with UPDATE
The UPDATE Statement
Updating Multiple Records
Updating All Records
Updating with a Subquery
Removing Data with DELETE
The DELETE Statement
Deleting All Records
Deleting with a Subquery
Views
The CREATE VIEW...AS Statement
Guidelines for Creating Views
Selecting a View
Customizing VIEWs
Custom Columns in Views
Using Views in Joins
Joining Tables with VIEWs
Removing Views
Indexes
The CREATE INDEX...ON Statement
Removing INDEX's
Stored Procedures
Why Stored Procedures?
Calling a Stored Procedure
Complex Stored Procedures
Analysis using Stored Procedures
Removing Stored Procedures
Appendix A - Answers to Problems
Appendix B - Codd's 12 Rules to Normalization
Appendix C - Northwind Data Model