sql operators oracle

7/29/2019 SQL operators Oracle

1/38

LEVEL PRACTITIONER

Oracle SQL Operators

Oracle 10g SQL


2/38

About the Author

2

Created By: Madhava(t-Madhava)/ Shanmu (105110)

Credential

Information:

Trainer/ Sr Architect

Version andDate:

1.0, Jan 30 , 2012

Cognizant Certified OfficialCurriculum


3/38

Icons Used

Questions

Demonstration

Hands onExercise

CodingStandards

BestPractices &IndustryStandards

Tools

33

Case

Study

Test YourUnderstanding

Workshop


4/38

Objectives

After completing this chapter you will be able to understand,

What are SQL Operators?What are Arithmetic Operators?What are Character Operators?What are Single Row Comparison Operators?What are Logical Operators?

What are Set Operators?

4


5/38

Recap of the Case Study

5

We will use the same CMS case study for learning how to use operators inDQL and DML statements

Case Study Scenario: This case study is to develop a Course Management System(CMS) for ABC University. The following are the two uses case for which the

database needs to be designed.

Add Course To add the course details into the course management system.Retrieve Course Retrieve the courses stored in the system and display it.

The courses to be added will have the following attributes Course Code, Course

Name, Number of participants, Course Description, Course Duration, Course startdate and Course Type.


6/38

SQL Operators

6

What are SQL Operators ?

An SQL Operator used for processing data values (stored in columns oftables) and returns a result. The data values are called operands.

SQL Operators are represented by special characters or by keywords.

Oracle SQL supports the following operators

1. Arithmetic Operators2. Character Operator3. Single Row Comparison Operators4. Multiple Row Comparison Operators5. Logical Operators6. Set Operators


7/38

Arithmetic Operators

7

Arithmetic operators are used to manipulate numeric operands that iscolumns storing numeric values.

Operator Description Example

+(unary) Makes operand positive SELECT + EMP_SAL FROM

EMPLOYEE;-(Unary) Makes operand negative SELECT - EMP_SAL FROM

EMPLOYEE;

/ Division(Used with Number andDate)

SELECT SAL / 10 FROM EMP;

* Multiplication SELECT SAL * 10 FROM EMP;

+ Addition (numbers and dates) SELECT SAL + 1000 FROM EMP;

- Subtraction (numbers and dates) SELECT SAL - 500 FROM EMP;


8/38

Character Operators

8

Character operators are used in to manipulate string operands that iscolumns storing string values.


|| Concatenates character strings SELECT EMP_NAME || || is

|| || Employee of CTS FROMEMPLOYEE;

|| operator combines the values of more than one column also.

Example:EMP_ID || EMP_NAME || JOIN_DATE


9/38

Single Row Comparison Operators

9

Single Row Comparison operators are used in conditions that compareone operand with another. The result of a comparison can be TRUE (or)FALSE.Operator Description Example

= Equality Test SELECT EMP_SAL FROMEMPLOYEE WHERE EMP_ID=3;

!=, Inequality Test SELECT EMP_PF FROM EMPLOYEEWHERE EMP_ID!=7;

> Greater than test SELECT EMP_SAL FROMEMPLOYEE WHERE EMP_ID>3;

< Less than test SELECT EMP_SAL FROM

EMPLOYEE WHERE EMP_ID= Greater than or equal to test. SELECT EMP_SAL FROMEMPLOYEE WHERE EMP_ID >= 20;


10/38

Multiple Row Comparison Operators

10

Multiple Row Comparison operators are used in conditions that compares a valueto each value in a list .


IN/NOT IN Equivalent to comparing theoperand value with a list ofvalues and if any match

happens it returns true.

SELECT EMP_ID FROM EMP WHERE ENAMEIN ('SMITH', 'WARD');

SELECT EMP_ID FROM EMP WHERE ENAMENOT IN ('SMITH', 'WARD');

BETWEENAND/NOTBETWEEN AND

Check is the operand valueis between a range, thisincludes the lower andhigher limits.

SELECT ENAME,JOB FROM EMP WHERESAL BETWEEN 3000 AND 5000; //selectsemployee whose salary between 3000 and5000. it also includes the value 3000 and 5000.

SELECT ENAME,JOB FROM EMP WHERESAL NOT BETWEEN 3000 AND 5000; //selects employee whose salary is NOT between3000 and 5000. it also includes the value 3000and 5000.


