CREATE TABLE Sectii(
    IdSectie INT PRIMARY KEY,
    Nume VARCHAR(20) NOT NULL,
    Buget INT NOT NULL
);

CREATE TABLE Angajati(
    IdAngajat INT PRIMARY KEY,
    Nume VARCHAR(20) NOT NULL,
    Prenume VARCHAR(50) NOT NULL,
    DataNasterii DATE NOT NULL,
    Adresa VARCHAR(20) NOT NULL,
    Functie VARCHAR(25) NOT NULL,
    Salariu INT NOT NULL,
    IdSectie INT NOT NULL
);

CREATE TABLE Furnizori(
    IdFurnizor INT PRIMARY KEY,
    Nume VARCHAR(20) NOT NULL,
    Prenume VARCHAR(30) NOT NULL,
    Adresa VARCHAR(50) NOT NULL
);

CREATE TABLE Clienti(
    IdClient INT PRIMARY KEY,
    Nume VARCHAR(20) NOT NULL,
    Prenume VARCHAR(30) NOT NULL,
    Adresa VARCHAR(50) NOT NULL
);

CREATE TABLE Produse(
    IdProdus INT PRIMARY KEY,
    Denumire VARCHAR(20) NOT NULL,
    Descriere VARCHAR(50) NOT NULL,
);

CREATE TABLE Componente(
    IdComponenta INT PRIMARY KEY,
    Denumire VARCHAR(20) NOT NULL,
    Descriere VARCHAR(50) NOT NULL,
);

CREATE TABLE Dependenti(
    IdAngajat INT ,
    Nume VARCHAR(20) PRIMARY KEY NOT NULL,
    Prenume VARCHAR(30) NOT NULL,
    DataNasterii DATE NOT NULL,
    GradRudenie VARCHAR(20) NOT NULL
);

CREATE TABLE Ingineri(
    IdAngajat INT PRIMARY KEY NOT NULL,
    Specialitate VARCHAR(20) NOT NULL,
);

CREATE TABLE Secretare(
    IdAngajat INT PRIMARY KEY NOT NULL,
    VitezaRedactare INT NOT NULL,
);

CREATE TABLE Achizitii(
    IdAchizitie INT PRIMARY KEY,
    IdAchizitor INT NOT NULL,
    IdFurnizor INT NOT NULL,
    IdComponenta INT NOT NULL,
    DATA DATE NOT NULL,
    NrComponente INT NOT NULL,
    PretUnitar FLOAT NOT NULL,
    IdAngajat INT NOT NULL
);

CREATE TABLE Compozitii(
    NrComponente INT NOT NULL,
    IdComponenta INT NOT NULL,
    IdProdus INT NOT NULL
);

CREATE TABLE Vanzari(
    IdVanzare INT PRIMARY KEY,
    IdVanzator INT NOT NULL,
    IdProdus INT NOT NULL,
    IdClient INT NOT NULL,
    DATA DATE NOT NULL,
    NrProduse INT NOT NULL,
    IdAngajat INT NOT NULL
);

CREATE TABLE Activitati(
    IdProiect INT NOT NULL,
    IdAngajat INT NOT NULL,
    Ore INT NOT NULL,
);

CREATE TABLE Proiecte(
    IdProiect INT PRIMARY KEY,
    Denumire VARCHAR(50) NOT NULL,
    Termen DATE NOT NULL,
    Buget INT NOT NULL
);

ALTER TABLE angajati ADD CONSTRAINT fk_angajati FOREIGN KEY (IdSectie) REFERENCES sectii (IdSectie)
ALTER TABLE dependenti ADD CONSTRAINT fk_dependenti FOREIGN KEY (IdAngajat) REFERENCES angajati (IdAngajat)
ALTER TABLE secretare ADD CONSTRAINT fk_secretare FOREIGN KEY (IdAngajat) REFERENCES angajati (IdAngajat)
ALTER TABLE ingineri ADD CONSTRAINT fk_ingineri FOREIGN KEY (IdAngajat) REFERENCES angajati (IdAngajat)
ALTER TABLE achizitii ADD CONSTRAINT fk_achizitii FOREIGN KEY (IdFurnizor) REFERENCES furnizori (IdFurnizor)
ALTER TABLE achizitii ADD CONSTRAINT fks_achizitii FOREIGN KEY (IdComponenta) REFERENCES componente (IdComponenta)
ALTER TABLE compozitii ADD CONSTRAINT fk_compozitii FOREIGN KEY (IdComponenta) REFERENCES componente (IdComponenta)
ALTER TABLE compozitii ADD CONSTRAINT fks_compozitii FOREIGN KEY (IdProdus) REFERENCES produse (IdProdus)
ALTER TABLE vanzari ADD CONSTRAINT fk_vanzari FOREIGN KEY (IdProdus) REFERENCES produse (IdProdus)
ALTER TABLE vanzari ADD CONSTRAINT fks_vanzari FOREIGN KEY (IdClient) REFERENCES clienti (IdClient)
ALTER TABLE activitati ADD CONSTRAINT fk_activitati FOREIGN KEY (IdAngajat) REFERENCES angajati (IdAngajat)
ALTER TABLE activitati ADD CONSTRAINT fks_activitati FOREIGN KEY (IdProiect) REFERENCES proiecte (IdProiect)
ALTER TABLE achizitii ADD CONSTRAINT fka_achizitii FOREIGN KEY (IdAngajat) REFERENCES angajati (IdAngajat)
ALTER TABLE vanzari ADD CONSTRAINT fka_vanzari FOREIGN KEY (IdAngajat) REFERENCES angajati (IdAngajat)

