SQL Course: Level 2b | CCE
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 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.

We encourage you to use the CCE SQL level self-assessment tool if you are unsure which course level to enrol in.

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 who have equivalent knowledge and understanding of content covered in the SQL: Level 2a course outline.

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.

Features

  • $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.

Syntax error in "{block name:"Course Meta Content - SQL Course - Le..."
  1. There's no block with the name: Course Meta Content - SQL Course - Level 2b - SQL3

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

...

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.
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.