Overview
This course aims to provide the technical skills required to write basic Transact-SQL queries against most databases. T-SQL is used in Sybase, MS SQL Server and is compatible with a range of other products including Oracle, DB2, and MS Access
Learning method
Instructor led.
The course features lectures with multimedia presentations, tutorial discussions, and practical hands-on exercises. Sessions are conducted in a PC equipped laboratory
Who will benefit from this course?
Those needing to query and alter data in modern database systems such as database administrators, implementers, system engineers, and developers who are responsible for writing queries.
Pre-requisites
Participants should have prior experience using the Microsoft Windows operating system.
It is also important to have some understanding of basic relational database concepts and be familiar with the role of a database administrator.
What can you expect to gain from this course?
Upon completion of this course you will be able to:
- describe the uses of and the ways to execute the Transact-SQL language
- use querying tools
- write SELECT queries to retrieve data
- group and summarise data by using Transact-SQL
- join data from multiple tables
- write queries that retrieve and modify data by using subqueries
- modify data tables
- query text fields with full-text search
- describe how to create programming objects.
Course Content
Module 1:Introduction to Transact-SQL
- Programming language
- Understanding the types of Transact-SQL statements
- Transact-SQL syntax elements
Module 2:Using Transact-SQL
- Using SQL Query Analyser
- Using object browser tool in SQL
- Using the OSQL utility query analyser
- Transact-SQL statements
- Creating and executing Transact-SQL scripts
- Retrieving data using the SELECT statement
- Filtering data
- Formatting results sets
- Processing queries
- Understanding performance considerations
- Retrieving data and manipulating results sets
Module 4:Grouping and summarizing data
- Listing the Top n Values
- Using aggregate functions
- Grouping fundamentals
- Generating aggregate values within result sets
- Using the COMPUTE
- Using the COMPUTE by clauses
Module 5:Joining multiple tables
- Using aliases for table names
- Combining data from multiple tables
- Combining multiple result sets
- Querying multiple tables
Module 6:Working with subqueries
- Introducing subqueries
- Using a subquery as a derived table
- Using a subquery as an expression
- Using a subquery to correlate data
- Using the EXISTS and NOT EXISTS clauses
- Working with subqueries
- Using transactions
- Inserting, deleting and updating data
- Performance considerations
- Modifying data
Module 8:Querying full-text indexes
- Introducing Microsoft Search Service
- Understanding Microsoft Search Service components
- Getting information about full-text indexes
- Writing full-text queries
- Querying full-text indexes
Module 9:Introduction to programming objects
- Displaying the text of a programming object
- Introduction to views
- Evaluating the advantages of views
- Creating views
- Introducing stored procedures
- Introducing triggers
- Introducing user-defined functions
- Working with views