INSERT INTO sectii VALUES(1,'Productie',500000);
INSERT INTO sectii VALUES(2,'Motoare',10000000);
INSERT INTO sectii VALUES(3,'Caroserie',750000);
INSERT INTO sectii VALUES(4,'Asamblare',1250000);
INSERT INTO sectii VALUES(5,'Testare',400000);

INSERT INTO angajati VALUES (1,'Popa','Mihai','10/02/1990','Craiova', 'Tester', 1500,1);
INSERT INTO angajati VALUES (2,'Popescu','Andrei','02/10/1990','Bucuresti', 'Programator', 1700,2);
INSERT INTO angajati VALUES (3, 'Ilie', 'Mihai', '10/02/1987', 'Ploiesti', 'Tester', 2100, 3);
INSERT INTO angajati VALUES (4,'Mihai','Daniel','1989/12/21','Craiova', 'Muncitor', 1950,4);
INSERT INTO angajati VALUES (5,'Ion','Razvan','1985/07/10','Bucuresti', 'Programator', 2250,5);
INSERT INTO angajati VALUES (6,'Dumitru','Ion','1970/01/01','Brasov', 'Tester', 2500,1);
INSERT INTO angajati VALUES (7,'Dinu','Andrei','02/10/1989','Timisoara', 'Tester', 1800,2);
INSERT INTO angajati VALUES (8,'Andrei','Alin','12/05/1987','Bucuresti', 'Programator', 2300,3);
INSERT INTO angajati VALUES (9,'Mihalcea','Daniela','1989/12/21','Craiova', 'Tester', 2150,4);
INSERT INTO angajati VALUES (10,'Mihnea','Razvy','1989/12/21','Brasov', 'Muncitor', 1550,5);
INSERT INTO angajati VALUES (11,'Mitatu','Robert','1989/12/21','Craiova', 'Muncitor', 60000,5);

INSERT INTO furnizori VALUES(1, 'Popescu', 'Razvan', 'Craiova');
INSERT INTO furnizori VALUES(2, 'Danescu', 'Ovidiu', 'Constanta');
INSERT INTO furnizori VALUES(3, 'Dancioiu', 'Andrei', 'Bucuresti');
INSERT INTO furnizori VALUES(4, 'Popa', 'Alin', 'Pitesti');

INSERT INTO Clienti VALUES(1,'Alexandru','Lucian','Slatina');
INSERT INTO Clienti VALUES(2,'Stefran','Marian','Bucuresti');
INSERT INTO Clienti VALUES(3,'Andrei','Mihai','Rimisoara');
INSERT INTO Clienti VALUES(4,'Lia','Virgy','Gorj');
INSERT INTO Clienti VALUES(5,'Alexandru','Maria','Bucuresti');
INSERT INTO Clienti VALUES(6,'Stefan','Ioana','Iasi');
INSERT INTO Clienti VALUES(7,'Andrei','Alexa','Craiova');
INSERT INTO Clienti VALUES(8,'Mihai','Andreea','Timisoara');
INSERT INTO Clienti VALUES(9,'Liviu','Ics','Valcea');
INSERT INTO Clienti VALUES(10,'Popa','Andrei','Constanta');
INSERT INTO Clienti VALUES(11,'Marinescu','Ion','Bucuresti');


