SQL

---

---CREATE TABLE table_name(column_name1 datatype, column_name2 datatype, ... column _nameN datatype );

CREATE TABLE Dept(DeptNo NUMBER(2), DeptName VARCHAR2(10) CreateDate DATE DEFAUL T sysdate);

CREATE TABLE temp_employee as SELECT * FROM employee ALTER TABLE table_name ADD column_name datatype; ALTER TABLE employee ADD experience number(3); ALTER TABLE table_name DROP COLUMN column_name; ALTER TABLE employee DROP COLUMN location;

ALTER TABLE table_name MODIFY column_name datatype; ALTER TABLE employee MODIFY salary number(15,2); RENAME old_table_name To new_table_name;

RENAME employee TO my_emloyee; DROP TABLE table_name;

TRUNCATE TABLE table_name

---

---SELECT column_list FROM table-name[WHERE Clause][GROUP BY clause][HAVING clause] [ORDER BY clause];

INSERT INTO TABLE_NAME [ (col1, col2, col3,...colN)] VALUES (value1, value2, val ue3,...valueN);

INSERT INTO TABLE_NAME VALUES (value1, value2, value3,...valueN);

INSERT INTO employee (id, name, dept, age, salary location) SELECT emp_id, emp_n ame, dept, age, salary, location FROM temp_employee;

UPDATE table_name SET column_name1 = value1,column_name2 = value2, ...[WHERE con dition]

UPDATE employee SET location ='Mysore' WHERE id = 101;

UPDATE employee SET job_id =(SELECT job_id FROM emp1 where empid=100),

salary =(SELECT salary FROM emp2 where e mpid=200)

WHERE empid = 50

DELETE FROM table_name [WHERE condition]; DELETE FROM employee WHERE id = 100;

DELETE FROM employee WHERE Deptid = (SELECT deptid FROM dept WHERE DeptName LIKE '%Public%')

---

---COMMIT:

DELETE FROM Dept WHERE DeptID IN (10,20) UPDATE Emp SET DeptID = 80 WHERE empID = 100 COMMIT;

(2)

ROLLBACK:

DELETE FROM Dept WHERE DeptID IN (10,20) ROLLBACK; SAVEPOINT: UPDATE... SAVEPOINT Update_Done; INSERT... ROLLBACK TO Update_Done; ---

---GRANT: GRANT SELECT ON employee TO user1; REVOKE: REVOKE SELECT ON employee FROM user1; ROLE: CREATE ROLE Test [IDENTIFIED BY PWD];

DROP ROLE Test;

---

---LOWER: Lower('SQL Course') = sql course UPPER: Upper('SQL Course') = SQL COURSE INITCAP: InitCap('SQL Course') = SQL Course CONCAT: Concat('Hello','World') = HelloWorld SUBSTR: Substr('HelloWorld'1,5) = Hello LENGTH: Lenght('HelloWorld') = 10

INSTR: Instr('HelloWorld','W') = 6 LPAD: Lpad(Salary,10,'*')=*****24000 RPAD: Rpad(Salary,10,'*')=24000*****

REPLACE: Replace('Jack and Jue','J','BL') = Black and Blue TRIM: Trim('H' from 'HelloWorld') = elloWorld

ROUND: Round(45.926,2) = 45.93 TRUNC: Trunc(45.926,2) = 45.92 MOD: MOD(1600,300) = 100

MONTHS_BETWEEN: Months_Between('01-SEP-95','11-JAN-94') = 19.677 ADD_MONTHS: Add_Months('11-JAN-94',6) = '11-JUL-94' NEXT_DAY: Next_Day('01-SEP-95','Friday') = '08-SEP-95' LAST_DAY: Last_Day('01-Feb-95') = '28-Feb-95'

LIKE: SELECT first_name, last_name FROM student_details WHERE first_na me LIKE 'S%';

BETWEEN AND:SELECT first_name, last_name, age FROM student_details WHERE age BET WEEN 10 AND 15;

IN: SELECT first_name, last_name, subject FROM student_detai ls WHERE subject IN ('Maths', 'Science');

ORDER BY: SELECT column-list FROM table_name [WHERE condition][ORDER BY co lumn1 [, column2, .. columnN] [DESC]];

SELECT name, salary FROM employee ORDER BY salary; SELECT name, salary FROM employee ORDER BY 1, 2; COUNT: SELECT COUNT (*) FROM employee WHERE Dept = 'Electronics'; DISTINCT: SELECT DISTINCT Dept FROM employee;

SELECT COUNT (DISTINCT name) FROM employee; MAX: SELECT MAX (salary) FROM employee;

MIN: SELECT MIN (salary) FROM employee; AVG: SELECT AVG (salary) FROM employee; SUM: SELECT SUM (salary) FROM employee;

