SQL Database Fundamentals for Data Science – By Microsoft
SQL is an essential skill to have in every data scientist. You should have SQL skill and better
The course introduces students to the basic principles of working with structured data in the relational model, teaches how to design data, describe database objects in terms of a real DBMS, compose queries in the SQL language, use views, procedures, functions and triggers, create indexes, manage competitive access to data and manipulate the transaction mechanism
About the course
The course is based on the study and application of the SQL language for creating, modifying database objects and managing data in an arbitrary relational database.
Performing practical tasks within the course involves the use of My SQL DBMS.
The course covers the design stages of relational databases, query design rules, the main methods of indexing data. The course will explore the use of transactions and data access rights.
The course also provides an overview of current trends in data science with the advent of BigData. At the end of the course, the scope of NoSQL databases will be shown and current approaches to processing big data are indicated.
The form of study is correspondence (remote). Weekly classes will include viewing thematic video lectures, reading an electronic synopsis and performing test tasks with automated verification of results, testing for the material covered. To obtain a certificate, you must complete all the tasks, tests and write the final exam.
To successfully master the course, knowledge of the basics of programming, basic concepts of set theory, the basics of linear algebra and the basics of mathematical statistics are necessary. The course is designed for undergraduate students. The course can be useful not only for students in the areas of “Computer and Information Science” training, but also for other professionals who need to manipulate large amounts of data.
Week 1. Introductory lecture, which tells about the main content of the course, the sections of the course, the place of databases for various information systems.
Week 2. Database design. Model “Entity-relationship”.
Week 3. Relational algebra. Normalization of relational relations.
Week 4. Basic database objects and their description in the SQL language.
Week 5. Queries in SQL.
Week 6. Views, procedures, functions, triggers.
Week 7. Indexing data.
Week 8. Optimization of query execution.
Week 9. Managing Competitive Access.
Week 10. Directions and trends in the development of databases. Modern approaches to processing Big Data.
The course results will be the ability of students to master the terminology and basic database objects, the basics of SQL, the ability to design data and create database objects in terms of the relational model, write queries in SQL, create indexes to speed up query execution, construct views, functions, procedures and triggers , manage access to the database of many users, use the mechanisms of transactions, as well as the formation of the ability to apply database methods in various fields.
At the end of the course, users will know:
1. Principles of the description of the conceptual infological (infological) data model
2. Principles of data design in terms of the relational model
3. The main operators of the SQL language
4. Current trends in the development of data science
5. The main types of NoSQL databases
6. Basic methods of data analysis
At the end of the course, users will be able to:
1. Describe data structures in terms of the “Entity-relationship” conceptual model
2. Create database objects
3. Describe the integrity constraints for the data.
4. Build additional structures to speed up the search for data
5. Write queries in SQL language
6. Create views, functions, procedures, and triggers.
7. Create new users and give them access to the database
8. Use the transaction mechanism for competitive data access.
Upon completion of the course, users will understand:
1. Basic principles of data design
2. How indexes are arranged
3. How the query optimizer works
4. How the transaction manager works
5. What are the challenges in processing Big Data?
SQL is an essential skill to have in every data scientist. You should have sql skill and better understanding. In this complete SQL course you will learn about the following..
SQL Query Types
SELECT Statement Retrieve records from a table
SELECT LIMIT Statement Retrieve records from a table and limit results
SELECT TOP Statement Retrieve records from a table and limit results
INSERT Statement Insert records into a table
UPDATE Statement Update records in a table
DELETE Statement Delete records from a table
TRUNCATE TABLE Statement Delete all records from a table (no rollback)
UNION Operator Combine 2 result sets (removes duplicates)
UNION ALL Operator Combine 2 result sets (includes duplicates)
INTERSECT Operator Intersection of 2 result sets
MINUS Operator Result set of one minus the result set of another
EXCEPT Operator Result set of one minus the result set of another
SQL Comparison Operators
Comparison Operators Operators such as greater than, less than and so on
JOIN Tables Inner and Outer joins
ALIASES Create a temporary name for a column or table
DISTINCT Clause Retrieve unique records
FROM Clause List tables and join information
WHERE Clause Filter results
ORDER BY Clause Sort query results
GROUP BY Clause Group by one or more columns
HAVING Clause Restrict the groups of returned rows
COUNT Function Return the count of an expression
SUM Function Return the sum of an expression
MIN Function Return the min of an expression
MAX Function Return the max of an expression
AVG Function Return the average of an expression
AND Condition 2 or more conditions to be met
OR Condition Any one of the conditions are met
AND & OR Combining AND and OR conditions
LIKE Condition Use wildcards in a WHERE clause
IN Condition Alternative to multiple OR conditions
NOT Condition Negate a condition
IS NULL Condition Test for NULL value
IS NOT NULL Condition Test for NOT NULL value
BETWEEN Condition Retrieve within a range (inclusive)
EXISTS Condition Condition is met if subquery returns at least one row
SQL Tables and Views
CREATE TABLE Create a table
CREATE TABLE AS Create a table from another table’s definition and data
ALTER TABLE Add, modify or delete columns in a table; rename a table
DROP TABLE Delete a table
GLOBAL TEMP Tables Tables that are distinct within SQL session
LOCAL TEMP Tables Tables that are distinct within modules and embedded SQL program
SQL VIEW Virtual tables (views of other tables)
SQL Keys, Constraints and Indexes
Primary Keys Create or drop primary keys
Indexes Create and drop indexes (performance tuning)
SQL Data Types
Data Types Data Types in SQL
Comments How to create comments within your SQL statement
and so on.
You will get complete overview of SQL database and learn skill you required for data science.