Baze de Date - lucrarea de laborator - SQL* Plus

Baze de Date - Lucrarea de laborator

Notiuni introductive privind Sistemele de Gestiune a Bazelor de Date si limbajul SQL

Utilitatea oricarei colectii de date in obtinerea de informatii depinde in mare masura de modul de organizare a acestor date. Regulile dupa care sunt organizate si manipulate datele depind de modelul de date utilizat. Exista mai multe modele de date utilizate in proiectarea Sistemelor de Gestiune a Bazelor de Date (SGBD, traducerea termenului Database Management System-DBMS): modelul ierarhic, modelul retea, modelul relational, modelul obiect-orientat, modelul obiect-relational. Dintre acestea, modelul relational este cel mai larg raspandit, in special in aplicatii comerciale.

Modelul relational si limbajul SQL

Modelul de date relational sta la baza majoritatii SGBD-urilor comerciale care exista si apar la ora actuala. Popularitatea crescanda a modelului relational si raspandirea tot mai larga a bazelor de date relationale se datoreaza robustetii acestui model precum si faptului ca dispune de limbaje pentru descriere si manipularea datelor de nivel inalt, simple, dar foarte puternice. Caracteristica principala a acestor limbaje, numite generic limbaje relationale, o reprezinta capacitatea lor de a permite definirea de relatii noi pe baza unor relatii existente.

Modelul relational implica:

Structurarea datelor sub forma de relatii, care sunt colectii (multimi) de tupluri, fiecare tuplu fiind la randul sau o multime de valori ale atributelor relatiei.

O multime de operatori care actioneza asupra relatiilor pentru a produce noi relatii.

O baza de date relationala este perceputa de utilizatorii sai ca o colectie de tabele bidimensionale care sunt usor de inteles. Corespondenta dintre termenii relationali si termenii folositi de utilizatori:

relatie: tabela;

tuplu: rand (linie) dintr-o tabela, inregistrare;

cardinalitate: numarul de linii ale tabelei;

grad: numar de coloane ale tabelei;

atribut: coloana, camp;

cheie primara: identificator unic;

domeniu: multimea de valori ale unui atribut.

Dintre SGBD-urile utilizate la ora actuala, vom studia Oracle si Access.

Orice SGBD necesita un limbaj de interogare pentru a permite utilizatorului sa acceseze datele. SQL (Structured Query Language) este limbajul utilizat de majoritatea sistemelor de baze de date relationale.

Limbajul SQL a fost dezvoltat intr-un prototip de sistem de administrare a bazelor de date relationale - System R -de IBM la mijlocul anilor 1970. In 1979, Corporatia Oracle introduce prima implementare a SQL in varianta comerciala. Caracteristicile acestui limbaj sunt:

SQL este prezentat in limba engleza. Foloseste cuvinte (ca select, insert, delete) ca parti ale setului de comenzi.

SQL este un limbaj neprocedural: specifica ce informatii se doresc, nu modul in care cum se obtin. Cu alte cuvinte SQL nu cere sa se specifice metoda de acces la date. Toate cererile SQL folosesc optimizarea cererilor, executate de catre SGBD pentru a obtine rapid datele solicitate.

SQL pune la dispozitie comenzi pentru o varietate de operatii incluzand:

inserarea, extragerea si stergerea rindurilor intr-o tabela;

crearea, modificarea si stergerea tabelelor;

controlul accesului la baza de date;

garantarea consistentei bazei de date;

Comenzile SQL opereaza asupra tabelelor si, in cea mai mare parte a situatiilor, rezultatul returnat este tot o tabela.

SQL a fost definit ca limbaj standard pentru bazele de date relationale de Institutul National American de Standarde (ANSI) in anul 1986. Organizatia Internationala de Standarde (ISO) a adoptat de asemenea SQL ca limbaj standard pentru SGBD-uri, sub denumirea de SQL-92 (sau, mai simplu, SQL2). Toate SGBD-urile suporta diferite variante (dialecte) de SQL si toti producatorii de SGBD trebuie sa se alinieze la standardul ISO, pentru a asigura vanzarea produselor lor.

Limbajul SQL inglobeaza atat componenta de descriere a datelor relationale (LDD - Limbaj de Descriere a Datelor, traducere de la DDL- Data Description Language) cat si componenta de manipulare a datelor (LMD - Limbaj de Manipulare a Datelor, traducere de la DML - Data Manipulation Language), componentele teoretice necesare in limbajele relationale.