GROUP BY: SELECT location, Dept, SUM (salary)FROM employee GROUP BY locati on, dept;

HAVING: SELECT Dept, SUM (salary)FROM employee GROUP BY dept HAVING SUM (salary) > 25000

(3)

ANY: SELECT EmpID, LastName, JobID, Salary FROM Employees

WHERE Salary < ANY (SELECT Salary FROM Employees WHERE J obID='IT')

< ANY means less than Max Salary in return salar ies

> ANY means more than Min Salary in return salar ies

= ANY means IN

ALL: SELECT EmpID, LastName, JobID, Salary FROM Employees

WHERE Salary < ALL (SELECT Salary FROM Employees WHERE J obID='IT')

< ALL means less than Min Salary > ALL means more than Max Salary NVL: IF Commission NULL, replace value.

NVL(Commission,0)

NVL(Hire_Date,'01-JAN-97') NVL(Job_ID,'No JOB')

NVL2: IF Commission NULL, 1stValue else 2ndValue NVL2(Commission,'2ndValue','1stValue')

NULLIF: Compares and if values/expressions are equal, returns NULL. NULLIF(Length(FirstName),Length(LastName))

COALESCE:IF Manager_ID Not NULL use it else use commission and if Commission NUL L use -1.

COALESCE(Manager_ID,Commission,-1)

---

A]PRIMARY KEY

-Column level: Column name datatype [CONSTRAINT constraint_name] PRIMARY KEY CREATE TABLE employee( id number(5) PRIMARY KEY, Name char(20),salary number(10),location char(10));

CREATE TABLE employee( id number(5) CONSTRAINT e mp_id_pk PRIMARY KEY,Name char(20),salary number(10),location char(10));

Table level: [CONSTRAINT constraint_name] PRIMARY KEY (column_name1,column_na me2,..) // Multiple columns denotes composite primary key

CREATE TABLE employee( id number(5),Name char(20 ),salary number(10),location char(10),CONSTRAINT emp_id_pk PRIMARY KEY (id)); B]FOREIGN KEY or Referential Integrity

-Column level: [CONSTRAINT constraint_name] REFERENCES Referenced_Table_name(co lumn_name)

CREATE TABLE product

(product_id number(5) CONSTRAINT pd_id_pk PRIMAR Y KEY

product_name char(20), supplier_name char(20), unit_price number(10)); CREATE TABLE order_items

(order_id number(5) CONSTRAINT od_id_pk PRIMARY KEY,

product_id number(5) CONSTRAINT pd_id_fk REFEREN CES product(product_id),

product_name char(20), supplier_name char(20), unit_price number(10));

(4)

CREATE TABLE employee

( id number(5) PRIMARY KEY, name char(20),

dept char(10), age number(2),

mgr_id number(5) REFERENCES employee(id), salary number(10),

location char(10))

Table level: [CONSTRAINT constraint_name] FOREIGN KEY(column_name) REFERENCES referenced_table_name(column_name);

CREATE TABLE order_items (order_id number(5) , product_id number(5), product_name char(20), supplier_name char(20), unit_price number(10)

CONSTRAINT od_id_pk PRIMARY KEY(order_id),

CONSTRAINT pd_id_fk FOREIGN KEY(product_id) REFE RENCES product(product_id));

C]NOT NULL - [CONSTRAINT constraint name] NOT NULL CREATE TABLE employee

( id number(5),

name char(20) CONSTRAINT nm_nn NOT NULL, dept char(10),

age number(2), salary number(10), location char(10)); D]UNIQUE KEY

-Column level: [CONSTRAINT constraint_name] UNIQUE CREATE TABLE employee

( id number(5) PRIMARY KEY, name char(20),dept char(10), age number(2),

salary number(10),

location char(10) UNIQUE); CREATE TABLE employee

dept char(10), age number(2), salary number(10),

location char(10) CONSTRAINT loc_un UNIQUE); Table level: [CONSTRAINT constraint_name] UNIQUE(column_name)

dept char(10), age number(2), salary number(10), location char(10),

CONSTRAINT loc_un UNIQUE(location)); E]CHECK CONSTRAINT

(5)

-Column level: [CONSTRAINT constraint_name] CHECK (condition) CREATE TABLE employee

dept char(10), age number(2),

gender char(1) CHECK (gender in ('M','F')), salary number(10),

location char(10)); Table level:

( id number(5) PRIMARY KEY, name char(20), dept char(10), age number(2), gender char(1), salary number(10), location char(10),

CONSTRAINT gender_ck CHECK (gender in ('M','F')) );

---

---SQL

JOINS-A]SQL Equi Joins:

