Oracle

Data Types: 

Data types specifies type of data within a table column

Oracle have following data types:

1. Number(p,s)

2. Char→ varchar2(maxsize)

3. Date.

Number(p,s): p→ precision (total number of digits) s→ scale ( it is used to store fixed, floating point numbers).

Syntax: columnname number(p,s)

Example:

SQL> create table test(sno number(7,2));

SQL> insert into test values (12345.67);

SQL> select * from test;

Sno --------- 12345.67

SQL> insert into test values(123456.7)

Error: value larger than specified precision allows for this column.

Note: whenever we are using number(p,s) then total number of digits before decimal places upto "p-s" number of digits. [eg:- p-s => 7-2=5]

After Decimal:

SQL> insert into test values(12345.6789);

SQL> select * from test;

Sno ------------- 12345.68

SQL> insert into test values(12345.6725);

SQL> select * from test;

Sno ------------ 12345.67

Note: whenever we are using number(p,s) and also if we are passing more number of digits after decimal place then oracle server only automatically rounded that number based on specified "scale". Number(p): It is used to store fixed numbers only.

Maximum length of the precision is up to "38".

Example: SQL> create table test1(sno number(7));

SQL> insert into test1 values(99.9);

SQL> select * from test1;

Sno ------- 100

SQL> insert into test1 values(99.4);

SQL> select * from test1;

Sno ----------- 99

2) Char: It is used to store fixed length "alpha numeric" data in bytes. Maximum limit is

upto 2000 bytes. Syntax: columnname char (size); By default character data type having one byte. Character data type automatically "Blank Padded".

3) Date: It is used to store dates in oracle date format.

Syntax: columnname date;

In oracle by default date format is DD- MON-YY 

Database terminologies

Data Definition Language(DDL): → Create → Alter → Drop → Truncate → Rename (orace 9i) These commands are used to define structure of the table.
1) Create: It is used to create database objects like tables, views, sequences, indexes.
Creating a table: Syntax:

a) create table tablename(columnname1 datatype(size), columnname2 datatype(size)..... );
Example: SQL> create table first(sno number(10), name varchar2(10));
b) To view structure of the table: Syntax: desc tablename;
Example: SQL> desc first;
2) Alter: It is used to change structure of the existing table.

Blank Padded: Whenever we are passing less number of characters than the specified data type size then oracle server automatically allocates blank spaces after the value. Example:- Varchar2(maxsize): It is used to store variable length alphanumeric data in bytes. Maximum limit is upto 4,000 bytes. Whenever we are using varchar2() data type oracle server not blank padded. After the value passed into that column.

Oracle 7.0 introduced "varchar2()" data type prior to oracle 7.0 we are using varchar2 data type.

It is also same as varchar2() data type. But it will stores upto 2000 bytes.

Syntax: column_name varchar2(maxsize);

A) Add: It is used to add number of columns into the existing table.

Syntax: Alter table tablename add(col1 datatype(size));

Example:- SQL> alter table first add sal number(10); SQL> desc table;

B) Modify: It is used to change column datatype or column datatype size only.

Syntax: alter table tablename modify(col datatype(size), col2 datatype(size));

Example:-

SQL> alter table first modify sno date; 

Types OF Commands (DDL AND DML)

There are mainly two different types of database language is ORACLE. They are:

1) SQL (STRUCTURED QUERY LANGUAGE)'

2) PL/SQL (PROCEDURAL LANGUAGE extension of SQL)

3) Dynamic SQL.(optional)

ORACLE is a Relational Database product which is used to store data permanently in secondary storage devices. If you want to operate ORACLE then we are using following languages:

1) SQL: It is non-procedural language. 2) PL/SQL: It is a procedural language.

All organizations store same type of data.

DATA: It is a collection of Raw facts.

Example: 101 dinesh 2000

102 mahesh 3000

In above example, there no meaningful data such data is known as Raw facts.

INFORMATION: It is a collection of meaningful data or processed data.

Example: EmpID Ename Salary

101 dinesh 2000

102 naresh 3000

In the above example, there is meaningful data which is in table format which consist of three different fields.

DATA STORE: It is a place where we can store data or information.

1) Books & Papers 2) Flat files 3) Database

FLAT FILES: This is a traditional mechanism which is used to store data or information in individual unrelated files. These files are also called as Flat Files.

Drawbacks of Flat files:

1) Data Retrieval 2) Data Redundancy 3) Data Integrity 4) Data Security 5) Data Indexing

1) Data Retrieval: If we want to retrieve data from flat files then we must develop application program in high level languages, where as if we want to retrieve data from databases then we are using Sequel Language.

SEQUEL (Structured English Query Language)

2) Data Redundancy: Sometimes we are maintaining multiple copies of the same data in

different locations this data is also called as Duplicate data or Redundant data. In Flat files mechanism when we are modifying data in one location it is not effected in another location. This is called INCONSISTENCY.

In databases, every transaction internally having 4 properties. These properties are known as ACID properties. ACID Properties:

A mean Atomicity (ROLLBACK)

C mean Consistency

I mean Isolation

D mean Durability (COMMIT)

These properties only automatically maintains consistent data in databases.

3) Data Integrity: Integrity means to maintain proper data. If we want to maintain proper data then we are defining set of rules, these rules are also called as " Business rules". In databases, we are maintaining proper data using 'constraints', 'triggers'. If we want to maintain proper data in flat files we must develop application programs in high level languages like COBOL, JAVA, ETC.....

4) Data Security: Data stored in flat files cannot be secured because flat files doesn't

