Centre for Continuing Education

SQL Course: Level 2b

SQL. Work smarter, not harder.

COVID-19 update: arrangement of our courses

We are now delivering courses both online and in-person. Please check the delivery format for each class before you enrol.

Please note that course materials for all classes (excluding prescribed textbooks) are shared electronically within 48 hours of a course starting. Printing is not available.

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), building on your knowledge of SQL gained in SQL Course: 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.


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.


This 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

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

Delivery style

Delivered as presenter-taught computer-based training.


You will be provided with a link to download and access an eBook prior to class.

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.


  • $50 repeat class - Conditions apply
  • Expert trainer
  • Small class size
  • CCE Statement of Completion

Apply for the IT repeat discount.

What others say.

  • The tutor was great and the course was very hands-on. The material was fantastic and covered everything I wanted to learn in detail. Highly recommended for anyone wanting to expand their SQL skills.

  • The best course I have attended in a while. The course really made me think about different ways of using SQL that I had not previously considered.

  • Great tutor, excellent communicator, I wouldn’t hesitate to recommend these courses.

SQL Course: Level 2b

<p>{block name:“Course Tagline - SQL”}</p><p>{block name:“Block - COVID 19 updates”}</p><p>This is the second part of a two-part course (see <a href="/course/SQL2">SQL Level 2a</a>) that teaches you


What others say.

  • Great tutor, excellent communicator, I wouldn't hesitate to recommend these courses.
We acknowledge the tradition of custodianship and law of the Country on which the University of Sydney campuses stand. We pay our respects to those who have cared and continue to care for the Country.