1)SQL Inner Join-NATURAL JOIN:

SELECT EmpID, EmpName, DeptID, LocationID FROM Emp

NATURAL JOIN Dept USING:

SELECT EmpID, EmpName, DeptID, LocationID FROM Emp

JOIN Dept USING (DeptID) ON:

SELECT EmpID, EmpName, DeptID, LocationID FROM Emp E

JOIN Dept D

ON E.DeptID = D.DeptID

-SELECT O.order_id, P.product_name, P.unit_price FROM pr oduct P, order_items O WHERE O.product_id = P.product_id;

2)SQL Outer

Join-Left Outer Join:

- SELECT e.LastName, d.DeptId, d.DeptName FROM Employee e LEFT OUTER JOIN Department d ON (e.DeptId = d.DeptId)

- SELECT O.order_id, P.product_name, P.unit_price FROM product P, order_items O WHERE O.product_id(+)= P.product_id;

- SELECT Persons.LastName, Persons.FirstName, Orde rs.OrderNo FROM Persons LEFT JOIN Orders ON Persons.P_Id=Orders.P_Id ORDER BY Pe rsons.LastName

Right Outer Join:

- SELECT e.LastName, d.DeptId, d.DeptName

FROM Employee e RIGHT OUTER JOIN Department d ON (e.DeptId = d.DeptId)

- SELECT O.order_id, P.product_name, P.unit_price FROM product P, order_items O WHERE O.product_id = P.product_id(+);

(6)

rs.OrderNo FROM Persons RIGHT JOIN Orders ON Persons.P_Id=Orders.P_Id ORDER BY P ersons.LastName

Full Outer Join:

- SELECT e.LastName, d.DeptId, d.DeptName FROM Employee e FULL OUTER JOIN Department d ON (e.DeptId = d.DeptId)

- SELECT Persons.LastName, Persons.FirstName, Orde rs.OrderNo FROM Persons FULL JOIN Orders ON Persons.P_Id=Orders.P_Id ORDER BY Pe rsons.LastName

3)SQL Self

Join-SELECT A.sales_person_id, A.name, A.manager_id, B.sales_ person_id, B.name FROM sales_person A, sales_person B WHERE A.manager_id = B.sal es_person_id;

B]SQL Non Equi Joins:

SELECT O.order_id, P.product_name, P.unit_price FROM product P, order_items O WHERE O.product_id != P.product_id;

C]Cross Joins: Returns Cartesian Product (m x n) from 2 tables

Select EmpNo, EmpName, Job, DeptNo,DeptName from Emp CROSS JOIN Dept;

---

---AUTO INCREMENT a Field:

CREATE SEQUENCE seq_person MINVALUE 1

START WITH 1 INCREMENT BY 1 CACHE 10

INSERT INTO Persons (P_Id,FirstName,LastName)VALUES (seq_person. nextval,'Lars','Monsen')

---

---CASE Expression:

SELECT LastName, Salary,

(CASE WHEN Salary<5000 THEN 'LOW'

WHEN Salary<10000 THEN 'Medium' WHEN Salary<15000 THEN 'High' ELSE 'EXcellent'

END) Qualified Salary,

CASE Job_ID When 'IT_PROG' THEN 1.10*Salary

When 'ST_CLERK' THEN 1.15*Salary When 'SA_REP' THEN 1.20*Salary ELSE Salary

END "Revised Salary" FROM Employee;

DECODE Expression:

SELECT LastName, Salary,

DECODE(Job_ID, 'IT_PROG', 1.10*Salary,

'ST_CLERK', 1.15*Salary, 'SA_REP', 1.20*Salary, Salary) "Revised Salary" FROM Employee;

---

---COUNT:

(7)

COUNT(expr)

COUNT(Distinct expr) GROUP Functions:

SELECT Column, Group_Function FROM Table

[Where Condition]

[GROUP BY Grou_By_Expression] [Having Group_Condition] [ORDER BY Column]

SELECT Dept_ID, Avg(Salary) FROM Employees

WHERE EmpName Not Like '%abc%' GROUP BY Dept_ID;

HAVING Avg(Salary) > 5000 ORDER BY Dept_ID;

SELECT Dept_ID, Job_ID, Sum(Salary) FROM Employees

GROUP BY Dept_ID, Job_ID

---

---UNION:

SELECT EmpId, JobId FROM Employees UNION

SELECT EmpId, JobId FROM JobHistory

SELECT EmpId, To_Number(NULL), HireDate FROM Employees UNION

SELECT EmpId, JobId, To_Date(NULL) FROM JobHistory SELECT EmpId, JobId, Salary FROM Employees

UNION

SELECT EmpId, JobId, 0 FROM JobHistory UNION ALL:

