English | MP4 | AVC 1280×720 | AAC 44KHz 2ch | 2h 38m | 434 MB

There is an increasing need for data scientists and analysts to understand relational data stores. Organizations have long used SQL databases to store transactional data as well as business intelligence related data. This course was designed for data scientists who need to work with SQL databases. Specifically, it was designed to help these professionals learn how to perform common data science tasks, including exploration and extraction of data within relational databases.

Instructor Dan Sullivan kicks off the course with a brief overview of SQL data manipulation and data definition commands. He then focuses on how to use SQL queries to prepare data for analysis; leverage statistical functions to better understand that data; and work with aggregates, window operations, and more.

## Table of Contents

**Introduction**

1 The need for SQL in data science

2 What you should know

**1. Foundations of SQL for Data Science**

3 Overview of data science operations

4 Data manipulation commands

5 Data definition commands

6 SQL standards

7 Installing PostgreSQL

**2. Basic Statistics with SQL**

8 Loading data

9 Basic aggregate functions

10 Statistical aggregate functions

11 Grouping and filtering data

12 Joining and filtering data

13 Challenge Test an attribute for normal distribution

14 Solution Test an attribute for normal distribution

**3. Data Munging with SQL**

15 Reformat character data

16 Extract strings from character data

17 Filter with regular expressions

18 Reformat numeric data

19 Use SOUNDEX with misspelled text

20 Challenge Prepare a data set for analysis

21 Solution Prepare a data set for analysis

**4. Filtering and Aggregation**

22 Use the HAVING clause to find subgroups

23 Subqueries for column values

24 Subqueries in FROM clauses

25 Subqueries in WHERE clauses

26 Use ROLLUP to create subtotals

27 Use CUBE to total across dimensions

28 Use Top-N queries to find top results

29 Challenge Filter and aggregate a data set

30 Solution Filter and aggregate a data set

**5. Window Functions and Ordered Data**

31 Introduction to window functions

32 NTH VALUE and NTILE

33 RANK, LEAD, and LAG

34 WIDTH BUCKET and CUME DIST

35 Challenge Segment a data set using Window functions

36 Solution Segment a data set using Window functions

**6. Common Table Expressions**

37 Introduction to common table expressions (CTEs)

38 Multiple table common table expressions

39 Hierarchical tables

40 Recursive common table expressions

41 Challenge Rewrite a complex query to use CTEs

42 Solution Rewrite a complex query to use CTEs

**Conclusion**

43 Next steps

Resolve the captcha to access the links!