Task I: Basics of DBMS Packages (Oracle)

• Various RDBMS packages available in Market

• Introduction to ORACLE and oraclelive.com compiler
• Features of ORACE and Roadmap of various Versions
• SQL Fundamentals
a. DDL
b. DML
c. DCL
d. TCL  Introduction to various Data Types.

RDBMS Database

A relational database management system (RDBMS) stores data in a tabular form where

a column represents a property and each row in a table represents a record. RDBMS

allows Create, Read, Update, and Delete (CRUD) operations. Structured Query

Language (SQL) is the language that is used to query, update, and delete data in

relational database management systems (RDBMS). SQL is a standard query language.

SQL language queries are also known as SQL commands or SQL statements.

Here is the list of the top 10 most popular RDBMSs are:

1. Oracle

2. MySQL

3. SQL Server

4. PostgreSQL

5. IBM DB2

6. Microsoft Access
7. SQLite

8. MariaDB
9. Informix
 10. Azure SQL

Oracle
Oracle Database is a relational database management system (RDBMS) developed by

Oracle Corporation. It is a software system that allows users to store, organize, and retrieve

large amounts of data efficiently and securely.

The Oracle Database is designed to run on various operating systems, including Windows,

Unix, and Linux. Businesses and organizations of all sizes use it for various applications,

such as e-commerce, customer relationship management, supply chain management, and

data warehousing.

The Oracle Database offers advanced features such as high availability, scalability,

security, and performance tuning, making it a popular choice for large-scale and mission-

critical applications. It also supports various programming languages and interfaces,

allowing developers to build custom applications that can access and manipulate the

database.

Oracle Live SQL

Oracle Live SQL is a web-based SQL editor with an Oracle database built-in. It’s

maintained by Oracle and allows you to write and run SQL statements easily without

having to set up your own Oracle database.

As of October 2022, Oracle Live SQL runs Oracle database version 19c Enterprise Edition
–

19.14.0.0.0.

How to Access?
You can access Oracle Live SQL by going to livesql.oracle.com.

You’ll see a page that looks like this:

The main feature of this page is to allow you to search or browse for tutorials. We’ll cover

this later in the guide, but Oracle Live SQL includes a range of tutorials to help you learn

specific features of Oracle, where you can read instructions and run the SQL right in the

browser.

You can also just access an SQL editor by clicking Start Coding Now.

However, you’ll need to be logged in to do this.

Once you’ve signed in, this is the main Oracle Live SQL screen you’ll see.
There is a range of things on the screen:

Sidebar: On the left, you’ll see a sidebar that includes a few menu items such as Home,
My

Session, and Schema. We’ll cover these options later in this guide. If you don’t see this

sidebar, you can click on the hamburger menu to show and hide it.

SQL Worksheet: the top part of the main area of the screen is the SQL Worksheet. You

can write and run SQL statements here.

Output: the output of your SQL statements is shown at the bottom of the screen. This can

be resized up and down to make more room for the SQL or more room for the output.

Buttons: on the top right of the screen you can see a few buttons:

• Clear: clear the SQL in the worksheet

• Find: find a string in your SQL worksheet

• Actions: view session details, reset your session, or set the maximum rows

preference.
 • Save: save your SQL worksheet

• Run: run the script

We’ll learn more about these buttons later in the guide.

Footer: on the bottom of the screen, you can see some information about the tool: what

version of Oracle Live SQL is published, what Oracle database version it uses, and some

links to documentation and other tools.

Key features of Oracle Database are:

• Scalability: Oracle Database is designed to scale from small systems to large

enterprise deployments, providing support for multiple processors, storage systems,

and network configurations.

• High availability: The database has built-in mechanisms for data protection and

disaster recovery, including backup and recovery, replication, and clustering

technologies, which ensure that data is always available.

• Security: Oracle Database provides a variety of security features to protect data,

such as encryption, access controls, and auditing, ensuring that data is kept safe

from unauthorized access and tampering.

• Performance: The database is optimized for high performance, providing advanced

caching, indexing, and query optimization technologies, as well as support for in-

