Sandhya.np14 at 11:13, 5 February 2019

2019-02-05T11:13:32Z

Sandhya.np14 at 10:15, 5 February 2019

2019-02-05T10:15:13Z

PoojaMoolya: Created page with " {| border=1 | '''Time''' | '''Narration''' |- | 00:01 | Welcome to the''' Spoken Tutorial''' on''' Select statement.''' |- | 00:06 | In this tutorial we will learn abo..."

2019-01-30T11:02:17Z

Created page with " {| border=1 | '''Time''' | '''Narration''' |- | 00:01 | Welcome to the''' Spoken Tutorial''' on''' Select statement.''' |- | 00:06 | In this tutorial we will learn abo..."

New page

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

|-
| 00:01
| Welcome to the''' Spoken Tutorial''' on''' Select statement.'''

|-
| 00:06
| In this tutorial we will learn about

Basic '''Select statement'''

|-
| 00:12
| '''Select''' with '''WHERE clause'''

|-
| 00:15
| '''Select''' with '''relational operators'''

|-
| 00:18
| '''Select''' with '''logical operators'''

|-
| 00:21
| '''Alias''' for column names

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

'''Ubuntu Linux 14.04''' operating system

|-
| 00:31
| '''PostgreSQL 9.3.x'''

and '''pgAdmin 1.18'''

|-
| 00:39
| To follow this tutorial, you should have

Basic understanding of '''database''' and '''tables'''.

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

|-
| 00:53
| Let us open '''pgAdmin'''.

|-
| 00:56
| Right-click on the '''students table''' and select '''View Data''' and then '''View All Rows.'''

|-
| 01:04
| We can see the '''students table''' with the 4 records that we inserted earlier.

