Query a database easily with the essential commands of SQL programming. This SQL Programming for (Almost) Beginners course will allow you to define the objects of a relational database, to formulate queries to query and update a database, to give or remove accesses, etc.
Objectives
To provide the knowledge and skills required to formulate queries and to update a database using the SQL language.
Is it for you ?
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.
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.
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
- Client-server architecture
- Why is semantic integrity a priority?
- Definition and ownership of transaction management (COMMIT)
Détails + / -
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
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
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, multi-column rows...
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
Aggregation functions
The participant will see how to use group functions to obtain statistical summaries.
- Use cases
- GROUP BY, HAVING clauses
The ALL and ANY quantifiers
The participant will see the operators that allow unequal comparisons
- Truth of the predicate
- Truth of the comparison
Assembling operations
In this module, the participant will see the set operations in SQL using the operators :
- UNION
- INTERSECT
- EXCEPT
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
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
💡 Useful information
Our training sessions are offered in Montreal or Quebec City, in person or in virtual format. Dates and locations are provided when you select your session below. If you have any questions regarding registration, schedules, the language of instruction, or cancellation policies, please consult our FAQ .
Trainers
Private or personalized training
Do you have several employees interested in the same training course? Whether in person at your offices or remotely in virtual mode, we offer private training courses tailored to your team's needs. Group rates are available. Contact us for more details or request a quote online.
Request a quote