Oracle Basics

Oracle Basics. By Shiva Chindam

Introduction 







Oracle has grown from its humble beginnings as one of a number of databases available in the 1970s to the overwhelming market leader of today. In its early days, Oracle Corporation was known as an aggressive sales and promotion organization. Over the years, the Oracle database has grown in depth and quality, and its technical capabilities now match its early hype. With the Oracle8, Oracle8i, Oracle9i etc releases,Oracle has added more power and features to its already solid base.



Before we dive into the specific foundations of these new releases, we must spend a little time describing some Oracle basics--how databases evolved to arrive at the relational model, a brief history of Oracle Corporation, and an introduction to the basic features and configurations of the database.

The Evolution of the Relational Database 

The concept of the relational database was first described around 1970 by Dr. Edgar F. Codd in an IBM research publication entitled "System R4 Relational." Initially, it was unclear whether any system based on these concepts could achieve commercial success. Nevertheless, Relational Software, Incorporated (RSI) was founded in 1979 and released Oracle V.2 as the world's first relational database. By 1985, Oracle could claim more than 1,000 relational database customer sites. IBM itself would not embrace relational technology in a commercial product until the Query Management Facility in 1983.





Database management systems were first defined in the 1960s to provide a common organizational framework for what had been data stored in independent files. The relational database uses the concept of linked two-dimensional tables consisting of rows and columns, as shown in Figure 1

Relational model with two tables  

                    

Terminology Used in a Relational Database A relational database can contain one or many tables. A table is the basic storage structure of an RDBMS. A table holds all the data necessary about something in the real world, such as employees, invoices, or customers. The slide shows the contents of the EMPLOYEES table or relation. The numbers indicate the following: 1. A single row or table representing all data required for a particular employee. Each row in a table should be identified by a primary key, which allows no duplicate rows. The order of rows is insignificant; specify the row order when the data is retrieved. 2. A column or attribute containing the employee number. The employee number identifies a unique employee in the EMPLOYEES table. In this example, the employee number column is designated as the primary key. A primary key must contain a value, and the value must be unique. 3. A column that is not a key value. A column represents one kind of data in a table; in the example, the salary of all the employees. Column order is insignificant when storing data; specify the column order when the data is retrieved. 4. A column containing the department number, which is also a foreign key. A foreign key is a column that defines how tables relate to each other. A foreign key refers to a primary key or a unique key in another table. In the example, DEPARTMENT_ID uniquely identifies a department in the DEPARTMENTS table. 5. A field may have no value in it. This is called a null value. In the EMPLOYEES table, only

Writing SQL Statements   



• SQL statements are not case sensitive. • SQL statements can be on one or more lines. • Keywords cannot be abbreviated or split across lines. • Clauses are usually placed on separate lines.

‘Select’ --DML Statements Basic SELECT Statement SELECT *|{[DISTINCT] column|expression [alias],...} FROM table; • SELECT identifies what columns • FROM identifies which table Ex: Select * from EMP; Select Distinct ename from EMP

‘Insert’ --DML Statements    

insert into a values (1); insert into a values (42); insert into a values (100); commit;

‘UPDATE’ --DML Statements 





Update Table EMP set Ename= ‘RAGHU’ where EMP_ID=121213 Update EMP set SAL=SAL+10 where DOJ=’10-10-1998’ Commit;

‘Delete’ --DML Statements  



Delete from EMP; Commit; Delete from EMP where Ename=‘Raghu’

Merge --DML Statements 

merge [ hints ] into table-name-1 | view-name-1 [ alias-1 ] using table-name-2 | view-name-2 | subquery [ alias-2 ] on ( condition ) [ merge-update-clause ] [ merge-insert-clause ] [ error-logging-clause ];



merge into table_dest d using table_source s on (s.id = d.id) when matched then update set d.txt = s.txt when not matched then insert (id, txt) values (s.id, s.txt);

Arithmetic Expressions 

You may need to modify the way in which data is displayed, perform calculations, or look at what-if scenarios. These are all possible using arithmetic expressions. An arithmetic expression can contain column names, constant numeric values, and the arithmetic operators.

DDL Statements  



DDL stands for data definition language. DDL statements are SQL Statements that define or alter a data structure such as a table. Hence, a typical DDL statement is create table or alter table.

CREATE Simple Create Statement create table t ( a number, b varchar2(10) 

It is possible to create the constraints together with the create statement. As a foreign key references a known type, it is not necessary to specify the foreign key's column type.

create table orders ( order_id number primary key order_dt date, cust_id references customers )

ALTER 

alter table foo_table add bar_column char(1);



You can add a new column, drop a new column, Rename a column, add a FK, add constraint, disable constarint

DROP 

drop table table_name



Table View PL_SQL object etc

 

truncate 

Truncate table Table_name



Destructive command Non recoverable

