Difference between revisions of "RDBMS-PostgreSQL/C2/Aggregation-facilities-in-SQL/English-timed"
PoojaMoolya (Talk | contribs) (Created page with "{| border=1 | '''Time''' | '''Narration''' |- |00:01 | Welcome to the''' Spoken Tutorial''' on '''Aggregation facilities in SQL.''' |- |00:07 | In this tutorial we will...") |
Sandhya.np14 (Talk | contribs) |
||
Line 9: | Line 9: | ||
|- | |- | ||
|00:07 | |00:07 | ||
− | | In this tutorial we will learn about | + | | In this tutorial, we will learn about: '''Group by ''', '''Having '''and '''Order by clause'''. |
− | + | ||
− | '''Group by ''' , '''Having '''and '''Order by clause''' | + | |
|- | |- | ||
|00:16 | |00:16 | ||
− | | To record this tutorial, I am using '''Ubuntu Linux 14.04''' operating system | + | | To record this tutorial, I am using: '''Ubuntu Linux 14.04''' operating system, |
|- | |- | ||
|00:23 | |00:23 | ||
− | | '''PostgreSQL 9.3.x''' | + | | '''PostgreSQL 9.3.x''', |
'''pgAdmin 1.18''' | '''pgAdmin 1.18''' | ||
Line 25: | Line 23: | ||
|- | |- | ||
|00:31 | |00:31 | ||
− | | To follow this tutorial, you should have basic understanding of | + | | To follow this tutorial, you should have basic understanding of '''SELECT''' statement and '''Aggregate functions.''' |
− | + | ||
− | '''SELECT | + | |
|- | |- | ||
Line 43: | Line 39: | ||
|- | |- | ||
|01:00 | |01:00 | ||
− | |'''Aggregate functions''' are mostly used with the '''Group By clause '''of the '''SELECT | + | |'''Aggregate functions''' are mostly used with the '''Group By clause '''of the '''SELECT''' statement. |
|- | |- | ||
Line 53: | Line 49: | ||
| It returns one record for each group. | | It returns one record for each group. | ||
− | '''Group by''' can be done with one or more | + | '''Group by''' can be done with one or more '''column'''s. |
|- | |- | ||
Line 59: | Line 55: | ||
| The syntax for group by clause is: | | The syntax for group by clause is: | ||
− | '''SELECT column1 | + | '''SELECT column1 comma aggregate-function of column2''' |
''FROM table-name''' | ''FROM table-name''' | ||
Line 65: | Line 61: | ||
'''[WHERE condition]''' | '''[WHERE condition]''' | ||
− | '''GROUP BY column1 | + | '''GROUP BY column1 comma column2''' |
|- | |- | ||
Line 73: | Line 69: | ||
|- | |- | ||
| 01:43 | | 01:43 | ||
− | | Type the code as shown here and execute the query. | + | | Type the code as shown here and '''execute''' the '''query'''. |
|- | |- | ||
Line 79: | Line 75: | ||
| We can see the student details from various departments and cities. | | We can see the student details from various departments and cities. | ||
− | How can we group these | + | How can we group these '''record'''s based on a '''column'''? |
|- | |- | ||
Line 89: | Line 85: | ||
| Let us write a query. | | Let us write a query. | ||
− | First clear the''' SQL editor window.''' | + | First, clear the''' SQL editor window.''' |
|- | |- | ||
Line 101: | Line 97: | ||
|- | |- | ||
|02:21 | |02:21 | ||
− | |After grouping, the '''aggregate function Count '''will count the number of records under each department. | + | |After grouping, the '''aggregate function 'Count' '''will count the number of records under each department. |
|- | |- | ||
|02:28 | |02:28 | ||
− | |The grouping is based on | + | |The grouping is based on '''row'''s with the same value in the specified column i.e '''deptid''' |
|- | |- | ||
|02:36 | |02:36 | ||
− | |Note that the table is not physically rearranged. | + | |Note that the '''table''' is not physically rearranged. |
|- | |- | ||
|02:40 | |02:40 | ||
− | | Let’s execute the code and see the output. | + | | Let’s execute the '''code''' and see the output. |
|- | |- | ||
Line 121: | Line 117: | ||
|- | |- | ||
| 02:49 | | 02:49 | ||
− | | Next, let us see how to add '''where''' condition in the '''Group by | + | | Next, let us see how to add '''where''' condition in the '''Group by''' clause. |
|- | |- | ||
Line 129: | Line 125: | ||
|- | |- | ||
|02:57 | |02:57 | ||
− | |Here a '''where | + | |Here a '''where''' condition is specified to display the total number of students in the department ''''CS'.''' |
|- | |- | ||
|03:04 | |03:04 | ||
− | |The''' Where | + | |The''' Where''' clause used in a query containing a '''Group By''' clause, eliminates the rows before grouping. |
|- | |- | ||
Line 141: | Line 137: | ||
|- | |- | ||
| 03:14 | | 03:14 | ||
− | | Let us see a few more examples of '''Group by | + | | Let us see a few more examples of '''Group by''' clause. |
|- | |- | ||
Line 149: | Line 145: | ||
|- | |- | ||
| 03:21 | | 03:21 | ||
− | | I want to group the '''students | + | | I want to group the '''students''' table to find out the number of male and female students. |
So, we have to group the rows with the column '''gender'''. | So, we have to group the rows with the column '''gender'''. | ||
Line 175: | Line 171: | ||
|- | |- | ||
| 03:52 | | 03:52 | ||
− | | Likewise, we can make use of any '''aggregate function '''in the '''SELECT | + | | Likewise, we can make use of any '''aggregate function '''in the '''SELECT '''statement with '''Group by''' clause. |
|- | |- | ||
Line 195: | Line 191: | ||
|- | |- | ||
|04:21 | |04:21 | ||
− | |The list of column names in the''' SELECT | + | |The list of column names in the''' SELECT''' clause must appear in the '''Group by''' clause also. |
− | Let us | + | Let us execute and see the output. |
|- | |- | ||
| 04:32 | | 04:32 | ||
− | | Next we will learn about the '''Having | + | | Next we will learn about the '''Having '''clause. |
|- | |- | ||
| 04:36 | | 04:36 | ||
− | | '''HAVING | + | | '''HAVING''' clause is used to retrieve rows for the specified condition from a grouped result. |
|- | |- | ||
|04:43 | |04:43 | ||
− | | Use the '''WHERE | + | | Use the '''WHERE''' clause to exclude rows that you don't want to group, whereas use the''' HAVING''' clause to filter rows after they have been grouped. |
− | + | ||
− | + | ||
|- | |- | ||
Line 217: | Line 211: | ||
| The syntax is | | The syntax is | ||
− | '''SELECT column1 | + | '''SELECT column1 comma aggregate-function of column2 ''' |
'''FROM table-name''' | '''FROM table-name''' | ||
Line 223: | Line 217: | ||
'''[WHERE condition]''' | '''[WHERE condition]''' | ||
− | '''GROUP BY column1 | + | '''GROUP BY column1 comma column2''' |
'''HAVING Condition''' | '''HAVING Condition''' | ||
Line 263: | Line 257: | ||
|- | |- | ||
| 05:53 | | 05:53 | ||
− | | Here, we are using '''aggregate function count''' in the '''having | + | | Here, we are using '''aggregate function 'count'''' in the '''having''' clause. |
That is, the count should be greater than 1. | That is, the count should be greater than 1. | ||
Line 269: | Line 263: | ||
|- | |- | ||
|06:02 | |06:02 | ||
− | |Here the student records are grouped by '''department''' and then by '''city'''. | + | |Here, the student records are grouped by '''department''' and then by '''city'''. |
The '''having''' condition is applied to the filtered rows. | The '''having''' condition is applied to the filtered rows. | ||
Line 279: | Line 273: | ||
|- | |- | ||
| 06:16 | | 06:16 | ||
− | | Next we will see the '''Order by | + | | Next we will see the '''Order by''' clause. |
|- | |- | ||
| 06:19 | | 06:19 | ||
− | | '''Order By | + | | '''Order By''' clause is used to sort the records in ascending or descending order. |
|- | |- | ||
|06:25 | |06:25 | ||
− | | This '''clause''' is always used at the end of the '''SELECT | + | | This '''clause''' is always used at the end of the '''SELECT''' statement. |
|- | |- | ||
|06:30 | |06:30 | ||
− | | The syntax is shown here | + | | The syntax is shown here. |
|- | |- | ||
Line 303: | Line 297: | ||
|- | |- | ||
|06:42 | |06:42 | ||
− | |I have added the''' Order by''' '''clause''' to the end of the''' SELECT | + | |I have added the''' Order by''' '''clause''' to the end of the '''SELECT''' statement. |
|- | |- | ||
Line 331: | Line 325: | ||
| Type the code. | | Type the code. | ||
− | This query will sort the '''students | + | This query will sort the '''students''' table by '''city''' in ascending order. |
|- | |- | ||
Line 337: | Line 331: | ||
|Use a comma to separate the columns. | |Use a comma to separate the columns. | ||
− | Then within each '''city, '''it will sort by the student names. | + | Then, within each '''city, '''it will sort by the student names. |
|- | |- | ||
Line 365: | Line 359: | ||
| Here we can see the sorted result of grouped rows. | | Here we can see the sorted result of grouped rows. | ||
− | '''Dept id '''is sorted in ascending order and within each department, the '''city''' is sorted in ascending order. | + | '''Dept id '''is sorted in ascending order and within each '''department''', the '''city''' is sorted in ascending order. |
|- | |- | ||
Line 375: | Line 369: | ||
|- | |- | ||
| 08:32 | | 08:32 | ||
− | | In this tutorial, we have learnt about | + | | In this tutorial, we have learnt about the clauses: |
'''Group by ''' | '''Group by ''' | ||
Line 381: | Line 375: | ||
'''Having''' | '''Having''' | ||
− | '''Order by''' | + | '''Order by'''. |
|- | |- | ||
| 08:40 | | 08:40 | ||
− | | As an assignment- Write a '''Select | + | | As an assignment- Write a '''Select''' statement to display the number of students with the same '''CGPA'''. |
|- | |- | ||
| 08:47 | | 08:47 | ||
− | | Hint: Group the rows by '''CGPA''' column | + | | Hint: Group the rows by '''CGPA''' column. |
|- | |- | ||
| 08:51 | | 08:51 | ||
− | | The video at the following link | + | | The video at the following link summarizes the '''Spoken Tutorial''' project. |
Please download and watch it. | Please download and watch it. | ||
Line 399: | Line 393: | ||
|- | |- | ||
| 08:58 | | 08:58 | ||
− | | The''' Spoken Tutorial Project''' | + | | The''' Spoken Tutorial Project''' team conducts workshops and gives certificates. |
− | + | ||
− | conducts workshops and gives certificates | + | |
For more details, please write to us. | For more details, please write to us. | ||
Line 411: | Line 403: | ||
|- | |- | ||
| 09:11 | | 09:11 | ||
− | | Spoken Tutorial project is funded by NMEICT, MHRD, Government of India. | + | | Spoken Tutorial project is funded by '''NMEICT, MHRD''', Government of India. |
More information on this mission is available at this link. | More information on this mission is available at this link. | ||
Line 417: | Line 409: | ||
|- | |- | ||
| 09:22 | | 09:22 | ||
− | | This is Nirmala Venkat from IIT Bombay, signing off. Thanks for watching. | + | | This is Nirmala Venkat from '''IIT Bombay''', signing off. Thanks for watching. |
|} | |} |
Latest revision as of 12:18, 6 February 2019
Time | Narration |
00:01 | Welcome to the Spoken Tutorial on Aggregation facilities in SQL. |
00:07 | In this tutorial, we will learn about: Group by , Having and Order by clause. |
00:16 | To record this tutorial, I am using: Ubuntu Linux 14.04 operating system, |
00:23 | PostgreSQL 9.3.x,
pgAdmin 1.18 |
00:31 | To follow this tutorial, you should have basic understanding of SELECT statement and Aggregate functions. |
00:41 | For more details, refer to the previous RDBMS PostgreSQL tutorials on this website. |
00:48 | Aggregate functions perform calculations on a set of values and return a single value. |
00:55 | We learnt about these functions in the earlier tutorials. |
01:00 | Aggregate functions are mostly used with the Group By clause of the SELECT statement. |
01:06 | Group by clause is used to collect identical data in groups. |
01:12 | It returns one record for each group.
Group by can be done with one or more columns. |
01:21 | The syntax for group by clause is:
SELECT column1 comma aggregate-function of column2 FROM table-name' [WHERE condition] GROUP BY column1 comma column2 |
01:37 | Let’s open the SQL Editor window in pgAdmin for demonstration. |
01:43 | Type the code as shown here and execute the query. |
01:50 | We can see the student details from various departments and cities.
How can we group these records based on a column? |
02:00 | Say, we want to display how many students are there in each department. |
02:06 | Let us write a query.
First, clear the SQL editor window. |
02:11 | Now type the code as shown here. |
02:15 | This query will group the records in the students table with the deptid column. |
02:21 | After grouping, the aggregate function 'Count' will count the number of records under each department. |
02:28 | The grouping is based on rows with the same value in the specified column i.e deptid |
02:36 | Note that the table is not physically rearranged. |
02:40 | Let’s execute the code and see the output. |
02:44 | The number of students in each department is displayed as output. |
02:49 | Next, let us see how to add where condition in the Group by clause. |
02:54 | We will change the same code to add a condition. |
02:57 | Here a where condition is specified to display the total number of students in the department 'CS'. |
03:04 | The Where clause used in a query containing a Group By clause, eliminates the rows before grouping. |
03:11 | Execute the code now to see the output. |
03:14 | Let us see a few more examples of Group by clause. |
03:18 | Type the code as shown here. |
03:21 | I want to group the students table to find out the number of male and female students.
So, we have to group the rows with the column gender. |
03:31 | Execute the query. |
03:33 | We can see the output as expected. |
03:36 | Now I want to find out the maximum CGPA score of students from each city. |
03:43 | For this, I'll type the query as shown. |
03:48 | Now execute the code to see the output. |
03:52 | Likewise, we can make use of any aggregate function in the SELECT statement with Group by clause. |
03:59 | Next we will see how to use more than one column in Group by clause. |
04:05 | Type the code as shown here. |
04:08 | Here, after group by you can see two columns deptid and city. |
04:14 | First it will group the records with department and then group with city, for each department. |
04:21 | The list of column names in the SELECT clause must appear in the Group by clause also.
Let us execute and see the output. |
04:32 | Next we will learn about the Having clause. |
04:36 | HAVING clause is used to retrieve rows for the specified condition from a grouped result. |
04:43 | Use the WHERE clause to exclude rows that you don't want to group, whereas use the HAVING clause to filter rows after they have been grouped. |
04:54 | The syntax is
SELECT column1 comma aggregate-function of column2 FROM table-name [WHERE condition] GROUP BY column1 comma column2 HAVING Condition |
05:09 | Let us see an example for this.
Switch to the SQL Editor window. |
05:15 | We will add the condition 'having city= 'Chennai'.
The HAVING clause comes after the Group By clause. |
05:24 | Note that you can apply HAVING clause only to columns that appear in the Group By clause or an aggregate function. |
05:32 | Execute and see the result. |
05:35 | We can see two rows as output.
There are 3 students from CS department and 1 student from EE department. |
05:45 | The condition specifies that the students are from Chennai. |
05:50 | Let’s see another example. |
05:53 | Here, we are using aggregate function 'count' in the having clause.
That is, the count should be greater than 1. |
06:02 | Here, the student records are grouped by department and then by city.
The having condition is applied to the filtered rows. |
06:11 | Once again, execute to see the result. |
06:16 | Next we will see the Order by clause. |
06:19 | Order By clause is used to sort the records in ascending or descending order. |
06:25 | This clause is always used at the end of the SELECT statement. |
06:30 | The syntax is shown here. |
06:34 | Let us switch back to SQL Editor window for demonstration. |
06:38 | Type the code as shown on the screen. |
06:42 | I have added the Order by clause to the end of the SELECT statement. |
06:47 | Now execute the query.
We see that the total number of students in each department is sorted in ascending order. |
06:56 | By default, the sorting is always done in ascending order. |
07:01 | Type DESC at the end of the query to sort in descending order. |
07:07 | Execute the query to see the output. |
07:11 | We can also sort result set on multiple columns. |
07:16 | Type the code.
This query will sort the students table by city in ascending order. |
07:23 | Use a comma to separate the columns.
Then, within each city, it will sort by the student names. |
07:31 | Columns specified in the ORDER BY clause must be one of the columns selected in the SELECT column list. |
07:38 | Let us see the output. Here the city is sorted in ascending order and within each city the student name is sorted in ascending order. |
07:50 | Let us modify the same query to see city in descending order and the student name in ascending order. |
07:58 | Execute to see the output. |
08:03 | We will see another example of order by in Group by clause.
Execute the query to see the output. |
08:11 | Here we can see the sorted result of grouped rows.
Dept id is sorted in ascending order and within each department, the city is sorted in ascending order. |
08:25 | With this we come to the end of this tutorial.
Let us summarize. |
08:32 | In this tutorial, we have learnt about the clauses:
Group by Having Order by. |
08:40 | As an assignment- Write a Select statement to display the number of students with the same CGPA. |
08:47 | Hint: Group the rows by CGPA column. |
08:51 | The video at the following link summarizes the Spoken Tutorial project.
Please download and watch it. |
08:58 | The Spoken Tutorial Project team conducts workshops and gives certificates.
For more details, please write to us. |
09:07 | Please post your timed queries in this forum. |
09:11 | Spoken Tutorial project is funded by NMEICT, MHRD, Government of India.
More information on this mission is available at this link. |
09:22 | This is Nirmala Venkat from IIT Bombay, signing off. Thanks for watching. |