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