Limbajul SQL

Limbajul SQL

SQL a fost propus de catre ANSI, in octombrie 1986, ca limbaj standard pentru manipularea bazelor de date relationale. Începand cu 1987 nici un SGBD relational nu poate fi comercializat fara sa propuna o interfata SQL.

În general, exista patru tipuri de utilizatori a unei baze de date SQL:

Utilizatorul final, care vede datele sub forma de tabele, care nu trebuie sa cunoasca modelul relational al lui Codd, care poate pune intrebari ad-hoc asupra bazei de date;

Administratorul aplicatiilor, care defineste tabelele si care trebuie sa cunoasca modelul relational al lui Codd;

Administratorul sistemului, care este responsabil cu optimizarea stocarii tabelelor si cu optimizarea accesului;

Programatorul de aplicatii, care trebuie sa exploateze concurenta si facilitatile controlului concurentei pentru a evita conflictele intre utlizatori si a asigura astfel integritatea si securitatea datelor.

Pentru utilizatorul final, un tabel este o relatie, iar pentru ultimii trei utlizatorii un tabel este o reprezentare a unei relatii la un moment dat.

Standardul SQL include un limbaj de definire a datelor (a schemei unei baze de date relaţ 737c28h ;ionale) (LDD), un limbaj de manipulare a datelor (LMD), un limbaj de interogare a bazei de date (LID) si un limbaj de control al bazei de date (LCD).

Administratorul foloseste LDD pentru a crea tabele, utilizatorul final foloseste LMD si LID pentru a manipula baza de date, iar programatorul de aplicatii foloseste LCD pentru a controla baza de date.

Limbajul SQL permite definirea si modificarea schemei unei baze date relationale, interogarea si modificarea unei baze de date relaţ 737c28h ;ionale, controlul securitatii si integritatii bazei.

Exemplele din acest capitol se refera la un sistem formal conceput, care tine evidenta angajatiolor unei firme. Diagrama conceptuala pentru modelul relational este prezentata in Fig. 5.2-1.

Fig. 0‑ Diagrama conceptuala

Schemele relationale corespunzatoare diagramei conceptuale sunt:

EMP(empno, ename, job, mgr, hiredate, sal, comm, deptno),

DEPT(deptno, dname, loc),

SALGRADE(grade, losal, hisal).

Descrierea tabelelor corespunzatoare acestor scheme relationale:

EMP

empno : codul angajatului, care trebuie sa fie unic (cheie primara).

ename : numele si prenumele angajatului.

job : profesia angajatului.

mgr : codul de identificare a sefului unui angajat.

hiredate : data de angajare.

sal : salarul lunar.

comm : comisionul unui angajat care poate sa fie si null.

deptno: codul departamentului la care lucreaza angajatului (este cheie externa, refera cheia primara deptno din tabela DEPT).

DEPT

deptno : codul departamentului, care trebuie sa fie unic, (cheie primara).

dname : denumirea departamentrului

loc : locul unde se afla departamentul

SALGRADE

grade : un cod care indica gradul de salarizare, este cheie primara

losal : limita inferioara

hisal : limita superioara

În continuare toate exemplele din acest capitol vor lua in considerare aceste tabele. Vom prezenta limbajul SQL din punct de vedere teoretic si vom ilustra posibilitatile sale practice, cu exemple concrete, utilizand sintaxa SQL*PLUS a sistemului ORACLE.

Definirea schemei unei baze de date relaţ 737c28h ;ionale

Limbajul de definire a datelor include instructiuni ce permit crearea schemei bazei de date, adaugarea relatiilor la schema bazei, stergerea unor relatii existente, adaugarea de noi atribute relatiilor existente, optimizarea bazei (index, grup, declansator), definirea structurii fizice si logice a unei baze de date, restrictii cu privire la atributele unei relatii.

Specificarea bazelor de date nu a fost descrisa de catre standardul ANSI si de aceea conceptul de baza de date este implementat diferit in diversele dialecte SQL. Majoritatea implementarilor SQL contin instructiuni pentru crearea unei baze de date, fie sub forma unor comenzi ale versiuni respective, fie sub forma unor programe utilitare.

O schema de baze de date descrie relatiile, atributele relatiilor, domeniul in care aceste atribute iau valori, restrictii de integritate, drepturi de utilizare a relatiilor si vizualizarilor, detalii cu privire la implementarea fizica a relatiilor.

Definirea unei scheme cuprinde clauza CREATE SCHEMA, o clauza AUTHORIZATION ce permite identificarea creatorului schemei, atatea clauze CREATE TABLE cate tabele sunt continute in schema, clauze CREATE VIEW corespunzatoare vizualizarilor continute in schema si operatii de acordare a privilegiilor (GRANT). Un utilizator poate avea o singura schema.

Obiectele ce pot fi manipulate intr-o schema depind de dialectul SQL. Tabelele, vizualizarile, indecsii, grupurile, sinonimele, procedurile, functiile si declansatorii reprezinta cateva dintre obiectele importante manipulate de SQL*PLUS.

Procesul de definire a unui obiect presupune crearea, modificarea si suprimarea acestuia.

Cand introducem o comanda SQL ea este stocata intr-o parte din memorie, numita buffer si ramane acolo pana cand introducem o noua comanda SQL. Cuvintele cheie din SQL nu pot fi prescurtate.

SQL este un limbaj pentru comunicarea cu serverul Oracle la accesul datelor. În SQL comenzile se termina cu ;

În SQL*PLUS comenzile pot fi prescurtate, minimul 4 caractere. Nu este folosit nici un caracter terminator pentru comenzile SQL*PLUS. Este un mediu care corespunde limbajului ORACLE

Intram in SQL*PLUS astfel:

Ø     Din Windows :

1. Start -> Programs -> Oracle for Windows NT -> SQL*Plus8.0 .

2. Nume Utilizator, parola si baza de date.

Ø     Din linia de comanda :

Sqlplus [username [/password[@database]]]

unde

username - numele utilizatorului pentru baza de date.

Password - parola utilizatorului pentru baza de date.

@database - baza de date la care se doreste conectarea.

Vom exemplifica modul de definire a catorva obiecte ce pot apartine unei scheme de baza de date.

Definirea unui tabel

Exista doua tipuri de tabele : tabele de baza, care sunt memorate fizic in memorie si tabele view (vizualizari), care sunt virtuale si pentru care doar definitia este memorata.

Tipurile de date cele mai frecvent intalnite in SQL*PLUS sunt:

CHAR pentru sir de lungime fixa, daca nu se specifica implicit lungimea minima este 1 iar lungimea maxima este 2000);

VARCHAR2 pentru sir de caractere de lungime variabila (O lungime maxima poate fi specificata, implicit lungimea minima este 1 iar lungimea maxima este 4000);

NUMBER pentru numere in virgula fixa si mobila;

LONG pentru text de lungime variabila pana la 2 GB (gigabytes);

LONG RAW pentru date binare , nu apre in PL SQL;

DATE pentru date calendaristice, intervalul pentru date este intre 4712 BC si 9999 AD.

BOOLEAN poate avea una din cele trei valori: TRUE, FALSE, NULL;

PLS_INTEGER pentru intregi cu semn cuprinsi intre 147483647 si

BLOB date binare, de lungime maxima 4GB;

BFILE date binare depozitate intr-un fisier extern, pina la 4 GB;

Crearea structurii unui tabel de baza se face prin comanda CREATE TABLE, care are urmatoarea sintaxa:

CREATE TABLE [schema.]table

(column datatype [DEFAULT expr],…);

unde:

schema : este numele utilizatorului care a creat tabela, este optional;

table : numele tabelei;

column : numele unei coloane;

datatype : tipul coloanei si lungimea coloanei (este un tip predefinit existent);

DEFAULT expr : este o optiune prin care se specifica o valoare implicita pentru o anumita coloana, in cazul in care nu se specifica nici o valoare pentru coloana respectiva, (previne introducerea unei valori NULL).

Exista cateva reguli la definirea tabelului cum ar fi:

Numele tabelei si numele coloanelor trebuie sa inceapa cu litera si poate avea lungimea maxima 30;

Numele poate contine numai A-Z, a-z, 0-9, _(underscore), $ si #;

Numele tabelului trebuie sa fie unic in cadrul bazei de date in care il creati;

Numele nu poate fi unul din cuvintele rezervate ale sistemului Oracle sau ale limbajului SQL;

Nu se face deosebirea intre litere mici si litere mari, (deci EMP este aceelasi nume cu emp).

Crearea unui tabel cu denumirea dept se va realiza astfel:

CREATE TABLE dept

(deptno NUMBER (2),

dname VARCHAR2(14),

loc VARCHAR2(13));

