MU-ADVSQL: Advanced SQL Programming

To view the class schedule you need to become an Affiliate

• Largest “Guaranteed To Run” public technical training schedules available
• Easy to become an Affiliate – no charge or fee

Become an EPIC Affiliate

already an Affiliate?  Login

About this Course

Advanced SQL is a hands-on training course that dives into the advanced SQL topics like inner and outer joins, aggregate functions (AVG, COUNT, SUM, MIN, MAX), data manipulation, using DDL to create and alter tables, transactions, stored procedures, and triggers. The course utilizes the MySQL database but all hands-on work in this course is ANSI SQL compliant and should work with most SQL databases.

Audience Profile

Application developers, data analysts, IT professionals looking to become a database administrator, or anyone looking to learn more advanced SQL skills.

At Course Completion

Outline

Module 1: How to Retrieve Data From Two or More Tables
How to work with inner joins
How to code an inner join
How to use table aliases
How to join to a table in another database
How to use compound join conditions
How to use a self-join
How to join more than two tables
How to use the implicit inner join syntax
How to work with outer joins
How to code an outer join
Outer join examples
Other skills for working with joins
How to join tables with the USING keyword
How to join tables with the NATURAL keyword
How to use cross joins
How to work with unions
How to code a union
A union that combines result sets from different tables
A union that combines result sets from the same tables
A union that simulates a full outer join

Module 2: How to Insert, Update, and Delete Data
How to insert new rows
How to insert a single row
How to insert multiple rows
How to insert default values and null values
How to use a subquery in an INSERT statement
How to update existing rows
How to update rows
How to use a subquery in an UPDATE statement
How to delete existing rows
How to delete rows
How to use a subquery in a DELETE statement

Module 3: How to Code Summary Queries
How to work with aggregate functions
How to code aggregate functions
Queries that use aggregate functions
How to group and summarize data
How to code the GROUP BY and HAVING clauses
Queries that use the GROUP BY and HAVING clauses
How the HAVING clause compares to the WHERE clause
How to code compound search conditions

Module 4: How to Code Subqueries
An introduction to subqueries
Where to code subqueries
When to use subqueries
How to code subqueries in the WHERE clause
How to use the IN operator
How to use the comparison operators
How to use the ALL keyword
How to use the ANY and SOME keywords
How to cde correlated subqueries
How to use the EXISTS operator
How to code subqueries in other clauses
How to code subqueries in the HAVING clause
How to code subqueries in the SELECT clause
How to code subqueries in the FROM clause
How to work with complex queries
A complex query that uses subqueries
A procedure for building complex queries

Module 5: How to Create Databases, Tables, and Indexes
How to work with databases
How to create and drop a database
How to select a database
How to work with tables
How to create a table
How to code a primary key constraint
How to code a foreign key constraint
How to alter the columns of a table
How to alter the constraints of a table
How to rename, truncate, and drop a table
How to work with indexes
How to create an index
How to drop an index
A script that creates a database

Module 6: How to Create Views
An introduction to views
How views work
Benefits of using views
How to work with views
How to create a view
How to create an updatable view
How to use the WITH CHECK OPTION clause
How to insert or delete rows through a view
How to alter or drop a view

Module 7: Language Skills for Writing Stored Programs
An introduction to stored programs
Four types of stored programs
A script that creates and calls a stored procedure
A summary of statements for coding stored programs
How to write procedural code
How to display data
How to declare and set variables
How to code IF statements
How to code CASE statements
How to code loops
How to use a cursor
How to declare a condition handler
How to use a condition handler
How to use multiple condition handlers

Module 8: How to Use Transactions and Locking
How to work with transactions
How to commit and rollback transactions
How to work with save points
How to work with concurrency and locking
How concurrency and locking are related
The four concurrency problems that locks can prevent
How to set the transaction isolation level
How to lock selected rows
How to prevent deadlocks

Module 9: How to Use Transactions and Locking
How to code stored procedures
How to create and call a stored procedure
How to code input and output parameters
How to set a default value for a parameter
How to validate parameters and raise errors
A stored procedure that inserts a row
How to work with user variables
How to work with dynamic SQL
How to drop a stored procedure
How to code stored functions
How to create and call a function
How to use function characteristics
A function that calculates balance due
How to drop a function
How to use Workbench with procedures and functions
How to view and edit stored routines
How to create stored routines
How to drop stored routines

Module 10: How to Create Triggers and Events
How to work with triggers
How to create a BEFORE trigger
How to use a trigger to enforce data consistency
How to create an AFTER trigger
How to view or drop triggers
How to work with events
How to turn the event scheduler on or off
How to create an event
How to view, alter, or drop events

Prerequisites

Students should have a base understanding of relational databases, normalized data, and how to utilize SQL to query data and filter result sets.