Unit-III

Selection: A select operation selects a subset of rows (records) in a table (relation) that satisfy a
selection condition. The ability to select rows from out of complete result set is called Selection. It
involves conditional filtering and data staging. The subset can range from no rows, if none of the
rows satisfy the selection condition, to all rows in a table.

The SELECT command is used to select data from a database. The data returned is stored in a
result table, called the result set

Selecton (σ)

1. Selection(σ): It is used to select required tuples of the relations.

Example:
A B C

1 2 4

2 2 3

3 2 3

4 3 4

For the above relation, σ(c>3) R will select the tuples which have c more than 3.
A B C

1 2 4

4 3 4

Projection: A project operation selects only certain columns (attributes) from a table. The result
table has a subset of the available attributes and can include anything from a single attributes to all
available columns.
It selects certain attributes from the table and discards other attributes. If we are interested in only
certain attributes of a relation, we use project operation to project the relation over these attributes
only. Therefore the result of PROJECT operation is visualized as a vertical partition of the
relation into two relations.
One has needed the columns(attributes) and contains the result of the operation and other contains
the discarded columns.

Project (Π)
Projection(π): It is used to project required column data from a relation.
Example: Consider below table e want columns B and C from Relation R.
Example:
A B C

1 2 4

2 2 3

3 2 3

4 3 4

π(B,C)R will show following columns.

B C

2 4

2 3

3 4
Note: By Default, projection removes duplicate data.

WHERE Clause:
WHERE clause is an optional condition that can be used with a SELECT statement. It’s purpose

is to filter out specific records from a dataset or table.

WHERE clause is used to filter records.

It is used to extract only those records that fulfill a specified condition.

The WHERE clause filters out the Records or specifies a condition while extracting the records

from a single table or joining multiple tables.

It follows the SELECT and FROM clause. The conditions in the WHERE clause must be

evaluated to TRUE for a row to be returned as a result. WHERE clause is used

with UPDATE, DELETE, or SELECT a particular data set.

Operators in that can be used with the WHERE clause

Operator Description
= Equal
> Greater than
< Less than
>= Greater than or Equal
<= Less than or Equal
<> Not equal
BETWEEN Between a certain range
LIKE Search for a pattern
IN To specify multiple possible values in a particular column
Syntax:

SELECT <column names>

FROM <table name>
WHERE <conditions>;

employee
fname salary deptid
John 46,000 4
Williams 42,000 5
Hussain 27,000 2
Stuwart 46,500 5
Glenn 51,000 3
Mark 48,000 5
Stephen 54,000 2
Richards 50,000 5
Bose 56,000 1
Gary 25,000 5

Example:

SELECT name
FROM employee
WHERE deptid=1;

Output:
Bose
We can use various Arithmetic Operators on the data stored in the tables.

Arithmetic Operators are:

+ [Addition]
- [Subtraction]
/ [Division]
* [Multiplication]
% [Modulus]

Addition (+) :
It is used to perform addition operation on the data items, items include either single column
or multiple columns.

SELECT 30 + 20;

Subtraction (-) :
It is use to perform subtraction operation on the data items, items include either single column
or multiple columns.

SELECT 30 - 20;

Division(/):
It is used to perform Division
SELECT 30 / 5; Ggggvvbbbb

Multiplication (*) :
It is use to perform multiplication of data items.
SELECT 30 * 20;

Modulus ( % ) :
It is use to get remainder when one data is divided by another.
SELECT 31 / 5;
Logical Operators:

Operator Description

ALL TRUE if all of the subquery values meet the condition

AND TRUE if all the conditions separated by AND is TRUE

ANY TRUE if any of the subquery values meet the condition

BETWEEN TRUE if the operand is within the range of comparisons

EXISTS TRUE if the subquery returns one or more records

IN TRUE if the operand is equal to one of a list of expressions

LIKE TRUE if the operand matches a pattern

NOT Displays a record if the condition(s) is NOT TRUE

OR TRUE if any of the conditions separated by OR is TRUE

All:

SELECT fname
FROM employee
WHERE salary >all (SELECT salary
FROM employee
WHERE deptid=5);

Any:
SELECT fname
FROM employee
WHERE salary >any (SELECT salary
FROM employee
WHERE deptid=5);

And:
SELECT * FROM Customers
WHERE City = "London" AND Country = "UK";

In:
SELECT * FROM Customers
WHERE City IN ('Paris','London');

Not in:
SELECT * FROM Customers
WHERE City NOT IN ('Paris','London');

Like:
SELECT * FROM Customers
WHERE City LIKE 'abc';

Not Like:
SELECT * FROM Customers
WHERE City NOT LIKE 'abc';

OR:
SELECT * FROM Customers
WHERE City = "London" OR Country = "UK";

Between:
SELECT * FROM Products
WHERE Price BETWEEN 50 AND 60;

Exists:
SELECT SupplierName
FROM Suppliers
WHERE EXISTS (SELECT ProductName FROM Products WHERE Products.SupplierID =
Suppliers.supplierID);
String Functions

