Duration
7 hours (usually 1 day including breaks)
Requirements
Good SQL knowledge.
Overview
This course has been created for people already acquainted with SQL. The course introduces you into secrets common to all SQL databases as well as MySQL specific syntax, functions and features.
Course Outline
DQL (Data Query Language)
- Correlation in FROM, WHERE, SELECT and HAVING clauses
- Correlation and performance
- Using CASE, IF, COALESCE functions
- Using variables
- Casting and converting
- Dealing with NULL, NULL-safe operators
- Using regular expression with REGEXP operator
- Useful MySQL specific group by functions (GROUP_CONCAT, etc.)
- GROUP BY WITH ROLLUP
- EXISTS, ALL, ANY
- Multitable OUTER JOIN
- Rewriting subqueries as joins
DML (Data Modification Language)
- Multi-row inserts
- INSERT by SELECT
- Using subqueries in DML statements
- Using variables in DML queries
- Locking tables and rows
- Updating data in many tables
- IGNORE clause
- REPLACE clause
- DELETE versus TRUNCATE
DDL (Data Definition Language)
- Creating tables with select
- Temporary tables
Stored Procedures
- Short introduction to MySQL stored procedures
Duration
21 hours (usually 3 days including breaks)
Requirements
There are no specific requirements needed to attend this course.
Overview
The aim of this course is to provide a clear understanding of the use of SQL for different
databases (Oracle, SQL Server, MS Access…). Understanding of analytic functions and the
way how to join different tables in a database will help delegates to move data analysis
operations to the database side, instead of doing this in MS Excel application. This can also
help in creating any IT system, which uses any relational database.
Course Outline
Selecting data from database
- Syntax rules
- Selecting all columns
- Projection
- Arithmetical operations in SQL
- Columns aliases
- Literals
- Concatenation
Filtering outcome tables
- WHERE clause
- Comparison operators
- Condition LIKE
- Condition BETWEEN…AND
- Condition IS NULL
- Condition IN
- AND, OR, NOT operators
- Several conditions in WHERE clause
- Operators order
- DISTINCT clause
Sorting outcome tables
- ORDER BY clause
- Sort by multiple columns or expressions
SQL Functions
- Differences between single-row and multi-row functions
- Character, numeric, DateTime functions
- Explicit and implicit conversion
- Conversion functions
- Nested functions
- Dual table (Oracle vs other databases)
- Getting current date and time with different functions
Aggregate data using aggregate functions
- Aggregate functions
- Aggregate functions vs NULL value
- GROUP BY clause
- Grouping using different columns
- Filtering aggregated data – HAVING clause
- Multidimensional Data Grouping – ROLLUP and CUBE operators
- Identifying summaries – GROUPING
- GROUPING SETS operator
Retrieving data from multiple tables
- Different types of joints
- NATURAL JOIN
- Table aliases
- Oracle syntax – join conditions in WHERE clause
- SQL99 syntax – INNER JOIN
- SQL99 syntax – LEFT, RIGHT, FULL OUTER JOINS
- Cartesian product – Oracle and SQL99 syntax
subqueries
- When and where subquery can be done
- Single-row and multi-row subqueries
- Single-row subquery operators
- Aggregate functions in subqueries
- Multi-row subquery operators – IN, ALL, ANY
Set operators
- UNION
- UNION ALL
- INTERSECT
- MINUS/EXCEPT
Transactions
- COMMIT, ROLLBACK SAVEPOINT statements
Other schema objects
Hierarchical queries and samples
- Tree construction (CONNECT BY PRIOR and START WITH clauses)
- SYS_CONNECT_BY_PATH function
Conditional expressions
- CASE expression
- DECODE expression
Data management in different time zones
- Time zones
- TIMESTAMP data types
- Differences between DATE and TIMESTAMP
- Conversion operations
Analytic functions
- Use of
- Partitions
- Windows
- Rank functions
- Reporting functions
- LAG/LEAD functions
- FIRST/LAST functions
- Reverse percentile functions
- hypothetical rank functions
- WIDTH_BUCKET functions
- Statistical functions
Duration
14 hours (usually 2 days including breaks)
Requirements
SQL: Fundamentals of Querying or equivalent knowledge.
Overview
Students will learn advanced queries and how to add, update, and delete data, tables, views, and indexes.
Course Outline
Lesson 1: Querying with unions and advanced joins
- Querying multiple tables with unions
- Advanced Joins
- Calculating with COMPUTE
Lesson 2: Querying with subqueries
Lesson 3: Adding data
- Inserting data
- SELECT INTO
Lesson 4: Updating and removing data
- Updating records
- Deleting records
- Deleting the contents of tables
Lesson 5: Manipulating tables and views
- Creating tables
- Modifying tables
- Deleting tables
- Adding and removing views
Lesson 6: Manipulating indexes
- Adding and removing indexes
Lesson 7: Ensuring data integrity with transactions
Lesson 8: Creating databases
- Creating a database with SQL
- Deleting a database
