Laborator 02 - Introducere în SQL și SQL*Plus

• Comenzi SQL*Plus
• Limbajul SQL
• Comenzi DDL
• Comenzi DML

Resurse BD

Laborator 2

Descriere

• SQL*Plus este un interpretor în linie de comandă (CLI – command line interpreter) care este utilizat pentru scrierea și lansarea către motorul bazei de date Oracle a interogărilor SQL și programelor PL/SQL;
• În mediul Windows, SQL*Plus apare și sub forma de aplicație GUI;
• Pentru a se face o conexiune la baza de date folosindu-se varianta GUI, un utilizator trebuie sa introducă username, password și host string ;
• La instalarea bazei de date se pot instala schemele default care vin cu distribuția, cum ar fi schemele(userii) scott și hr.

Utilizare în consolă

• Pentru a utiliza SQL*Plus în consolă, un utilizator trebuie sa seteze, atât pe Windows cât și pe Linux, variabilele de mediu ORACLE_BASE, ORACLE_HOME, ORACLE_SID și să adauge la variabila de mediu PATH calea ORACLE_HOME/bin, și doar în Linux să adauge la LD_LIBRARY_PATH;

       set ORACLE_BASE=C:\app\oracle
       set Oracle_HOME=%ORACLE_BASE%\product\12.1.0\dbhome_1
       set ORACLE_SID=orcl
       set PATH=%PATH%;%ORACLE_HOME%\bin
 

       export ORACLE_BASE=/u01/app/oracle
       export Oracle_HOME=$ORACLE_BASE/product/12.1.0/dbhome_1
       export ORACLE_SID=orcl
       export PATH=$ORACLE_HOME/bin:$PATH
       export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
 

Conectare
Pentru a vă conecta la SQL*Plus în consolă comanda care se folosește este:

sqlplus [username[/password[@bazadate]]]

• Cu user

• Cu user și parolă

• Cu user, parolă și baza de date

Comenzi
Comenzile SQL*Plus pot fi editate direct în linia de comandă, sau pot fi editate cu orice editor cunoscut; Există un editor simplu, inclus în utilitar, care se accesează tastând edit (ed) în linia de comandă și care recunoaște următoarele comenzi:

Editare comenzi

• Comanda editată rămâne în buffer până la editarea următoarei comenzi;
• O comandă poate fi editată pe mai multe linii tastând ENTER la sfârșitul liniei curente;
• Terminatorul pentru o comandă este *;*

O comandă editată cu un editor se poate executa astfel:
* Se copiază comanda din editor (Copy/Paste) și se execută cu / sau RUN;
* Se execută tot scriptul (care poate conține mai multe comenzi) astfel:
  * SQL> @file_path_and_name
  * SQL> start file_path_and_name

În linia de comandă se mai pot da și următoarele comenzi:
* SAVE file_path_and_name – permite salvarea într-un fișier a conținutului bufferului SQL*Plus;
* GET file_path_and_name – încarcă conținutul unui fișier salvat în prealabil în buffer;
* START file_path_and_name – lansează în execuție comenzile salvate în fișier;
* @file_path_and_name – execută comenzile din fișier;
* EXIT – ieșire din SQL*Plus.

Descriere

• Un sistem de gestionare a bazelor de date necesită un limbaj de interogare pentru a permite utilizatorilor să acceseze datele;
• SQL este un limbaj de interogare structurat care este utilizat de majoritatea bazelor de date relaționale;
• Caracteristici SQL:
  • Utilizează setul standard de comenzi: select, insert, update, delete, etc
  • Este un limbaj neprodedural, care optimizează cererile fără a specifica modul de execuție
  • Cererile se execută secvențial, înregistrare cu înregistrare, deci se prelucrează o singură înregistrare la un moment dat

Comenzi
Comezile SQL sunt conturate ca blocuri de interogare folosite pentru:

