Course NT-SQLforA MS SQL for programmers and analysts: database design, query construction, analytics problem solving, programming basics
The course is designed for database administrators, database developers and client applications, as well as specialists in the field of business intelligence. It will be interesting for users who want to understand the work of the database, be able to create queries to support the work of automation systems and with understanding to form analytical queries.
After completing this course, students will be able to:
- describe the main architecture concepts of Microsoft SQL Server 2016;
- determine how Transact-SQL is similar and different from other query languages;
- understand data types in SQL Server;
- create a database to solve business problems;
- operate with data using Transact-SQL;
- sort and filter data;
- use built-in functions;
- group and aggregate data;
- use subqueries;
- write queries of different levels of complexity to separate and joined tables;
- create views (Views);
- write analytical queries of different levels of complexity;
- program with T-SQL;
- create stored procedures;
- develop and use custom functions;
- write and use triggers.
Audience Profile
Students will gain knowledge and skills for database design for solving production problems, writing basic Transact-SQL queries for Microsoft SQL Server, building analytical queries, basic programming in Transact-SQL. The course provides the necessary minimum of knowledge to understand the operation of Microsoft SQL Server, operate with business data and configure automation. This course is basic for database administrators and developers, as well as business intelligence professionals.
Before attending this course, students must have:
- basic knowledge of relational databases;
- basic knowledge of basic Windows functionality;
- basic file storage skills (Google Drive – to access course materials).
- Introduction to DB
- SQL History
- Scheme of work with DB
- Hierarchical database structure (need to know business processes)
- Normalization of DB (1-3 NF)
- Spreading the table by elements to the database
- Task: construct the structure of the base by the source fields
- Principles of working with MS SQL and MS SQL Server Management Studio
- MS SQL versions
- Warehouse MS SQL 2016
- Installing Microsoft SQL Server
- Installing MS SQL Server Management Studio
- MS SQL Server Management Studio (Database Creation, Table Creation, Data Types – General representation)
- Creating tables
- SQL Data Types (Text, Numeric, Date Time)
- Attributes and limitations of columns and tables on examples (PRIMARY KEY, IDENTITY, UNIQUE, NULL and NOT NULL, DEFAULT, CHECK, CONSTRAINT (features and purposes))
- External key
- Feature of removing entries of linked tables
- Configuring ON DELETE and ON UPDATE for linked tables
- ALTER (what can be changed and what not)
- Create backup
- Working with INSERT, UPDATE, DELETE and SELECT operators
- INSERT – insert entries into a table
- UPDATE – change field values in entries
- DELETE – delete entries from table
- SELECT – simple requests for data output
- SELECT with field calculations
- DISTINCT – obtaining unique values by fields
- Aliases for column names
- Features of prescribing pseudonyms from multiple words
- Sort (ASC/DESC, usability of aliases and numbers in order)
- TOP and OFFSET…
- SELECT execution order
- Filtering records by field values (Calculations in the operator WHERE, Logical operators, property IS NULL, Filtering operators: IN, BETWEEN, LIKE)
- Working with aggregate functions and grouping
- Aggregate functions (AVG, SUM, MIN, MAX, COUNT, Peculiarity of work with NULL values, use All and Distinct)
- Operators GROUP BY and HAVING
- Examples of using GROUP BY
- Selection field prescription rules, inability to use aliases
- GROUP BY for several fields
- GROUP BY calculated value
- Filtering groups. HAVING
- Built-in functions
- Functions for working with strings (LEN, CHARINDEX, PATINDEX, LEFT, RIGHT, SUBSTRING, CONCAT)
- Functions for working with numbers (ROUND, CEILING, FLOOR, RAND)
- Functions of working with date (GETDATE (), DAY (), MONTH (), YEAR (), DATENAME, DATEPART, FORMAT, DATEDIFF)
- Using Date Conversions in Grouping
- Type conversion functions (CAST, CONVERT)
- Branching functions (CASE, IIF)
- Working with subqueries
- Uncorrelating subqueries
- Correlating subqueries
- Subqueries in SQL Core Commands
- Update Correlating
- Merge tables
- The essence of the "rough" union
- Merge. INNER JOIN
- The essence of INNER JOIN
- The concept of "beads" with consecutive combining of tables
- Merge. INNER SELF JOIN
- Merge. OUTER JOIN (LEFT JOIN, LEFT JOIN vs INNER JOIN, RIGHT JOIN, FULL OUTER JOIN)
- Principles of solving problems on combining tables (INNER JOIN)
- Principles of solving problems on combining tables (LEFT JOIN).
- When join is unnecessary
- When join is needed
- When it is possible with or without left join
- Using table buffers (views, table variables, temporary and derivative tables)
- Views. Create, modify, and delete
- Updated View
- Neo-Renewable View
- Table variables
- Temporary tables
- Derivative tables
- Variable and basic operators in T-SQL programming
- Working with variables (DECLARE, SET, SELECT, PRINT)
- Select and variables
- Variables in queries
- Conditional expressions
- Cycles
- BREAK and CONTINUE operators
- Procedures, custom functions and triggers
- Example of the procedure
- Parameters in procedures
- Default settings in procedures
- Variants of transfer values
- Output parameters and return result
- Custom Features
- Basic limitations
- Simple function
- Query Functions
- Principles of creating functions
- The essence of the variable
- Default Option
- Real-time statistics
- Request execution plan
- Query Statistics
- Triggers
- Principles of triggers
- Setting up and using triggers
- INSTEAD OF type triggers
- Treating Multiple Lines with Trigger
- Window functions in analytics requests
- Components of window functions
- Principles of using OVER
- Using PARTITION to group data
- Restriction of rods in window functions
- Groups of window functions: aggregate, ranking, displacement, analytical.
- CUBE and ROLLUP capabilities
- Using string sets operators: UNION, EXCEPT, INTERSECT
- Requests from UNION and UNION ALL
- Queries with EXCEPT and INTERSECT
- PIVOT – usability and alternatives
- Using summary tables without PIVOT
- Stages of using PIVOT
- Summary tables with PIVOT
- PIVOT with grouping
- PIVOT + UNION
- Alternative ways to obtain summary tables
- Additional topics
- Implementing Error Handling
- Transactions
- Import and export data
- Query optimization
- XML Processing
Sign up for the closest date
Class schedule
Date
Time
20.01.25
09:30 - 17:00
21.01.25
09:30 - 17:00
22.01.25
09:30 - 17:00
23.01.25
09:30 - 17:00
24.01.25
09:30 - 17:00
Copyright © 2024 Networking Technologies