RDBMS-PostgreSQL/C2/Select-statement/English

From Script | Spoken-Tutorial
Revision as of 16:01, 4 May 2017 by Nirmala Venkat (Talk | contribs)

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search

>>Title of script: Select


Author: Nirmala Venkat

Keywords: RDBMS, PostgreSQL, pgAdmin, Select, select with where clause, relational operators, arithmetic operators, logical operators, alias column, video tutorial

Visual Cue Narration
Slide 1: Welcome to the Spoken Tutorial on Select statement.
Slide 2:

Learning Objectives

In this tutorial we will learn about
  • Basic Select statement
  • Select with WHERE clause
  • Select with relational operators
  • Select with logical operators
  • Alias for column names
Slide 3:

System requirement

To record this tutorial, I am using
  • Ubuntu Linux 14.04 operating system
  • PostgreSQL 9.3.x
  • pgAdmin 1.18
Slide 4:

Pre-requisites

To follow this tutorial, you should have

Basic understanding of database and tables.

For more details, refer to the RDBMS PostgreSQL series on this website.

Open the pgAdmin Let us open pgAdmin.
Right-click >>students table >> View Data and then View All Rows.

Show the output of the inserted records

Right-click on the students table and select View Data and then View All Rows.

We can see the students table with the 4 records that we inserted earlier.

Type 2 rows of data I'll show how to insert records in this Edit Data window.

Click on the save icon on the top left of the toolbar to save the changes.

We can insert records either in this Edit data window or through the Insert statement.

Close this window.

Open the SQL editor window and type the code. I’ll insert some more records for demonstration purpose.

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

Highlight the Null value Note that, I have entered ‘NULL’ value in the dob column for student id ‘s012’.

In PostgreSQL, the term NULL is used to represent a missing value or an unknown value.

A field with a NULL value represents no value.

Click on the execute query icon. Execute the query.

Retrieve the data from the table to see the output.

So far we have inserted 14 records.

Point to the blank cell A NULL value field appears to be blank.

Null is not the same as a zero value or space value.

It implies that a database field value has not been stored.

Assignment 1 Pause the tutorial and do this assignment.

Insert 10 records into the students table.

Next, we will see about how to use SELECT statement with many clauses.
Slide 5(a) :

SELECT

  • The SELECT statement retrieves data from a database.
  • The data is returned in rows and columns format i.e. in a tabular format.
  • It has many clauses that we can combine to form a powerful query.
Slide 5(b):

Syntax:

Select column-names from table-name;

The basic syntax of the SELECT statement is:

Select column-names from table-name;

Now let us learn to write queries using Select statements.
Switch back to the PgAdmin main screen.
Right click on students node and select Scripts and then SELECT script. Right-click on students node, select Scripts and then click on SELECT script.
Highlight the statement

SELECT studentid, studentname,, city, gender, dob, cgpa FROM students;

This is a simple Select statement which retrieves data from the students table.

You can specify the column names as shown here or use 'asterisk' to select all the columns.

Type,

SELECT studentid, studentname, city, cgpa FROM students;

I want the details of certain columns only.

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

This SQL statement will display -

  • the student id, student name, city and cgpa columns
  • from the Students table.
Click on the Execute button. Now, let’s execute the query to see the output.
The expected rows and columns are displayed.

Let’s move ahead.

Slide 6:

SELECT with WHERE clause

SELECT with WHERE clause is used,
  • To filter the rows which match the criteria that is specified and
  • To limit the number of rows
Syntax:

SELECT column-names

FROM table-name

WHERE condition

The syntax of this statement is,

SELECT column-names FROM table-name WHERE condition

Records that satisfy the specified condition will be displayed on executing this statement.

Switch to pgAdmin Let’s switch back to the pgAdmin SQL Editor.
Type,

SELECT studentid, studentname, city, dob, cgpa from students where city ='Mumbai'

Type,

SELECT studentid, studentname, city, cgpa from students where city ='Mumbai'.

Click on the Execute button. Now, execute this query.
Highlight according to narration 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.

Here the City is the column name and 'Mumbai' is the value to filter.

Type,

SELECT * from students where city ='Bangalore'

Highlight the condition

Now let’s change the city value to ' Bangalore' and execute the query.

We can see that there are no records retrieved.

This is because the condition returns false on execution.

Point to the output Which means there are no records that match with the given condition.

<PAUSE>

Next, we will see how to use comparison or relational operators in the WHERE clause.
Slide 7:

Comparison/ Relational Operators:

= - equal to

<> or != - Not equal to

> - greater than

> - less than

>= - greater than or equal to

<= - less than or equal to

Relational operators listed here, can also be used within the WHERE clause.
Switch to pgAdmin Switch back to the pgAdmin SQL Editor.
Type,

Select studentid, studentname, city, cgpa from students where cgpa > 8.5

Type,

Select studentid, studentname, city, cgpa from students where cgpa > 8.5

Click on the Execute button. Execute the query.
The output shows details of students who have cgpa greater than 8.5
Type,

