SQL Advanced (Enterprise Databases)

This course teaches the development techniques needed to take full advantage of powerful and flexible data-driven applications. Students will learn how to use data in extensive, enterprise-class databases.

After completing this course, students will be able to:

• Query data simultaneously from multiple, related tables
• Design subqueries and correlated subqueries
• Use unions to combine queries into a single result set
• Performance-tune your queries
• Apply views to shield users from underlying data complexity
• Execute triggers
• Create stored procedures
  

Target audience

Students should have a good working knowledge of SQL and database design gained through prior experience or from an attendance on an introductory level course.

Course content

Entity-Relationship Diagrams

Text Functions Matching Patterns with LIKE • Text manipulation functions • Exercise 1: Text Manipulation Functions • Dates in SQL Server • Exercise 2: Date Handling • Conversion Functions • Using Union to Combine Queries • Exercise 3: Using Union • Transact-SQL's CASE statement

A Review of Aggregate Functions GROUP BY • HAVING • WITH CUBE and WITH ROLLUP • COMPUTE BY • Exercise 4: Fancy Aggregate Functions

Querying Data Across Tables Joins the ANSI-92 Way • Outer Joins • Exercise 5: Using an Outer Join to Show Sales Information • Self Joins • Exercise 6: Self-join and an organization chart

Subqueries Exercise 7: Subqueries • Correlated Subqueries • Exercise 8: Correlated Subqueries • Performance Tuning • Connecting to Data in Other Databases

Beginning Transact-SQL Programming Introduction to Stored Procedures • Exercise 9: A Simple Stored Procedure • Transactions in SQL Server • Exercise 10: Using a Stored Procedure to Calculate YTD Sales • Triggers • Exercise 11: Triggers

Appendix A: The Books Database Entity-Relationship Diagrams • Create Table Statements

Appendix B: Recommended Resources

About Courseware Company :: Instructor-led training courseware :: Self-study solutions :: Buy online today