Oracle SQL: March 2015

Monday, March 16, 2015

Using Oracle TRIM Function.

Oracle TRIM function are used to remove all specific characters either from the beginning, ending or both side of strings.

Note :- trim set should have only one character.

Syntax :
----------
TRIM( [ [ LEADING | TRAILING | BOTH ] trim_character FROM ] string )

LEADING :- Remove trim_character from the front of string.

TRAILING :- Remove trim_character from the end of string.

BOTH :- Remove trim_character from the both (start and end) side of string.

SQL> SELECT TRIM('K' FROM 'KINKGK') RESULT FROM DUAL;

RESU
----
INKG

SQL> SELECT TRIM( LEADING 'K' FROM 'KINKGK') RESULT FROM DUAL;

RESUL
-----
INKGK

SQL> SELECT TRIM( BOTH 'K' FROM 'KINKGK') RESULT FROM DUAL;

RESU
----
INKG

SQL> SELECT TRIM( TRAILING 'K' FROM 'KINKGK') RESULT FROM DUAL;

RESUL
-----
KINKG

SQL>

Friday, March 13, 2015

Shorting Rows using ORDER BY Clause.

ORDER BY Clause are used to short retrieved data. The default shorting is ascending order. If you want to short data in descending order you have to write ORDER BY DESC.

1.

SQL> select last_name, salary, department_id, commission_pct
2 from employees
3 where last_name like 'K%'
4 order by salary;

LAST_NAME SALARY DEPARTMENT_ID COMMISSION_PCT
------------------------- ---------- ------------- --------------
Khoo 3100 30
Kumar 6100 80 .1
Kaufling 7900 50
King 10000 80 .35
Kochhar C 17000 90
King 24000 90

6 rows selected.

2.

SQL> select last_name, salary, department_id, commission_pct
2 from employees
3 where last_name like 'K%'
4 order by salary desc;

LAST_NAME SALARY DEPARTMENT_ID COMMISSION_PCT
------------------------- ---------- ------------- --------------
King 24000 90
Kochhar C 17000 90
King 10000 80 .35
Kaufling 7900 50
Kumar 6100 80 .1
Khoo 3100 30

6 rows selected.

3.

SQL> select last_name, salary, department_id, commission_pct
2 from employees
3 where last_name like 'K%'
4 order by 2;

LAST_NAME SALARY DEPARTMENT_ID COMMISSION_PCT
------------------------- ---------- ------------- --------------
Khoo 3100 30
Kumar 6100 80 .1
Kaufling 7900 50
King 10000 80 .35
Kochhar C 17000 90
King 24000 90

6 rows selected.

4.

SQL> select last_name, salary, department_id, commission_pct
2 from employees
3 where last_name like 'K%'
4 order by 2 desc;

LAST_NAME SALARY DEPARTMENT_ID COMMISSION_PCT
------------------------- ---------- ------------- --------------
King 24000 90
Kochhar C 17000 90
King 10000 80 .35
Kaufling 7900 50
Kumar 6100 80 .1
Khoo 3100 30

6 rows selected.

SQL>

1 and 3 number query working similar. 2 and 4 number query also working similar.

Using Comparison Condition to filter rows.

Oracle provides the following comparison operator to filter rows.

Operator	Operation
=	Equal
>	Greater Than
<	Less than
>=	Greater Than or Equal
<=	Less than or Equal
BETWEEN (Lowest Value) AND (Highest Value)	Between Two Value
IN	List of value
LIKE	Match pattern
IS NULL	Value is null
IS NOT NULL	Value is not null.

----- This query shows data from employees table those employees department id is 90.

SQL> select last_name, salary, department_id

2 from employees

3 where department_id=90;

LAST_NAME SALARY DEPARTMENT_ID

------------------------- ---------- -------------

King 24000 90

Kochhar C 17000 90

De Haan 17000 90

SQL>

-----

SQL> select last_name, salary, department_id

2 from employees

3 where salary > 20000;

LAST_NAME SALARY DEPARTMENT_ID

------------------------- ---------- -------------

King 24000 90

SQL>

-----

SQL> select last_name, salary, department_id

2 from employees

3 where salary >=24000;

LAST_NAME SALARY DEPARTMENT_ID

------------------------- ---------- -------------

King 24000 90

SQL>