• Inserarea, extragerea și ștergerea liniilor dintr-o tabelă
• Crearea, modificarea și ștergerea obiectelor din baza de date
• Controlul acesului la baza de date
• Execuția calculelor
• Specificarea alternativă a capetelor de coloane
• Concatenarea coloanelor
• Sortarea liniilor
• Introducerea criteriilor de căutare

Limbajul SQL are următoarele comenzi, grupate în 4 categorii:

• Data Manipulation Language (DML): SELECT, INSERT, UPDATE, DELETE, MERGE
• Data Definition Language (DDL): CREATE, ALTER, DROP, RENAME, TRUNCATE, COMMENT
• Data Control Language (DCL): GRANT, REVOKE
• Transaction Control Language (TCL): COMMIT, ROLLBACK, SAVEPOINT

Comenzile DML

• SELECT – folosită pentru a extragerea datelor din baza de date
• INSERT – folosită pentru inserarea datelor în baza de date
• UPDATE – folosită pentru modificarea datelor din baza de date
• DELETE – folosită pentru ștergerea înregistrărilor

Comenzile DDL

• CREATE – folosită pentru crearea unui obiect (tabel, view, index, etc.) în baza de date
• ALTER – folosită prentru modificarea structurii unui obiect din baza de date
• DROP – folosită prentru ștergerea unui obiect din baza de date

Comenzile DCL

• GRANT – folosită prentru a grantifica drepturi de acces la un obiect din baza de date
• REVOKE – folosită pentru a revoca drepturile de acces

Reguli de scriere a comenzilor SQL

• Comenzile se pot edita pe una sau mai multe linii;
• Clauzele sunt uzual plasate pe linii separate;
• Cuvintele predefinite nu pot fi separate pe mai multe linii;
• Comenzile nu sunt case sensitive.

Sintaxa

• Pentru a crea o tabelă se folosește comanda DDL CREATE TABLE.
• Sintaxa comenzii (verisunea simplificată; mai multe detalii aici):
  • schema – numele shemei în care se va crea tabelul
  • tablename – numele tabelului
  • column_name_k – numele coloanei k
  • datatype – tipul de date al unei coloane
  • DEFAULT value | expression– specific dacă o coloană va avea o valoare default, dată fie de o valoare fie de o expresie
  • inline_constraints – o coloană poate să aibă constrângeri precum: UNIQUE, PRIMARY KEY, NOT NULL

CREATE TABLE [ schema. ] tablename
(
    column_name_1 datatype [DEFAULT VALUE|expression] [inline_constraints] ,
    [column_name_2 datatype [DEFAULT VALUE|expression] [inline_constraints] , ....]
)

Exemple
Ex. 1. Să se creeze un nomenclator de departamente, o tabelă pentru grila de salarizare și o tabelă pentru evidența angajaților.

Constrângeri

• Constrângerile sunt proprietăți ale unei coloane ce trebuie să fie îndeplinite în orice moment.
• Cele mai comune constrângeri sunt:
  • NOT NULL – valoarea unei coloane nu trebuie să fie NULL
  • UNIQUE – valoarea unei coloane (combinarea de valori ale unor coloane) trebuie să fie unice pentru fiecare înregistrare din tabelă
  • PRIMARY KEY – valoarea unei coloane (combinarea de valori ale unor coloane) trebuie să fie unice pentru fiecare linie din tabelă și să nu fie NULL. Cheia primară este identificatorul unic al unei înregistrări dintr-o tabelă
  • FOREIGN KEY – valoarea unei coloane (combinatia de valori ale unor coloane) fac legătura cu alta tabela prin referirea cheii primare/unice din acea tabela

Ex. 2. Să se adauge chei primare si chei străine pentru tabelele de la Ex. 1. Pentru tabela departamente să se adauge o constrângere unique pentru coloana denumire_departament. Pentru tabela angajati sa se adauge o constrângere unique pentru coloanele nume și prenume.

Observații

