Магистратура
2020/2021
SQL
Статус:
Курс обязательный (Магистр по наукам о данных)
Направление:
01.04.02. Прикладная математика и информатика
Где читается:
Факультет компьютерных наук
Когда читается:
1-й курс, 2 семестр
Формат изучения:
без онлайн-курса
Преподаватели:
Гоменюк Кирилл Сергеевич
Прогр. обучения:
Магистр по наукам о данных
Язык:
английский
Кредиты:
5
Контактные часы:
90
Course Syllabus
Abstract
Course objectives: · To introduce future data analysts to a relations data source · To teach students how to use the language tools of modern DBMS to extract and prepare data Practical skills: · Building SQL queries in a PostgreSQL database · Preparing analytical reports on relational databases · Data preparation for further analysis · Retrieving data from DB at the application level (e.g. with Python) Pre-requisites: · Beginner programming skills · Basic knowledge of discrete mathematics (working with sets) Other information: · Students will learn to write SQL queries for several different databases · The course includes three parts: theoretical foundation for data manipulation, the key features of SQL language, and SQL application for solving practical tasks.
Expected Learning Outcomes
- Learn basic DDL keywords. Create, drop, alter, truncate
- Learn how to create a database in RDBMS
- Understand data integrity support in DBMS
- Understand fundamentals of relational data model
- Learn to transform questions addressed to subject area into query expressions suitable for relational data model
- Know basic operations available in relational data model
- Understand methods of combining records from tables
- Know general data types in SQL
- Know syntax of simple SELECT queries
- Understand how to calculate totals
- Know how to rewrite the same queries in different styles
- Understand how to work with non-numeric aggregation
- Learn to integrate subqueries into other queries
- Know syntax of complex SELECT queries
- Understand how to use window functions and CTE
- Know the difference between regular and materialized views
- Know how to calculate rank and running totals
- Know how to change data in a database with SQL
- Understand basic data security settings
- Learn how to access relational database from an application
Course Contents
- Introduction to RDBMSIn our course, we will use relational databases as data sources for writing queries. We will start with creating databases in Postgres, namely, you will add new tables with constraints of different types, configure column settings like data type or default values. You will learn how to set the data structure of tables, fill them with data, customize the DBMS behavior related to data integrity maintenance. Practice tasks focus on developing skills in working with client software and writing DDL code to build a database.
- Theoretical base for writing queriesWe continue working with relational databases. The main theme of this week is the relational data model and possible operations defined in it. Videos and exercises focus on the theoretical foundations of processing data stored in tables
- Writing queries to multiple tablesThis week contains a detailed overview of how basic operations of relational model can be implemented in PostgreSQL databases.
- Writing queries with aggregate functions and subqueriesThis week introduces another important operation available in RDBMS - aggregation. Aggregate functions transform grouped rows into scalar values like summation, row numbers, averages, maximal and minimal figures. The second part of this week is devoted to subqueries which can be included in different parts of a query
- Writing analytical queries and creating viewsThis week is devoted to advanced aggregate functions and views. A view is a database object which executes a query when it is accessed. Views can be used to edit data of underlying tables. Window functions extend functionality of regular aggregate calculations - read previous and next rows, compute running total values and find ranks. These two types of function can be combined in a single query. SQL can be used to read and change data in a database. The language provides three instructions to modify the contents of tables - insert, update, and delete. In some cases, before these manipulations, it is needed to read and prepare a recordset which then will be used as a data source. You will learn how to write queries that change data, add subqueries inside such code, and also set permissions for the database users.
- Accessing databases from python programThe last part of our course includes writing programs in python to read and change data in a relational database.
