DATABASE MANAGEMENT SYSTEM DBMS By Prof Vinay S

DATABASE MANAGEMENT SYSTEM [DBMS] By Prof. Vinay S. Nalawade

SQL & PL-SQL

SQL Introduction

SQL Characteristics • ANSI & ISO • Manipulation • Common English Syntax • Either upper or Lower cases • Declarative not Procedural • Powerful

SQL Advantages • High Speed • Portable • Well defined Standard • Supports Object Based Programming • Used with all DBMS vendors • No coding required

SQL Advantages • Used for RDBMS • Easy to learn & understand • Complete Language for a database • Dynamic • Programming , Interactive Language • Client Server Language

SQL Advantages • Multiple data views • Used in Internet

SQL Data types • CHAR • VARCHAR • BOOLEAN • SMALLINT • INTEGER or INT • DECIMAL

SQL Data types • NUMERIC • FLOAT • DATE • TIME

SQL Literals • NUMERIC • CHARACTERS • STRINGS • DATE • TIME

SQL • DDL – CREATE, ALTER, DROP, RENAME • DML – SELECT, INSERT, UPDATE, DELETE • DCL – GRANT, REVOKE • TCL BEGIN, COMMIT, ROLLBACK

SQL OPERATORS • Arithmetic • Comparison • Logical • Bitwise • Compound

SQL • Creating Table • Modifying Table • Deleting Table

SQL VIEWS • Creating Views • Updating Views • Dropping Views

SQL INDEX • Single Column Index • Composite Index • Unique Index • Implicit Index

SELECT Overall Form SELECT <ATTRIBUTE AND FUNCTION LIST> FROM <TABLE LIST> [WHERE <CONDITION>] [GROUP BY <GROUPING ATTRIBUTE(S)>] [HAVING <GROUP CONDITION>] [ORDER BY <ATTRIBUTE LIST>]; Constructs within box braces are optional.

DISTINCT Use of DISTINCT, ALL RENAME Use of AS

SET OPERATIONS UNION , INTERSECTION , MINUS

String functions Use of %, _ With LIKE

AGGREGATE FUNCTIONS AVG, MIN, MAX, SUM, COUNT ORDER BY, GROUP BY, HAVING

PREDICATES • Comparison Predicates • BETWEEN Predicate • IS NULL Predicate

JOINS • Inner Join( Equi Join ) • Outer Join - Left Outer Join - Right Outer Join - Full Outer Join • Self Join – Tuple Variables

NESTED SUBQUERIES

Queries Employee(emp_no, name, skill, pay_rate) Position(posting_no, skill) Duty-allocation(posting_no, emp_no, day, shift)

Employee emp_no Position name skill pay_rate posting_no Duty-allocation posting_no emp_no day shift Q- Get complete details from duty allocation. - SELECT * FROM DUTY_ALLOCATION skill

Employee emp_no Position name skill pay_rate posting_no skill Duty-allocation posting_no emp_no day shift Q- Get duty allocation details for Emp-no 123461 for the month of April 1986 - SELECT POSTING_NO, DAY, SHIFT FROM DUTY_ALLOCATION WHERE EMP_NO=123461 AND DAY >=19860401 AND DAY <= 19860430

Employee emp_no Position name skill pay_rate posting_no skill Duty-allocation posting_no emp_no day shift Q- Find the shift details for employee ‘XYZ’ - SELECT POSTING_NO, DAY, SHIFT FROM DUTY_ALLOCATION, EMPLOYEE WHERE DUTY_ALLOCATION. EMP_NO=EMPLOYEE. EMP_NO AND NAME=‘XYZ’

Employee emp_no Position name skill pay_rate posting_no skill Duty-allocation posting_no emp_no day shift Q- Get employees whose rate of pay is more than or equal to the rate of pay of employee ‘XYZ’ - SELECT S. NAME, S. PAY_RATE FROM EMPLOYEE AS S, EMPLOYEE AS T WHERE S. PAY_RATE > =T. PAY_RATE AND T. NAME=‘XYZ’

Employee emp_no Position name skill pay_rate posting_no skill Duty-allocation posting_no emp_no day shift Q- Compare all pairs of posting_nos requiring the same skill. - SELECT S. POSTING_NO, T. POSTING_NO FROM POSITION AS S, POSITION AS T WHERE S. SKILL = T. SKILL AND S. POSTING_NO < T. POSTING_NO

