Debre Birhan University
College of Computing
Department of Software Engineering
Group Assignment
Course title: Database Administration
Prepared by
Name ID Number
Yenew Ayenew 1350
Selmon Ketema 1299
Kalab Beyene 1231
Mickel Selamsew 1266
Mohamed Seid 1275
Submitted to: Ins simegnew
Submission date: 10/06/2013
1. The difference between oracle database and sql server
Syntax: Syntax:
USE database_name CONNECT user_name/password
SET role
CREATE DATABASE student CREATE DATABASE student
When we run it the output become as
follow
When we run it the output become as follow
Detail Difference Between the two is shown below
create table student
(
stuId int primary key,
studName varchar(20),
studEmail varchar(30),
studjob varchar(24),
studAge varchar(40)
);
select *from studd;
insert into stud values(4,'yonas','yo@[Link]','assistant',10);
When we execute the table student using DESC command in
SQL Server
Doesn’t show
Each column's name
Whether or not null values are allowed (NULL or NOT NULL) for each column
Data type of columns, for example, CHAR, DATE, LONG, LONGRAW, NUMBER,
RAW, ROWID, VARCHAR2 (VARCHAR), or XMLType
Precision of columns (and scale, if any, for a numeric column)
When you do a DESCRIBE, VARCHAR columns are returned with a type of
VARCHAR2.
But in Oracle Database server
Example
create table student
(
stuId int primary key,
studName varchar(20),
studEmail varchar(30),
studjob varchar(24),
studAge varchar(40)
);
insert into sttd values (2,'yenew','ye@[Link]','coderr',23);
select *from student;
When we click CVS Export it link to excel as follow
When we execute the table student using DESC command in
SQL Server
Shows
Each column's name
Whether or not null values are allowed (NULL or NOT NULL) for each column
Data type of columns, for example, CHAR, DATE, LONG, LONGRAW, NUMBER,
RAW, ROWID, VARCHAR2 (VARCHAR), or XMLType
Precision of columns (and scale, if any, for a numeric column)
When you do a DESCRIBE, VARCHAR columns are returned with a type of
VARCHAR2.
desc student
When we run it is displayed as follow
create table personn
(
pId int primary key,
pName varchar (20),
);
select*from personn
insert into personn values(3,'selmon');
rollback;
When we execute rollback the output become
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
But
It needs begin transaction execute before rollback execution it looks as follow
create table personn
(
pId int primary key,
pName varchar (20));
select*from person
insert into personn values(3,'selmon');
rollback;
begin transaction
insert into personn values(5,'alemu');
rollback;
Now when we execute rollback the last one will omitted shown as follow
In oracle database server
In sql server when we insert values using INTO keyword values are
inserted without any problem
create table employ
(
EmployId int primary key,
EmployName varchar(10),
EmployAge int
);
insert employ values(1,'yenew',23);
But in oracle database server when we insert values to the table without
using INTO keyword the output become as follow
Note:-using comma at the end or before closing parenthesis in oracle display error or the query
doesn’t run shown as follow But in sql server it execute successfully.
Microsoft SQL Server supports SELECT statements that do not have a
FROM clause. This can be seen in the following example
SELECT getdate()
Example:-
create table coursee
(courseTitle varchar(20),
courseCode varchar(30),);
SELECT getdate()
When we execute SELECT getdate()the output becom as follow
2021-02-16 [Link].187
Oracle does not support SELECTs without FROM clauses. However,
Oracle provides the DUAL table which always contains one row. Use
the DUAL table to convert constructs such as the preceding one.
SELECT sysdate FROM dual;
KEY DIFFERENCE
Oracle runs on a wide variety of platforms while the SQL server can install on the
Windows server only.
Oracle supports star query optimization while SQL server doesn’t offer query
optimization.
In oracle, values do not change before commit whereas in SQL Server values are changed
even before commit.
Oracle allows rollback during the transaction process whereas SQL server doesn’t allow
rollback in the transaction process.
Oracle supports many "Schemas" with the instance whereas SQL server offers "Schemas"
within each user database.
Oracle allows database, full, file-level, incremental & differential backups on the other
hand, SQL server allows full, partial, and incremental backups.
Oracle uses both "after" and "before" triggers whereas SQL server mostly uses only
"after" triggers.
2. The difference between Oracle Database 9i, 10g, 11g and 12c
With Oracle Database 9i and 10g now both out of support, you should be thinking about an
oracle Database Upgrade to oracle 11g or 12c?
With each release there is always improvement and new features. Here are some key features of
Oracle Database 9i, 10g and 11g;
Oracle 9i Oracle10g Oracle11g
Further RAC Improvements
RAC Introduced RAC Improvements - Faster failover of services
- Providing High – improved cluster ware - Grid Plug &Play allowing seamless
availability - Introduction of VIP’s additions/removal of nodes
for RAC
Data Data Guard improvements Active Data Guard added
Guard enhancements - Real-time apply and log - Standby Database can now be queried
- Detection of gasps compressions whilst redo apply is active
and automatic resolution - Fast start failover added
Backup and Recovery Data Pump added Improved Manageability
- Block level recovery - Extract and import data - ASM Cluster File System (ACFS)
- Improved reporting faster than ever Introduced
and automation - Virtual Columns added
Oracle Database 12c - what is new?
There are a host of new features that are now available with Oracle database 12c, including
multitenant architecture which enables you to run pluggable databases in a multitenant container
database. There are also many improvements in areas such as Big Data, Data security and
Storage optimization, which are key areas which will help drive future business growth.
