Sandhya.np14 at 15:41, 5 February 2019

2019-02-05T15:41:56Z

PoojaMoolya: Created page with "{| border=1 | '''Time''' | '''Narration''' |- | 00:01 | Welcome to the''' Spoken Tutorial''' on''' Select with Aggregate functions.''' |- | 00:07 | In this tutorial w..."

2019-01-30T11:04:00Z

Created page with "{| border=1 | '''Time''' | '''Narration''' |- | 00:01 | Welcome to the''' Spoken Tutorial''' on''' Select with Aggregate functions.''' |- | 00:07 | In this tutorial w..."

New page

{| border=1
| '''Time'''
| '''Narration'''

|-
| 00:01
| Welcome to the''' Spoken Tutorial''' on''' Select with Aggregate functions.'''

|-
| 00:07
| In this tutorial we will learn more '''clauses''' that can be used with the '''select statement, '''such as-

'''Distinct'''

|-
|00:16
| '''Between'''

'''Like'''

|-
|00:19
| '''In'''

'''Is Null''' and '''Aggregate functions'''

|-
| 00:26
| To record this tutorial, I am using

'''Ubuntu Linux 14.04''' operating system

'''PostgreSQL 9.3.x'''

'''pgAdmin 1.18'''

|-
| 00:41
| To follow this tutorial, you should have basic understanding of database and tables.

|-
|00:48
| For more details, refer to the '''RDBMS – PostgreSQL''' series on this website.

|-
| 00:55
| Let’s begin. First we will learn about the '''DISTINCT''' clause.

|-
| 01:01
| '''DISTINCT keyword '''is used in the '''SELECT statement '''to list distinct and unique values.

It removes the duplicate rows or column values from the query result.

|-
| 01:18
| The syntax is as follows:

'''SELECT DISTINCT column-names FROM table-name WHERE [condition]'''

|-
| 01:28
| Let us open the '''pgAdmin '''for the demonstration.

|-
| 01:33
| Click on the '''SQL''' icon to open the '''SQL Editor.'''

|-
| 01:38
| Type the code as shown here.

This '''select statement '''will retrieve the unique city names from the '''students table'''.

|-
|01:48
| It will eliminate duplicate values from the specified column i.e. '''city'''.

|-
| 01:54
| Let us Execute the query to see the output.

|-
| 01:59
| Next, we will learn about the '''BETWEEN operator.'''

|-
| 02:03
| '''BETWEEN operator''' is used to retrieve values within a given range.

|-
|02:09
| The syntax is: '''SELECT column-names FROM table-name WHERE column-name BETWEEN value1 and value2;'''

|-
| 02:19
| Let's see an example for between operator.

|-
| 02:23
| Let's clear the screen and type the code for the query.

|-
| 02:28
| Say, I want to retrieve details of students who are born between January 1995 and January 1996.

|-
| 02:38
| Let's see the output.

Notice the output shows the list of students as expected.

|-
| 02:47
| We will see another example for '''BETWEEN operator.'''

|-
| 02:52
| This '''query''' will show all rows from the''' students table,''' where the '''cgpa''' value is between 7.8 and 8.5.

|-
|03:02
| Let us see the output.

|-
| 03:05
| Notice from the output that the value is inclusive of 7.8 and 8.5.

|-
|03:13
| The same '''query '''can also be written as shown here.

|-
|03:18
| Instead of '''BETWEEN operator''', we can use- '''greater than or equal to '''and

'''lesser than or equal to operators'''.

|-
| 03:27
| Execute the query to see the output.

|-
| 03:32
| Next we will learn about the '''LIKE operator.'''

|-
| 03:36
| '''LIKE operator''' is used to match text values against a given pattern.

|-
|03:42
| It allows '''wildcard characters '''to be used within the '''WHERE clause'''.

|-
| 03:47
| There are two '''wildcards '''that can be used in conjunction with the '''LIKE operator''':

|-
|03:53
| '''Percent (%):'''

The '''percent '''sign matches any sequence of string of any length.

|-
|04:00
| '''Underscore (_):'''

The '''underscore '''matches a single character.

|-
| 04:06
| We will see few examples using '''percent wildcard.'''

|-
| 04:11
| Suppose I don't remember the exact name of a student.

But I remember that the student’s name begins with the letter ''''Ra''''.

|-
|04:22
| It is very difficult to search the entire '''students table, '''as there are many rows in the '''table.'''

|-
| 04:29
| To make the search easy, we can use the '''LIKE operator''' as shown here.

|-
| 04:35
| Here, all the student's names start with ''''Ra'''' and is followed by any sequence of characters.

This is called '''pattern matching'''.

|-
| 04:48
| Let us see a few more examples.

|-
| 04:52
| Type the code as shown.

|-
|04:56
| This '''query '''returns those rows where the students’ names end with ''''la'.'''

|-
|05:03
| There can be any number of characters before ''''la'.'''

|-
| 05:10
| Let us see another pattern.

This '''statement''' returns all the rows where the student’s name has''' 'am' '''in the middle.

|-
|05:21
| Note''' percent '''sign before and after ''''am'''' .

|-
| 05:27
| Next we will see how to use '''underscore '''to match a single character.

|-
|05:34
| This will fetch rows of students whose name has three characters and has ''''a'''' in the 2<sup>nd</sup> position.

|-
| 05:44
| '''Underscore 'r' percent '''matches students whose names begin with a single character.

It is followed by ''''r'''' and ends with any number of characters.

Let's see the output.

|-
| 05:58
| Next we will see how to use '''IN operator.'''

|-
| 06:03
| Use '''IN operator '''in the '''WHERE clause '''to check if the value matches any value in a given list.

The syntax is: '''Select column-name from table-name WHERE column-name IN ( value1, value2...)''' etc

|-
| 06:22
| Let us see an example.

|-
| 06:25
| This '''statement''' will return all rows from the '''students table ''' where the city is either '''Chennai, Nagpur '''or '''Hyderabad'''.

|-
| 06:35
| It helps to reduce the usage of multiple '''OR conditions''' to get the same result.

Let us see the output

|-
| 06:44
| Next we will see how to use the '''IS NULL operator '''in the '''select statement'''.

|-
| 06:51
| This '''statement '''will list down the records where the data of birth column is empty.

|-
|06:58
| In the same way, we can also use the '''IS NOT NULL operator''', as shown here.

|-
| 07:07
| Let us now learn about '''aggregate function'''.

|-
| 07:11
| '''Aggregate functions '''are '''PostgreSQL built-in functions.'''

|-
| 07:16
| It operates on several rows of a query and returns a single result.

|-
| 07:22
| '''List of aggregate functions'''

|-
| 07:25
| '''Count''' – It Returns the number of rows in a table.

|-
| 07:30
| '''Sum''' – Returns the sum of a selected column

|-
| 07:34
| '''Max''' - Returns the largest value of a specified column

|-
| 07:39
| '''Min''' – Returns the smallest value of a specified column

|-
| 07:44
| '''Avg''' – Returns the average value for a specified column

|-
| 07:49
| Switch back to '''SQL Editor''' window.

|-
| 07:53
| Let’s say, we want to count the number of existing records in the '''students table.'''

For that, type the code as shown here.

|-
| 08:03
| The '''count open parentheses asterisk close parentheses function '''returns the number of rows that are fetched by the '''SELECT statement.'''

|-
| 08:13
| Execute this query to see the output.

|-
| 08:17
| There are 14 records in the '''students table.'''

|-
| 08:21
| We can also specify the column name in the '''function '''as shown here.

|-
| 08:27
| Once again execute the query.

|-
| 08:31
| Why is it showing 13?

Because the '''count function '''only counts values which are not '''NULL'''.

|-
| 08:40
| Let us see the output when we specify the '''date of birth''' column.

|-
| 08:45
| Type the code and execute.

|-
| 08:49
| Here we can see the '''date of birth''' column value is '''NULL''' for the student ''''Ram charan'.'''

|-
| 08:56
| I have left '''date of birth''' column blank during record creation for this student.

So the''' count function''' displays the output as 13.

|-
| 09:07
| Next let us see how to use the''' SUM function.'''

|-
| 09:11
| Let us clear the screen.

|-
| 09:14
| Type the code as shown here and execute the query.

This returns the summed-up value of the '''cgpa''' column.

|-
| 09:24
| You can also give an additional ''''Where' condition''' as shown here.

|-
| 09:30
| This returns the sum of '''cgpa''' values of the students who are from '''Mumbai''' city.

|-
| 09:38
| Next, let us find the maximum '''cgpa''' score.

|-
| 09:42
| This '''select statement '''returns the maximum '''cgpa''' score from the '''students table'''.

9.6 is the highest '''cgpa''' score in the '''students''' table.

|-
| 09:54
| Likewise, you can try the remaining '''aggregate functions '''on your own.

|-
| 10:00
| With this we come to the end of this tutorial.

Let us summarize.

|-
| 10:06
| In this tutorial, we have learnt about More clauses that can be used with the '''select statement, '''such as-

'''Distinct''' , '''Between''' , '''Like''' , '''In''' , '''Is Null''' and '''Aggregate functions'''

|-
| 10:21
| As an assignment, write '''select statements''' to Display all columns from '''students table''' where the student name ends with ''''a''''

|-
| 01:31
| Display the minimum and average of the '''cgpa''' column.

|-
| 10:36
| The video at the following link summarises the Spoken Tutorial project.

Please download and watch it.

|-
| 10:44
| The''' Spoken Tutorial Project''' Team

conducts workshops and gives certificates

For more details, please write to us.

|-
| 10:54
| Please post your timed queries in this forum.

|-
| 10:58
| Spoken Tutorial project is funded by NMEICT, MHRD, Government of India.

More information on this mission is available at this link.

|-
| 11:11
| This is Nirmala Venkat from IIT Bombay, signing off. Thanks for watching.

|}

RDBMS-PostgreSQL/C2/Select-with-Aggregate-functions/English-timed - Revision history

Sandhya.np14 at 15:41, 5 February 2019

PoojaMoolya: Created page with "{| border=1 | '''Time''' | '''Narration''' |- | 00:01 | Welcome to the''' Spoken Tutorial''' on''' Select with Aggregate functions.''' |- | 00:07 | In this tutorial w..."