RDBMS--------
DATA---DATA IS A RAW FACTS.
INFORMATION----A ABOUT THE DATA IS CALLED INFORMATION.
DATABASE---
DATA + INFORMATION=DB
DBMS-----
DB+SOFTWARE=DBMS
DATA + INFORMATION+SOFTWARE
RDBMS-
RELATIONAL MODEL+DBMS
(TABLE SPACES)+DBMS
OORDBMS----
OOPS+R+DBMS
DDBMS--- DISTRIBUTED+DBMS
DATA BASE--- IT IS A COLLECTION OF DATA AND INFORMATION IN A MEANINGFULL WAY SO THAT THE RETRIVAL, PROCESSING, FETCHING OF DATA IS NOW MORE MEANINGFULL AND EFFICIENT.
DBMS--- IT IS A SOFTWARE FOR MANAGE THE DATABASE SO THAT
THE RETRIVAL, PROCESSING, FETCHING OF DATA IS NOW MORE MEANINGFULL AND EFFICIENT.
RDBMS---HERE WE MERGE RELATIONAL MODEL (TABLE SPACES) WITH DBMS.
SQL----
STANDERED QUERY LANGUAGE
STRUCTURED QUERY LANGUAGE
PRONOUNCED “SEQUEL”
IT IS DEVELOPED BY “IBM”
NOW IT IS CONTROLLED BY ANSI AND ISO (AMERICAN NATIONAL STANDERED INSTITUTION AND INTERNATIONAL STANDERED ORGANIJATION )
SQL IS COMMON FOR ALL RDBMS SOFTWARES.
LIKE---ORACLE—8P,9I,10G ETC
MS-ACCESS
SQL SERVER
DB2 ETC.
FOUR COMPONENTS OF SQL----
1. DDL(DATA DEFINATION LANGUAGE)
2. DML(DATA MANUPULATION LANGUAGE)
3. DQL—DATA QUERY LANGUAGE
4. DCL(DATA CONTROL LANGUAGE)
1. DDL---
CREATE, ALTER DROP
2. DML--- INSERT , DELETE , UPDATE
3. DQL--- SELECT
4. DCL--- GRANT , REVOKE
INITIALLY SQL HAVE ONLY FOUR COMPONENTS AND NINE COMMANDS
AFTER THAT ONE MORE COMPONENT ARE ALSO ADDED IT IS CALLED ----
5. DTL(DATA TRANJACTION LANGUAGE)
-------
COMMIT, ROLLBACK , SAVE POINT
NOTE--- SQL IS CASE INSENSITIVE LANGUAGE. SO UPPER CASE LETTERS
AND LOWER CASE LETTERS ARE TREATED AS SAME.
NOTE—SQL IS CASE INSENSITIVE AT COMMAND LEVEL . IT IS CASE SENSITIVE
AT DATA LEVEL.
DATA TYPES---
CHAR----IT IS USED FOR FIXED LENGTH
(ONE CHARACTER TAKE ONE BYTE OF MEMORY)
VARCHAR2----IT IS USED FOR VARIABLE
LENGTH
NUMBER----ALL NUMBERS. INTEGERS
AND FLOATING POINT ALSO
NAME CHAR(20);
EX---KAPIL
HERE 5 BYTES—USED SPACE
, 15 BYTES PADDING SPACE
TOTAL SIZE== 20 BYTE ARE OCCUPY
NAME VARCHAR2(20);
EX—DHARAMENDRA
11 BYTE USED SPACE , 9 BYTES ARE FREE
FOR FURTHER USE.
SO TOTAL SIZE USE = 11 BYTES ARE OCCUPY
DDL COMMANDS---
1. CREATE COMMAND---IT IS USED FOR CREATE A TABLE.
SYNTAX
CREATE TABLE TABLENAME
(
COL1 DATATYPE(SIZE),
COL2 DATATYPE(SIZE),
COL3 DATATYPE(SIZE),
COLN DATATYPE(SIZE)
);
TABLE ---ENTITY SET
COLUMN—ATTRIBUTES
ROW---TUPPLE,ENTITY
CREATE TABLE STUDENT
(
NAME VARCHAR2(20),
ROLLNO NUMBER(3),
SEM NUMBER(1),
FEES NUMBER(7,2)
);
HERE FEES NUMBER(7,2)----
MAX---99999.99
DML COMMAND—
INSERT COMMAND—
SYNTAX---
INSERT INTO TABLENAME
(COL1,COL2,…..)
VALUES
(VAL1,VAL2,….);
INSERT INTO STUDENT
(NAME,ROLLNO,SEM,FEES)
VALUES
(‘BHANU’,27,1,60000);
INSERT INTO STUDENT
(SEM,FEES,ROLLNO,NAME)
VALUES
(1,24000,19,’ANSHUL’);
INSERT INTO STUDENT
VALUES
(‘KOUSHEL’,22,1,888);
DQL COMMAND---
SELECT COMMAND
SYNTAX---
SELECT COL1,COL2,…. FROM
TABLENAME
WHERE CONDITION
SELECT * FROM TABLENAME ;
HERE * - -- METACHARACTER(ALL DATA ALL ROW + ALL COLUMN)
DML OPERATION----
DELETE COMMAND—
SYNTAX---
DELETE TABLENAME ;
DELETE TABLENAME WHERE CONDITION;
DETETE STUDENT;
ALL THE DATA OF STUDENT TABLE ARE DELETED .
BUT TABLE STRUCTURE ARE PRESENT INSIDE DATABASE.
DELETE STUDENT WHERE NAME=’BHANU’;
ONLY ‘BHANU’ DATA ARE DELETED.
DML COMMAND----
UPDATE COMMAND---IT IS USED FOR UPDATE THE DATA PRESENT INSIDE TABLE.
SYNTAX---
UPDATE TABLENAME SET ……
WHERE CONDITION…….
UPDATE STUDENT SET FEES= FEES+10000;
UPDATE STUDENT SET
FEES= FEES-10000;
UPDATE STUDENT SET SEM= SEM+1;
UPDATE STUDENT SET NAME = ‘NEHA’ WHERE NAME = ‘BHANU’;
DDL COMMAND---
DROP COMMAND===
SYNTAX---
DROP TABLE TABLENAME;
TABLE AS WELL AS DATA ALL ARE DROPPED.
DDL COMMAND---
ALTER COMMAND—WE CAN MODIFY THE STRUCTURE OF THE TABLE.
SYNTAX---
ALTER TABLE TABLENAME
ADD
NEWCOLNAME DATATYPE(SIZE);
ALTER TABLE STUDENT ADD
YEAR NUMBER(1);
CONSTRAINTS----
IT IS THE LIMITATION ON THE TABLE
WE CAN RISTRICT THE DATA INSIDE THE TABLE FOR UNPROPER HANDLLING.
TYPES---
PRIMARY KEY---DUPLICATES ARE NOT ALLOWED,NULL ARE NOT ALLOWED
UNIQUE + NOT NULL = PRIMARY KEY
NOT NULL – NULL ARE NOT ALLOWED
DUPLICATED CAN BE ALLOWED
UNIQUE -- DUPLICATES ARE NOT ALLOWED BUT NULL CAN BE ALLOWED.
CHECK--- CHECK THE RANGE OF COLUMN.
CREATE TABLE CS_STUDENT
(
NAME VARCHAR2(20) NOT NULL,
F_NAME VARCHAR2(20) NOT NULL,
SEM NUMBER(1) NOT NULL,
YEAR NUMBER(1) NOT NULL,
ROLLNO NUMBER(3) PRIMARY KEY,
FEES NUMBER(7,2) CHECK (FEES >=20000 AND FEES <=80000),
PASSPORTNO NUMBER(6) UNIQUE
);
