# # Functii agregate - continuare # # # Baza de date contine 4 tabele: # Adrese # Clienti # Comenzi # Articole # si o tabela de legatura comenzi-articole ###Creare tabele #Tabela adrese CREATE TABLE IF NOT EXISTS adresa (id int unique auto_increment primary key, strada char(20), oras char(20)); #Tabela clienti CREATE TABLE IF NOT EXISTS client (id int unique auto_increment primary key, denumire char(50), adresa_id int, INDEX (adresa_id), FOREIGN KEY (adresa_id) REFERENCES adresa(id)); #Tabela comenzi CREATE TABLE IF NOT EXISTS comanda (id int unique auto_increment primary key, data date, client_id int, INDEX (client_id), FOREIGN KEY (client_id) REFERENCES client(id)); #Tabela articole CREATE TABLE IF NOT EXISTS articol (id int unique auto_increment primary key, descriere char(40), pret float); # Tabela de legatura dintre comenzi si articole (un articol poate sa apara in mai multe comenzi, o comanda are mai #multe articole CREATE TABLE IF NOT EXISTS comanda_articol (id int unique auto_increment primary key, comanda_id int, articol_id int, cantitate int, INDEX (comanda_id), INDEX (articol_id), FOREIGN KEY (comanda_id) REFERENCES comanda(id), FOREIGN KEY (articol_id) REFERENCES articol(id)); #Populare tabele cu date INSERT INTO adresa (strada, oras) VALUES ('Magheru', 'Bucuresti'), ('Brasov', 'Bucuresti'), ('Elisabeta', 'Bucuresti'), ('Maniu', 'Brasov'), ('Aviatorilor', 'Bucuresti'), ('Balcescu', 'Buzau'), ('Mihalache', 'Bucuresti'), ('Horea', 'Cluj Napoca'), ('Macaralei', 'Barlad'), ('Minerului', 'Petrosani'); ## INSERT INTO client (denumire, adresa_id) VALUES ('SC LEON SRL', 1), ('GEOMIL', 2), ('LIBERTY', 3), ('MINERON', 10), ('AEROFUN', 4), ('MEDITEL', 5); ## INSERT INTO comanda (data, client_id) VALUES ('2007-4-8', 1), ('2007-4-8', 1), ('2007-4-7', 2), ('2008-4-8', 2), ('2008-4-9', 3), ('2008-4-8', 4), ('2008-4-8', 5); ## INSERT INTO articol (descriere, pret) VALUES ('stilou', 5), ('pix bila', 4), ('creion', 0.5), ('marker', 2), ('guma', 0.75), ('rigla', 1), ('echer', 1), ('penar', 2), ('top hartie', 9.5), ('top hartie gloss', 9.5), ('cutie cerneala', 2.25); ## INSERT INTO comanda_articol (comanda_id, articol_id, cantitate) VALUES (1,1,10), (1,2,10), (1,3,10), (2,4,2), (2,5,4), (2,6,12), (3,8,20), (3,5,42), (4,1,20), (4,2,40), (4,3,20), (4,4,40), (4,5,20), (4,6,20), (5,1,100), (5,7,200), (6,2,25), (6,6,25), (7,3,100); ###Exercitii functii agregate - continuare #(1) Calculati varianta preturilor articolelor; calculati deviatia standard a preturilor articolelor. #(2) Listati orasele care apar in adrese; Listati orasele care apar in adresele clientilor, cu numarul corespunzator de adrese distincte; #(3) Listati numarul de comenzi pentru fiecare client, cu suma totala si media valorii comenzilor. #(4) (GROUP BY dupa mai multe coloane) Listati comenzile, cu pretul articolelor si cantitatea. #(5) (GROUP BY expresii) Listati numarul de comenzi pe fiecare an #(6) Listati numarul de comenzi din anul 2008 #(7) Listati numarul de comenzi pentru fiecare client, cu suma totala si media valorii comenzilor, avand suma totala peste 100 lei. # (1) Calculati varianta preturilor articolelor; calculati deviatia standard a preturilor articolelor. SELECT SQRT(VARIANCE(pret)), VARIANCE(pret) FROM articol; #(2) Listati orasele care apar in adrese; Listati orasele care apar in adresele clientilor, cu numarul corespunzator de adrese distincte; SELECT oras FROM adresa GROUP BY oras; SELECT oras, count(*) FROM adresa GROUP BY oras; #(3) Listati numarul de comenzi pentru fiecare client, cu suma totala si media valorii comenzilor. SELECT denumire AS Client, COUNT(DISTINCT comanda.id) AS 'Numar comenzi', SUM(pret * cantitate) AS 'Comenzi totale', AVG(pret * cantitate) AS 'Valoare medie comenzi' FROM client, comanda, comanda_articol, articol WHERE client.id = comanda.client_id AND comanda.id = comanda_articol.comanda_id AND comanda_articol.articol_id = articol.id GROUP BY client.id; #(4) (GROUP BY dupa mai multe coloane) Listati comenzile, cu pretul articolelor si cantitatea. SELECT pret, cantitate FROM articol, comanda_articol WHERE articol.id = comanda_articol.articol_id GROUP BY pret, cantitate; #(5) (GROUP BY expresii) Listati numarul de comenzi pe fiecare an SELECT YEAR(data) AS 'An', COUNT(*) AS 'Numar de comenzi' FROM comanda GROUP BY YEAR(data); #(6) Listati numarul de comenzi din anul 2008 SELECT YEAR(data) AS 'An', COUNT(*) AS 'Numar de comenzi' FROM comanda GROUP BY YEAR(data) HAVING An = '2008'; #(7) Listati numarul de comenzi pentru fiecare client, cu suma totala si media valorii comenzilor, avand suma totala peste 100 lei. SELECT denumire AS Client, COUNT(DISTINCT comanda.id) AS 'Numar comenzi', SUM(pret * cantitate) AS 'Comenzi totale', AVG(pret * cantitate) AS 'Valoare medie comenzi' FROM client, comanda, comanda_articol, articol WHERE client.id = comanda.client_id AND comanda.id = comanda_articol.comanda_id AND comanda_articol.articol_id = articol.id GROUP BY client.id HAVING SUM(pret * cantitate) > 100; select client.denumire, concat(adresa.strada, ', ',oras) As Adresa, articol.descriere, pret, cantitate, data from articol, comanda, comanda_articol, client, adresa where comanda.id = comanda_articol.comanda_id and comanda_articol.articol_id = articol.id and comanda.client_id = client.id and client.adresa_id = adresa.id and comanda.id = 1; select count(*) from articol where pret = (select min(pret) from articol where substring(descriere,1,1) = 'p') and substring(descriere,1,1) = 'p'; INSERT INTO adresa (strada, oras) VALUES ('Magheru', 'Bucuresti'); SELECT denumire AS Client, COUNT(DISTINCT comanda.id) AS 'Numar comenzi', SUM(pret * cantitate) AS 'Comenzi totale', AVG(pret * cantitate) AS 'Valoare medie comenzi' FROM client, comanda, comanda_articol, articol WHERE client.id = comanda.client_id AND comanda.id = comanda_articol.comanda_id AND comanda_articol.articol_id = articol.id GROUP BY client.id;