• A
  • A
  • A
  • АБB
  • АБB
  • АБB
  • А
  • А
  • А
  • А
  • А
Обычная версия сайта
2019/2020

SQL и автоматизация текущей отчетности

Статус: Дисциплина общефакультетского пула
Когда читается: 3, 4 модуль
Язык: английский
Кредиты: 3

Course Syllabus

Abstract

The course develops practical and theoretical skills for using SQL in analytics and automating reports. The course covers using SQL for extracting and analysing data and using Python and Excel for report automation. SQL is an important skill for analysts who work with large volumes of data and thus a competetive advantage for students during job search. This course is designed to provide a lot of practice for students in writing queries, so that they could use SQL fluently at work. In this course, students can choose between Excel and Python for report automation. This is done to account for varying levels of students' programming skills and the difference in technological stacks that analysts use, depending on their specialization. Course pre-requisites: Students are expected to be familiar with descriptive statistics and either Excel or Python.
Learning Objectives

Learning Objectives

  • To teach students how to use SQL for doing analytical tasks
  • To improve students' skills in report automation
Expected Learning Outcomes

Expected Learning Outcomes

  • use a database client with a GUI
  • become fluent in SELECT SQL queries
  • use basic data definition and data manipulation SQL queries
  • apply SQL to solve real-life analytical problems
  • improve report automation skills by learning to automate reports in Excel or in Python, depending on their starting programming experience
Course Contents

Course Contents

  • Introduction to databases and SQL
    Overview of contemporary database management systems. Using a database client. Relational model. Basic data types and functions that work with them. Type casting. Basics of Data Definition Language. Basics of Data Manipulation Language. Structure of SELECT queries. Connecting to databases from Excel and Python.
  • Table expressions and select lists basics
    Structure of table expressions. Applying filters. Joining tables, using subqueries and common table expressions. Grouping and aggregate functions. Cases. Window functions.
  • Analysing data with SQL
    RFM analysis, cohort analysis, ABC analysis. Visualizing analysis results.
  • Working with geospatial data
    Postgres extensions. Data types and functions for working with geospatial data. Case study.
  • Report automation
    Review of pandas and matplotlib. ETL scripts. Logging, error handling. Html reports with Jinja2.
  • Query optimization
    Query planner. EXPLAIN and ANALYZE statements. B-tree and hash indices.
Assessment Elements

Assessment Elements

  • non-blocking home assignments
    8 home assignments with equal weights
  • non-blocking course project
  • non-blocking final exam
Interim Assessment

Interim Assessment

  • Interim assessment (4 module)
    0.3 * course project + 0.3 * final exam + 0.4 * home assignments
Bibliography

Bibliography

Recommended Core Bibliography

  • Beaulieu, A. (2009). Learning SQL : Master SQL Fundamentals: Vol. 2nd ed. O’Reilly Media.
  • Beighley, L. (2007). Head First SQL : Your Brain on SQL —— A Learner’s Guide. Reilly - O’Reilly Media.

Recommended Additional Bibliography

  • Linoff, G. (2016). Data Analysis Using SQL and Excel: Vol. Second edition. Wiley.