Join Dan Sullivan for an in-depth discussion in this video Subqueries in FROM clauses, part of Advanced SQL for Data Scientists.
- [Instructor] Let's look at another example…of how to use subqueries.…Assume that anyone who earns more than $100,000 per year…is an executive.…And that we'd like to find the average…executive salary by department.…We can do this by creating a subquery…that returns the department and the salary…of executives only.…We then group by department and average the salaries…of those executives.…So let's start by building the subquery.…We'll select and we want to be able to select…a department and a salary.…We want to select this from the staff table.…
Now we want to limit this to executives…so we'll have a where clause that says…where salary is greater than $100,000.…Now I'm going to turn this into a subquery…so I'll wrap it in parentheses…and I want to make sure I give it an alias.…So I'll call this S-one.…Now I want to treat that almost like a table.…I want to select from this, I want to select…the department, include the alias in that,…and I want to select the average salary.…Select the average of the salary.…Now again average sometimes returns…
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
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.