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.
Duration
2 days
Schedule
9h to 16h
Regular fee
$1,035
Preferential fee
A preferential rate is offered to public institutions, to members of certain professional organizations as well as to companies that do a certain amount of business with Technologia. To know more, please read the "Registration and rates" section on our FAQ page. Please note that preferential rates are not available for online training courses. Discounts cannot be combined with other offers.
$930
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
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)
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)
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
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
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...
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
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 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
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
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
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
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
If you have any questions about registration, cancellation, schedules, language of instruction, etc. you will find answers in our FAQ.
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 quoteDuration
2 days
Schedule
9h to 16h
Regular fee
$1,035
Preferential fee
A preferential rate is offered to public institutions, to members of certain professional organizations as well as to companies that do a certain amount of business with Technologia. To know more, please read the "Registration and rates" section on our FAQ page. Please note that preferential rates are not available for online training courses. Discounts cannot be combined with other offers.
$930
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 quote