Oracle Database commonly referred To
As Oracle RDBMS or simply as Oracle. It
is the world's most popular database for
running online transaction processing
data warehousing and mixed database
workloads.
Larry Ellison and his two friends and
former co-workers, Bob Miner and
Ed Oates, started a consultancy called
Software Development Laboratories in
1977. SDL developed the original version
of the Oracle software. The
name Oracle comes from the code-name
of a CIA-funded project Ellison had
worked.
administration
ORACLE DATABASE 11G: SQL
FUNDAMENTALS 1 VOLUME 1
ON THIS WAS ORACLE DATABASE 11G: SQL
COURSE WE FUNDAMENTALS 1 VOLUME 2
HAVE LEARNED
SO MANY ORACLE DATABASE 11G:
THINGS . THE ADMINISTRATION
COURSE WAS WORKSHOP 1 VOLUME 1
120 HOURS
LONG. ORACLE DATABASE 11G:
ADMINISTRATION
COURSE WORKSHOP 1 VOLUME 2
SUBJECT
ORACLE DATABASE 11G:
ADMINISTRATION
WORKSHOP 2 VOLUME 1
ORACLE DATABASE 11G:
ADMINISTRATION
WORKSHOP 2 VOLUME 2
2
install and configure database
Software install in two
step
Installation process. 1st . Server /software
2nd . Dtabase create
(ORCL)
1. Software collection
After 1st installation 2 user ar
2. Installation available
1.Super user (SYS user)
3. Dtabase 2. Human resource (HR)
4. User
5. Multiple object
Sql module Table, view, index,
sequence, synonyms
Pl sql procedure, function, package,
trigger
3
SQL Statement
1.Select
2. Insert
1. Data manipulation language (DML)
3. Udpate
4.Delete
2. Data definition language (DDL)
5.marge
3. Data control language (DCL)
1. Create
4. Transaction control 2. Alter
3. Drop
4. Rename
5. Truncate
6. Comment
1. Commit 1. Grant
2. Rolback 2. Revoke
3. Savepoint
4
Entity Relationship Diagram of project (dba 130)
DEPARTMENT
Department_ID
(PK)
Department_Na
me
SUBJECT TEACHER
Subject_ID (PK) Teacher_ID
Subject_Name (PK)
Semester_ID Teacher_Name
(FK) STUDENT Department_I
Department_ID Student_ID (PK) D (FK)
(FK) Student_Name
Fathers_Name
Mothers_Name
SEMESTER Village SHIFT
Semester_ID Police_Station Shift_ID (PK)
(PK) District Shift_Name
Semester_Name Shift_ID (FK)
Department_ID
(FK)
Semester_ID
(FK)
project script
sys as sysdba
select name from v$database;
conn sys@orcl as sysdba
create tablespace DBA130_TABLESPACE
datafile'D:\app\Administrator\oradata\orcl\DBA130_TA
BLESPACE_DATAFILE.DBF'
size 10M autoextend on;
create temporary tablespace
DBA130_TEMP_TABLESPACE
tempfile'D:\app\Administrator\oradata\orcl\DBA130_TE
MP_TABLESPACE_DATAFILE.DBF'
size 10M autoextend on;
drop user DBA130 cascade;
create user DBA130 identified by 123
default tablespace DBA130_TABLESPACE
temporary tablespace DBA130_TEMP_TABLESPACE;
project script
alter user DBA130
quota 5M on DBA130_TABLESPACE;
create role DBA130_ROLE;
grant create session, create view, create synonym,
resource, dba
to DBA130_ROLE;
grant DBA130_ROLE to DBA130;
conn DBA130/123;
select name from v$database;
drop table Shift cascade constraints;
create table SHIFT
(
shift_id number,
7
project script
shift_name varchar2(20) );
Alter table SHIFT
add constraints shift_shift_id_pk primary key(shift_id);
desc shift;
insert into SHIFT
values(1,'first_shift');
insert into SHIFT
values(2,'second_shift');
commit;
select * from shift;
drop table DEPARTMENT cascade constraints;
create table DEPARTMENT
(
department_id number,
department_name varchar2(25) ); 8
project script
alter table DEPARTMENT
add constraints
department_department_id_pk primary
key(department_id);
desc DEPARTMENT;
insert into DEPARTMENT
values (1,'computer');
insert into DEPARTMENT
values (2,'civil');
insert into DEPARTMENT
values (3,'architecture');
insert into DEPARTMENT
values (4,'power');
insert into DEPARTMENT
values (5,'electrical');
insert into DEPARTMENT
values (7,'RS');
9
project script
commit;
select * from DEPARTMENT;
--************************************************
drop table SEMESTER cascade constraints;
create Table SEMESTER
(
semester_id number,
semester_name varchar2(20)
);
alter table SEMESTER
add constraints semester_semester_id_pk primary
key(semester_id);
desc SEMESTER;
insert into SEMESTER
values(1,'First Semester');
insert into SEMESTER
values(2,'Second_Semester');
1
0
project script
insert into SEMESTER
values(3,'Third Semester');
insert into SEMESTER
values(4,'Fourth Semester');
insert into SEMESTER
values(5,'Fifth Semester');
insert into SEMESTER
values(6,'Sixth Semester');
insert into SEMESTER
values(7,'Seventh Semester');
insert into SEMESTER
values(8,'Eight Semester');
select *
from SEMESTER;
--*************** STUDENT ***************
drop table STUDENT cascade constraints;
create table STUDENT 1
1
project script
(
student_id number,
student_name varchar2(25),
fathers_name varchar2(25),
mothers_name varchar2(25),
village varchar2(25),
police_station varchar2(25),
district varchar2(25),
shift_id number,
department_id number,
semester_id number
);
alter table STUDENT
add constraints student_student_id_pk
primary key(student_id);
alter table STUDENT
add constraints student_shift_id_fk foreign
key(shift_id)
references SHIFT(shift_id);
1
2
project script
alter table STUDENT
add constraints student_department_id_fk foreign
key(department_id)
references DEPARTMENT(department_id);
alter table STUDENT
add constraints student_semester_id_fk foreign
key(semester_id)
references SEMESTER(semester_id);
desc student;
insert into STUDENT
values
(1,'Kausar','Kader','Aleya','Katagong','Jorarganj','chitt
agong',1,1,8);
insert into STUDENT
values
(2,'Sharmin','Shofiullah','Jothsnara','Chor
Khoaj','Sonagazi','feni',2,1,8);
1
3
project script
insert into STUDENT
values
(3,'Halim','Samshu','Sakina','west joar','feni','feni',1,2,6);
insert into STUDENT
values
(4,'jahed','Jahangir','Salima','south
joar','feni','feni',2,2,7);
insert into STUDENT
values
(5,'Mashuk','Mamun','Munni','Azamnagar','Chhagolnai
ya','feni',1,3,5);
insert into STUDENT
values
(6,'Aminul','Anwar','Beauty','koiyara','Chhagolnaiya','chi
ttagong',2,3,4);
insert into STUDENT
values
(7,'Enamul','Shahab','Roksana','Habildar
basha','maizdi','noakhali',1,4,7);
1
4
project script
insert into STUDENT
values
(8,'Lina','Onath bondhu','Bobita','Chor
parboti','maizdi','noakhali',2,4,8);
insert into STUDENT
values
(9,'Pinky','Sattar','Shamima','maizdi','noakhali','noakhali
',1,5,7);
insert into STUDENT
values
(10,'Arafat','Kashem','Haseena','Koiyara','Chhagolnaiay
a','Chhagolnaiaya',2,5,4);
insert into STUDENT
values
(11,'Kamal','jamal','fatema','olinogor','Jorarganj','chitta
gong',1,6,1);
insert into STUDENT
values
(12,'Kasem','jalil','fahema','katagong','Jorarganj',
1
5
project script
'chittagong',2,6,2);
commit;
select * from student;
--*****************************************
drop table TEACHER cascade constraints;
create table TEACHER
(
teacher_id number,
teacher_name varchar2(25),
department_id number
);
alter table TEACHER
add constraints teacher_teacher_id_pk primary
key(teacher_id);
alter table TEACHER
add constraints teacher_department_id_fk foreign
key(department_id)
references DEPARTMENT(department_id);
1
6
project script
desc TEACHER;
insert into TEACHER
values(1,'S M Hamidul Hoque',1);
insert into TEACHER
values(2,'Jamshed Alam',2);
insert into TEACHER
values(3,'Jaman Ahmed',3);
insert into TEACHER
values(4,'Shamshur Rahman',4);
insert into TEACHER
values(5,'Shohid Ul Alam',5);
insert into TEACHER
values(6,'MD Sadeq',6);
insert into TEACHER
values(7,'Rafiqul Hoque',7);
commit;
1
7
project script
select *
from TEACHER;
--***************** SUBJECT ***********
drop table SUBJECT cascade constraints;
create table SUBJECT
(
subject_id number,
subject_name varchar2(30),
department_id number,
semester_id number
);
alter table SUBJECT
add constraints subject_subject_id_pk primary
key(subject_id);
alter table SUBJECT
add constraints subject_semester_id_fk foreign
key(semester_id)
references SEMESTER(semester_id);
1
8
project script
alter table SUBJECT
add constraints subject_department_id_fk foreign
key(department_id)
references DEPARTMENT(department_id);
desc subject;
insert into SUBJECT
values(1,'English',7,3);
insert into SUBJECT
values(2,'Programming Language',1,5);
insert into SUBJECT
values(3,'Estimating and Custing',2,5);
insert into SUBJECT
values(4,'Auto Cad',3,5);
insert into SUBJECT
values(5,'Power Plants Process',4,5);
1
9
project script
insert into SUBJECT
values(6,'Electrical Components',5,5);
insert into SUBJECT
values(7,'Electronics Circuits and machines',6,5);
commit;
select *
from SUBJECT;
select table_name
from user_tables;
-- Teacher_view ---------
create or replace view Teacher_view
as
select teacher_id,teacher_name,teacher.department_id,
department_name
from teacher,department
where
teacher.department_id=department.DEPARTMENT_ID
order by teacher_name; 2
0
project script
desc Teacher_view;
select *
from Teacher_view;
------ Subject_View -------
create or replace view Subject_View
As
select
subject_id,subject_name,subject.SEMESTER_ID,SEM
ESTER_name,
subject.DEPARTMENT_ID,DEPARTMENT_name
from subject,SEMESTER,DEPARTMENT
where
subject.SEMESTER_ID=SEMESTER.SEMESTER_ID
and
subject.DEPARTMENT_ID=DEPARTMENT.DEPARTM
ENT_ID
order by subject_name;
desc Subject_View;
2
1
project script
select *
from Subject_View;
----- Student_view ------
create or replace view Student_view
as
select
Student_ID,Student_Name,Fathers_Name,Mother
s_Name,
Village,Police_Station,District,
STUDENT.shift_id,shift.SHIFT_NAME,
STUDENT.department_id,department_name,
STUDENT.SEMESTER_ID,SEMESTER_NAME
from STUDENT,shift,DEPARTMENT,SEMESTER
where STUDENT.shift_id=shift.shift_id
and
STUDENT.department_id=department.departme
nt_id
and
2
2
project script
STUDENT.SEMESTER_ID=SEMESTER.SEMEST
ER_ID
order by Student_Name;
desc Student_view;
select *
from Student_view;
2
3
group members
1.Mujahidul islam
2. Abdullah al kawsar
3. Sharier arman shown
4.Sharmin aktar
5. Rabia borsi
2
4
2
5
2
6
2
7
2
8
2
9
