Data Query Language ( DQL )

Oracle not only provides two diff language to manipulate its database but also provides an environment which acts like an interface user and environment.

An environment is known as SQL*PLUS.

Whenever SQL command is issued SQL*PLUS it will navigate the database, database process the command and same the output back to SQL*PLUS

NOTE:

The basic diff between environment and editor are Editor is an application only responsible to edit text whereas environment is an application which is responsible to manipulate to other application and has its own command itself.

SQL*PLUS is environment as because user can write two type of command

i. SQL to manipulate database
ii. Its own command to manipulate itself

Not only ORACLE provides SQL*PLUS to internet which ORACLE database but as it provides another environment called ISQL.

The basic diff bet them is SQL*PLUS is can only be operated from windows but ISQL *PLUS can operated with an operating system having browser.

Whenever ORACLE is invoked for the first time shows a log on screen having three diff. Options

1. User name

2. Password
3. Hosting - it is an option which is requires an user is working under multi server architecture.

SELECT

It is powerful query statement responsible for extract data from table or more than one table.

SYNTAX
Select/[distinct]/<*/col1,col2,…….n>from<table name>

Select statement can perform 2 types of jobs.

i. Selection
ii. Projection – it can be possible in 2 different manners.
  a. Column wise projection or column restriction
  b. Row wise projection or row restriction

Column restriction is a concept through which those many attributes are displayed which are mentioned in the list.

Eg _ select empno, ename, sal from emp;

Row restriction is a concept through which those many records are displayed where a condition is satisfied.

NOTE:

―*‖ is represents all the columns of a table. Each SQL statement is terminated by (;).

Working with column alias:

Column alias is concept through which user can provide duplicate name to an existing column. This concept is empery nature.
It can be given using 3 diff. methods
i. Select empno NO,ename NAME, sal SALARY from emp;
ii. Select empno as NO,ename as NAME,sal as SALARY from emp;
iii. Select empno as ― employee no‖,ename as‖ NAME‖,sal as salary from emp;

Whenever column alias is represented in multiple word it has to be kept in a double quotation (― ‖)mark.

Working with column concatenation:

Column concatenation is concept through which user can concatenation two or more ten two column to get a customized output. to do so SQL provides an operator called || .

Select ename ||‘ is working as‘||job||‘and getting salary‘||Sal as ―job profile‖.

Working with virtual column (arithmetic operation and select statement):

Select statement support arithmetic operation that means formula concept is supported select statement and the outcome of a formula is a virtual column.

Advantages of virtual column:

è Supports proper memory management

Select empno, ename, sal, sal*.1 as TA, sal*.11 as DA,sal*.12 as HRA, (sal+(sal*.1)+(sal*.11)+(sal*.12)) as ―Total salary‖ from emp;

In the above example TA, DA, HRA, Total salary is virtual column, outcome of respective formula.

Row restriction through select statement:
It is a concept through which those many rewords are displayed where a condition is satisfied

To do so select statement provides a clause ‗where‘

Syntax of select using where

Select <exp>from<t_name> [where [condition];

NOTE:

To restrict depending upon condition min requirement is an operator.
SQL provides the following operator to restrict record depending upon following condition

1. Relational operator (>, <,>=, <>, ! =,=)

2. Logical operator(AND,OR,NOT)
3. Arithmetic operator (+,-,*,/)

Ex

Select * from where Sal>2000;
Select ename, job, sal from emp where sal>0 and sal<3000
Select * from emp where dept! =10;
Select * from emp where job=‘salesman‘ or job=‘analyst‘ or job =‘peon‘

NOTE :

SQL statements are case insensitive but a string values or a character value compared with a column is always case sensitive.

Above examples are the example of conventional operator responsible to restrict record depending upon condition upon condition apart from that some exclusive operator are given by SQL to restrict record depending upon conditions those are

Ø Between and

Ø In
Ø Like
Ø Is null

BETWEEN AND:

This is an operator responsible to display the o/p between a given ranges of value.

This operator can be operated a 2 type of values.
i.e. number and date.
e.g. – select * from emp where Sal between 2000 and 3000;
Select * from emp where sal not between 2000 and 3000;

IN:

―IN‖ operator is also known as ‗INLIST‘ operator, which is treated as advanced version of ‗‖R‖.
This operator can be operated on any type of value

Eg. -

Select * from emp where job in (‗SALESMAN‘,‘ANALYST‘,‘PEON‘)
Select * from emp where job not in (‗SALESMAN‘,‘ANALYST‘,‘PEON‘)

LIKE:

It is powerful query operator required to extract data from a table depending on the character position. This operator can be operated on 2 types of values

Like operator provides 2 wild card character i.e.

Ø _(underscore) to represent single character

Ø %( percentile) to resent multiple character

Q. Write a query which will display those records whose name starts with ‗S‘.

Select * from emp where ename like‗s%‘;

Q. Write a query which will display those records whose 3rd char is‘I‘ and last char is ‗h‘.

Select * from emp where ename like ‗__i%h‘;

NULL:

“It is a special type of value which is unassigned or inapplicable”. To extract data depending on NULL value user can use is NULL operator.

Q. writes a query which will display those records whose comm is null

Select * from emp where comm is null;
Select * from emp where comm is not null;