--create database CentruDE420;

use CentruDE420;
/*
------ crearea tabelelor ------

create table specialitati(

	idSpec int primary key not null,
	numeSpec char(15) not null

);

create table profesori(

	idProf int primary key not null,
	numeProf char(25) not null

);

create table grupe(

	idGrupa int primary key not null,
	numeGrupa char(6) not null,
	anul int not null,
	idSpec int references specialitati(idSpec) not null,
	idProf int references profesori(idProf) not null

);

create table discipline(

	idDisciplina int primary key not null,
	denumireDisciplina char(15) not null,
	idProf int references profesori(idProf) not null

);


create table studenti(

	idStud int primary key not null,
	numeStud char(15) not null,
	prenumeStud char(15) not null,
	sexStud char not null,
	dataStud date not null,
	telefonStud char(10) not null,
	idGrupa int references grupe(idGrupa) not null

);



insert into specialitati values
	(2, 'Contabilitate'),
	(4, 'Finante'),
	(1, 'Informatica'),
	(3, 'SB');

insert into profesori values
	(1, 'Arnaut G.'),
	(2, 'Popescu V.'),
	(3, 'Scurtu D.');

insert into grupe values
	(1,'I-1511', 2016, 1, 2),
	(2,'C-1421', 2017, 2, 1),
	(3,'F-1241', 2018, 3, 3);


insert into discipline values
	(1,'Matematica', 1),
	(2,'Istoria',3),
	(3,'SGBD',2);

insert into studenti values
	(1,'Balan','Petru','m','1996-05-17','079564122', 1),
	(2,'Rusu','Ana','f','1995-02-23','057684971', 3),
	(3,'Cazacu','Ion','m','1994-11-07','05754159', 2),
	(4,'Rotaru','Maria','f','1997-09-18','069156945', 1);


*/

--select *
--	from studenti;
--select *
--	from grupe;
--select *
--	from profesori;
--select *
--	from specialitati;
--select *
--	from discipline;

create view vStud as
select numeStud, prenumeStud
	from studenti as s inner join grupe as g on s.idGrupa = g.idGrupa
		where numeGrupa = 'I-1511';

select numeProf
	from profesori as p inner join discipline as d on p.idProf = d.idProf inner join grupe as g on g.idProf = d.idProf inner join studenti as s on s.idGrupa = g.idGrupa
		where s.numeStud = 'Rotaru';

-- numele prof care o invata pe rotaru maria la sgbd

select * vStud;

exec sp_changedbowner 'sa';