INSERT INTO produse VALUES(1,'Monitor','ecran 15 inch');
INSERT INTO produse VALUES(2,'Tastatura','---');
INSERT INTO produse VALUES(3,'Mouse','---');
INSERT INTO produse VALUES(4,'Unitate','---');
INSERT INTO produse VALUES(5,'Sistem audio','---');

INSERT INTO componente VALUES(1,'rezistenta','rezistenta de caldura');
INSERT INTO componente VALUES(2,'condensator','disp. electric');
INSERT INTO componente VALUES(3,'tranzistor','-------');
INSERT INTO componente VALUES(4,'circuit integrat','-------');
INSERT INTO componente VALUES(5,'termistor','--?---');

INSERT INTO dependenti VALUES (10,'Andrei','Costinela','12/15/1989','fiica');
INSERT INTO dependenti VALUES (8,'Boboc','Laura','05/17/1989','nepoata');
INSERT INTO dependenti VALUES (6,'Rat','Andrey','05/12/1988','unchi');
INSERT INTO dependenti VALUES (4,'Mirelus','Iulian','11/11/1969','strabunic');
INSERT INTO dependenti VALUES (2,'Gheorghe','Constantin','01/01/1980','var');
INSERT INTO dependenti VALUES (3,'Popescu','Ion','03/02/1980','fiu');

INSERT INTO ingineri VALUES (5, 'electronica');
INSERT INTO ingineri VALUES (4, 'mecanica');
INSERT INTO ingineri VALUES (3, 'elecrotehnica');
INSERT INTO ingineri VALUES (2, 'chimie');
INSERT INTO ingineri VALUES (1, 'informatica');

INSERT INTO secretare VALUES (9, 250);
INSERT INTO secretare VALUES (8, 270);
INSERT INTO secretare VALUES (7, 290);
INSERT INTO secretare VALUES (6, 200);
INSERT INTO secretare VALUES (10, 150);

INSERT INTO achizitii VALUES (1, 1, 1, 1, '01/01/2010', 100, 5, 8);
INSERT INTO achizitii VALUES (2, 2, 2, 2, '01/01/2010', 200, 7, 9);
INSERT INTO achizitii VALUES (3, 3, 3, 3, '01/01/2010', 300, 4, 5);
INSERT INTO achizitii VALUES (4, 4, 4, 4, '01/01/2010', 400, 6, 1);
INSERT INTO achizitii VALUES (5, 5, 3, 5, '01/01/2010', 400, 5, 6);

INSERT INTO Compozitii VALUES(30,1,2);
INSERT INTO Compozitii VALUES(20,2,3);
INSERT INTO Compozitii VALUES(10,3,5);
INSERT INTO Compozitii VALUES(40,4,4);
INSERT INTO Compozitii VALUES(50,5,1);

INSERT INTO vanzari VALUES(1,1,1,1,'01/01/2010',10,3);
INSERT INTO vanzari VALUES(2,1,2,7,'01/01/2010',20,3);
INSERT INTO vanzari VALUES(3,1,3,9,'01/01/2010',30,3);
INSERT INTO vanzari VALUES(4,1,4,4,'01/01/2010',40,3);
INSERT INTO vanzari VALUES(5,1,5,6,'01/01/2010',50,3);
INSERT INTO vanzari VALUES(6,1,5,11,'01/01/2010',50,3);

INSERT INTO Proiecte VALUES(1,'Sistem de achizitie de date','02/12/2020',5000);
INSERT INTO Proiecte VALUES(2,'Modul 2','07/12/2020',4500);
INSERT INTO Proiecte VALUES(3,'Modul 3','10/12/2020',7000);
INSERT INTO Proiecte VALUES(4,'Modul 4','09/12/2020',9000);
INSERT INTO Proiecte VALUES(5,'Modul 5','05/12/2020',2500);

INSERT INTO activitati VALUES(1,1,5);
INSERT INTO activitati VALUES(2,3,5);
INSERT INTO activitati VALUES(3,5,5);
INSERT INTO activitati VALUES(4,7,5);
INSERT INTO activitati VALUES(5,9,5);

SELECT * FROM Proiecte

/*3.1*/
SELECT Nume,Prenume FROM Furnizori WHERE Adresa = 'Bucuresti' UNION SELECT Nume,Prenume FROM Clienti WHERE Adresa = 'Bucuresti';

/*3.2*/
SELECT * FROM Sectii,Angajati;

/*3.3*/
SELECT Nume,Prenume FROM Furnizori,Achizitii WHERE EXISTS (SELECT NrComponente FROM Achizitii WHERE NrComponente>200) AND (Furnizori.IdFurnizor=Achizitii.IdFurnizor);

