Study Guide

Objective

SQL is the standard query language to work and deal with relational databases. Relational databases manage data in tables (or relations), which makes them efficient and flexible to store and access structured information. Entity-Relationship (ER) modeling help us to collect and visualize the requirements of the database to create an optimized database. This course will introduce students to SQL, its capabilities and functionalities, and how to create an ER diagram and how to use it to create a relational database. Besides, the students will get introduced to the MySQL database management system to manage, control, and query the data stored in the relational databases using SQL language. Apart from the intuitions, the student will get familiar with lots of SQL commands that needs to know as an aspiring Data Scientist or Data Analyst. After passing this course, the student will be prepared to enter the fantastic world of data analysis towards amazing job positions in the industry.

Content

1. Introduction:
Introduction to Database - Relational Databases - Database Management Systems- Why DBMS? - What is SQL? - Why SQL for Data Science? - SQL Elements - SQL Basic Statements

2. Requirements for Relational Databases:
Introduction – Entity – Attribute – Relationship - Cardinality of Relationships - Entity Relationship Diagram

3. Relational Database Modeling:
Introduction – Relations - Mapping Entities into Relations - Primary and Foreign Keys - Mapping Relationships into Relations - Data Types

4. MySQL Database:
Introduction to MySQL Database - Installing MySQL: Part 1 - Installing MySQL: Part 2 - Create a Database - Create a Table - Delete a Table/Database

5. Basic SQL Statements:
Introduction - CREATE DATABASE Statement - CREATE TABLE Statement - SELECT Statement - WHERE Clause - INSERT Statement - ALTER Statement- UPDATE Statement - DROP and TRUNCATE Statements

6. Filtering, Sorting and Calculating Data with SQL:
Introduction - Filtering: IN - Filtering: OR - Filtering: NOT - Wildcards in SQL - Sorting with ORDER BY - Aggregate functions - Math Operations: SUM, COUNT - Math Operations: MAX, MIN - Grouping Data - Having

7. Subqueries and Joins in SQL:
Introduction - Aliases - INNER JOIN - LEFT JOIN, RIGHT JOIN - CROSS JOIN - SELF JOIN - Subqueries - UNIOIN

8. Access MYSQL with Python:
Introduction - Python DB-API - Install PyMySQL and Create its Connection - Create Database and Table - SELECT Syntax - Retrieve Tables with Pandas

9. Final Tasks:
Project- Self-Study Essay

Location and time

Course is 100% online (Self-Study) course and it can be done in own pace. Course environment is TechClass -portal.

Materials

Lecture slides, quizzes and exercises online in TechClass -portal.

Teaching methods

This course is 100% virtual, thanks to figures, videos and content prepared for this course.

The student will pass this course after submitting the required quizzes, assignments, and the final project.

Employer connections

N/A

Exam schedules

Online.

International connections

N/A

Completion alternatives

N/A

Student workload

Lectures = 80h
Exercises = 40h
Self-study = 40h
Quizzes = 10h
Project = 40h
Total = 210 hours

Content scheduling

Up to Student her-/himself.

Further information

Course is for Open UAS Students.

Enrolments via hakija työpöytä https://hakija.oma.metropolia.fi/

Evaluation scale

Hyväksytty/Hylätty

Assessment criteria, satisfactory (1)

- The student knows the general framework of databases.
- The student is familiar with the preliminaries of the relational databases.
- The student knows what SQL is.
- The student knows some basic queries.
-The student knows different data types.

Assessment criteria, good (3)

- The student knows the concepts of ER diagrams
- The student knows how to create ER diagram.
- The student knows how to install MYSQL.
- The student is familiar with primary and foreign keys.
- The student knows how to create databases and tables.
- The student is familiar with the statements for filtering and sorting data.
-The student is familiar with joining table queries.
- The student is familiar with accessing database using Python.

Assessment criteria, excellent (5)

- The student understands the intuition behind relational databases.
- The student knows how to create relational databases using ER diagram
- The student understands the intuition behind Joining tables.
- The student understands the intuition behind Python DB-API.

Assessment methods and criteria

Exercise 30%
Quiz 20%
Project 30%
Essay 20%

Prerequisites

Introduction to Python for Data Science

Further information

Course is for Open UAS Students.