bd

DROP TABLE angajati;

CREATE TABLE angajati(
IdAngajat int NOT NULL PRIMARY KEY,
NUME varchar(20) NOT NULL,
PRENUME varchar(20) NOT NULL,
DataN datetime,
Adresa varchar(50),
Salariu decimal DEFAULT 2800,
IdSectie int
);

DROP TABLE sectii;

CREATE TABLE sectii(
IdSectie int NOT NULL PRIMARY KEY,
Nume varchar(50) NOT NULL,
Buget decimal,
);
ALTER TABLE angajati ADD CONSTRAINT fk_angajati FOREIGN KEY(IdSectie) REFERENCES Sectii(IdSectie);
--restrictie integritate diferentiala

INSERT INTO sectii VALUES(1,'Productie',1400);
INSERT INTO sectii VALUES(2,'Contabilitate',1200);
INSERT INTO sectii VALUES(3,'Vanzari',3000);

INSERT INTO angajati VALUES(100,'Ion','Viorel','02/21/1990','Craiova',2100,1);
INSERT INTO angajati VALUES(101,'Ana','Popa','01/01/1990','Bals',2200,2);
INSERT INTO angajati VALUES(102,'Nicu','Adrian','05/07/1989','Craiova',1800,1);
INSERT INTO angajati VALUES(103,'Gigel','Catel','09/03/1990','Slatina',1600,3);


DROP TABLE angajati1;

CREATE TABLE angajati1(
IdAngajat int NOT NULL PRIMARY KEY,
NUME varchar(20) NOT NULL,
PRENUME varchar(20) NOT NULL,
DataN datetime,
Adresa varchar(50),
Salariu decimal DEFAULT 2800,
IdSectie int
);


INSERT INTO angajati1 VALUES(100,'Ion','Viorel','02/21/1990','Craiova',2100,1);
INSERT INTO angajati1 VALUES(101,'Ana','Popa','01/01/1990','Bals',2200,2);
INSERT INTO angajati1 VALUES(104,'Fota','Lidia','04/03/1994','Turceni',2300,2);
INSERT INTO angajati1 VALUES(105,'Dragomir','Georgica','09/12/1994','Slatina',2700,3);

SELECT *FROM angajati1;

SELECT * FROM angajati
UNION
SELECT * FROM angajati1;

SELECT * FROM angajati
UNION ALL
SELECT * FROM angajati1;

SELECT * FROM angajati
INTERSECT
SELECT * FROM angajati1;

SELECT * FROM angajati
EXCEPT
SELECT * FROM angajati1;

SELECT * FROM angajati
WHERE IdSectie=1 AND Adresa='Craiova';


SELECT IdAngajat, NUME, Adresa FROM angajati; --proiectie


SELECT * FROM angajati,sectii; --produs cartezian

SELECT * FROM angajati,sectii
WHERE angajati.IdSectie=sectii.IdSectie; -- join

SELECT * FROM angajati a
WHERE IdSectie=1;

SELECT a.IdAngajat,a.Adresa,s.IdSectie,s.NUME,s.Buget FROM angajati a, sectii s
WHERE a.IdSectie=s.IdSectie;

SELECT angajati.NUME,PRENUME,DataN,Salariu FROM sectii, angajati WHERE sectii.IdSectie=angajati.IdSectie AND sectii.Nume='Productie';

SELECT * FROM angajati WHERE IdSectie IN (SELECT IdSectie FROM sectii WHERE IdSectie=1); --imbricare

SELECT * FROM angajati WHERE IdSectie IN (SELECT IdSectie FROM sectii WHERE IdSectie>1); --imbricare


drop table ACHIZITII
create table ACHIZITII(
idAchizitie int PRIMARY KEY,
idFurnizor int,
idComponenta int,
idAngajat int,
Data DateTime,
NrComponente int,
PretUnitar decimal(8,2)
);

drop table FURNIZORI
create table FURNIZORI(
idFurnizor int PRIMARY KEY,
Nume varchar(20),
Prenume varchar(20),
Adresa varchar(20));

drop table COMPONENTE
create table COMPONENTE(
idComponenta int PRIMARY KEY,
Denumire varchar(20),
Descriere varchar(20));

drop table PRODUSE
create table PRODUSE(
idProdus int PRIMARY KEY,
Denumire varchar(20),
Descriere varchar(20));

drop table CLIENTI
create table CLIENTI(
idClient int PRIMARY KEY,
Nume varchar(20),
Prenume varchar(20),
Adresa varchar(20));