Select * from students where city <> 'Mumbai'

Highlight the 'Not' operator.

Let us type another query to retrieve the student details who are not from Mumbai city.

Note the 'Not equal to' operator.

It is symbolised by an open and close angular bracket.

Click on the Execute button. Execute the query.
Likewise, we can use relational operators to filter data depending upon our requirement.

<PAUSE>

Next let’s learn how to use arithmetic operators in the Select statement.
Slide 8:

Arithmetic expressions

  • When we need to do calculations within SQL statements, we can use arithmetic expressions.
  • Arithmetic expressions contain column names, arithmetic operators and numeric values.
  • Arithmetic operators will however, not work for character data type columns.
Let’s do a simple arithmetic calculation using some operators.

Clear the screen.

Type Select 2+15+41+3;

Click on the Execute button.

Type Select 2+15+41+3;

Execute to see the output.

Type Select 230/5;


Click on the Execute button.

Type Select 230 divided by 5;

Again execute the query to see the output.

Let us see how to use arithmetic operators with a column of the table.
Point to the cgpa column We have a column called cgpa which is of numeric data type.

CGPA stands for Cumulative Grade Point Average.

Let us calculate the percentage from cgpa score.

Switch back to SQL Editor window.

Type,

Select studentid, studentname, cgpa, cgpa*9.5 from students;

Type the query as shown here.

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

Here, we are using the multiplication operator asterix to calculate the percentage.

Click on the Execute button. Let’s execute the query now.
Point to the output Percentage is calculated and displayed in a new column, as shown here.

But there is no specific column name for this column.

This percentage data is not stored in the database. It only displays the calculation.

Point to the output Now I want to give a name to this column, so that it is more meaningful.

For that, we can use an alias name.

Slide 9:

Column Alias

  • An alias is used to rename a column temporarily.
  • It appears as a heading in the output of the query.
  • Use column alias

if the original column name is not

meaningful or

when there is a column name conflict.

Type,

Select studentid, studentname, cgpa, cgpa*9.5 as Percentage from students;

Select studentid, studentname, cgpa, cgpa*9.5 “ PERCENTAGE” from students;

Here add the text as Percentage after the calculation column.

Execute the query to see the output.

We can also specify in capital letters as “PERCENTAGE” within double quotes.

Please note.

Column names which are mixed-case or uppercase have to be within double quotes in postgresql.

Click on the Execute button. Execute the query.
Point to the output We can see the column name has changed to PERCENTAGE in capital letters.
Next we will see how to use logical operators within the Select statement.
Slide 10(a):

Logical operators

  • Logical operators are used where more than one condition needs to be checked.
  • Using logical operators, we can combine two or more conditions.


Slide 10(b):

Logical operators

The most commonly used logical operators are:

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

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

NOT: This operator results in the reverse of a condition.

Switch to pgAdmin Switch back to PgAdmin interface.

Let us see few examples for logical operators.

Type,

Select studentid, studentname from students where city= ‘Pune’ and gender =’Female’

Type the code as shown.

Here we are checking for two conditions.

Students who are from Pune city whose gender is Female.

It will return the output when both the conditions are true.

Notice how the 'AND' logical operator is written.

Click on the Execute button. Execute the query to see the output.
Type,

Select studentid, studentname from students where city= ‘Pune’ or gender =’Female’

Click on the Execute button.

Next, let’s change the 'AND' to 'OR' logical operator and execute the query.
Point to the output The records are retrieved if either one of the conditions is true.

That is either the student is from Pune city or gender is ‘Female’

Switch to pgAdmin Next we will see an example of NOT operator.
Type,


Select studentid, studentname, city from students where NOT city ='Mumbai' AND NOT city='Chennai';

This query will display students who are not from Mumbai or Chennai.

Here we have used the NOT and AND operators together.

Click on the Execute button. Execute the query and see the output.

<PAUSE>

With this we come to the end of this tutorial.

Let us summarize.

Summary In this tutorial, we have learnt about
  • Basic Select statement
  • Select with WHERE clause
  • Select with relational operators
  • Select with logical operators
  • Alias column names
Assignment As an assignment, write a Select statement to
  1. Display all columns from students table for the student name 'Ram'
  2. Display studentid, studentname, dob of students whose
    • cgpa is greater than 8 and
    • less than 9.5
Slide 12:

About Spoken Tutorial project

The video at the following link summarises the Spoken Tutorial project.

Please download and watch it.

Slide 13:

Spoken Tutorial workshops

The Spoken Tutorial Project Team
  • conducts workshops
  • gives certificates

For more details, please write to us.

Slide 13:

Forum for specific questions

Please post your timed queries in this forum.
Slide 14:

Acknowledgement

Spoken Tutorial project is funded by NMEICT, MHRD, Government of India.

More information on this mission is available at

this link.

This is Nirmala Venkat from IIT Bombay, signing off. Thanks for watching.

Contributors and Content Editors

Nancyvarkey, Nirmala Venkat

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