Some Important Query in Oracle[Using Literal Character Strings,Duplicate Rows,Eliminating Duplicate Rows] :: Part-3

0

Hi Everybody;

Today we discuss some another important Query.

Using Literal Character Strings

By Using this Character Strings we can add some word. Let see an Example .

SELECT ename||’ is a ‘||job as “Employee Details” from emp;

Output

Employee Details

KING is a PRESIDENT
BLAKE is a MANAGER
CI,ARK is a MANAGER
JONES is a MANAGER
MARTIN is a SALESMAN
_ _ _ _
14 rows selected

The Example on the slide displays names and jobs of all employees. The column has the heading Employee Details. Notice the spaces between the single quotation marks in the select statement.

Select ename ||’: ‘||’1’|| ‘ Monthly salary = ‘||sal Monthly from emp;

MONTHLY

KING: 1 Month salary = 5000
BLAKE: 1 Month salary = 2850
CLARK: 1 Month salary = 2450
JAMES: 1 Month salary = 2975
JONES: 1 Month salary = 1250
MARTIN: 1 Month salary = 1600
ALLEN: 1 Month salary = 1500
_ _ _ _

14 rows selected.

Duplicate Rows

The default display of queries is all rows including duplicate rows.

SELECT deptno from emp;

OUTPUT

DEPTNO

10
30
10
20
_ _ _

14 rows selected.

The example displays all the department numbers from the EMP table. Notice that the department numbers are repeated.

Eliminating Duplicate Rows

Eliminating duplicate rows by using the DISTINCT keyword in select clause.

Select DISTINCT deptno from emp;

OUTPUT

DEPTNO

10
20
30

In the example the emp table actually contains 14 rows but there are only three unique department numbers in the table.

You can specify multiple columns after the DISTINCT qualifier. The DISTINCT qualifier affects all the selected columns and the result represents columns, and the result represents a distinct combination of the columns.

Select DISTINCT deptno, job from emp;

OUTPUT

DEPTNO JOB

10 CLERK
10 MANAGER
10 PRESIDENT
20 ANALYST
_ _ _ _ _

9 rows selected

Share.

About Author

Avatar

Recently complete my graduation from university of information technology and sciences[UITS] in IT

Leave A Reply