provides security mechanism. Whereas databases provides "ROLE based security".

5) Data Indexing: If we want to retrieve data very quickly from database then we are using

indexing mechanism. Whereas flat files doesn't provide indexing mechanism.

To overcome all the above problems, a new software used by all organization to store data or information in secondary storage devices. This is called DBMS software.

DBMS (DATABASE MANAGEMENT SYSTEM):

It is a collection of programs (S/W) written to manage database.

Example: ORACLE, FOXPRO, DB2, TERADATA, SQLSERVER, SYBASE, MYSQL, INGRESS, INFORMIX, SQLLITE...... etc;

In file based approach, every application program in the organization maintain its own file separate from other application program.

FILE based approach:

THIS IS OLD APPROACH

Once we are installing DBMS software into our system then automatically some place is created in hard disc. This is called "Physical Database". And also automatically an user interface is created. Through the user interface we can also directly interactive with the

database or indirectly interactive with the database using application programs in high level languages.

"AFTER INSTALLING DBMS SOFTWARE"

DATABASE: It is an organized collection of interrelated data used by application program in an organization. Once data stored in database it can be shared by number of users simultaneously and also this data can be integrated.

DBMS Architecture: American National Standard Institute(ANSI) has established three level architecture for database. This architecture is also called as "ansi/sparc" (Standard Planning And Requirements Committee) architecture.

Main objective of DBMS architecture is to separate users view of the database from the where physically it is stored.

This architecture mainly consist of three levels. They are:

1) External level 2) Conceptual level 3) Internal level

DBMS Architecture or Three Level Architecture

DBMS architecture provides "DATA INDEPENDENCE".

Data Independence: Upper levels are unaffected by changes in the lower levels is called as "Data Independence". DBMS architecture have two types of Data Independences:

1) Logical Data Independence 2) Physical Data Independence

1) Logical Data Independence: Changes to the conceptual level do not required to change

to the external level this is called Logical Data Independence. Example: Adding a new entity in conceptual level does not effect in external level. 2) Physical Data Independence: Changes to the internal level do not required to changes

in conceptual level. This is called Physical Data Independence. Example: Adding an index to the internal level it is not affected in conceptual level.

CONCEPTUAL LEVEL: It describes logical representation of the database. Conceptual level defines type of data storing in database and also defines what type of data does not store in database using constraints and also specifies the relationship between data items.

NOTE: This level does not define how data is stored in database. In relational databases we are defining conceptual levels through tables.

EXTERNAL LEVEL: This level describes end user view of the database. i.e., in this level some group of users access only part of the database. In this level only we are defining the view and those views given to the number of users.

INTERNAL LEVEL: Internal level describes how physically data is stored in database. This level is handled by database administrator only. In relational databases indexes, cluster are available in internal level.

DATA MODELS: How data is represented at the conceptual level defined by means of "Data Model" in the history of database design three data models have been used.

1) Hierarchical Data Model 2) Network Data Model 3) Relational Data Model

DBMS ARCHITECTURE

Hierarchical Data Model: This model introduced in 1960. In this data model organizes data in tree like structure, we are representing data in parent child hierarchy. In this data model also data is represented in the format of records and also record type is also same as table in relational data model.

This Data model having more duplicate records because this data model is implemented based on one- to - many relationships. That is why in this data model always child segments are repeated.

In this data model products, we are retrieved data very slowly because in this data model products data base servers searching data based on root node onwards.

In 1960, IBM introduced IMS( Information Management System) product based on Hierarchical Data Model.

If we want to operate hierarchical data model products then we are using procedural language.

1)HIERARCHICAL DATA MODEL
2)RELATIONAL DATA MODEL
3)Network Data Model: In 1970's, CODASYL(Conference Or Data System Language) committee introduced network data model. This data model is implemented based on many - to - many relationships. In this data model also data is stored in format of records. And also records type is also same as table in Relational Data model. In 1970's IBM company introduced IDMS(Information Data Management System) based on network data model. If we want to operate network data model products then we must use procedural language

→ In 1977 Larry Ellision, Bob Miner, Ed Oates founded new company " Software

Development Laboratories" (SDL) → In 1978 SDL Introduced oracle version1 (never released). → In 1979 SDL name changed into RSI(Relational Software Incorporation). → In 1982 RSI name changed into Oracle Corporation. ORACLE VERSIONS 1)Oracle 2.0 -> 1979 -> First public release -> In this 2.0 only basic SQL functionality is there "joins".

NETWORK DATA MODEL
4) Relational Data Model: In 1970, E.F.Codd introduced Relational Data Model. In this data model we are storing data in 2- dimensional tables. Relational data model mainly consist of 3 components.

1) Collection of Objects. 2) Set of Operators. 3) Set of Integrity rules.

If we want Relational Data Model products then we are using Non- Procedural Language

"SQL".

SQL Sub Languages in every database:

1) Data Definition Language (DDL): → Create → Alter → Drop → Truncate → Rename(oracle 9i)

2) Data Manipulation Language (DML): → Insert → update → delete → merge(oracle 9i)

3) Data Retrieval Language(DRL) or Data Query Language(DQL): → SELECT 4) Transactional Control Language (TCL): → Commit → Rollback → Savepoint 5) Data Control Language(DCL): → Grant → Revoke

Oracle 10g, 11g, 12c.(Enterprise Edition) Username: scott Password: tiger Error: Account locked message displayed for first time. So, again we have to connect. Username: \ sys as sysdba Password: sys SQL> alter user scott account unlock: SQL> conn scott/tiger Enter password: tiger Confirm password: tiger