SQL----> STRUCTURED QUERY LANGUAGE
STATEMENTS /LANGUAGES /SUBSET OF SQL
DDL---> DATA DEFINITION LANGUAGE
TO CREATE OR MODIFY THE STRUCTRE OF OBJECT
--> CREATE
TO CREATE THE TABLE
--> RENAME
TO RENAME A TABLE_NAME
--> ALTER
TO MODIFY THE STRUCTURE OF TABLE
--> TRUNCATE
TO REMOVE ALL THE RECORDS FROM THE TABLE
--> DROP
TO REMOVE THE TABLE FROM DATABASE
DML---> DATA MANIPULATION
TO MANIPULATE TABLE
--> INSERT
TO INSERT THE RECORDS
--> UPDATE
TO MODIFY THE EXISTING VALUE
--> DELETE
TO REMOVE ALL THE RECORDS OR SET OF RECORDS OR ONE
RECORD
TCL---> TRANSACTION CONTROL
TO CONTROL THE TRANSACTIONS
--> COMMIT
TO SAVE THE TRANSACTIONS
--> ROLLBACK
TO TAKES US THE POINT WHERE WE COMMITTED
--> SAVEPOINT
TO CREATE CHECK POINTS OR RESTORATION POINTS
DCL---> DATA CONTROL
TO CONTROL THE FLOW OF DATA
--> GRANT
TO GIVE PERMISSION
--> REVOKE
TO TAKEBACK THE PERMISSION
DQL---> DATA QUERY
TO RETRIEVE OR FETCH THE DATA
--> SELECT
TO DISPLAY THE FINAL OUTPUT
--> PROJECTION
TO RETRIEVE THE DATA BY SELECTING ONLY COLUMNS
--> SELECTION
TO RETRIEVE THE DATA BY SELECTING ROWS AND COLUMNS
--> JOINS
TO RETRIEVE THE DATA FROM MULTIPLE TABLES SIMULTANEOUSLY
CREATE
CREATE TABLE TABLE_NAME
(
COLUMN_NAME1 DATATYPE [CONSTRAINTS],
COLUMN_NAME2 DATATYPE [CONSTRAINST],
,
COLUMN_NAMEn DATATYPE [CONSTRAINTS]
);
RENAME
RENAME EXISTING_TABLE_NAME TO NEW_TABLE_NAME;
ALTER
TO ADD A COLUMN
ALTER TABLE TABLE_NAME
ADD COLUMN_NAME DATATYPE [CONSTRAINTS];
TO RENAME A COLUMN
ALTER TABLE TABLE_NAME
RENAME COLUMN EXISTING_COLUMN_NAME TO
NEW_COLUMN_NAME;
TO DROP A COLUMN
ALTER TABLE TABLE_NAME
DROP COLUMN COLUMN_NAME;
TO MODIFY THE DATATYPE
ALTER TABLE TABLE_NAME
MODIFY COLUMN_NAME NEW_DATATYPE;
TO MODIFY NULL/NOT NULL
ALTER TABLE TABLE_NAME
MODIFY COLUMN_NAME DATATYPE NULL/NOT NULL;
TO ADD A UNIQUE
ALTER TABLE TABLE_NAME
ADD CONSTRAINTS CONSTRAINT_REF_NAME
UNIQUE(COLUMN_NAME);
TO ADD A CHECK
ALTER TABLE TABLE_NAME
ADD CONSTRAINTS CONSTRAINT_REF_NAME CHECK(CONDITION);
TO ADD A PRIMARY KEY
ALTER TABLE TABLE_NAME
ADD CONSTRAINTS CONSTRAINT_REF_NAME PRIMARY
KEY(COLUMN_NAME);
TO ADD A FOREIGN KEY
ALTER TABLE TABLE_NAME
ADD CONSTRAINTS CONSTRAINT_REF_NAME FOREIGN
KEY(COLUMN_NAME)
REFERENCES PARENT_TABLE_NAME(COLUMN_NAME);
TRUNCATE
TRUNCATE TABLE TABLE_NAME;
DROP
DROP TABLE TABLE_NAME;
FLASHBACK
FLASHBACK TABLE TABLE_NAME
TO BEFORE DROP;
PURGE
PURGE TABLE TABLE_NAME;
INSERT
INSERT INTO TABLE_NAME VALUES(V1,V2,,,Vn);
INSERT INTO TABLE_NAME VALUES(&COLUMN1,&COLUMN2,,,&COLUMNn);
INSERT INTO
TABLE_NAME(COLUMN1,COLUMN2,,,COLUMNn)VALUES(V1,V2,,,,Vn);
UPDATE
UPDATE TABLE_NAME
SET COLUMN_NAME=NEW_VALUE
[WHERE <FILTER_CONDITION>];
UPDATE TABLE_NAME
SET COLUMN_NAME=NEW_VALUE , COLUMN_NAME=NEW_VALUE
[WHERE <FILTER_CONDITION>];
DELETE:
DELETE FROM TABLE_NAME
[WHERE <FILTER_CONDITION>];
COMMIT
COMMIT;
ROLLBACK
ROLLBACK;
SAVEPOINT
SAVEPOINT SAVEPOINT_NAME;
ROLLBACK TO SAVEPOINT_NAME;
GRANT
GRANT SQL_STATEMENT
ON TABLE_NAME
TO USER_NAME;
REVOKE
REVOKE SQL_STATEMENT
ON TABLE_NAME
FROM USER_NAME;
CARTESIAN JOIN/CROSS JOIN
ANSI
SELECT COLUMN-NAME/EXPRSSION
FROM TABLE_NAME1 CROSS JOIN TABLE_NAME2;
ORACLE
SELECT COLUMN_NAME/EXPRESSION
FROM TABLE_NAME1 , TABLE_NAME2;
INNER JOIN/EQUI JOIN
ANSI
SELECT COLUMN_NAME/EXPRESSION
FROM TABLE_NAME1 INNER JOIN TABLE_NAME2
ON <JOIN_CONDITION>;
ORACLE
SELECT COLUMN_NAME/EXPRESSION
FROM TABLE_NAME1 , TABLE_NAME2
WHERE <JOIN_CONDITION>;
NATURAL JOIN
ANSI
SELECT COLUMN-NAME/EXPRESSION
FROM TABLE_NAME1 NATURAL JOIN TABLE_NAME2;
SELF JOIN
ANSI
SELECT COLUMN_NAME/EXPRESSION
FROM TABLE_NAEM T1 JOIN TABLE_NAME T2
ON T1.COLUMN_NAME=T2.COLUMN_NAME;
ORACLE
SELECT COLUMN_NAME/EXPRESSION
FROM TABLE_NAME T1, TABLE_NAME T2
WHERE T1.COLUMN_NAME=T2.COLUMN_NAME;
LEFT OUTER JOIN
ANSI
SELECT COLUMN_NAME/EXPRESSION
FROM TABLE_NAEM1 LEFT [OUTER] JOIN TABLE_NAME2
ON TABLE_NAME1.COLUMN_NAME=TABLE_NAME2.COLUMN_NAME;
ORACLE
SELECT COLUMN_NAME/EXPRESSION
FROM TABLE_NAEM1 , TABLE_NAME2
WHERE TABLE_NAME1.COLUMN_NAME=TABLE_NAME2.COLUMN_NAME(+);
RIGHT OUTER JOIN
ANSI
SELECT COLUMN_NAME/EXPRESSION
FROM TABLE_NAEM1 RIGHT [OUTER] JOIN TABLE_NAME2
ON TABLE_NAME1.COLUMN_NAME=TABLE_NAME2.COLUMN_NAME;
ORACLE
SELECT COLUMN_NAME/EXPRESSION
FROM TABLE_NAEM1 , TABLE_NAME2
WHERE TABLE_NAME1.COLUMN_NAME(+)=TABLE_NAME2.COLUMN_NAME;
FULL OUTER JOIN
ANSI
SELECT COLUMN_NAME/EXPRESSION
FROM TABLE_NAEM1 FULL [OUTER] JOIN TABLE_NAME2
ON TABLE_NAME1.COLUMN_NAME=TABLE_NAME2.COLUMN_NAME;
DIFFERENCE BETWEEN VARCHAR AND VARCHAR2
VARCHAR-----> 2000 CHARACTERS
VARCHAR2----> 4000 CHARACTERS
SET OPERATORS
TO COMBINE TWO OR MORE SQL QUERIES
--> UNION
IT WILL COMBINE SQL QUERIES BY REMOVING THE DUPLICATED
VALUES
--> UNION ALL
IT WILL COMBINE SQL QUERIES BY RETRIEVING ALL THE RECORDS
--> INTERSECT
IT WILL COMBINE SQL QUERIES BY RETRIEVING COMMOM RECORD
--> MINUS
IT WILL COMBINE SQL QUERIES BY RETIEVING TABLE1 RECORDS IF
THOSE ARE
NOT PRESENT TABLE2
CONSTRAINTS
IT IS AN EXTRA VALIDATION WHICH IS ASSIGNED FOR AN COLUMN
TYPES OF CONSTRAINTS
UNIQUE
NOT NULL
CHECK
PRIMARY KEY
FOREIGN KEY
JOINS
THE PROCESS OF RETRIEVING THE DATA FROM MULTIPLE TABLES
SIMULTANEOUSLY
WHEN OR WHY DO WE USE JOINS
WHENEVER THE DATA TO BE DISPLAYED PRESENT IN MULTIPLE TABLES WE
USE
JOINS
TYPES OF JOINS
WE HAVE 5 TYPES OF JOINS
--> CARTESIAN JOIN/CROSS JOIN
--> INNER JOIN/EQUI JOIN (MATCHED RECORDS)
--> NATURAL JOIN
--> SELF JOIN
--> OUTER JOIN (UNMATCHED RECORDS)
--> LEFT OUTER JOIN
--> RIGHT OUTER JOIN
--> FULL OUTER JOIN
WHEN OR WHY DO WE USE SELF JOIN
WHENEVE THE DATA TO BE DISPLAYED PRESENT IN SAME TABLE
BUT
IN DIFFERENT RECORDS WE USE SELF JOIN
TO RETRIEVE THE DATA FROM MULTIPLE TABLES(DIFFERENT) ----> INNER JOIN
IN INNER JOIN, JOIN_CONDITION IS MANDATORY
THE RELATION BETWEEN THE TABLES (PRIMARY KEY AND FOREIGN
KEY)
TO RETRIEVE THE DATA FROM MULTIPLE TABLES(SAME) -------> SELF JOIN
EMPLOYEE AND MANAGER RELATION (MGR=EMPNO) (EMPNO=MGR)
LIKE OPERATOR ----> TO RETRIEVE PATTERN MATCHING
DIFFERNECE BETWEEN TRUNCATE AND DROP AND DELETE
TRUNCATE
--> TRUNCATE IS USED TO REMOVE ALL THE RECORDS FROM THE TABLE
--> THERE IS NO BACKUP OPTION FOR TRUNCATE
--> TRUNCATE IS A PERMANENT
DROP
--> DROP IS USED TO REMOVE THE TABLE FROM DATABASE
--> THERE IS A BACKUP OPTION FOR DROP
--> DROP IS A TEMPORARY
DELETE
--> DELETE IS USED TO REMOVE ALL THE RECORDS OR SET OF RECORDS
OR ONE RECORD
--> DELETE IS A TEMPORARY
SYNTAX FOR SUB QUERY
SELECT COLUMN_NAME/EXPRESSION
FROM TABLE_NAME
WHERE COLUMN_NAME/EXPRSSION OPERATOR (SELECT
COLUMN_NAME/EXPRESSION
FROM TABLE_NAME
[WHERE <FILTER_CONDITION>]);
PRIMARY KEY
--> PRIMARY KEY IS USED TO IDENTIFY A RECORD UNIQUELY FROM THE
TABLE
--> PRIMARY KEY WILL NOT ACCEPT DUPLICATED OR REPEATED VALUES
--> PRIMARY KEY WILL NOT ACCEPT NULL
--> PRIMARY KEY IS ALWAYS THE COMBINATION OF UNIQUE AND NOT NULL
--> WE SHOULD HAVE ONLY ONE PRIMARY KEY IN A TABLE
--> PRIMARY KEY IS ALSO CALLED AS UNIQUE KEY
FOREIGN KEY
--> FOREIGN KEY IS USED TO ESTABLISH THE CONNECTION BETWEEN
TABLES
--> FOREIGN KEY WILL ACCEPT THE DUPLICATED VALUES
--> FOREIGN KEY WILL ACCEPT NULL VALUES
--> FOEIGN KEY IS NOT A COMBINATION UNIQUE AND NOT NULL
--> WE CAN HAVE MULITPLE FOREIGN KEYS IN A TABLE
--> FOREIGN KEY IS ALSO CALLED AS REFERENTIAL INTEGRITY
CONSTRAINT
REFERENTIAL INTEGRITY CONSTRAINT
THE PARENT TABLE CANNOT BE DESTORED UNTIL UNLESS HAVING
CONNECTION WITH
CHILD TABLE
SUB QUERY
DEF
WORKING PROCEDURE
WHEN OR WHY DO WE USE SUB QUERY
TYPES OF SUB QUERY
SUB QUERY OPERATORS
NESTED SUB QUERY
TYPES OF ATTRIBUTES
KEY ATTRIBUTES/CANDIDATE KEY
NON-KEY ATTRIBUES
PRIME KEY ATTRIBUTE
NON-PRIME KEY ATTRIBUTE
COMPOSITE KEY ATTRIBUE
SUPER KEY ATTRIBUTE
FOREIGN KEY ATTRIBUTE
TYPES OF FUNCTIONAL DEPENDENCY
TOTAL FUNCTIONAL DEPENDENCY
PARTILA FUNCTIONAL DEPENDENCY
TRANSTITIVE FUNCTIONAL DEPENDENCY
NORMALISATION
FIRST NORMAL FORM (1NF)
SECOND NORMAL FORM (2NF)
THIRD NORMAL FORM (3NF)
BOYCE-CODD NORMAL FORM (BCNF/3.5NF)
DIFFERNECE BETWEEN HAVING CLAUSE AND WHERE CLAUSE
GROUP BY CLAUSE
HAVING CLAUSE
MULTI ROW FUNCTION/GROUP FUNCTION/AGGREGATE FUNCTION
