#
# 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;