Exploring SELECT statement in Sql

OPERATOR PRECENDENCE

 

The operator precedence in oracle is as:

*, /,+,-

 

QUERRY

 

SQL> select ename, sal, 12*sal+100

2  from emp;

 

 

OUTPUT

 

ENAME            SAL 12*SAL+100

———- ——— ———-

SMITH            800       9700

ALLEN           1600      19300

WARD            1250      15100

JONES           2975      35800

 

Note:

NLL values in an arithmetic expression result to a NULL

 

SE1

Write a query to produce the following output

 

OUTPUT

 

ENAME      12*SAL+COMM

———- ———–

KING

 

COLUMN ALIAS

 

Column alias help us to display any column name in our desired manner. For example we can display the sal column name as Salary to make it easy to understand.

 

QUERRY

 

SQL> SELECT ename NAME, sal Salary

2  from emp;

 

 

OUTPUT

 

NAME          SALARY

———- ———

SMITH            800

ALLEN           1600

WARD            1250

.

.

 

 

If a column alias has a space in between enclose the column alias in double Quotation.

 

SE2 Write and test the following query.

 

SQL> SELECT ename “NAME”, sal*100 ” ANNUAL SALARY”

2  from emp;

 

 

 

Concatenation (||)

 

It is used to display large string outputs.

 

QUERRY

 

 

SQL> SELECT ename ||”||’is a ‘ || job “EMPLOYEE DETAILS”

2  from emp;

 

OUTPUT

 

EMPLOYEE DETAILS

———————–

SMITH is a CLERK

ALLEN is a SALESMAN

 

Comparision operator;

 

=, <,>, <=, >=, <>

 

SE3 Display the ename ,salary and commission of all employees whose salary < commission.

 

Between Operator

 

It is used to display row based on a range of values.

 

SE4  Display the ename and salary of all employees whose salary is between $100 and $1500.

In operator

It is used to test values in  list.

WHERE <field> IN (VAL1,VAL2,VAL N)

QUERRY

 

SE5: Display the empno, ename, salary and manager field of employees whose empnos are in the list

7092, 7566,7788.

 

LIKE Operator

 

This operator is used to perform wild card searches.

 

QUERRY

 

SQL> Select ename

2  from emp

3  where ename like ‘S%’;

 

 

OUTPUT

 

ENAME

———-

SMITH

SCOTT

 

SE6: Display the ename of all employees whose name has an ‘A’ as the second alphabet and end with a ‘N’

AND operator

 

It is used where we want to search with multiple conditions.

 

QUERRY

SE7:  Select the names of all employees who earn  less than or equal to 1100 dollars and work as clerks.

 

 

NOT operator

 

If we want to deselect some particular entries, conditional searching.

 

 

SE8 Study and perform the following querries. Also include the output and your comments.

 

SQL> SELECT ename, job

2  from emp

3  where job NOT IN (‘CLERK’,’MANAGER’)

 

 

 

 

Rules of Precedence

 

Order evaluated

operation

1

All comparision

2

NOT

3

AND

4

OR

 

 

SE9:

Display ename , job, salary of all employees who are either salesman , or president and their salary is greater than 1500.

Sorting by Multiple Columns and column alias

 

SE10:

Perform the following querry and analyse the results.

SQL> SELECT ename,deptno,sal

2  from emp

3  order by deptno,sal desc

4  ;

 

 

OUTPUT

 

ENAME         DEPTNO       SAL

———- ——— ———

KING              10      5000

CLARK             10      2450

MILLER            10      1300

 

QUERRY

 

SQL> select ename,empno,sal*12 “ANNUAL SALARY”

2    FROM emp;

 

 

 

ENAME          EMPNO ANNUAL SALARY

———- ——— ————-

SMITH           7369          9600

ALLEN           7499         19200

WARD            7521         15000

JONES           7566         35700

 

QUERRY

 

SQL> select ename,empno,sal*12 “ANNUAL SALARY”

2  FROM emp

3  order by sal*12;

 

 

OUTPUT

 

ENAME          EMPNO ANNUAL SALARY

———- ——— ————-

SMITH           7369          9600

JAMES           7900         11400

ADAMS           7876         13200

 

 

Case conversion

LOWER(‘SQL COURSE’)=sql course.

UPPER(‘SQL course’)=SQL COURSE.

INITCAP(‘SQL course’)=Sql Course.

 

SE11: Perform and analyse

 

SQL> SELECT empno,ename

2  FROM emp

3  where ename = upper(‘Blake’);

 

 

OUTPUT

 

EMPNO ENAME

——— ———-

7698 BLAKE

 

 

 

ROUNDING OFF

 

We use this command to round off figures to decimal places.

 

 

 

QUERRY

 

SQL> SELECT ROUND(45.923,2), ROUND(45.923,0), ROUND(45.923,0),ROUND(45.923,-1

2  from DUAL

3  ;

 

ROUND(45.923,2) ROUND(45.923,0) ROUND(45.923,0) ROUND(45.923,-1)

————— ————— ————— —————-

45.92              46              46               50

 

SE12:

Display the following output.

 

EMPNO ENAME          SALARY   ROUNDED

——– ———- ———- ———-

7369 SMITH             800       1000

7499 ALLEN            1600       2000

7521 WARD             1250       1000

7566 JONES            2975       3000

7654 MARTIN           1250       1000

……..

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s