Join Dan Sullivan for an in-depth discussion in this video ROLLUP and CUBE to create subtotals, part of Advanced SQL for Data Scientists.
- [Instructor] Let's continue our look…at ways to group and aggregate data…with two other operators, ROLLUPs and CUBEs.…Now first, let's modify the staff division region view…we created to include country code.…To do that, I'm going to use the command…CREATE OR REPLACE VIEW,…and as the name implies, if this view doesn't exist,…it will simply create it.…If there is a version of this view that already exists,…it'll replace that version…with the version I'm about to specify.…And we'll call this staff_div_reg_country,…and we'll define this as the SELECT of all the columns…in the staff table, plus company_division,…company_regions, and country.…
And we'll select these columns FROM staff table,…which we'll alias as s.…And the staff table will LEFT JOIN…to the company_divisions table, which we'll alias as cd,…and that left join will be performed on department…in both state and company_division.…And then we'll take the results of that left join operation…and apply another left join, this time to company_regions,…and that will be on the staff table region_id,…
The course begins with a brief overview of SQL. Then the five major topics a data scientist should understand when working with relational databases: basic statistics in SQL, data preparation in SQL, advanced filtering and data aggregation, window functions, and preparing data for use with analytics tools.
- Data manipulation
- ANSI standards
- SQL and variations
- Statistical functions in SQL
- String, numeric, and regular expression functions in SQL
- Advanced filtering techniques
- Advanced aggregation techniques
- Windowing functions for working with ordered data sets
Skill Level Advanced
SQL: Data Reporting and Analysiswith Emma Saunders2h 16m Intermediate
1. SQL as a Tool for Data Science
SQL data definition features5m 32s
2. Basic Statistics with SQL
3. Data Munging with SQL
4. Filtering, Joins, and Aggregation
5. Window Functions and Ordered Data
6. Preparing Data for Analytics Tools
- Mark as unwatched
- Mark all as unwatched
Are you sure you want to mark all the videos in this course as unwatched?
This will not affect your course history, your reports, or your certificates of completion for this course.Cancel
Take notes with your new membership!
Type in the entry box, then click Enter to save your note.
1:30Press on any video thumbnail to jump immediately to the timecode shown.
Notes are saved with you account but can also be exported as plain text, MS Word, PDF, Google Doc, or Evernote.