Technofunction

All About View in Oracle SQL Database

November 6th, 2010

What is a View in Oracle SQL Database?

A view is a logical table based on table or another view which is used to present the combinations of data present in the database. A view as itself doesn’t contain any data but it is more like a window through which the data from the tables can be viewed or changed. The table on which view is based is known as the base table and view is stored as a select statement in the data dictionary.

Advantages of Creating a View:-

  • It restricts the data access to predefined tables and columns so that a database user can see only information relevant for him.
  • It makes the complex queries easy.
  • Provides the data independence.
  • Can be used to present the different views of the same data.

There are majorly two types of views:-

1.    Simple Views

2.    Complex Views

The major difference b/w the two lies in the DML operations.

(more…)

Posted by Ankit under Structured Query Language(SQL) with 2 Comments
Tags: SQL

Oracle Basic Data Types

October 31st, 2010

Table Fundamentals:-

A table is a database objects that holds the data.  The simplest way to think of a table is as a spreadsheet. Now column of the table has a specific data type associated with them.  For e.g.:- if a column has a number data type than we can’t insert the character in that column.

Oracle Basic Data Types:-

Data Type Description
CHAR(size) It is used to store the character string values of fixed length. The size in the bracket determines the number of the characters it can hold in a cell. The maximum size this data type can hold is 255 characters.

The major problem is that it statically allocates the space which means if you assign 40 spaces while you are using 20 characters only than your 20 spaces will be left and not used.

However it has advantage as it is faster than variable character (varchar). Sometimes it can be faster than varchar , upto 50% even so in case you sure of characters it is wiser to use char data type.
VARCHAR/VARCHAR2(size) It is used to store the variable length alphanumeric data. It is more flexible than char data type. The maximum value this data type can hold is upto 4000 characters. It represents the data of type string, yet stores this data in variable length format. Varchar is much wiser choice than char due to its variable length format characteristic.
DATE It is used to represent the   date and time. The standard format is DD-MON-YY i.e. 21-jun-10. You can change this standard by using the appropriate function. DateTime stores the time in 24 hours format.
Number(P,S) The NUMBER data type is used to store numbers (fixed or floating point). Numbers of any magnitude can be stored upto a 38 digits of precision. Valid values are from 0, and positive and negative numbers with magnitude. There are two important terms used in number data type context these are:

1.       PRECISION determines maximum length of data.

2.       SCALE determines number of places to the right of the decimal.
LONG It is used to store variable length character strings containing upto 2GB. LONG data can be used to store arrays of binary data in ASCII format. Only one LONG value can be stored per table. It cannot be used in subqueries, functions, and expressions. A table containing LONG value cannot be clustered.
RAW/ LONG RAW This data type is used to store binary data, such as digitized picture of image. Data in these columns are stored without any further conversion. RAW data types can have a maximum of 255 bytes. It can contain upto 2GB data. Values stored in these columns cannot be indexed.
Timestamp Includes year, month, day, hour, minute and seconds. Other similar data types are Timestamp with time zone which have time zone also with fractional seconds precision.
Blob, Clob LOB locators what point to large binary object/ large character object in the database. These have capacities of upto 4 gigabytes.
Posted by Ankit under Structured Query Language(SQL) with No Comments

Introduction to SQL and SQL vs SQL *PLUS

October 16th, 2010

Structured Query Language(SQL) is a language that provides an interface to relational database systems. SQL was developed by IBM for use in system R, and is a standard. It is both ISO and ANSI standard.

Basically SQL is composed of different languages which are as follows:-

Components of SQL

1. DDL (DATA DEFINITION LANGUAGE):-

  • CREATE: – to create the objects in the database.
  • ALTER: – Alters the structure of the database.
  • TRUNCATE: – Remove all records from a table, including all the spaces allocated for the records are removed.
  • COMMENT: – Add comment to the data dictionary.
  • GRANT: – Gives user’s the access privilege to the databases.
  • REVOKE: – Withdraw the access privileges given with the GRANT command.

2. DML (DATA MANIPULATION LANGUAGE)

  • INSERT:- Insert the data into a table
  • UPDATE:- Updates the existing data within a table
  • DELETE: – Deletes all records from a table, the space from the records remain.
  • CALL: – call a PL/SQL or Java subprogram.
  • EXPLAIN PLAN: - Explain the access path to data.
  • LOCK: – Table control concurrency.

(more…)

Posted by Ankit under Structured Query Language(SQL) with No Comments
Tags: SQL