11/38


11


LIKE/NOTLIKE

The LIKE operator is used forwild card matching._ is used for single character.% used for multiple or nocharacter.

SELECT EMP_NAME FROM EMP WHEREEMP_NAME LIKE %an';//Select employees whose name ends with ANExample: Conan, Ronan etc.

SELECT EMP_NAME FROM EMP WHEREEMP_NAME LIKE Ro_'; //Select employeeswhose name starts with Ro and ends with onecharacter after it.Example: Ron, Roy etc.

ALL Compares a value with every

value in a list or returned by aquery. Must be preceded by =, !=, >, = ALL (1400, 3000); // Compares ifsalary is greater than all the values in the list whichis 1400 & 3000 if so returns true


12/38


12


ANY/SOME Compares a value toeach value in a list orreturned by a query. Mustbe preceded by =, !=, >, 1500; //returns all records which has salary > 1500 and

name is not null


13/38

Time To Reflect

13

Associates to reflect the following before proceeding What is the operator used for concatenating two column values?

What is the operator used for checking whether a age falls in the range10 and 60?

What is operator used to check if a name starts with "An"? What is operator used to check if a column values meets all the values

in a list or a subquery?

How to check if a column is null?


14/38

Lend A Hand - Prerequisites

Pre-requisite # 1 :Associates should ensure thatthe tables specified in the document are availablein the oracle database with each table followed bythe employee id .

Pre-requisite # 2: Load the table with necessarydata using the DML statements.

14


15/38


16/38

Lend a Hand - Operators

Problem # 4: Select all the courses whose base fee > 200.

Hint: Use the course_fees _ table for this.

Problem # 5: Display the students Id , first name whose first name and

last name are not equal.Hint: Use the student_info _ table for this.

Problem # 6: Select all the courses whose base fee is in the range 100and 3000.

Hint: Use the course_fees_ table for this.Problem # 7: Display the students Id , first name whose first name startswith A

Hint: Use the student_info _ table for this.

16


17/38

Lend a Hand - Operators

Problem # 8: Display the students Id , first name whose first name has acharacter o

Hint: Use the student_info_ table for this.

Problem # 9: Display the names of all the courses where the coursedescription is Null.

Hint: Use the courses_info_ table for this.

17


18/38

Lend a Hand - Solutions

Solution #1:

Solution #2:

Solution #3:

Solution #4:

Solution #5:

18


19/38

Lend a Hand - Solutions

Solution #6:

Solution #7:

Solution #8:

Solution #9:

19


20/38

Logical Operators

20

Logical operators is used for manipulating the results of one or moreconditions.

Example: If age > 45 AND salary < 4000. Here And is the operator used tocombine the results of the both the condition and returns a result.


NOT Returns TRUE if the conditionreturns FALSE. Returns FALSE ifthe return values is TRUE.

SELECT EMP_NAME FROM EMP WHERENOT (JOB IS NULL) // Retrieves theemployee names who has a job assigned.

AND Used to combine two conditions.Returns TRUE if both conditionare met. Returns FALSE if eitherof it is FALSE.

SELECT EMP_NAME FROM EMP WHEREJOB='CLERK AND manager=Tom ; //Retrieves the employee names who has adesignation clerk and their manager is Tom.

OR Returns TRUE if one of thecondition returns TRUE. ReturnsFALSE if both are FALSE.

SELECT EMP_NAME FROM EMP WHEREJOB='CLERK OR MGR_ID=10 ; //Retrieves the employee names who has adesignation clerk (or) their manager is Tom.


21/38

Set Operators

21

Set operators combine the results of two queries into a single result.

The two queries can be a select query from a same table or from differenttables.

The different types of Set Operators are1. UNION - Returns all distinct rows selected by both the queries.

2. UNION ALL - Returns all rows selected by either query, including allduplicates.

3. INTERSECT - Returns all distinct rows selected by both queries

4. MINUS - Returns all distinct rows selected by the first query but notthe second.


22/38


23/38


24/38

Example: Union Operator

24

Product_ID Product_Name

1 Product 1

2 Product 2

3 Product 3

Order_ID Shop_Name

1 Product 1

5 Product 5

4 Product 4

5 Product 5