1. RPAD(): This function is used to make the given string as long as the given size by adding the given
symbol on the right.
Syntax: RPAD('geeks', 8, '0');
Output: ‘geeks000’

2. LPAD(): This function is used to make the given string of the given size by adding the given
symbol.
Syntax: LPAD('geeks', 8, '0');
Output: 000geeks

3. LTRIM(): This function is used to cut the given sub string from the original string.
Syntax: LTRIM('123123geeks', '123');
Output: geeks

4. RTRIM(): This function is used to cut the given sub string from the original string.
Syntax: RTRIM('geeksxyxzyyy', 'xyz');
Output: ‘geeks’

5. LOWER(): This function is used to convert the upper case string into lower case.
Syntax: SELECT LOWER('GEEKSFORGEEKS.ORG');
Output: geeksforgeeks.org

6. UPPER(): This function is used to convert the string into upper case.
Syntax: SELECT UPPER('geeksforgeeks.org');
Output: GEEKSFORGEEKS.ORG

7. LENGTH(): This function is used to find the length of a word.

Syntax: LENGTH('GeeksForGeeks');
Output: 13

8. INSTR(): This function is used to find the occurrence of an alphabet.

Syntax: INSTR('geeks for geeks', 'e');

Output: 2 (the first occurrence of ‘e’)

9. SUBSTR(): This function is used to extract a sub string from the given string at given position.
Syntax: SUBSTR('geeksforgeeks', 1, 5);
Output: ‘geeks’

10. INITCAP(): The INITCAP function in PLSQl is used for setting the first character in each word to
uppercase and the rest to lowercase.
11. CONCAT(): The CONCAT Function is used to join two strings
SQL> select concat(fname,lname) from expt4 where loc='odisha';
Output:
veenarana
To_char: This function converts DATETIME or DATE values to character string values.

To_number: This function can convert a number or a character expression representing a

number value to a DECIMAL data type.

To_date: TO_DATE converts char to a value of DATE data type.

Date Functions:
LAST_DAY( ): It displays the last date of the month

SQL> select last_day('02-Nov-2023') from guntur;

LAST_DAY(
---------
30-NOV-23
30-NOV-23
30-NOV-23
30-NOV-23
30-NOV-23
30-NOV-23

6 rows selected.

SQL> select last_day('02-Nov-2023') from guntur where deptno=4;

LAST_DAY(
---------
30-NOV-23

NEXT_DAY( )
It displays the next date of the next specified day
SQL> select next_day('03-Nov-2023','Monday') from guntur where
deptno=4;
NEXT_DAY(
---------
06-NOV-23

MONTHS_BETWEEN( )
It displays the months between specified dates

SQL> select months_between('02-Nov-2023','02-May-2023') from guntur where

deptno=4;

MONTHS_BETWEEN('02-NOV-2023','02-MAY-2023')
-------------------------------------------
6
ADD_MONTHS ( )
SQL> select add_months('02-Aug-2023',3) from guntur where
deptno=4;
ADD_MONTH
---------
02-NOV-23

SYSDATE( )
It displays System Date
SQL> select sysdate from guntur where deptno=4;

SYSDATE
---------
02-NOV-23

ROUND()
select ROUND (TO_DATE ('02-Nov-23'),'year') from guntur;
ROUND(TO_
---------
01-JAN-24

LEAST() Displays the smallest value

SQL> select least (8,9,4,7,2,4) from guntur;

LEAST(8,9,4,7,2,4)
------------------
2
2
2
2
2
2

6 rows selected.

SQL> select greatest (8,9,4,7,2,4) from guntur;

GREATEST( ) Displays the Biggest Value.

GREATEST(8,9,4,7,2,4)
---------------------
9
9
9
9
9
9

6 rows selected.
Numeric Functions
ABS(): It returns the absolute value of a number.

Syntax: SELECT ABS(-243.5);

Output: 243.5

CEIL(): It returns the smallest integer value that is greater than or equal to a number.

Syntax: SELECT CEIL(25.75);

Output: 26

DIV(): It is used for integer division.

Syntax: SELECT 10 DIV 5;

Output: 2

EXP(): It returns e raised to the power of a number.

Syntax: SELECT EXP(1);

Output: 2.718281828459045

FLOOR(): It returns the largest integer value that is less than or equal to a number.

Syntax: SELECT FLOOR(25.75);

Output: 25

GREATEST(): It returns the greatest value in a list of expressions.

Syntax: SELECT GREATEST(30, 2, 36, 81, 125);

Output: 125

LEAST(): It returns the smallest value in a list of expressions.

Syntax: SELECT LEAST(30, 2, 36, 81, 125);

Output: 2
ROUND(): It returns a number rounded to a certain number of decimal places.

Syntax: SELECT ROUND(5.553);

Output: 6

SIGN(): It returns a value indicating the sign of a number. A return value of 1 means positive; 0
means negative.

Syntax: SELECT SIGN(255.5);

Output: 1

POWER(m, n): It returns m raised to the nth power.

Syntax: SELECT POWER(4, 2);

Output: 16