memory processing and parallel execution.

• Manageability: Oracle Database includes a range of tools and features for managing

the database, such as graphical user interfaces, command-line interfaces, and APIs,

which simplify tasks such as database monitoring, backup and recovery, and

performance tuning.
 • Compatibility: The database supports a wide range of programming languages and

interfaces, including SQL, PL/SQL, Java, and .NET, allowing developers to build

custom applications that can access and manipulate the data in the database.

• Cloud integration: Oracle Database provides native integration with cloud

platforms such as Oracle Cloud Infrastructure, enabling easy deployment and

management of databases in the cloud.

History of Oracle

Oracle Corporation was founded by Lawrence Ellison (Larry Ellison), Bob Miner, Ed

Oates, and Bruce Scott in August 1977. They have a lot of experience in building database

programs for several companies and builds their first project (a special database program)

for the CIA (Central Intelligence Agency). Oracle was named after "Project Oracle," a

project for one of their clients named Central Intelligence Agency, and the company that

created Oracle was called Systems Development Labs (SDL). Systems Development Labs

was renamed Relational Software Inc. (RSI) in 1978 to expand their market for the new

database. They had again changed the name of the company from RSI to Oracle Systems

Corporation in 1982.

The first commercially available RDBMS named Oracle V2 (Version 2) was built using
PDP-

11 assembler language (SQL-based RDBMS). Although they already developed a

commercial RDBMS in 1977, it wasn't available for purchase until 1979, when Oracle

version 2 was released.

In 1983, Oracle database portable version named "Oracle version 3" was released. This

version was written in the C programming language. It was the first relational database that

can run in mainframes, minicomputers, PCs, or any hardware with a C compiler. It also

supports SQL queries and transactions execution.

The other subsequent versions are:

o In 1984, Oracle 4 was released that supports Transactions [Commit/Rollback],

export/import utilities, and the report writer.

o In 1985, Oracle 5 was released, which provides support for Client-Server

Architecture.

This new feature has the capability to connect the client's software to a database

server through a network.

o In 1989, Oracle 6 added support for PL/SQL language. It also comes with new

features such as OLTP high-speed systems, hot backup capability, and row-level

locking.

o In 1992, Oracle 7 was released. This version comes in the market as a result of four

years of hard work and two years of customer testing. It added some exciting

features and capabilities in the area of security, administration, development, and

performance. o In 1997, Oracle 8 was released. This version comes with the support

of ORDBMS that was designed to work with Oracle's network computer (NC). It

also added support for Java, HTML, and OLTP.

o In 1998, Oracle 8i was released. Here 'I' stands for Internet. It was the first database

version that added support for Web technologies such as Java and HTTP.

o In 2001, Oracle 9i was released with 400 new features such as XML, RAC (Real

Application Clusters), etc. These features reduce database size and provide high

availability & enhanced performance.

o In 2003, Oracle 10g was released with grid computing technology means grid. It

was the first version that supports 64-bit LINUX OS.

o In 2006, Oracle 11g was released. This version comes with new features such as

Oracle Database Replay, Transaction Management using Log Miner, Virtual

 Column Partitioning, Case sensitive passwords, Online Patching, Parallel Backups

on the same file using RMAN, and many others.

o In July 2014, Oracle 12C was released with Cloud support.

o In Feb 2018, Oracle 18 C was released. This version was the world's first

autonomous database.

Introduction to SQL

Structure Query Language(SQL) is a database query language used for storing and

managing data in Relational DBMS. SQL was the first commercial language introduced for

E.F Codd's Relational model of database. Today almost all RDBMS(MySql, Oracle,

Infomix, Sybase, MS Access) use SQL as the standard database query language. SQL is

used to perform all types of data operations in RDBMS.

SQL Command

SQL defines following ways to manipulate data stored in an RDBMS:

DDL: Data Definition Language

This includes changes to the structure of the table like creation of table, altering table,

deleting a table etc.

All DDL commands are auto-committed. That means it saves all the changes permanently

in the database.

Command Description