1. Se pot crea toate constrângeile de la Ex. 2 în momentul când se creează tabelele (vezi următoarele exemple);
2. Atenție la ordinea în care se creează tabelele, deoarece nu se poate crea o cheie străină într-o tabela atâta timp cât tabela la care se face referință nu este creata;
3. În momentul când se dorește să se șteargă tabelele din baza de date, mai întâi trebuie șterse cele care referă alte tabele apoi tabela care este referita prin cheie străină. Din exemplele noastre, mai întâi trebuie ștearsa tabela angajati (care referă tabela departamente printr-o cheie străină) și apoi tabela departamente;
4. Pentru a vedea structura unei tabele se folosește comanda desc[ribe] nume_tabel, ex: desc angajati.

Sintaxa

• Pentru a insera informația într-o tabelă se va folosi comanda DML INSERT.
• Sintaxa comenzii (versiunea simplificată; mai multe detalii aici):
  • Specific în ce coloane vreau să inserez, trebuie să inserez în cel puțin coloanele care au constrângeri precum PRIMARY KEY și NOT NULL și nu au valori DEFAULT pe acestea

INSERT INTO nume_tabel(column_name1 [, column_name2 [, ...]]) VALUES(value1 [, value2, [....]])

• Nu specific în ce coloane vreau să inserez, trebuie să dau valori pentru toate coloanele, în ordinea în care au fost declarate acestea

INSERT INTO nume_tabel VALUES(value_column1, value_column2, ...)

Exemple
Ex. 3. Să se insereze informații în tabelele create la Ex. 1. (Atenție la ordinea în care se face insertul)

Sintaxa

• Prentru a vizualiza datele dintr-o tabelă se folosește comanda DML SELECT.
• Sintaxa comenzii (versiunea simplificată; mai multe detalii aici)

SELECT [* | {column_name1[, column_name2[,...]]}]
FROM  TABLE_NAME
[WHERE conditions]

Exemple
Ex. 4. Să se afișeze toate informațiile din tabela angajații.

Ex. 5. Să se afișeze numele, funcția și salariul pentru toți angajații.

Ex. 6. Să se afișeze numele, prenumele și salariu pentru angajații din departamentul 10.

Ex. 7. Să se afișeze numele, prenumele și salariu pentru angajații care au funcția ‘Director’.

Sintaxa

• Pentru a face o modificare se folosește comnda DML UPDATE
• Sintaxa comenzii (versiunea simplificată; mai multe detalii aici)

UPDATE TABLE_NAME 
SET column_name1 = new_value1 [, column_name2 = new_value2, [,...]] 
[WHERE conditions]

Exemple
Ex. 8. Să se modifice numele angajatului cu id-ul 1002 în ‘Popescu’. Verificați dacă s-a facut modificarea.

Ex. 9. Să se acorde comision la toți angajații egal cu 10% din salariu. Verificați dacă s-a facut modificarea.

Sintaxa
Pentru a șterge o înregistrare se folosește comanda DML DELETE; Sintaxa comenzii (veriunea simplificată; mai multe detalii aici)

DELETE FROM tablename [WHERE conditions];

Exemple
Ex. 10. Să se șteargă

• Angajatul cu numele ‘Pop’ și prenumele ‘Alina’
• Angajații care au venit în firmă înainte de ‘1-JAN-1985’
• Angajații care au comisionul null

Ex. 1. Să se implementeze o bază de date pentru un Blog folosind următoarea diagramă.

Ex. 2. Să se implementeze o bază de date pentru un rețete culinare folosind următoarea diagramă.

Ex. 3. Să se implementeze o bază de date pentru o flota de masini folosind următoarea diagramă.

Ex. 4. Să se implementeze o bază de date pentru o florărie folosind următoarea diagramă.

Ex. 5. Să se implementeze o bază de date pentru o bibliotecă folosind următoarea diagramă.

Ex. 6. Să se implementeze o bază de date pentru cântece folosind următoarea diagramă.