-- Creare tabela STUDENȚI
CREATE TABLE STUDENȚI (
Idstud INT PRIMARY KEY,
Grupa VARCHAR(10),
Nume VARCHAR(50),
Prenume VARCHAR(50),
Annast INT,
Adresa VARCHAR(100),
Tel VARCHAR(15)
);
-- Introducere date în tabela STUDENȚI
INSERT INTO STUDENȚI (Idstud, Grupa, Nume, Prenume, Annast, Adresa, Tel)
VALUES
(1, 'IT14', 'Levitchi', 'Iulia', 1997, 'Cahul', '22557'),
(2, 'IT14', 'Ursachi', 'Daniel', 1998, 'Gotești', '58741'),
(3, 'IM14', 'Blaja', 'Maria', 1994, 'Cahul', '24598'),
(4, 'IM14', 'Butnaru', 'Ion', 1995, 'Leova', '32554'),
(5, 'IMA14', 'Drangoi', 'Elena', 1991, 'Cahul', '29898');
SELECT Idcatedra, Dencatedra, COUNT([Link]) "Numar Profesori"
FROM CATEDRA c, PROFESOR p
WHERE [Link] = [Link]
GROUP BY Idcatedra, Dencatedra
HAVING COUNT([Link]) >= 2;
-- Creare tabela DISCIPLINA
CREATE TABLE DISCIPLINA (
Iddiscip VARCHAR(10) PRIMARY KEY,
Dendiscip VARCHAR(100),
Nrore INT,
Nrcredite INT,
Idprof VARCHAR(10)
);
-- Introducere date în tabela DISCIPLINA
INSERT INTO DISCIPLINA (Iddiscip, Dendiscip, Nrore, Nrcredite, Idprof)
VALUES
('D1', 'Algebra', 120, 4, 'P4'),
('D2', 'TCI', 180, 6, 'P3'),
('D3', 'Engleza', 90, 3, 'P10'),
('D4', 'Baze de date', 120, 4, 'P2'),
('D5', 'Statistica', 60, 2, 'P3'),
('D6', 'Etica profesionala', 90, 3, 'P5'),
('D7', 'Ingineria programarii', 180, 6, 'P1'),
('D8', 'PAC', 175, 5, 'P8'),
('D9', 'GAC', 120, 4, 'P7'),
('D10', 'SIM', 120, 4, 'P2'),
('D11', 'Teoria economica', 90, 3, 'P6'),
('D12', 'PGI', 175, 5, 'P9');
-- Creare tabela PROFESOR
CREATE TABLE PROFESOR (
Idprof VARCHAR(10) PRIMARY KEY,
Nume VARCHAR(50),
Prenume VARCHAR(50),
Titlust VARCHAR(50),
Gradst VARCHAR(10),
Idcated VARCHAR(10)
);
-- Introducere date în tabela PROFESOR
INSERT INTO PROFESOR (Idprof, Nume, Prenume, Titlust, Gradst, Idcated)
VALUES
('P1', 'Munteanu', 'Ivan', 'Asistent', NULL, 'C1'),
('P2', 'Bîrlea', 'Svetlana', 'Lector', 'Doctor', 'C1'),
('P3', 'Popovici', 'Ilona', 'Lector', NULL, 'C2'),
('P4', 'Bagrin', 'Dumitru', 'Lector', NULL, 'C2'),
('P5', 'Todos', 'Irina', 'Conferențiar ', 'Doctor','C3'),
('P6', 'Antohi', 'Liudmila', 'Lector', NULL, 'C3'),
('P7', 'Boboc', 'Ana', 'Lector', NULL, 'C1'),
('P8', 'Bunea', 'Marina', 'Lector', 'Doctor', 'C1'),
('P9', 'Zarișneac', 'Natalia', 'Lector', NULL, 'C3'),
('P10', 'Bozbei', 'Valentine', 'Asistent', NULL, 'C4');
-- Creare tabela CATEDRA
CREATE TABLE CATEDRA (
Idcatedra VARCHAR(10) PRIMARY KEY,
Dencatedra VARCHAR(100),
Sefcatedra VARCHAR(50)
);
-- Introducere date în tabela CATEDRA
INSERT INTO CATEDRA (Idcatedra, Dencatedra, Sefcatedra)
VALUES
('C1', 'Inginerie și științe Aplicate', 'Rumeus I.'),
('C2', 'Informatica și Matematica', 'Bagrin D.'),
('C3', 'EMAS', 'Rosca S.'),
('C4', 'Limbi moderne', 'Fugiji M.');
-- Creare tabela REUSITA
CREATE TABLE REUSITA (
Idr VARCHAR(10) PRIMARY KEY,
Idstud INT,
Iddiscip VARCHAR(10),
Nota INT,
Dataexam DATE
);
-- Introducere date în tabela REUSITA
INSERT INTO REUSITA (Idr, Idstud, Iddiscip, Nota, Dataexam)
VALUES
('r1', 1, 'D1', 5, '2014-12-29'),
('r2', 2, 'D1', 10, '2014-12-29'),
('r3', 3, 'D1', 6, '2014-12-29'),
('r4', 4, 'D1', 8, '2014-12-29'),
('r5', 5, 'D1', 9, '2014-12-29'),
('r32', 1, 'D2', 8, '2014-12-27'),
('r33', 2, 'D2', 8, '2014-12-27'),
('r34', 3, 'D2', 8, '2014-12-27'),
('r35', 4, 'D2', 7, '2014-12-27'),
('r36', 5, 'D2', 5, '2014-12-27'),
('r63', 1, 'D3', 5, '2015-01-16'),
('r64', 2, 'D3', 7, '2015-01-16'),
('r65', 3, 'D3', NULL, '2015-01-16'), -- Nota lipsă
('r66', 4, 'D3', 9, '2015-01-16'),
('r67', 5, 'D3', 6, '2015-01-16'),
('r94', 1, 'D6', 10, '2014-12-14'),
('r95', 2, 'D6', 10, '2014-12-14'),
('r96', 3, 'D6', 10, '2014-12-14'),
('r97', 4, 'D7', 5, '2014-12-26'),
('r98', 5, 'D7', 6, '2014-12-26'),
('r125', 1, 'D12', 6, '2014-12-16'),
('r126', 2, 'D12', 7, '2014-12-16'),
('r127', 3, 'D12', 8, '2014-12-16'),
('r128', 4, 'D12', 9, '2014-12-16'),
('r129', 5, 'D12', 10, '2014-12-16');