Lab 4, Sql Server - rextester

Language: Layout:

CREATE TABLE Specialitate(
    IdSpecialitate INT PRIMARY KEY NOT NULL,
    NumeSpecialitate VARCHAR (60)
    )
    
INSERT INTO Specialitate VALUES
    (1001, 'Contabilitate'),
    (1002, 'Administrarea Aplicatiilor WEB'),
    (1003, 'Programarea si analiza produselor program'),
    (1004, 'Retele de Calculatoare'),
    (1005, 'Servicii Administrative si de Secretariat'),
    (1006, 'Administrarea bazelor de date'),
    (1007, 'Operator pentru Suportul Tehnic al Calculatoarelor');
    
SELECT IdSpecialitate AS [Codul specialităţii], NumeSpecialitate AS [Denumirea specialităţii]
FROM Specialitate AS [Lista specialităţilor];

CREATE TABLE Profesor(
    IdProfesor INT PRIMARY KEY NOT NULL,
    NumeProfesor VARCHAR (60)
    )
    
INSERT INTO Profesor VALUES
    (2001, 'Railean A.'),
    (2002, 'Obada L.'),
    (2003, 'Luncasu G.'),
    (2004, 'Damaschin I.'),
    (2005, 'Bratescu O.'),
    (2006, 'Tanasiev N.'),
    (2007, 'Usturoi I.'),
    (2008, 'Lupu D.'),
    (2009, 'Tomulescu L.'),
    (2010, 'Tudoreanu T.'),
    (2011, 'Melnic L.'),
    (2012, 'Golic L.'),
    (2013, 'Comarnițchi I.'),
    (2014, 'Dovgani I.'),
    (2015, 'Frunza O.'),
    (2016, 'Golub A.'),
    (2017, 'Andronic I.'),
    (2018, 'Jalba R.'),
    (2019, 'Nicsan A.'),
    (2020, 'Chinisciuc M.'),
    (2021, 'Suhan C.');

SELECT IdProfesor AS [Codul profesorului], NumeProfesor AS [Numele profesorului]
FROM Profesor AS [Lista profesorilor];

CREATE TABLE Grupa(
    IdGrupa INT PRIMARY KEY NOT NULL,
    NumeGrupa VARCHAR (60),
    AnulAdmiterii INT,
    IdProfesor INT NOT NULL FOREIGN KEY REFERENCES Profesor(IdProfesor),
    IdSpecialitate INT NOT NULL FOREIGN KEY REFERENCES Specialitate(IdSpecialitate)
    )
    
INSERT INTO Grupa VALUES
    (3001, 'P - 1923', 2019, 2003, 1003),
    (3002, 'P - 2013', 2020, 2007, 1003),
    (3003, 'P - 1742', 2017, 2011, 1003),
    (3004, 'W - 1831', 2018, 2012, 1002),
    (3005, 'W - 2011', 2020, 2002, 1002),
    (3006, 'W - 1921', 2019, 2020, 1002),
    (3007, 'R - 2011', 2020, 2014, 1004),
    (3008, 'R - 1833', 2018, 2004, 1004),
    (3009, 'R - 1742', 2017, 2017, 1004),
    (3010, 'C - 1921', 2019, 2001, 1001),
    (3011, 'C - 1742', 2017, 2008, 1001),
    (3012, 'C - 1833', 2018, 2015, 1001),
    (3013, 'S - 1921', 2019, 2018, 1005),
    (3014, 'S - 2013', 2020, 2005, 1005),
    (3015, 'S - 1833', 2018, 2010, 1005),
    (3016, 'O - 2013', 2020, 2019, 1007),
    (3017, 'O - 1831', 2018, 2016, 1007),
    (3018, 'O - 1921', 2019, 2021, 1007),
    (3019, 'B - 1923', 2019, 2009, 1006),
    (3020, 'B - 2013', 2020, 2013, 1006),
    (3021, 'B - 1831', 2018, 2006, 1006);
    
SELECT IdGrupa AS [Codul grupei], NumeGrupa AS [Numele grupei], AnulAdmiterii AS [Anul admiterii], IdProfesor AS [Codul profesorului], IdSpecialitate AS [Codul specialităţii]
FROM Grupa AS [Lista grupelor din colegiu];

CREATE TABLE Catedra(
    IdCatedra INT PRIMARY KEY NOT NULL,
    NumeCatedra VARCHAR (60),
    IdProfesor INT NOT NULL FOREIGN KEY REFERENCES Profesor(IdProfesor)
    )
    