/*3.4*/
SELECT IdAngajat FROM Achizitii WHERE IdComponenta=(SELECT IdComponenta FROM Componente WHERE Denumire='condensator')
SELECT Nume,Prenume FROM Angajati,Achizitii,Componente WHERE (Componente.Denumire='condensator') AND (Angajati.IdAngajat=Achizitii.IdAngajat) AND (Achizitii.IdComponenta=Componente.IdComponenta)
SELECT Angajati.Nume,Prenume,DataNasterii,Salariu FROM Sectii,Angajati WHERE Sectii.IdSectie=Angajati.IdSectie AND Sectii.Nume='Productie'

/*3.5*/
SELECT Nume,Prenume,Adresa FROM Furnizori WHERE IdFurnizor=(SELECT IdFurnizor FROM Achizitii WHERE IdComponenta=(SELECT IdComponenta FROM Componente WHERE Denumire='Rezistenta'))

/*3.6*/
SELECT Nume,Prenume,Adresa FROM Furnizori WHERE IdFurnizor=(SELECT IdFurnizor FROM Achizitii WHERE IdComponenta=(SELECT IdComponenta FROM Componente WHERE Denumire='Condensator')AND NrComponente>=150)

/*3.7*/
SELECT Nume,Prenume,Adresa FROM Clienti,Vanzari,Produse WHERE (Produse.Denumire='Monitor') AND (Clienti.IdClient=Vanzari.IdClient) AND (Produse.IdProdus=Vanzari.IdProdus) AND (Vanzari.NrProduse>=10)

/*3.8*/
SELECT Nume,Prenume FROM Angajati,Vanzari,Produse WHERE (Produse.Denumire='Monitor') AND (Vanzari.IdProdus=Produse.IdProdus) AND (Vanzari.IdAngajat=Angajati.IdAngajat)

/*3.9*/
SELECT Nume,Prenume,DataNasterii FROM Angajati,Proiecte,Activitati WHERE (Proiecte.Denumire='Sistem de achizitie de date') AND (Proiecte.IdProiect=Activitati.IdProiect) AND (Angajati.IdAngajat=Activitati.IdAngajat)

/*3.10*/
SELECT Sectii.Nume FROM Sectii,Proiecte,Angajati,Activitati WHERE (Angajati.IdAngajat=Activitati.IdAngajat) AND (Activitati.IdProiect=Proiecte.IdProiect) AND (Angajati.IdSectie=Sectii.IdSectie) AND (Proiecte.Denumire='Sistem de achizitie de date')

/*3.11*/
SELECT Nume,Prenume,DataNasterii FROM Angajati,Ingineri WHERE (Angajati.IdAngajat=Ingineri.IdAngajat) AND (Ingineri.Specialitate='electronica')

/*3.12*/
SELECT Angajati.Nume,Angajati.Prenume FROM Angajati,Sectii,Dependenti WHERE (Angajati.IdAngajat=Dependenti.IdAngajat) AND (Sectii.Nume='Productie') AND (Dependenti.Prenume='Ion') AND (Dependenti.GradRudenie='fiu')

/*3.13*/
SELECT Angajati.Nume,Angajati.Prenume FROM Angajati,Sectii,Ingineri WHERE (Angajati.IdAngajat=Ingineri.IdAngajat) AND (Sectii.Nume='Productie') AND (Angajati.IdSectie=Sectii.IdSectie)

/*3.14*/
SELECT Angajati.Nume,Angajati.Prenume FROM Angajati,Furnizori,Achizitii WHERE (Furnizori.Nume='Popescu') AND (Furnizori.Prenume='Razvan') AND (Furnizori.IdFurnizor=Achizitii.IdFurnizor) AND (Angajati.IdAngajat=Achizitii.IdAngajat)

/*3.15*/
SELECT Angajati.Nume,Angajati.Prenume FROM Angajati,Clienti,Vanzari WHERE (Angajati.IdAngajat=Vanzari.IdAngajat) AND (Clienti.IdClient=Vanzari.IdClient) AND (Clienti.Nume='Marinescu') AND Clienti.Prenume='Ion'

/*3.16*/
SELECT Produse.Denumire FROM Furnizori,Achizitii,Produse,Compozitii WHERE (Furnizori.IdFurnizor=Achizitii.IdFurnizor) AND Compozitii.IdProdus=Produse.IdProdus AND Compozitii.IdComponenta=Achizitii.IdComponenta AND Furnizori.Nume='Danescu' AND Furnizori.Prenume='Ovidiu'