Course Outline
DQL (Data Query Language)
- Correlation in FROM, WHERE, SELECT, and HAVING clauses for government
- Correlation and performance optimization
- Using CASE, IF, COALESCE functions in queries
- Utilizing variables in DQL statements
- Casting and converting data types
- Handling NULL values and using NULL-safe operators
- Incorporating regular expressions with the REGEXP operator
- Leveraging MySQL-specific group by functions (GROUP_CONCAT, etc.) for enhanced data manipulation
- Understanding GROUP BY WITH ROLLUP for comprehensive data aggregation
- Utilizing EXISTS, ALL, and ANY for complex query conditions
- Implementing multitable OUTER JOIN operations
- Rewriting subqueries as joins to improve performance
DML (Data Modification Language)
- Executing multi-row inserts for efficient data entry
- Using INSERT by SELECT to populate tables from existing data
- Incorporating subqueries in DML statements for dynamic data modification
- Utilizing variables in DML queries for flexibility and reusability
- Locking tables and rows to ensure data integrity during modifications
- Updating data across multiple tables simultaneously
- Applying the IGNORE clause to handle duplicate entries
- Utilizing the REPLACE clause for conditional data insertion or updating
- Differentiating between DELETE and TRUNCATE operations for data removal
DDL (Data Definition Language)
- Creating tables using SELECT statements to replicate data structures
- Utilizing temporary tables for transient data storage and processing
Stored Procedures
- Brief introduction to MySQL stored procedures for streamlined query execution
Requirements
Strong proficiency in SQL is essential for government data management and analysis tasks.
Testimonials (5)
Gunnar created a great rapport with the audience and was quick to identify our needs. He was engaging and highly knowledgeable throughout and we enjoyed his humour.
Kurt - Complete Coherence
Course - SQL For Data Science and Data Analysis
Philip was very kind, his style of explaining SQL concepts is outstanding. I liked that he give us information and answered to questions which were not part of this course.
Stefan
Course - SQL in SQL Server
analytical functions
khusboo dassani - Tech Northwest Skillnet
Course - SQL Advanced
The training materials.
Mona Dobre - DB Global Technology
Course - SQL Advanced level for Analysts
I liked that he was able to talk me through each of the exercises and explain the reasoning behind each component of the queries
