SQL Course: Level 2b

SQL. Work smarter, not harder.

Learn SQL the smart way with SQL training courses at CCE, the University of Sydney.

This is the second part of a two-part course (see SQL Level 2a) that teaches you how to interrogate and manipulate the structures of a relational database management system.

The content of this course is no more difficult than the content of SQL Level 2a, it merely extends your knowledge of SQL, exposing you to useful structures in relational database management systems from which to run your code (Triggers, Stored Procedures, Table Valued Functions, Views, User Defined Functions), building on your knowledge of SQL gained in SQL Level 2a, extending the use of some of the clauses covered there and introducing more clauses and keywords helping you become more expert in SQL’s current power and modern usage.

Outcomes

Upon successful completion of this course, participants should be able to:

1. Expand their ability to solve problems with SQL with the extra functionality of the clauses covered in the previous courses.
2. Use new clauses and structures designed to solve more difficult problems.
3. Learn new techniques for problem solving (recursion and ‘row’ arithmetic) and how to use them in SQL.
4. Learn ways to store SQL code for reusability.
5. Execute stored SQL code from other applications.

Content

This SQL training course covers the following topics:

• Learn the power of using the result sets of other queries in further queries with table expressions including Derived Tables, Common Table Expressions (CTEs), Table Valued Functions, Views and Temporary Tables.
• Understand the design problems with Derived Tables and their resolution with the structure of CTEs.
• Use CTEs for to recursively derive result sets (looking again and again for further rows for found rows such as an organisational management hierarchy or spare parts replacement history).
• Apply a table valued function or query to the rows of another query with CROSS APPLY and OUTER APPLY.
• Further explore the use of Windowed columns and Windowed columns with Frames utilising the functions LAG, LEAD, FIRST_VALUE, LAST_VALUE and Aggregate functions to relate values in rows of a result set to other rows in a result set (For example: creating running totals for customers orders or performing arithmetic with values on different rows in a result set).
• Extend the power of the GROUP BY clause with Multiple Grouping Sets using the GROUPING_ID function to create concatenated English sentence reporting columns.
• Implement Transactions and discuss Transaction Isolation Levels.
• Learn the purpose, power and essential place of stored procedures in multi-user systems, creating, dropping and altering them and implementing Input and Output Parameters. Build stored procedures with control of flow statements (If statements, Return, Begin End blocks ). Implement a client application that calls a stored procedure (using VBA and ADO to illustrate) and displays results in a client application using code and middle ware that is common in principle to all client/server applications.

Implement triggers to enforce the types of referential integrity not supported by DRI, to enforce data integrity rules not possible with constraints and record an audit trail.

• Build custom functions and discuss where they can be used.
• Handle errors handling in SQL scripts, stored procedures and triggers and create custom error messages.

Intended Audience

This advanced course is suitable for participants who have previously completed SQL Level 2a.

Delivery Style

This course is delivered as presenter-taught computer-based training.

Additional Information

Please bring a USB flash drive to class if you would like to make a copy of your work or any relevant class materials. Alternatively, you can save these to a cloud storage space or email them to your personal email address.