Limbajul SQL - recapitulare

Aplicatia folosita pentru recapitularea limbajului SQL este un sistem de gestiune a furnizorilor si vanzarilor catre clientii unui magazin; baza de date contine mai multe tabele:

adresa - adresele clientilor si ale furnizorilor
furnizor - datele despre firmele furnizorilor;
client_card_fidelitate - datele despre clientii care au card de fidelitate, inclusiv adresa, numarul de cumparaturi, valoarea totala cumparaturi si numarul de puncte
articol - articolele din portofoliul firmei de comert: articolele au o denumire, n furnizor si o anumita cantitate in stoc
vanzare - o tranzactie cu un client
vanzare_articol - detaliile tranzactiilor cu un anume client
comanda_stoc - o tranzactie cu un client

Crearea tabelelor



CREATE TABLE IF NOT EXISTS adresa
(id int unique auto_increment primary key,
oras char(20),
distanta_km int);

CREATE TABLE IF NOT EXISTS furnizor
(id int unique auto_increment primary key,
denumire char(50),
adresa_id int,
INDEX (adresa_id),
FOREIGN KEY (adresa_id) REFERENCES adresa(id));



CREATE TABLE IF NOT EXISTS client_card_fidelitate
(id int unique auto_increment primary key,
nume char(20),
prenume char(20),
adresa_id int,
sex int,
data_inregistrare date,
numar_cumparaturi int DEFAULT 0,
valoare_totala_cumparaturi float DEFAULT 0.0,
numar_puncte int DEFAULT 0,
INDEX (adresa_id),
FOREIGN KEY (adresa_id) REFERENCES adresa(id));

CREATE TABLE IF NOT EXISTS articol
(id int unique auto_increment primary key,
denumire char(30),
furnizor_id int NOT NULL,
stoc_count int NOT NULL,
pret_per_unitate float NOT NULL,
INDEX (furnizor_id),
FOREIGN KEY (furnizor_id) REFERENCES furnizor(id));


 CREATE TABLE IF NOT EXISTS vanzare
(id int unique auto_increment primary key,
data date,
client_id int,
INDEX (client_id),
FOREIGN KEY (client_id) REFERENCES client_card_fidelitate(id));



 CREATE TABLE IF NOT EXISTS vanzare_articol
(id int unique auto_increment primary key,
vanzare_id int,
articol_id int,
cantitate int,
INDEX (vanzare_id),
INDEX (articol_id),

FOREIGN KEY (vanzare_id) REFERENCES vanzare(id),
FOREIGN KEY (articol_id) REFERENCES articol(id));


CREATE TABLE IF NOT EXISTS comanda_stoc
(id int unique auto_increment primary key,
articol_id int NOT NULL,
stoc_nou int NOT NULL,
INDEX (articol_id),
FOREIGN KEY (articol_id) REFERENCES articol(id));

Popularea tabelelor



INSERT INTO adresa (oras, distanta_km) VALUES
('Bucuresti',5),('Brasov',200),('Buzau',120),('Cluj Napoca',450),('Constanta',350),
('Craiova',150),('Slatina',120),('Voinesti',65),('Oradea',500),('Timisoara',450),
('Salonta',500);


INSERT INTO furnizor (denumire, adresa_id) VALUES
('Dorna', 1),('Albalact', 1),('Salonta SA', 11),
('Braun', 1),('Comtim',10),('Samsung', 1),
('Sony', 1),('Rex SRL',5),('Comalim',3),('Alpen',2),
('Camara ta',7),('Pampers',1),('Oradeanca',9),('Burger SA',9),
('Bergenbier  SA',1),('Tuborg SA',1);

INSERT INTO client_card_fidelitate (nume, prenume, adresa_id, sex, data_inregistrare) VALUES
('Ionescu','Ion',1, 0,'2009-1-2'),('Popescu','Ion',1,0,'2009-1-2'),
('Georgescu','Adina',1,1,'2009-1-2'),('Marinescu','Ana',1,1,'2009-1-2'),
('Preda','Ion',1,0,'2009-1-3'),('Marin','Georgeta',1,1,'2009-1-3');

INSERT INTO vanzare (data, client_id) VALUES
('2009-1-2', 1),
('2009-1-2', 2),
('2009-1-2', 3),
('2009-1-3', 4),
('2009-1-3', 5),
('2009-1-3', NULL),
('2009-1-3', NULL),
('2009-1-3', NULL),
('2009-1-3', NULL),
('2009-1-3', NULL),
('2009-1-3', NULL),
('2009-1-3', NULL),
('2009-1-3', NULL),
('2009-1-3', NULL);




INSERT INTO articol (denumire, furnizor_id, stoc_count, pret_per_unitate) VALUES
('Lapte Dorna 1L', 1,1000,3),
('Iaurt Dorna 0.25L',1, 500,1),
('Smantana Dorna 0.3L',1, 250,1.5),
('Smantana Dorna 0.5L',1, 250,2),
('Lapte Fulga 1L', 2,1200,2.5),
('Iaurt Zuzu 0.25L', 2,450,0.75),
('Lapte Zuzu 2.5% 1L', 2,750,2.3),
('Lapte Zuzu 1.1% 1L', 2,820,2.7),
('Salam Sibiu 0.3 KG', 3,200,22),
('Sunca porc 0.25KG', 3,140,5.5),
('Costita afumata 0.5KG', 3,100, 8.5),
('Muraturi borcan 0.45KG', 11,100,4.5),
('Pampers 1-2 ani, 35 buc', 12,300,22.5),
('Pampers 1-2 ani, 10 buc', 12,100,8.5),
('Pampers 0-1 ani, 55 buc', 12,400,24),
('Bere Burger pet 2L', 14,200,6.5),
('Bere Burger cutie bax 6buc', 14,500,8.5),
('Bere Bergenbier pet 2.5L', 15,1000,5.5),
('Bere Tuborg sticla 0.5L', 16,100,3.5),
('Bere Bergenbier cutie bax 6buc', 15,450,9.5);



INSERT INTO vanzare_articol (vanzare_id, articol_id, cantitate) VALUES

(1,1,2),(1,15,1),(1,18,2),
(2,3,1),(2,16,1),(2,8,1),(2,9,2),(2,4,1),
(3,1,1),(3,2,1),
(4,6,1),(4,12,2),(4,19,2),
(5,17,2),(5,12,1),
(6,11,1),(6,11,2),(6,1,1),(6,3,4),
(7,10,1),(7,12,2),
(8,11,2),(8,6,2),
(9,11,1),(9,4,3),(9,5,2),
(10,11,1),(10,1,2),
(11,7,1),
(12,1,4),
(13,3,1),
(14,4,1);

Exercitii

Listati adresele din tabela de adrese. Cate adrese sunt ? Cate adrese sunt la o distanta mai mica de 100 km ?
Listati clientii cu card de fidelitate, cu numele, prenumele si cu adresele lor (oras si distanta pana la adresa)
Listati articolele, cu numele furnizorului si cu adresa furnizorului
Listati toate vanzarile, fiecare vanzare cu numele si cantitatea articolelor sale
Listati vanzarile, fiecare vanzare cu clientul cu card de fidelitate
Ce articol s-a vandut cel mai bine ?