Docstoc

Oracle Presentation

Document Sample
Oracle Presentation Powered By Docstoc
					ORACLE SQL
            Overview
Personal DBMS Vs Client/Server DBMS
Oracle 8 Environment
SQL – syntax and examples
PL/SQL-introduction
                                      Server

                           Gets file requests from clients
Personal                        Sends files to client
DBMS                      Receives files back from clients




                                 NETWORK



            Client A                                           Client B
 Sends file requests to server                      Sends file requests to server
  Receives files from server                         Receives files from server
        Updates data                                       Updates data
  Sends files back to server                         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

                           Gets data requests from clients
 Client/server             Adds, Deletes and updates data
 DBMS                         Sends results to clients




                                NETWORK



                                                             Client B
             Client A                              Sends data requests to server
   Sends data requests to server                   Receives results from server
   Receives results from server                 Sends new data or changes to 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.   Data Definition Language (DDL) : Create, Alter,
     Drop, Rename, Truncate
2.   Data Manipulation Language (DML): Insert,
     Delete, Update
3.   Data Retrieval: Select
4.   Transaction Control: Commit, Rollback, Savepoint
5.   Data Control Language (DCL): Grant, Revoke
                          DEPARTMENT
                Dept     Dept            Location
                ID       Name
                10       Finance         Charlotte
                20       Infosys         New York
                30       Marketing       Woodbridge
                40       Accountant      California

     POSITION
                                          QUALIFICATION
Position   Position
ID         Description             Qualification      Qualification
1          President               ID                 Description
2          Manager                 1                  Doctorate
3          Programmer              2                  Masters
4          Accountant              3                  Bachelors
5          Salesman                4                  Associates
                              EMPLOYEE

Emp   Last      First     Position   Super   Hire       Salary   Comm   Dept   Qual
ID    Name      Name      ID         ID      Date                       ID     ID


111   Smith     John      1                  04/15/60   265000   3500   10     1


246   Houston   Larry     2          111     05/19/67   150000   1000   40     2


123   Roberts   Sandi     2          111     12/02/91   75000           10     2

433   McCall    Alex      3          543     05/10/97   66500           20     4


543   Dev       Dereck    2          111     03/15/95   80000    2000   20     1


200   Shaw      Jinku     5          135     01/03/00   24500    3000   30

222   Chen      Sunny     4          123     08/15/99   35000           10     3


135   Garner    Stanley   2          111     02/29/96   45000    5000   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        varchar2(10),
 fname        varchar2(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 deptID=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
      dept.deptid=emp.deptid and emp.empid='111';
Transaction Control:

COMMIT

ROLLBACK [ to {savepoint}]

SAVEPOINT {name}

commit;

savepoint point1;

rollback to point1;
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 emp.empid =
  empno;
  IF current_salary IS NULL THEN
       RAISE salary_missing;
  ELSE
       UPDATE emp SET salary = salary + increase WHERE emp.empid
  = empno;
  END IF;
EXCEPTION
  WHEN salary_missing THEN
       UPDATE emp SET salary=0 where emp.empid=empno;
END raise_salary;

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:43
posted:8/9/2010
language:English
pages:19
Description: Oracle presentation document