=> What is SQL?
Structure query language
DBMS AND RDBMS
DBMS(DATA BASE MANAGEMET SYSTEM)
RDBMS(RELATION DATA BASE MANAGEMET SYSTEM)
DBMS RDBMS
Data is stored in a dbms as a file. Data is stored in an RDBMS in the form of
tables.
DBMS Supports a single user. RDBMS Supports a multiple users.
Example: Example:
======== ==========
dBase,Visual Foxro,xmls Oracle data base,
Mysql,sql server,postgresql,etc..
DBMS does’nt supports integrity constraints RDBMS supports integrity constarints.
RDBMS
========
Create the tables, inserting the data , delete the data, update the data.
SQL
PL/SQL
SQL
=======================================
- SQL IS DB LANGUAGE WHICH IS USED TO COMMUNICATE WITH DATABASE.
- SQL WAS INTRODUCED BY "IBM".
- INITIAL NAME IS "SEQUEL" AND LATER RENAMED AS A "SQL".
- SQL QUERIES ARE NOT A CASE SENSITIVE WE CAN WRITE SQL QUERIES IN
UPPER / LOWER / COMBINATION OF UPPER & LOWER CASE CHARACTERS.
Data base
=========
It is collection of inter related to the data.
DATA TYPES
==========
The type of data that we are going to store in a table column is called Data type.
1)Char
2)Varchar
3)Varchar2
4)number(p,s)/integer
5)date
6)time stamp
7)lobs
8)long
1)CHAR
========
To store fixed length data.
Alpha numeric data allowed.
AAAA12233
=> maximum size 2000 bytes.
=> In oracle default char data type having 1 byte.
Syntax
======
X char(10)
Ex
==
Name -------Brahma-----4 bytes ---------> extra bytes are wasted
EX:-
Char(2)
State_code Char(2)
AP
TS
Country code char(3)
IND
AUS
USA
2)VARCHAR2
==========
It is used to store variable length data…
=> maximum size 4000 bytes.
=> in varchar2 extra bytes are released
Syntax
--------
X Varchar2(10)
EX:
===
Venkatesh--------1----- extra bytes are released
3) VARCHAR
============
Oracle 7.0
=> maximum size 2000 bytes..
=> we are creating varchar data type also then oracle server internally automatically converts
varchar data type into varchar2 data type.
4)NUMBER(P,S)
=============
P Means precision => total [Link] digits allowed
S Means Scale => [Link] digits allowed after decimal.
Salary number(7,2)
5000-----allowed
500.00---allowed
5000.00----allowed
50000.00----allowed
500000.00-----not allowed
5) NUMBER(P)
===========
It allows numeric data upto 38 characters
Ex
===
Empid number(5)
10
100
1000
10000-----> invalid
5) Date
======
It allows date and time
=>default date format in oracle sql is
DD-MON-YY/YYYY
6)TIME STAMP
==============
It allows date/time/milliseconds/minutes/fraction of seconds
Ex
===
T timestamp(DD-MON-YY HH:MIN:SS:FS)
7)Long
======
To store the information
=> maximum size is 2GB Data.
Syntax
======
Column name Long
8)LOB’S(Large objects)
=================
1)CLOB
2)BLOB
3)BFILE
1)CLOB(CHARACTER LARGE OBJECT)
===============================
To store huge information
Max size depends on sql/plsql
2)BLOB(BINARY LARGE OBJECT)
===========================
To store images
Syntax
======
X BLOB
3)BFILE
=======
To store files.
X BFILE
COMMANDS IN SQL (SUB LANGUAGES)
=============================================================================
1)DDL
2)DML
3)DQL/DRL
4)DCL
5)TCL
1)DDL(Data Definition Language)
=============================
1)Create
2)Alter
3)Drop
4)Truncate
5)Rename
2)DML(Data manipulation Language)
==============================
1)Insert
2)Update
3)Delete
3)DQL/DRL(Data Query language/Data Retrieval Language)
=================================================
1)Select
4)DCL(Data control Language)
===========================
1)Savepoint
2)Commit
3)Rollback
5)TCL(Transaction Control Language)
===============================
1)Grant
2)Revoke