Some Commands while we are retrieving the data from the data base (Condition for WHERE/SELECT/FROM etc) ORACLE Queries - BZU PAGES: Find Presentations, Reports, Student's Assignments and Daily Discussion; Bahauddin Zakariya University Multan

**.BZU.** · #1 12-03-2009, 03:32 AM

Some Condition while we are retrieving the data from the data base

consider we have a table of the student. having different records.
we want to display some records according to our own choice.
e.g. we want to show the records of the students whose subject is English we will enter the following statement.

Code:

select *
from student
where subject = 'English';

in the above query

select is for selecting the table column, here * indicates that system will display all the column of the table saved in the student data base

from student means get data from student's table.

where is a simple condition... and it will show only the data of those students whose subject are English saved in data base.

Showing the all Columns and Tables by using desc

Code:

desc table name;

Arithmetic Operations:

we can perform on the table

See this example.

Code:

Select subject,marks,name,marks+5
 from student;

Marks+5 will display a new column in which all the values in the marks table will be display after addition of 5.

Null:

Null is also used in Oracle data Base systems;

Code:

Select *
from student
where marks is null;

no need to explain

Distinct values Display:

We can remove the duplication while displaying the data from the table.

Code:

Select distinct subject
from student;

we can use

Code:

save ABC,

for saving any query in the file format, (ABC is the file name) which we can call by using different queries. like

Quote:

@ABC
Run ABC
Get ABC
Start ABC

Above 4 queries will show the same result.

where ABC is the saved file name. which exist in the BIN folder of your installed Directory...

Between Operation:

It is used to display the records between the some range described in the query.

Consider we have a Data Base table of Emp (Emplyee) and using following query

Code:

select *
from emp
where salary between 3000 and 5000;

This display the record of ranging from 3000 to 5000 salary employees.

In Operator:

Display the record of empno 1,3 and 5 only

Code:

select *
from emp
where empno in (1,2,3);

we will use single quote if we are displaying more that one characters empno.
Consider if table records are saved as 01,03 and 05

Code:

select *
 from emp
where empno in ('01','02','03');

Like Operator:

It is used as wild card / you may say for string searching.

Code:

Select empno,ename
from emp
where ename like 'A%';

Search all elements starting from A

Code:

where ename like '%A' ;  /// it will search all the elements ending on A
where ename like '%A%'; /// it will search all elements containing letter A

Quote:

_

represent any single character while we are searching the data by using like operator.

Code:

Select empno,ename
from emp
where ename like '_A%';

The above query will find all the column where second letter is A,

your practices :

Write the query which display the name of those employees where name of employees containing letter Z;

And operator:

It is used to find out the column where both of the conditions are true

Code:

Select empno,ename
 from emp
 where ename Nazia and Jabeen;

Order By:

Ascending order
Descending order

Order by came last in select clause.

Code:

select empno,ename,sal
from emp
order by sal desc;

note: for ascending order we just command

Code:

order by sal;

no need to put the trailing phrase. because it is the built in function of SQL Plus...

we can use two different columns at a time.

Code:

select empno,ename,sal,city
from emp
order by city,sal desc;

Finished...

Any Question ?