Class Details
SQL Course: Level 2b
Course summary
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.
Learning 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
Who this course is for
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
Materials
Course materials are distributed electronically using Dropbox.
What you need to do before the course
For this course, you will need Microsoft SQL Server Express and Microsoft SQL Server Management Studio installed on your device.
Face-to-face classes (CCE, Sydney)
Classes are held in a computer lab. You do not need to bring your own device or login details.
Online classes
For the best experience, we recommend using a computer with a large monitor or dual screens/devices. Small laptop screens can make it difficult to follow the facilitator’s display while working in the software.
Sessions
Location
Face-to-face (CCE Building, Sydney)
Public transport
As bus and train timetables regularly change, we suggest you visit the Transport for NSW trip planner before you set out.
The nearest train stations are Newtown Station and Macdonaldtown Station. Macdonaldtown Station is closer by distance than Newtown Station, however trains do not stop as frequently as at Newtown Station.
Other information
Parking
Nearby parking is available at Wilson Parking - RPA Medical Centre Car Park (entry via Elizabeth Street off Carillon Avenue).
A Flexi Saver Rate is available but must be booked in advance online and includes the booking fee. Bookings can be made via the Wilson website or by downloading the Wilson Parking App. Otherwise, hourly parking rates will apply, as advertised on the Wilson Parking website.
Meet the facilitators
John Cumming
John 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 post graduate levels, including helping PhD students manage research data.
In over 30 years of programming and data management, John has consulted for many companies large and small, including Westpac, Readers Digest, PwC, Thales and a research group for Novartis.
Along the way, John has achieved qualifications including an Associate Diploma in commercial data processing, certificates in Program Design and Networking, and progress towards a Masters degree.
John’s passion for learning has seen him teach himself several coding languages and explored programming in many ways including successfully programming computers to enhance the lives of people with disabilities.
