SQL Course: Level 2a

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

You will be provided with a link to download and access course materials prior to class.