create to create new table or database

alter for alteration

 truncate delete data from table

drop to drop a table

rename to rename a table

DML: Data Manipulation Language

DML commands are used for manipulating the data stored in the table and not the table
itself.

DML commands are not auto-committed. It means changes are not permanent to database,

they can be rolled back.

Command Description

insert to insert a new row

update to update existing row

delete to delete a row

merge merging two rows or two tables

TCL: Transaction Control Language

These commands are to keep a check on other commands and their affect on the database.

These commands can annul changes made by other commands by rolling the data back to

its original state. It can also make any temporary change permanent.

Command Description

commit to permanently save

 rollback to undo change

savepoint to save temporarily

DCL: Data Control Language

Data control language are the commands to grant and take back authority from any

database user.

Command Description

grant grant permission of right

revoke take back permission.

Introduction to Oracle data types

In Oracle, every value has a data type that defines a set of characteristics for the value.

These characteristics cause Oracle to treat the values of one data type differently from the

values of another. For example, you can add values of the NUMBER data type, but not

values of the RAW data type.

When creating a new table, you specify a data type for each of its columns. Similarly, when
you create a new procedure, you specify a data type for each of its arguments.

The data type defines the allowed values that each column or argument can store. For

example, a DATE column cannot store a value of February 30, because this is not a valid

date.

Oracle has a number of built-in data types illustrated in the following table:

Cod Data Type

e
1 VARCHAR2(size [BYTE | CHAR])

1 NVARCHAR2(size)

2 NUMBER[(precision [, scale]])

8 LONG

12 DATE

21 BINARY_FLOAT

22 BINARY_DOUBLE

23 RAW(size)

24 LONG RAW

69 ROWID

96 CHAR [(size [BYTE | CHAR])]

96 NCHAR[(size)]

112 CLOB

112 NCLOB

113 BLOB

114 BFILE

180 TIMESTAMP [(fractional_seconds)]

181 TIMESTAMP [(fractional_seconds)] WITH TIME ZONE

182 INTERVAL YEAR [(year_precision)] TO MONTH

183 INTERVAL DAY [(day_precision)] TO

SECOND[(fractional_seconds)]
208 UROWID [(size)]
 231 TIMESTAMP [(fractional_seconds)] WITH LOCAL TIMEZONE

Each data type has a code managed internally by Oracle. To find the data type code of a

value in a column, you use the DUMP() function.

Character data types

Character data types consist of CHAR, NCHAR, VARCHAR2, NVARCHAR2, and

VARCHAR.

The NCHAR and NVARCHAR2 data types are for storing Unicode character strings.

The fixed-length character data types are CHAR, NCHAR and the variable-length character

data types are VARCHAR2, NVARCHAR2.

VARCHAR is the synonym of VARCHAR2. However, you should not use VARCHAR

because Oracle may change its semantics in the future. For character data

types, you can specify their sizes either in bytes or characters.

Number data type

The NUMBER data type has precision p and scale s. The precision ranges from 1 to 38

while the scale range from -84 to 127.

If you don’t specify the precision, the column can store values including fixed-point and

floating-point numbers. The default value for the scale is zero.

Datetime and Interval data types

Datetime data types are DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, and

TIMESTAMP WITH LOCAL TIME ZONE. The values of a datetime data type are

datetimes.

The interval data types are INTERVAL YEAR TO MONTH and INTERVAL DAY TO
SECOND. The values of the interval data type are intervals.

RAW and LONG RAW data types

The RAW and LONG RAW data types are for storing binary data or byte strings e.g., the

content of documents, sound files, and video files.

The RAW data type can store up to 2000 bytes while the LONG RAW data type can store

up to 2GB.
Exercise on Creation of Table

a) Create the following table with mentioned constraints:

Relation Name: S
Column Data Width Constraints
Name Type
Sno Varchar2 5 Primary Key
Snam Varchar2 20 Unique
e
City Varchar2 20 City must be New Delhi, Patiala,
Amritsar and Qadian
Statu Number 3 Must be greater than 10
s

