ORACLE OBJECTS AND OBJECT TYPES

Oracle object types are user-defined data types that make it possible to model complex real-world entities such as customers and purchase orders as unitary entities --"objects"--in the database.

Oracle object technology is a layer of abstraction built on Oracle's relational technology. New object types can be created from any built-in database types and/or any previously created object types, object references, and collection types. Meta-data for user-defined types is stored in a schema that is available to SQL, PL/SQL, Java, and other published interfaces.

Object types and related object-oriented features such as variable-length arrays and nested tables provide higher-level ways to organize and access data in the database. Underneath the object layer, data is still stored in columns and tables, but you are able to work with the data in terms of the real-world entities--customers and purchase orders, for example--that make the data meaningful. Instead of thinking in terms of columns and tables when you query the database, you can simply select a customer.

For example you use address which comprises of streetno, locality, city, pin, country, in many tables. You define these columns in each table separately like


     Create table emp (empno number(5),Name varchar2(20),Sal number(10,2),Streetno varchar2(20),Locality varchar2(20),City varchar2(20),Pin varchar2(7),Country varchar2(20));

     Create table customers (custcode number(5),Name varchar2(20),Bal number(10,2),Streetno varchar2(20),Locality varchar2(20),City varchar2(20),Pin varchar2(7),Country varchar2(20));

Instead of defining address columns again and again in each table, you can define an address datatype and then use this user defined datatype in all tables.

Defining an Object Type


The following example create an object type

     Create type address as object ( Streetno varchar2(20),Locality varchar2(20),City varchar2(20),Pin varchar2(7),Country varchar2(20));

Now you can use this datatype while defining tables emp and customer like this

    Create table emp (empno number(5),Name varchar2(20),Sal number(10,2),Emp_addr address);

    Create table customers (custcode number(5),Name varchar2(20),Bal number(10,2),Cust_addr address);

