Course NT-SQLforA-F MS SQL for analytics: database design, query construction, analytics problem solving
The course is designed for 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 or generate reports, as well as students who plan to take the exam.
After completing this course, students will be able to:
- describe the basic concepts of Microsoft SQL Server 2016 architecture;
- 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 connected tables;
- create views;
- write analytical queries of different levels of complexity.
Audience Profile
Students will gain knowledge and skills for database design to solve production problems, write basic queries in Transact-SQL for Microsoft SQL Server, build analytical queries. The course provides the necessary minimum of knowledge to understand the operation of Microsoft SQL Server and operate business data. This course is basic for database developers and business analysts.
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 the database
- SQL History
- DB workflow
- Hierarchical database structure (need to know business processes)
- DB normalization (1-3 NF)
- Spreading a table by elements in the database
- Task: build a database structure based on output fields
- Principles of working with MS SQL and MS SQL Server Management Studio
- MS SQL versions
- MS SQL 2016 composition
- Installing Microsoft SQL Server
- Installing MS SQL Server Management Studio
- MS SQL Server Management Studio (Database Creation, Table Creation, Data Types — General View)
- Creating tables
- SQL Data Types (Text, Numeric, Date Time)
- Attributes and restrictions of columns and tables on examples (PRIMARY KEY, IDENTITY, UNIQUE, NULL and NOT NULL, DEFAULT, CHECK, CONSTRAINT (features and purposes))
- Foreign Key
- Feature of deleting related table records
- ON DELETE and ON UPDATE settings for linked tables
- ALTER (what can and cannot be changed)
- Creating a backup
- Working with INSERT, UPDATE, DELETE, and SELECT statements
- INSERT – inserting records into a table
- UPDATE – changing field values in posts
- DELETE – deleting records from a table
- SELECT – simple pull requests
- SELECT with field calculation
- DISTINCT – getting unique values by fields
- Using Aliases for column names
- Features of multi-word aliasing
- Sorting (ASC/DESC, aliasing and serialization options)
- TOP and OFFSET…FETCH NEXT – partial output of records from a table
- SELECT execution order
- Filtering records by field values (WHERE clause calculations, Logical operators, IS NULL property, Filtering operators: IN, BETWEEN, LIKE)
- Working with aggregate functions and grouping
- Aggregate Functions (AVG, SUM, MIN, MAX, COUNT, Null Feature, All and Distinct)
- GROUP BY and HAVING statements
- GROUP BY examples
- Rules for specifying fields in Select, the impossibility of using aliases
- GROUP BY to multiple fields
- GROUP BY calculated value
- Filtering groups. HAVING
- Built-in functions
- String functions (LEN, CHARINDEX, PATINDEX, LEFT, RIGHT, SUBSTRING, CONCAT)
- Functions for working with numbers (ROUND, CEILING, FLOOR, RAND)
- Date functions (GETDATE(), DAY(), MONTH(), YEAR(), DATENAME, DATEPART, FORMAT, DATEDIFF)
- Using date transformations in grouping
- Type conversion functions (CAST, CONVERT)
- Branch functions (CASE, IIF)
- Working with subqueries
- Non-correlated subqueries
- Calling subqueries
- Subqueries in basic SQL commands
- Update correlated
- Combining tables
- The essence of "rough" association
- Merge. INNER JOIN
- The essence of the union INNER JOIN
- The concept of "necklace" when joining tables sequentially
- Merge. INNER SELF JOIN
- Merge. OUTER JOIN (LEFT JOIN, LEFT JOIN vs INNER JOIN, RIGHT JOIN, FULL OUTER JOIN)
- Principles for solving table join problems (INNER JOIN)
- Principles for solving table join problems (LEFT JOIN).
- When join is not needed
- When join is needed
- When possible with or without left join
- Using table buffers (views, table variables, temporary and derived tables)
- Create, edit, and delete
- Updatable View
- Non-updatable View
- Temporary tables
- Travel tables
- Window functions, CUBE, ROLLUP, and PIVOT in analytics queries
- window function components
- How to use OVER
- Using PARTITION to group data
- Line Limiting in Window Functions
- Groups of window functions: aggregate, rankings, offsets, analytics.
- CUBE and ROLLUP features
- Using pivot tables without PIVOT
- PIVOT steps
- Pivot tables from PIVOT
- PIVOT with grouping
- PIVOT + UNION
- Using rowset operators: UNION, EXCEPT, INTERSECT
- Query with UNION and UNION ALL
- EXCEPT and INTERSECT queries
Sign up for the closest date
Copyright © 2024 Networking Technologies