Employee emp_no Position name skill pay_rate posting_no skill Duty-allocation posting_no emp_no day shift Q- Find employee to fill a position. - SELECT EMP_NO, POSTING_NO, SKILL FROM EMPLOYEE, POSITION WHERE EMPLOYEE. SKILL = POSITION. SKILL

Employee emp_no Position name skill pay_rate posting_no skill Duty-allocation posting_no emp_no day shift Q- Get the names & pay_rates of employees with emp_no less than 123460 whose rate of pay is more than rate of pay of employee with emp_no greater than or equal to 123460. - SELECT NAME, PAY_RATE FROM EMPLOYEE WHERE EMP_NO < 123460 AND PAY_RATE > (SELECT PAY_RATE FROM EMPLOYEE WHERE EMP_NO >= 123460

Employee emp_no Position name skill pay_rate posting_no skill Duty-allocation posting_no emp_no day shift Q- Get employees who are working either on the date 19860419 or 19860420 - SELECT EMP_NO FROM DUTY_ALLOCATION WHERE DAY = 19860419 OR DAY = 19860420.

Employee emp_no Position name skill pay_rate posting_no Duty-allocation posting_no emp_no day Q- find the employees with the lowest pay rate - SELECT EMP_NO , NAME, PAY_RATE FROM EMPLOYEE WHERE PAY_RATE<=ALL (SELECT PAY_RATE FROM EMPLOYEE) shift skill

Employee emp_no Position name skill pay_rate posting_no Duty-allocation posting_no emp_no day shift Q- Get the name of chef paid at the minimum pay_rate - SELECT NAME FROM EMPLOYEE WHERE SKILL = ‘CHEF’ AND PAY_RATE<=ALL (SELECT PAY_RATE FROM EMPLOYEE WHERE SKILL = ‘CHEF’) skill

Employee emp_no Position name skill pay_rate posting_no skill Duty-allocation posting_no emp_no day shift Q- Find the names and the rate of pay of all employes who are allocated a duty - SELECT NAME , PAY_RATE FROM EMPLOYEE WHERE EXISTS (SELECT * FROM DUTY_ALLOCATION WHERE EMPLOYEE. EMP_NO=DUTYALLOCATION. EMP_NO)

Employee emp_no Position name skill pay_rate posting_no skill Duty-allocation posting_no emp_no day shift Q- Find the names and the rate of pay of all employees who are not allocated duty - SELECT NAME , PAY_RATE FROM EMPLOYEE WHERE NOT EXISTS (SELECT * FROM DUTY_ALLOCATION WHERE EMPLOYEE. EMP_NO=DUTYALLOCATION. EMP_NO)

Employee emp_no Position name skill pay_rate posting_no skill Duty-allocation posting_no emp_no day shift Q- Get employees who are waiters or work at posting-no 321 - (SELECT EMP_NO FROM EMPLOYEE WHERE SKILL=‘WAITER’) UNION (SELECT EMP_NO FROM DUTY ALLOCATION WHERE POSTING_NO=321)

Employee emp_no Position name skill pay_rate posting_no skill Duty-allocation posting_no emp_no day shift Q- Get employees numbers of persons who work at posting_no 321 but do not have the skill of waiter - (SELECT EMP_NO FROM DUTY _ALLOCATION WHERE POSTING_NO=321) MINUS (SELECT EMP_NO FROM EMPLOYEE WHERE SKILL =‘WAITER’)

Employee emp_no Position name skill pay_rate posting_no Duty-allocation posting_no emp_no day shift Q- Get list of employees not assigned to duty - (SELECT EMP_NO FROM EMPLOYEE) MINUS (SELECT EMP_NO FROM DUTY_ALLOCATION) skill

Employee emp_no Position name skill pay_rate posting_no skill Duty-allocation posting_no emp_no day shift Q- Get list of names of employees with the skill of chef who are assigned a duty - SELECT NAME FROM EMPLOYEE WHERE EMP_NO IN ((SELECT EMP_NO FROM EMPLOYEE WHERE SKILL = ‘CHEF’) INTERSECT (SELECT EMP_NO FROM DUTY_ALLOCATION)

Employee emp_no Position name skill pay_rate posting_no Duty-allocation posting_no emp_no day shift Q- Get a count of different employees on each shift - SELECT SHIFT, COUNT (DISTINCT EMP_NO) FROM DUTY_ALLOCATION GROUP BY SHIFT; skill

Queries Employee(emp_name, street, city) Works(emp_name, company_name, salary) Company(company_name, city) Manages(emp_name, manager_name)

Employee emp_name Street Company city Company_name Works emp_name city Manages company_name salary employee_name Manager emp_name Manager_name Q- Find the names of all employees who work for FBC - SELECT EMPLOYEE_NAME FROM WORKS WHERE COMPANY_NAME=‘FBC’

Employee emp_name Street Company city company_name Works emp_name city Manages company_name salary emp_name Manager_name Q- Find the names & cities of all employees who work for FBC - SELECT EMPLOYEE_NAME , CITY FROM WORKS, EMPLOYEE WHERE EMPLOYEE_NAME=WORKS. EMPLOYEE_NAME AND COMPANY_NAME = ‘FBC’

Employee emp_name Street Company city company_name Works emp_name city Manages company_name salary emp_name Manager_name Q- Find the names, street address & cities of residence of all employees who work for FBC & earn more than $ 10, 000 - SELECT EMPLOYEE_NAME , STREET, CITY FROM WORKS, EMPLOYEE WHERE EMPLOYEE_NAME=WORKS. EMPLOYEE_NAME AND COMPANY_NAME = ‘FBC’ AND SALARY > 10000

Employee emp_name Street Company city Company_name Works emp_name city Manages company_name salary employee_name Manager emp_name Manager_name Q- Find all employees in the database who live in the same cities as the companies for which they work. - SELECT W. EMPLOYEE_NAME FROM WORKS AS W, EMPLOYEE AS E, COMPANY AS C WHERE E. EMPLOYEE_NAME=W. EMPLOYEE_NAME AND C. COMPANY_NAME=W. COMPANY_NAME AND

Employee emp_name Street Company city Company_name Works emp_name city Manages company_name salary employee_name Manager emp_name Manager_name Q- Find all employees in the database who live in the same cities & on the same street as do their managers. - SELECT E. EMPLOYEE_NAME FROM EMPLOYEE AS E, EMPLOYEE AS T, MANAGES WHERE E. EMP_NAME=MANAGES. EMP_NAME AND E. STREET=T. STREET AND E. CITY=T. CITY AND T. EMP_NAME=MANAGES. MANAGER_NAME;

Employee emp_name Street Company city Company_name Works emp_name city Manages company_name salary employee_name Manager emp_name Manager_name Q- Fin all employees in the database who do not work for FBC - (SELECT EMPLOYEE_NAME FROM EMPLOYEE) MINUS (SELECT EMPLOYEE_NAME FROM WORKS WHERE COMPANY_NAME = ‘FBC’)

Employee emp_name Street Company city Company_name Works emp_name city Manages company_name salary employee_name Manager emp_name Manager_name Q- Find all employees in the database who earn more than every employee of ‘FBC’ - SELECT EMPLOYEE_NAME FROM WORKS WHERE SALARY > SELECT MAX(SALARY) FROM WORKS WHERE COMPANY_NAME = ‘FBC’)

Employee emp_name Street Company city Company_name Works emp_name city Manages company_name salary employee_name Manager emp_name Manager_name Q- Find all employees in the database who earn more than average salary of all employees of their company. - SELECT T. EMPLOYEE_NAME FROM WORKS T WHERE SALARY > (SELECT AVG(S. SALARY) FROM WORKS S WHERE T. COMPANY_NAME=S. COMPANY_NAME)

PL/ SQL • Product of Oracle corporation. • Procedural Language SQL. • Programming Language from Oracle. • Used for data processing data at Server side. • Can be called in SQL *Plus, Forms 6 i, Reports 6 i, Menus 6 i, Graphics 6 i etcs. • Can be called through any front end

PL/ SQL • 4 GL(Supports OOPS). • Syntax: Declaration BEGIN Block of stmts; END; / • /-is compulsory to end the block which is called as terminator. • Block Level Language.

PL/ SQL • Screen i/p & screen o/p not allowed. • SQL commands which allowed in PL/SQL are insert, update, delete, rollback, commit, savepoint, select. • DDL commands not allowed within PL/SQL block. • SQL *Plus commands not allowed within PL/SQL block.

PL/ SQL • Demos: • CREATE TABLE STUDENT( ID NUMBER(4), NAME VARCHAR 2(40)); • Table created on which we can create following PL/SQL block.

PL/ SQL • Demo: Simple PL/SQL block SQL> BEGIN INSERT INTO STUDENT VALUES(6, 'Rahul'); COMMIT; END; /

PL/ SQL • Demo: Simple PL/SQL block SQL> DECLARE X NUMBER(4); BEGIN X: =56; INSERT INTO STUDENT VALUES(X, 'AMEYA'); END; /

PL/ SQL • Demo: Simple PL/SQL block SQL> DECLARE X NUMBER(4); BEGIN SELECT ID INTO X FROM STUDENT WHERE NAME = ‘Rahul'; INSERT INTO STUDENT VALUES(X, 'Om'); END; /

PL/ SQL • Demo: Simple PL/SQL block SQL> DECLARE X STUDENT % ROWTYPE; BEGIN SELECT * INTO X FROM STUDENT WHERE ID=56; INSERT INTO STUDENT VALUES(X. ID, X. NAME); END; /

PL/ SQL • Demo: Loops in PL/SQL block SQL> DECLARE X NUMBER(4): =2; BEGIN WHILE X<6 LOOP INSERT INTO STUDENT VALUES(X, 'GUEST'); X: =X+1; END LOOP; END; /

PL/ SQL • Demo: Loops in PL/SQL block SQL> DECLARE X NUMBER(4): =2; BEGIN INSERT INTO STUDENT VALUES(X, 'A'); GOTO ABC; <<PQR>> INSERT INTO STUDENT VALUES(X, 'C'); X: =X+1; <<ABC>> INSERT INTO STUDENT VALUES(X, 'B'); IF X < 4 THEN GOTO PQR; END IF; END;

PL/ SQL • Demo: PL/SQL Sub - block SQL> DECLARE Z NUMBER(4): =2; BEGIN INSERT INTO STUDENT VALUES(Z, 'BEFORE SUB'); DECLARE A NUMBER(4): =3; BEGIN INSERT INTO STUDENT VALUES(A, 'IN SUB'); END; INSERT INTO STUDENT VALUES(Z, 'AFTER SUB'); END; /

PL/ SQL CURSORS • Cursor is a variable similar to 2 D array. • Cursor is based on SELECT statement. • It is used for storing multiple rows. • It is used for processing , handling multiple rows. • It is used for storing data temporarily.

PL/ SQL CURSORS • Creating Table for Cursor operations : CREATE TABLE EMP 2( EMPNO NUMBER(2), ENAME VARCHAR 2(40), SAL NUMBER(4), DEPTNO NUMBER(2));

PL/ SQL CURSORS • Creating Table for Cursor operations : CREATE TABLE TEMP( EMPNO NUMBER(2), ENAME VARCHAR 2(40), SAL NUMBER(4), DEPTNO NUMBER(2));

PL/ SQL CURSORS • Cursor Demo : DECLARE CURSOR C 1 IS SELECT * FROM EMP 2; X EMP 2 % ROWTYPE; BEGIN OPEN C 1; FOR Y IN 1. . 5 LOOP FETCH C 1 INTO X; INSERT INTO TEMP VALUES(X. EMPNO, X. ENAME, X. SAL, X. DEPTNO); END LOOP; CLOSE C 1; END; /

PL/ SQL CURSORS • Cursor is read only variable. • You will have to fetch one row at a time into some intermediate ‘x’ variable & do the process with ‘x’ variable. • For Y in 1. . 3 called as ranking Report. • You can only fetch sequentially. • Closing cursor isn’t compulsory. If we want to open it again, it have to be closed first & then open it again.

PL/ SQL CURSORS • Demo: Cursors with attributes DECLARE CURSOR C 1 IS SELECT * FROM EMP 2; X EMP 2 % ROWTYPE; BEGIN OPEN C 1; LOOP FETCH C 1 INTO X; IF C 1%NOTFOUND THEN EXIT; END IF; INSERT INTO TEMP VALUES(X. EMPNO, X. ENAME, X. SAL, X. DEPTNO); END LOOP; CLOSE C 1; END;

PL/ SQL CURSORS • Demo: Multiple cursors DECLARE CURSOR C 1 IS SELECT * FROM EMP 2; CURSOR C 2 IS SELECT * FROM TEMP; BEGIN FOR X IN C 1 LOOP FOR Y IN C 2 LOOP IF X. EMPNO=Y. EMPNO THEN INSERT INTO EMP 3 VALUES(X. EMPNO, Y. ENAME, X. SAL, Y. DEPTNO); END IF; END LOOP; END; /

PL/ SQL CURSORS • Cursor Attributes: - %found, %notfound - %rowcount - %open eg. IF C 1%OPEN THEN CLOSE C 1; END IF; • Compulsory things for Cursor operations: 1)Declaration of C 1 & variable X 2)Open c 1 3)Fetch C 1 into X 4)Exit when C 1 not found 5)Close C 1

