About this Course
During this 4-day course, students will learn Transact-SQL as implemented in SQL Server 2000, 2005 and SQL Server 2008 R2. The course is designed and paced for students that do not have a programming background.
The course provides students with knowledge to learn the technical skills needed to write Transact-SQL SELECT statements accessing one or more tables. The student will learn the various techniques for combining data, filtering the results as well as how to control and format the statement output.
The course starts by establishing a foundation understanding of a SQL Server database and use of the tools available to discover the database definition. Students are prepared to use various Microsoft tools to submit queries and view the results.
Following a step-by-step process for creating SQL queries from business requirements, students learn how to write a query based on the way SQL Server processes the SQL statement. This differs from most courses, which present SQL as a set of features. This approach uses the natural way of breaking down the problem into a logical set of steps. Each step can be validated before moving to the next step.
Lectures that highlight and explain Transact-SQL concepts are reinforced with extensive follow-along demonstrations and hands-on labs.
This course is intended for Business Analysts, System Administrators, Database Administrators and other professionals with no programming or SQL experience. This course is also valuable for anyone who needs to learn how to write just a SELECT statement to access a SQL Server database.
At Course Completion
After attending this course, students will be able to:
- Use the SQL Server Management Studio tool to browse the definition of the database, find necessary object names, create database diagrams, and execute a SQL query to return data rows.
- Write a simple SELECT statement, returning all the columns and rows and formulate meaningful join strategy to bring together all necessary data.
- Write a WHERE clause to filter the rows based on equality, a list of values, a range of values and a wildcard character condition.
- Understand the impact on filtering based on implicit data conversions using data type precedence and how to create a proper search argument.
- Control the display of data in a SQL SELECT including renaming columns, using simple expressions with function to change output values, conditionally change the output values using CASE expressions, sort the result set, eliminate duplicate rows and control the number of rows returned.
- Write Transact-SQL queries that aggregate values across rows based a group and filter the result set based on aggregated values.
1. Discovering the Database
- Using SQL Server Management Studio (SSMS)
- Identifying table relationships
- SSMS Diagram Support
- Preparing to Write a SQL SELECT
2. Accessing Tables
- The Basic SELECT
- Combining Two Tables with a Join
- Inner Join
- Outer Join
- Combining Multiple Tables with a Join
3. Filtering the Data
- Basic Filtering with WHERE clause
- Equality expressions
- Wildcard filtering
- Complex Filtering
- Range and List-based Filtering
- Using Built-in Functions
4. Displaying Data
- Defining the Result Set
- Manipulating Numbers and Data Types
- Using Conditional Column Output
- NULL Functions
- CASE Expressions
- Controlling Output Rows
- TOP operator
5. Aggregating Data
- Grouping and Summarizing Data
- Filtering Aggregate
Before attending this course, students should have:
- Ability to work with the Windows operating system
- Windows login, startup of applications and programs, menu and toolbar manipulation skills
- Basic knowledge of the purpose of a database
Venue: LIVE Online
Live Online Training
Get the same training you expect in the classroom without leaving your office or home. These are NOT recorded classes. They are LIVE sessions with an expert instructor. We use the latest in video conferencing technologies and audio so you can confidently participate in any class just like being right there in person. We guarantee the effectiveness of our online training delivery approach that we will give you your money back if you are not totally satisfied. Ask us for a demo.
Online class requirements:
- Moderate to fast Internet
- A phone or computer headset is required in order to hear the instructor/moderator). You can use Computer Audio (VoIP) or you can dial in from a regular phone. For convenience, we recommend a hands-free headset or phone.
- Training software must be installed on your computer (trial versions are acceptable)
- RECOMMENDED: Dual Monitors or computers. For optimal online learning experience, we recommend participants have dual monitors or two computers. Your online classroom credentials allow you to join multiple times from multiple computers. Participants should use one monitor or computer to view the instructor’s shared screen and another monitor or computer to work with the software.
What happens when you enroll in an online class
When you register for an online class, you will receive a welcome email followed by login access to the Citrix GoToTraining virtual classroom. A workbook (printed copy or eBook) will be sent to you prior to the start of class.
Online Training Advantages
Convenience: You don’t have to travel and can attend from your home, office or anywhere with an internet connection. Our online classes are conducted using GoToTraining, a more robust version of the popular GoToMeeting screen sharing and conferencing platform. To accommodate multiple time zones, courses are typically scheduled from 10am – 5pm Eastern with a one-hour lunch break at 12:30 – 1:30 pm Eastern and a 10-minute break in the morning and afternoon. When conducting custom online course for your group, class times can be modified to accommodate your timezone.
Interactive Learning: Our online training is fully interactive. You can speak and chat with the instructor and classmates at any time. Various interactive techniques are used in every class. Our small class sizes (typically 4 – 8 students), allow our instructors to focus on individual performance and issues and to work closely with you to meet your unique needs. Classes are designed to be a hands-on learning experience, providing opportunities for you to try your new skills while the instructor is available for review, questions, and feedback. You have the option to give the instructor permission to view your computer to provide one-on-one assistance when needed.