drop table PROIECTE
create table PROIECTE(
idProiect int PRIMARY KEY,
Denumire varchar(20),
Termen DateTime,
Buget int);

drop table VANZARI
create table VANZARI(
idVanzare int PRIMARY KEY,
idProdus int,
idClient int,
idAngajat int,
Data datetime,
NumarProdus int);

drop table ACTIVITATI
create table ACTIVITATI(
idProiect int,
idAngajat int,
Ore int,
Constraint x1 PRIMARY KEY(idProiect,idAngajat));

drop table COMPOZITII
create table COMPOZITII(
idComponenta int,
idProdus int,
NrComponente int,
Constraint x2 PRIMARY KEY(idComponenta,idProdus));



alter table ACHIZITII add Constraint f1 foreign key(idFurnizor) REFERENCES Furnizori(idFurnizor);

alter table ACHIZITII add Constraint f2 foreign key(idComponenta) REFERENCES Componente(idComponenta);

alter table ACHIZITII add Constraint f3 foreign key(idAngajat) REFERENCES Angajati(idAngajat);

alter table COMPOZITII add Constraint f4 foreign key(idComponenta) REFERENCES Componente(idComponenta);

alter table COMPOZITII add Constraint f5 foreign key(idProdus) REFERENCES Produse(idProdus);

alter table VANZARI add Constraint f6 foreign key(idProdus) REFERENCES PRODUSE(idProdus);

alter table VANZARI add Constraint f7 foreign key(idClient) REFERENCES Clienti(idClient);

alter table ACTIVITATI add Constraint f8 foreign key(idPROIECT) REFERENCES Proiecte(idProiect);

alter table ACTIVITATI add Constraint f9 foreign key(idAngajat) REFERENCES Angajati(idAngajat);

alter table VANZARI add Constraint f10 foreign key(idAngajat) REFERENCES Angajati(idAngajat);


INSERT INTO FURNIZORI VALUES (1,'ALECU','ION','CRAIOVA');
INSERT INTO FURNIZORI VALUES (2,'IANCU','DAN','BALS');
INSERT INTO FURNIZORI VALUES (3,'STOICA','NICU','BUCURESTI');

INSERT INTO PRODUSE VALUES (1,'TRICOU','ROSU');
INSERT INTO PRODUSE VALUES (2,'CAMASA','ALBA');
INSERT INTO PRODUSE VALUES (3,'SAPCA','NEAGRA');
INSERT INTO PRODUSE VALUES (4,'PANTOFI','MARO');

INSERT INTO COMPONENTE VALUES (1,'NASTURI','MARI');
INSERT INTO COMPONENTE VALUES (2,'ATA','SUBTIRE');
INSERT INTO COMPONENTE VALUES (3,'BENTITA','ALBA');

INSERT INTO CLIENTI VALUES (1,'ILIESCU','ION','RAHOVA');
INSERT INTO CLIENTI VALUES (2,'UDREA','ELENA','JILAVA');
INSERT INTO CLIENTI VALUES (3,'COCOS','DORIN','GHERLA');

INSERT INTO PROIECTE VALUES (1,'DNA','03/23/2015',2000);
INSERT INTO PROIECTE VALUES (2,'DIICOT','05/20/2015',1500);
INSERT INTO PROIECTE VALUES (3,'CSM','11/21/2015',2500);

INSERT INTO ACHIZITII VALUES (1,1,1,100,'03/24/2015',5,3);
INSERT INTO ACHIZITII VALUES (2,2,3,102,'03/25/2015',6,4);
INSERT INTO ACHIZITII VALUES (3,3,2,100,'03/26/2015',10,18);
INSERT INTO ACHIZITII VALUES (4,2,1,103,'03/15/2015',7,10);
update ACHIZITII set NrComponente=200 where idAngajat=100;

INSERT INTO COMPOZITII VALUES (1,1,5);
INSERT INTO COMPOZITII VALUES (2,2,3);
INSERT INTO COMPOZITII VALUES (3,3,1);
INSERT INTO COMPOZITII VALUES (2,4,1);

INSERT INTO VANZARI VALUES (1,2,3,101,'03/23/2015',3);
INSERT INTO VANZARI VALUES (2,4,2,100,'03/10/2015',1);
INSERT INTO VANZARI VALUES (3,2,1,102,'03/05/2015',2);
INSERT INTO VANZARI VALUES (4,3,1,100,'03/06/2015',1);

