Script | Spoken-Tutorial - User contributions [en]

PostgreSQL-Database/C2/GROUP-BY-and-HAVING-Clause/English

2026-04-27T10:31:58Z

ANJALISATDIVE:

'''Title of the Script: GROUP BY and HAVING Clause'''

'''Author: Ketki Bhamble / EduPyramids'''

'''Keywords:''' PostgreSQL, pgAdmin 4, query, GROUP BY clause, HAVING clause, aggregate functions, COUNT(*), EduPyramids, Video Tutorial.

{| border=1
|-
| align=center| '''Visual Cue'''
| align=center| '''Narration'''
|-
|| '''Slide 1'''

'''Title Slide'''
|| Welcome to this Spoken Tutorial on '''GROUP BY and HAVING Clause''' in '''PostgreSQL'''.
|-
|| '''Slide 2'''

'''Learning Objectives'''
|| In this tutorial, we will learn how to

* Use the '''GROUP BY '''clause
* Apply aggregate functions with '''GROUP''' '''BY''' and
* Filter grouped results using the '''HAVING''' '''clause'''.

|-
|| '''Slide 3'''

'''System Requirements '''

* '''Ubuntu 24.04 LTS'''
* '''PostgreSQL version 18.1''', and
* '''PgAdmin 4 version 9.11'''.

|| To record this tutorial, I am using the following setup.
|-
|| '''Slide 4'''

'''Pre-requisites '''

[https://edupyramids.org/ https://EduPyramids.org] '''

|| To follow this tutorial,
* Learners should have '''PostgreSQL''' and '''pgAdmin4.'''
* For the prerequisite '''PostgreSQL''' tutorials, please visit this website.

|-
|| '''Slide 5'''

'''Code Files '''

The following code file is required to practice this tutorial:

* '''gh-commands.txt'''

This file is provided in the Code Files link on this tutorial page.

Please download and extract the file.

|| The following '''code file''' is required to practice this tutorial.

This file is provided in the '''Code Files''' link of this tutorial page.

Please download and extract the file.
|-
||
|| Let us get started.

I have opened the '''pgAdmin4 '''interface and connected to the localhost server.
|-
|| Display the students table on the screen

SELECT * FROM students;
|| We are using the '''students''' table.

It contains '''student ID''', '''name''', '''city,''' '''gender''', date of birth, and '''CGPA'''.
|-
|| '''Slide 6'''

'''GROUP BY'''
|| The '''GROUP BY''' clause groups rows that have the same values into summary rows.

It is used along with aggregate functions to organise results.
|-
|| '''Slide 7'''

'''Syntax of GROUP BY'''

'''SELECT column_name, aggregate_function(column_name)'''

'''FROM table_name'''

'''GROUP BY column_name;'''
|| This is the syntax of the''' GROUP BY clause'''.

'''GROUP BY clause '''groups rows with the same column values.
|-
||
|| Let us find the number of students from each city.
|-
|| '''Type: '''

'''SELECT city, COUNT(*) '''

'''FROM students'''

'''GROUP BY city;'''

Click the '''Execute query icon.'''

Point to the results.
|| Type the following query.

It groups rows by city and counts the total number of students in each city.

Let us execute the query.

The result shows each city with the total number of students from that city.
|-
||
|| Now let us calculate the average '''CGPA''' of students in each city.
|-
|| '''Type:'''

'''SELECT city, ROUND(AVG(cgpa), 2)'''

'''FROM students'''

'''GROUP BY city;'''

Click the Execute query icon.
|| Type the following query.

This query groups students by city and calculates the average '''CGPA '''for each group.Let us execute this query.
|-
||
|| We can also group records using other columns such as gender.
|-
|| '''Type: '''

'''SELECT gender, COUNT(*)'''

'''FROM students'''

'''GROUP BY gender;'''

Click the Execute query icon.

Point to the results in the table.
|| Type this query.

This query groups students based on gender.

Let us execute the query.

The result shows the count of male and female students in the table.
|-
|| '''Slide 8'''

'''HAVING clause'''
|| The '''HAVING''' clause filters grouped results.

It is used along with the '''GROUP BY''' clause and aggregate functions.
|-
|| '''Type: '''

'''SELECT city, COUNT(*)'''

'''FROM students'''

'''GROUP BY city'''

'''HAVING COUNT(*) > 2;'''

Click the Execute query icon.

Point to the results in the table.
|| Type the following query.

This query groups the records by '''city'''.

Let us execute the query.

It displays only those cities where the number of students is greater than 2.
|-
|| '''Type: '''

'''SELECT city, ROUND(AVG(cgpa), 2)'''

'''FROM students'''

'''GROUP BY city'''

'''HAVING ROUND(AVG(cgpa), 2) > 8.5;'''

Click the Execute query icon.

Point to the results in the table.
|| Type the following query.

Here the '''HAVING''' clause filters the grouped results.

Let us execute the query.

It displays only those cities where the average '''CGPA''' is greater than 8 point 5.
|-
|| '''Type: '''

'''SELECT city, COUNT(*)'''

'''FROM students'''

'''WHERE cgpa > 8'''

'''GROUP BY city'''

'''HAVING COUNT(*) > 1;'''

Click the Execute query icon.

Point to the results in the table.
|| Let us type this query.

In this query, we will analyze student data city-wise.

First, we filter students with '''CGPA''' greater than 8.

Then, we group these students by city.

The '''WHERE''' clause filters rows before grouping. However the''' HAVING '''clause filters groups after aggregation.

Then we will count students in each city.

Let us execute the query.

Output displays only those cities with more than one student.
|-
||
|| With this we come to the end of this tutorial.
|-
|| '''Slide 9'''

'''Summary'''

In this tutorial, we learnt how to:

* Use the GROUP BY clause,
* Apply aggregate functions with GROUP BY, and
* Filter grouped results using the HAVING clause.

|| Let us summarize.
|-
|| '''Slide 10 + 11'''

'''Assignment'''

As an Assignment,

* Find the maximum '''CGPA''' obtained by students in each city.
* Display the minimum '''CGPA '''for each gender.
* Find the total '''CGPA''' of students for each city.
* Display cities where the average '''CGPA''' is less than 8.5.
* Find genders that have more than 5 students.
* Display cities where the maximum '''CGPA''' is greater than 9.

|| We encourage you to do this assignment.

|-
|| '''Slide 12'''

'''Thank you'''

This '''Spoken''' '''Tutorial''' is brought to you by '''EduPyramids''' Educational Private Limited. SINE IIT Bombay.

'''Thank you'''
|| Thank you for joining.
|-
|}

PostgreSQL-Database/C2/Alias-Names/English

2026-04-27T10:06:32Z

ANJALISATDIVE:

Title of the Script: Alias Names

Author: EduPyramids

Keywords: PostgreSQL, pgAdmin 4, alias, AS keyword, space, underscore, double quotes, video tutorial, EduPyramids.

{| border=1
|-
! width="35%" | '''Visual Cue'''
! width="65%" | '''Narration'''

|-
|| '''Title Slide 1'''
|| Welcome to this Spoken Tutorial on '''Alias Names''' in '''PostgreSQL'''.

|-
|| '''Slide 2'''

'''Learning Objectives'''
|| In this tutorial, we will learn to,

* Create alias names using:
* space,
* AS keyword,
* underscore and
* double quotes.

|-
|| '''Slide 3'''

'''System Requirements'''

'''To record this tutorial, I am using'''

• '''Ubuntu 24.04 LTS,'''

• '''PostgreSQL version 18.1, and'''

• '''pgAdmin 4 version 9.11.'''

|| To record this tutorial, I am using the following setup.

|-
|| '''Slide 4'''

'''Pre-requisites'''
|| To follow this tutorial,
* Learners should have '''PostgreSQL''' and '''pgAdmin 4'''
* For the prerequisite '''PostgreSQL''' tutorials, please visit this website.

|-
|| '''Slide 5'''

'''Code Files'''

The following code file is required to practice this tutorial:

• '''an-commands.txt'''

This file is provided in the Code Files link on this tutorial page.

|| The following code file is required to practice this tutorial.

This file is provided in the '''Code Files''' link of this tutorial page.

Please download and extract the file.

|-
||
|| Let us get started.

|-
|| '''Point to the interface.'''
|| I have opened the '''pgAdmin 4''' interface and connected to the localhost server.

|-
|| Display the students table

Type:

'''SELECT * FROM students;'''

|| Let us display the student table.
Type the following query.
Click on the Execute query icon to run the query.
Notice the student table with all the records.

|-
|| '''Slide 5'''
'''What is an Alias?'''
|| Let us see what an alias is?
An alias is a temporary name given to a column or a table.
It improves the readability of the output.
Alias names exist only for the duration of the query.

|-
|| '''Type:'''

'''SELECT studentname fullname FROM students;'''

|| Let us create an alias using a '''space'''.
Type the following query.

|-
|| Highlight '''studentname fullname'''

|| Here, '''studentname''' is the original column name.

'''fullname''' is the alias given using a '''space'''.

|-
|| Click on the execute query button.
|| Click on the '''Execute query''' button to execute the query.
The output displays the '''studentname''' column with the new heading '''fullname'''.

|-
|| '''Type:'''
'''SELECT city AS studentcity FROM students;'''
|| Now let us create an '''alias''' using the '''as''' keyword.
Type the following query.

|-
|| '''Highlight city AS studentcity'''
|| Here, '''AS''' keyword explicitly assigns the '''alias''' name '''studentcity''' to the '''city''' column.

|-
|| Click the '''Execute query''' icon
Point to the output..
|| Let us execute the query.
Observe the output.
The original column name '''city''' is now replaced with '''studentcity'''.

|-
|| Type:

'''SELECT studentid AS student_id FROM students;'''
|| Now let us use an '''underscore''' in the '''alias''' name.
Type the following query.

|-
|| Highlight '''student_id'''

|| Here, '''student_id''' is the '''alias''' name.
The '''underscore''' is used to separate words for better readability.

|-
|| Click the Execute query icon.
|| Let us execute the query.
The column heading is now changed to '''student_id'''.

|-
|| '''Highlight: SELECT studentid AS student_id FROM students;'''
'''Type:'''

'''SELECT studentid student_id FROM students;'''
|| Here the '''AS''' keyword is optional, it means the query runs with or without '''AS'''.
Let us type this query and execute it.
Both the queries give the same result.

|-
|| Type: '''SELECT cgpa AS "Student cgpa" FROM students;'''
|| Now let us create an alias using double quotes. Type the following query.

|-
|| Highlight '''"student cgpa"'''
|| Here double quotes allow us to include spaces in the '''alias''' name.
Double quotes are required when the '''alias''' contains spaces.

|-
|| Click the '''Execute query''' icon.
|| Let us execute the query.
The column heading '''cpga''' is now changed to '''student cgpa'''.

|-
|| Type:

'''SELECT * FROM students;'''
|| '''Alias''' names are temporary and do not modify the actual table structure.
To check, let us type this query and execute it.
Observe that the actual table is not modified.

|-
|| '''Slide 7'''

'''Summary'''

In this tutorial we have learnt to,

• Create alias names using:

• space,

• AS keyword,

• underscore and

• double quotes.

|| With this we come to the end of this tutorial.

Let us summarise.

|-
|| '''Slide 8'''

'''Assignment'''

'''As an assignment,'''

Create alias names for gender and date of birth columns using different methods of aliasing.
SELECT * FROM students;

|| As an assignment, please do the following.

|-
|| '''Slide 9'''
'''Thank You'''

This Spoken Tutorial is brought to you by EduPyramids Educational Private Limited, SINE IIT Bombay.

|| Thank you.

|}

PostgreSQL-Database/C2/Alias-Names/English

2026-04-27T10:06:04Z

ANJALISATDIVE:

Title of the Script: Alias Names

Author: EduPyramids

Keywords: PostgreSQL, pgAdmin 4, alias, AS keyword, space, underscore, double quotes, video tutorial, EduPyramids.

{| border=1
|-
! width="35%" | '''Visual Cue'''
! width="65%" | '''Narration'''

|-
|| '''Title Slide 1'''
|| Welcome to this Spoken Tutorial on '''Alias Names''' in '''PostgreSQL'''.

|-
|| '''Slide 2'''

'''Learning Objectives'''
|| In this tutorial, we will learn to,

* Create alias names using:
* space,
* AS keyword,
* underscore and
* double quotes.

|-
|| '''Slide 3'''

'''System Requirements'''

'''To record this tutorial, I am using'''

• '''Ubuntu 24.04 LTS,'''

• '''PostgreSQL version 18.1, and'''

• '''pgAdmin 4 version 9.11.'''

|| To record this tutorial, I am using the following setup.

|-
|| '''Slide 4'''

'''Pre-requisites'''
|| To follow this tutorial,
* Learners should have '''PostgreSQL''' and '''pgAdmin 4'''
* For the prerequisite '''PostgreSQL''' tutorials, please visit this website.

|-
|| '''Slide 5'''

'''Code Files'''

The following code file is required to practice this tutorial:

• '''an-commands.txt'''

This file is provided in the Code Files link on this tutorial page.

|| The following code file is required to practice this tutorial.

This file is provided in the '''Code Files''' link of this tutorial page.

Please download and extract the file.

|-
||
|| Let us get started.

|-
|| '''Point to the interface.'''
|| I have opened the '''pgAdmin 4''' interface and connected to the localhost server.

|-
|| Display the students table

Type:

'''SELECT * FROM students;'''

|| Let us display the student table.
Type the following query.
Click on the Execute query icon to run the query.
Notice the student table with all the records.

|-
|| '''Slide 5'''
'''What is an Alias?'''
|| Let us see what an alias is?
An alias is a temporary name given to a column or a table.
It improves the readability of the output.
Alias names exist only for the duration of the query.

|-
|| '''Type:'''

'''SELECT studentname fullname FROM students;'''

|| Let us create an alias using a '''space'''.
Type the following query.

|-
|| Highlight '''studentname fullname'''

|| Here, '''studentname''' is the original column name.

'''fullname''' is the alias given using a '''space'''.

|-
|| Click on the execute query button.
|| Click on the '''Execute query''' button to execute the query.
The output displays the '''studentname''' column with the new heading '''fullname'''.

|-
|| '''Type:'''
'''SELECT city AS studentcity FROM students;'''
|| Now let us create an '''alias''' using the '''as''' keyword.
Type the following query.

|-
|| '''Highlight city AS studentcity'''
|| Here, '''AS''' keyword explicitly assigns the '''alias''' name '''studentcity''' to the '''city''' column.

|-
|| Click the '''Execute query''' icon
Point to the output..
|| Let us execute the query.
Observe the output.
The original column name '''city''' is now replaced with '''studentcity'''.

|-
|| Type:

'''SELECT studentid AS student_id FROM students;'''
|| Now let us use an '''underscore''' in the '''alias''' name.
Type the following query.

|-
|| Highlight '''student_id'''

|| Here, '''student_id''' is the '''alias''' name.
The '''underscore''' is used to separate words for better readability.

|-
|| Click the Execute query icon.
|| Let us execute the query.
The column heading is now changed to '''student_id'''.

|-
|| '''Highlight: SELECT studentid AS student_id FROM students;'''
'''Type:'''

'''SELECT studentid student_id FROM students;'''
|| Here the '''AS''' keyword is optional, it means the query runs with or without '''AS'''.
Let us type this query and execute it.
Both the queries give the same result.

|-
|| Type: '''SELECT cgpa AS "Student cgpa" FROM students;'''
|| Now let us create an alias using double quotes. Type the following query.

|-
|| Highlight '''"student cgpa"'''
|| Here double quotes allow us to include spaces in the '''alias''' name.
Double quotes are required when the '''alias''' contains spaces.

|-
|| Click the '''Execute query''' icon.
|| Let us execute the query.
The column heading '''cpga''' is now changed to '''student cgpa'''.

|-
|| Type:

'''SELECT * FROM students;'''
|| '''Alias''' names are temporary and do not modify the actual table structure.
To check, let us type this query and execute it.
Observe that the actual table is not modified.

|-
|| '''Slide 7'''
'''Summary'''
In this tutorial we have learnt to,

• Create alias names using:

• space,

• AS keyword,

• underscore and

• double quotes.

|| With this we come to the end of this tutorial.

Let us summarise.

|-
|| '''Slide 8'''
'''Assignment'''

'''As an assignment,'''

Create alias names for gender and date of birth columns using different methods of aliasing.
SELECT * FROM students;

|| As an assignment, please do the following.

|-
|| '''Slide 9'''
'''Thank You'''

This Spoken Tutorial is brought to you by EduPyramids Educational Private Limited, SINE IIT Bombay.

|| Thank you.

|}

PostgreSQL-Database/C2/Alias-Names/English

2026-04-27T10:05:41Z

ANJALISATDIVE:

Title of the Script: Alias Names

Author: EduPyramids

Keywords: PostgreSQL, pgAdmin 4, alias, AS keyword, space, underscore, double quotes, video tutorial, EduPyramids.

{| border=1
|-
! width="35%" | '''Visual Cue'''
! width="65%" | '''Narration'''

|-
|| '''Title Slide 1'''
|| Welcome to this Spoken Tutorial on '''Alias Names''' in '''PostgreSQL'''.

|-
|| '''Slide 2'''

'''Learning Objectives'''
|| In this tutorial, we will learn to,

* Create alias names using:
* space,
* AS keyword,
* underscore and
* double quotes.

|-
|| '''Slide 3'''

'''System Requirements'''

'''To record this tutorial, I am using'''

• '''Ubuntu 24.04 LTS,'''

• '''PostgreSQL version 18.1, and'''

• '''pgAdmin 4 version 9.11.'''

|| To record this tutorial, I am using the following setup.

|-
|| '''Slide 4'''

'''Pre-requisites'''
|| To follow this tutorial,
* Learners should have '''PostgreSQL''' and '''pgAdmin 4'''
* For the prerequisite '''PostgreSQL''' tutorials, please visit this website.

|-
|| '''Slide 5'''

'''Code Files'''
The following code file is required to practice this tutorial:

• '''an-commands.txt'''

This file is provided in the Code Files link on this tutorial page.

|| The following code file is required to practice this tutorial.

This file is provided in the '''Code Files''' link of this tutorial page.

Please download and extract the file.

|-
||
|| Let us get started.

|-
|| '''Point to the interface.'''
|| I have opened the '''pgAdmin 4''' interface and connected to the localhost server.

|-
|| Display the students table

Type:

'''SELECT * FROM students;'''

|| Let us display the student table.
Type the following query.
Click on the Execute query icon to run the query.
Notice the student table with all the records.

|-
|| '''Slide 5'''
'''What is an Alias?'''
|| Let us see what an alias is?
An alias is a temporary name given to a column or a table.
It improves the readability of the output.
Alias names exist only for the duration of the query.

|-
|| '''Type:'''

'''SELECT studentname fullname FROM students;'''

|| Let us create an alias using a '''space'''.
Type the following query.

|-
|| Highlight '''studentname fullname'''

|| Here, '''studentname''' is the original column name.

'''fullname''' is the alias given using a '''space'''.

|-
|| Click on the execute query button.
|| Click on the '''Execute query''' button to execute the query.
The output displays the '''studentname''' column with the new heading '''fullname'''.

|-
|| '''Type:'''
'''SELECT city AS studentcity FROM students;'''
|| Now let us create an '''alias''' using the '''as''' keyword.
Type the following query.

|-
|| '''Highlight city AS studentcity'''
|| Here, '''AS''' keyword explicitly assigns the '''alias''' name '''studentcity''' to the '''city''' column.

|-
|| Click the '''Execute query''' icon
Point to the output..
|| Let us execute the query.
Observe the output.
The original column name '''city''' is now replaced with '''studentcity'''.

|-
|| Type:

'''SELECT studentid AS student_id FROM students;'''
|| Now let us use an '''underscore''' in the '''alias''' name.
Type the following query.

|-
|| Highlight '''student_id'''

|| Here, '''student_id''' is the '''alias''' name.
The '''underscore''' is used to separate words for better readability.

|-
|| Click the Execute query icon.
|| Let us execute the query.
The column heading is now changed to '''student_id'''.

|-
|| '''Highlight: SELECT studentid AS student_id FROM students;'''
'''Type:'''

'''SELECT studentid student_id FROM students;'''
|| Here the '''AS''' keyword is optional, it means the query runs with or without '''AS'''.
Let us type this query and execute it.
Both the queries give the same result.

|-
|| Type: '''SELECT cgpa AS "Student cgpa" FROM students;'''
|| Now let us create an alias using double quotes. Type the following query.

|-
|| Highlight '''"student cgpa"'''
|| Here double quotes allow us to include spaces in the '''alias''' name.
Double quotes are required when the '''alias''' contains spaces.

|-
|| Click the '''Execute query''' icon.
|| Let us execute the query.
The column heading '''cpga''' is now changed to '''student cgpa'''.

|-
|| Type:

'''SELECT * FROM students;'''
|| '''Alias''' names are temporary and do not modify the actual table structure.
To check, let us type this query and execute it.
Observe that the actual table is not modified.

|-
|| '''Slide 7'''
'''Summary'''
In this tutorial we have learnt to,

• Create alias names using:

• space,

• AS keyword,

• underscore and

• double quotes.

|| With this we come to the end of this tutorial.

Let us summarise.

|-
|| '''Slide 8'''
'''Assignment'''

'''As an assignment,'''

Create alias names for gender and date of birth columns using different methods of aliasing.
SELECT * FROM students;

|| As an assignment, please do the following.

|-
|| '''Slide 9'''
'''Thank You'''

This Spoken Tutorial is brought to you by EduPyramids Educational Private Limited, SINE IIT Bombay.

|| Thank you.

|}

PostgreSQL-Database/C2/Alias-Names/English

2026-04-27T09:54:11Z

ANJALISATDIVE: Created page with "Title of the Script: Alias Names Author: EduPyramids Keywords: PostgreSQL, pgAdmin 4, alias, AS keyword, space, underscore, double quotes, video tutorial, EduPyramids. {|..."

Title of the Script: Alias Names

Author: EduPyramids

Keywords: PostgreSQL, pgAdmin 4, alias, AS keyword, space, underscore, double quotes, video tutorial, EduPyramids.

{| border=1
|-
! width="35%" | '''Visual Cue'''
! width="65%" | '''Narration'''

|-
|| '''Title Slide 1'''
|| Welcome to this Spoken Tutorial on '''Alias Names''' in '''PostgreSQL'''.

|-
|| '''Slide 2'''
'''Learning Objectives'''
|| In this tutorial, we will learn to,

* Create alias names using:
* space,
* AS keyword,
* underscore and
* double quotes.

|-
|| '''Slide 3'''
'''System Requirements'''

'''To record this tutorial, I am using'''

• '''Ubuntu 24.04 LTS,'''

• '''PostgreSQL version 18.1, and'''

• '''pgAdmin 4 version 9.11.'''

|| To record this tutorial, I am using the following setup.

|-
|| '''Slide 4'''
'''Pre-requisites'''
|| To follow this tutorial,
* Learners should have '''PostgreSQL''' and '''pgAdmin 4'''
* For the prerequisite '''PostgreSQL''' tutorials, please visit this website.

|-
|| '''Slide 5'''
'''Code Files'''
The following code file is required to practice this tutorial:

• '''an-commands.txt'''

This file is provided in the Code Files link on this tutorial page.

|| The following code file is required to practice this tutorial.

This file is provided in the '''Code Files''' link of this tutorial page.

Please download and extract the file.

|-
||
|| Let us get started.

|-
|| '''Point to the interface.'''
|| I have opened the '''pgAdmin 4''' interface and connected to the localhost server.

|-
|| Display the students table
Type: **SELECT * FROM students;**
|| Let us display the student table.
Type the following query.
Click on the Execute query icon to run the query.
Notice the student table with all the records.

|-
|| '''Slide 5'''
'''What is an Alias?'''
|| Let us see what an alias is?
An alias is a temporary name given to a column or a table.
It improves the readability of the output.
Alias names exist only for the duration of the query.

|-
|| '''Type:'''
'''SELECT studentname fullname FROM students;'''
|| Let us create an alias using a '''space'''.
Type the following query.

|-
|| Highlight '''studentname fullname'''
|| Here, '''studentname''' is the original column name.
'''fullname''' is the alias given using a '''space'''.

|-
|| Click on the execute query button.
|| Click on the '''Execute query''' button to execute the query.
The output displays the '''studentname''' column with the new heading '''fullname'''.

|-
|| '''Type:'''
'''SELECT city AS studentcity FROM students;'''
|| Now let us create an '''alias''' using the '''as''' keyword.
Type the following query.

|-
|| '''Highlight city AS studentcity'''
|| Here, '''AS''' keyword explicitly assigns the '''alias''' name '''studentcity''' to the '''city''' column.

|-
|| Click the '''Execute query''' icon
Point to the output..
|| Let us execute the query.
Observe the output.
The original column name '''city''' is now replaced with '''studentcity'''.

|-
|| Type: '''SELECT studentid AS student_id FROM students;'''
|| Now let us use an '''underscore''' in the '''alias''' name.
Type the following query.

|-
|| Highlight '''student_id'''
|| Here, '''student_id''' is the '''alias''' name.
The '''underscore''' is used to separate words for better readability.

|-
|| Click the Execute query icon.
|| Let us execute the query.
The column heading is now changed to '''student_id'''.

|-
|| '''Highlight: SELECT studentid AS student_id FROM students;'''
'''Type:'''
'''SELECT studentid student_id FROM students;'''
|| Here the '''AS''' keyword is optional, it means the query runs with or without '''AS'''.
Let us type this query and execute it.
Both the queries give the same result.

|-
|| Type: '''SELECT cgpa AS "Student cgpa" FROM students;'''
|| Now let us create an alias using double quotes. Type the following query.

|-
|| Highlight '''"student cgpa"'''
|| Here double quotes allow us to include spaces in the '''alias''' name.
Double quotes are required when the '''alias''' contains spaces.

|-
|| Click the '''Execute query''' icon.
|| Let us execute the query.
The column heading '''cpga''' is now changed to '''student cgpa'''.

|-
|| Type: **SELECT * FROM students;**
|| '''Alias''' names are temporary and do not modify the actual table structure.
To check, let us type this query and execute it.
Observe that the actual table is not modified.

|-
|| '''Slide 7'''
'''Summary'''
In this tutorial we have learnt to,

• Create alias names using:

• space,

• AS keyword,

• underscore and

• double quotes.

|| With this we come to the end of this tutorial.

Let us summarise.

|-
|| '''Slide 8'''
'''Assignment'''

'''As an assignment,'''

Create alias names for gender and date of birth columns using different methods of aliasing.
SELECT * FROM students;

|| As an assignment, please do the following.

|-
|| '''Slide 9'''
'''Thank You'''

This Spoken Tutorial is brought to you by EduPyramids Educational Private Limited, SINE IIT Bombay.

|| Thank you.

|}

PostgreSQL-Database/C2/Aggregate-Functions/English

2026-04-23T06:41:53Z

ANJALISATDIVE: Created page with " '''Title of the script: Aggregate Functions''' '''Authors:''' EduPyramids.''' '''Keywords:''' PostgreSQL, pgAdmin 4, Aggregate Functions, COUNT, AVG(average), SUM, MIN, MA..."

'''Title of the script: Aggregate Functions'''

'''Authors:''' EduPyramids.'''

'''Keywords:''' PostgreSQL, pgAdmin 4, Aggregate Functions, COUNT, AVG(average), SUM, MIN, MAX, Null values, COUNT asterisk, ROUND value, EduPyramids, Video Tutorial.

{| border=1
|-
! width="35%" | '''Visual Cue'''
! width="65%" | '''Narration'''

|-
|| '''Title Slide'''
|| Welcome to this Spoken Tutorial on '''Aggregate Functions'''.

|-
|| '''Slide 2'''
'''Learning Objectives'''
|| In this tutorial, we will learn about
* '''Aggregate Functions'''

|-
|| '''Slide 3'''
'''System Requirements'''
• '''Ubuntu 24.04 LTS'''
• '''PostgreSQL version 18.1''', and
• '''PgAdmin 4 version 9.11'''.
|| To record this tutorial, I am using the following setup.

|-
|| '''Slide 4'''
'''Pre-requisites'''
|| To follow this tutorial,
* Learners should have '''Postgres Q L''' and '''p g Admin 4'''
* For the prerequisite '''Postgres Q L''' tutorials, please visit this website.

|-
|| '''Slide 5'''
'''Code Files'''
The following code file is required to practice this tutorial:
'''af-commands.txt'''
This file is provided in the Code Files link on this tutorial page.
|| The following code file is required to practice this tutorial.
This file is provided in the Code Files link of this tutorial page.
Please download and extract the file.

|-
||
|| Let us get started.

|-
|| '''Slide 6'''
'''Aggregate Functions'''
|| '''Aggregate''' functions perform calculations on multiple rows and return a single value.
Some common aggregate functions are '''COUNT''', '''average''', '''SUM''', '''MIN''', and '''MAX'''.

|-
||
|| I have opened the '''p g Admin 4''' interface and connected to the localhost server.
Let us learn these functions using query examples.
This is the student table.
First, we will learn about the '''COUNT''' function.

|-
|| '''Type:'''
SELECT COUNT(*) FROM students;'''
'''Highlight COUNT(*)
Click on the Execute Query button.
|| Let us type the following query.
The '''COUNT''' asterisk function returns the total number of rows in the table.
It counts all records including those containing '''NULL''' values.
Let us execute the query.
This shows the total number of students stored in the table.

|-
||
|| Now we will see how '''COUNT''' works when applied to a specific column.

|-
|| '''Type:'''
'''SELECT COUNT(cgpa) FROM students;'''
'''Highlight:'''
'''COUNT(cgpa)'''
Click on the Execute Query button.
|| Type the query.
The '''COUNT''' function counts the number of not '''NULL''' values in the '''C G P A''' column.
Let us execute the query.

|-
|| '''Cursor on the output.'''
|| The output displays the number of students whose '''C G P A''' value is available.

|-
|| Show both the outputs in different query windows.
|| Here, '''COUNT asterisk''' returns all the rows.
However, the COUNT '''column''' counts only '''not NULL''' values of the specified column.

|-
||
|| Next, we will calculate the average C G P A.

|-
|| '''Type:'''
'''SELECT AVG(cgpa) FROM students;'''
|| Type the following query.

|-
|| '''Highlight:'''
'''AVG(cgpa)'''
|| The '''average''' function calculates the average of all cgpa values.

|-
|| Click on the Execute Query button.
'''Cursor on the output.'''
|| Let us execute the query.
The output displays the average c g p a of all students.

|-
|| Type this command:
'''SELECT ROUND(AVG(cgpa), 2)'''
'''FROM students;'''
Click on the execute button.
|| Observe that, using the average keyword we get insignificant digits after the decimal point.
To reduce the number of insignificant digits after the decimal point, type this query.
ROUND value 2 limits the result to 2 digits after the decimal point.
Execute the query.
The output displays only two digits after the decimal point.

|-
||
|| Now, let us find the highest C G P A.

|-
|| '''Type:'''
'''SELECT MAX(cgpa) FROM students;'''
'''Highlight:'''
MAX(cgpa)
|| Type the following query.
The '''MAX''' function returns the largest value in the C G P A column.

|-
|| Click on the Execute Query button.
'''Cursor on the output.'''
|| Execute the query.
The result displays the highest C G P A recorded in the students table.

|-
||
|| Next, we will find the lowest C G P A .

|-
|| '''Type:'''
'''SELECT MIN(cgpa) FROM students;'''
'''Highlight:'''
'''MIN(cgpa)'''
|| Type the following query.
'''MIN''' returns the smallest value in the column.

|-
|| Click on the Execute Query button.
'''Cursor on the output.'''
|| Execute the query.
The output displays the lowest C G P A recorded in the students table.

|-
||
|| Finally, we will calculate the total C G P A .

|-
|| '''Type:'''
'''SELECT SUM(cgpa) FROM students;'''
'''Highlight:'''
'''SUM(cgpa)'''
|| Type the following query.
The '''SUM''' function helps us find the total C G P A of all students.

|-
|| Click on the Execute Query button.
'''Cursor on the output.'''
|| Execute the query.
The output displays the sum of all CGPA values.

|-
||
|| With this, we come to the end of this tutorial.

|-
|| '''Slide 10'''
'''Summary'''
In this tutorial, we have learnt about
• '''Aggregate Functions'''
|| Let us summarise.

|-
|| '''Slide 11'''
'''Assignment'''
'''As an Assignment,'''
• Write a query to find how many different cities are present in the students table.
• Calculate the average CGPA of students born after 1996.
• Find the highest CGPA among female students.
• Find the lowest CGPA among male students.
• Calculate the total CGPA of students from each city.
|| We encourage you to do this assignment.

|-
|| '''Slide 12'''
This '''Spoken Tutorial''' is brought to you by '''EduPyramids''' Educational Private Limited SINE IIT Bombay.
'''Thank you'''
|| Thank you.

|}

PostgreSQL-Database/C2/Filtering-and-Sorting-Data/English

2026-02-18T06:25:11Z

ANJALISATDIVE: Created page with "Title of the Script: Filtering and Sorting Data Author: EduPyramids Keywords: PostgreSQL, pgAdmin 4, WHERE, ORDER BY, data filtering, data sorting, ascending order, descen..."

Title of the Script: Filtering and Sorting Data

Author: EduPyramids

Keywords: PostgreSQL, pgAdmin 4, WHERE, ORDER BY, data filtering, data sorting, ascending order, descending order, EduPyramids, video tutorial.

LaTeX/C3/Frame-Pages-and-Colour-Text-Boxes/English

2025-12-01T07:12:47Z

ANJALISATDIVE: Created page with "Title of the Script: '''Frame Pages and Colour Text Boxes''' Author: Ambika Vanchinathan Keywords: LaTeX, fancybox, fcolorbox, fancypage, thisfancybox, shadowsize, shadowbox..."

Title of the Script: '''Frame Pages and Colour Text Boxes'''

Author: Ambika Vanchinathan

Keywords: LaTeX, fancybox, fcolorbox, fancypage, thisfancybox, shadowsize, shadowbox, ovalbox,fboxsep, video tutorial.

'''Pre-requisite: Framing Floats and Lists'''

{| border =1
|-
|| '''Visual Cue'''
|| '''Narration'''
|-
|| '''Slide 1'''

'''Title Slide'''
| Welcome to the spoken tutorial on '''Frame Pages and Colour Text Boxes.'''
|-
|
'''Slide 2'''

'''Learning Objectives'''
|
In this tutorial, we will learn to

*Add coloured boxes with fcolorbox
*Customize box spacing
*Frame pages with different boxes
|-
|
'''Slide 3'''

'''System Requirements'''

To record this tutorial, I am using:

Linux Mint OS version 21.3
Texworks version 0.6.6
|

To record this tutorial I am using the following setup.
|-
|
'''Slide 4'''

'''Pre-requisites'''
| To follow this tutorial, learners should have basic knowledge of LaTeX.
|-
|
'''Slide 5'''

'''Code files'''

The following code files are required to practice this tutorial:

1. fancybox-eg.tex

2. Free-Stock-Photos.jpg

These files are provided in the Code Files link of this tutorial page.
|
The following code files are required to practice this tutorial.

These files are provided in the '''Code Files''' link of this tutorial page.
|-
|
Comment line 139

Click the green Typeset button and show the output
|
Let us get started

Comment line 139.

Click the green '''Typeset''' button to run the code.

Here is the output.

We see text in a two coloured box.
|-
|
'''Highlight:'''

line 141

And point to the box in the output
|
'''\fcolorbox{red}{green}''' specifies the frame and filler colours of the box.

Here the '''frame''' is red and the '''filler''' is green.

Note that we have loaded the '''color''' package.
|-
|
'''Highlight'''

line 147

And point to the box in the output

Zoom the output to show the box clearly.
| Here the '''frame''' is green and the '''filler''' is white.
|-
|
Comment line 20

Click the green Typeset button and show the output

'''Show all the boxes from top to bottom.'''
|
Comment line 20.

Run the code.

Here is the output.

Observe that all the boxes have no space around the text.
|-
|
Uncomment line 20

Click the green Typeset button and show the output
|
Let us fill the box with some space around the text.

Uncomment line 20.

Run the code.

Here is the output.

Now there is space around the text inside all the boxes.
|-
|
'''Highlight :'''

line 20
| '''\fboxsep''' command specifies the gap amount between the text and the box.
|-
|
Uncomment line 7,8,9,10

Click the green Typeset button and show the output

Point to the shadow box of the complete first page
|
Uncomment lines 7, 8, 9 and 10.

Run the code.

Here is the output.

The complete first page is in a shadow box.
|-
|
'''Highlight :'''

lines 7 to 10

Point to the first page with shadow.
|
'''\thisfancypage''' command, defines the shadow for the current page only.

The first page is now shown within a shadow box.
|-
|
Uncomment line 13, 14, 15, 16

Click the green Typeset button and show the output

Point to the shadow box of all the pages in output
|
Uncomment lines 13, 14, 15, and 16.

Run the code.

Here is the output

All pages are displayed inside a shadow box.
|-
|
'''Highlight :'''

lines 13 to 16
|
'''\fancypage''' defines a shadow box for

all the pages.
|-
|
Comment line 7,8,9,10 and 16

Uncomment line 17

Click the green Typeset button and show the output

Point to the '''oval box''' in the output
|
Comment lines 7, 8, 9, 10 and 16.

Uncomment line 17.

Run the code

Here is the output

All the pages are enclosed within an oval box.
|-
|
'''Highlight:'''

lines 13 to 17
| '''\fancypage''' command defines all the pages to be in '''oval box''' here
|-
|
'''Slide 6'''

'''Summary'''

In this tutorial, we have learnt to

Add coloured boxes with '''fcolorbox'''
Customize box spacing
Frame pages with different boxes
|
With this we come to the end of this tutorial.

Let us summarise.
|-
|
'''Slide 7'''

'''Assignment'''
|
As an '''Assignment'''

Create question and answer with

answer enclosed in a blue box
|-
|
'''Slide 8'''

'''Thank you'''
| Thank you for joining
|}

LaTeX/C3/Adding-Line-Numbers-in-LaTeX/English

2025-12-01T06:21:58Z

ANJALISATDIVE:

Title of the Script: '''Adding line numbers in LaTeX'''

Author: Ambika Vanchinathan

Keywords: LaTeX, lineno package, line numbers, math environments, linelabel, modulolinenumbers, mathlines, video tutorial.

'''Pre-requisite: Writing Style Files in LaTeX'''

{| border =1
|-
|| '''Visual Cue'''
|| '''Narration'''
|-
|| '''Slide 1'''

'''Title Slide'''
|| Welcome to the spoken tutorial on '''Adding Line Numbers '''in '''LaTeX.'''
|-
|| '''Slide 2'''

'''Learning Objectives'''

|| In this tutorial, we will learn to
* Add line numbers to a document
* Enable line numbers in math environment
* Label and cross reference a line number
* Enable line numbering in steps and change its font size
|-
|| '''Slide 3'''

'''System Requirements'''

* Linux Mint OS version 21.3
* TeXworks Editor version 0.6.6

|| To record this tutorial, I am using the following setup.
|-
|| '''Slide 4'''

'''Pre-requisites'''

|| To follow this tutorial, learners should have basic knowledge of ''' LaTeX'''.
|-
|| '''Slide 5: '''

'''Code files'''

The following code files are required to practice this tutorial:

1. lineno-eg.tex

These files are provided in the Code Files link of this tutorial page.
|| The following code file is required to practice this tutorial.

This file is provided in the '''Code Files''' link of this tutorial page.
|-
||
|| Let us get started.
|-
|| '''Highlight:'''

\usepackage{lineno}

|| This command loads the line number package.

It is recommended to keep the installation up to date.

If any issue arises, please update the packages by downloading them from [http://ctan.org/ ctan.org].

|-
|| '''Highlight:'''

\linenumbers

Click the green Typeset button and show the output

|| '''\linenumbers '''command enables printing line numbers on the document.

Click the green '''Typeset''' button to run the code.

Output shows the line numbers printed on all lines except the math equations.

|-
|| Comment line 5

Uncomment line 7

Click the green Typeset button and show the output

|| Comment line 5

Uncomment line 7

Run the code to see the output.

Observe the line numbers in math equations.
|-
|| '''Highlight:'''

line 7
|| Option '''[mathlines, displaymath] '''with''' '''

'''\usepackage{lineno}, '''enables line numbers in math environments.

This applies to both numbered and unnumbered equations.
|-
|| Uncomment lines 23, 49

Click the green Typeset button and show the output

|| Uncomment lines 23 and 49.

Run the code

Here we must compile the code twice.

In the first run we see question marks.

That is, the first run assigns line numbers.

The second run fixes the references.

Like cross-references, line numbers need, two compilations to appear correctly.

Let us compile again.

Here is the output after the second compilation.

Observe the line number and page number reference''' '''at the end of the document.
|-
|| '''Highlight:'''

line 23, 49
|| '''\linelabel '''command labels the line number.

'''\ref '''and '''\pageref '''generate''' '''the line number and page number of a text in a line.
|-
|| Uncomment line 17

Click the Typeset button and show the output
|| Uncomment line 17.

Run the code

Here is the output.

We see that line numbers are printed after every 5 lines.
|-
|| '''Highlight:'''

line 17
|| Command '''\modulolinenumbers[5]'''

enables line numbering in steps of 5.

We can change the number 5 to reset to a required jump.
|-
|| Uncomment line 14

Click the Typeset button and show the output
|| Uncomment the line 14.

Run the code.

Here is the output.
|-
|| '''Highlight:'''

line 14

|| We can see that the font color and size of the line numbers have changed.

The command '''\linenumberfont '''resets, line numbers to red and font to script size.

Note that, we have also loaded the color package to get the colours.
|-
|| '''Slide 6'''

'''Summary'''

In this tutorial, we have learnt to
* Add line numbers to a document
* Enable line numbers in math environment
* Label and cross reference a line
* Enable line numbering in steps and change its font size

|| With this we come to the end of this tutorial.

Let us summarise.

|-
|| '''Slide 7'''

'''Assignment'''
|| As an '''Assignment,'''

Print line numbers after every 3 lines in blue colour.
|-
|| '''Slide 8'''

'''Thank you'''

|| Thank you for joining
|-
|}

LaTeX/C3/Adding-Line-Numbers-in-LaTeX/English

2025-12-01T06:20:47Z

ANJALISATDIVE: Created page with "{| border =1 |- || '''Visual Cue''' || '''Narration''' |- || '''Slide 1''' '''Title Slide''' || Welcome to the spoken tutorial on '''Adding Line Numbers '''in '''LaTeX.'''..."

{| border =1
|-
|| '''Visual Cue'''
|| '''Narration'''
|-
|| '''Slide 1'''

'''Title Slide'''
|| Welcome to the spoken tutorial on '''Adding Line Numbers '''in '''LaTeX.'''
|-
|| '''Slide 2'''

'''Learning Objectives'''

|| In this tutorial, we will learn to
* Add line numbers to a document
* Enable line numbers in math environment
* Label and cross reference a line number
* Enable line numbering in steps and change its font size
|-
|| '''Slide 3'''

'''System Requirements'''

* Linux Mint OS version 21.3
* TeXworks Editor version 0.6.6

|| To record this tutorial, I am using the following setup.
|-
|| '''Slide 4'''

'''Pre-requisites'''

|| To follow this tutorial, learners should have basic knowledge of ''' LaTeX'''.
|-
|| '''Slide 5: '''

'''Code files'''

The following code files are required to practice this tutorial:

1. lineno-eg.tex

These files are provided in the Code Files link of this tutorial page.
|| The following code file is required to practice this tutorial.

This file is provided in the '''Code Files''' link of this tutorial page.
|-
||
|| Let us get started.
|-
|| '''Highlight:'''

\usepackage{lineno}

|| This command loads the line number package.

It is recommended to keep the installation up to date.

If any issue arises, please update the packages by downloading them from [http://ctan.org/ ctan.org].

|-
|| '''Highlight:'''

\linenumbers

Click the green Typeset button and show the output

|| '''\linenumbers '''command enables printing line numbers on the document.

Click the green '''Typeset''' button to run the code.

Output shows the line numbers printed on all lines except the math equations.

|-
|| Comment line 5

Uncomment line 7

Click the green Typeset button and show the output

|| Comment line 5

Uncomment line 7

Run the code to see the output.

Observe the line numbers in math equations.
|-
|| '''Highlight:'''

line 7
|| Option '''[mathlines, displaymath] '''with''' '''

'''\usepackage{lineno}, '''enables line numbers in math environments.

This applies to both numbered and unnumbered equations.
|-
|| Uncomment lines 23, 49

Click the green Typeset button and show the output

|| Uncomment lines 23 and 49.

Run the code

Here we must compile the code twice.

In the first run we see question marks.

That is, the first run assigns line numbers.

The second run fixes the references.

Like cross-references, line numbers need, two compilations to appear correctly.

Let us compile again.

Here is the output after the second compilation.

Observe the line number and page number reference''' '''at the end of the document.
|-
|| '''Highlight:'''

line 23, 49
|| '''\linelabel '''command labels the line number.

'''\ref '''and '''\pageref '''generate''' '''the line number and page number of a text in a line.
|-
|| Uncomment line 17

Click the Typeset button and show the output
|| Uncomment line 17.

Run the code

Here is the output.

We see that line numbers are printed after every 5 lines.
|-
|| '''Highlight:'''

line 17
|| Command '''\modulolinenumbers[5]'''

enables line numbering in steps of 5.

We can change the number 5 to reset to a required jump.
|-
|| Uncomment line 14

Click the Typeset button and show the output
|| Uncomment the line 14.

Run the code.

Here is the output.
|-
|| '''Highlight:'''

line 14

|| We can see that the font color and size of the line numbers have changed.

The command '''\linenumberfont '''resets, line numbers to red and font to script size.

Note that, we have also loaded the color package to get the colours.
|-
|| '''Slide 6'''

'''Summary'''

In this tutorial, we have learnt to
* Add line numbers to a document
* Enable line numbers in math environment
* Label and cross reference a line
* Enable line numbering in steps and change its font size

|| With this we come to the end of this tutorial.

Let us summarise.

|-
|| '''Slide 7'''

'''Assignment'''
|| As an '''Assignment,'''

Print line numbers after every 3 lines in blue colour.
|-
|| '''Slide 8'''

'''Thank you'''

|| Thank you for joining
|-
|}