Comenzi pentru crearea bazei de date si a tabelelor - SQL

Pentru definirea unei baze de date se pot parcurge doua etape:

o etapa pregatitoare in care se realizeaza crearea unei partitii, precum si a modului de alocare a spatiului fizic in care vor fi memorate datele. Aceasta etapa este parcursa de obicei de administratorul bazei de date;

o etapa pentru crearea bazei de date in care se realizeaza definirea tabelelor, a indecsilor, a viziunilor.

Comanda CREATE DATABASE nume_baza_de_date permite crearea unei baze de date. Aceasta comanda este folosita uzual de administratorul bazei de date.

Atunci cand se creaza o baza de date este necesar sa se tina seama de separarea structurii de tabele astfel incat fiecare tabel sa contina o cheie primara si o cheie externa.

Cheia primara trebuie sa indeplineasca urmatoarele cerinte:

fiecare inregistrare sa fie unica in tabel;

toate coloanele dintr-un tabel sa fie unice.

Comanda CREATE TABLE nume_tabel (atribute) permite realizarea definirii structurii de date a unei tabele, conform atributelor specificate. Pentru fiecare atribut se poate specifica un nume, un tip, lungimea, restrictiile de integritate.

Exemplu: Sa se creeze tabela PROD (din cadrul unei baze de date numita FIRMA privind activitatea de desfacere), cu urmatoarele caracteristici (atribute):

COD_P reprezinta codul produsului si are tipul “numeric”, lungimea fiind de 4 bytes si care nu poate lua valoarea NULL

DENP reprezinta denumirea produsului si are tipul “caracter”, lungimea fiind de 15 bytes.

UMP reprezinta unitatea de masura pentru produsul respectiv si are tipul “caracter”, lungimea fiind de 3 bytes.

Vom scrie:

CREATE DATABASE firma;

CREATE TABLE prod ( cod_p NUMBER(4) NOT NULL, denp CHAR(15),

ump CHAR(3));

Instructiunea ALTER TABLE actualizeaza structura de date a unei tabele, prin adaugarea unor atribute noi (se va folosi clauza ADD) sau modificarea unor atribute existente deja (clauza MODIFY).

Exemplu: Sa se adauge la tabela PROD un nou camp numit PU – ceea ce semnifica pretul unitar.

ALTER TABLE prod

ADD pu NUMBER(9):

Aceasta comanda poate fi utilizata numai pentru adaugarea sau modificarea coloanelor unui tabel. Daca doriti eliminarea unor coloane, va trebui sa creati un nou tabel cu structura dorita si apoi sa adaugati inregistrarile din vechiul tabel.

Comanda DROP TABLE nume_tabela va sterge o tabela cu numele specificat din baza de date curenta. Se vor sterge automat indecsii si restrictiile de integritate aferente, dar vor ramane viziunile referitoare la tabele.

Mai exista o comanda de distrugere a unei baze de date DROP DATABASE, dar care nu este acceptata de toate sistemele de administrare a bazelor de date.

Comenzi pentru actualizare a datelor dintr-o tabela

Datele dintr-o tabela pot fi actualizate in toate din cele 3 moduri cunoscute:

adaugare, modificare, stergere de tupluri.

Comanda INSERT INTO nume_tabel (atribute) VALUES (valori) adauga o inregistrare la sfarsitul tabelei precizate. In comanda se vor specifica campurile (atributele) intr-o anumita ordine, precum si valorile aferente acestora in aceeasi ordine.

Daca nu se specifica atributele, se iau in considerare toate in ordinea de la crearea tabelei. Daca se specifica numai o parte din campuri, cu valorile lor aferente, atunci celelalte atribute vor avea valori nule.

Exemplu: Sa se adauge o inregistrare noua in tabela PROD cu valorile:

COD_P sa ia valoarea” 22”;

DENP sa ia valoarea “IBM686”;

UMP sa ia valoarea “bucati”.

Vom scrie:

INSERT INTO prod VALUES (22, “IBM686”, “bucati”);

Comanda DELETE FROM nume_tabel [WHERE conditii] sterge una sau mai multe inregistrari dintr-o tabela specificata. Daca nu e specificata clauza WHERE, se sterg toate inregistrarile din tabela.