Putem crea o tabela copiind informatii dintr-o alta tabela, printr- o interogarea a tabelei respective, adica printr-un subquery, folosind urmatoarea sintaxa:

CREATE TABLE table

[(column (, column …)]

AS subquery;

unde:

table : numele tabelei;

column : numele unei coloane, se pot specifica valorile implicite si constrangerile de integritate;

subquery : este o instructiune SELECT care defineste setul de randuri ce pot fi inserate in noua tabela;

În cazul in care nu se specifica lista de coloane numele coloanelor din tabela vor fi acelasi cu numele coloanelor din subquery. De asemenea, se pot redenumi campurile din noul tabel. Daca se specifica lista de coloane, atunci numarul de coloane din tabela trebuie sa fie egal cu numarul de coloane din subquery.

Un exemplu, in care se va crea o tabela utilizand un subquery, se va scrie astfel:

CREATE TABLE dept30

AS

SELECT empno, ename, sal*12 ANNSAL, hiredate

FROM emp

WHERE deptno = 30;

În exemplu de mai sus s-a creat o tabela care contine detaliile tuturor angajatilor din departamentul 30. Putem verifica existenta tabelei de date noi create si definitia coloanelor tabelei utilizand comanda DESCRIBLE din SQL*PLUS.

Aceasta comanda poate fi folositoare cand trebuie sa creem un tabel similar cu unul existent, care contine date asemanatoare.

La crearea unei tabele pot fi specificate anumite constrangeri declarative prin care se exprima o conditie ce trebuie respectata de toate tuplurile unei relatii. Constrangerilor le pot fi asociate nume unice. Constrangerile declarative pot fi:

Constrangeri de domeniu, care definesc valorile luate de un atribut (NOT NULL, UNIQUE, CHECK);

Constrangeri de integritate a entitatii, care precizeaza cheia primara (PRIMARY KEY);

Constrangeri de integritate referentiala, care asigura coerenta intre cheile primare si cheile externe corespunzatoare (FOREIGN KEY)

Asertiuni, care specifica anumite constrangeri generale referitoare la atribute (ASSERTION).

În Oracle sunt valide urmatoarele constrangeri:

NOT NULL, specifica faptul ca, coloana respectiva nu poate contine valori NULL;

UNIQUE Key, valoarea coloanei sau valorile combinatiilor de coloane trebuie sa fie unica unice pentru toate tuplurile din tabela.

PRIMARY KEY, defineste cheia primara, care identifica in mod unic fiecare tuplu (rand) din tabela;

FOREIGN KEY, defineste cheia externa, si stabileste o relatie intre coloana respectiva si o coloana a tabelei referite;

CHECK, specifica o conditie care trebuie sa fie indeplinita.

Toate constrangerile sunt stocate in dictionarul de date. Putem afisa constrangerile definite pentru o tabela specificata vizualizand tabela USER_CONSTRAINTS a dictionarului de date.

Daca nu este specificat un nume pentru o constrangere Oracle generteaza un nume unic cu formatul SYS_Cn, unde n este un intreg.

Definirea unei constangeri la crearea unei tabele se realizeaza astfel:

CREATE TABLE [schema.]table

(column datatype [DEFAULT expr],

[column_constraint],

……

[table_constraint]);

unde:

schema : este numele utilizatorului care a creat tabela, este optional in cazul in care utlizatorul este si detinatorul tabelei ;

table : numele tabelei;

DEFAULT expr : specifica o valoare implicita, daca valoarea a fost omisa in instructiunea INSERT;

column : numele coloanei;

datatype : tipul coloanei si lungimea coloanei;

column_constraint : este o constrangere de integritate definita la nivel de coloana;

table_constraint : este o constrangere de integritate definita la nivel de tabela;

Definirea unei costrangeri la nivel de coloana se realizeaza folosind urmatoatrea sintaxa:

column [CONSTRAINT constraint_name] constaint_type

Definirea unei costrangeri la nivel de tabela se realizeaza astfel:

column,…

[CONSTRAINT constraint_name] constaint_type

(column, …),

unde:

constraint_name : este numele constrangeri;

constraint_type : este tipul constrangeri;

O constrangere la nivel de coloana se refera la o singura coloana specificata si poate defini orice tip de constrangere, iar o constrangere la nivel de tabela se refera la una sau mai multe coloane si este definita separat de definirea coloanei in tabela, poate defini orice tip de constrangere exceptand NOT NULL.

Vom exemplifica definirea unor constrangeri prin cateva exemple.

a.        Definirea unei constrangeri de cheie primara (PRIMARY KEY) coloanei empno din tabela emp.

CREATE TABLE emp(

empno NUMBER (4),

ename VARCHAR(10),

…..

deptno NUMBER(7,2) NOT NULL,

CONSTRAINT emp_empno_pk

PRIMARY KEY (empno));

b.       Definirea constrangeri NOT NULL la nivel de coloana pentru coloanele ename si deptno din tabela emp.

CREATE TABLE emp(

empno NUMBER (4),

ename VARCHAR(10) NOT NULL,

job VARCHAR2(9),

mgr NUMBER(4),

hiredate DATE,

sal NUMBER(7,2),

comm NUMBER(7,2),

deptno NUMBER(7,2) NOT NULL);

c.        Definirea constringeri de cheie unica pentru coloana dname si cheie primara pentru coloana deptno din tabela dept.

CREATE TABLE dept(

deptno NUMBER (2),

dname VARCHAR(12),

loc VARCHAR2(13),

CONSTRAINT dept_dname_uk UNIQUE(dname),

CONSTRAINT dept_deptno_pk PRIMARY KEY (deptno));

d.       Definirea unei constrangeri de cheie externa (FOREIGN KEY) pentru coloana deptno din tabela emp, care refera cheia primara deptno din tabela dept.

CREATE TABLE emp(

empno NUMBER (4),

ename VARCHAR(10) NOT NULL,

job VARCHAR2(9),

mgr NUMBER(4),

hiredate DATE,

sal NUMBER(7,2),

comm NUMBER(7,2),

deptno NUMBER(2) NOT NULL);

CONSTRAINT emp_deptno_fk FOREIGN KEY (deptno) REFERENCES dept (deptno) ON DELETE CASCADE );

O cheie externa este definita folosind urmatoarele cuvinte cheie :

FOREIGN KEY defineste coloana din tabela copil, in cadrul constrangeri la nivel de tabela;

REFERENCES identifica tabela si coloana din tabela parinte;

ON DELETE CASCADE indica faptul ca, atunci cand randurile din tabela parinte sunt sterse, vor fi sterse si rindurile dependente din tabela copil (este optionala). Daca nu se specifica aceasta optiune randurile din tabela parinte nu pot fi sterse daca este referinta la tabela copil.

e.        Definirea unei constrangeri care trebuie indeplinita de toate tuplurile din tabela. Coloana deptno din tabela dept trebuie sa aibe valori cuprinse intre 10 si 99, pentru toate randurile din tabela.

CREATE TABLE dept(

deptno NUMBER (2),

dname VARCHAR(12),

loc VARCHAR2(13),

CONSTRAINT dept_deptno_ck CHECK (DEPTNO BETWEEN 10 AND 99));

Modificarea structurii unui tabel se realizeaza cu ajutorul comenzii ALTER TABLE care permite adaugarea sau modificarea unei coloane, activarea, dezactivarea sau suprimarea unor constrangeri.

Comanda ALTER TABLE, in cazul adaugarii unei noi coloane, are sintaxa:

ALTER TABLE table

ADD (column datatype [DEFAULT expr]

[, column datatype]…)

Adaugarea unei noi coloane cu denumirea job la tabela dept30, se va realiza prin:

ALTER TABLE dept30

ADD (job VARCHAR2 (9));

Coloana nou creata a deveni ultima coloana in tabela.

Comanda ALTER TABLE, in cazul modificarii unei coloane, are sintaxa:

ALTER TABLE table

MODIFY (column datatype [DEFAULT expr]

[, column datatype]…)

Modificarea coloanei ename din tabela dept30 (se pot modifica tipul, lungimea si valorile implicite ale coloanei).

ALTER TABLE dept30

MODIFY (ename VARCHAR2 (15));

Comanda ALTER TABLE, in cazul adaugarii unei noi constangeri coloanei, are sintaxa:

ALTER TABLE table

