Laborator 4

Limbajul de interogare a datelor 

Subiectul acestei sedinte este invatarea unor elemente de limbaj de interogare a bazelor de date.



 Exercitii 



Aplicatia cu care veti lucra modeleaza sistemul de gestiune al angajatilor de la o firma
de tehnologia informatiei. In tabela departament se stocheaza informatiile despre departamente; in tabela
angajat se stocheaza informatiile despre fiecare angajat. Se definesc doua chei straine in tabela angajat:
(a) departament_id care e referinta pentru id din tabela departament;
(b) manager_id - o coloana tot din tabela angajat - care e referinta pentru id din tabela angajat





CREATE DATABASE IF NOT EXISTS numeprenume12;
use numeprenume12;

##Crearea tabelelor

CREATE TABLE IF NOT EXISTS departament
(id int unique auto_increment primary key,
nume char(20),
manager_id int);

CREATE TABLE IF NOT EXISTS angajat
(id int unique auto_increment primary key,
nume char(20),
prenume char(20),
departament_id int,
manager_id int ,
salariu int,
angajare date,
vechime date,
INDEX (departament_id),
FOREIGN KEY (departament_id) REFERENCES departament(id),
FOREIGN KEY(manager_id) REFERENCES angajat(id));






Se populeaza mai intai tabela departament (datorita constrangerii de integritate impuse prin
referinta: departament_id din tabela angajat este cheie straina pentru id din tabela departament, se impune aceasta ordine de inserare
a datelor)




INSERT INTO departament (nume, manager_id) VALUES
('R&D', 1), ('QA', 2), ('IT', 3), ('Backend', 4), ('HR', 5);


INSERT INTO angajat
(nume, prenume, departament_id, manager_id, salariu, angajare)
VALUES
('Popa', 	'Ion', 	  1, NULL, 9000, '2000-1-12'),
('Popescu', 	'Maria',   1, 1, 4000, '2003-5-6'),
('Marinescu', 	'Vasile', 1, 1, 4500, '2004-6-3'),
('Ionescu', 	'Andrei', 1, NULL, 3500, '2002-1-1'),
('Vasilescu', 	'Ana', 	  2, NULL, 2500, '2006-3-3'),
('Dragan', 	'Dinu',   2, 5, 2000, '2004-11-12'),
('Mihailescu', 	'Adrian', 5, NULL, 3500, '2006-10-12'),
('Teodorescu', 	'Matei',  3, NULL, 3000, '2005-1-12'),
('Popescu', 	'Vasile', 3, 8, 4000, '2005-9-9'),
('Mateescu', 	'Dumitru',3, 8, 3500, '2007-2-5'),
('Calinescu', 	'Alin',   4, NULL, 3200, '2005-8-2'),
('Popescu', 	'Mihaela',4, 12, 1800, '2005-4-8'),
('Ionescu', 	'Diana',  5, NULL, 5500, '2001-1-12');





Tabela angajat



Tabela departament




 Intrebari 


    

  1. Listati numele angajatilor
    2. 

  2. Listati numele angajatilor, ordonate crescator
    3. 

  3. Selectati numele angajatilor, ordonate descrescator
    4. 

  4. Selectati numele angajatiolor, ordonate descrescator dupa departament_id, crescator dupa salariu;
    5. 

  5. Selectati numele angajatilor care lucreaza la R&D
    6. 

  6. Listati angajatii care nu lucreaza la R&D
    7. 

  7. Listati angajatii care au salariu mai mare de 3000 lei
    8. 

  8. Selectati angajatii care au salariul 3000 lei
    9. 

  9. Selectati angajatii care au salariul intre 3000 si 5000 de lei
    10. 

  10. Selectati angajatii care nu au manager
    11. 

  11. Listati angajatii ai caror manager are ID-ul  1
    12. 

  12. Enumerati angajatii ai caror manager este Popa Ion
    13. 

  13. Listati angajatii care au salariu mai mare de 2500 lei si lucreaza la Backend
    14. 

  14. Selectati angajatii ai caror manager NU este Popa Ion si care au salariu mai mare de 4000 lei
    15. 




 Raspunsuri 





##1
SELECT nume, prenume
FROM angajat;

SELECT concat(nume,' ',prenume)
FROM angajat;

SELECT concat_ws(' ',nume, prenume)
FROM angajat;

##2
SELECT nume, prenume
FROM angajat
ORDER BY nume, prenume ASC;

##3
SELECT nume, prenume
FROM angajat
ORDER BY nume, prenume DESC;