|-
| 01:10
| I'll show how to insert records in the ''' Edit Data''' window.

|-
| 01:19
| Click on the save icon on the top left of the toolbar to save the changes.

|-
| 01:25
| We can insert records either in this '''Edit data '''window or through the '''Insert statement.'''

Close this window.

|-
| 01:33
| I’ll insert some more records for demonstration purpose.

I’ll open the SQL editor window and type the insert statement shown here.

|-
| 01:43
| Note that, I have entered ‘NULL’ value in the '''date of birth''' column for '''student id ‘s012’.'''

|-
| 01:51
| In '''PostgreSQL''', the term '''NULL''' is used to represent a missing value or an unknown value.

|-
| 01:59
| A field with a '''NULL''' value represents no value.

|-
| 02:03
| Execute the query.

Retrieve the data from the table to see the output.

|-
| 02:09
| So far we have inserted 14 records.

|-
| 02:13
| A '''NULL''' value field appears to be blank.

|-
| 02:17
| Null is not the same as a zero value or space value.

|-
| 02:22
| It implies that a database field value has not been stored.

|-
| 02:27
| Pause the tutorial and do this assignment.

Insert 10 records into the '''students table'''.

|-
| 02:35
| Next, we will see about how to use '''SELECT''' statement with many clauses.

|-
|02:41
| The '''SELECT statement '''retrieves data from a '''database'''.

|-
| 02:45
| The data is returned in rows and columns format i.e. in a tabular format.

|-
| 02:51
| It has many clauses that we can combine to form a powerful query.

|-
| 02:57
| The basic syntax of the '''SELECT '''statement is:''' '''

'''Select column-names from table-name;'''

|-
| 03:05
| Now let us learn to write queries using '''Select statements'''.

|-
| 03:10
| Switch back to the '''PgAdmin''' main screen.

|-
| 03:14
| Right-click on '''students''' node, and select '''Scripts''' and then click on '''SELECT script.'''

|-
| 03:21
| This is a simple '''Select statement '''which retrieves data from the '''students table'''.

|-
| 03:26
| You can specify the column names as shown here or use ''''asterisk'''' to select all the columns.

|-
| 03:34
| I want the details of certain columns only.

So, type the code as shown here with specific column names.

|-
| 03:43
| The '''SQL statement '''will display - the '''student id, student name, city '''and '''cgpa '''columns

from the '''Students table'''.

|-
| 03:54
| Now, let’s execute the query to see the output.

|-
| 03:58
| The expected rows and columns are displayed.

Let’s move ahead.

|-
|04:04
| '''SELECT with WHERE clause''' is used, To filter the rows which match the criteria that is specified and To limit the number of rows

|-
|04:14
| The syntax of this '''statement''' is, '''SELECT column-names FROM table-name WHERE condition'''

|-
| 04:23
| Records that satisfy the specified '''condition '''will be displayed on executing this '''statement.'''

|-
| 04:30
| Let’s switch back to the '''pgAdmin SQL Editor.'''

|-
| 04:34
| Type, '''SELECT ''' studentid, studentname, city, cgpa''' from students where city ='Mumbai'.'''

|-
| 04:43
| Now, execute this query.

|-
| 04:46
| It will retrieve the rows from the '''students table '''and display students from '''Mumbai''' city.

'''WHERE''' is followed by a condition that returns either '''true''' or '''false'''.

|-
| 04:57
| Here the '''City''' is the column name and ''''Mumbai'''' is the value to filter.

|-
| 05:03
| Now let’s change the city value to ' '''Bangalore'''' and execute the query.

We can see that there are no records retrieved.

|-
| 05:12
| This is because the '''condition '''returns '''false '''on execution.

|-
| 05:17
| Which means there are no records that match with the given '''condition'''.

|-
| 05:21
| Next, we will see how to use '''comparison '''or '''relational operators '''in the '''WHERE clause.'''

|-
| 05:28
| '''Relational operators '''listed here, can also be used within the '''WHERE clause.'''

|-
| 05:34
| Switch back to the '''pgAdmin SQL Editor.'''

|-
| 05:37
| Type, '''Select studentid, studentname, city, cgpa from students where cgpa > 8.5'''

|-
| 05:49
| Execute the query.

|-
| 05:51
| The output shows details of students who have '''cgpa '''greater than '''8.5'''

|-
| 05:57
| Let us type another query to retrieve the student details who are not from '''Mumbai''' city.

|-
| 06:04
|Note the ''''Not equal to'''' operator.

|-
| 06:07
| It is symbolised by an '''open '''and '''close angular bracket'''.

Execute the query.

|-
| 06:14
| Likewise, we can use '''relational operators '''to filter data depending upon our requirement.

|-
| 06:21
| Next let’s learn how to use '''arithmetic operators''' in the '''Select statement'''.

|-
| 06:27
| When we need to do calculations within '''SQL statements''', we can use '''arithmetic '''expressions.

|-
| 06:33
| '''Arithmetic''' expressions contain column names, arithmetic operators and numeric values.

|-
| 06:40
| '''Arithmetic operators '''will however, not work for '''character data type '''columns.

|-
| 06:47
| Let’s do a simple arithmetic calculation using some '''operators'''.

Clear the screen.

|-
| 06:54
| Type '''Select 2+15+98+45;'''

Execute to see the output.

|-
| 07:03
| Type''' Select 230 divided by 5;'''

Again execute the query to see the output.

|-
| 07:11
| Let us see how to use '''arithmetic operators''' with a column of the '''table'''.

|-
| 07:16
| We have a column called '''cgpa ''' with '''numeric data type'''.

'''CGPA''' stands for '''Cumulative Grade Point Average.'''

|-
| 07:26
| Let us calculate the percentage from '''cgpa''' score.

Switch back to SQL Editor window.

|-
| 07:33
| Type the query as shown here.

What we want to do is multiply the '''cgpa''' with '''9.5 '''to get the actual percentage.

|-
| 07:44
| Here, we are using the '''multiplication operator asterix '''to calculate the percentage.

|-
| 07:50
| Let’s execute the query now.

|-
| 07:53
| Percentage is calculated and displayed in a new column, as shown here.

But there is no specific column name for this column.

|-
| 08:02
| This percentage data is not stored in the '''database'''. It only displays the calculation.

|-
| 08:09
| Now I want to give a name to this column, so that it is more meaningful.

For that, we can use an '''alias '''name.

|-
| 08:18
| An '''alias''' is used to rename a column temporarily.
|-
| 08:23
|It appears as a heading in the output of the query.

|-
| 08:27
|Use column '''alias''' if the original column name is not meaningful or

when there is a column name conflict.

|-
| 08:35
| Here add the text as '''Percentage '''after the calculation column.

Execute the query to see the output.

|-
| 08:44
| We can also specify in capital letters as '''“PERCENTAGE” '''within double quotes.

|-
| 08:51
| Please note, Column names which are mixed-case or uppercase have to be within double quotes in '''postgresql'''.

|-
| 08:59
| Execute the query.

|-
|09:01
| We can see the column name has changed to '''PERCENTAGE''' in capital letters.

|-
| 09:07
| Next we will see how to use '''logical operators''' within the '''Select statement.'''

|-
| 09:13
| '''Logical operators '''are used where more than one condition needs to be checked.

|-
| 09:18
| Or Using '''logical operators, '''we can combine two or more '''conditions'''.

|-
| 09:25
| The most commonly used '''logical operators''' are:

'''AND''': This joins two or more '''conditions '''and returns results only when all the '''conditions '''are '''true'''.

|-
| 09:38
| '''OR''': This joins two or more '''conditions''' and returns results when at least one of the '''conditions '''is '''true'''.

|-
| 09:47
| '''NOT''': This operator results in the reverse of a '''condition'''.

|-
| 09:53
| Switch back to '''PgAdmin''' interface.

Let us see few examples for logical operators.

|-
| 10:00
| Type the code as shown.

Here we are checking for two '''conditions''' ie. Students who are from '''Pune''' city whose gender is '''Female'''.

|-
| 10:11
| It will return the output when both the '''conditions''' are '''true'''.

Notice how the ''''AND' logical operator '''is written.

|-
| 10:20
| Execute the query to see the output.

|-
| 10:23
| Next, let’s change the ''''AND'''' to ''''OR' logical operator '''and see the output.

|-
| 10:29
| The records are retrieved if either one of the '''conditions''' is '''true'''.

|-
| 10:34
| That is either the student is from Pune city or gender is ‘Female’

|-
| 10:40
| This query will display students who are not from Mumbai or Chennai.

|-
| 10:46
| Here we have used the '''NOT''' and '''AND operators''' together.

|-
| 10:52
| Execute the query and see the output.

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

Let us summarize.

|-
| 11:01
| In this tutorial, we have learnt about

Basic '''Select statement'''

|-
| 11:05
| '''Select''' with '''WHERE clause'''

'''Select''' with '''relational operators'''

|-
| 11:10
| '''Select''' with''' logical operators''' and

'''Alias''' for column names

|-
| 11:15
| As an assignment, write a '''Select statement '''to Display all columns from '''students table '''for the student name ''''Ram''''

|-
| 11:24
| Display '''studentid, studentname, dob '''of students''' '''whose '''cgpa '''is '''greater than '''8 and '''less than '''9.5

|-
| 11:34
| The video at the following link summarises the Spoken Tutorial project.

Please download and watch it.

|-
| 11:40
| The''' Spoken Tutorial Project''' Team

conducts workshops and gives certificates

For more details, please write to us.

|-
| 11:49
| Please post your timed queries in this forum.

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

More information on this mission is available at this link.

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

|}

RDBMS-PostgreSQL/C2/Select-statement/English-timed - Revision history

Sandhya.np14 at 11:13, 5 February 2019

Sandhya.np14 at 10:15, 5 February 2019

PoojaMoolya: Created page with " {| border=1 | '''Time''' | '''Narration''' |- | 00:01 | Welcome to the''' Spoken Tutorial''' on''' Select statement.''' |- | 00:06 | In this tutorial we will learn abo..."