Oracle SQL Tutorial -8 Comparative Operators

Hi ,

In this article I will tell you some operators .

Comparison operators When we want to compare data, for example, there are statements that make it easier for us to find the name we are looking for in more than one name.

BETWEEN OPERATOR

  • We use it when we want to find expressions within a certain range . For example , we are looking for people between 1950 and 1964 , so we run the following query on our sql page :

SELECT CUST_FIRST_NAME,CUST_LAST_NAME,CUST_YEAR_OF_BIRTH FROM SH.CUSTOMERS WHERE CUST_YEAR_OF_BIRTH BETWEEN 1950 AND 1964;

IN OPERATOR

SELECT CUST_FIRST_NAME,CUST_LAST_NAME_CUST_ID FROM SH.CUSTOMERS WHERE CUST_ID IN(3228,7673,4115);

LIKE OPERATOR

  • We use the like operator to handle calls using variables .

  • % ” zero or more characters .

  • _ “ specifies only one character .

For example : If we want to list the people with the letter “e” in their name :

SELECT DISTINCT CUST_FIRST_NAME,CUST_LAST_NAME FROM SH.CUSTOMERS WHERE CUST_FIRST_NAME LIKE '%e%';

For example : If we want to see people with the second character ‘ r ‘ in their last name:

SELECT DISTINCT CUST_FIRST_NAME,CUST_LAST_NAME FROM SH.CUSTOMERS WHERE CUST_LAST_NAME LIKE '_r%';

IS NULL OPERATOR

I want to explain this operator with an example . In the query I run below , I wanted to see the non-null marital status of the people in the table and I used IS NOT NULL command .

SELECT DISTINCT CUST_FIRST_NAME,CUST_LAST_NAME,CUST_MARITAL_STATUS FROM SH.CUSTOMERS WHERE CUST_MARITAL STATUS IS NOT NULL;

LOJİK OPERATOR

AND OPERATOR

  • In order for the And operator to return a record, both conditions specified must be correct.

SELECT CUST_FIRST_NAME,CUST_LAST_NAME,CUST_CITY,CUST,CITY_ID FROM SH.CUSTOMERS WHERE CUST_LAST_NAME LIKE '_b%' AND CUST_CITY_ID>=52500;

OR OPERATOR

  • At least one of the two conditions must be true if the Or operator is in operation .

SELECT CUST_FIRST_NAME,CUST_LAST_NAME,CUST_CITY,CUST_CITY_ID FROM SH.CUSTOMERS WHERE CUST_FIRST_NAME='Deb' OR CUST_CITY='Wymondham';

NOT OPERATOR

  • Note is “-non” used to see the operator . Explaining this with an example helps us to understand it more easily .

SELECT DISTINCT CUST_FIRST_NAME,CUST_LAST_NAME FROM SH.CUSTOMERS WHERE CUST_FIRST_NAME NOT LIKE '%b%';

ORDER-BY OPERATOR

When we want to sort the records taken with the select statement , we use them . If not specified , it is sorted from small to large by default . Use of :

SELECT kolon_adi
FROM tablo_adi
ORDER BY kolon_adi ASC | DESC
  • ASC : For sorting in ascending direction .

  • DESC : Used to sort in descending direction .

  • It is written after the SELECT statement .

SELECT DISTINCT CUST_FIRST_NAME,CUST_LAST_NAME,CUST_YEAR_OF_BIRTH FROM SH.CUSTOMERS ORDER BY CUST_YEAR_OF_BIRTH ASC;

A different example :

SELECT EMPLOYEE_ID,JOB_ID,AVG(SALARY),COUNT(*)KISISAYISI FROM ADMIN.NEW_TABLE GROUP BY EMPLOYEE_ID,JOB_ID ORDER BY EMPLOYEE_ID;

 

See you in my next post.

About Melike Duran

Leave a Reply

Your email address will not be published. Required fields are marked *