INSERT INTO Catedra VALUES
    (4001, 'Administrarea aplicatiilor web si Retele de calculatoare', 2001),
    (4002, 'Informatică Aplicata', 2004),
    (4003, 'Economie', 2006),
    (4004, 'Limba si literatura română', 2008),
    (4005, 'Limbi străine', 2009),
    (4006, 'Matematică', 2010),
    (4007, 'Stiinte sociou - manistice', 2011),
    (4008, 'Biologie, chimie si fizică', 2012),
    (4009, 'Educatie fizica', 2013);

SELECT IdCatedra AS [Codul catedrei], NumeCatedra AS [Numele catedrei], IdProfesor AS [Codul profesorului]
FROM Catedra AS [Catedrele colegiului];

CREATE TABLE Disciplina(
    IdDisciplina INT PRIMARY KEY NOT NULL,
    NumeDisciplina VARCHAR (60),
    IdCatedra INT NOT NULL FOREIGN KEY REFERENCES Catedra(IdCatedra)
    )
    
INSERT INTO Disciplina VALUES
    (5001, 'Ed. fizica', 4009),
    (5002, 'POO', 4002),
    (5003, 'Ed. pentru societate', 4007),
    (5004, 'Istoria', 4007),
    (5005, 'Tehnici de comunicare', 4004),
    (5006, 'L. romana', 4004),
    (5007, 'L. engleza', 4005),
    (5008, 'Geografie', 4007),
    (5009, 'Matematica', 4006),
    (5010, 'SQL', 4002),
    (5011, 'Tehnologii de comunicare', 4004),
    (5012, 'Informatica', 4002),
    (5013, 'Dezvoltarea aplicatiilor WEB', 4001),
    (5014, 'Ment.ret.de calc.', 4003),
    (5015, 'Admin.afac.', 4002),
    (5016, 'Dr.afac.', 4002),
    (5017, 'Utiliz.BD', 4001),
    (5018, 'prog.viz.', 4001);

SELECT IdDisciplina AS [Codul disciplinei], NumeDisciplina AS [Numele disciplinei], IdCatedra AS [Codul catedrei]
FROM Disciplina AS [Disciplinile studiate in colegiu];

CREATE TABLE Raion(
    IdRaion INT PRIMARY KEY NOT NULL,
    NumeRaion VARCHAR (60)
    )
    
INSERT INTO Raion VALUES
    (6001, 'O. Chisinau'),
    (6002, 'R. Orhei'),
    (6003, 'R. Cahul'),
    (6004, 'R. Hincesti'),
    (6005, 'R. Ungheni'),
    (6006, 'R. Ialoveni'),
    (6007, 'R. Soroca'),
    (6008, 'R. Singerei'),
    (6009, 'R. Straseni'),
    (6010, 'R. Falesti'),
    (6011, 'R. Causeni'),
    (6012, 'R. Floresti'),
    (6013, 'R. Drochia'),
    (6014, 'R. Anenii Noi'),
    (6015, 'R. Edinet'),
    (6016, 'R. Calarasi'),
    (6017, 'R. Criuleni'),
    (6018, 'R. Briceni'),
    (6019, 'R. Telenesti'),
    (6020, 'R. Stefan Voda'),
    (6021, 'R. Riscani'),
    (6022, 'R. Nisporeni'),
    (6023, 'R. Cantemir'),
    (6024, 'R. Cimislia'),
    (6025, 'R. Glodeni'),
    (6026, 'R. Ocnita'),
    (6027, 'R. Leova'),
    (6028, 'R. Rezina'),
    (6029, 'R. Taraclia'),
    (6030, 'R. Donduseni'),
    (6031, 'R. Soldanesti'),
    (6032, 'R. Dubasari'),
    (6033, 'R. Basarabeasca');
    
SELECT IdRaion AS [Codul raionului], NumeRaion AS [Denumirea raionului]
FROM Raion AS [Raioane];

CREATE TABLE Localitate(
    IdLocalitate INT PRIMARY KEY NOT NULL,
    NumeLocalitate VARCHAR (60),
    IdRaion INT NOT NULL FOREIGN KEY REFERENCES Raion(IdRaion)
    )
    
INSERT INTO Localitate VALUES
    (7001, 'Chisinau', 6001),
    (7002, 'Durlesti', 6001),
    (7003, 'Bubuieci', 6001),
    (7004, 'Bacioi', 6001),
    (7005, 'Sangera', 6001),
    (7006, 'Mitoc', 6002),
    (7007, 'Cotihana', 6003),
    (7008, 'Miciurin', 6004),
    (7009, 'Cetireni', 6005),
    (7010, 'Sociteni', 6006),
    (7011, 'Rublenita', 6007),
    (7012, 'Vranesti', 6008),
    (7013, 'Roscani', 6009),
    (7014, 'Socii Noi', 6010),
    (7015, 'Zaim', 6011),
    (7016, 'Vascauti', 6012);
    
