SQL Course: Level 2b
SQL. Work smarter, not harder.
COVID-19 update: arrangement of our courses
We are now delivering courses online and in-person. Please check the delivery format of each class before enrolling.
Please note that course materials (excluding prescribed texts) are shared electronically within 48 hours of course commencement. 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).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 style
- Presenter-taught training in a computer lab
- Online training via the platform Zoom
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
If you are attending an online class, you will need your own device.
Materials
You will be provided with a link to download and access course materials prior to class.
Features
- $50 repeat class - Conditions apply
- Expert trainer
- Small class size
- CCE Statement of Completion
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.
<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
...When | Time | Where | Session Notes |
---|---|---|---|
Thu 25 Mar 2021 | 9am - 4:30pm (UTC+11:00) | Room 300 - Face-to-face (CCE Building, Newtown) | |
Fri 26 Mar 2021 | 9am - 4:30pm (UTC+11:00) | Room 300 - Face-to-face (CCE Building, Newtown) | |
Thu 01 Apr 2021 | 9am - 4:30pm (UTC+11:00) | Room 300 - Face-to-face (CCE Building, Newtown) |
<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
...When | Time | Where | Session Notes |
---|---|---|---|
Thu 03 Jun 2021 | 9am - 4:30pm (UTC+10:00) | Online via Zoom - Online via Zoom | |
Fri 04 Jun 2021 | 9am - 4:30pm (UTC+10:00) | Online via Zoom - Online via Zoom | |
Thu 10 Jun 2021 | 9am - 4:30pm (UTC+10:00) | Online via Zoom - Online via Zoom |
If there isn't a class to suit you, please join the waiting list.
Related content. Further your learning.
What others say.
- Great tutor, excellent communicator, I wouldn't hesitate to recommend these courses.
- 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.
Subjects
- Arts and humanities
- Business and management
-
English
-
Horticulture and gardening
- HSC preparation (years 10-12)
-
Human resources
- Information technology
- Language and culture
- Marketing
- Music and film
- Project management
- Psychology and psychiatry
-
Public sector