SELECT EmpId, JobId FROM Employees UNION ALL

SELECT EmpId, JobId FROM JobHistory INTERSECT:

SELECT EmpId, JobId FROM Employees INTERSECT

SELECT EmpId, JobId FROM JobHistory MINUS:

SELECT EmpId, JobId FROM Employees MINUS

SELECT EmpId, JobId FROM JobHistory

---

---VIEWS:

CREATE [OR REPLACE] [FORCE|NOFoRCE] VIEW viewname [(alias[,alias ]...)]

AS SubQuery

[WITH CHECK OPTION [CONSTRAINT constraint]] [WITH READ ONLY [CONSTRAINT constraint]] CREATE VIEW vwSalary

(8)

AS SELECT empID,empName,salary*12 AnnSal FROM Employees

WHERE deptNo = 10

CREATE OR REPLACE VIEW vwDept (Name, MinSal, MaxSal, AvgSal) AS select d.deptName, MIN(e.salary), MAX(e.salary), AVG(e.salary )

FROM Employees e JOIN Department d ON (e.deptNo = d.deptNo)

GROUP BY d.deptName

CREATE OR REPLACE VIEW vwemp AS SELECT * FROM employees WHERE deptNo = 10

WITH CHECK OPTION CONSTRAINT EMP_CK // It allows to update only records in WHERE condition

CREATE OR REPLACE VIEW vwemp AS SELECT * FROM employees WHERE deptNo = 10

WITH READ ONLY // Read only no updates allowed DESC vwSalary;

SELECT * FROM vwSalary; DROP VIEW vmemp;

---

---SEQUENCE:

CREATE SEQUENCE seqName

[INCREMENT BY n] [START WITH n] [{MAXVALUE n | NOMAXVALUE}] [{MINVALUE n | NOMINVALUE}] [{CYCLE | NOCYCLE}] [{CACHE n | NOCACHE}] CREATE SEQUENCE SeqEmp

INCREMENT BY 10 START WITH 100 MAXVALUE 1000

INSERT INTO emp (EmpID,Name,Location) VALUES (SeqEmp.NEXTVAL,'A' ,'Pune')

SELECT SeqEmp.CURRVAL FROM emp

SELECT SeqEmp.NEXTVAL FROM emp //Increments at each execution an d can be used in any other table

ALTER SEQUENCE SeqEmp INCREMENT BY 20

MAXVALUE 500 //START WITH can't be altered DROP SEQUENCE SeqEmp;

---

---INDEXES:

UNIQUE INDEX: Automatic created When defined PRIMARY KEY or UNIQUE key c onstraint.

(9)

CREATE INDEX index_name ON table_name (column_name1,column_name2...); DROP INDEX index_name;

---

---SYNONYMS:

CREATE [PUBLIC] SYNONYM synonym_name FOR object;

// Table, view, sequence, stored procedure, function, package, m aterialized view, Java class schema object, user-defined object, synonym

CREATE PUBLIC SYNONYM dept For alice.dept_sum_vu; DROP PUBLIC SYNONYM dept;

---

---DATA DICTIONARY VIEW:

SELECT * FROM DICTIONARY :

Shows all Table_Name and Comment.

Table_Name Includes ALL_xxxxx, USER_xxxxx,DBA_xxxx,V$_xxx. e.g USER_OBJECTS USER_TABLES USER_TAB_COLUMNS USER_CONSTRAINTS USER_CONS_COLUMNS USER_VIEWS USER_SEQUENCES USER_SYNONYMS

SELECT * FROM USER_OBJECTS - Shows Object_Name, Object_Type like Tables,Views,INDEX,SEQUENCE,synonym.

SELECT * FROM USER_TABLES - Shows user specific table list SELECT * FROM USER_TAB_COLUMNS - Shows user specific table list and its columns

SELECT * FROM USER_CONSTRAINTS - Shows user specific table list and its Constraints

SELECT * FROM USER_CONS_COLUMNS - Shows user specific table list and its Constraints with minimal details

SELECT * FROM USER_VIEWS - Shows user specific views SELECT * FROM USER_SEQUENCES - Shows user specific Sequences

SELECT * FROM USER_SYNONYMS - Shows user specific Synonyms (Private Synonyms only)

---

---ADD/VIEW COMMENTS TO TABLE OR COLUMN: TABLE:

COMMENT ON TABLE Employees

IS 'Employee Information' COLUMN:

COMMENT ON TABLE COLUMN Employees.EName IS 'Employee Name' VIEW COMMENTS BY:

SELECT * FROM ALL_TAB_COMMENTS SELECT * FROM USER_TAB_COMMENTS SELECT * FROM ALL_COL_COMMENTS SELECT * FROM USER_COL_COMMENTS

---