First Lecture Oracle(Sql)

Step 1)
Installation and Configuration of Oracle 10g XE.
Step 2)
After Installation create user account using Database Administrative account.
(This can be easily done using Oracle Database XE home page.Please Refer Document given at the end of the article for creating new user account using XE homepage)

Step 3)
Login with User account and Create a simple table

create table student_table (name varchar2(10),roll number(3));

Insert some values into it.
Insert some duplicate values,null values.
Explain how to avoid it by using primary and not null key.

Step 2:Create employee and department table.

create table employee_table
(
employee_id number(5) constraints empidpk primary key,
employee_name varchar2(10)constraints empnamenn not null,
hire_date date,
salary number(5),
dept_no number(2) references department_table(dept_no)
);

Explain why the table creation is failed?

create table department_table
(
dept_no number(2) constraints dept_idpk primary key,
dept_name varchar2(10) constraints dept_namenn not null,
dept_location varchar2(15) constraints dept_locnn not null
);

Insert values into Department table:
insert into department_table values (10,'account','mumbai');
insert into department_table values(30,'admin','pune');
insert into department_table values(20,'hr','delhi');

Ask them to modify student table created in step 1 accordingly.
More on this coming soon.

Refer below tutorial for you to quickly up and running using Oracle Database XE by creating a simple application.
http://download.oracle.com/docs/cd/B25329_01/doc/admin.102/b25610/toc.htm

Oracle Server

ORACLE SERVER:
Oracle Server is composed of two major parts
1) Oracle Instance.
2) Oracle Database.

Oracle Server = (Oracle Instance + Oracle Database)

An Oracle instance is a is composed of two major parts
1) Memory structure.
2) Background processes.

Oracle instance = (Memory structure + background processes.)

The typical background processes are

PMON, SMON , MMON ,DBWR ,LGWR ,CKPT ,ARCH etc ..

Click on image to zoom...

(Note:
1>The Instance must be started to access the data in the database.

2>There are several files, processes, and memory structures in an oracle server; however, not all of them are used when processing a SQL statement. Some of them are used to improve the performance of the database, ensure that the database can be recovered in event of a software or hardware error, or perform other tasks necessary to maintain the database.

3>Every time an instance is started, a System Global Area (SGA) is allocated and Oracle background processes are started.

4>Always opens one and only one database

)

View

The Most Important part of View is its Syntax. And understanding its component.

Syntax:

CREATE OR REPLACE VIEW view [FORCE|NOFORCE*][(alices[,alice],…)]

AS sub query

[WITH CHECK OPTION[CONSTRAINT constraint name]]

[WITH READ ONLY [CONSTRAINT constraint name]]




[] : OPTIONAL
*:Default Parameter


The sub query that defines the view can’t contain order by clause However you can use order by to display view contents.

Use of views

• Data Independence.
• Complex queries easy.
• Restrict data access.
• · To present different view of same data.

Delete

· You cant delete a primary key row who has an reference with another table value .

For example.

Delete from department where department_id=10 (Provided there are employees working in department 10)

Will return Integrity constraint violated error.

However

Following statement will easily get executed

Delete from department where department_id=10 (Provided there are no employees working in department 10)

· There is no Delete * from

Merge

Merge Statement:


Description:

• Useful in data warehousing application.
• Update the row if they already exists and Insert the rows if they are not present.
• Avoids Multiple update statements.

syntax:


MERGE INTO table_name table_alias

USING (table|view|sub_query) alias

ON (join condition)

WHEN MATCHED THEN

UPDATE SET

col1 = col_val1,

col2 = col2_val

WHEN NOT MATCHED THEN

INSERT (column_list)

VALUES (column_values);



Example:


MERGE INTO copy_emp c

USING employees e

ON (c.employee_id = e.employee_id)

WHEN MATCHED THEN

UPDATE SET

c.first_name = e.first_name,

c.last_name = e.last_name,

...

c.department_id = e.department_id

WHEN NOT MATCHED THEN

INSERT VALUES(e.employee_id, e.first_name, e.last_name,

e.email, e.phone_number, e.hire_date, e.job_id,

e.salary, e.commission_pct, e.manager_id,

e.department_id);

More coming soon.

Install Oracle XE on linux.

Installation of Oracle XE and configuration.

• If you downloaded the oracle-xe-univ-10.2.0.1-1.0.i386.rpm executable, you would enter this command:
# rpm -ivh downloads/oracle-xe-univ-10.2.0.1-1.0.i386.rpm

The installation displays a status of its progress.
• When prompted, run the following command:
# /etc/init.d/oracle-xe configure




Oracle Database 10g Express Edition Configuration
-------------------------------------------------
This will configure on-boot properties of Oracle Database 10g Express
Edition. The following questions will determine whether the database should
be starting upon system boot, the ports it will use, and the passwords that
will be used for database accounts. Press to accept the defaults.
Ctrl-C will abort.

Specify the HTTP port that will be used for Oracle Application Express [8080]:8888

Specify a port that will be used for the database listener [1521]:1521

Specify a password to be used for database accounts. Note that the same
password will be used for SYS and SYSTEM. Oracle recommends the use of
different passwords for each database account. This can be done after
initial configuration:
Do you want Oracle Database 10g Express Edition to be started on boot (y/n) [y]

Starting Oracle Net Listener...Done
Configuring Database...Done
Starting Oracle Database 10g Express Edition Instance...Done
Installation Completed Successfully.
To access the Database Home Page go to http://127.0.0.1:8888/apex

For Installation guide please refer.

http://www.oracle.com/technology/software/products/database/xe/files/install.102/b25144/toc.htm#CIHHJEHF