Database Knowledge for a Tester Part IV

1 COMPUTATIONS ON TABLE DATA

Computations on table data may include displaying an employee’s name and the employee salary from the EMPLOYEE Master table alongwith the annual salary of the employee (i.e. Salary * 12). This can be achieved by using Arithmetic operator. We will see all these explained in below mentioned sections.

1.1 Operators Usage

1.1.1 Arithmetic Operator
Oracle allows arithmetic operators to be used while viewing records from the table or while performing Data Manipulation operations such as Insert, Update and Delete.

For e.g.

Consider we want to increment the salary of all employees by Rs 500.

UPDATE EMPLOYEE SET SALARY = (SALARY + 500)

Similarly you can use the above arithmetic operator for manipulating other data based on the requirement.

1.1.2 Logical Operator

The AND operator
The AND operator requires that each condition must be met for the record to be included in the result set. It can be used in any valid SQL statement such as Select, Insert, Update or Delete.

For e.g.

Consider we want to view the employee whose salary lies between 1000 to 5000.

SELECT EMPLOYEE_NAME FROM EMPLOYEE WHERE SALARY>=1000 AND SALARY<=5000.

The OR operator
The OR operator requires that any of the conditions must be met for the record to be included in the result set. It can be used in any valid SQL statement such as Select, Insert, Update or Delete.

For e.g.

Consider we want to view the employee which belongs to either ADMIN or IT department.

SELECT EMPLOYEE_NAME FROM EMPLOYEE WHERE DEPTT=’ADMIN’ OR DEPTT=’IT’

Combining the AND and OR operator
It can be used in any valid SQL statement such as Select, Insert, Update or Delete. When combining these conditions it is important to use brackets so that the database knows what order to evaluate each condition.

For e.g.

Consider we want to view the employee and which belongs to either ADMIN or IT department and their salary is greater than 3000.

SELECT EMPLOYEE_NAME, SALARY FROM EMPLOYEE WHERE (DEPTT=’ADMIN’ OR DEPTT=’IT’) AND SALARY>3000.

The NOT operator

The NOT operator display only those records that do not satisfy the conditions specified.

For e.g.

Consider we want to view the employees which are not married.

SELECT EMPLOYEE_NAME FROM EMPLOYEE WHERE NOT (MARITAL STATUS=’M’).

1.1.3 Range Searching – BETWEEN operator

The BETWEEN operator allows the selection of rows that contain values within a specified lower and upper limit. The range coded after the word BETWEEN is inclusive.

For e.g.

We can write the “Consider we want to view the employee whose salary lies between 1000 to 5000” also as:

SELECT EMPLOYEE_NAME FROM EMPLOYEE WHERE SALARY BETWEEN1000 AND 5000

1.1.4 Pattern Matching – LIKE predicate

The LIKE predicate allows comparison of one string value with another string value, which is not identical. This is achieved by using wildcard characters. Two wildcard characters that are available are:

• % allows to match any string of any length (including zero length)
• _ allows to match on a single character.

For e.g.

Suppose we want to view the Employee Name which starts with Letter “S”.

SELECT EMPLOYEE_NAME FROM EMPLOYEE WHERE EMPLOYEE_NAME LIKE ‘S%’

Similarly if we want to view only those employee name which start with letter S and is of 4 characters only.

SELECT EMPLOYEE_NAME FROM EMPLOYEE WHERE EMPLOYEE_NAME LIKE ‘S____’

1.1.5 IN and NOT IN Predicates

If a value needs to be compared to a list of values then the IN predicate is used. This predicate helps reduce the need to use multiple OR conditions.

For e.g.

Consider we want the list of all employees which belong to either ADMIN, HR, QUALITY, IT or PROJECT department.

SELECT EMPLOYEE_NAME FROM EMPLOYEE WHERE EMPLOYEE_NAME IN (‘ADMIN’,’HR’, ‘QUALITY’,’ IT’, ‘PROJECT’)

Note: NOT IN is just the opposite of IN and select all the rows where values do not match the values in the list.

1.2 Oracle Functions

1.2.1 Oracle Table “DUAL”

DUAL is a table owned by SYS. When an arithmetic calculation is to be performed such as 2*2 or 4/2 and so on, there is no table being referenced; only numeric literals are being used. To facilitate such calculation via a SELECT statement, Oracle provides a dummy table called DUAL.

For e.g.

SELECT 2*2 from DUAL will return 4.

SELECT SYSDATE from DUAL will return system date.

1.2.2 GROUP or AGGREGATE Functions

Functions that act on a set of values are called Group or Aggregate functions. For e.g. SUM is a function which calculated the total set of numbers. A Group function returns a single result row for a group of queried rows.

Some of the GROUP or AGGREGATE Function is described below:

1.2.3 Single Row or Scalar Functions

Functions that act on only one value at a time are called Scalar Functions. Single row function can be classified corresponding to different data types as:

String Functions - For String data type.
Numeric Functions – For Number data type
Conversion Functions – For Conversion data type
Date Functions - For Date data type

Some of the available scalar functions are described below:

1.3 Date Functions

1.4 Miscellaneous Functions

UID – This function returns an integer value corresponding to the userid of the user currently logged in.

Syntax:

UID(INTO [variable])

For e.g.

SELECT UID FROM DUAL;

USER – This function returns the user name of the user who has logged in. The value returned is in varchar 2 datatype.

Syntax:

USER

For e.g.
SELECT USER FROM DUAL;

Technovation By SKS

Search This Blog