virtual classroom
Contact
Contact
virtual classroom cart
Show training

Creating Queries to databases on SQL server platform with SQL for analytics

training code: SQL01+SQL02 / ENG DL 3d / EN
training assurance
For more information, please contact the sales department. For more information, please contact the sales department.
2,300.00 PLN 2,829.00 PLN with TAX

The course is addressed to people who want to know the basics of the SQL language and planning to expand knowledge and further development in the field of data analysis using the SQL language.

Students will learn about the world standard in communicating with databases and selected SQL functions dedicated to analysts, methods of creating virtual collections (CTE).

They will learn to retrieve data from the database using the SQL language. They will be able to optimize the queries that are created by the wizard in the MS Query tool in Excel.

They will be able to create server objects that facilitate analytical tasks – views, storage procedures and functions. They will learn the standard (Recordset ADO, VBA) methods of downloading data to MS Excel.

 Good knowledge of the Windows environment. Good knowledge of Excel.

  • Training: English
  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)