INSERT INTO ACTIVITATI VALUES (1,100,150);
INSERT INTO ACTIVITATI VALUES (1,101,120);
INSERT INTO ACTIVITATI VALUES (2,102,130);
INSERT INTO ACTIVITATI VALUES (2,103,100);
INSERT INTO ACTIVITATI VALUES (2,104,100);

--TEMA
--1
SELECT Nume,Prenume FROM FURNIZORI WHERE Adresa='BUCURESTI'
UNION
SELECT Nume,Prenume FROM CLIENTI where adresa='RAHOVA';
--2
SELECT * FROM angajati,sectii;
--3
SELECT FURNIZORI.Nume, FURNIZORI.Prenume FROM FURNIZORI,ACHIZITII
WHERE
FURNIZORI.idFurnizor=ACHIZITII.idFurnizor AND   NrComponente>4;
--4
SELECT angajati.nume,angajati.prenume from COMPONENTE,angajati,ACHIZITII
WHERE
(achizitii.idAngajat=angajati.IdAngajat AND ACHIZITII.idComponenta=COMPONENTE.idComponenta) and COMPONENTE.Denumire='ata';
--5
SELECT FURNIZORI.Nume, FURNIZORI.Prenume, FURNIZORI.Adresa from COMPONENTE,ACHIZITII,FURNIZORI
WHERE
(FURNIZORI.idFurnizor=ACHIZITII.idFurnizor and ACHIZITII.idComponenta=COMPONENTE.idComponenta) and COMPONENTE.Denumire='nasturi'
--6
SELECT FURNIZORI.Nume, FURNIZORI.Prenume, FURNIZORI.Adresa from COMPONENTE,ACHIZITII,FURNIZORI
WHERE
(FURNIZORI.idFurnizor=ACHIZITII.idFurnizor and ACHIZITII.idComponenta=COMPONENTE.idComponenta)
 and
ACHIZITII.NrComponente>=3 AND COMPONENTE.Denumire='nasturi';
--7
SELECT CLIENTI.Nume, CLIENTI.Prenume, CLIENTI.Adresa from CLIENTI,PRODUSE, VANZARI
where
VANZARI.idProdus=PRODUSE.idProdus and VANZARI.idClient=CLIENTI.idClient and PRODUSE.Denumire='camasa'and VANZARI.NumarProdus>=2;
select * from vanzari
select * from produse
--8
SELECT angajati.Nume, angajati.Prenume from angajati,PRODUSE, VANZARI
where
VANZARI.idProdus=PRODUSE.idProdus and angajati.IdAngajat=VANZARI.idAngajat and PRODUSE.Denumire='camasa';
--9
SELECT angajati.Nume, angajati.Prenume,angajati.DataN from angajati,ACTIVITATI,PROIECTE
where
angajati.IdAngajat=ACTIVITATI.idAngajat and ACTIVITATI.idProiect=PROIECTE.idProiect and PROIECTE.Denumire='DNA';
--10
select * from angajati
select * from sectii
SELECT sectii.Nume FROM SECTII, angajati, ACTIVITATI, PROIECTE WHERE
angajati.IdSectie=sectii.IdSectie and angajati.IdAngajat=ACTIVITATI.idAngajat and proiecte.idProiect=activitati.idProiect
 and  PROIECTE.Denumire='DNA';
 --14
SELECT angajati.Nume, angajati.Prenume from angajati,ACHIZITII,FURNIZORI
where
angajati.IdAngajat=ACHIZITII.idAngajat and ACHIZITII.idFurnizor=FURNIZORI.idFurnizor and FURNIZORI.Nume='Alecu';
--15
SELECT angajati.Nume, angajati.Prenume from angajati,VANZARI,CLIENTI
where
angajati.IdAngajat=VANZARI.idAngajat and VANZARI.idClient=CLIENTI.idClient and CLIENTI.Nume='Iliescu'
--16
SELECT PRODUSE.Denumire from ACHIZITII,COMPOZITII,FURNIZORI,COMPONENTE,PRODUSE where
ACHIZITII.idFurnizor=FURNIZORI.idFurnizor and ACHIZITII.idComponenta=COMPONENTE.idComponenta
and COMPOZITII.idComponenta=COMPONENTE.idComponenta and COMPOZITII.idProdus=PRODUSE.idProdus and FURNIZORI.Nume='Stoica';