SELECT nume, prenume
FROM angajat
ORDER BY nume DESC, prenume DESC;

##4
SELECT nume, prenume
FROM angajat
ORDER BY departament_id DESC, salariu;

SELECT nume AS Nume, prenume AS Prenume, departament_id AS Dept, Salariu
FROM angajat
ORDER BY departament_id DESC, salariu;

##5
SELECT nume
FROM angajat
WHERE departament_id = 1;


SELECT angajat.nume
FROM angajat, departament
WHERE angajat.departament_id = departament.id
AND departament.nume = 'R&D';

SELECT angajat.nume
FROM angajat JOIN departament
ON angajat.departament_id = departament.id
AND departament.nume = 'R&D';


##6
SELECT angajat.nume
FROM angajat, departament
WHERE angajat.departament_id = departament.id
AND departament.nume <> 'R&D';

SELECT angajat.nume
FROM angajat JOIN departament
ON angajat.departament_id = departament.id
AND departament.nume <> 'R&D';


#7 Angajatii care au salariu mai mare de 3000 lei
SELECT nume, prenume
FROM angajat
WHERE salariu > 3000;

SELECT nume, prenume, salariu
FROM angajat
WHERE salariu > 3000;

#8 Angajatii care au salariul 3000 lei
SELECT nume, prenume, salariu
FROM angajat
WHERE salariu = 3000;


#9 Angajatii care au salariul intre 3000 si 5000 de lei (incluzand limitele)
SELECT nume, prenume, salariu
FROM angajat
WHERE salariu >= 3000 AND salariu <= 5000;


SELECT nume, prenume, salariu
FROM angajat
WHERE salariu BETWEEN 3000 AND 5000;

SELECT nume, prenume, salariu
FROM angajat
WHERE salariu > 3000 AND salariu < 5000;

#10 Angajatii care nu au manager
SELECT nume, prenume
FROM angajat
WHERE manager_id IS NULL;

#11 Angajatii ai carui manager are ID-ul  1
SELECT nume, prenume
FROM angajat
WHERE manager_id = 1;


#12 Angajatii ai carui manager este Popa Ion
SELECT nume, prenume
FROM angajat
WHERE manager_id IN
(SELECT id
FROM angajat
WHERE nume='Popa' AND prenume='Ion');


SELECT concat_ws(' ',a1.nume, a1.prenume) As Angajat, concat(a2.nume,' ',a2.prenume) as Manager
FROM angajat a1 JOIN angajat a2 ON
a1.manager_id = a2.id
WHERE a2.nume = 'Popa' AND a2.prenume = 'Ion';



#13 Angajatii care au salariu mai mare de 2500 lei si lucreaza la Backend

SELECT angajat.nume AS Angajat, departament.nume AS Departament, salariu AS Salariu
FROM angajat, departament
WHERE
(angajat.departament_id = departament.id
AND departament.nume = 'Backend')
AND salariu > 2500;

#14 Angajatii ai caror manager NU este Popa Ion si care au salariu mai mare de 4000 lei

SELECT nume, prenume, manager_id, salariu
FROM angajat
WHERE manager_id IS NULL
OR manager_id NOT IN
(SELECT id
FROM angajat
WHERE nume='Popa' AND prenume='Ion')
AND salariu > 1000;


## (de ce includem 'manager_id IS NULL' ?)
SELECT nume, prenume
FROM angajat
WHERE manager_id NOT IN
(SELECT id
FROM angajat
WHERE nume='Popa' AND prenume='Ion')


## aceeasi problema - nu includem acei angajati care nu au manager daca facem doar join
SELECT concat(a1.nume,' ',a1.prenume) As Angajat, concat(a2.nume,' ',a2.prenume) as Manager, a1.salariu
FROM angajat a1 INNER JOIN angajat a2
ON (a1.manager_id = a2.id)
WHERE ((a2.nume<>'Popa' OR a2.prenume<>'Ion')
AND a1.salariu > 1000);




 Exercitii suplimentare 


    

  1. Verificati daca managerul celor de la R&D este si el la R&D
    2. 

  2. Listati managerii, in dreptul fiecaruia trecand si departamentul la care lucreaza
    3. 

  3. Listati departamentele cu mai mult de un manager
    4. 

  4. Verificati daca toti managerii sunt din acelasi departament cu subordonatii lor
    5. 

  5. Executati corectiile necesare pentru ca la punctul anterior raspunsul sa fie afirmativ
    6. 

  6. Asigurati-va ca in tabela departament manager_id este setat corect (verificati si daca nu e corect, corectati)
    7.