-----

SQL> select last_name, salary, department_id

2 from employees

3 where salary between 17000 and 20000;

LAST_NAME SALARY DEPARTMENT_ID

------------------------- ---------- -------------

Kochhar C 17000 90

De Haan 17000 90

SQL>

-----

SQL> select last_name, salary, department_id, commission_pct

2 from employees

3 where department_id = 90

4 and commission_pct is null;

LAST_NAME SALARY DEPARTMENT_ID COMMISSION_PCT

------------------------- ---------- ------------- --------------

King 24000 90

Kochhar C 17000 90

De Haan 17000 90

SQL>

-----

SQL> select last_name, salary, department_id, commission_pct

2 from employees

3 where department_id in (80,90);

LAST_NAME SALARY DEPARTMENT_ID COMMISSION_PCT

------------------------- ---------- ------------- --------------

King 24000 90

Kochhar C 17000 90

De Haan 17000 90

Russell 14000 80 .4

Partners 13500 80 .3

Errazuriz 12000 80 .3

------ This query show data from employees those last name start with K.

SQL> select last_name, salary, department_id, commission_pct

2 from employees

3 where last_name like 'K%';

LAST_NAME SALARY DEPARTMENT_ID COMMISSION_PCT

------------------------- ---------- ------------- --------------

Kaufling 7900 50

Khoo 3100 30

King 10000 80 .35

King 24000 90

Kochhar C 17000 90

Kumar 6100 80 .1

6 rows selected.

Thursday, March 12, 2015

Using concatenation operator.

Concatenation operator are used to link column or string with another column or strings.

SQL> select 'Employee Name '||first_name||' '||last_name "Name of Employee" , salary from employees;

Name of Employee SALARY
------------------------------------------------------------ ----------
Employee Name Steven King 24000
Employee Name Neena Kochhar C 17000
Employee Name Lex De Haan 17000
Employee Name Alexander Hunold 9000
Employee Name Bruce Ernst 6000
Employee Name David Austin 4800
Employee Name Valli Pataballa 4800
Employee Name Diana Lorentz 4200
Employee Name Nancy Greenberg 12008
Employee Name Daniel Faviet 9000
Employee Name John Chen 8200

11rows selected.

SQL>

Using column Alias.

Column alias are used to rename column heading.

SQL> select last_name, salary, salary*2 two_times_salary from employees;

LAST_NAME SALARY TWO_TIMES_SALARY
------------------------- ---------- ----------------
King 24000 48000
Kochhar C 17000 34000
De Haan 17000 34000
Hunold 9000 18000
Ernst 6000 12000
Austin 4800 9600
Pataballa 4800 9600
Lorentz 4200 8400
Greenberg 12008 24016
Faviet 9000 18000
Chen 8200 16400

11 rows selected.

SQL> select last_name, salary, salary*2 as two_times_salary from employees;

LAST_NAME SALARY TWO_TIMES_SALARY
------------------------- ---------- ----------------
King 24000 48000
Kochhar C 17000 34000
De Haan 17000 34000
Hunold 9000 18000
Ernst 6000 12000
Austin 4800 9600
Pataballa 4800 9600
Lorentz 4200 8400
Greenberg 12008 24016
Faviet 9000 18000
Chen 8200 16400

11 rows selected.

SQL> select last_name, salary, salary*2 "Two Times Salary " from employees;

LAST_NAME SALARY Two Times Salary
------------------------- ---------- -----------------
King 24000 48000
Kochhar C 17000 34000
De Haan 17000 34000
Hunold 9000 18000
Ernst 6000 12000
Austin 4800 9600
Pataballa 4800 9600
Lorentz 4200 8400
Greenberg 12008 24016
Faviet 9000 18000
Chen 8200 16400

11 rows selected.

Arithmetic Operation In SQL.

SQL> select last_name, salary, salary*2 from employees;

LAST_NAME SALARY SALARY*2
------------------------- ---------- ----------
King 24000 48000
Kochhar C 17000 34000
De Haan 17000 34000
Hunold 9000 18000
Ernst 6000 12000
Austin 4800 9600
Pataballa 4800 9600
Lorentz 4200 8400
Greenberg 12008 24016
Faviet 9000 18000

10 rows selected.

In this query third column showing two times of employee salary.

What is NULL.

