Practical No 7 Operators

Practical NO.
10
Operation (Arithmetic, Logical, Concatenation)
Operators
There are various types of operators discussed in this appendix. The following types are
provided:
 Arithmetic Operators are used to perform mathematical calculations.

 Assignment Operators are used to assign a value to a property or variable.
Assignment Operators can be numeric, date, system, time, or text.
 Comparison Operators are used to perform comparisons.
 Concatenation Operators are used to combine strings.
 Logical Operators are used to perform logical operations and include AND, OR,
or NOT.
 Boolean Operators include AND, OR, XOR, or NOT and can have one of two
values, true or false.
NULL Value in Expression

NULL is a reserved keyword used to indicate that a data value is unknown. It is the ASCII
abbreviation for NULL characters (\0).
Usage in Expressions
 NULL is not greater than, less than, equal to, or not equal to any other expression. Use
the Boolean-Predicate to determine whether an expression value is NULL.
 You can write queries with expressions that contain the <=> operator for NULL=NULL joins.
See Equi-Joins and Non Equi-Joins in Analyzing Data.
 Vertical accepts NULL characters ('\0') in constant strings and does not remove null characters
from VARCHAR fields on input or output.
Between in, Like Operators
BETWEEN in SQL
The BETWEEN operator in SQL is used to select values within a given range. For example, to
extract only those records where the age of the person is between 20 and 25, we have to use the
SQL BETWEEN operator.
SQL between Syntax
SELECT columnist
FROM table name
Where column BETWEEN val1 AND val2;
where SELECT, FROM, WHERE, BETWEEN, and are the keywords, column list is a list of
columns, table name is the name of the table, column is the column name, and val1 is the minimum
value of the range and val2 is the maximum value of the range.
LIKE Operator in SQL

The LIKE in SQL is used to extract records where a particular pattern is present. For example,
imagine that we want to extract a person’s name that includes ‘john’. So, if there are three people
with names Johnathan, Johnny, and Marcus, then with help of the LIKE in SQL, we can extract
records where the names are Johnathan and Johnny, since they have the required pattern present
in them.
The LIKE in SQL is used in conjunction with wildcard characters, such as:
 Percentage symbol: It is a substitution for zero, one, or more characters.

 Underscore symbol: It is a substitution for a single character.
SQL like Syntax
SELECT column list
FROM table name
WHERE column LIKE ‘_xxxxx%’;
Let’s do some operations with the LIKE query in SQL. Extract all records where the employee
name starts with the letter ‘J’
Select * from employee where e name l LIKE 'j%';
Column Alias
 Aliases are created to make table or column names more readable.
 The renaming is just a temporary change and table name does not change in the original
database.
 Aliases are useful when table or column names are big or not very readable.
 These are preferred when there are more than one table involved in a query.
Basic Syntax:
 For column alias:
 SELECT column as alias name FROM table name;
 column: fields in the table
 alias name: temporary alias name to be used in replacement of original column name
table name: name of table
Sorting (order by class)
The SQL ORDER BY clause is used to sort the records in the result set for a SELECT statement.
Syntax
The syntax for the ORDER BY clause in SQL is:
SELECT expressions
FROM tables
[WHERE conditions]
ORDER BY expression [ASC | DESC];
Parameters or Arguments
Expressions
The columns or calculations that you wish to retrieve.
Tables
The tables that you wish to retrieve records from. There must be at least one table listed in the
FROM clause.
WHERE conditions
Optional. Conditions that must be met for the records to be selected.
ASC
Optional. ASC sorts the result set in ascending order by expression. This is the default behavior,
if no modifier is provider.
DESC
Optional. DESC sorts the result set in descending order by expression.

Single row Function
Single row functions can be used in SELECT statement, WHERE and ORDER BY clause. Single
row functions can be -
 General functions - Usually contains NULL handling functions. The functions under the category are
NVL, NVL2, and NULLIF, COALESCE, CASE, DECODE.
 Case Conversion functions
o UPPER function converts a string to upper case.
o LOWER function converts a string to lower case.
o INITCAP function converts only the initial alphabets of a string to upper case.
 Character
o CONCAT function concatenates two string values.
o LENGTH function returns the length of the input string.
o SUBSTR function returns a portion of a string from a given start point to an end point.
o INSTR function returns numeric position of a character or a string in a given string.
o LPAD and RPAD functions pad the given string upto a specific length with a given character.
o TRIM function trims the string input from the start or end.
o REPLACE function replaces characters from the input string with a given character.
 Date functions -
o MONTHS_BETWEEN function returns the count of months between the two dates.
o ADD_MONTHS function add 'n' number of months to an input date.
o NEXT_DAY function returns the next day of the date specified.
o LAST_DAY function returns last day of the month of the input date.
o ROUND and TRUNC functions are used to round and truncates the date value.
 Number functions
o ROUND and TRUNC functions are used to round and truncate the number value.
o MOD is used to return the remainder of the division operation between two numbers.

Practical No 7 Operators

Uploaded by

Copyright:

Available Formats

Practical No 7 Operators

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Practical No 7 Operators

Uploaded by

Copyright:

Available Formats

Practical NO.

 Arithmetic Operators are used to perform mathematical calculations.

NULL Value in Expression

Between in, Like Operators

LIKE Operator in SQL

 Percentage symbol: It is a substitution for zero, one, or more characters.

The columns or calculations that you wish to retrieve.

Optional. Conditions that must be met for the records to be selected.

Optional. DESC sorts the result set in descending order by expression.

You might also like