The basics of the SQL language
In the era of Big Data, knowing how to query a database has become an essential skill for both developers and data analysts. SQL (Structured Query Language) is the universal standard used to communicate with almost all modern database management systems.
Do you feel limited by simplified interfaces or manual Excel extractions?
This hands-on training will immerse you in the heart of the code. You will learn to structure your thinking logically to extract exactly the information you need, regardless of the complexity of the data.
Is it for you ?
This training is aimed at business analysts, junior developers, database managers, aspiring data scientists, or any professional wishing to become autonomous in data extraction.
Prerequisites
Knowledge of conceptual data modeling (Course BD101) would be an asset.
What You'll Walk Away With
- ✓ Analytical autonomy: No longer depend on the IT department to get your reports; create your own custom extractions.
- ✓ Data precision: Eliminate manual handling errors by letting the database server process complex calculations.
- ✓ Universal skill: The SQL learned here is transferable to MySQL, PostgreSQL, SQL Server, Oracle, and many other systems.
- ✓ Time optimization: Automate your data collection processes so you can focus on their interpretation.
Training content
It is important to master SQL (Structured Query Language), as it is one of the most popular languages for querying relational databases. With this course, participants will be able to communicate with databases to manipulate the data they contain.
1 Introduction to Databases
In this module the participant will learn about the essential components of modern computer systems and the characteristics of persistent data.
- What is data and what are the types of data
- What is a database and what is a database management system (DBMS)
- Database schema and data definition language
- Data manipulation and query language: SELECT, INSERT, UPDATE, DELETE
- Clientserver architecture
- Why is semantic integrity a priority?
- Definition and ownership of transaction management (COMMIT)
2 The SQL DDL language
In this module the participant will see the characteristics of SQL programming
- 4 subdivisions of the language: DDL/DML/DCL/TCL
- Create a table (CREATE TABLE)
- General syntax for creating a table
- SQL types (exact or approximate numeric)
- Strings of characters
- Date and time, etc.
- Deleting a table (DROP TABLE) with RESTRICT and CASCADE
- Modifying the schema of a table (ALTER TABLE)
- How to manage integrity constraints (static or dynamic)
- Virtual tables: implementation, update according to the ANSI standard and by query modification
3 The SQL DML language
In this module the participant will see several commands to manipulate data.
- Insertion in a table
- Insertion of a part of the columns
- Inserting from a Select
- Definition of a table from another table
- Modification and deletion of rows
4 The SELECT
In this module the participant will manipulate SQL SELECT queries
- Relational algebra
- SQL query and Select SQL syntax
- Selection of rows in a table
- Projection of a table, choice of columns, ALL, DISTINCT clauses
- General expressions on columns
- Selection on a table (WHERE)
- Syntax of SQL conditions
- SQL BETWEEN for data intervals
- SQL IN to validate the value of a column (Syntax, conditions...)
- SQL LIKE for searches on specific formats
- The TOP clause to limit the number of rows returned
- The operators
- Mathematical functions
- String functions
- Sorting results (ORDER BY) with nested SELECT, multicolumn rows...
5 JOINS or joins
The participant will see relational operations on several tables with join attributes belonging to compatible domains.
- Internal (INNER) and external (OUTER) joins
- Join table: what should be displayed vs. what should be searched
6 Aggregation functions
The participant will see how to use group functions to obtain statistical summaries.
- Use cases
- GROUP BY, HAVING clauses
7 The ALL and ANY quantifiers
The participant will see the operators that allow unequal comparisons
- Truth of the predicate
- Truth of the comparison
8 Assembling operations
In this module, the participant will see the set operations in SQL using the operators :
- UNION
- INTERSECT
- EXCEPT
9 CASE
The participant will see how to use the CASE command to obtain a result with several possibilities.
- Use in statements or clauses
- Use to compare a column < result set
- Use to construct a series of Boolean conditions to determine a result
- Use to update a column
10 Security in SQL
Here the participant will see the security aspect of SQL (GRANT) and role management.
- User identification
- Authentication
- Creation of users
- Security levels
- Granting and deleting privileges
📌 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.
