Difference between revisions of "RDBMS-PostgreSQL/C2/Select-statement/English-timed"

From Script | Spoken-Tutorial
Jump to: navigation, search
 
Line 182: Line 182:
 
|-  
 
|-  
 
| 03:58
 
| 03:58
−
| The expected rows and columns are displayed.  
+
| The expected '''row'''s and '''column'''s are displayed.  
  
 
Let’s move ahead.  
 
Let’s move ahead.  
Line 222: Line 222:
 
|-  
 
|-  
 
| 05:03
 
| 05:03
−
| Now, let’s change the city value to ' '''Bangalore'''' and execute the query.  
+
| Now, let’s change the city value to ''''Bangalore'''' and execute the query.  
  
 
We can see that there are no records retrieved.  
 
We can see that there are no records retrieved.  
Line 236: Line 236:
 
|-  
 
|-  
 
| 05:21
 
| 05:21
−
| Next, we will see how to use '''comparison '''or '''relational operators '''in the '''WHERE clause.'''  
+
| Next, we will see how to use comparison or '''relational operators '''in the '''WHERE''' clause.
  
 
|-  
 
|-  
 
| 05:28
 
| 05:28
−
| '''Relational operators '''listed here, can also be used within the '''WHERE clause.'''  
+
| '''Relational operators '''listed here, can also be used within the '''WHERE''' clause.
  
 
|-  
 
|-  
Line 248: Line 248:
 
|-  
 
|-  
 
| 05:37
 
| 05:37
−
| Type, '''Select studentid, studentname, city, cgpa from students where cgpa > 8.5'''  
+
| Type: '''Select studentid, studentname, city, cgpa from students where cgpa > 8.5'''  
  
 
|-  
 
|-  
Line 268: Line 268:
 
|-  
 
|-  
 
| 06:07
 
| 06:07
−
| It is symbolised by an '''open '''and '''close angular bracket'''.  
+
| It is symbolized by an open and close angular bracket.  
  
 
Execute the query.  
 
Execute the query.  
Line 278: Line 278:
 
|-  
 
|-  
 
| 06:21
 
| 06:21
−
| Next let’s learn how to use '''arithmetic operators''' in the '''Select statement'''.  
+
| Next let’s learn how to use arithmetic '''operators''' in the '''Select''' statement.  
  
 
|-  
 
|-  
 
| 06:27
 
| 06:27
−
|  When we need to do calculations within '''SQL statements''', we can use '''arithmetic '''expressions.  
+
|  When we need to do calculations within '''SQL statements''', we can use arithmetic expressions.  
  
 
|-  
 
|-  
 
| 06:33
 
| 06:33
−
|  '''Arithmetic''' expressions contain column names, arithmetic operators and numeric values.  
+
|  Arithmetic expressions contain '''column''' names, arithmetic operators and numeric values.  
  
 
|-  
 
|-  
 
| 06:40
 
| 06:40
−
|  '''Arithmetic operators '''will however, not work for '''character data type '''columns.  
+
|  Arithmetic operators will however not work for '''character data type '''columns.  
  
 
|-  
 
|-  
Line 300: Line 300:
 
|-  
 
|-  
 
| 06:54
 
| 06:54
−
| Type '''Select 2+15+98+45;'''  
+
| Type: '''Select 2+15+98+45;'''  
  
 
Execute to see the output.  
 
Execute to see the output.  
Line 306: Line 306:
 
|-  
 
|-  
 
| 07:03
 
| 07:03
−
| Type''' Select 230 divided by 5;'''  
+
| Type:''' Select 230 divided by 5;'''  
  
−
Again execute the query to see the output.  
+
Again '''execute''' the '''query''' to see the output.  
  
 
|-  
 
|-  
 
| 07:11
 
| 07:11
−
| Let us see how to use '''arithmetic operators''' with a column of the '''table'''.  
+
| Let us see how to use arithmetic operators with a column of the '''table'''.  
  
 
|-  
 
|-  
Line 324: Line 324:
 
| Let us calculate the percentage from '''cgpa''' score.  
 
| Let us calculate the percentage from '''cgpa''' score.  
  
−
Switch back to SQL Editor window.  
+
Switch back to '''SQL Editor''' window.  
  
 
|-  
 
|-  
Line 330: Line 330:
 
| Type the query as shown here.  
 
| Type the query as shown here.  
  
−
What we want to do is multiply the '''cgpa''' with '''9.5 '''to get the actual percentage.  
+
What we want to do is, multiply the '''cgpa''' with '''9.5 '''to get the actual percentage.  
  
 
|-  
 
|-  
 
| 07:44
 
| 07:44
−
| Here, we are using the '''multiplication operator asterix '''to calculate the percentage.  
+
| Here, we are using the multiplication operator asterisk, to calculate the percentage.  
  
 
|-  
 
|-  
Line 348: Line 348:
 
|-  
 
|-  
 
| 08:02
 
| 08:02
−
| This percentage data is not stored in the '''database'''. It only displays the calculation.  
+
| This percentage data is not stored in the database. It only displays the calculation.  
  
 
|-  
 
|-  
 
| 08:09
 
| 08:09
−
| Now I want to give a name to this column, so that it is more meaningful.  
+
| Now I want to give a name to this column so that it is more meaningful.  
  
 
For that, we can use an '''alias '''name.  
 
For that, we can use an '''alias '''name.  
Line 371: Line 371:
 
|-  
 
|-  
 
| 08:35
 
| 08:35
−
| Here add the text as '''Percentage '''after the calculation column.  
+
| Here, add the text as '''Percentage '''after the calculation column.  
  
 
Execute the query to see the output.  
 
Execute the query to see the output.  
Line 381: Line 381:
 
|-  
 
|-  
 
| 08:51
 
| 08:51
−
| Please note, Column names which are mixed-case or uppercase have to be within double quotes in '''postgresql'''.  
+
| Please note, column names which are mixed-case or uppercase have to be within double quotes in '''postgresql'''.  
  
 
|-  
 
|-  
Line 393: Line 393:
 
|-  
 
|-  
 
| 09:07
 
| 09:07
−
| Next we will see how to use '''logical operators''' within the '''Select statement.'''  
+
| Next we will see how to use '''logical operators''' within the '''Select''' statement.
  
 
|-  
 
|-  
Line 401: Line 401:
 
|-  
 
|-  
 
| 09:18
 
| 09:18
−
| Or Using '''logical operators, '''we can combine two or more '''conditions'''.  
+
| Or, using '''logical operators, '''we can combine two or more '''conditions'''.  
  
 
|-  
 
|-  
Line 407: Line 407:
 
| The most commonly used '''logical operators''' are:  
 
| The most commonly used '''logical operators''' are:  
  
−
'''AND''': This joins two or more '''conditions '''and returns results only when all the '''conditions '''are '''true'''.  
+
'''AND''': This joins two or more '''conditions '''and returns results only when all the conditions are '''true'''.  
  
 
|-  
 
|-  
 
| 09:38
 
| 09:38
−
| '''OR''': This joins two or more '''conditions''' and returns results when at least one of the '''conditions '''is '''true'''.  
+
| '''OR''': This joins two or more '''conditions''' and returns results when at least one of the conditions is '''true'''.  
  
 
|-  
 
|-  
Line 427: Line 427:
 
| Type the code as shown.  
 
| Type the code as shown.  
  
−
Here we are checking for two '''conditions''' ie. Students who are from '''Pune''' city whose gender is '''Female'''.  
+
Here, we are checking for two '''conditions''' i.e. students who are from '''Pune''' city whose gender is '''Female'''.  
  
 
|-  
 
|-  
Line 449: Line 449:
 
|-  
 
|-  
 
| 10:34
 
| 10:34
−
| That is either the student is from Pune city or gender is ‘Female’  
+
| That is either the student is from '''Pune''' city or gender is ‘Female’  
  
 
|-  
 
|-  
Line 465: Line 465:
 
|-  
 
|-  
 
| 10:55
 
| 10:55
−
| With this we come to the end of this tutorial.  
+
| With this, we come to the end of this tutorial.  
  
 
Let us summarize.  
 
Let us summarize.  
Line 473: Line 473:
 
| In this tutorial, we have learnt about  
 
| In this tutorial, we have learnt about  
  
−
Basic '''Select statement'''  
+
basic '''Select''' statement,
  
 
|-  
 
|-  
 
| 11:05
 
| 11:05
−
|  '''Select''' with '''WHERE clause'''  
+
|  '''Select''' with '''WHERE''' clause,
  
−
'''Select''' with '''relational operators'''  
+
'''Select''' with '''relational operators''',
  
 
|-  
 
|-  
Line 485: Line 485:
 
|  '''Select''' with''' logical operators''' and  
 
|  '''Select''' with''' logical operators''' and  
  
−
'''Alias''' for column names  
+
'''Alias''' for column names.
  
 
|-  
 
|-  
 
| 11:15
 
| 11:15
−
| As an assignment, write a '''Select statement '''to  Display all columns from '''students table '''for the student name ''''Ram''''
+
| As an assignment, write a '''Select''' statement to  display all columns from '''students''' table for the student name 'Ram'.
  
 
|-  
 
|-  
 
| 11:24
 
| 11:24
−
|  Display '''studentid, studentname, dob '''of students''' '''whose   '''cgpa '''is '''greater than '''8 and '''less than '''9.5  
+
|  Display '''studentid, studentname, dob '''of students whose '''cgpa '''is greater than 8 and less than 9.5  
  
 
|-  
 
|-  
 
| 11:34
 
| 11:34
−
| The video at the following link summarises the Spoken Tutorial project.  
+
| The video at the following link summarizes the '''Spoken Tutorial''' project.  
  
 
Please download and watch it.  
 
Please download and watch it.  
Line 503: Line 503:
 
|-  
 
|-  
 
| 11:40
 
| 11:40
−
| The''' Spoken Tutorial Project''' Team
+
| 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 515: Line 513:
 
|-  
 
|-  
 
| 11:53
 
| 11:53
−
| 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 521: Line 519:
 
|-  
 
|-  
 
| 12:04
 
| 12:04
−
| 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 16:43, 5 February 2019

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 symbolized 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 asterisk, 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 i.e. 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 summarizes 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.

Contributors and Content Editors

PoojaMoolya, Sandhya.np14

Retrieved from "https://script.spoken-tutorial.org/index.php?title=RDBMS-PostgreSQL/C2/Select-statement/English-timed&oldid=45702"