2. SGBD Oracle

Oracle este un sistem de gestiune a bazelor de date si contine un set complet de constructori de aplicatii si produse pentru utilizatori, cautand sa asigure solutii complete in tehnologia informatiei. Aplicatiile Oracle sunt portabile pe un numar mare de statii de lucru si sisteme de operare, de la calculatoare personale la multiprocesoare. Oracle este inzestrat cu un flexibil Sistem de Gestiune al Bazei de Date (SGBD)- Serverul Oracle- care administreaza stocarea si regasirea informatilor utilizate in aplicatii.

Serverul ORACLE 7, pe care se vor efectua lucrarile de laborator, administreaza o baza de date cu toate avantajele unei structuri relationale, si controleaza:

Stocarea de date in sfera bazelor de date dedicate.

Recuperarea de date pentru aplicatii utilizand tehnici de optimizare adecvate.

Securitatea bazelor de date si a taskurilor permise pentru anumiti utilizatori.

Consistenta si protectia datelor, incluzand arhivarea taskurilor si mecanisme de cautare.

Comunicarea si integritatea informatiilor,cand bazele de date sunt distribuite intr-o retea.

Aplicatiile Oracle pot fi rulate pe acelasi computer ca si Serverul Oracle sau pe un sistem local pentru utilizator (sistemul 'client'), in timp ce Oracle SGBD (serverul) ruleaza pe un altul (sistemul 'server').

Limbajul SQL poate fi utilizat de fiecare instrument Oracle, pentru accesul la baza de date.

PL/SQL este limbajul procedural al lui Oracle pentru scrierea aplicatiilor si pentru manipularea datelor in afara bazei de date. PL/SQL poate include un subset al comenzilor SQL, cand accesul la baza de date este cerut.

SQL*Plus este un produs Oracle in care pot fi utilizate limbajele SQL si PL/SQL. De asemenea are propriul limbaj de comanda pentru controlul comportarii produsului si pentru formatarea rezultatelor interogarilor SQL. Din SQL* Plus se pot trimite comenzi si obtine rezultate de la Serverul Oracle.

In continuare sunt prezentate comenzile SQL cele mai frecvent utilizate in SQL* Plus:

SELECT este comanda folosita pentru adresarea de cereri (interogari - query) bazelor de date

INSERT aceste comenzi sunt utilizate pentru introducerea de linii noi,

UPDATE pentru modificarea liniilor existente,

DELETE sau pentru stergerea unor linii din tabelele bazei de date.

Lucrare: Aplicatie – pagina web „burse socrates” Referat: Structuri de control in PHP

CREATE aceste comenzi sunt utilizate pentru a crea,

ALTER pentru a modifica,

DROP sau pentru a sterge obiecte ale bazei de date (tabele, indecsi, etc)

Pentru scrierea comenzilor SQL in SQL* Plus trebuie sa fie respectate urmatoarele reguli:

Comenzile SQL pot fi pe una sau mai multe linii.

Clauzele sunt uzual plasate pe linii separate.

Cuvintele de comanda nu pot fi separate pe mai multe linii.

Comenzile SQL nu sunt 'case sensitive'.

Comanda SQL este introdusa la promptul SQL si liniile subsecventelor sunt numerotate.

O comanda SQL este considerata curenta cat timp ea este in buffer si poate fi rulata fie plasand un punct si virgula (;) la sfarsitul ultimei clauze, fie apelul: R[UN] [comanda SQL] la promptul SQL.

2.1 Comanda SELECT

Blocul de interogare de baza (cerere) il reprezinta declaratia (comanda) SELECT. Aceasta regaseste informatile dorite din tabelele bazei de date, implementand toti operatorii din algebra relationala. In cea mai simpla forma, comanda SELECT trebuie sa contina:

Clauza SELECT, care specifica coloanele ce vor fi afisate.

Clauza FROM care specifica tabela (sau tabelele) din care sunt extrase datele.

Este posibil sa selectam toate coloanele din tabela prin specificarea unui asterix (*) dupa cuvantul SELECT. De exemplu, daca dorim sa selectam toate coloanele din tabela EMP:

SELECT *

FROM EMP;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

