2021.02.25 Lab 4, Sql Server

2021.02.25 Lab 4

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.');

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, 'W - 1831', 2018, 2002, 1002),
    (3003, 'R - 2011', 2020, 2004, 1004),
    (3004, 'C - 1742', 2017, 2001, 1001),
    (3005, 'S - 1833', 2018, 2005, 1005),
    (3006, 'O - 1921', 2019, 2007, 1007),
    (3007, 'B - 2013', 2020, 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);

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, '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', '02.10.2002', '078539275', 3001, 7006);
    
SELECT * FROM Student;


run \| edit \| history \| help	0