Centre for Continuing Education

SQL Course: Level 2a

SQL. Work smarter, not harder.

Learn SQL the smart way with SQL training courses at CCE, the University of Sydney.

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

Outcomes

Upon successful completion of this course, participants should be able to:

  1. Expand on the clauses you learnt in the Level 1 Introduction cause.
  2. Include more clauses in an SQL SELECT statements (their purposes, what they consume and produce, and execution order).
  3. Build rows for a query from multiple tables and table expressions in the FROM clause.
  4. Perform calculations such as subtotals and grand totals in multiple ways.
  5. Write INSERT, UPDATE and DELETE statements in various forms to add, change and delete rows in tables.
  6. Solve more complex problems by consuming the result sets of SQL statements in other SQL statements.
  7. Build tables and columns, applying constraints in SQL.
  8. Advance into the basics of scripting in preparation for creating stored procedures, views, functions and triggers (covered in SQL Course: Level 2b).

Content

This SQL training course covers the following topics:

  • A brief recap of the content covered in SQL Course: Level 1 (note: no database design content from Level 1 course will be repeated) including an overview of SQL Server Management Studio, following 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”.
  • Understand and use relational and logical operators (AND, OR, BETWEEN, NOT, IN, LIKE and IS NULL).
  • Use FETCH, OFFSET to retrieve a subset rows relative to a row.
  • Rationalising data sets using DISTINCT.
  • Retrieve aggregated data into grand totals and subtotals using GROUP BY.
  • Filter aggregated result sets with HAVING.
  • UNION, INTERSECT and EXCEPT queries.
  • Apply output selection logic to the select clause with simple and searched CASE.
  • Join 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.
  • Use the view builder to form the basis of a SQL statement.
  • Use SELECT INTO to create tables from existing data.
  • Insert individual and multiple rows with INSERT.
  • Modify individual and multiple rows with UPDATE.
  • Remove individual and multiple rows with DELETE.
  • The OUTPUT clause.
  • The importance and use of intrinsic SQL functions.
  • Understand 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

This SQL Level 2 course is suitable for participants who have completed SQL Level 1 or those who have a prior understanding of relational database management.

Delivery Style

This SQL Level 2 course is delivered as presenter-taught computer-based training.

Features

  • Free repeat class - Conditions apply
  • Free USB flash drive
  • Expert trainer
  • Dedicated computer for every student
  • Small class size
  • Student notes – yours to keep
  • Statement of completion

What others say.

  • Perfect duration, and breaking up the 3 days over the two different weeks helped us digest what we’d learned before we resumed the next bit, and I think helps commit the learnings to longer term memory. The workbook was well laid out and easy to follow and will make a great future reference too. The lunch provided was delicious and plentiful, and the location is fairly easily accessed by public transport. Each day was the perfect duration for the content being delivered. And the free wifi for use in breaks was also a bonus.

  • A well-structured, thoughtfully assembled course. Thoroughly impressed with the content and the seamless and logical progression through the material. The tutor was engaging, did an excellent job of holding peoples attention, checking that participants were grasping the material, as well as balancing the answering of questions with ensuring the class moved on through the material to make it worth the cost. I was also impressed with the considered use of small breaks to keep us engaged and maintain energy and focus.

  • Just want to say this is a very valuable course and I will definitely recommend it to anyone interested in SQL. The instructor has made the course very interesting and easy to understand. I had some knowledge with SQL and have been using it at work before coming to this course. My aim was to fully utilise SQL at my workplace, the tutor has greatly fulfilled this during the course and assisted with lots of in class practice which made the class very vibrant.

  • The tutor has a knack for explaining concepts using easy to understand analogies. This course is a great follow-up to SQL 1.

  • The course was very well paced. Loved how the tutor broke down the SQL structure and made it simpler to read.

  • The tutor is very talented and has an excellent approach in breaking down complex matter into understandable blocks.

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

  • I got a lot out of this course, and would recommend it for those looking for a solid working knowledge of SQL.

  • Excellent course, would definitely recommend it to anyone wanting to get a good understanding of SQL. The Tutor was great.

  • The tutor was fantastic, obviously a real expert in the area. He did such a great job of presenting such a complex topic into manageable learning areas. Highly recommend this course for anyone who needs SQL skills.

  • Great balance between content and application – one of the best I’ve experienced. Teacher is clearly a pro.

  • The instructor has a perfect ability to explain every query in depth throughout the full course, and I am very satisfied with their teaching.

  • Excellent course and instructor. I’ve been to courses costing 10 times as much that paled in comparison.

$911 Limited GST free
SQL Course: Level 2a

<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

...
$911 Limited GST free
SQL Course: Level 2a

<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

...
$911 Limited GST free
SQL Course: Level 2a

<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

...
$911 Limited GST free
SQL Course: Level 2a

<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

...
$911 Limited GST free
SQL Course: Level 2a

<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

...
$911 Limited GST free
SQL Course: Level 2a

<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

...
$911 Limited GST free
SQL Course: Level 2a

<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

...
If there isn't a class to suit you, please join the waiting list.