Exemplu: Sa se stearga din tabela PROD toate inregistrarile care au unitatea de masura “bucati”.

DELETE FROM prod WHERE ump “bucati”;

Comanda UPDATE nume_tabel SET atribut1 = valoare1; … ; WHERE conditie modifica una sau mai multe inregistrari dintr-o tabela conform clauzelor specificate.

Daca se doreste modificarea doar a anumitor inregistrari care indeplinesc o anumita conditie, atunci se foloseste clauza WHERE. Vechile valori a atributelor specificate sunt inlocuite prin noile valori care apar in clauza SET.

Daca omiteti clauza WHERE, toate inregistrarile vor fi actualizate cu valoarea data.

Exemplu: Sa se modifice pentru toate inregistrarile care au COD_P cuprins intre 2 si 22, urmatorul camp: DENP = “calculator”.

UPDATE prod SET denp = “calculator” WHERE (cod_p>2 AND cod_p<22);

PROBLEME REZOLVATE

1.     Fie tabela DEPOZIT:

a.     Sa se extraga toate informatiile referitoare la tabela de mai sus.

b.     Sa se extraga doar numele produselor aflate in depozit (fara ca acestea sa se repete).

Rezolvare:

a. Vom folosi instructiunea:

SELECT * FROM depozit;

PROIECT: Ce este CSS ? PROIECT: Wamp Server

b.     Vom folosi instructiunea:

SELECT DISTINCT produs FROM depozit;

2. a. Sa se creeze tabela STUDENTI ce va contine rezultatele obtinute in cadrul unui an de niste studenti, asa cum este prezentata mai jos:

b. Sa se returneze numarul de studenti care au obtinut nota 10 la Informatica.

c. Concatenati campurile “Nume” si “Prenume” pentru a obtine un singur camp “Numele si prenumele studentului”.

d. Afisati valorile campului “Numele si prenumele studentului” cu majuscule.

Rezolvare :

a.     Comanda este:

CREATE TABLE studenti (nume CHAR(10), prenume CHAR(15), statistica NUMBER(3), informatica NUMBER(3), analiza NUMBER(3));

b.     Se va folosi instructiunea:

SELECT COUNT (Nume) Nota_10

FROM studenti

WHERE informatica = 10;

Nota_10 reprezinta un alias.

c.      Instructiunea va fi:

SELECT CONCAT (nume, prenume) “Numele si prenumele studentului”

FROM studenti;

d.     SELECT UPPER (Numele si prenumele studentului)

FROM studenti;

3. Fie tabela FACTURA1 ce contine campurile: SIMBOLUL, CANTITATEA SI PRETUL, iar tabela FACTURA 2 ce contine campurile SIMBOLUL, DENUMIREA SI UNITATEA DE MASURA relative la un produs.

a.     Sa se listeze simbolul, cantitatea si pretul din tabela FACTURA1 si din tabela FACTURA2 denumirea si unitatea de masura.

b.     Sa se ordoneze tabela FACTURA2 dupa campul DEN.

Rezolvare :

a.     Fie tabela FACTURA1:

FACTURA2:

Se observa cum campul SIMBOL este comun celor doua tabele.

SELECT F.SIMBOL, F.CANT, F.PRET, D.DEN, D.UM

FROM FACTURA1 F, FACTURA2 D

WHERE F.SIMBOL = D.SIMBOL;

b.     SELECT * FROM FACTURA2

ORDER BY DEN;

4.     Folosind tabela STUDENTI, sa se listeze toti studentii care au aceeasi nota la disciplina INFORMATICA cu cel care are numele ISPAS.

Rezolvare : Vom scrie:

SELECT nume, prenume, informatica

FROM studenti

WHERE informatica = (SELECT informatica

FROM studenti

WHERE nume = “Ispas”)

5.     Scrieti o interogare care sa returneze studentii al caror nume de familie incepe cu litera “I

Rezolvare : Instructiunea este:

SELECT * FROM studenti WHERE nume LIKE “I%”;

Simbolul % folosit intr-o expresie LIKE are rol de caracter de inlocuire.

6.     Presupunem ca avem o lista cu numele a studentilor ce participa la tenis, si o alta lista a studentilor ce participa la volei. Sa se decida care studenti participa la ambele activitati.