----- ------- ------- ---- --------- --------- ------- ------

7369 SMITH CLERK 7902 13-JUL-83 800.00 20

7499 ALLEN SALESMAN 7698 15-AUG-83 1,600.00 300.00 30

7521 WARD SALESMAN 7698 26-MAR-84 1,250.00 500.00 30

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

De mentionat faptul ca Oracle pune la dispozitie cateva tabele deja create: EMP (Employee - Angajati), DEPT (Deparments - Departamente), care pot fi folosite pentru a studia comenzile SQL.

Pentru a lista toate numerele departamentelor, numele angajatilor si numarul managerilor in tabela EMP, introduceti urmatoarele comenzi:

SELECT DEPTNO , ENAME , MGR

FROM EMP;

Exercitii:

1. Selectati toate informatiile din tabela SALGRADE.

2. Selectati toate informatiile din tabela DEPT.

2.1.1 Clauza DISTINCT

Atunci cand prin comanda SELECT se selecteza numai o parte din coloanele unei tabele, operatia de algebra relationala executata este o operatie de proiectie (projection), in urma careia este posibil sa se obtina linii identice (cu valori duplicat). Pentru eliminarea valorilor duplicate din rezultat, se poate include clauza DISTINCT in comanda SELECT. Se poate urmari efectul clauzei DISTINCT comparaind rezultatele afisate de comenzile:

SELECT DEPTNO FROM EMP;

SELECT DISTINCT DEPTNO FROM EMP;

2.1.2 Clauza ORDER BY

In mod normal, ordinea randurilor obtinute ca rezultat al unei cereri este nedefinita. Clauza ORDER BY poate fi utilizata pentru a sorta randurile. Clauza ORDEY BY trebuie sa fie intotdeauna ultima in declaratia SELECT. Pentru a sorta dupa coloana ENAME, se introduce:

SELECT ENAME,JOB,SAL*12,DEPTNO

FROM EMP

ORDER BY ENAME;

ENAME JOB SAL*12 DEPTNO

--------- ------- -------- ------

ADAMS CLERK 13200 20

ALLEN SALESMAN 19200 30

. . . . . . . . . . . . . . . . . . . .

Ordinea implicita a sortarii este ascendenta:

Valorile numerice cele mai mici primele.

Valorile de tip date calendaristice cele mai mici primele.

Valorile de tip caracter in ordinea alfabetica.

Pentru a inversa aceasta ordine, se specifica optiunea DESC dupa numele coloanei in clauza ORDER BY. Pentru a inversa ordinea coloanei HIREDATE, se introduce:

SELECT ENAME,JOB,HIREDATE

FROM EMP

ORDER BY HIREDATE DESC;

ENAME JOB HIREDATE

--------- --------- ---------

JAMES CLERK 23-JUL-84

KING PRESIDENT 09-JUL-84

BLAKE MANAGER 11-JUN-84

------ --------- ---------

MILLER CLERK 21-NOV-83

JONES MANAGER 31-OCT-83

ALLEN SALESMAN 15-AUG-83

SMITH CLERK 13-JUN-83

Exercitii

Listati numerele de departament si numele angajatilor in ordinea numelor departamentelor.

2.1.3 Clauza WHERE

Clauza WHERE contine o conditie pe care trebuie sa o indeplineasca orice linie pentru a putea fi selectata si afisata. Clauza WHERE, daca este folosita, trebuie sa urmeze clauzei FROM:

SELECT coloane

FROM tabela

WHERE conditie de cautare

Clauza WHERE poate compara valori in coloana, valori literale, expresii aritmetice sau functii. In forma cea mai simpla, clauza WHERE asteapta trei elemente:

un nume de coloana

un operator de comparatie

un nume de coloana, constanta sau lista de valori.

Operatorii de comparatie utilizati in clauza WHERE pot fi impartiti in doua categorii: operatori aritmetici de comparatie si operatori SQL de comparatie.

Operatorii aritmetici de comparatie

Operatorii aritmetici folositi sunt: = , > , >= , < , <= .

