Grouping Result Sets

Loading...

From the course by IBM

Databases and SQL for Data Science

2971 ratings

IBM

Databases and SQL for Data Science

2971 ratings

Course 5 of 9 in the Specialization IBM Data Science Professional Certificate

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.

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

Meet the Instructors

Rav Ahuja
Data Science Program Manager
IBM

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.

Download on the App Store

Get it on Google Play