SELECT IdLocalitate AS [Codul localitatii], NumeLocalitate AS [Denumirea localitatii], IdRaion AS [Codul raionului]
FROM Localitate AS [Lista localitatilor];

CREATE TABLE Student(
    IdStudent INT PRIMARY KEY NOT NULL,
    NumeStudent VARCHAR (60),
    PreumeStudent VARCHAR (60),
    PatronimicStudent VARCHAR (60),
    SexStudent VARCHAR (60),
    DataNasterii VARCHAR (11),
    NrTelefon VARCHAR (10),
    IdGrupa INT NOT NULL FOREIGN KEY REFERENCES Grupa(IdGrupa),
    IdLocalitate INT NOT NULL FOREIGN KEY REFERENCES Localitate(IdLocalitate)
    )
    
INSERT INTO Student VALUES
    (8001, 'Aura', 'Alexiuc', 'Alexei', 'Femenin', '2002.02.08', '078539275', 3001, 7016),
    (8002, 'Semion', 'Dubrovschii', 'Iuri', 'Masculin', '2003.10.08', '078956565', 3018, 7002),
    (8003, 'Vladislav', 'Garabajiu', 'Grigore', 'Masculin', '2002.06.10', '068164937', 3003, 7001),
    (8004, 'Ana', 'Oprea', 'Vitalie', 'Femenin', '2003.04.12', '068647444', 3013, 7004),
    (8005, 'Andrei', 'Tataru', 'Andrei', 'Masculin', '2001.02.20', '079461098', 3019, 7002),
    (8006, 'Tudor', 'Grati', 'Mihail', 'Masculin', '2002.11.11', '078539275', 3006, 7005),
    (8007, 'Dumitru', 'Cobzac', 'Andrei', 'Masculin', '2002.12.09', '067578362', 3007, 7008),
    (8008, 'Vladimir', 'Saharnean', 'Iurie', 'Masculin', '2003.07.08', '069878789', 3021, 7001),
    (8009, 'Daniela', 'Melnic-Antoci', 'Sergiu', 'Femenin', '2004.10.10', '079537626', 3009, 7002),
    (8010, 'Alina', 'Lungu', 'Mihail', 'Femenin', '2000.05.08', '069648372', 3016, 7009),
    (8011, 'Cristina', 'Vasilache', 'Petru', 'Femenin', '2002.12.18', '068789098', 3011, 7001),
    (8012, 'Cătălin', 'Gorbatovschi', 'Iurie', 'Masculin', '2002.11.28', '060981235', 3012, 7003),
    (8013, 'Alexandrina', 'Frunze', 'Vasile', 'Femenin', '2001.10.30', '078612211', 3005, 7007),
    (8014, 'Elena', 'Tricolici', 'Ghenadie', 'Femenin', '2004.05.22', '079675654', 3014, 7010),
    (8015, 'Corina', 'Rusu', 'Oleg', 'Femenin', '2002.03.02', '078165435', 3015, 7006),
    (8016, 'Cristian', 'Fetco', 'Grigore', 'Masculin', '2001.02.21', '076220221', 3016, 7004),
    (8017, 'Sergiu', 'Stambol', 'Timofei', 'Masculin', '2004.12.17', '060989062', 3017, 7015),
    (8018, 'Dumitru', 'Ghimp', 'Nicolae', 'Masculin', '2003.04.12', '068962342', 3003, 7001),
    (8019, 'Alexandru', 'Negru', 'Ion', 'Masculin', '2000.09.09', '069876845', 3001, 7012),
    (8020, 'Svetlana', 'Cazacu', 'Nicolae', 'Femenin', '2002.08.08', '078609890', 3020, 7013),
    (8021, 'Ghenadie', 'Andronic', 'Andrei', 'Masculin', '2004.07.07', '069015263', 3021, 7007),
    (8022, 'Leonid', 'Juc', 'Alexei', 'Masculin', '2001.02.03', '069654987', 3012, 7014),
    (8023, 'Maria', 'Melnic', 'Ilie', 'Femenin', '2001.10.28', '069123451', 3003, 7011),
    (8024, 'Valeria', 'Micleușan', 'Valeriu', 'Femenin', '2004.05.17', '078657096', 3004, 7002),
    (8025, 'Eduard', 'Leca', 'Nicolae', 'Masculin', '2002.09.24', '069807987', 3015, 7001),
    (8026, 'Viorel', 'Pahome', 'Viorel', 'Masculin', '2000.07.30', '060591745', 3016, 7003),
    (8027, 'Pavel', 'Costandoi', 'Pavel', 'Masculin', '2004.04.04', '079756790', 3011, 7001),
    (8028, 'Stela', 'Botnarencu', 'Ilie', 'Femenin', '2003.05.21', '079797970', 3008, 7005),
    (8029, 'Lucian', 'Burjacovschi', 'Valeriu', 'Masculin', '2003.11.26', '069051284', 3001, 7011),
    (8030, 'Ionela', 'Ciobanu', 'Ion', 'Femenin', '2001.01.25', '069765412', 3020, 7008);
    
