Why take this course?
After completing this course, you will be able to
- execute SQL statements (using SPUFI).
- write standard Select statements.
- wse SQL functions in your queries.
- code simple and correlated subselects.
- code intersection and difference operations using the EXISTS predicate.
- efficiently code UNION statements.
- write efficient implicit and explicit Inner Joins between two or more tables.
- code left, right and full outer joins.
- code meaningful Cartesian joins and avoid invalid ones.
- maintain data using Insert, Update and Delete statements.
This course gives you a comprehensive introduction to the DML component of the SQL language. Working in an interactive environment, you will gain the experience you need to INSERT, UPDATE, DELETE and SELECT using standard functions, aggregate functions, sub-selects, unions and joins.
Who should attend?
End users, analysts and developers who need to interface with relational databases in an interactive environment, and who will be coding simple to intermediate SQL queries.
Any prerequisites?
You should have a basic understanding of databases.
Dates and Fees
2 Days
- $ 737 (CAD) Up to 3 - $ 1,477 Up to 6 - $ 2,214 Plus - $ 367 Sorry, no classes are currently scheduled, please contact us to request a class
What Topics are covered?
Basic SQL Queries
- The SELECT Statement
- The SELECT Clause
- Selecting Columns
- Qualifying Column Names
- Renaming Columns
- Selecting All Columns
- Selecting Literals
- Arithmetic Expression
- Character Expression
- Date or Time Expression
- CASE Expression
- The DISTINCT Keyword
- The FROM Clause
- The WHERE Clause
- Basic Predicate
- NOT Keyword
- Linking Predicates
- The IN Predicate
- The BETWEEN Predicate
- The IS NULL Predicate
- The IS NOT NULL Predicate
- The LIKE Predicate
- Special Registers
- The ORDER BY Clause
SQL Functions
- SQL Functions
- Column Functions
- The COUNT Function
- The MIN and MAX Functions
- The SUM Function
- The AVG Function
- The GROUP BY Clause
- The HAVING Clause
- Scalar Functions
- Character Functions
- SUBSTR
- LEFT, RIGHT
- LTRIM, RTRIM, STRIP
- REPLACE
- LOWER/LCASE, UPPER/UCASE
- DIGITS
- CHAR with Decimal Argument
- Supported Date and Time Formats
- CHAR with Date Argument
- Number Functions
- INTEGER
- DECIMAL
- ROUND, TRUNC
- MOD, POWER, SQRT
- RAND
- Date Functions
- Time Specific Functions
- DAYS
- DATE and TIME
- Miscellaneous Functions
- LENGTH
- COALESCE/VALUE
- NULLIF
Subselects
- SELECT within SELECT
- Subselect Returning a Single Value
- Subselect Returning Multiple Values
- Correlated Subselect
- EXISTS/NOT EXISTS
- Intersection
- Difference
Union and Joins
- UNION
- UNION Considerations
- UNION Example
- CARTESIAN JOIN
- INNER JOIN
- Example
- Using the Inner Join Operator
- With Local Predicates
- OUTER JOIN
- Using the Left Outer Join Operator
- Using the Right Outer Join Operator
- Using the Full Outer Join Operator
- With Local Predicates
Data Maintenance
- INSERT
- Single Row Insert
- Mass Insert
- UPDATE
- Updating All Rows
- Updating Selected Rows
- DELETE
- Deleting All Rows
- Deleting Selected Rows
Competency Assessment and Certificate
At the request of the person booking, a technical competency assessment will be conducted with the student.This assessment will be a combination of written and multiple choice questions as well as specific coding tests.
Upon a minimum pass rate of 85%, students will receive a Certificate of Competency instead of the standard certificate of course completion.
Please Contact us for more details or specific needs.