კურსი 20461: Querying Microsoft SQL Server® 2014
დაწყების თარიღი: 7 ოქტომბერი, 2020

Course: Querying in Microsoft SQL Server

 

Part 1 – Fundamentals

Module 1 – Introduction

  • Database Structure and Navigation;
  • Writing Simple SELECT Statements;
  • Eliminate Duplicates with DISTINCT;
  • Using TOP to cut big data Tables;

 

Module 2 – Sorting and Filtering

  • Sorting Data with ORDER BY (ASC & DESC);
  • Filtering Data with predicates (= > < !) using WHERE;
  • Using IN and BETWEEN to filter data easily;
  • Using logical operators AND & OR for more complex filtering;

 

Module 3 – Grouping and Aggregating

  • Using Aggregate Functions (SUM, AVG, COUNT, MAX, MIN);
  • Aggregating data with GROUP BY;
  • Filtering Aggregated data with HAVING;

 

Module 4 – Joins, Unions and Nulls

  • Using aliases for naming columns and tables;
  • Querying multiple tables with horizontal combination (LEFT JOIN & INNER JOIN);
  • Querying multiple tables with vertical combination (UNION & UNION ALL);
  • Understanding unknown value (NULL) and reasons of its appearance;

 

Module 5 – Data Types and Number Functions

  • Understanding main Data Types (INT, DECIMAL, VARCHAR, DATETIME);
  • Using Math operations to work with numbers (+, -, ( ), /, *);
  • Using scalar functions for numbers (ROUND, ABS, POWER, SQRT);

 

Module 6 – String Functions and Conversions

  • Using functions to manipulate with Strings (LEFT, RIGHT, LEN);
  • Combining strings with + operator;
  • Using LIKE for more flexible filtering of String Data;
  • Understanding conversion between number and string using CAST and its importance;

 

Module 7 – Date Functions and Conversions

  • Using functions to manipulate with Dates (EOMONTH, DAY, MONTH, YEAR, GETDATE, DATEADD, DATEDIFF, “+”);
  • Understanding similarities with string type (LEFT, RIGHT, “+”)
  • Conversion between string and date with CAST and its importance;

 

Module 8 – Cases and Null Functions

  • Writing CASE expressions;
  • Using IIF function for simple cases;
  • Using functions to manipulate with NULLs (ISNULL, NULLIF);
  • Understanding how to work with NULLs: IS NULL, IS NOT NULL; [Fail of predicates]

 

 

Part 2 – Important tools

Module 9 –Subqueries and temporary Tables

  • Using Subqueries to connect multiple queries;
  • Using EXISTS predicate in Filters and Cases;
  • Creating temporary tables with INTO clause;
  • Deleting temporary tables with DROP TABLE clause;

 

Module 10 – Creating and Modifying Data

  • Using CREATE to Create new tables;
  • Adding data into the existed table with INSERT INTO;
  • Using UPDATE and DELETE to modify data;
  • Understanding and using PRIMARY KEY option;

 

Module 11 – Defining Variables

  • Defining and filling variables (DECLARE, SET, SELECT);
  • Understanding Scalar and Table variables and using them into the queries;
  • Using subqueries to fill variables;

 

Module 12 – All Join and Union types

  • Using other join types to work with multiple tables (RIGHT JOIN, FULL JOIN, CROSS JOIN)
  • Using Apply (CROSS, OUTER) to work with multiple tables;
  • Using other union types to work with multiple tables (EXCEPT, INTERSECT);

 

 

Part 3 – Advanced features

Module 13 – Window Functions

  • Using Window Functions (ROW_NUMBER, RANK, DENSE_RANK, NTILE, LAG, LEAD, FIRST_VALUE, LAST_VALUE);
  • Creating Windows with OVER;

 

Module 14 – Common Table Expressions

  • Using Common Table Expressions (WITH);
  • Understanding difference with subqueries and temporary tables;

 

Module 15 – Views and Inline Functions [

  • Understanding and using Views;
  • Understanding and using Inline Table-Valued Functions;
  • [difference between them]

 

Module 16 – Pivoting and advanced grouping

  • Writing Queries with PIVOT and UNPIVOT;
  • Using ROLLUP and CUBE with GROUP BY;

 

 24 საათი
 1350 ლარი
ინსტრუქტორი:
აითი-ნოლიჯის ინსტრუქტორი
ენა: ქართული
კურსზე დარეგისტრირებულთა რაოდენობა: 9