Relation Name: P
Column Data Width Constraints
Name Type
Pno Varchar2 5 Primary Key
Pname Varchar2 20 Unique
Color Varchar2 20 Not Null
City Varchar2 20 City must be Jalandhar, Patiala,
Amritsar and Qadian

Relation Name: SP
Column Data Widt Constrains
Name Type h
Sno Varchar 5 Refers Sno of Supplier table
2
Pno Varchar 5 Refers Pno of Part table
2
Qty Numbe 4 Must be greater than 100
r

b) Identify the primary key of SP table and apply Primary constraint on

that.
--a)

create table S(

Sno varchar2(5) not null,

Sname varchar2(20),

City varchar2(20) not null, Status number(3), constraint pk_S PRIMARY

KEY(Sno), constraint uk_S UNIQUE(Sname), constraint chkcity_S

CHECK(City IN ('New Delhi','Patiala','Amritsar','Qadian')), constraint

chkstatus_S CHECK(Status>10)

);

create table P(

Pno varchar2(5) not null,

Pname varchar2(20),

City varchar2(20) not null,

Color varchar2(20) not null,

constraint pk_P PRIMARY

KEY(Pno), constraint uk_P

UNIQUE(Pname),

constraint chkcity_P CHECK(City IN ('Jalandhar','Patiala','Amritsar','Qadian'))

);

create table SP(

Pno varchar2(5) not null,

Sno varchar2(20) not null,

Qty number(4),
constraint fk1_SP FOREIGN KEY(Pno) REFERENCES P(Pno),

constraint fk2_SP FOREIGN KEY(Sno) REFERENCES S(Sno),

constraint chkqty_S CHECK(Qty>100)

);

desc S;

desc P;

desc SP;
-- b) The primary key of SP table is a composite primary key combining Pno and Sno.

Alter Table SP

ADD Constraint pk_SP PRIMARY KEY(Sno,Pno);

Task II: Exercise on Insertion of records.

 a) Insert the following record.
Relation Name: S
SNO NAME CITY STATUS
S1 SUNEET QADIAN 20
S2 ANKIT AMRITSAR 10
S3 AMIT PATIALA 30

Relation Name: P
PNO NAME COLOR CITY
P1 NUT RED QADIAN
P2 BOLT GREEN AMRITSAR
P3 SCREW BLUE JALANDHAR
P4 SCREW RED QADIAN

Relation Name: SP
SNO PNO QTY SNO
S1 P1 250 S1
S1 P2 300 S1
S1 P3 500 S1
S2 P1 250 S2

b) Check the working of all the constraints by inserting the invalid

data in the corresponding columns.
Note down the errors encountered.

--a)

INSERT ALL

INTO S VALUES('S1','SUNEET','Qadian',20)

INTO S VALUES('S2','ANKIT','Amritsar',11)

INTO S VALUES('S3','AMIT','Patiala',30)
 SELECT * FROM dual;

SELECT * from S;

INSERT ALL

INTO P VALUES('P1','NUT','Qadian','RED')

INTO P VALUES('P2','BOLT','Amritsar','GREEN')

INTO P VALUES('P3','SCREW','Jalandhar','BLUE')

INTO P VALUES('P4','SCREW1','Qadian','RED')

SELECT * FROM dual;

SELECT * from P;

INSERT ALL

INTO SP(Sno,Pno,Qty) VALUES('S1','P1',250)

INTO SP(Sno,Pno,Qty) VALUES('S1','P2',300)

INTO SP(Sno,Pno,Qty) VALUES('S1','P3',500)

INTO SP(Sno,Pno,Qty) VALUES('S2','P1',250)

SELECT * FROM dual;

SELECT * from SP;

--b)

Violating NOT NULL constraint- insert into S(Sno,Sname) Values('S4','Vasu');

Violating CHECK constraint- insert into S(Sno,Sname,City,Status)

Values('S4','Vasu','Chandigarh',30);

Violating UNIQUE constraint- insert into S(Sno,Sname,City,Status)

Values('S1','Vasu','Qadian',30);