Differences
This shows you the differences between two versions of the page.
| Both sides previous revision Previous revision Next revision | Previous revision | ||
|
laboratoare:laborator01 [2014/10/13 08:39] Andrei Roșu-Cojocaru |
laboratoare:laborator01 [2014/10/31 04:13] (current) Andrei Roșu-Cojocaru [Soluții] |
||
|---|---|---|---|
| Line 1118: | Line 1118: | ||
| **Exemplu**. În situația în care se dorește afișarea listei de animale pentru care se afișează informații precum denumirea, rasa, sexul, vârsta, numele și prenumele aparținătorului, numele părinților (dacă sunt disponibile), denumirile bolilor de care au suferit, numele și prenumele medicilor care i-au tratat, limitând rezultatele la înregistrările valide (animale cu data nașterii în trecut), s-ar putea folosi următoarea interogare: | **Exemplu**. În situația în care se dorește afișarea listei de animale pentru care se afișează informații precum denumirea, rasa, sexul, vârsta, numele și prenumele aparținătorului, numele părinților (dacă sunt disponibile), denumirile bolilor de care au suferit, numele și prenumele medicilor care i-au tratat, limitând rezultatele la înregistrările valide (animale cu data nașterii în trecut), s-ar putea folosi următoarea interogare: | ||
| - | <code sql> | + | <code mysql> |
| SELECT a.name AS name, | SELECT a.name AS name, | ||
| b.name AS breed, | b.name AS breed, | ||
| Line 1174: | Line 1174: | ||
| Există mai multe tipuri de joncțiuni ce se pot stabili între tabele: | Există mai multe tipuri de joncțiuni ce se pot stabili între tabele: | ||
| - | * **cross-join**, rezultatul fiind produsul cartezian al tuplurilor din cadrul tabelelor implicate în operaţie; fie ''t<sub>1</sub>'' şi ''t<sub>2</sub>'' tabelele pentru care se realizeaza cross-join, având ''m'' şi respectiv ''n'' înregistrări; rezultatul interogării conţine ''m x n'' înregistrări; <code sql> | + | * **cross-join**, rezultatul fiind produsul cartezian al tuplurilor din cadrul tabelelor implicate în operaţie; fie ''t<sub>1</sub>'' şi ''t<sub>2</sub>'' tabelele pentru care se realizeaza cross-join, având ''m'' şi respectiv ''n'' înregistrări; rezultatul interogării conţine ''m x n'' înregistrări; <code mysql> |
| SELECT * FROM animal CROSS JOIN owner; | SELECT * FROM animal CROSS JOIN owner; | ||
| </code> <code> | </code> <code> | ||
| Line 1218: | Line 1218: | ||
| 35 rows in set (0.00 sec) | 35 rows in set (0.00 sec) | ||
| </code> | </code> | ||
| - | * **inner-join**, ce include înregistrările din tabele pentru care există corespondențe în privința atributului pe care se face asocierea; <code sql> | + | * **inner-join**, ce include înregistrările din tabele pentru care există corespondențe în privința atributului pe care se face asocierea; <code mysql> |
| SELECT * FROM animal INNER JOIN owner ON animal.owner_id = owner.id | SELECT * FROM animal INNER JOIN owner ON animal.owner_id = owner.id | ||
| </code> <code> | </code> <code> | ||
| Line 1239: | Line 1239: | ||
| * **outer-join**, care include înregistrările comune unei laturi a legăturii, completând câmpurile care nu au corespondent pe cealaltă latură cu ''NULL''; în acest caz, se pot folosi cuvintele cheie ''LEFT'' | ''RIGHT'' ''JOIN'', specificând partea relaţiei ce va include toate rezultatele. | * **outer-join**, care include înregistrările comune unei laturi a legăturii, completând câmpurile care nu au corespondent pe cealaltă latură cu ''NULL''; în acest caz, se pot folosi cuvintele cheie ''LEFT'' | ''RIGHT'' ''JOIN'', specificând partea relaţiei ce va include toate rezultatele. | ||
| - | * left <code sql> | + | * left <code mysql> |
| SELECT * FROM animal LEFT OUTER JOIN owner ON animal.owner_id = owner.id; | SELECT * FROM animal LEFT OUTER JOIN owner ON animal.owner_id = owner.id; | ||
| </code> <code> | </code> <code> | ||
| Line 1255: | Line 1255: | ||
| 7 rows in set (0.00 sec) | 7 rows in set (0.00 sec) | ||
| </code>\\ Sunt incluse toate înregistrările din tabela ''animal'', pentru cele care nu au corespondent în tabela ''owner'' completându-se atributele respective cu valori ''NULL''. | </code>\\ Sunt incluse toate înregistrările din tabela ''animal'', pentru cele care nu au corespondent în tabela ''owner'' completându-se atributele respective cu valori ''NULL''. | ||
| - | * right <code sql> | + | * right <code mysql> |
| SELECT * FROM animal RIGHT OUTER JOIN owner ON animal.owner_id = owner.id; | SELECT * FROM animal RIGHT OUTER JOIN owner ON animal.owner_id = owner.id; | ||
| </code> <code> | </code> <code> | ||
| Line 1279: | Line 1279: | ||
| Tipuri particulare de asocieri sunt: | Tipuri particulare de asocieri sunt: | ||
| - | * **self-join**, care implică duplicarea tabelei prin utilizarea de alias-uri; <code sql> | + | * **self-join**, care implică duplicarea tabelei prin utilizarea de alias-uri; <code mysql> |
| SELECT * FROM animal a1 JOIN animal a2 ON a1.father_id = a2.id OR a1.mother_id = a2.id; | SELECT * FROM animal a1 JOIN animal a2 ON a1.father_id = a2.id OR a1.mother_id = a2.id; | ||
| </code> <code> | </code> <code> | ||
| Line 1289: | Line 1289: | ||
| +----+-------+----------+--------+---------------------+------------+----------+-----------+-----------+----+-------+----------+--------+---------------------+------------+----------+-----------+-----------+ | +----+-------+----------+--------+---------------------+------------+----------+-----------+-----------+----+-------+----------+--------+---------------------+------------+----------+-----------+-----------+ | ||
| </code> | </code> | ||
| - | * **unions** care presupune adăugarea tuturor înregistrărilor din tabele pentru a determina suma compozită a acestora (numărul de atribute întoarse și denumirea acestora trebuie să fie aceeași pentru a se permite realizarea acestei operații) <code sql> | + | * **unions** care presupune adăugarea tuturor înregistrărilor din tabele pentru a determina suma compozită a acestora (numărul de atribute întoarse și denumirea acestora trebuie să fie aceeași pentru a se permite realizarea acestei operații) <code mysql> |
| SELECT first_name, last_name, email, phone_number, 'Pet Owner' AS role FROM owner | SELECT first_name, last_name, email, phone_number, 'Pet Owner' AS role FROM owner | ||
| UNION | UNION | ||
| Line 1329: | Line 1329: | ||
| **Exemplu**. Determinarea animalului de companie cu vârsta cea mai mare aflat în evidența clinicii veterinare poate fi realizată prin instrucțiunea: | **Exemplu**. Determinarea animalului de companie cu vârsta cea mai mare aflat în evidența clinicii veterinare poate fi realizată prin instrucțiunea: | ||
| - | <code sql> | + | <code mysql> |
| SELECT a.name AS name, | SELECT a.name AS name, | ||
| YEAR(CURRENT_TIMESTAMP) - YEAR(a.birth_date) - (MONTH(CURRENT_TIMESTAMP) < MONTH(a.birth_date) OR (MONTH(CURRENT_TIMESTAMP) = MONTH(a.birth_date) AND DAY(CURRENT_TIMESTAMP) < DAY(a.birth_date))) AS age | YEAR(CURRENT_TIMESTAMP) - YEAR(a.birth_date) - (MONTH(CURRENT_TIMESTAMP) < MONTH(a.birth_date) OR (MONTH(CURRENT_TIMESTAMP) = MONTH(a.birth_date) AND DAY(CURRENT_TIMESTAMP) < DAY(a.birth_date))) AS age | ||
| Line 1349: | Line 1349: | ||
| **Exemplu**. Determinarea animalului de companie cu vârsta cea mai mare aflat în evidența clinicii veterinare poate fi realizată si prin instrucțiunea: | **Exemplu**. Determinarea animalului de companie cu vârsta cea mai mare aflat în evidența clinicii veterinare poate fi realizată si prin instrucțiunea: | ||
| - | <code sql> | + | <code mysql> |
| SELECT a.name AS name, | SELECT a.name AS name, | ||
| YEAR(CURRENT_TIMESTAMP) - YEAR(a.birth_date) - (MONTH(CURRENT_TIMESTAMP) < MONTH(a.birth_date) OR (MONTH(CURRENT_TIMESTAMP) = MONTH(a.birth_date) AND DAY(CURRENT_TIMESTAMP) < DAY(a.birth_date))) AS age | YEAR(CURRENT_TIMESTAMP) - YEAR(a.birth_date) - (MONTH(CURRENT_TIMESTAMP) < MONTH(a.birth_date) OR (MONTH(CURRENT_TIMESTAMP) = MONTH(a.birth_date) AND DAY(CURRENT_TIMESTAMP) < DAY(a.birth_date))) AS age | ||
| Line 1366: | Line 1366: | ||
| **Exemplu**. Determinarea animalului de companie pentru care au existat consultații în cadrul clinicii veterinare poate fi realizată si prin instrucțiunea: | **Exemplu**. Determinarea animalului de companie pentru care au existat consultații în cadrul clinicii veterinare poate fi realizată si prin instrucțiunea: | ||
| - | <code sql> | + | <code mysql> |
| SELECT a.name AS name, | SELECT a.name AS name, | ||
| b.name AS breed | b.name AS breed | ||
| Line 1385: | Line 1385: | ||
| **Exemplu**. Determinarea speciei cu cea mai mare vârstă medie dintre cele aflate în evidența clinicii veterinare poate fi realizată prin instrucțiunea: | **Exemplu**. Determinarea speciei cu cea mai mare vârstă medie dintre cele aflate în evidența clinicii veterinare poate fi realizată prin instrucțiunea: | ||
| - | <code sql> | + | <code mysql> |
| SELECT b.name, | SELECT b.name, | ||
| MAX(average_age) | MAX(average_age) | ||
| Line 1414: | Line 1414: | ||
| **Exemplu**. Determinarea numărului de consultații de care a beneficiat fiecare animal de companie în parte în cadrul clinicii veterinare poate fi realizat prin instrucțiunea: | **Exemplu**. Determinarea numărului de consultații de care a beneficiat fiecare animal de companie în parte în cadrul clinicii veterinare poate fi realizat prin instrucțiunea: | ||
| - | <code sql> | + | <code mysql> |
| SELECT a.name AS name, | SELECT a.name AS name, | ||
| (SELECT COUNT(*) FROM medical_record mr WHERE mr.animal_id = a.id) AS number_of_examinations | (SELECT COUNT(*) FROM medical_record mr WHERE mr.animal_id = a.id) AS number_of_examinations | ||
| Line 1500: | Line 1500: | ||
| **Exemple**. Determinarea numărului de boli de care a suferit, respectiv a numărului de consultații de care a beneficiat fiecare animal de companie în parte în cadrul clinicii veterinare poate fi realizat prin intermediul unei proceduri, respectiv a unei funcții stocate: | **Exemple**. Determinarea numărului de boli de care a suferit, respectiv a numărului de consultații de care a beneficiat fiecare animal de companie în parte în cadrul clinicii veterinare poate fi realizat prin intermediul unei proceduri, respectiv a unei funcții stocate: | ||
| - | <code sql> | + | <code mysql> |
| DELIMITER // | DELIMITER // | ||
| CREATE PROCEDURE get_number_of_diseases( | CREATE PROCEDURE get_number_of_diseases( | ||
| Line 1516: | Line 1516: | ||
| </code> | </code> | ||
| - | <code sql> | + | <code mysql> |
| DELIMITER // | DELIMITER // | ||
| CREATE FUNCTION get_number_of_examinations(animal_id INT) | CREATE FUNCTION get_number_of_examinations(animal_id INT) | ||
| Line 1636: | Line 1636: | ||
| Când vizualizarea referă alte vizualizări, cuvintele-cheie ''LOCAL'' şi ''CASCADED'' (implicit) indică nivelul până la care sunt realizate verificările: doar la nivelul vizualizării definite în mod curent sau şi pentru vizualizările imbricate. | Când vizualizarea referă alte vizualizări, cuvintele-cheie ''LOCAL'' şi ''CASCADED'' (implicit) indică nivelul până la care sunt realizate verificările: doar la nivelul vizualizării definite în mod curent sau şi pentru vizualizările imbricate. | ||
| - | <code sql> | + | <code mysql> |
| CREATE VIEW pets_per_owner AS | CREATE VIEW pets_per_owner AS | ||
| SELECT CONCAT(o.first_name,' ', o.last_name) AS name, | SELECT CONCAT(o.first_name,' ', o.last_name) AS name, | ||
| Line 1783: | Line 1783: | ||
| **Exemplu**. Un cursor poate fi folosit pentru a calcula vârsta medie a animalelor de companie diferențiată pe sexe, aplicând corecții asupra datei de naștere acolo unde se detectează valori incorecte: | **Exemplu**. Un cursor poate fi folosit pentru a calcula vârsta medie a animalelor de companie diferențiată pe sexe, aplicând corecții asupra datei de naștere acolo unde se detectează valori incorecte: | ||
| - | <code sql> | + | <code mysql> |
| DELIMITER // | DELIMITER // | ||
| CREATE PROCEDURE average_age_per_gender() | CREATE PROCEDURE average_age_per_gender() | ||
| Line 1951: | Line 1951: | ||
| O editură este caracterizată prin denumire, cod unic de identificare, descriere, localitatea, regiunea şi ţara în care operează. | O editură este caracterizată prin denumire, cod unic de identificare, descriere, localitatea, regiunea şi ţara în care operează. | ||
| - | Un client este identificat prin nume, prenume, adresa, telefon, email. Presupunând că librăria dispune şi de o platformă on-line pentru achiziţii, pentru fiecare client se vor reţine tipul (administrator / client), precum şi rolul (super-administrator, administrator simplu / client inregistrat, client verificat). Cu excepţia numărului de telefon, nici un câmp nu poate fi vid. Pentru email trebuie să se respecte formatul ''username@domain.country''. | + | Un client este identificat prin CNP, nume, prenume, adresa, telefon, email. Presupunând că librăria dispune şi de o platformă on-line pentru achiziţii, pentru fiecare client se vor reţine tipul (administrator / client), precum şi rolul (super-administrator, administrator simplu / client inregistrat, client verificat). Cu excepţia numărului de telefon, nici un câmp nu poate fi vid. Pentru email trebuie să se respecte formatul ''username@domain.country''. |
| - [**25 puncte**] Să se proiecteze schema conceptuală a bazei de date.\\ Studenţii care nu participă la ora de laborator şi trimit rezolvările prin email, vor ataşa schema conceptuală a bazei de date ca fişier .pdf. | - [**25 puncte**] Să se proiecteze schema conceptuală a bazei de date.\\ Studenţii care nu participă la ora de laborator şi trimit rezolvările prin email, vor ataşa schema conceptuală a bazei de date ca fişier .pdf. | ||
| Line 1961: | Line 1961: | ||
| - [**10 puncte**] Să se majoreze cu 20% preţul cărţilor apărute înainte de anul 2010. Ulterior se vor lista toate volumele existente în librărie grupate pe ani de apariţie (în ordine cronologică), pentru fiecare an în parte ordonate descrescător în funcţie de preţ. | - [**10 puncte**] Să se majoreze cu 20% preţul cărţilor apărute înainte de anul 2010. Ulterior se vor lista toate volumele existente în librărie grupate pe ani de apariţie (în ordine cronologică), pentru fiecare an în parte ordonate descrescător în funcţie de preţ. | ||
| - [**10 puncte**] Să se afişeze toate cărţile unui autor, fie că le-a redactat singur, fie că acestea au mai mulţi autori.\\ Să se identifice numărul de volume elaborate, precizându-se numărul celor la care e unic autor şi al celor pe care le-a realizat colaborând cu alţi autori. | - [**10 puncte**] Să se afişeze toate cărţile unui autor, fie că le-a redactat singur, fie că acestea au mai mulţi autori.\\ Să se identifice numărul de volume elaborate, precizându-se numărul celor la care e unic autor şi al celor pe care le-a realizat colaborând cu alţi autori. | ||
| - | - [**10 puncte**] Să se creeze două rutine stocate pe server (o procedură si o funcţie) care determină valoarea unei comenzi de aprovizionare, respectiv a unei facturi.\\ Folosind aceste rutine stocate, să se identifice primele trei edituri, respectiv primii trei clienţi în funcţie de volumul tranzacţiilor din librărie. | + | - [**10 puncte**] Să se creeze două rutine stocate pe server (funcții) care determină valoarea unei comenzi de aprovizionare, respectiv a unei facturi.\\ Folosind aceste rutine stocate, să se identifice primele trei edituri, respectiv primii trei clienţi în funcţie de volumul tranzacţiilor din librărie. |
| - [**10 puncte**] Să se creeze o procedură stocată care calculează suma tuturor facturilor pentru un client, afişând lista facturilor şi valoarea lor, ordonată cronologic. | - [**10 puncte**] Să se creeze o procedură stocată care calculează suma tuturor facturilor pentru un client, afişând lista facturilor şi valoarea lor, ordonată cronologic. | ||
| - [**10 puncte**] Să se identifice lista celor mai profitabile cărţi, ordonate în funcţie de suma obţinută din vânzări. | - [**10 puncte**] Să se identifice lista celor mai profitabile cărţi, ordonate în funcţie de suma obţinută din vânzări. | ||
| Line 1979: | Line 1979: | ||
| ===== Soluții ===== | ===== Soluții ===== | ||
| - | disponibile pe 20 octombrie 2014 | + | [[https://github.com/aipi2014/Laborator01/tree/master/rezolvari|Soluții]] |
