SQL и автоматизация текущей отчетности
- To teach students how to use SQL for doing analytical tasks
- To improve students' skills in report automation
- 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
- Introduction to databases and SQLOverview 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 basicsStructure of table expressions. Applying filters. Joining tables, using subqueries and common table expressions. Grouping and aggregate functions. Cases. Window functions.
- Analysing data with SQLRFM analysis, cohort analysis, ABC analysis. Visualizing analysis results.
- Working with geospatial dataPostgres extensions. Data types and functions for working with geospatial data. Case study.
- Report automationReview of pandas and matplotlib. ETL scripts. Logging, error handling. Html reports with Jinja2.
- Query optimizationQuery planner. EXPLAIN and ANALYZE statements. B-tree and hash indices.
- Interim assessment (4 module)0.3 * course project + 0.3 * final exam + 0.4 * home assignments
- 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.
- Linoff, G. (2016). Data Analysis Using SQL and Excel: Vol. Second edition. Wiley.