Oracle sql
This presentation is the property of its rightful owner.
Sponsored Links
1 / 19

ORACLE SQL PowerPoint PPT Presentation


  • 106 Views
  • Uploaded on
  • Presentation posted in: General

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 Sends files to client Receives files back from clients. Personal DBMS. NETWORK. Client B Sends file requests to server

Download Presentation

ORACLE SQL

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Oracle sql

ORACLE SQL


Overview

Overview

  • Personal DBMS Vs Client/Server DBMS

  • Oracle 8 Environment

  • SQL – syntax and examples

  • PL/SQL-introduction


Oracle sql

Server

Gets file requests from clients

Sends files to client

Receives files back from clients

Personal DBMS

NETWORK

Client B

Sends file requests to server

Receives files from server

Updates data

Sends files back to server

Client A

Sends file requests to server

Receives files from server

Updates data

Sends files back to server


Personal dbms problems

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


Oracle sql

Server

Gets data requests from clients

Adds, Deletes and updates data

Sends results to clients

Client/server DBMS

NETWORK

Client B

Sends data requests to server

Receives results from server

Sends new data or changes to server

Client A

Sends data requests to server

Receives results from server

Sends new data or changes to server


Client server dbms

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

Oracle 8 Environment

  • SQL * Plus

  • PL/SQL

  • Query Builder

  • Developer

  • Enterprise Manager

  • Web application server


Sql plus commands

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


Oracle sql

SQL

  • Both an ANSI and ISO standard

  • Types of commands:

    • 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


Oracle sql

DEPARTMENT

POSITION

QUALIFICATION


Oracle sql

EMPLOYEE


Oracle sql

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}


Oracle sql

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);


Oracle sql

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]


Oracle sql

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';


Oracle sql

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';


Oracle sql

Transaction Control:

COMMIT

ROLLBACK [ to {savepoint}]

SAVEPOINT {name}

commit;

savepoint point1;

rollback to point1;


Oracle sql

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;


Oracle sql

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;


  • Login