Objectives of the training
To provide the knowledge and skills required to formulate queries and to update a database using the SQL language.Targeted audience
Developers (other than supervisors), Web specialists and those working in networking positions (other than security).Prerequisite
Knowledge of conceptual data modeling (Course BD101) would be an asset.Trainers
Course architecture
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
- Client-server architecture
- Why is semantic integrity a priority?
- Definition and ownership of transaction management (COMMIT)
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
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
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, multi-column rows...
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
The participant will see how to use group functions to obtain statistical summaries.
- Use cases
- GROUP BY, HAVING clauses
The participant will see the operators that allow unequal comparisons
- Truth of the predicate
- Truth of the comparison
In this module, the participant will see the set operations in SQL using the operators :
- UNION
- INTERSECT
- EXCEPT
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
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
Private or personalized training
If you have more than 8 people to sign up for a particular course, it can be delivered as a private session right at your offices. Contact us for more details.
Request a quotePrivate or personalized training
If you have more than 8 people to sign up for a particular course, it can be delivered as a private session right at your offices. Contact us for more details.
Request a quote