SQL Course: Level 2a
SQL. Work smarter, not harder.
This SQL course will show you how to extract and change data in relational databases with SQL (Structured Query Language) that traverses multiple tables with Select, Insert, Update and Delete statements, functions and scripts. SQL is a current hot skill, valued by employers.
Microsoft SQL Server is used for hands on exercises during this course. However, the content of the SQL course is kept as close as possible to standard SQL.
Please note, there will be no instruction on how relational databases work in this course. If you do not understand how databases work or why databases are designed in the way they are or you have never written a simple SQL query from scratch, please complete SQL: Level 1 training before you attempt this SQL training course.
This course is the first part of a two-part coverage of the essentials of traditional and modern SQL, both of which you will need to employ to successfully interrogate and manipulate the structures of relational database management systems.
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 on the clauses learnt in SQL: Level 1
- include more clauses in an SQL SELECT statements (their purposes, what they consume and produce, and execution order)
- build rows for a query from multiple tables and table expressions in the FROM clause
- perform calculations such as subtotals and grand totals in multiple ways
- write INSERT, UPDATE and DELETE statements in various forms to add, change and delete rows in tables
- solve more complex problems by consuming the result sets of SQL statements in other SQL statements
- build tables and columns, applying constraints in SQL
- advance into the basics of scripting in preparation for creating stored procedures, views, functions and triggers (covered in SQL: Level 2b).
Content
- A brief recap of the content covered in SQL: Level 1 including:
- an overview of SQL Server Management Studio
- scripting conventions
- commenting
- creating derived columns with arithmetic
- concatenating data
- aliases and the SELECT clause
- Wildcard, the basic FROM clause and the basic WHERE clause. This recap is mainly for people who work in environments other than SQL Server Management Studio to allow them to get up to speed with SQL Server Management Studio by practicing some simple SQL and also to allow the people who did the SQL 1 course to get their “heads back into SQL”
- Understanding and using relational and logical operators (AND, OR, BETWEEN, NOT, IN, LIKE and IS NULL)
- Using FETCH, OFFSET to retrieve a subset rows relative to a row
- Rationalising data sets using DISTINCT
- RetrievING aggregated data into grand totals and subtotals using GROUP BY
- Filtering aggregated result sets with HAVING
- UNION, INTERSECT and EXCEPT queries
- Applying output selection logic to the select clause with simple and searched CASE
- Joining multiple tables in a query (INNER joins, OUTER (LEFT, RIGHT and FULL) joins, CROSS joins)
- Table aliasing
- Further aggregation with multiple dimensions using ROLLUP, CUBE, the GROUPING function and the CASE statement combined
- Queries that use sub-queries in the SELECT, FROM and WHERE clause
- Using the view builder to form the basis of a SQL statement
- Using SELECT INTO to create tables from existing data
- Inserting individual and multiple rows with INSERT
- Modifying individual and multiple rows with UPDATE
- Removing individual and multiple rows with DELETE
- The OUTPUT clause
- The importance and use of intrinsic SQL functions
- Understanding common SQL Server data types, data type precedence and implicit versus explicit data type conversion
- Scripting basics and variables
- How indexes work
- Essential DDL statements in SQL and their use in modifying tables, normalising data and increasing query execution speed
- Identity columns
- The basics of Windowed columns with the OVER clause using PARTITION BY and ORDER BY, Row numbering, tiling, aggregate and ranking functions
Intended audience
Suitable for those who have completed SQL: Level 1, or who have equivalent knowledge and understanding of content covered in the SQL: Level 1 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
Course materials are distributed electronically using Dropbox.
Overview
Sessions:
Location:
Price:
Class schedule
<p>{block name:"Course Tagline - SQL"}</p>
<p>This SQL course will show you how to extract and change data in relational databases with SQL (Structured Query Language) that traverses multiple tables
...Thu 28 Nov 2024
9am - 4:30pm (UTC+11:00)
Fri 29 Nov 2024
9am - 4:30pm (UTC+11:00)
Mon 2 Dec 2024
9am - 4:30pm (UTC+11:00)
<p>{block name:"Course Tagline - SQL"}</p>
<p>This SQL course will show you how to extract and change data in relational databases with SQL (Structured Query Language) that traverses multiple tables
...Thu 6 Mar 2025
9am - 4:30pm (UTC+11:00)
Fri 7 Mar 2025
9am - 4:30pm (UTC+11:00)
Mon 10 Mar 2025
9am - 4:30pm (UTC+11:00)
<p>{block name:"Course Tagline - SQL"}</p>
<p>This SQL course will show you how to extract and change data in relational databases with SQL (Structured Query Language) that traverses multiple tables
...Thu 10 Apr 2025
9am - 4:30pm (UTC+10:00)
Fri 11 Apr 2025
9am - 4:30pm (UTC+10:00)
Mon 14 Apr 2025
9am - 4:30pm (UTC+10:00)
<p>{block name:"Course Tagline - SQL"}</p>
<p>This SQL course will show you how to extract and change data in relational databases with SQL (Structured Query Language) that traverses multiple tables
...Thu 19 Jun 2025
9am - 4:30pm (UTC+10:00)
Fri 20 Jun 2025
9am - 4:30pm (UTC+10:00)
Mon 23 Jun 2025
9am - 4:30pm (UTC+10:00)
<p>{block name:"Course Tagline - SQL"}</p>
<p>This SQL course will show you how to extract and change data in relational databases with SQL (Structured Query Language) that traverses multiple tables
...Mon 12 May 2025
9am - 4:30pm (UTC+10:00)
Tue 13 May 2025
9am - 4:30pm (UTC+10:00)
Fri 16 May 2025
9am - 4:30pm (UTC+10:00)
If there isn't a class to suit your preferred time or delivery format, please JOIN the waiting list.
Featured 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...
What others say
All elements of this course were great - perfect for someone to learn some of the key elements of SQL. Would not hesitate to recommend this course. I'm looking forward to attending 2b in the coming months.
- Steve Austin
Great balance between content and application - one of the best I've experienced. Teacher is clearly a pro.
- Roy Elton
This is a well-structured course taught by an excellent tutor who aims for all his students to understand the material, without leaving anyone behind.
- Kai Meng