Training agenda
  1. Discussion of the basic issues related to relational databases (on the example of the database on the SQL Server platform)
    • Tables
    • Fields
    • Keys
    • Indices
    • Relations
    • Integrity ties
    • Views
    • Stored procedures.
  2. Creating selection questions (SQL language)
    • SELECT phrase
    • Data filtering (WHERE)
    • Data sorting (ORDER BY)
    • Grouping data (GROUP BY)
    • Aggregating data (SUM, AVG, MIN, MAX, COUNT)
    • Joining tables (WHERE … = …, WHERE … IN …, INNER JOIN, LEFT JOIN, etc.)
    • Combining data from various databases
    • Using text functions (LEFT, RIGHT, SUBSTRING, etc.)
    • Conditional phrases
    • Data type conversion.
  3. Creating functional queries
    • Adding data (INSERT)
    • Modifying data (UPDATE)
    • Deleting data (DELETE)
    • Deleting tables (DROP TABLE).
  4. Downloading data from SQL Server using Microsoft Excel
    • Creating connections to MS SQL Server data
    • MS Query supplement.
  5. The use of scheduling functions
    • Window function OVER ()
    • ROW_NUMBER ()
    • RANK()
    • DENSE_RANK ()
    • NTILE (n)
  6. Unusual data grouping
    • ROLLUP
    • CUBE
    • GROUPING SETS
  7. Dictionaries, totals, differences and ratios of collections
    • UNION
    • UNION ALL
    • EXCEPT
    • INTERSECT
  8. Selected text functions, dates and times, mathematical, checking
  9. Subqueries
  10. CTE (Common Table Expression) Queries
  11. Creating parameterized stored procedures
  12. Sending SQL commands from Excel VBA code (ADO Recordset)