ORACLE SQL Overview Personal DBMS Vs ClientServer DBMS

ORACLE SQL

Overview Personal DBMS Vs Client/Server DBMS Oracle 8 Environment SQL – syntax and examples PL/SQL-introduction

Server Personal DBMS Gets file requests from clients Sends files to client Receives files back from clients NETWORK Client A Sends file requests to server Receives files from server Updates data Sends files back to server Client B Sends file requests to server Receives files from server Updates data Sends files back to server

Personal DBMS - Problems Demand on the client and the network Does not perform table locking automatically Not fault tolerant in the case of client failure Do not have file based transaction logging

Server Client/server DBMS Gets data requests from clients Adds, Deletes and updates data Sends results to clients NETWORK Client A Sends data requests to server Receives results from server Sends new data or changes to server Client B Sends data requests to server Receives results from server Sends new data or changes to server

Client/Server DBMS Minimal load on the client and the network Performs table locking automatically Fault tolerant in the case of client failure File based transaction logging

Oracle 8 Environment SQL * Plus PL/SQL Query Builder Developer Enterprise Manager Web application server

SQL*Plus commands Sqlplus username/password ALTER USER user-name IDENTIFIED BY newpassword START filename | @ filename CLEAR SCREEN HELP <command> SAVE filename[. ext] REPLACE|APPEND EXIT

SQL Both an ANSI and ISO standard Types of commands: 1. 2. 3. 4. 5. Data Definition Language (DDL) : Create, Alter, Drop, Rename, Truncate Data Manipulation Language (DML): Insert, Delete, Update Data Retrieval: Select Transaction Control: Commit, Rollback, Savepoint Data Control Language (DCL): Grant, Revoke

DEPARTMENT Dept ID Dept Name Location 10 Finance Charlotte 20 Infosys New York 30 Marketing Woodbridge 40 Accountant California POSITION Position ID Position Description 1 QUALIFICATION President Qualification ID Qualification Description 2 Manager 1 Doctorate 3 Programmer 2 Masters 4 Accountant 3 Bachelors 5 Salesman 4 Associates

EMPLOYEE Emp ID Last Name First Name Position ID 111 Smith John 1 246 Houston Larry 2 123 Roberts Sandi 433 Mc. Call 543 Super ID Hire Date Salary Comm Dept ID Qual ID 04/15/60 265000 3500 10 1 111 05/19/67 150000 1000 40 2 2 111 12/02/91 75000 10 2 Alex 3 543 05/10/97 66500 20 4 Dev Dereck 2 111 03/15/95 80000 20 1 200 Shaw Jinku 5 135 01/03/00 24500 30 222 Chen Sunny 4 123 08/15/99 35000 135 Garner Stanley 2 111 02/29/96 45000 10 3 30 5

Data Definition Language: CREATE TABLE {table} ( {column datatype [DEFAULT expr] [column_constraint]. . . | table_constraint} [, { column datatype [DEFAULT expr] [column_constraint]. . . ) ALTER TABLE {table} [ADD|MODIFY {column datatype [DEFAULT expr] [column_constraint]} [DROP drop_clause] DROP TABLE {table} [cascade constraints] DESC {table}

CREATE TABLE Emp ( empid Decimal(10) NOT NULL, positionid Number(2), supervisorid Number(3), deptid Number(2), qualid Number(1), lname varchar 2(10), fname varchar 2(10), salary Decimal(10, 2), hiredate Date, commission Decimal(4, 2), PRIMARY KEY (empid), FOREIGN KEY (positionid) REFERENCES Position(positionid), FOREIGN KEY (deptid) REFERENCES Dept(deptid), FOREIGN KEY (qualid) REFERENCES Qualification(qualid) ); ALTER TABLE EMP MODIFY Commission decimal(7, 2);

Data Manipulation Language: INSERT INTO {table | view} [ (column [, column]. . . ) ] VALUES (expr, expr. . . ) UPDATE {table | view } SET { (column [, column] = { expr | } [WHERE condition] DELETE [FROM] {table | view} [WHERE condition]

INSERT INTO Dept( deptid, deptname, location) VALUES(50, 'IT', 'Dallas'); INSERT INTO Emp(empid, lname, fname, positionid, supervisorid, hiredate, salary, deptid, qualid) VALUES(227, 'howser', 'Barbara', 4, 111, '25 -AUG-83', 45000, 10, 3); UPDATE dept SET deptname='Sales' WHERE dept. ID=50; DELETE FROM dept WHERE deptid='50';

Data Retrieval: SELECT [DISTINCT | ALL] {table|view} FROM {table | view} [WHERE condition ] [GROUP BY expr [, expr]] [ORDER BY {expr} [ASC | DESC]] select * from dept; select deptname from dept where deptid='10'; select lname, fname from emp order by lname desc; select max(salary) from emp group by positionid; select deptname from dept, emp where deptid=emp. deptid and empid='111';

Transaction Control: COMMIT ROLLBACK [ to {savepoint}] SAVEPOINT {name} commit; savepoint 1; rollback to point 1;

Data Control Language: GRANT [privileges] ON object TO user|public [WITH GRANT OPTION] REVOKE [privileges] ON object TO user|public [CASCADE CONSTRAINTS] grant select, update on emp to XYZ ; revoke update on emp to XYZ;

A PL/SQL Example: CREATE OR REPLACE PROCEDURE raise_salary (empno INTEGER, increase REAL) IS current_salary REAL; salary_missing EXCEPTION; BEGIN SELECT salary INTO current_salary FROM emp WHERE empid = empno; IF current_salary IS NULL THEN RAISE salary_missing; ELSE UPDATE emp SET salary = salary + increase WHERE empid = empno; END IF; EXCEPTION WHEN salary_missing THEN UPDATE emp SET salary=0 where empid=empno; END raise_salary;