Recent Posts

Friday, 22 April 2016

SQL Tutorial

DBMS

     A database management system (DBMS) is a software package with computer programs that control the creation, maintenance, and use of a database. To open Oracle in Command prompt just type sqlplus in RUN.


RDBMS

     RDBMS stands for relational database management system. A relational database is the one in which data is stored in the form of tables i.e., rows and columns. And a system that manages this database is known as database management system. The major functions performed by RDBMS are creating, editing and deleting tables and inserting, deleting and editing data into at tables.
     The data items are recognized by a unique entry known as primary key. The main examples of RDBMS are Oracle, SQL server, DBase.


SQL (Structured query language)

* It is non procedural language which is used to communicate  any type of database such as Oracle DB2, SQL server e.t.c.,
* This language was developed by a German scientist MR MF Codd in 1968. ANSI approved this language in 1970 and it was released at 1972.
Query: Query is a mechanism by using query we can perform language operations. Those are DML,DDL,DRL,DCL,TCL.


Features of SQL

1. SQL is not a case sensitive language because all the commands of SQL we can write in capital letters or small letters.
2. Every command of SQL should ends with semicolon.
3. SQL can also Pronounced as SEQUEL (Structured English query language).
4. SQL can call as NLI (Natural language interface) because every command of SQL almost similar reassembles to normal English language.
5. SQL can be called as common language interface because this  is the only  language to communicate with any type of database such as Oracle, Sybase, DB2, Teradata e.t.c.,
6. Structure query language was mainly subdivided into five sublanguages those are fallowing types.


Data types in Oracle 10g

These data types are totally divided into two types.

1. Number

i. NUMBER (SIZE)
     This data type allows us to enter numeric values such as integer values or decimal values. In SQL these data types further divided into NUMBER(SIZE)This data type allows us to enter only integer values student roll numbers employee numbers e.t.c., Here size  represents no of digits.
E.g:
EID NUMBER(3)

ii. NUMBER(P,S) (P=Precision, S= Scale)
     This data type allows us to enter decimal values such as Employee salaries student average marks e.t.c., Here p represents precision and s represents scale.  Here Precision means no of digits including decimal points where as scale means no of decimal places.
E.g:
ESAL NUMBER(6,2)
AM NUMBER(3,2)

2. Alphabets 

     The data type allows us to enter character values such as student names employee names e.t.c., this data type is further divided into three types 
1. Char(Size) 
2. Varchar(Size)
3. Varchar2(Size)
Char (Size)
     This data type allows us to enter character values the maximum length of this data type is 1 to 2000 bytes of characters this data type always allocates the memory in a static fashion.
E.g: Ename char(15)

Varchar (Size)/Varchar2(Size)
     This data type also allows us to enter character values such as student names, employee names e.t.c., The maximum length of this data type is 1 to 4000 characters or bytes. It always allocates the memory in a dynamic fashion.
Note: The difference between varchar data type and varchar2 data type is – Varchar data type is developed along with SQL where as varchar2 data type developed by Oracle corporation varchar data type always allocates the memory in a dynamic fashion where as varchar2 data type supports garbage values.

3. Date

     The data type allows us to enter date values such as student joining date employee hiring data e.t.c., the default date format is Oracle is DD/MM/.YYYY.
E.g: Jdate date
Timestamp
     The data type allows to enter both date and time values. The default format of timestamp is DD/MM/YYYY HH/MM/SS.
E.g: Logintime timestamp

Miscellunious datatypes


These data types are further divided into three types CLOB, BLOB, Bfile.
i. CLOB (Character Large Object): This data type allows us to all types of characters and the minimum length of this data type is 1 to 4GB.
Whenever we need to enter employee history student conduct e.t.c., then we need to use this data type.

ii. BLOB (Binary large Object): This data type allows us to enter any type of photos, graphical pictures clippings, sounds, multimedia messages e.t.c., the minimum length of this data type is 1 to 4 GB.

iii. BFILE: This data type also allows us to enter BLOB types of values and also binary data, XML data e.t.c., the minimum length of data type is 1 to 4 GB.

Next Tutorial  Data definition Language

No comments:

Post a Comment