NULL is a value that is unavailable, unassigned, unknown or inapplicable. If a column in a row has no value, then the column we can said null value.

Arithmetic Operation With NULL : All types of arithmetic operation with null value result will be null.

Count function with NULL : Count (EXP) function return the result only for not null value. COUNT(*) return the result with null and not null values.

Select specific column from table.

SQL> select department_id, department_name from departments;

DEPARTMENT_ID DEPARTMENT_NAME
------------- ------------------------------
10 Administration
20 Marketing
30 Purchasing
40 Human Resources
50 Shipping
60 IT
70 Public Relations
80 Sales
90 Executive
100 Finance
110 Accounting
11 rows selected.

SQL>

Selecting all row and column from table.

SQL> conn hr/hr;
Connected.
SQL> select * from departments;

DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
10 Administration 200 1700
20 Marketing 201 1800
30 Purchasing 114 1700
40 Human Resources 203 2400
50 Shipping 121 1500
60 IT 103 1400
70 Public Relations 204 2700
80 Sales 145 2500
90 Executive 100 1700
100 Finance 108 1700
110 Accounting 205 1700

11 rows selected.

SQL>

About SQL.

About SQL: - SQL stands for structured query language. SQL are used for communicate with the database server. SQL has the following types.

Statement	Description
SELECT	Retrieves data from the database
INSERT UPDATE DELETE MERGE	Enters new rows, changes existing rows, and removes unwanted rows from tables in the database, respectively. Collectively known as data manipulation language (DML).After DML need to execute COMMIT to change save in your oracle database.
CREATE ALTER DROP RENAME TRUNCATE	Sets up, changes, and removes data structures from tables. Collectively Known as data definition language (DDL). Auto COMMIT. After execute any DDL statement database change will be auto save.
COMMIT ROLLBACK SAVEPOINT	Manages the changes made by DML statements. Changes to the data can be grouped together into logical transactions.
GRANT REVOKE	Gives or removes access rights to both the Oracle database and the structures within it. Collectively known as data control language (DCL).

Connect with Oracle Database.

Microsoft Windows [Version 6.1.7601]
Copyright (c) 2009 Microsoft Corporation. All rights reserved.

C:\Users\rajib.pradhan>sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Fri Mar 13 00:39:38 2015

Copyright (c) 1982, 2010, Oracle. All rights reserved.

SQL> conn /as sysdba
Connected.
SQL> alter user hr identified by hr account unlock;

User altered.

SQL> conn hr/hr
Connected.
SQL> show user
USER is "HR"

SQL>

Installing Oracle 10g release 2 database software.

Installing Oracle 10g release 2 database software.

1. Go to your disk where you have Oracle Database Software (Assume I have in disk F)

2. You ensure that all of file in your disk like

3. Now double click in setup.exe file.

You can see now this screen. This processes checking your computer hardware and software configuration. If your computer configuration is ok then you can see this screen.

4. Choose Basic Installation:
Select this option to quickly install Oracle Database 10g. This method requires minimal user input. It installs the software and optionally creates a general-purpose database based on the information you provide.
For basic installation, you specify the following:
Oracle Home Location Enter the directory in which to install the Oracle Database 10g software. You must specify a new Oracle home directory for each new installation of Oracle Database 10g. By default select your drive where you have maximum free space. You can change this directory rename C

Installation Type Select Enterprise Edition. ( By default no need to change)

Global Database Name This is your database name (By default oracle). If you want to change your database name you can change it.

Database password Type your database password. Don’t lose this password, since you will need it to connect to the database server.(sys is my password)

5. Now click next. You can see this screen

Wait some while. Some processes are executed in here. After processes complete click Next then you can see this screen.

A summary screen appears showing information such as your global settings, space requirements and the new products to be installed. Click Install to start the installation..

7. The Install window appears showing installation progress.

This screen ensure you that all of file copy in your directory.(Just wate and see process no need to do)

After this screen you can see Configuration Assistants window appears. This window lists the configuration assistants that are started automatically. .(Just wate and see process no need to do)

After this screen you can see Database Configuration Assistants screen this screen ensure you that successfully create your database. .(Just wate and see process no need to do)

8. After this screen you can see this screen.

Click Ok

9. This is last screen(End of Instillation)

Click Exit now you get a alert message Click Yes.