PL/ SQL CURSORS • Types of Cursors 1)Explicit Cursors - Programmer created. - Have to be declared explicitly. - Used for storing, handling, processing multiple rows. - Used for storing data temporarily. - Used for rows locking manually.

PL/ SQL CURSORS 2)Implicit Cursors -Oracle created (system cursor). - Already available with system. - Used for checking status for last DMLstatement. (weather successful or not) - Can even get a count of how many rows affected by last DML statement. - Used to maintain logs(audit rails) of DML operations.

DATABASE TRIGGERS • Routine(set of commands) that get executed automatically whenever some event takes place. • Triggers written on tables. • Events are BEFORE INSERT , AFTER INSERT BEFORE DELETE , AFTER DELETE BEFORE UPDATE , AFTER UPDATE

DATABASE TRIGGERS • Trigger Demo: CREATE OR REPLACE TRIGGER TG BEFORE INSERT ON EMP 2 BEGIN INSERT INTO TEMP VALUES(1, 'INSERTING IN EMP 2', 1, 1); END; /

DATABASE TRIGGERS • Triggers are stored in database in compiled format. • Uses: - Used to maintain Logs(audit trails). - All triggers are at Server level. - You can perform DML operations using any front end , trigger always execute. - Whether you issue rollback/commit the data is always consistent.