Rezolvare : Vom folosi operatorul INTERSECT, deoarece acesta returneaza liniile gasite de ambele interogari.

SELECT * FROM tenis

INTERSECT

SELECT * FROM volei;

7.     Folosind tabelul DEPOZIT de la exercitiul 1, scrieti o interogare care sa returneze urmatorul rezultat:

Rezolvare SELECT produs, pret unitar

FROM depozit

WHERE produs = “Portocale” AND pret_unitar 12000;

PROBLEME PROPUSE

1.     Creati o tabela care sa contina informatii referitoare la numele, prenumele si telefonul unui angajat, iar apoi sa se scrie o interogare care sa returneze numele angajatilor.

2.     Fie tabela PERSOANA ce contine inregistrarile:

a.     Se cere selectarea din tabela a angajatilor ce au functiile de “ec.” sau “ing.”, cu o vechime de 4 ani;

b.     Sa se listeze economistii care  au salariul mai mare sau egal cu 2.000.000;

c.      Sa se calculeze totalul salariilor din tabela PERSOANA.

3.     Folosind tabela de la exercitiul precedent, sa se listeze:

a.     Inregistrarile tabelei PERSOANA in ordinea crescatoare a campului MARCA, iar apoi in ordinea descrescatoare a campului NUME;

b.     Salariul minim, maxim si mediu pentru fiecare functie;

c.      Diferenta dintre salariul minim si salariul maxim.

4.     Care din urmatorii operatori sunt operatori pentru multimi?

1. UNION, WHERE, SELECT, FROM;
2. INTERSECT, MINUS, LIKE, IN;
3. OR, IN, BETWEEN, AND, NOT;
4. UNION, INTERSECT, MINUS, IN, BETWEEN.

5.     Care functie calculeaza media aritmetica?

1. COUNT;
2. AVG;
3. VARIANCE;
4. STDDEV.

6.     Care functie returneaza radacina patrata dintr-un numar?

1. POWER;
2. SIGN;
3. SQRT;
4. EXP.

7.     Functia ABS:

1. executa o operatie trigonometrica;
2. returneaza cel mai mic numar intreg care este mai mare sau egal cu un argument;
3. returneaza cel mai mare numar intreg care este mai mic sau egal cu un argument;
4. returneaza valoarea absoluta a unui numar.

8.     Functiile de tip caracter sunt:

1. CHR, CONCAT, INITCAP, LPAD, LTRIM, REPLACE, SUBST;
2. CEIL, CONCAT, REPLACE, FLOOR, RPAD, LOWER;
3. UPPER, GREATEST, LEAST, TO_CHAR, LENGTH, SQRT;
4. INITCAP, SYSDATE, LPAD, RPAD, LOWER, UPPER.

9.     Care instructiune SELECT este corecta:

1. SELECT produs ORDER BY nume;
2. SELECT produs, nume ORDER BY nume;
3. SELECT nume FROM produs;
4. SELECT * GROUP BY nume.

10.  Instructiunea ALTER TABLE este folosita pentru:

1. eliminarea unor coloane dintr-un tabel;
2. crearea unui tabel;
3. adaugarea sau modificarea coloanelor dintr-un tabel;
4. stergerea unui tabel impreuna cu toti indecsii corespunzatori.

11.  Care instructiune INSERT este corecta:

1. INSERT inventar (pret, produs, observatii)

VALUES (2000, “calculator”,);

2. INSERT inventar (pret, produs, observatii)

VALUES (2000, “calculator”, ”ultimul model”);

3. INSERT INTO inventar (pret, produs, observatii)

VALUES (2000, “calculator”,);

4. INSERT INTO (pret, produs, observatii)

VALUES (2000, “calculator”,).

12.  Care instructiune este corecta:

1. DELETE firma;
2. INSERT INTO firma;
3. UPDATE firma (“produs”, 25000, ”kg”)
4. UPDATE firma

SET valoare = 25000.

13.  Pentru eliminarea unui intreg tabel, se foloseste instructiunea:

1. DROP TABLE nume_tabel;
2. DELETE nume_tabel;
3. DELETE *;
4. ALTER TABLE nume_tabel.