SQL(Structured Query Languages)
Standard
Languages for Databases
ANSI
(American National Standard Institute) Standard
Used
to Interact / communicate with database
Once
Learned user every where
SQL
is re-categorized in sub-languages according to tasks:
DCL(Data Control Languages)
Used
to Control Database Security
Create
User, Change Password, Assign Rights, snatch rights
CREATE
USER, GRANT RIGHTS, REVOKE RIGHTS
CREATE USER
You can create new users
by this command and assign password to it.
CREATE USER SCOTT
IDENTIFIED BY TIGER;
ALTER USER
ALTER
USER SCOTT IDENTIFIED BY TIGERS;
GRANT RIGHTS
GRANT
RESOURCE, CONNECT, CREATE TABLE, CREATE SESSION,DBA TO SCOTT;
REVOKE RIGHTS
REVOKE
CREATE SESSION FROM SCOTT;
DDL(Data Definition Language)
Used
to interact with database
Define
objects, change objects, remove objects
CREATE,
ALTER, DROP
CREATE TABLE
CREATE
TABLE EMP(
EMPNO NUMBER(10) primary key,
ENAME VARCHAR2(30),
SAL NUMBER(12,2),
COMM NUMBER(12,2),
DEPTNO NUMBER(10)
);
ALTER TABLE
ALTER
TABLE EMP
ADD MGR NUMBER(5);
ALTER TABLE EMP
MODIFY ENAME VARCHAR2(50);
DROP TABLE
DROP
TABLE EMP;
TRUNCATE
TRUNCATE TABLE EMP_HISTORY;
DML (Data Manipulation Language)
Used
to interact with tables
Store
new records, change records, remove records, select data from tables.
INSERT,UPDATE,
DELETE, SELECT
INSERT
INSERT
INTO EMP
VALUES
(1,’ASAD’,5000,500,7787,10);
INSERT INTO EMP(EMPNO,ENAME,SAL)
VALUES (2,’ALEEM’,7000);
INSERT INTO EMP(EMPNO,ENAME,SAL)
VALUES
(&no,’&name’,&salary);
INSERT INTO EMP_HISTORY
SELECT * FROM EMP;
UPDATE
UPDATE
EMP
SET MGR = 7767;
UPDATE EMP
SET COMM = 200
WHERE COMM IS NULL;
UPDATE EMPLOYEES
SET JOB_ID = (SELECT
JOB_ID
FROM EMPLOYEES
WHERE EMPLOYEE_ID = 205),
SALARY = (SELECT
SALARY
FROM EMPLOYEES
WHERE EMPLOYEE_ID = 205)
WHERE
EMPLOYEE_ID = 114;
UPDATE COPY_EMP
SET
DEPARTMENT_ID = (SELECT DEPARTMENT_ID
FROM EMPLOYEES
WHERE EMPLOYEE_ID = 100)
WHERE JOB_ID =
(SELECT JOB_ID
FROM EMPLOYEES
WHERE EMPLOYEE_ID = 200);
DELETE
DELETE
FROM EMP;
DELETE FROM EMP
WHERE SAL < 2000;
DELETE FROM EMPLOYEES
WHERE DEPARTMENT_ID =
(SELECT DEPARTMENT_ID
FROM DEPARTMENTS
WHERE DEPARTMENT_NAME
LIKE '%PUBLIC%');
SELECT
SELECT
* FROM EMP;
SELECT ENAME,JOB,SAL
FROM EMP;
SELECT ENAME EMPLOYEE,JOB
DESIGNATION, SAL SALARY
FROM EMP;
SELECT ENAME,JOB,SAL,SAL*.2 BONUS
FROM EMP;
SELECT ‘The name of Employee
is’||ENAME
FROM EMP;
SELECT * FROM EMP
ORDER BY ENAME;
SELECT * FROM EMP
ORDER BY 2;
SELECT * FROM EMP
ORDER BY JOB,DEPTNO;
SELECT * FROM EMP
ORDER BY JOB DESC,DEPTNO;
SELECT * FROM EMP
WHERE SAL < 3000;
SELECT * FROM EMP
WHERE
SAL > 5000;
SELECT * FROM EMP
WHERE DEPTNO = 20;
SELECT * FROM EMP
WHERE DEPTNO IN (10,30,40);
SELECT * FROM EMP
WHERE SAL BETWEEN 2000 AND 5000;
SELECT * FROM EMP
WHERE ENAME LIKE ‘A%;’
SELECT * FROM EMP
WHERE ENAME LIKE ‘%ALI%’;
SELECT * FROM EMP
WHERE JOB = ‘MANAGER’
AND SAL < 3000;
SELECT * FROM EMP
WHERE JOB = ‘CLERK’
OR SAL > 2000;
SELECT * FROM EMP
WHERE COMM IS NOT NULL;
TCL (Transaction Control Language)
Used
to control Transactions
COMMIT,
ROLLBACK, SAVEPOINT
COMMIT;
ROLLBACK;
SAVEPOINT;
Operator Precedence
|
Operator
|
Meaning
|
|
1
|
Arithmetic operators
|
|
2
|
Concatenation operator
|
|
3
|
Comparison conditions
|
|
4
|
IS [NOT] NULL, LIKE, [NOT] IN
|
|
5
|
[NOT] BETWEEN
|
|
6
|
Not equal to
|
|
7
|
NOT logical condition
|
|
8
|
AND logical condition
|
|
9
|
OR logical condition
|
Functions
Single
Row
CHARACTER
FUNCTIONS
UPPER
SELECT UPPER(ENAME)
FROM EMP;
LOWER
SELECT LOWER(ENAME
FROM EMP;
INITCAP
SELECT INITCAP(ENAME)
FROM EMP;
LENGTH
SELECT
LENGTH(ENAME) FROM EMP;
SUBSTR
SELECT
SUBSTR(ENAME,2,3) FROM EMP;
INSTR
SELECT
INSTR(ENAME,’A’) FROM EMP;
LPAD
SELECT
LPAD(ENAME,10,’~’) FROM EMP;
RPAD
SELECT
RPAD(ENAME,10,’~’) FROM EMP;
REPLACE
SELECT
REPLACE(ENAME,’S’,’A’) FROM EMP;
TRIM
SELECT TRIM (‘H’ FROM
‘HELLO WORLD’) FROM EMP;
NUMERIC
FUNCTIONS
ROUND
SELECT
ROUND(17879.879,2) FROM DUAL;
SELECT
ROUND(17879.241,2) FROM DUAL;
TRUNC
SELECT
TRUNC(17879.879,2) FROM DUAL;
SELECT
TRUNC(17879.241,2) FROM DUAL;
MOD
SELECT MOD(15,4) FROM
DUAL;
DATE FUNCTIONS
ADD_MONTHS
SELECT
ADD_MONTHS(HIREDATE,10) FROM EMP;
MONTHS_BETWEEN
SELECT
MONTHS_BETWEEN(SYSDATE,HIREDATE) FROM EMP;
LAST_DAY
SELECT
LAST_DAY(SYSDATE) FROM DUAL;
NEXT_DAY
ROUND
TURNC
CONVERSION FUNCTIONS
TO_CHAR
SELECT
TO_CHAR(SYSDATE,’DD-MM-RR HH:MI’) FROM DUAL;
SELECT
TO_CHAR(SAL,’999,999.00’) FROM EMP;
TO_NUMBER
SELECT
TO_NUMBER(SUBSTR(HIREDATE,1,2)) FROM EMP;
TO_DATE
SELECT
TO_DATE(’10-10-04’,’MM-DD-YY’) FROM DUAL;
OTHER FUNCTIONS
NVL
SELECT NVL(SAL,100)
FROM EMP;
NVL2
SELECT
NVL2(COMM,SAL+COMM,SAL) FROM EMP;
NULLIF (if both parameters in the
function are equal then return null)
SELECT
NULLIF(1234,1234) FROM DUAL;
COALESCE
(return first non null value)
SELECT
COALESCE(SUBSTR('ABC',4),'NOT BC','NO SUBSTRING') FROM DUAL;
DECODE
SELECT
DECODE(JOB,’MANAGER’,SAL*.2,’CLERK’,SAL*.1,SAL*.05) FROM EMP;
CASE EXPRESSION
SELECT CASE JOB WHEN
‘PRESIDENT’ THEN ‘A+’
WHEN ‘MANAGER’ THEN
‘A’
ELSE ‘B’ END
FROM EMP;
Group Functions
SUM
SELECT SUM(SAL) FROM EMP;
SELECT SUM(SAL) FROM EMP
GROUP BY DEPTNO;
SELECT SUN(SAL) FROM EMP
GROUP BY JOB;
AVG
SELECT AVG(SAL) FROM EMP
GROUP BY DEPTNO;
MIN
SELECT MIN(SAL) FROM EMP;
MAX
SELECT MAX(SAL) FROM EMP;
COUNT
SELECT COUNT(*) FROM EMP;
SELECT COUNT(*) FROM EMP
GROUP BY JOB;
Having Clause
SELECT SUM(SAL)
FROM EMP
GROUP BY JOB
HAVING SUM(SAL) > 5000;
Joins
INNER
JOIN
SELECT DNAME,ENAME,JOB,SAL
FROM EMP,DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO;
OUTER JOIN
SELECT DNAME,ENAME,JOB,SAL
FROM EMP,DEPT
WHERE EMP.DEPTNO(+) = DEPT.DEPTNO;
SELF JOIN
SELECT A.ENAME MANAGER,A.EMPNO MANAGERID,
B.ENAME SUBORDINATE,B.EMPNO SUBORDINATEID
FROM EMP A,EMP B
WHERE A.EMPNO = B.MGR;
NATURAL JOIN
SELECT DEPTNO,DNAME,ENAME,JOB,SAL
FROM DEPT
NATURAL JOIN EMP
USING CLAUSE
SELECT DNAME,JOB,SAL
FROM EMP
JOIN DEPT
USING (DEPTNO)
ON CLAUSE
SELECT DNAME,ENAME,JOB,SAL
FROM EMP JOIN DEPT
ON (EMP.DEPTNO = DEPT.DEPTNO);
SELF JOIN USING ON CLAUSE
SELECT
A.ENAME SUBNAME,A.JOB SUBJOB,
A.EMPNO SUBNO, B.ENAME MNGNAME,
B.JOB MNGJOB,B.EMPNO MNGNO
FROM EMP A JOIN EMP B
ON (A.MGR = B.EMPNO)
Three way join with ON CLAUSE
SELECT EMPNO,CITY,DNAME
FROM EMP
JOIN DEPT
ON (EMP.DEPTNO = DEPT.DEPTNO)
JOIN LOC
ON (LOC.LOCATION_ID = DEPT.LOCATION_ID)
Non equi join
SELECT E.LAST_NAME,E.SALARY,J.GRADE_LEVEL
FROM EMP E JOIN JOB_GRADES J
ON E.SAL BETWEEN J.LOWEST_SAL AND
J.HIGHEST_SAL
LEFT
OUTER JOIN
SELECT DNAME,ENAME,JOB,SAL
FROM EMP E LEFT OUTER JOIN DEPT D
ON (E.DEPTNO = D.DEPTNO)
RIGHT
OUTER JOIN
SELECT DNAME,ENAME,JOB,SAL
FROM EMP E RIGHT OUTER JOIN DEPT D
ON (E.DEPTNO = D.DEPTNO)
FULL OUTER JOIN
SELECT
DNAME,ENAME,JOB,SAL
FROM EMP E FULL OUTER JOIN DEPT D
ON (E.DEPTNO = D.DEPTNO)
CORSS JOIN
SELECT DNAME,ENAME,JOB,SAL
FROM EMP
CROSS JOIN DEPT
Sub-Query
SINGLE
ROW SUBQUERY
SELECT * FROM EMP
WHERE SAL < (SELECT SAL FROM EMP WHERE ENAME =
‘SMITH’);
MULTIPLE ROW
SUBQUERY (IN)
SELECT * FROM EMP
WHERE
SAL IN (SELECT SAL FROM EMP WHERE DEPTNO = 10)
MULTIPLE
ROW SUBQUERY (ANY)
SELECT
* FROM EMP
WHERE
SAL < ANY (SELECT SAL FROM EMP WHERE DEPTNO = 10)
MULTIPLE
ROW SUBQUERY (ALL)
SELECT
* FROM EMP
WHERE
SAL < ALL (SELECT SAL FROM EMP WHERE DEPTNO = 10)
Set Operators
UNION
(returns both queries after eliminating duplications)
UNION
ALL (returns results from both queries, including all duplications)
INTERSECT
(returns rows that are common to both queries)
MINUS
(returns rows in the first query that are not present in second query)
Views
CREATE VIEW ORG_EMP_V AS
SELECT A.ENAME MANAGER,A.EMPNO MANAGERID,
B.ENAME SUBORDINATE,B.EMPNO SUBORDINATEID
FROM EMP A,EMP B
WHERE A.EMPNO = B.MGR;
Sequence
CREATE SEQUENCE SEQ_EMP1
START WITH 1
INCREMENT BY 1
NOCACHE;
CREATE SEQJUENCE SEQ_EMP2
START WITH 100
INCREMENT BY 1
MINVALUE 90
MAXVALUE 150
CYCLE;
SELECT SEQ_EMP1.NEXTVAL
FROM
DUAL;
SELECT
SEQ_EMP1.CURRVAL
FROM
DUAL;
Synonym
CREATE SYNONYM E FOR EMP;
Index
CREATE INDEX IDX_EMP_ENAME ON EMP(ENAME);
Constraints
PRIMARY KEY
UNIQUE
NOT NULL
FOREIGN KEY
CHECK
CREATE TABLE DEPT(
DEPTNO NUMBER(10) PRIMARY
KEY,
DNAME VARCHAR2(100) NOT
NULL);
CREATE TABLE EMP(
EMPNO NUMBER(10) PRIMARY
KEY,
ENAME VARCHAR2(100) NOT
NULL,
PHONE VARCHAR2(15) UNIQUE,
SAL NUMBER(15,2),
DEPTNO NUMBER(10),
CHECK (SAL <
3000),
FOREIGN KEY (DEPTNO) REFERENCES EMP);
Security
CREATE ROLE R1 IDENTIFIED BY R1;
GRANT SELECT ON EMP TO R1;
GRANT UPDATE,DELETE ON EMP TO R1;
GRANT R1 TO SCOTT;
PL/SQL NOTES
Block
Declaration
Section (Declare all variable, constants, cursors, exceptions)
Executable
Section (All executable PL/SQL Commands, call other procedures,DML)
Exception
Section (Tran Run time errors and give
your own messages)
Variable
PL/SQL
Variable
Scalar
variable
Composite
variable
Declare
X number(10);
V_EMPNO EMP.EMPNO%TYPE;
V_EMP EMP%ROWTYPE;
BEGIN
SELECT *
INTO V_EMP
FROM EMP
WHERE EMPNO = 7788;
DBMS_OUTPUT.PUT_LINE(V_EMP.ENAME);
END;
DML in PL/SQL
SELECT
INSERT
UPDATE
DELETE
Declare
X number(10);
V_EMPNO EMP.EMPNO%TYPE;
V_EMP EMP%ROWTYPE;
BEGIN
SELECT *
INTO V_EMP
FROM EMP
WHERE EMPNO = 7788;
DBMS_OUTPUT.PUT_LINE(V_EMP.ENAME);
INSERT INTO EMP1 (EMPNO,ENAME,JOB,SAL)
VALUES (V_EMP.EMPNO,V_EMP.ENAME,V_EMP.JOB,V_EMP.SAL);
UPDATE EMP1
SET SAL = V_EMP.SAL
WHERE EMPNO = V_EMP.EMPNO;
DELETE FROM EMP1
WHERE DEPTNO = V_EMP.DEPTNO;
END;
Flow Control
If condition
DECLARE
V_EMP EMP%ROWTYPE;
BEGIN
SELECT *
INTO V_EMP
FROM EMP
WHERE ENAME =
‘SCOTT’;
IF V_EMP.SAL <
3000 THEN
DBMS_OUTPUT.PUT_LINE(‘A’);
ELSIF V_EMP.SAL <
2000 THEN
DBMS_OUTPUT.PUT_LINE(‘B’);
ELSIF V_EMP.SAL <
1000 THEN
DBMS_OUTPUT.PUT_LINE(‘C’);
ELSE
DBMS_OUTPUT.PUT_LINE(‘D’);
END IF;
END;
Basic loop
DECLARE
X NUMBER := 1;
BEGIN
LOOP
X := X +1
;
DBMS_OUTPUT.PUT_LINE(X);
EXIT WHEN
X = 10;
END LOOP;
END;
While loop
DECLARE
X NUMBER := 1;
BEGIN
WHILE X < 10 LOOP
X := X
+1;
DBMS_OUTPUT.PUT_LINE(X);
END LOOP;
END;
For loop
BEGIN
FOR I IN 1..10 LOOP
DBMS_OUTPUT.PUT_LINE(I):
END LOOP;
END;
Exceptions
DECLARE
V_EMP EMP%ROWTYPE;
BEGIN
SELECT *
INTO V_EMP
FROM EMP
WHERE DEPTNO = 1;
DBMS_OUTPUT.PUT_LINE(V_EMP.ENAME);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(‘SORRY, NO
DATA FOUND’);
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE(‘SORRY, MANY
RECORDS FOUND’);
END;
Cursor
DECLARE
V_EMP EMP%ROWTYPE;
CURSOR C_EMP IS
SELECT *
INTO V_EMP
FROM EMP
WHERE DEPTNO = 10;
BEGIN
OPEN C_EMP;
LOOP
FETCH C_EMP INTO V_EMP;
EXIT WHEN C_EMP%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(V_EMP.ENAME);
END LOOP;
END;
Cursor for Loop
BEGIN
FOR I IN (SELECT * FROM EMP) LOOP
DBMS_OUTPUT.PUT_LINE(I.ENAME);
END LOOP;
END;
Procedures
IN PARAMETER
CREATE OR REPLACE PROCEDURE P_EMP(P_EMPNO NUMBER)
IS
V_ENAME EMP.ENAME%TYPE;
BEGIN
SELECT ENAME
INTO V_ENAME
FROM EMP
WHERE EMPNO = P_EMPNO;
DBMS_OUTPUT.PUT_LINE(V_ENAME);
END;
Call
Procedure
BEGIN
P_EMP(100);
END;
OUT PARAMETER
CREATE OR REPLACE PROCEDURE P_EMP(P_EMPNO IN NUMBER,
P_ENAME OUT VARCHAR2)
IS
BEGIN
SELECT ENAME
INTO P_ENAME
FROM EMP
WHERE EMPNO = P_EMPNO;
END;
Call
Procedure
DECLARE
X NUMBER;
BEGIN
P_EMP(100,X);
END;
IN OUT PARAMETER
CREATE OR REPLACE PROCEDURE P_EMP(P_EMPNO_SAL IN OUT
NUMBER)
IS
BEGIN
SELECT SAL
INTO P_EMPNO_SAL
FROM EMP
WHERE EMPNO = P_EMPNO_SAL;
END;
DECLARE
X NUMBER := 100;
BEGIN
P_EMP(X);
END;
Functions
CREATE OR REPLACE FUNCTION F_EMP(P_EMPNO IN NUMBER)
RETURN VARCHAR2
IS
V_EMP EMP%ROWTYPE;
BEGIN
SELECT ENAME
INTO P_ENAME
FROM EMP
WHERE EMPNO = P_EMPNO;
END;
CALL FUNCTION
SELECT F_EMP(7788) FROM DUAL;
TRIGGER
CREATE OR REPLACE TRIGGER INS_EMP
AFTER INSERT OR DELETE OR UPDATE OF SAL ON EMP
REFERENCES NEW AS N OLD AS O
FOR EACH ROW
BEGIN
IF INSERTING THEN
INSERT INTO
EMP1(EMPNO,ENAME,JOB)
VALUES (:
ELSIF UPDATING THEN
UPDATE EMP1
SET SAL = :N.SAL
WHERE EMPNO = :O.EMPNO;
ELSIF DELETING THEN
DELETE FROM EMP
WHERE EMPNO = :O.EMPNO;
END IF;
END;
No comments:
Post a Comment