DATABASE TRIGGERS - If your trigger fails, DML operation on table is automatically rolled back. - If your DML operation on table is failing, then trigger changes are automatically rolled back. - ROLLBACK & COMMIT not allowed in Trigger. - Used to maintain summary logs/detailed logs(audit trails) of insertions.

DATABASE TRIGGERS • Types of Triggers 1)Statement Level Trigger 2)Row Level Trigger(For each row) • Maximum 12 triggers are possible per table.

DATABASE TRIGGERS • Trigger Demo: CREATE OR REPLACE TRIGGER TG BEFORE INSERT ON EMP 2 BEGIN INSERT INTO TEMP VALUES(1, 'INSERTING IN EMP 2', 1, 1); END; /

STORED PROCEDURES (USE COMMAND ON MYSQL : DELIMITER // ) • Procedure Demo: CREATE PROCEDURE DISPLAY(IN R INTEGER(3)) BEGIN SELECT * FROM STUD WHERE R=ROLL; END // CALL DISPLAY(4);

STORED PROCEDURES • Procedure Demo: CREATE PROCEDURE DISPLAY 1(IN R INT, OUT NM VARCHAR(40)) BEGIN SELECT NAME INTO NM FROM STUD WHERE R=ROLL; END // CALL DISPLAY 1(4, @N)// SELECT @N//

STORED FUNCTIONS • Function Demo: CREATE FUNCTION ANNUALSAL(SAL INT) RETURNS INT(7) DETERMINISTIC BEGIN DECLARE ASAL INT(7); SET ASAL=SAL*12; RETURN ASAL; END; SELECT ANNUALSAL(2340) FROM DUAL;

ROLES & PRIVILEGES • SYSTEM PRIVILEGES • OBJECT PRIVILEGES

EMBEDDED SQL • Need • Host variables • Advantages • Working • Syntax • Open , Fetch, Close Statements

DYNAMIC SQL • Need • Advantages • Working • Syntax