SQL. Work smarter, not harder.

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 Course: 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).This course builds 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

By the end of this course, you should be able to:

• expand your ability to solve problems with SQL with the extra functionality of the clauses covered in the previous courses
• use new clauses and structures designed to solve more difficult problems
• learn new techniques for problem solving (recursion and 'row' arithmetic) and how to use them in SQL
• learn ways to store SQL code for reusability
• execute stored SQL code from other applications.

Content

• 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

Suitable for those who have previously completed the SQL: Level 2a course, or equivalent.

Delivery modes

• Face-to-face, presenter-taught training in a computer lab
• Online training via the platform Zoom

Delivery style

Face-to-face classes

These classes run in a computer lab and you do not need to bring your own device.

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.

Online classes

You will need your own device. If you are using a Mac, you will need Windows installed alongside MacOS so that you can run the SQL Server on your computer. CCE staff will not be able to assist you with setting up a Windows environment.

An SQL Server Installation Guide (Pdf) will be emailed to you before class.

Materials

You will be provided with a link to download and access course materials prior to class.