Coloanele dintr-o tabela pot contine urmatoarele tipuri de date: caracter, numar sau data calendaristica. Sirurile de caractere si datele calendaristice din clauza WHERE trebuie sa fie inchise in ghilimele simple ('). Pentru a afisa numele, numerele, ocupatia si departamentele tuturor functionarilor (CLERK) din tabela EMP, introduceti:

SELECT ENAME, EMPNO, JOB, DEPTNO

FROM EMP

WHERE JOB = 'CLERK';

ENAME EMPNO JOB DEPTNO

--------- ----- ----- ------

SMITH 7369 CLERK 20

ADAMS 7876 CLERK 20

JAMES 7900 CLERK 30

MILLER 7934 CLERK 10

Pentru a gasi toate numele de departamente cu numarul de departament mai mare ca 20, introduceti:

SELECT DNAME, DEPTNO

FROM DEPT

WHERE DEPTNO >20;

DNAME DEPTNO

---------- ------

SALES 30

OPERATIONS 40

Operatori SQL de comparatie

Operator Semnificatie

-------- ------------

BETWEEN..AND valoarea atributului este cuprinsa intre doua valori date (inclusiv)

IN(list) compara valoarea atributului cu o lista de valori

LIKE compara valoarea atributului cu un model de tip caracter

IS NULL compara valoarea atributului cu valoarea NULL

Operatorul BETWEEN realizeaza teste pentru valori intre o valoare minima si o valoare maxima. De notat ca valorile specificate sunt inclusive si ca limita minima trebuie specificata prima. Presupunand ca dorim sa vedem angajatii al caror salariu este intre 1000 si 2000, vom introducecomanda

SELECT ENAME, SAL

FROM EMP

WHERE SAL BETWEEN 1000 AND 2000;

ENAME SAL

------- ----------

ALLEN 1,600.00

WARD 1,250.00

MARTIN 1,250.00

TURNER 1,500.00

ADAMS 1,100.00

MILLER 1,300.00

Operatorul IN testeaza valorile atributului cu cele dintr-o lista specificata. Presupunem ca dorim sa gasim angajatii care au unul din cele trei numere de marca (MGR):

SELECT EMPNO, ENAME, SAL, MGR

FROM EMP

WHERE MGR IN (7902,7566,7788);

EMPNO ENAME SAL MGR

----- ----- --------- -----

7369 SMITH 800.00 7902

7788 SCOTT 3,000.00 7566

7876 ADAMS 1,100.00 7788

7902 FORD 3,000.00 7566

Operatorul LIKE. Uneori nu se cunosc valorile exacte pe care le cautam. Utilizand operatorul LIKE este posibil sa selectam randurile care se potrivesc cu un model specificat de caractere. Pentru construirea modelului sirului de cautare se pot utiliza doua simboluri: % si -:

Simbol Reprezentare

------ ----- ----- --------

% orice secventa de zero sau mai multe caractere

- un singur caracter oarecare

Pentru a lista toti angajatii al caror nume incepe cu un S, introduceti:

SELECT ENAME

FROM EMP

WHERE ENAME LIKE 'S%';

ENAME

--------

SMITH

SCOTT

Semnele '%' si '_' pot fi utilizate in orice combinatie de caractere.

Operatorul IS NULL face teste specifice pentru valorile care sunt NULL.

SELECT ENAME,MGR

FROM EMP

WHERE MGR IS NULL;

ENAME MGR

----------- ---------

KING

Exercitii

Afisati toti salariatii angajati in anul 1983.

2.1.4 Interogarea cu conditii multiple

In clauza WHERE se poate compune o expresie logica folosind operatorii logici AND, NOT sau OR. De exemplu, pentru a gasi toti functionarii (CLERK) care castiga intre1000 si 2000, se introduce comanda:

SELECT EMPNO,ENAME,JOB,SAL

FROM EMP

WHERE SAL BETWEEN 1000 AND 2000

AND JOB = 'CLERK';

EMPNO ENAME JOB SAL

---- ------- -------- --------

7876 ADAMS CLERK 1,100.00

7934 MILLER CLERK 1,300.00

Se pot construi expresii logice oricat de complicate folosind operatorii AND, NOT si OR. Cand mai multi operatori logici apar in aceeasi clauza WHERE, ordinea de evaluare a expresiei se face pe baza precedentei operatorilor (ordinea de precedenta este: NOT, AND, OR) sau se pot adauga paranteze.

Exercitii

Afisati toti managerii (MANAGER) cu salarii peste 1500 si toti vanzatorii (SALESMAN) din tabela EMP.