Select Product_ID, Product_Name from ProductsUNION

Select Oder_Id,Oder_Name from Orders;

Products Orders

Product_Id Product_Name1 Product 1

2 Product 2

3 Product 3

4 Product 4

5 Product 5

All the unique records from boththe tables will be fetched.

Duplicaterecords

within the

table.

Duplicaterecords

across thetable.

Output:


25/38


26/38

Example: Union All Operator

26

Select Product_ID, Product_Name from ProductsUNION ALL


Products Orders


1 Product 1

2 Product 2

3 Product 3

Order_ID Shop_Name

1 Product 1

5 Product 5

4 Product 4

Product_Id Product_Name

1 Product 1

2 Product 2

3 Product 3

4 Product 4

5 Product 5

1 Product 1

Output:

This also retrieves theduplicate records


27/38

Intersect Operator

27

The INTERSECT operator returns all records common in the selectqueries.

The number of columns and data type of the columns being selectedmust be identical in all the SELECT statements used in the query.

Syntax:

select column1,column1, .....,columnN from table1

INTERSECT

select column1,column1, .....,columnN from table2;


28/38

Example: Intersect Operator

28


1 Product 1

2 Product 2

3 Product 3

Order_ID Shop_Name

2 Product 2

4 Product 4

5 Product 5

Select Product_ID, Product_Name from ProductsINTERSECT


Products Orders


2 Product 2

Product 2 record which iscommon between twotables is retrieved.

Output:


29/38

Minus Operator

29

The MINUS Operator return the rows of the first query that are not presentin the second query.

The result of first select statement MINUS The result of second selectstatement.

Syntax:

select column1,column1, .....,columnN from table1

MINUS

select column1,column1, .....,columnN from table2;


30/38

Example: Minus Operator

30


1 Product 1

2 Product 2

1 Product 3

Order_ID Shop_Name

2 Product 2

3 Product 3

4 Product 4

Select Product_ID, Product_Name from ProductsMINUS


Products Orders


1 Product 1

1 Product 3

Output:


31/38

Time To Reflect

31

Associates to reflect the following before proceeding What is the operator used for retrieving the common records between

two tables?

How can one retrieve all the unique records from both the tables?

How can one retrieve all the records including the duplicate values fromboth the tables?


32/38


33/38

Lend a Hand Set Operator

33

Pre- Requisite: Create a table named COURSE_FEES_HISTORYwith the followingcolumns.

ColumnName

Data Type

Course_Code Varchar2

Base_fees NumberSpecial_fees Number

Created_By Varchar2

Updated_By Varchar2

COURSE_CODE BASE_FEES SPECIAL_FEES DISCOUNT

1 180 100 10

2 150 110 10

3 160 170 54 150 100 10

6 190 100 40

COURSE_CODE BASE_FEES SPECIAL_FEES CREATED_BY Updated _By

1 120 123 Ram Ramesh

2 150 110 Bala Ram

3 160 170 Bala Vinu

4 170 235 Ram Ram

6 190 100 Vinod Vinod

COURSE_FEES_HISTORY

COURSE_FEES


34/38


34

Problem # 1:

Display all the unique courses between course_fees_ andcourse_fees_history _ . Use the following columns to check foruniqueness Course_Code,BASE_FEES and SPECIAL_FEES of the courses in both theCOURSE_FEES _ and COURSE_FEES_HISTORY _.

Problem # 2:

Display all the common courses between course_fees _ andcourse_fees_history _ . Use the following columns to check forcommonality Course_Code, BASE_FEES and SPECIAL_FEES of the courses in boththe COURSE_FEES _ and COURSE_FEES_HISTORY _

Problem #3 :

Display all the courses which exists course_fees_history _ but not incourse_fees _ . Use the following columns for comparing recordsCourse_Code, BASE_FEES and SPECIAL_FEES of the courses in both theCOURSE_FEES _ and COURSE_FEES_HISTORY _.


35/38


36/38


36

Solution # 2:

COURSE_FEES BASE_FEES SPECIAL_FEES

2 150 110

3 160 170


37/38


37

Solution #3 :

BASE_FEES SPECIAL_FEES BASE_FEES

1 120 123

4 170 235


38/38

You have successfully completed

Oracle SQL Operators

Oracle 10g SQL

sql operators oracle

Documents