ADD ([CONSTRAINT constraint] type (column);

Adaugarea unei constrangeri de cheie externa pentru coloana mgr din tabela emp, care refera coloana empno din tabela emp (deci nu putem introduce codul sefului mgr, pentru un angajat daca seful nu exista in tabela empno) .

ALTER TABLE emp

ADD CONSTRAINT emp_mgr_fk

FOREIGN KEY(mgr) REFERENCES emp(empno);

Comanda ALTER TABLE, in cazul dezactivarii unei constangeri, are sintaxa:

ALTER TABLE table

DISABLE CONSTRAINT constraint [CASCADE];

Clauza CASCADE dezactiveaza dependentele constrangeri de integritate.

Dezactivarea constrangeri emp_empno_pk este exemplificta prin secventa:

ALTER TABLE emp

DISABLE CONSTRAINT emp_mgr_fk CASCADE;

Comanda ALTER TABLE, in cazul activarii unei constangeri, are sintaxa:

ALTER TABLE table

ENABLE CONSTRAINT constraint ;

Activarea constrangeri emp_empno_pk este exemplificta prin secventa:

ALTER TABLE emp

ENABLE CONSTRAINT emp_mgr_fk;

Comanda ALTER TABLE, in cazul stergerii unei constangeri, are sintaxa:

ALTER TABLE table

DROP PRIMARY KEY | UNIQUE (column) |

CONSTRAINT constraint [CASCADE];

Stergerea constrangeri de cheie primara din tabela dept precum si stergerea constrangeri de cheie externa asociata din tabela emp.

ALTER TABLE dept

DROP PRIMARY KEY CASCADE;

Putem vizualita toate constrangerile pe o tabela interogand tabela USER_CONSTRAINTS.

Afisarea tuturor constrangerilor pe tabela emp, se realizeaza prin secventa:

SELECT constraint_name, constraint_type,

search_condition

FROM user_constraints

WHERE table_name = 'EMP';

Vizualizarea coloanelor asociate constangerilor din tabela emp se realizeaza interogand tabela USER_CONS_COLUMNS.

SELECT constraint_name, column_name,

FROM user_cons_columns

WHERE table_name = 'EMP';

Stergerea unei tabele se realizeaza cu ajutorul comenzii DROP TABLE care are sintaxa:

DROP TABLE table;

unde: table este numele unei tabele

Schimbarea numelui unei tabele, a unei vizualizari, sau a unei secvente se relizeaza cu comanda RENAME care are sintaxa:

RENAME old_name TO new_name;

unde:

old_name este numele vechi

new_name este numele nou

Stergerea unei tabele si eliberarea spatiului ocupat de aceea tabela se realizeaza cu comanda TRUNCATE, care are sintaxa:

TRUNCATE TABLE table;

unde: table este numele unei tabele

Comanda DELETE TABLE poate sterge randuri dintr-o tabela dar nu poate elibera spatiul ocupat de aceea tabela.

Se poate adauga comentarii unei tabele sau unei coloane prin utilizarea comenzi COMMENT care are sintaxa:

COMMENT ON TABLE table | COLUMN table.column

IS 'text';

unde: table numele tabelei

column este numele unei coloane dintr-o tabela

text este textul din comentariu

Comentariul este salvat in dictionarul de date si poate fi vizualizat in una din urmatoarele tabele ale dictionarului de date in coloana COMMENTS:

ALL_COL_COMMENTS

USER_COL_COMMENTS

ALL_TAB_COMMENTS

USER_TAB_COMMENTS

Putem sterge un comentariu dintr-o tabela prin setarea acestuia la un sir vid (' ').

Stergerea comentariului pentru tabela emp se va realiza prin urmatoarea secventa:

COMMENT ON TABLE emp IS ' ';

Definirea unui tabel view (vizualizare)

O vizualizare este o perceptie logica a datelor apartinand unor tabele sau altor vizualizari. Vizualizarea este definita plecand de la o cerere de manipulare a datelor si mosteneste aceleasi caracterisici ca si obiectele la care se refera (tip, constrangere etc.). Vizualizarea nu solicita o alocare de memorie si este definita in dictionarul datelor cu aceleasi caracteristici ca si un tabel. Exista doua categorii de vizualizari: simple si complexe. Un view simplu contine date dintr-o singura tabela, nu contine functii sau grupari ale datelor si poate contine comenzi de manipulare a datelor. Un view complex contine date din mai multe tabele, contine functii sau grupari ale datelor si nu poate contine comenzi de manipulare a datelor.

Crearea unei vizualizari se realizeaza prin comanda CREATE VIEW, care are sintaxa:

CREATE [OR REPLACE] [FORCE | NOFORCE] VIEW view

[(alias [, alias]…)]

AS subquery

[WITH CHECK OPTION [CONSTRAINT constraint]]

[WITH READ ONLY]

unde

OR REPLACE recreeaza un view daca el exista.

FORCE creeaza un view indiferent daca exista sau nu tabelele de baza.

NOFORCE creeaza un view numai daca exista tabele de baza.

view este numele vizualizari.

alias este numele expresiilor selectate de subquery a vizualizari.

subquery este o instructiune SELECT.

WITH CHECK OPTION specifica faptul ca, numai randurile accesibile (care indeplinesc o conditie) ale unui view pot fi modificate.

Constraint este numele asignat constrangeri CHECK OPTION.

WITH READ ONLY asigura ca, nu pot fi realizate operatii de manipulare a datelor pe acest view.

Vom exemplifica crearea unui view prin cateva exemple.

Crearea unui view care contine informatii despre angajatii din departamentul 10.

CREATE VIEW empvu10

AS SELECT empno, ename, job

FROM emp

WHERE deptno = 10;

Putem afisa structura unui view prin utilizarea comenzi DESCRIBE din SQL*PLUS. Continutul unei tabele view se poate afisa la fel ca si orice tabela folosind comanda SELECT.

Modificarea unui view deja existent, prin adaugarea unui alias fiecarei coloane, se va realiza prin secventa urmatoare:

CREATE OR REPLACE VIEW empvu10

(employee_number, employee_name, job_title)

AS SELECT empno, ename, job

FROM emp

WHERE deptno = 10;

Ordinea aliasurilor trebuie sa tina cont de ordinea coloanelor din subquery.

Crearea unui view complex care contine functii pe grup si afiseaza date din mai multe tabele.

Crearea unui view cu numele dept_sum_vu care contine numele departamentului, salariul minim pe departament, salariul maxim pe departament si media aritmetica a salariului pe departament, se va realiza in urmatoarea secventa:

CREATE VIEW dept_sum_vu

(name, minsal, maxsal, avgsal)

AS SELECT d.dname, MIN(e.sal), MAX(e.sal), AVG(e.sal)

FROM emp e, dept d

WHERE e.deptno = d.deptno

GROUP BY d.dname;

Putem realiza operatiile de manipularea a datelor (INSERT, UPDATE) numai pentru view-uri simple. Nu se pot muta randuri daca view-ul contine o functie pe grup, o clauza GROUP BY, sau cuvantul DISTINCT.

Utilizarea clauzei WITH CHECK OPTION permite specificarea unei constrangeri.

CREATE OR REPLACE VIEW empvu20

AS SELECT *

FROM emp

WHERE deptno = 20

WITH CHECK OPTION CONSTRAINT empvu20_ck;

View-ul creat permite afisarea informatiilor despre angajatii din departamentul 20 si nu permite modificarea informatiilor datorita optiunii WITH CHECK OPTION.

Prin adaugarea optiuni WITH READ ONLY la definirea unui view, operatiile de manipulare a datelor nu pot fi realizate asupra acelui view.

CREATE OR REPLACE VIEW empvu10

(employee_number, employee_name, job_title)

AS SELECT empno, ename, job

FROM emp

WHERE deptno = 10

WITH READ ONLY;

Orice incercare de modificare sau mutare a unui rand din empvu10 va determina aparitia unei erori.

Suprimarea unei tabele view se va realiza cu comanda DROP VIEW, care are sintaxa:

DROP VIEW view;

Comanda indeparteaza definitia view-ului din baza de date.

Definitia unei tabele view este salvata in tabela USER_VIEWS din dictionarul datelor.

Definirea unui index

Pentru a realiza un acces rapid la informatii au fost introdusi indecsi. Indecsii sunt independenti fizic si logic de date. Aceasta inseamna ca ei pot fi creati sau stersi in orice moment si nu au efect asupra tabelelor de baza sau asupra altor indecsi. Indexul contine valorile coloanelor indexate si adresele inregistrarilor corespunzatoare acestor coloane, permitand un acces rapid. Un index poate sa se refere la mai multe coloane ale aceluiasi tabel in acest caz se numeste index compus. Cand se sterge o tabela se vor sterge si indecsi corespunzatori ei. Cazurile in care se recomanda crearea unui index sunt:

Coloana este utilizata frecvent intr-o clauza WHERE sau intr-o conditie de join.

Coloana contine un interval mare de valori.

Coloana contine un numar mare de valori NULL.

Doua sau mai multe coloane sunt frecvent utilizate impreuna intr-o clauza WHERE sau intr-o conditie de join.

Tabela contine multe inregistrari si cele mai multe interogari indeparteaza mai putin de 2-4% din inregistrari.

Nu se recomanda crearea unui index in urmatoarele cazuri:

Tabela contine un numar mic de inregistrari.

Coloanele tabelei nu sunt frecvent utilizate intr-o conditie de interogare.

Cele mai multe interogari indeparteaza mai mult de 2-4% din inregistrari.

Tabela este frecvent modificata.

Doua tipuri de indecsi pot fi creati. Un tip este index unic si este creat automat la definirea unei constrangeri de PRIMARY KEY sau UNIQUE key intr-o tabela.

Cel de-al doilea tip de index nu este index unic si este creat manual pentru a mari viteza si timpul de acces la inregistrarile unei tabele.

Crearea unui index pe una sau mai multe coloane ale unei tabele se realizeaza cu comanda CREATE INDEX, care are sintaxa:

CREATE INDEX index

ON table (column[, column]…);

Crearea unui index pe coloana ename in tabela emp se va realiza prin urmatoarea secventa:

CREATE INDEX emp_ename_idx

ON emp(ename)

Stergerea unui index din dictionarul datelor se va realiya folosind comanda DROP INDEX, care are sintaxa:

DROP INDEX index;

Nu putem modifica un index. Pentru a modifica un index el trebuie sters si recreat din nou. Stergerea unui index se poate realiza numai de cel care a creat indexul (proprietarul) sau cel care are drepul (privilegiul) de DROP ANY INDEX.

Stergerea indexului emp_ename_idx creat anterior se va realiza prin urmatoarea secventa:

DROP INDEX emp_ename_idx;

Indecsi sunt salvati in tabela view USER INDEXES din dictionarul datelor. Putem deasemenea verifica coloanele invocate intr un index prin interogarea tabelei view USER_IND_COLUMNS.

Manipularea unei baze de date

Limbajul de manipulare a datelor este o parte importanta a SQL. Cand dorim sa adaugam (INSERT), sa modificam (UPDATE) sau sa stergem (DELETE) date din baza de date vom executa o comanda a limbajului de manipulare a datelor (Data Manipulation Language).

Adaugarea unui rand intr-o tabela se va realiza cu ajutorul comenzi INSERT care are sintaxa:

INSERT INTO table[(column [,column…])]

VALUES (value [,value…]);

unde

table este numele tabelei in care se va insera un rand.

column este numele coloanei din tabela.

value este valoarea corespunzatoare coloanei.

Aceasta comanda adauga numai un rand la un moment dat intr-o tabela.

Inserarea unui rand continand valori pentru fiecare coloana a tabelei. Vom insera un rand nou in tabela dept prin urmatorea secventa:

INSERT INTO dept(deptno, dname, loc)

VALUES (50, 'ANALIST', 'ORADEA');

Inserarea unui rand care contine valori NULL se poate realiza implicit prin omiterea coloanei care contine valori NULL din lista de coloane. Vom exemplifica prin exemplu urmator:

INSERT INTO dept(deptno, dname)

VALUES (60, 'MIS');

Inserarea unui rand care contine valori NULL se poate realiza explicit prin specificarea cuvantului NULL in lista de valori. Vom exemplifica prin exemplu urmator:

INSERT INTO dept(deptno, dname, loc)

VALUES (70, 'FINANCIAR', NULL);

Inserarea valorilor prin utilizarea variabilelor de substitutie va permite introducerea valorilor interactiv. De exemplu, vom introduce informatii interactiv in tabela dept prin secventa urmatoare:

INSERT INTO dept(deptno, dname, loc)

VALUES (&dept_id '&dept_name', '&dept_loc');

In linia de prompter se va afisa un mesajul pentru fiecare variabila, de forma ' Enter value for <variabila> :'.

Afisarea unui mesaj definit de utilizator prin utilizarea comenzii ACCEPT din SQL*PLUS, se va realiza astfel:

ACCEPT dept_id PROMPT ' Introduce-ti dept_id:

ACCEPT dept_name PROMPT ' Introduce-ti dept_name:

ACCEPT dept_loc PROMPT ' Introduce-ti dept_loc:

INSERT INTO dept(deptno, dname, loc)

VALUES (&dept_id '&dept_name', '&dept_loc');

Copierea unor inregistrari dintr-o alta tabela utilizand comanda INSERT va fi exemplificata prin urmatorul exemplu:

INSERT INTO manager(id, name, salary, hiredate)

SELECT empno, ename, sal, hiredate

FROM emp

WHERE job = ' MANAGER';

Numarul de coloane in clauza INSERT trebuie sa fie acelasi cu numarul de coloane din comanda SELECT.

Modificarea unui rand existent dintr-o tabela se realizeaza utilizand comanda UPDATE, care are sintaxa:

UPDATE table

SET column = value [, column = value]

[WHERE condition]

unde:   

table este numele tabelei.

column este numele coloanei din tabela.

value valoarea corespunzatoare coloanei.

condition conditia care identifica randurile care vor fi modificate.

Utilizarea clauzei WHERE pentru a specifica randurile care vor fi modificate intr-o tabela:

UPDATE emp

SET deptno = 20

WHERE empno = 7782;

Se modifica coloana deptno pentru inregistrarile care au campul empno = 7782.

Subqueri multiple coloane pot fi implementate intr-o clauza SET a comenzi UPDATE. Vom exemplifica acest lucru prin urmatorul exemplu:

UPDATE emp

SET (job, deptno) =

(SELECT job, deptno

FROM emp

WHERE empno = 7499)

WHERE empno = 7698;

Se modifica coloanele job si deptno pentru angajatul cu codul 7698, coloanele vor fi egale cu a angajatului cu codul 7499.

Putem utiliza subqueri intr-o comanda UPDATE pentru a modifica randuri intr-o tabela bazate pe valori dintr-o alta tabela. Vom exemplifica printr-un exemplu:

UPDATE employee

SET deptno = (SELECT deptno

FROM emp

WHERE empno = 7788)

WHERE job = (SELECT job

FROM emp

WHERE empno = 7788);

În exemplu, se modifica numarul de departament pentru angajatii care au acelasi job ca si angajatul cu codul 7788.

Nu poate fi modificata valoarea unei coloane dintr-o inregistrare daca aceea coloana este legata printr-o constrangere de integritate. De exemplu, modificarea coloanei deptno din tabela emp va determina aparitia unei erori, deoarece coloana deptno este cheie externa, .

UPDATE emp

SET deptno = 55

WHERE deptno = 10;

Pentru a suprima un rand existent dintr-o tabela se va utiliza comanda DELETE, care are urmatoarea sintaxa:

DELETE [FROM] table

[WHERE condition]

unde:

table este numele tabelei.

condition este o conditie care identifica randurile care vor fi suprimate.

Randurile care se vor sterge se vor specifica prin clauza WHERE.

DELETE FROM dept

WHERE dname = 'ANALIST

Se vor sterge toate randurile din tabela dept care au numele departamentului ANALIST.

Daca clauza WHERE este omisa se vor sterge toate randurile din tabela.

DELETE FROM dept;

Putem utiliza subqueri-uri la stergerea randurilor dintr-o tabela, bazandu-ne pe valori dintr-o alta tabela. Vom exemplifica prin urmatorul exemplu:

DELETE FROM employee

WHERE deptno =

(SELECT deptno

FROM dept

WHERE dname ='VÂNZARI

În exemplu de mai sus se vor sterge toate randurile din tabela employee care au numarul de departament egal cu angajati din departamentul VÂNZARI.

Nu putem sterge un rand care contine o cheie primara care a fost utilizata ca si cheie externa intr-o alta tabela. De exemplu, stergerea randurilor din tabela dept care au coloana deptno =10, va determina aparitia unei erori, deoarece deptno este o cheie primara in tabela dept si a fost utilizata ca si cheie externa in tabela emp.

DELETE FROM dept

WHERE deptno = 10;

Interogarea unei baze de date relationale.

Comanda fundamentala a standardului SQL care permite interogarea unei baze de date este SELECT. Sintaxa generala a comenzii SELECT este urmatoarea:

SELECT [ DISTINCT ]

FROM lista de tabele

WHERE conditie de cautare asupra liniilor

GROUP BY lista de atribute care permit partitionarea

HAVING conditie asupra partitiilor

ORDER BY lista de atribute

Clauzele SELECT si FROM sunt obligatorii si specifica datele care se vor selecta si tabelele din care se selecteaza. Restul clauzelor sunt optionle si permit rafinarea selectiei.

Strategia pentru scrierea comenzii SELECT este data de urmatorul algoritm:

Se determina coloanele (atributele) ce vor fi vizualizate si se include in clauza SELECT.

Se determina tabelele implicate si se includ in clauza FROM.

Daca clauza SELECT include functii pe grup, atunci se introduce clauza GROUP BY si se reiau toate atributele mentionate in clauza SELECT la are au fost aplicate functii pe grup.

Se determina conditiile care limiteaza selectarea. Conditiile care se refera la grup apar in clauza HAVING. Iar cele care se refera la valori individuale apar in clauza WHERE.

Daca este necesara valoarea unui atribut din alt tabel sau este necesara o functie pe grupuri in clauza WHERE, atunci se utilizeaza un subquery (o alta instructiune SELECT).

Daca este necesara fuzionarea rezultatelor din doua clauze SELECT, se utilizeaza clauza UNION.

Cu ajutorul clauzei ORDER BY se precizeaza ordinea in care apar tuplurile.

Pentru a exemplifica vom lua in considerare tabele EMP si DEPT care au fost descrise mai sus si vom arata cateva exemple concrete implementate in SQL*PLUS.

Utilizand instructiunea SELECT putem face urmatoarele operatii:

Selectie – selecteaza randuri dintr-o tabela de date dupa un anumit criteriu. Sunt vazute doar randurile care indeplinesc conditia din criteriul specificat.

Proiectie – selecteaza anumite coloane dintr-o tabela de date.

Join – permite afisarea datelor din mai multe tabele, tabelele avand o legatura intre ele.

Interogarea unei tabele

Varianta cea mai simpla a instrutiuni SELECT este :

SELECT [ DISTINCT ]

FROM lista de tabele;

unde:

SELECT identifica ce coloane.

FROM identifica din ce tabele.

DISTINCT elimina duplicatele.

Exemple

Ø     Selectarea tuturor coloanelor din tabela DEPT.

SELECT *

FROM dept;

Ø     Selectarea unor coloane specificate explicit si toate randurile dintr-o tabela. Afisam numarul departamentului si localitatea din tabela DEPT.

SELECT deptno , loc

FROM dept;

OBS:

Alinierea in cazul datelor de tip data si de tip caracter este la stanga iar in cazul tipului numeric este la dreapta.

Implicit afisarea va fi cu majuscule. Default Display : Uppercase

Precedenta operatorilor aritmetici este: *, /, +, -.

Parantezele pot forta evaluarea prioritatii si clarifica instructiunea.

Ø     Afisam numele persoanei, salarul pe o luna si salarul annual plus un bonus de 100, din tabela EMP.

SELECT ename, sal, 12*sal+100

FROM emp;

OBS: Se va calcula astfel : (12*sal)+100, se va tine cont de prioritatile operatiilor si atunci parantezele nu sunt necesare.

Ø     Afisam numele persoanei, salarul pe o luna si salarul annual care a fost calculat din salarul pe o luna plus un bonus de 100 inmultit cu 12, din tabela EMP.

SELECT ename, sal, 12*(sal+100)

FROM emp;

OBS: Folosirea parantezelor va forta evaluarea expresiei.

Ø     Utilizarea unui alias pentru o coloana se face astfel:

SELECT ename AS name, sal salary;

FROM emp;

OBS: cuvantul cheie AS poate fi optional. Coloanele name si salary implicit vor fi afisate cu litere mari.

SELECT ename “Name” sal*12 “Annual salary”

FROM emp;

OBS: În acest caz numele coloanelor va fi afisat cum au fost specificate: “Name” si “Annual salary”.

Concatenarea coloanelor sau sirurilor de caractere cu alte coloane

Ø     Utilizarea operatorului de concatenare

SELECT ename || job AS “Employees”

FROM emp;

În acest exemplu: campurile ename si job vor fi concatenate si vor avea alias-ul “Employees”. Cuvantul cheie AS este necesar in acest caz pentru a citi corect.

Ø     Utilizarea caracterului de concatenare in exemplul urmator:

SELECT ename || ‘ ‘ || ‘is a’ || ‘ ‘ || job

AS “ Employee Details”

FROM emp;

Ø     Eliminarea randurilor duble poate fi facuta utilizand cuvantul cheie DISTINCT in clauza SELECT.

SELECT DISTINCT depno

FROM emp;

OBS: Pot fi specificate mai multe coloane dupa DISTINCT, in acest caz vor fi luate in considerare toate coloanele, adica vor fi evaluate toate informatiile din toate coloanele impreuna.

SELECT DISTINCT depno , job

FROM emp;

Restrictionarea randurilor returnate dintr-o tabela se poate face utilizand clauza WHERE in instructiunea SELECT.

SELECT [DISTINCT]

FROM tabela

[WHERE conditie

Ø     Afiseaza numele angajatului (ename), job-ul (job) si numarul departamentului (deptno), pentru toti angajatii din tabela EMP al caror nume de job este ANALIST.

SELECT ename, job, deptno

FROM emp;

WHERE job = ’ANALIST’;

OBS: Sirurile de caractere si tipul data calendaristica trebuiesc incluse intre apostroafe. Formatul de data in Oracle implicit este: DD_MON_YY. Acest format se poate modifica.

Operatorii de comparatie sunt:

egal

> mai mare

< mai mic

>= mai mare egal

<= mai mic egal

<> diferit

Operatorii de comparatie pot fi utilizati in compararea unei expresii cu o alta expresie. Ei sunt utilizati in clauza WHERE in urmatorul format :

… WHERE expresie operator valorare

Exemple

… WHERE hiredate = '01-JAN-95'

… WHERE sal > 1500

… WHERE ename ='SMITH'

Alti operatori de comparatie sunt:

BETWEEN…AND… compara daca se gaseste intre doua valori, deci intr-un interval [min, max]

IN (list) cuprins in lista de valori.

LIKE daca se potriveste cu macheta.

IS NULL compara cu o valoare NULL

Ø     Afiseaza toti angajati care au salariul intre 1000 si 1500.

SELECT ename, sal

FROM emp

WHERE sal BETWEEN 1000 AND 1500;

Trebuie specificata mai intai limita inferioara apoi limita superioara.

Ø     Afiseaza angajati pentru care codul managerului (mgr) este una din valorile specificate in lista.

SELECT ename, sal, mgr

FROM emp;

WHERE mrg IN (7902,7566,7788);

Utilizarea operatorului LIKE.

Conditia de cautare poate contine unul din caracterele:

% -inlocuieste 0 sau mai multe caractere

- -inlocuieste orice caracter, dar unul singur.

Ø     Afiseaza toate persoanele a caror nume incepe cu S.

SELECT ename, sal

FROM emp

WHERE ename LIKE '%S';

Ø     Afiseaza toate persoanele a caror data de angajare (hiredate) este intre ianuarie 1981 si decembrie 1981.

SELECT ename

FROM emp

WHERE hiredate LIKE '%81';

Ø     Afiseaza toti angajati a caror nume contine litera A al doilea caracter.

SELECT ename

FROM emp

WHERE ename LIKE '_A';

Ø     Afiseaza toti angajatii a caror nume contine 'A_B'.

SELECT ename

FROM emp

WHERE ename LIKE '%A_%B' Escape'';

Cand folosim ESCAPE , underscore este interpretat ca si caracter.

Utilizarea operatorului IS NULL

O valoare NULL nu este 0 sau spatiu. O valoare NULL este o valoare necunoscuta. Daca intr-o expresie vom avea o valoare NULL evaluarea expresiei va fi NULL.

Ø     Afiseaza toti angajatii care nu au comision (comm este NULL).

SELECT ename, job

FROM emp

WHERE comm IS NULL;

Operatorii logici sunt:

AND -returneaza TRUE cand ambele componente sunt TRUE.

OR -returneaza TRUE cand cel putin una din componente este TRUE.

NOT -returneaza TRUE cand componenta este FALSE.

Ø     Afiseaza toti angajati care au job ='CLERK' si salarul >= 1100.

SELECT empno, ename, job, sal

FROM emp

WHERE sal >=1100 AND job = 'CLERK';

Ø     Afiseaza toti angajati care au job = 'CLERK' sau salarul >= 1100.

SELECT empno, ename, job, sal

FROM emp

WHERE sal >= 1100 OR job = 'CLERK';

Ø     Afiseaza toti angajatii din tabela EMP a caror job nu este CLERK, MANAGER sau ANALYST.

SELECT ename, job

FROM emp

WHERE job NOT IN ('CLERK', 'MANAGER', 'ANALYST');

Clauza ORDER BY permite afisarea randurilor intr-o ordine specifica. Trebuie plasata ultima clauza intr-o comanda SELECT. Clauza ORDER BY revine ultimei comenzi SELECT.

ASC - ascendent, care este ordinea implicita.

DES - descendent

Ø     Afiseaza angajati in ordine descrescatoare dupa data de angajare. (hiredate).

SELECT ename, job, deptno, hiredate

FROM emp

ORDER BY hiredate DESC;

Ø     Sortarea datelor dupa aliasul coloanei. Poate fi utilizat aliasul coloanei in clauza ORDER BY.

SELECT empno, ename, sal*12 salar_anual

FROM emp

ORDER BY salar_anual;

Putem sorta datele dupa mai multe coloane. Numarul maxim de coloane fiind cel pe care il are tabela. Putem specifica in clauza ORDER BY si coloane care nu apar in clauza SELECT.

Ø     Afiseaza datele din tabela EMP ordonate dupa numarul departamentului (deptno) ascendent si dupa salar (sal) descendent.

SELECT ename, sal

FROM emp

ORDER BY deptno, sal DESC;

OBS: Campul deptno nu este specificat in SELECT, dar poate fi folosit in clauza ORDER BY.

Functii in SQL

Exista doua tipuri de functii in SQL. Functii care manipuleaza un singur rand (single-row) si functii care manipuleaza un grup de randuri (multiple-row).

Functiile single-row sunt:

Functii pentru tipul caracter

Functii pentru tipul numeric

Functii pentru data calendaristica

Functii de conversie dintr-un tip de data in altul

Functii generate : NVL

DECODE

Functii pentru tipul caracter

Sunt de doua tipuri:

Functii de conversie

- LOWER ( column/expresie) -converteste la litere mici o valoare alfanumerica

- UPPER (column/expresie) - converteste la litera mare

- INITCAP (column/expresie) - converteste prima litera

mare restul mici

Functii de manipulare a caracterelor:

-CONCAT(column/expresie, column/expresie)- concateneaza doua coloane. Este echivalent cu

- SUBSTR (column/expresie, m [,n]) – returneaza caractere din sir incepand de la pozitia m, n caractere. Daca n este omis va returna incepand de la m pana la sfarsitul sirului.

- LENGTH (column/expresie) – returneaza numarul de caractere.

-INSTR (column/expresie, m) – returneaza pozitia caracterului specificat in sirul dat de expresie.

- LPAD ( column/expresie, n,'string') – aliniaza la dreapta pe lungimea n, introducand sirul ‘string’ pe lungimea ramasa din partea stanga.

Functii de conversie a sirurilor de caractere:

Vom exemplifica utilizarea functiilor de conversie pentru siruri de caractere prin cateva exemple.

Ø     Afiseaza informatii pentru angajatul cu numele 'Blake'.

SELECT empno, ename, sal, comm, deptno

FROM emp

WHERE INITCAP(ename) ='Blake';

Functii de manipulare a caracterelor:

Vom exemplifica utilizarea functiilor de conversie pentru siruri de caractere prin cateva exemple.

Ø     Afiseaza informatii despre angajatii care lucreaza la departamentul FINANCIAR.

SELECT ename, sal, comm, LENGTH(ename)

FROM emp

WHERE SUBSTR(job, 1, 9) ='FINANCIAR';

Functii pentru date numerice

ROUND - rotunjeste o valoare , specificata zecimal.

TRUNC - truncheaza o valoare specificata zecimal.

MOD - returneaza restul impartirii a doua numere.

Functii pentru date calendaristice

Oracle stocheaza data intr-un format intern numeric: secol, an, luna, zi, ora, minute si secunde. Implicit formatul pentru data este : DD_MON_YY. SYSDATE este o functie care returneaza data si ora curenta.

Dual este o tabela care apartine user-ului SYS si poate fi accesata de toti utilizatorii. Afisarea datei curente folosind tabela dual :

SELECT SYSDATE

FROM dual;

Adunand sau scazand o valoare numerica dintr-o data calendaristica rezultatul va fi tot o data calendaristica. Deoarece tipul data este stocat ca si tipul numeric, putem utiliza operatori aritmetici cum ar fi adunarea si scaderea. Putem aduna sau scadea un numar dintr-o data

Se pot utiliza urmatoarele operatii:

Ø     Afiseaza numele angajatilor si numarul de saptamani de lucru pentru toti angajati din departamentul 10.

SELECT ename, (SYSDATE - hiredate)/7 WEEKS

FROM emp

WHERE depno=10;

Functii utilizate pentru tipul Date

MONTHS_BETWEEN(date1,date2): returneaza numarul de luni dintre doua date.

ADD_MONTHS(date, n): aduna un numar n de luni la o data, n trebuie sa fie intreg si pozitiv.

NEXT_DAY(date,'sir') - returneaza urmatoarea zi dintr-o data specificata, sir poate fi un numar reprezentand o zi sau un sir de caractere.

LAST_DAY(date) - returneaza ultima zi dintr-o luna.

ROUND(date[,’fmt’]) - returneaza data rotunjita la unitatile specificate in formatul fmt. Daca formatul fmt este omis, data este rotunjita la data cea mai apropiata.

TRUNC(date[,’fmt’]) - truncheaza o data dupa formatul specificat fmt, daca formatul este omis se truncheaza la zi.

Exemple:

MONTHS_BETWEEN('01-SEP-95', '11-JAN-94')

ð   

ADD_MONTHS('11-JAN-94', 6) => '11-JUL-94'

NEXT_DAY('01-SEP-95','FRIDAY')    => '08-SEP-95'

LAST_DAY('01-SEP-95')    => '30-SEP-95'

ROUND('25-JUL-95','MONTH') => 01-AUG-95

ROUND('25-JUL-95','YEAR') => 01-JAN-96

TRUNC('25-JUL-95','MONTH') => 01-JUL-95

TRUNC('25-JUL-95','YEAR') => 01-JAN-95

Ø     Afiseaza numarul angajatilor si data angajarii pentru toti angajatii din 1987.

SELECT ename, hiredate, ROUND(hiredate,'MONTH'), TRUNC(hiredate,'MONTH')

FROM emp

WHERE hiredate like '%87' ;

Functii de conversie

În asignari, Oracle poate converti automat urmatoarele tipuri de date:

Asignarea reuseste daca serverul Oracle poate converti tipul de date al valorii folosite in asignare la tipul variabilei asignate.

Pentru evaluarea expresiilor Oracle poate converti automat urmatoarele tipuri de date:

În general, cand este necesara o conversie de tip de date, in locurile in care regulile de conversie folosite in asignari nu pot fi folosite, serverul Oracle foloseste regulile pentru expresii.

Conversiile explicite de date

Exista 3 functii pentru conversia unui tip de data in altul:

TO_CHAR(number/date [,'fmt') - converteste o valoare numerica sau o data la un sir de caractere, dupa formatul specificat 'fmt'.

TO_NUMBER(char) - converteste un sir de caractere continand cifre la un numar.

TO_DATE(char[,'fmt']) - converteste un sir de caractere reprezentand o data calendaristica, la tipul data dupa formatul specificat 'fmt', daca 'fmt' este omis, formatul de data implicit este DD-MON-YY.

Formatul model trebuie inclus intre apostroafe ' ' si este case sensitive.

Elemente de formatare a datei:

Ø     Afiseaza numele si data angajarii pentru toti angajatii din tabela EMP.

SELECT ename, TO_CHAR(hiredate, fmDD Month YYYY') DATA_ANG

FROM emp ;

Elemente pentru formatarea orei dintr-o data

Elementele de timp formateaza partea de timp dintr-o data.

Includerea de caractere in afisarea datei se face prin incadrarea lor intre ghilimele ''.

Afisarea in litere a unui numar se face astfel:

Pentru a afisa valoarea unui numar ca si caracter se pot folosi urmatoarele formate:

Convertirea unui sir de caractere la un numar se face folosind functia: TO_NUMBER(char).

Convertirea unui sir la o data se face folosind functia: TO_DATE(char[,'fmt']).

Ø     Afisam salarul lui SCOTT folosind simbolul si indicatorul de mii.

SELECT TO_CHAR(sal, '$99,999') SALARY

FROM emp WHERE ename = 'SCOTT ';

Ø     Afiseaza numele si data angajarii tuturor angajatilor din tabela EMP care s-au angajat in 22 februarie 1981.

SELECT ename, hiredate

FROM emp

WHERE hiredate = TO_DATE('February 22, 1981', 'Month dd, YYYY');

Functia NVL

Converteste o valoare NULL la o valoare actuala.

Tipurile de date care pot fi folosite sunt: date, number si char:

NVL(comm, 0)

NVL(hiredate, '01-JAN-97')

NVL(job, 'No job yet').

Sintaxa:

NVL(expr1, expr2)

unde:

expr1 este expresia care poate contine NULL

expr2 este valoarea la care va fi convertita o expresie NULL.

Ø     Afiseaza numele, salarul, comisionul si salarul annual pentru toti angajatii din tabela EMP, daca comisionul este NULL va fi convertit la 0.

SELECT ename, sal, comm, (sal*12)+NVL(comm,0)

FROM emp;

Functia DECODE

Este similara cu CASE sau IF-THEN-ELSE. Are sintaxa:

DECODE(col/expresie, s1, r1[,s2, r2,…..][,default])

Functia DECODE evalueaza expresia, dupa care o compara cu fiecare valoare s1, s2,….. Daca valoarea expresiei este aceasi cu una dintre valorile s1, s2,… atunci se va returna rezultatul corespunzator valorii gasite. Daca valoarea implicita [default] este omisa, o valoare NULL va fi returnata atunci cand nu se gaseste nici o valoare identica cu expresia.

Exemplu:

SELECT job, sal,

DECODE(job, 'ANALIST', sal*1.1,

'CLERK', sal*1.15,

'MANAGER', sal*1.2,

sal)

REVISED_SALARY

FROM emp;

Aceeasi instructiune poate fi scrisa folosind IF-THEN-ELSE astfel:

IF job = 'ANALIST' THEN sal = sal*1.1

IF job = 'CLERK ' THEN sal = sal*1.15

IF job = 'MANAGER' THEN sal = sal*1.2

ELSE sal = sal

Functiile avand ca si rezultat un singur rand, pot fi incuibate pana la orice adancime. Functiile incuibate sunt evaluate incepand cu cel mai adanc nivel spre nivelul superior.

Ø     Afiseaza numele managerului, cel care nu mai are sef (mgr este NULL).

SELECT ename,NVL(TO_CHAR(mgr), 'No Manager')

FROM emp

WHERE mgr IS NULL;

Interogarea mai multor tabele

Folosind o conditie de join putem obtine date din mai multe tabele. Scrierea unei conditii de join se face intr-o clauza WHERE.

Randuri dintr-o tabela pot fi legate cu randuri dintr-o alta tabela continand aceasi valoare in coloane corespondente, de obicei coloane care contin chei primare si chei externe. Exista doua tipuri principale de conditii join :

equijoin

non-equijoin

In plus metodele join includ urmatoarele:

outer join

self join

set operators

Equijoin

O conditie de equijoin se va scrie astfel:

SELECT tabel1.coloana, tabel2.coloana

FROM tabel1, tabel2

WHERE tabel1.coloana = tabel2.coloana;

Ø     Afiseaza numele angajatului, numele departamentului precum si localitatea unde se afla departamentul, pentru toti angajati.

SELECT emp.empno, emp.ename, emp.depno,

dept.dname, dept.loc

FROM emp, dept

WHERE emp.depno = dept.depno;

Relatia dintre tabela EMP si DEPT este un equijoin, valorile din coloana depno din ambele tabele trebuie sa fie egale.

Deoarece coloana depno are acelasi nume in ambele tabele, ea trebuie prefixata cu numele tabelei pentru a nu exista ambiguitati.

În plus, de conditia de join se poate adauga un criteriu de cautare in clauza WHERE.

Ø     Afiseaza numele angajatului, numele departamentului precum si localitatea unde se afla departamentul, pentru angajatul cu numele King.

SELECT ename, emp.depno, dname, loc

FROM emp, dept

WHERE emp.depno = dept.depno

AND INICAP(ename) = 'King';

Nu este necesara prefixarea coloanelor cu numele tabelei in cazul in care nu sunt comune in ambele tabele .

Se pot utiliza aliasuri pentru tabele, simplificand astfel scrierea interogarii:

SELECT ename, e.depno, dname, loc

FROM emp e, dept d

WHERE e.depno = d.depno

AND INICAP(ename) = 'King';

OBS: Aliasul tabelei este valid numai in SELECT-ul curent.

Daca folosim un alias pentru o tabela atunci numele tabelei trebuie inlocuit cu aliasul specific tabelei .

Non-Equijoin

Avand 2 tabele EMP si SALGRADE:

EMP:

SALGRADE:

Relatia dintre tabela EMP si tabela SALGRADE este de non-equijoin in sensul ca, coloana sal din tabela EMP este intre valorile coloanele Losal si Hisal din tabela SALGRADE.

Ø     Afisam numele angajatilor, salarul si gradul de salarizare pentru fiecare angajat din tabela EMP.

SELECT e.ename, e.sal, s.grade

FROM emp e, salgrade s

WHERE e.sal BETWEEN s.losal AND s.hisal;

Obs: Se poate folosi >= AND <= inlocuind astfel BETWEEN.

Outer-join

Vom utiliza operatia de outer-join pentru a vizualiza randuri care nu sunt vizualizate intr-o conditie de join. Operatorul outer-join este semnul (+). Scrierea unei conditii de outer-join se va realiza folosind urmatoarea sintaxa:

SELECT table.column, table.column

FROM table1, table2

WHERE table1.column(+) = table2.column;

sau

SELECT table.column, table.column

FROM table1, table2

WHERE tabel1.column = tabel2column(+);

Randurile care nu apar la o conditie de join pot fi returnate folosind operatorul outer-join intr-o conditie de join.

Operatorul outer-join este plasat in partea deficienta in informatie. Acest operator va avea ca efect crearea unor randuri NULL in tabela deficitara in informatie pentru infomatia existenta in tabela care contine informetie in plus. Operatorul outer-join (+) poate fi plasat in oricare dintre tabele dar nu in ambele simultan.

Vom considera tabele EMP si DEPT:

EMP: DEPT:

Ø     Afisarea tuturor angajatilor si numele tuturor departamentelor vom scrie urmatoarea secventa :

SELECT e.ename, d.depno, d.dname

FROM emp e, dept d

WHERE e.depno (+) = d.depno

ORDER BY e.depno;

Obs: Departamentul ACCOUNTING care nu are angajati va fi si el afisat.

O conditie care implica outer-join nu poate sa contina operatoeul IN si nici o alta conditie folosind operatorul OR

Self-join

Cateodata este nevoie sa interoghezi o tabela cu ea insasi. Vom lua spre exemplificare urmatoarea tabela:

EMP(ANGAJATI): EMP (MANAGERI):

De exemplu, pentru a gasi numele managerului lui Blake va trebui sa parcurgem urmatorii pasi :

gasirea lui Blake in tabela EMP, cautand in coloana ename

gasirea codului managerului pentru Blake adica mgr, care este 7839.

gasirea persoanei care are empno=7839 si afisarea numelui acestei persoane. Deci persoana care are empno=7839 este KING.

Ø     Afisam numele angajatului si numele managerului angajatului. Este necesar sa simulam doua tabele in clauza FROM, sunt doua aliasuri ANGAJATI si MANAGER, pentru aceeasi tabela EMP.

SELECT angajati.ename || ' works for ' || manager.ename

FROM emp angajati, emp manager

WHERE angajati.mgr = manager.empno;

În acest exemplu tabela EMP a fost interogata cu ea insasi prin folosirea unor aliasuri cu nume diferit pentru aceesi tabela.

Functii pe grup

Aceste functii opereaza pe seturi de randuri avand un rezultat pe grup. Fiecare din aceste functii accepta un argument. În continuare vom enumera aceste functii:

AVG([DISTINCT | ALL], n) - returneaza valoarea medie a lui n, ignorand valorile nule.

COUNT( ) - returneaza numarul de randuri pentru care expresia expr este diferita de null. Returneaza toate randurile selectate utilizand * incluzand duplicatele si randurile cu valori null.

MAX([DISTINCT | ALL], expr) - returneaza valoarea maxima a expresiei expr, ignorand valorile null.

MIN([DISTINCT | ALL], expr) - returneaza valoarea minima a expresiei expr, ignorand valorile null.

STDDEV([DISTINCT | ALL], x) - returneaza devierea standard a lui x, ignorand valorile null.

SUM([DISTINCT | ALL], n) - returneaza suma valorilor lui n, ignorand valorile null.

VARIANCE([DISTINCT | ALL], x) - returneaza varianta lui x, ignorand valorile null.Utilizand clauza DISTINCT rezultatul va contine numai randurile neduplicate. Clauza ALL determina ca functia sa ia in considerare si valorile duplicate. Implicit este clauza ALL.

Toate functiile exceptand functia COUNT(*) ignora valorile null. Substituirea valorilor null se realizeaza utilizand functia NVL.

Tipul argumentelor pentru functiile COUNT, MIN si MAX poate fi: CHAR, VARCHAR2, NUMBER sau DATE.

Putem utiliza functiile AVG, SUM, VARIANCE si STDDEV pentru coloanele care contin date numerice.

Ø     Afisarea primei persoane angajate respectiv a celei mai noi persoane care s-a angajat, se va realiza in secventa:

SELECT MIN(hiredate), MAX(hiredate)

FROM emp;

Ø     Afisarea numarului de departamente din tabela EMP (se va lua in considerare si valorile duplicate)

SELECT COUNT(deptno)

FROM emp;

Ø     Afisarea numarului de departamente distincte din tabela EMP (nu se vor lua si valorile duplicate)

SELECT COUNT(DISTINCT(deptno))

FROM emp;

Toate functiile pe grup exceptand functia COUNT(*) ignora valorile nule. Functia NVL forteaza includerea valorilor null de catre functiile pe grup.

Ø     În acest exemplu, se va calcula media aritmetica a comisionului pentru toti angajati din tabela emp, (in cazul in care coloana comm are valori null, se va converti la valoarea zero valoarea null).

SELECT AVG(NVL(comm, 0)) 'Comisionul annual'

FROM emp;

Crearea grupurilor de date: Clauza GROUP BY

În exemplele anterioare, toate functiile pe grup au tratat tabela de date ca un grup mare de informatii. Uneori este nevoie ca tabela sa fie impartita in grupuri mai mici. Acest lucru se poate realiza folosind clauza GROUP BY intr-o comanda SELECT, astfel:

SELECT coloane, functii_pe _grup(coloana)

FROM tabele

[WHERE conditii]

[GROUP BY expresia_de_ grupare]

[ORDER BY lista_de_coloane];

unde:

expresia_de_grupare: specifica coloanele ale caror valori vor determina gruparea randurilor.

Utilizand clauza WHERE, putem exclude randuri inaintea impartirii lor pe grupuri. Nu putem utiliza aliasul coloanei in clauza GROUP BY, ci doar numele coloanei. Implicit rindurile sunt ordonate crescator dupa numele coloanelor incluse in lista clauzei GROUP BY. Ordinea de sortare poate fi modificata utilizand clauza ORDER BY.

Ø     Afisam numarul departamentului si media aritmetica a salariului pe departament.

SELECT deptno, AVG(sal)

FROM emp

GROUP BY deptno;

Cand utilizam clauza GROUP BY, trebuie sa includem in clauza GROUP BY toate coloanele din lista SELECT care nu sunt incluse in functiile pe grup (in exemplu de mai sus coloana deptno).

OBS: Coloanele din clauza GROUP BY nu trebuie neaparat sa fie incluse in lista coloanelor SELECT.

Ø     Afisam media aritmetica a salariului pe departament, fara sa afisam numarul departamentului.

SELECT AVG(sal)

FROM emp

GROUP BY deptno;

Ø     Afisam numarul departamentului si media aritmetica a salariului grupat pe departament in ordine crescatoare a mediei aritmetice a salariului pe departament.

SELECT deptno, AVG(sal)

FROM emp

GROUP BY deptno

ORDER BY AVG(sal);

OBS: Putem folosi functiile pe grup in clauza ORDER BY, ca in exemplu de mai sus.

Uneori este nevoie sa afisam rezultatele unui grup din interiorul unui alt grup. Acest lucru se realizeaza specificand in clauza GROUP BY lista de coloane. În clauza GROUP BY pot fi specificate mai multe coloane.

Ø     Sa se afiseze suma totala a salarului pe fiecare job in cadrul fiecarui departament.

SELECT deptno, job, SUM(sal)

FROM emp

GROUP BY deptno, job;

În acest exemplu, prima data randurile vor fi grupate dupa numarul departamentului, iar in cadrul departamentului dupa job. Functia SUM se aplica pe fiecare departament in parte si va calcula suma pe job in cadrul fiecarui departament.

Nu putem folosi clauza WHERE pentru a restrictiona grupurile. Vom exemplifica acest lucru prin urmatorul exemplu:

SELECT deptno, AVG(sal)

FROM emp

WHERE AVG(sal) > 2000

GROUP BY deptno;

OBS: Acest exemplu are o eroare la clauza WHERE AVG(sal) > 2000 deoarece nu se poate utiliza clauza WHERE impreuna cu clauza GROUP BY. Acest exemplu poate fi corectat prin inlocuirea clauzei WHERE cu clauza HAVING.

Vom utiliza clauza HAVING intr-o instructiune SELECT pentru a specifica care dintre grupuri vor fi afisate. Sintaxa instructiunii SELECT va fi:

SELECT coloane, functii_pe _grup(coloana)

FROM tabele

[WHERE conditii]

[GROUP BY expresia_de_ grupare]

[HAVING BY conditie_de_ grupare]

[ORDER BY lista_de_coloane];

unde:

conditie_de_grupare: specifica conditia de restrictionare a grupurilor.

Cand se utilizeaza clauza HAVING intr-o instructiune SELECT se vor executa urmatorii pasi:

se vor grupa randurile.

se vor aplica functiile pe grup.

se vor afisa grupurile care respecta conditia din clauza HAVING.

Clauza HAVING poate precede clauza GROUP BY, dar este recomandat sa plasam clauza GROUP BY inainte deoarece este mult mai logic. Grupurile sunt formate si functiile pe grup sunt calculate inainte ca, clauza HAVING sa fie aplicata grupurilor din lista SELECT.

Ø     Se cere sa se afiseze salarul total lunar pentru fiecare functie (job), pentru care salarul total mai mare decat 10 000, ordonat dupa salarul total lunar.

SELECT job, SUM(sal) SUMA_SALAR

FROM emp

GROUP BY job

HAVING SUM(sal) > 10.000

ORDER BY SUM(sal);

OBS: Functiile pe grup pot fi incuibate doar maxim 2 adancimi. De exemplu, pentru a afla maximul salariului mediu pe depatament, vom scrie secventa urmatoare:

SELECT MAX(AVG(sal))

FROM emp

GROUP BY deptno;

Ordinea de evaluare a clauzelor este:

clauza WHERE

clauza GROUP BY

clauza HAVING

Clauza ORDER BY este plasata ultima.

Subinterogarile (subqueries.

O subinterogare este o interogare ale carei rezultate sunt transmise ca argumente unei alte interogari.

Utilizarea unei subinterogari va permite rezolvarea unei probleme, ca de exemplu: 'Cine are salarul mai mare decat salarul lui Bob'. Pentru rezolvarea acestei probleme este nevoie de doua interogari, una pentru a gasi salarul lui Bob si a doua pentru a gasi cine are salarul mai mare decat salarul lui Bob. Putem rezolva aceasta problema combinand doua interogari, plasand o interogare in interiorul alteia. Interogarea din interior numita si subinterogare returneaza o valoare care este utilizata de interogarea din exterior.

Explicat mai simplu, o subinterogare va permite sa transmitem setul de rezultate al unei interogari la alta. Sintaxa generala este urmatoarea:

SELECT coloane

FROM tabela

WHERE expr operator

(SELECT coloane

FROM tabela);

unde:

operator: include operatorii de comparatie.

Operatorii de comparatie sunt de doua tipuri:

operatori care returneaza un singur rand (single-row): >, >=, <, <=, <>.

Operatori care returneaza mai multe randuri (multiple-row): IN, ANY si ALL.

Cateva observatii referitoare la utilizarea subinterogarilor:

O subinterogare trebuie inclusa intre paranteze.

Scrierea unei subinterogari se va face la dreapta operatorului de comparatie.

Nu putem folosi o clauza ORDER BY intr-o subinterogare.

Doua tipuri de operatori de comparatie pot fi utilizati in subinterogari: operatori care returneaza un singur rand (single-row) si operatori care returneaza mai multe randuri (multiple-row).

Exista 3 tipuri de subinterogari:

Subinterogari care returneaza un singur rand (single-row subqueries).

Subinterogari care returneaza mai multe randuri (multiple-row subqueries).

Subinterogari care returneaza mai multe coloane (multiple-column subqueries).

Putem scrie o subinterogare in clauze SQL cum ar fi:

Clauza WHERE

Clauza HAVING

Clauza FROM

În continuare, vom exemplifica prin cateva exemple aceste tipuri de subinterogari.

1. Subinterogari care returneaza un singur rand (single-row subqueries).

Ø     Sa se afiseze toti angajati care au aceeasi functie (job) ca si angajatul cu numele POP.

SELECT ename, job

FROM emp

WHERE job =

(SELECT job

FROM emp

WHERE ename ='POP');

Ø     Sa se afiseze departamentele (deptno) care au salarul minim pe departament mai mare decat salarul minim pe departamentul 20.

SELECT deptno, MIN(sal)

FROM emp

GROUP BY deptno

HAVING MIN(sal) >

(SELECT MIN(sal)

FROM emp

WHERE deptno =20);

Subinterogari care returneaza mai multe randuri (multiple-row subqueries).

Acestea utilizeaza operatorii multiple-row: IN, ANY si ALL

IN - verifica daca valoarea exista in lista rezultata din subinterogare.

ANY - verifica daca conditia este indeplinita pentru cel putin una din elementele listei rezultate din subinterogare.

ALL - verifica daca conditia este indeplinita pentru toate elementele listei rezultate din subinterogare.