RESTRICTING AND SORTING DATA

RESTRICTING AND SORTING DATA

SELECT
FROM
WHERE
ORDER BY

NOTE : Without SELECT and FROM Clause there is no query will be executed.

Where Clause: 

SELECT * FROM Employees;

SELECT Department_id,First_Name,Last_Name, Salary FROM Employees where department_id=90 ;

SELECT First_Name, Salary FROM Employees WHERE First_Name= 'lex';

No Data Found -- CASE SENSITIVE IN DATA IS MANDOTORY

SELECT First_Name, Salary FROM Employees WHERE First_Name= 'Lex';

SELECT First_Name,Hire_Date, Salary FROM Employees WHERE Hire_Date = '05/21/2007';

SELECT First_Name,Hire_Date, Salary FROM Employees WHERE Hire_Date = '05-21-2007';

LOGICAL OPERATORS:

Both AND/OR are used in where class only

AND -- TWO OR MORE CONDITIONS SATISFY
OR  -- EITHER ONE CONDITION IS SATISFIED 

SELECT First_name,Salary FROM Employees Where First_name='Lex' and salary>10000; 

SELECT First_name,Salary FROM Employees Where First_name='Lex' or salary>10000;

RELATIONAL OPERATOR

= < > >= =< |= 

IN        ,  NOT IN
LIKE      ,  NOT LIKE
BETWEEN   ,  NOT BETWEEN 
IS NULL   ,  IS NOT NULL 
ALL 
ANY 

NOTE 

=    Denotes to get only one value
in   Multiple values 

SELECT First_name, Salary from employees where first_name in ('Steven', 'Lex');  

SELECT First_name, Salary from employees where first_name = 'Steven', 'Lex'; 

--ORA-00933: SQL command not properly ended

SELECT First_name, Salary from employees where first_name = 'Steven' and 'Lex'; 

--ORA-00920: invalid relational operator

SELECT First_name, Salary from employees where first_name = 'Steven';

SELECT First_name, Salary from employees where first_name not in ('Steven', 'Lex');  

SELECT First_name, Salary FROM Employees where first_name not in ('Steven', 'Lex');  

SELECT First_name, Salary FROM Employees where first_name like ('Steven', 'Lex');  

ORA-00907: missing right parenthesis

SELECT First_name, Salary FROM Employees where first_name like ('Steven');  

LIKE is used to pattern matching search

% denotes to Something or Nothing

SELECT First_name, Salary FROM Employees where first_name like '%a';  

The Query Shows the ending letter of a;

SELECT First_name, Salary FROM Employees where first_name like '%a%';

The above Query shows the who are all the name having a;  

SELECT First_name, Salary FROM Employees where first_name like '%_a%'; -- 2nd letter of a

SELECT First_name, Salary FROM Employees where first_name like '%_a_i%';-- 2nd letter of a and 4th letter of i

SELECT First_name, Salary FROM Employees where first_name like '%A%r';-- Starts from A and Ends with r  

SELECT First_name, Salary FROM Employees where first_name like 'Ne%'; -- Starts with Ne 

Not Like:

Its opposite of the like operator. 

SELECT First_name, Salary FROM Employees where first_name not like '%_a%'; -- Except 2nd letter of a
SELECT First_name, Salary FROM Employees where first_name not like '%_a_i%';-- Except 2nd letter of a and 4th letter of i
SELECT First_name, Salary FROM Employees where first_name not like '%A%r';-- Except Starts from A and Ends with r  
SELECT First_name, Salary FROM Employees where first_name not like 'Ne%'; -- Except Starts with Ne 

Escape Concepts

Select first_name, email_id from t1 where email_id like '___%' escape'%';

In mail id having underscore.It is represented as  a character suppose we want to search the before the underscore value using escape keyword.

Between

Its belong to range of the two values.

Select first_name, salary from employees where salary between 11000 and 12000;

It will display the range of the salary between the 11000 and 12000.

Select first_name, salary from employees where salary not between 11000 and 12000;

It will display except the  range of the salary between the 11000 and 12000.

Note : the % is not working in the between, not between, in, not in operator

> all

Greater than the greatest value

Select first_name, salary from employees where Salary>15000;

Select first_name, salary from employees where Salary>15000,20000;

ORA-00933: SQL command not properly ended

The greater than compares only one values

Select first_name, salary from employees where Salary>all(15000,20000);

If we need to compare multiples of values use greater than all

>any 

Greater than the lowest value

Select first_name, salary from employees where Salary>any (15000,20000,10000);

It will display the above 10000 salary because Its the lowest value.


Is NULL

Select first_name, salary, commission_pct from employees where commission_pct is null;

ORDER BY 

The ORDER BY keyword is used to sort the result-set in ascending or descending order.

The ORDER BY keyword sorts the records in ascending order by default. To sort the records in descending order, use the DESC keyword.

Select * from employees order by first_name; -- The query retrieves order by using the first_name column

Select * from employees order by 3;-- 3 denotes to last column 

Select * from employees order by first_name desc;

DESC - Descending Unknown values having high priority 
ASC  - Ascending

Select * from employees order by commission_pct desc; 

if not mention the DESC or ASC.  Ascending  will taken as  default.