Training Services
Microsoft SQL Server 2005 Querying with Transact-SQL: Complete Course
Course Code: SQL25TS
Course Duration: 5 days
Price: £750 for the first delegate in a group and £250 for each additional delegate †
Introduction
This 5 day course comprises the complete material covered in the series ‘Microsoft SQL Server 2005 Querying with Transact-SQL Part 1’ through to ‘Microsoft SQL Server 2005 Querying with Transact-SQL Part 5’ and is intended for students new to querying with Microsoft SQL Server products and who need a total immersion in the subject on this latest release. This is a comprehensive and intensive course with plenty of illustrated examples and augmented with practical hands-on exercises. The course is applicable to working with all versions of SQL Server 2005 including SQL Server 2005 Express with Advanced Services.
Prerequisites
Students should ideally be familiar with basic programming concepts.
Understand the fundamental design of relational databases and the concept of data normalization.
Objectives
Use SQL Server Management Studio
Understand the Syntax of Transact-SQL
Retrieve, Filter and Sort Data
Query Data from Multiple Tables by Using Joins
Summarize and Rank Grouped Data
Combine and Limit Result Sets
Work with Subqueries
Use Crosstab Queries
Use Common Table Expressions
Understand Transactions
Modify Data
Query XML Data
Query Full Text Indexes
Query Metadata
Run Distributed Queries
Understand how to Use Other Programmable Objects
Handle Errors Gracefully
Course Content1. Introduction to SQL Server Management Studio
Identifying the Elements of SQL Server Management Studio
Objects in a SQL Server Database
Database Diagrams
Components of a SQL Server Database Table
Native SQL Server Data Types
Creating and Using SQL Server Solutions
Executing a Query in SQL Server Management Studio
2. Introduction to SQL and Transact-SQL
ANSI SQL vs. T-SQL (Transact-SQL)
Categories of SQL Statements
T-SQL Scripts
Using Batches
Adding Comments to T-SQL Code
Working with Variables
Conditional Statements
3. Using the SELECT Statement to Retrieve Data
Examine the Basic Syntax of the SELECT Statement
Retrieving Columns of Data from a Table
Using Aliases for Column Names
Using Aliases for Table Names
4. Retrieving Specific Rows of Data Using the WHERE Clause
How to Use the WHERE Clause
Overview of Operators
Filter Data by Using Comparison Operators
Filter Data by Using String Comparisons
Filter Data by Using Logical Operators
Filter Data Using a Range of Values
Filter Data Using a List of Values
Working with NULL Values
Introducing Native SQL Server Functions
Functions Relating to NULL Values
5. Formatting and Sorting Result Sets
Sorting Data Using the ORDER BY Clause
Eliminating Duplicate Rows
Using String Literals
Using Expressions
6. Querying Data from Multiple Tables by Using Joins
Introduction to Joins
Using Inner Joins
Using Outer Joins
Using Cross Joins
Joining More than Two Tables
Joining a Table to Itself
Using Non-Equi Joins
How to Join a Table to a User-Defined Function
7. Summarizing Data Using Aggregate Functions
Using Aggregate Functions Native to SQL Server
Using Aggregate Functions with NULL Values
Grouping Summarized Data with GROUP BY
Filtering Grouped Data Using the HAVING Clause
Using the ROLLUP and CUBE Operators
Using the COMPUTE Clause
How to Implement Custom Aggregate Functions
8. Ranking Grouped Data
Ranking Data Using Ranking Functions
Using ROW_NUMBER
Using RANK
Using DENSE_RANK
Using NTILE
Summary of Ranking Functions Based on Their Functionality
9. Combining and Limiting Result Sets
Combining Result Sets Using the UNION Operator
Limiting Result Sets Using the EXCEPT and INTERSECT Operators
Identifying the Order of Precedence of UNION, EXCEPT, and INTERSECT
Limiting Result Sets Using the TOP Operator
Limiting Result Sets Using the TABLESAMPLE Operator
10. Working with Subqueries
Introduction to Subqueries
Using Subqueries as Expressions and Derived Tables
Using the ANY, ALL, and SOME Operators
Scalar versus Tabular Subqueries
Using Correlated Subqueries
Using the EXISTS Clause with Correlated Subqueries
Using the APPLY Operator
Subqueries versus Joins
Subqueries versus Temporary Tables
11. Performance Considerations for Writing Queries
How SQL Server Processes T-SQL Queries
How the Query Optimizer Processes Search Arguments
Writing Efficient Search Arguments
12. Crosstab Queries
Using the PIVOT Operator
Using the UNPIVOT Operator
13. Common Table Expressions
Introduction to Common Table Expressions
Using Common Table Expressions
Recursive Queries Using Common Table Expressions
Techniques for Querying Hierarchical Data
14. Introduction to Transactions
What Is a Transaction?
How SQL Server Modifies Data in Tables
Managing Transactions
Nested Transactions
Transaction Isolation Levels
15. Inserting Data into Tables
Inserting a Single Row into a Table
Inserting Multiple Rows into a Table
Inserting Values into Identity Columns
Differentiating Various INSERT Statements
Using the OUTPUT Clause with the INSERT Statement
16. Deleting Data from Tables
Deleting Rows from a Table
Truncating a Table
Deleting Rows Based on Data in Other Tables
Using the OUTPUT Clause with the DELETE Statement
17. Updating Data in Tables
Updating Rows in a Table
Updating Rows Based on Data in Other Tables
Using the OUTPUT Clause with the UPDATE Statement
18. Working with SQL Server Date and Time Data
Data Type Precedence
Implicit Data Type Conversions
Querying and Modifying Date and Time Data
19. Querying XML Data
Introduction to XML
How SQL Server Implements XML
Generating XML Based Reports
Querying XML by Using OpenXML
Introduction to XQuery
Querying XML by Using XQuery
Querying Relational Data Combined with XML Data
20. Querying Full Text Indexes
Overview of Full Text Indexes
Full Text Indexing and the Querying Process
How SQL Server Implements Full Text Indexes
Overview of Full Text Search
Using the CONTAINS Predicate
Using the FREETEXT Predicate
Using the Full Text Functions
Differences Between Full Text Functions and Predicates
Combining Full Text Search and Transact-SQL Predicates
21. Querying Metadata
Different Categories of Data
Grouping Concepts Related to Different Categories of Data
Understanding Metadata
Querying Metadata by Using SQL Server Views
Querying Metadata by Using SQL Server Commands
22. Distributed Queries
Overview of Distributed Queries
Writing Ad Hoc Distributed Queries
Creating a Linked Server
Creating a Distributed Query Using a Linked Server
23. Introduction to Views
Overview of Views
Creating and Modifying a View
Considerations When Creating Views
Examining the Impact of Using SELECT * in Views
Restrictions for Modifying Data by Using Views
24. Introduction to Stored Procedures
Overview of Stored Procedures
How Stored Procedures Are Executed by SQL Server
Creating and Using a Simple Stored Procedure
25. Error Handling
Using @@ERROR
Using RAISERROR
Using TRY…CATCH
26. Introduction to User Defined Functions
Overview of User Defined Functions (UDFs)
Creating and Modify UDFs
Restrictions When Creating UDFs
Implementing Different Types of UDFs
Performance Consideration for Using User-Defined Functions
27. Introduction to Triggers
Overview of Triggers
How Triggers Work
28. Appendix – Introduction to Query File Maintenance
Overview of Versioning and Source Control
Features of Visual SourceSafe
Using Visual SourceSafe for Version Control of Queries
† Notes on Price - Prices quoted above (which are all in UK pounds sterling) represent our standard rates for training and CTR reserves the right to subject these prices to change without notice. The total cost to deliver any course will be confirmed in writing by one of our representatives when we have been furnished with full specific details about required course content, training venue, number of delegates and such other relevant information. Please contact us for a quote. Thank you.
Computer Training Resources can provide on-site instructor led training courses throughout the UK. If your organization is based in Amber Valley, Avon, Bedfordshire, Berkshire, Bolsover, Buckinghamshire, Cambridgeshire, Cheshire, Chesterfield, Cleveland, Cornwall, County Durham, Cumberland, Cumbria, Derbyshire, Derbyshire Dales, Devon, Dorset, Durham, East Sussex, East Yorkshire, Erewash, Essex, Gloucestershire, Greater London, Greater Manchester, Hampshire, Herefordshire, Hertfordshire, High Peak, Humberside, Huntingdonshire, Isle of Wight, Isles of Scilly, Kent, Lancashire, Leicestershire, Lincolnshire, Merseyside, Middlesex, Norfolk, North East Derbyshire, North Yorkshire, Northamptonshire, Northumberland, Nottinghamshire, Oxfordshire, Rutland, Shropshire, Somerset, South Derbyshire, South Yorkshire, Staffordshire, Stockport, Suffolk, Surrey, Tyne And Wear, Warwickshire, West Midlands, West Sussex, West Yorkshire, Westmorland, Wiltshire, Worcestershire, Aberdeenshire, Angus, Argyllshire, Ayrshire, Banffshire, Berwickshire, Buteshire, Cromartyshire, Caithness, Clackmannanshire, Dumfriesshire, Dunbartonshire, Dumbartonshire, East Lothian, Edinburghshire, Fife, Forfarshire, Haddingtonshire, Inverness-shire, Kincardineshire, Kinross-shire, Kirkcudbrightshire, Lanarkshire, Linlithgowshire, Midlothian, Morayshire, Nairnshire, Orkney, Peeblesshire, Perthshire, Renfrewshire, Ross-shire, Roxburghshire, Selkirkshire, Shetland, Stirlingshire, Sutherland, West Lothian, Wigtownshire, Anglesey, Brecknockshire, Caernarfonshire, Carmarthenshire, Cardiganshire, Denbighshire, Flintshire, Glamorgan, Merioneth, Monmouthshire, Montgomeryshire, Pembrokeshire, Radnorshire, England, Scotland or Wales then Computer Training Resources can offer you on-site instructor led training. Specialists in delivering Microsoft SQL Server training.
Computer Training Resources can deliver on-site instructor led training courses in your town or city, anywhere in the UK, for example: Alfreton, Altrincham, Ambergate, Ashbourne, Ashford, Bakewell, Barlborough, Barnsley, Basildon, Basingstoke, Bath, Bedford, Bedworth, Belper, Beverley, Birmingham, Birkenhead, Blackburn, Blackfriars, Blackpool, Bloomsbury, Bolton, Borehamwood, Borrowash, Boston, Bournemouth, Bracknell, Bradford, Braintree, Breadsall, Brentwood, Bridlington, Brighton, Brimington, Bristol, Buckingham, Burnley, Burton-upon-Trent, Buxton, Camberley, Cambridge, Camden Town, Canary Wharf, Cannock, Canterbury, Carlisle, Central London, Chapel en le Frith, Chatsworth, Chelmsford, Cheltenham, Chester, Chesterfield, City of London, Clay Cross, Clerkenwell, Clowne, Codnor, Colchester, Congleton, Covent Garden, Coventry, Crewe, Crich, Cromford, Darley Dale, Daventry, Denby, Derby, Derby City Centre, Derby City Council, Derby Derbyshire, Doncaster, Dorchester, Dorking, Dover, Driffield, Dronfield, Duffield, Durham, Ealing, Eastbourne, Eastleigh, Eastwood, Eckington, Etwall, Euston, Exeter, Falmouth, Felixstowe, Feltham, Filey, Findern, Folkestone, Gateshead, Gillingham, Glossop, Gloucester, Godalming, Grantham, Gravesend, Greenwich, Gresley, Grimsby, Guildford, Halifax, Harlow, Harrogate, Harrow, Hartshorne, Harwich, Hastings, Hatfield, Hayfield, Heage, Heanor, Hereford, Hertford, High Wycombe, Hinckley, Holbrook, Hornsea, Hounslow, Hucknall, Huddersfield, Hull, Huntingdon, Ilkeston, Ipswich, Kendal, Kilburn, Killamarsh, Kings Cross, Kingston, Lancaster, Langley Mill, Leamington Spa, Leeds, Leicester, Leicester Square, Letchworth, Lichfield, Lincoln, Linton, Liverpool, London, London Bridge, Long Eaton, Loughborough, Louth, Macclesfield, Maidenhead, Maidstone, Manchester, Mansfield, Matlock, Melbourne, Middlesbrough, Milton Keynes, Mosborough, Nantwich, New Mills, Newark, Newbury, Newcastle, Newhaven, Newmarket, Newport, North Wingfield, Northampton, Norwich, Nottingham, Nuneaton, Orpington, Oxford, Overseal, Paddington, Paignton, Penrith, Penzance, Peterborough, Piccadilly, Pinxton, Plymouth, Poole, Portsmouth, Preston, Prestwich, Reading, Repton, Retford, Richmond, Ripley, Ripon, Romford, Rotherham, Rugby, Rugeley, Runcorn, Salford, Salisbury, Sandiacre, Sawley, Scunthorpe, Sevenoaks, Sheffield, Shirebrook, Shirland, Shrewsbury, Skegness, Slough, Smalley, Soho, South Normanton, Southampton, Southend, Southport, Southwark, Southwell, St Albans, St Helens, Stafford, Staines, Stamford, Staveley, Stevenage, Stockport, Stoke-on-Trent, Stourbridge, Stratford, Stroud, Sunderland, Sutton Coldfield, Swadlincote, Swanage, Swanley, Swindon, Swiss Cottage, Tamworth, Taunton, Telford, Tewkesbury, Tibshelf, Tilbury, Torbay, Torquay, Uttoxeter, Uxbridge, Wakefield, Walsall, Wandsworth, Warrington, Warwick, Waterloo, Watford, Wellingborough, West Byfleet, West End of London, West Hallam, Westminster, Weybridge, Weymouth, Whaley Bridge, Whitehaven, Wigan, Winchester, Windsor, Wingerworth, Wirksworth, Woking, Wokingham, Wolverhampton, Worcester, Workington, Worksop, Worthing, Yeovil, York, Aberdeen, Cumbernauld, Dumfries, Dundee, Edinburgh, Fort William, Glasgow, Grangemouth, Hamilton, Inverness, Isle of Skye, Kirkcaldy, Livingston, Perth, Portree, Stirling, Cardiff, Swansea, Wrexham, Belfast, Douglas, Guernsey and Jersey. Specialists in delivering Microsoft SQL Server training.