SQL Fundamentals

Description

A thorough knowledge of SQL is fundamental nowadays both for programmers of all kinds, (not only database ones, as almost every program now uses a database) and database analysts alike. Even though given dialects of the language vary, transact-SQL which has been implemented in SQL Server 2012 is in 95% compliant with ANSI SQL99 standard and its further extensions, therefore its knowledge allows to work with any relational database server. The course is aimed at giving the participants the knowledge and skills crucial for reading and modifying data stored in the bases, as well as for creating and modifying database objects. Those of you who already know SQL, will benefit from broadening and systemizing their knowledge. Programmers who are daily users of object or procedural languages will learn key differences between them and SQL language, whereas administrators and advanced users will learn how to use the vast capabilities of SQL in the scope of  data reading and analysis.

Options

  • Course level: 200
  • Start date: as required
  • Duration time: 3 days

Price: 3500 zł

  • Open training
  • On-site (costumer's premises)
  • Customer’s computer

Class

The course is addressed to programmers, database administrators and all those who aim to write queries which don’t barely return demanded results, but do so efficiently. The participants will acquire the practical skills crucial for reading, analyzing and modifying data, as well as designing and creating databases and their objects.  All participants will receive the second edition of “Practical SQL Course”, which this training has been based upon.

Plan

The basic training is comprised of 14 modules. Just like all the other courses on our offer, it has been designed by our experts, therefore we can freely customize it to suit the individual needs of the participants.  We would like to encourage you not only to choose modules which you find relevant for your needs, but also to feel free to contact us with your suggestions and questions you would like to hear answered during the course.
Our trainings are unique also because we limit the time set aside for laboratory type classes customarily conducted after the completion of each module, to the benefit of exercises done with the assistance of the trainer and demonstrations. We believe in this manner we can equip you with substantially more practical information and tips, and focus on dealing with the issues directly within your scope of interest. However, each day of the training is finished with a laboratory class of approximately 1hr, so that you can do self-practice and do exercises of your choice.

Modules

Duration: Level:
Module 1

Relational Database Model
  • Tables as Sets
  • Three Database Models: Relational, Object Oriented and Flat
  • Principles of Relational Model
  • Codd’s Rules
  • Normalization and Functional Dependencies

60 minutes 200
Module 2

Structured Query Language Standards
  • Characteristics of SQL (Set-Based Processing, Declarativity and Interpretability)
  • Lexical Elements (Identifiers, Literals, Statements, Expressions and Comments)
  • SQL Dialects
  • SQL ANSI/ISO Standards (History, Data Manipulation, Control, Transaction Management, Connection Management, Session Management and Diagnostics Management Statements)
  • Compatibility Levels

60 minutes 200
Module 3

Single Table Queries
  • Queries and Select Statement
  • From Clause
  • Selecting Columns
  • De-Duplicating Rows
  • Expressions (Math, String and Assigning Operators)
  • System Functions (Math, String, Date/Time and Conversion Functions, Special Case Function)
  • Formatting Result Sets
  • Sorting Rows

60 minutes 200
Module 4

Data Types
  • Numeric Data Types
  • Date/Time Data Types
  • Boolean Data Type
  • String Data Types (Unicode and Non-Unicode Strings, Collations)
  • Large Binary Objects
  • XML Data Type
  • Table Data Type
  • Additional Data Types

60 minutes 200
Module 5

Selecting Rows
  • Three Value Logic (NULL and Logic Operators)
  • WHERE Clause
  • Comparison Operators
  • Boolean Operators
  • TOP Clause
  • Ad-Hoc Query Paging
  • Writing Effective Queries (Query Logical and Physical Flow, SARG Arguments)

90 minutes 200
Module 6

Joining Tables And Queries Results
  • Natural and Non-Natural Joins (Foreign Keys and Aliases)
  • Equi and Nonequi Joins
  • Outer Joins
  • Cross Joins
  • Joining Multiple Tables
  • Self Joins (Removing Duplicates, Foreign Keys Inside A Table)
  • Unions (SUM, INTERSECT and EXCEPT Operators)
  • Joining Table Function Results (APPLY Operator)

90 minutes 200
Module 7

Grouping Rows
  • SQL Group Functions
  • Expression and Group Functions Nesting
  • GROUP BY Clause
  • CUBE and ROLLUP Operators
  • GROUPING SETS Clause
  • Efficient Row Grouping
  • OVER Clause (Ranging, Grouping and Analytic Functions, Grouping, Ordering and Selecting Rows)
  • PIVOT And UNPIVOT Operators
  • HAVING Clause

120 minutes 200
Module 8

Subqueries
  • Subqueries as Variables
  • Non Correlated Subqueries
  • Correlated Subqueries
  • Subqueries as Data Sources
  • Common Table Expressions (CTE)
  • EXIST, ANY/SOME and ALL Operators

60 minutes 300
Module 9

Data Modification
  • Inserting Rows (INSERT Statement, Primary Keys, Defaults, Inserting NULL, Row Constructors, Inserting Query Results)
  • Deleting Rows (DELETE Statement, TRUNCATE TABLE Statement)
  • Modifying Cells (UPDATE Statement, Updating Multiple Columns, Updating Cells Based on Data from Different Tables)
  • MERGE Statement (Determinism, Efficient Data Merging)

120 minutes 200
Module 10

Transactions and Concurrency
  • ACID Properties
  • Transactional Data Access (Auto-Commit Mode, Beginning, Committing and Rollbacking Transactions, Transactions Nesting, Savepoints)
  • Blocks
  • Deadlocks
  • Transaction Isolation Levels
  • Optimistic Concurrency Model

60 minutes 300
Module 11

Databases And Tables
  • Creating and Dropping Databases
  • Creating and Dropping Tables
  • Schemas
  • Altering Table Structure
  • Constraints (NOT NULL, Primary Key, Foreign Key, Unique, Default, Check)
  • How Do Constraints Affect Performance?

60 minutes 200
Module 12

Views And Indexes
  • Creating and Dropping Views
  • Altering Views
  • Using Standard Views
  • Indexed Views
  • Advantages of Views
  • Creating, Altering and Dropping Indexes
  • Index Rebuilding

60 minutes 200
Module 13

Programmability Objects
  • Stored Procedures
  • DML and DDL Triggers
  • Scalar and Tabular Functions
  • Querying Metadata
  • Structural Exception Handling
  • Common Mistakes
  • Changing Execution Context
  • Signing Code Modules

120 minutes 200
Module 14

Controlling Access
  • User Accounts
  • Roles
  • Permissions
  • Permission Inheritance

60 minutes 200

Form

Ask Question

Register