SELECT IdStudent AS [Codul studentului], NumeStudent AS [Numele studentului], PreumeStudent AS [Prenumele studentului], 
       PatronimicStudent AS [Patronimicul studentului], SexStudent AS [Sexul studentului], DataNasterii AS [Data nasterii], 
       NrTelefon AS [Nr. telefon mobil], IdGrupa AS [Codul grupei], IdLocalitate AS [Codul localitatii]
FROM Student AS [Lista studentilor];

CREATE TABLE DisciplinaPeGrupa(
    IdDisciplinaPeGrupa INT PRIMARY KEY NOT NULL,
    IdGrupa INT NOT NULL FOREIGN KEY REFERENCES Grupa(IdGrupa),
    IdDisciplina INT NOT NULL FOREIGN KEY REFERENCES Disciplina(IdDisciplina),
    IdProfesor INT NOT NULL FOREIGN KEY REFERENCES Profesor(IdProfesor)
    )
    
INSERT INTO DisciplinaPeGrupa VALUES
    (9001, 3001, 5002, 2011),
    (9002, 3004, 5013, 2009),
    (9003, 3007, 5014, 2017),
    (9004, 3010, 5015, 2021),
    (9005, 3013, 5016, 2005),
    (9006, 3016, 5017, 2012),
    (9007, 3019, 5018, 2006);

SELECT IdDisciplinaPeGrupa AS [Cod], IdGrupa AS [Codul grupei], IdDisciplina AS [Codul disciplinei], IdProfesor AS [Codul profesorului]
FROM DisciplinaPeGrupa AS [Disciplinile studiate in fiecare grupa];

CREATE TABLE Note(
    IdNote INT PRIMARY KEY NOT NULL,
    IdStudent INT NOT NULL FOREIGN KEY REFERENCES Student(IdStudent),
    IdDisciplina INT NOT NULL FOREIGN KEY REFERENCES Disciplina(IdDisciplina),
    Data VARCHAR (10),
    Nota INT
    )
    
INSERT INTO Note VALUES
    (11001, 8001, 5002, '2021.02.25', 10),
    (11002, 8002, 5002, '2021.02.24', 9),
    (11003, 8003, 5002, '2021.02.23', 8),
    (11004, 8004, 5002, '2021.02.22', 10),
    (11005, 8005, 5002, '2021.02.19', 6),
    (11006, 8006, 5002, '2021.02.18', 9),
    (11007, 8007, 5002, '2021.02.17', 6),
    (11008, 8008, 5002, '2021.02.16', 9),
    (11009, 8009, 5002, '2021.02.15', 7),
    (11010, 8010, 5002, '2021.02.12', 7),
    (11011, 8011, 5002, '2021.02.11', 10),
    (11012, 8012, 5002, '2021.02.10', 5),
    (11013, 8013, 5002, '2021.02.09', 6),
    (11014, 8014, 5002, '2021.02.08', 9),
    (11015, 8015, 5002, '2021.02.05', 2),
    (11016, 8016, 5002, '2021.02.04', 6),
    (11017, 8017, 5002, '2021.02.03', 8),
    (11018, 8018, 5002, '2021.02.02', 9),
    (11019, 8019, 5002, '2021.02.01', 8),
    (11020, 8020, 5002, '2021.01.29', 7),
    (11021, 8021, 5002, '2021.01.28', 7),
    (11022, 8022, 5002, '2021.01.27', 9),
    (11023, 8023, 5002, '2021.01.26', 10),
    (11024, 8024, 5002, '2021.01.25', 4),
    (11025, 8025, 5002, '2021.01.22', 7),
    (11026, 8026, 5002, '2021.01.21', 7),
    (11027, 8027, 5002, '2021.01.20', 8),
    (11028, 8028, 5002, '2021.01.19', 7),
    (11029, 8029, 5002, '2021.01.18', 9),
    (11030, 8030, 5002, '2021.01.15', 10);
  
SELECT IdNote AS [Codul notei], IdStudent AS [Codul studentului], IdDisciplina AS [Codul disciplinei], 
       Data AS [Data notei primite], Nota
FROM Note AS [Lista notelor studentilor la disciplinile studiate];

View schema
Execution time: 0,19 sec, rows selected: 192, rows affected: 192, absolute service time: 0,44 sec, absolute service time: 0,47 sec