Master
2022/2023
SQL
Type:
Compulsory course
Area of studies:
Applied Mathematics and Informatics
Delivered by:
Big Data and Information Retrieval School
Where:
Faculty of Computer Science
When:
1 year, 3 module
Mode of studies:
distance learning
Online hours:
82
Open to:
students of one campus
Instructors:
Kirill Gomenyuk
Master’s programme:
Магистр по наукам о данных (заочная)
Language:
English
ECTS credits:
5
Contact hours:
8
Course Syllabus
Abstract
Nowadays data analysis plays a significant role in business effectiveness. Most of the data sources are relational databases, which may contain lots of records. Successful data scientists should know how to retrieve and manipulate such information.
This course consistently introduces students to database management systems, theoretical base of data manipulations, writing simple and complex queries. The course covers essential aspects of data retrieving and processing.
Course materials consist mostly of practical tasks and screencasts, allowing students to implement new knowledge in writing queries to one of the most advanced free DBMS - PostgreSQL.
Learning Objectives
- Able to build SQL queries in a PostgreSQL database
- Able to build SQL queries in a PostgreSQL database
- Able to prepare analytical reports on relational databases
- Able to preprocess data for further analysis
- Able to retrieve data from DB at application level (e.g. python)
Expected Learning Outcomes
- 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.
- Learn basic DDL keywords.
- Learn how to create a database in RDBMS.
- Understand data integrity support in DBMS.
- 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
- 1. Introduction to RDBMS
- 2. Theoretical base for writing queries
- 3. Writing queries to multiple tables
- 4. Writing queries with aggregate functions and subqueries
- 5. Writing analytical queries and creating views
- 6. Accessing databases from python program
Interim Assessment
- 2022/2023 3rd module0.3 * Graded Quizzes + 0.3 * Peer Review + 0.4 * Final Project
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
- Bassan, A. S., & Sarkar, D. (2014). Mastering SQL Server 2014 Data Mining. Birmingham, England: Packt Publishing. Retrieved from http://search.ebscohost.com/login.aspx?direct=true&site=eds-live&db=edsebk&AN=933788