Master joins, improve result reliability, and integrate AI while protecting confidential information
This practical training helps you transform a business need into a useful SQL query. It is intended for people who already have the basics of the SQL language and want to gain autonomy.
You'll review the fundamentals (filters, functions, grouping), and then delve deeper into joins: understanding relationships between tables, choosing the right type, and avoiding duplicates or incomplete results. You'll also see how to validate a query that runs without meeting the real need.
Finally, you'll learn how to use AI to prepare, explain, and refine your SQL queries, without exposing your organization's confidential data.
Is it for you ?
This training is intended for anyone who queries, extracts or analyzes data via SQL: analysts, accountants, administrative staff, managers or advanced ERP/CRM users (without being SQL developers).
Prerequisites
• Have completed MS524 — MS SQL for Non-Computer Scientists: Creating Queries
• Have equivalent experience and have worked with SQL queries
• Be familiar with SQL basics such as SELECT, FROM, WHERE, and ORDER BY.
No experience in advanced programming is required.
What You'll Walk Away With
- ✓ Translate a business request into a solid SQL query, even on unfamiliar tables
- ✓ Build joins in a clear and reusable way
- ✓ Identify duplicates, missing data and inconsistent results before publishing or sharing results
- ✓ Correct and adapt existing queries with confidence
- ✓ Use AI as a support, without disclosing confidential information
Training content
1 Strengthen your SQL query fundamentals
By the end of this step, you'll be able to build clear SQL queries from a simple business need. This hands-on review will consolidate the essentials of SQL Server before tackling more complex requests involving multiple tables.
- Select, filter, and sort data with SELECT, FROM, WHERE, and ORDER BY
- Comparison operators and combined conditions with AND, OR and NOT
- Searches with IN, BETWEEN, LIKE, IS NULL and IS NOT NULL
- Table and column aliases
- Common SQL functions, calculations, and CASE WHEN expressions
- Aggregation, GROUP BY, and HAVING functions.
2 Understanding the structure of a relational database
In this section, you’ll explore a SQL Server database to find the tables, columns, and relationships needed to make a request. You'll see how the data is organized and related without having to know the entire database in advance.
- Organize data across multiple tables
- Primary keys and foreign keys
- One-to-One, One-to-Many, and Many-to-Many Relationships
- Finding tables and columns
3 Build SQL joins with a structured method
At this stage, you translate a business need into a query using multiple tables. An 8-step methodology will allow you to determine the starting point, track relationships, and gradually build understandable and reusable SQL joins.
- Identifying the left and right tables
- Gradually constructing a query using multiple tables
- Using INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN
4 Correct and validate query results
You will be able to recognize a query that is executing correctly, but that does not meet the real business need. Based on realistic situations, you will develop methods to explain the results, spot anomalies and confirm the reliability of the results.
- Validating results against the original request
- Identifying Missing Data with IS NULL
- Checking row counts and levels of detail
- Using DISTINCT, grouping functions, and validation queries.
5 Using artificial intelligence to support SQL development
You can now use artificial intelligence to prepare, understand, correct, and document SQL queries. You'll see how to provide the necessary context to the tool while respecting privacy rules and maintaining responsibility for validating code and results.
- Formulation of a business request for an AI
- Prepare a minimal context containing the allowed tables and columns
- Using AI to explore a process without inventing the structure of the database
- Anonymization of examples and protection of confidential information
- Analysis and understanding of the generated code
- Human validation of AI-generated queries and results.
6 Adopt basic SQL optimization best practices
Finally, you'll be able to recognize some practices that can make a query unnecessarily cumbersome or difficult to maintain. You'll work on applying simple improvements before forwarding a query or requesting further performance analysis.
- Limitations and risks of using SELECT* instead of selecting only the required columns
- Application of precise and adapted filters
- Reduction of unnecessary rows in results
- Effects of joins and duplicates on data volume
- SQL code readability, use of aliases and query organization.
Keep in Mind
A practical, visual approach, combining explanations with real-world scenarios. No advanced SQL programming: the emphasis is on method and autonomy. AI is a tool to support decision-making, not a substitute for human validation.
📌 Practical information
Our training sessions are offered in Montreal or Quebec City, in person or in a virtual classroom. Dates and locations are specified when you select your session below. If you have any questions, check out our FAQ.
