Duration
29.0 hours
Regular fee
$250
Objectives of the training
The majority of business applications manipulate persistent data. These data are stored and organized in relational databases and are managed using the SQL language. This training is intended for developers who will learn to use SQL to manage tables and manipulate data stored in Microsoft SQL Server databases. Possible certification: IT ENI Certification - Operating a database with the SQL language.Targeted audience
DevelopersPrerequisite
None.Trainers
Upcoming information
Course architecture
Towards relational databases
• Introduction to relational databases
• Data storage
• Ways of organizing data
• Sequential organization
• Direct or random organization
• Indexed sequential organization
• Evolution towards the relational model
• The relational model - rules and concepts
• Normalization
Introduction to SQL
• Generalities about the language
• A query language
• A standard and normalized language
• A universal language
• The statement categories
The Microsoft SQL Server solution
• Introduction to SQL Server
• SQL Server Management Studio
• Creating a new database
• Managing access rights
• The script editor
DDL - Table Management
• Introduction
• Creating tables
• SQL Server data types (with exercise)
• Implementing data integrity - Nullity and default value
• Implementing Data Integrity - Primary and Secondary Keys
• Implementing data integrity - Validation constraint (with exercise)
• Visualization of the database schema
• Modifying tables - Columns
• Modification of tables - Constraints
• Implementing referential integrity (with exercise)
• Deleting tables
• Indexing data
• Lesson plan - Creating tables
DML - Updating data
• Introduction
• Some useful elements of Transact SQL (with exercise)
• Inserting data
• Modifying data
• Deleting data (with exercise)
• Transaction management and locks
• Demonstration - Transaction management (with exercise)
• Case Study - Updating a database
Relational algebra
• Introduction to relational algebra
• The principles implemented
• The restriction
• The projection
• The associated calculations
• The Cartesian product
• The join
• Union, intersection, difference and division
• Solving a problem by example: setting up the view
• Solving a problem by example: scheduling operations
• Statement of the practical - Queries in relational algebra
DML - Data extraction
• Introduction
• The basic syntax
• Projection and elementary calculations
• Demonstration - Projection and elementary calculations
• The restriction
• Demonstration - The restriction
• The aggregate calculation
• Demonstration - The aggregate calculation
• The sorting of results
• Relating sets : The cartesian product
• Relating sets : The join
• Demonstration - Relating sets : the join
• Relating sets: The outer join (with exercise)
• Storing intermediate results (with exercise)
• Lesson Plan - SQL Queries (Part 1)
SQL - Advanced Concepts
• Introduction
• Subqueries
• Implementing subqueries : Delete, Update, Insert (with exercise)
• Implementing subqueries : Select (with exercise)
• Using CTE tables (with exercise)
• Implementation of the set operators : UNION, EXCEPT, INTERSECT
• View objects
• Practical Learning Statement - SQL Queries (Part 2)
SQL Server - Configuring the server for shared use
• The problem
• Configuring the Windows firewall
• Enabling named channels in SQL Server
Summary lab
• Lecture - Video rental
SQL Server 2014 - SQL, Transact SQL - Designing and building a database (with practical exercises and answers)
• The relational model
• Implementation of databases
• SQL commands
• Transact SQL: the procedural language
• Distributed data management
• Advanced types
• Common Language Runtime (CLR)
• Introduction to relational databases
• Data storage
• Ways of organizing data
• Sequential organization
• Direct or random organization
• Indexed sequential organization
• Evolution towards the relational model
• The relational model - rules and concepts
• Normalization
Introduction to SQL
• Generalities about the language
• A query language
• A standard and normalized language
• A universal language
• The statement categories
The Microsoft SQL Server solution
• Introduction to SQL Server
• SQL Server Management Studio
• Creating a new database
• Managing access rights
• The script editor
DDL - Table Management
• Introduction
• Creating tables
• SQL Server data types (with exercise)
• Implementing data integrity - Nullity and default value
• Implementing Data Integrity - Primary and Secondary Keys
• Implementing data integrity - Validation constraint (with exercise)
• Visualization of the database schema
• Modifying tables - Columns
• Modification of tables - Constraints
• Implementing referential integrity (with exercise)
• Deleting tables
• Indexing data
• Lesson plan - Creating tables
DML - Updating data
• Introduction
• Some useful elements of Transact SQL (with exercise)
• Inserting data
• Modifying data
• Deleting data (with exercise)
• Transaction management and locks
• Demonstration - Transaction management (with exercise)
• Case Study - Updating a database
Relational algebra
• Introduction to relational algebra
• The principles implemented
• The restriction
• The projection
• The associated calculations
• The Cartesian product
• The join
• Union, intersection, difference and division
• Solving a problem by example: setting up the view
• Solving a problem by example: scheduling operations
• Statement of the practical - Queries in relational algebra
DML - Data extraction
• Introduction
• The basic syntax
• Projection and elementary calculations
• Demonstration - Projection and elementary calculations
• The restriction
• Demonstration - The restriction
• The aggregate calculation
• Demonstration - The aggregate calculation
• The sorting of results
• Relating sets : The cartesian product
• Relating sets : The join
• Demonstration - Relating sets : the join
• Relating sets: The outer join (with exercise)
• Storing intermediate results (with exercise)
• Lesson Plan - SQL Queries (Part 1)
SQL - Advanced Concepts
• Introduction
• Subqueries
• Implementing subqueries : Delete, Update, Insert (with exercise)
• Implementing subqueries : Select (with exercise)
• Using CTE tables (with exercise)
• Implementation of the set operators : UNION, EXCEPT, INTERSECT
• View objects
• Practical Learning Statement - SQL Queries (Part 2)
SQL Server - Configuring the server for shared use
• The problem
• Configuring the Windows firewall
• Enabling named channels in SQL Server
Summary lab
• Lecture - Video rental
SQL Server 2014 - SQL, Transact SQL - Designing and building a database (with practical exercises and answers)
• The relational model
• Implementation of databases
• SQL commands
• Transact SQL: the procedural language
• Distributed data management
• Advanced types
• Common Language Runtime (CLR)
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
29.0 hours
Regular fee
$250
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