Explore
For Enterprise
Join for Free
Log In

Browse
Search
For Enterprise
Log In
Join for Free

Grouping Result Sets

Loading...

Databases and SQL for Data Science

4.7 (6,645 ratings) | 67K Students Enrolled

Course 5 of 9 in the IBM Data Science Specialization

Much of the world's data resides in databases. SQL (or Structured Query Language) is a powerful language which is used for communicating with and extracting data from databases. A working knowledge of databases and SQL is a must if you want to become a data scientist. The purpose of this course is to introduce relational database concepts and help you learn and apply foundational knowledge of the SQL language. It is also intended to get you started with performing SQL access in a data science environment. The emphasis in this course is on hands-on and practical learning . As such, you will work with real databases, real data science tools, and real-world datasets. You will create a database instance in the cloud. Through a series of hands-on labs you will practice building and running SQL queries. You will also learn how to access databases from Jupyter notebooks using SQL and Python. No prior knowledge of databases, SQL, Python, or programming is required. Anyone can audit this course at no-charge. If you choose to take this course and earn the Coursera course certificate, you can also earn an IBM digital badge upon successful completion of the course. LIMITED TIME OFFER: Subscription is only $39 USD per month for access to graded materials and a certificate.

Skills You'll Learn

Cloud Databases, Python Programming, Ipython, Relational Database Management System (RDBMS), SQL

Reviews

4.7 (6,645 ratings)

5 stars
5,002 ratings
4 stars
1,259 ratings
3 stars
262 ratings
2 stars
67 ratings
1 star
55 ratings

DT

Jun 17, 2019

This is a great review of an important topic. If I knew how much this mattered for the advanced capstone course I would have paid more attention. As it was, I came back here frequently.

LG

Mar 06, 2019

I really liked this course and it is worth it for learning basic SQL queries with Python. I wish we could have more practical exercises but on the whole this was an excellent course.

From the lesson

Week 2 - Advanced SQL

By the end of this module you will: (1) earn how to use string patterns and ranges to search data, how to sort and group data in result sets, as well as (2) learn how to work with multiple tables in a relational database using join operations.

Using String Patterns, Ranges4:22

Sorting Result Sets2:44

Grouping Result Sets3:32

Taught By

Rav Ahuja
AI and Data Science Program Director

Transcript

Hello and welcome to Grouping Select Statement Result Sets. In this video, we will learn about some advanced techniques in retrieving data from a relational database table, and sorting and grouping how the results set displays. At the end of this lesson, you will be able to explain how to eliminate duplicates from a result set and describe how to further restrict a result set. At times, a select statement result set can contain duplicate values. Based on our simplified library database model, in the author table example, the country column lists the two-letter country code of the author's country. If we select just the country column, we get a list of all of the countries. For example; Select country from author order by one. The order by clause sorts the result set. This result set lists the countries the authors belong to, sorted alphabetically by country. In this case, the result set displays 20 rows, one row for each of the 20 authors. But some of the authors come from the same country, so the result set contains duplicates. However, all we need is a list of countries the authors come from. So in this case, duplicates do not make sense. To eliminate duplicates, we use the keyword distinct. Using the keyword "distinct" reduces the result set to just six rows. But what if we wanted to also know how many authors come from the same country? So now we know that the 20 authors come from six different countries. But we might want to also know how many authors come from the same country. To display the result set listing the country and number of authors that come from that country, we add the "group by" clause to the select statement. The "group by" clause groups a result into subsets that has matching values for one or more columns. In this example, countries are grouped and then counted using the count function. Notice the column heading for the second column and the result set. The numeric value "2" displays as a column name because the column name is not directly available in the table. The second column in the result set was calculated by the count function. Instead of using the column named "2", we can assign a column name to the result set. We do this using the "as" keyword. In this example, we change the derived column name "2" to column name "Count" using the "as count" keyword. This helps clarify the meaning of the result set. Now that we have the count of authors from different countries, we can further restrict the number of rows by passing some conditions. For example, we can check if there are more than four authors from the same country. To set a condition to a "group by" clause, we use the keyword "having". The "having" clause is used in combination with the "group by" clause. It is very important to note that the "where" clause is for the entire result set, but the "having" clause works only with the "group by" clause. To check if there are more than four authors from the same country, we add the following to the select statement, having count country greater than four. Only countries that have five or more authors from that country are listed in the result set. In this example, those countries are China with six authors and India, also with six authors. Now you can explain how to eliminate duplicates from a result set and describe how to further restrict a result set. Thanks for watching this video.

Explore our Catalog

Join for free and get personalized recommendations, updates and offers.

Coursera provides universal access to the world’s best education, partnering with top universities and organizations to offer courses online.

© 2019 Coursera Inc. All rights reserved.

Get it on Google Play

Coursera
About
Leadership
Careers
Catalog
Certificates
MasterTrack™ Certificates
Degrees
For Enterprise
For Government
For Campus

Community
Learners
Partners
Developers
Beta Testers
Translators

Connect
Blog
Facebook
LinkedIn
Twitter
Instagram
YouTube
Tech Blog

More
Terms
Privacy
Help
Accessibility
Press
Contact
Directory
Affiliates