Inserting Records in tables with object datatypes.
To insert record into emp table you have to give the following statement.
    Insert into emp values (101,’Sami’,2000,address(‘A.C.Gaurds Road’,’A.C.Gaurds’,’Hyd’,’500004’,’India’);

To update city of employee number 101 you have to give the following update statement
Update emp e set e.emp_addr.city=’Bombay’ where e.empno=101;

To select rows from emp table give the following statement
Select * from emp;
To select only empno,name and city give the following statement
Select e.empno, e.name, e.emp_addr.city from emp e;

My Favourite Game

DML, DCL, DDL, TCL Statements

SQL commands are divided into categories like DML (Data Manipulation language), DDL (Data definition language), TCL (Tranction control language) and DCL (Data control language). Here is a list of SQL commands.

1. DML COMMANDS

• INSERT
• UPDATE
• SELECT
• DELETE

2.DDL COMMANDS

• CREATE
• ALTER
• DROP 

3.TCL COMMANDS 

• COMMIT
•  ROLLBACK

4.DCL COMMANDS 

• GRANT
• REVOKE

-------------------------------------------------------------

 

DML

DML is abbreviation of Data Manipulation Language. It is used to retrieve, store, modify, delete, insert and update data in database.

DDL commands used to create the objects, DML commands used to manipulate the objects. DML commands are INSERT–This command is used to insert the records into table, UPDATE–This command is used to update the record which is already exisiting in the table, DELETE– This command is used to delete the record which is already existing the table. It is deletes only records not the structure.

Examples: SELECT, UPDATE, INSERT statements

DDL

DDL is abbreviation of Data Definition Language. It is used to create and modify the structure of database objects in database.

DDL is used to create the structure of the table. DDL commands are CREATE —Used to create any objects(Table,View,Trigger,Stored procedure) in the database. ALTER – Used to modify the objects in the database.DROP –this command used to drop the objects with the structure.TRUNCATE–This command also used to delete the objects. RENAME–used to modify the object name in the data base.

Examples: CREATE, ALTER, DROP statements

DCL

DCL is abbreviation of Data Control Language. It is used to create roles, permissions, and referential integrity as well it is used to control access to database by securing it.

DCL commands used to security purpose.

GRANT–this command used to give the pemissons to the paricular Data.

REVOKE–This command used to release the permissions, which already given to any user.

Examples: GRANT, REVOKE statements

TCL

TCL is abbreviation of Transactional Control Language. It is used to manage different transactions occurring within a database.

Examples: COMMIT, ROLLBACK statements

Single Line Functions

The single line function work on individual columns from individual rows and return one result per row.


The single line functions take different types of arguments, work on data item from each row, and return one value back for each row.

The different single line functions are:

 Numeric functions

 Date functions

 Character functions

 Conversion functions

NUMERIC FUNCTION:

ABSOULTE:

Abs() function is used to display the absolute value of a specified value, (i.e) negative into positive value.

COS:

Cos() function is used to find the cosine value.

LOG:

Log () function is used to find the logarithmic value.

POWER:

power() function is used to find the power value of specified value.

SQRT:

sqrt() function is used to find the square root value.

CEIL:

ceil() function is used to display round up value.

FLOOR:

floor() function is used to display the value truncating the decimal point value.



SIGN:

sign() function is used to display the first element.

MOD:

mod() function is used to calculate the modulus value.

TAN:

tan() function is used to find the tangent value.

TRUNCATE:

trun() function is used to truncate the floating point value and display.

DATE FUNCTION:

SYSDATE:

It is used to display the current date.

ADD FUNCTION:

It is used to add the months to the current date.

LAST_DAY:

It is used to display the last day of the current date or given date.

NEXT_DAY:

It is used to display the next day of the specified day. It specifies the date of the next day.

ROUND:

It is used to display the current date.

GREATEST:

It is used to display the end of the month.

CHARACTER FUNCTION:

The character function are used to manipulate in the characters.

INITCAP:

It is used to display the initial letter in capital letter, if it small.

UPPER:

It is used to display the string in upper case, that it is in lower case .

LOWER:

It is used to display the string in lower case, if it is upper case.

LENGTH:

It is used to find the length of the string. It display the number of strings(characters).

REPLACE:

It is used to replace the string with another string. It can also be used to replace the name.

LPAD:

It is used to display the special character with the specified character and number of character in left.

RPAD:

It is used to display the special character with the specified character and number of character in right.

LTRIM:

Ltrim() function is used to truncate the space in the left. It displays the string truncating the left space if any.

RTRIM:

It is same as ltrim, it display the string truncating the right space.

SUBSTRING:

The function substr() is used to display the string that specified with number to display.

TRANSULATE:

Transulate() function is used to transulate the character into another character.











CONVERSION FUNCTION:

TO_CHAR:

It is used to convert the system date into character. Specify the date format in character as dd month YYYY, it displays, in month as character and the date and year in numerics.

TO_DATE:

It is used to convert the format into character. It displays the month in character, if specified.

TO_NUMBER:

It is used to display the number. It can also add the two numbers.

TRUNC:

It is used to select the user id, it displays the user number.



GROUP FUNCTIONS:

NO NAME

1 Aaa

2 Bbb

3 Ccc

4 Ddd

MIN:

The function min() is used to display the minimum value. In the table, min(no), 1 is selected.

MAX:

The function max()is used to display the maximum value. In the table, max(no), 4 is selected.

SUM:

The sum() function is used to display the sum. In this table, sum (0), displays the sum,10.

AVG:

The function is used to calculate the average, displays the average. In the above table it displays the average,2.5.

COUNT:

It displays the number of records. It is specified with the asterisk(*) to count the number of records. In the above table displays 4.

Structure of DBMS

Structure of DBMS

For more details Click here

History of Database Systems

A Brief History of Database Systems

Data are raw facts that constitute building blocks of information. Database is a collection of information and a means to manipulate data in a useful way, which must provide proper storage for large amounts of data, easy and fast access and facilitate the processing of data. Database Management System (DBMS) is a set of software that is used to define, store, manipulate and control the data in a database. From pre-stage flat-file system, to relational and object-relational systems, database technology has gone through several generations and its 40 years history.

• 1968 File-Based: predecessor of database, Data maintained in a flat file.  Processing characteristics determined by common use of magnetic tape medium.
• 1968-1980 Era of non-relational database: A database provides integrated and structured collection of stored operational data which can be used or shared by application systems.  Prominent hierarchical database model was IBM’s first DBMS called IMS. Prominent network database model was CODASYL DBTG model; IDMS was the most popular network DBMS.
• 1970-present Era of relational database and Database Management System (DBMS): Based on relational calculus, shared collection of logically related data and a description of this data, designed to meet the information needs of an organization; System catalog/metadata provides description of data to enable program-data independence; logically related data comprises entities, attributes, and relationships of an organization’s information. Data abstraction allows view level, a way of presenting data to a group of users and logical level, how data is understood to be when writing queries.

Example Database Systems are:

•  Microsoft Corp- SQL Server
• Oracle- Oracle 9i
• IBM – IMS/DB, DB2

 For More Details refer here