Join Dan Sullivan for an in-depth discussion in this video Subqueries in WHERE clauses, part of Advanced SQL for Data Scientists.
- [Male Voice] We can use subqueries in where clauses.…These are useful when we want to make comparisons…within a single table.…For example, if we want to find…the department of the person with the highest salary,…we can use a subquery in the where clause…that finds the maximum salary of all staff.…Let's start by building the subquery.…We want to select maximum…or max…salary from the staff table.…Now, because we're working with subqueries,…we want to make sure we use an alias,…so I'll assign this the alias s2,…and I'll make sure that I reference s2 above…and it's a subquery so I will wrap this in parentheses.…
So now I have a subquery that returns…the maximum salary that is listed in the staff table.…Now I want to build out the rest of my query,…which is to select the department from the staff table,…and I'll use s1 as the reference there,…where the salary in s1 is equal to this maximum salary.…So I'll move things around a little bit…to make it obvious what I'm doing here.…So what I'm doing is I have a subquery,…
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.