Program Overview

Centre for Continuing Education

The Centre for Continuing Education at the University of Sydney offers a diverse range of professional development short courses. These courses are designed to enhance professional development, grow technical and people skills, and support career progression.

Course Subjects

The Centre offers courses in various subjects, including:

• Business and management
  • Business communication
  • Business strategy
  • Business writing
  • Cultural competence and diversity
  • Finance
  • Leadership
  • Management
  • Organisational psychology
  • Sales and customer service
• English
• HSC preparation (years 10-12)
  • HSC biology
  • HSC business studies
  • HSC chemistry
  • HSC economics
  • HSC English
  • HSC mathematics
  • HSC physics
  • Year 11 (Revision)
  • Years 10-12 study and essay skills
• Humanities and culture
  • Creative writing
  • Music
  • Philosophy
  • Psychology
• Information technology
  • Adobe
  • AI and machine learning courses
  • Data analysis and analytics
  • Google
  • Microsoft
• Language
  • Arabic
  • Brazilian Portuguese
  • Chinese
  • French
  • German
  • Greek
  • Italian
  • Japanese
  • Spanish
• Marketing
  • Digital marketing
  • Marketing communications
  • Product development
• Project management
  • Agile methodology courses
  • Change management for projects
  • Stakeholder and people management
  • Technical project management skills

SQL Course: Level 2b

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

Course Description

The content of this course is no more difficult than the content of SQL Course: Level 2a. It merely extends students' knowledge of SQL, exposing them to useful structures in relational database management systems from which to run their code (Triggers, Stored Procedures, Table Valued Functions, Views, User Defined Functions). This course builds on students' 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 students become more expert in SQL's current power and modern usage.

Outcomes

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

• Expand their 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 course covers:

• 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 course is suitable for those who have previously completed the SQL: Level 2a course, or who have equivalent knowledge and understanding of content covered in the SQL: Level 2a course outline.

Delivery Modes

The course is offered in two 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 students do not need to bring their own device.

Online Classes

Students will need their own device. If they are using a Mac, they will need Windows installed alongside MacOS so that they can run the SQL Server on their computer.

Materials

Course materials are distributed electronically using Dropbox.

Organisational Training

For groups of six or more, courses can be delivered to the team with the option to tailor content.

Related Courses

Related courses include:

• SQL Course: Level 1
• SQL Course: Level 2a

Meet the Facilitators

The course facilitator is John Cumming, who has been teaching for more than 20 years. During this time, he has designed courses and taught at TAFE and universities at the undergraduate and postgraduate levels, including helping PhD students.

What Others Say

Previous students have praised the course, saying it was "the best course I have attended in a while" and that the tutor was "great" and the course was "very hands-on". They also appreciated the material, which was "fantastic" and covered everything they wanted to learn in detail.