/*==============================================================*/
/* DBMS name:      Microsoft SQL Server 2008                    */
/* Created on:     11/3/2017 8:56:29 PM                         */
/*
 I. kollokvium megoldasa -2017-11-10
                                                                */
/*==============================================================*/

/*
1.	Hozza létre a fenti bal oldali adatbázistáblákat a Hallgatóeredmények
 adatbázisban. Az adatbázis nem létezik. Tiltsa le a vizsgaidőszak 
 törlését, ha van nyilvántartott eredménye, a Vizsgaidőszak 
 kulcsérték-változása pedig propagálódjon a gyerektáblában. 
 Hallgató törlése esetén törlődjenek az osztályzatai, az Indexszám
 változtatások pedig csak akkor legyenek lehetségesek, ha nem kötődnek
 hozzájuk érdemjegyek. A JegyBeírDátum alapértelmezése az aktuális dátum,
 a Jegy értéke nem haladhatja meg a 10-et, a VizsgaIdVége értékének
 nagyobbnak kell lennie a VizsgaIdKezdete értékétől. (5 pont)


*/

use master
drop database Hallgatóeredmények;

create database Hallgatóeredmények;
go

use Hallgatóeredmények;
go

/*==============================================================*/
/* Table: HALLGATO                                              */
/*==============================================================*/
create table HALLGATO (
   INDEXSZAM            char(6)              not null,
   VEZETEKNEV           varchar(15)          not null,
   NEV                  varchar(15)          not null,
   SZULDATUM            date                 not null,
   constraint HallgAzon primary key (INDEXSZAM)
)
go

/*==============================================================*/
/* Table: TANULMANYIPROGRAM                                     */
/*==============================================================*/
create table TANULMANYIPROGRAM (
   TANULMPROGAZON       smallint             not null,
   TANULMPROGNEV        varchar(50)          not null,
   TANULMPROGBEVEZEVE   numeric(4,0)         not null,
   TANULMPROGERVVEGE    decimal(4,0)         not null,
   constraint TPAzon primary key (TANULMPROGAZON)
)
go

/*==============================================================*/
/* Table: VIZSGAIDOSZAK                                         */
/*==============================================================*/

-- Hallgatoi megoldas:
create table VIZSGAIDOSZAK (
   ISKOLAEV             char(5)              not null,
   VIZSGAIDOSZAK        smallint             not null,
   VIZSIDKEZDETE        date                 not null,
   VIZSIDVEGE           date                 not null
	check (vizsidvege > vizsidkezdete),
   constraint VizsgaIdAzon primary key (ISKOLAEV, VIZSGAIDOSZAK)
)
go

-- Eredeti megoldas:
create table VIZSGAIDOSZAK (
   ISKOLAEV             char(5)              not null,
   VIZSGAIDOSZAK        smallint             not null,
   VIZSIDKEZDETE        date                 not null,
   VIZSIDVEGE           date                 not null,
   constraint VizsgaIdAzon primary key (ISKOLAEV, VIZSGAIDOSZAK),
   constraint ck_vizsgahataroklogikaja check (vizsidvege > vizsidkezdete)
)
go


/*==============================================================*/
/* Table: TARGY                                                 */
/*==============================================================*/
create table TARGY (
   TANULMPROGAZON       smallint             not null,
   TARGYAZON            int                  not null,
   TARGYNEV             varchar(30)          not null,
   FELEV                smallint             not null,
   HETIELOADASSZAM      smallint             not null,
   HETILABORSZAM        smallint             null,
   constraint TAzon primary key (TANULMPROGAZON, TARGYAZON),
   constraint Tartozik foreign key (TANULMPROGAZON)
      references TANULMANYIPROGRAM (TANULMPROGAZON)
)
go

/*==============================================================*/
/* Table: EREDMENY                                              */
/*==============================================================*/
create table EREDMENY (
   TANULMPROGAZON       smallint             not null,
   TARGYAZON            int                  not null,
   INDEXSZAM            char(6)              not null,
   ISKOLAEV             char(5)              not null,
   VIZSGAIDOSZAK        smallint             not null,
   JEGY                 numeric(2,0)         not null
		constraint ck_jegyfelsokorlat check (jegy < 11),
   JEGYBEIRDATUM        date                 not null
		constraint df_jegybeirdatum default getdate(),
   constraint EredmAzon 
   primary key (TANULMPROGAZON, TARGYAZON, INDEXSZAM, ISKOLAEV, VIZSGAIDOSZAK),
   constraint VizsgaidőszakOsztályzata foreign key (ISKOLAEV, VIZSGAIDOSZAK)
      references VIZSGAIDOSZAK (ISKOLAEV, VIZSGAIDOSZAK)
      on delete no action on update cascade,
   constraint Vizsgázik foreign key (INDEXSZAM)
      references HALLGATO (INDEXSZAM)
      on delete cascade on update no action
)
go

/*
2./Szúrjon be utólagosan egy 1 jel hosszúságú alfanumerikus 
  Neme (magyarázat:Gender) oszlopot a Hallgató táblába, amelynek 
  alapértelmezett értéke ’F’. Az mező (oszlop) a következő értékeket 
  tartalmazhatja: ’F’, ’f’, ’N’ és ’n’. (1 pont).
*/

-- Elso valtozat:
alter table hallgato
	add Neme char(1) default 'F',
	constraint ck_NemeErtekei check (neme in ('F','f','N','n'));
go

alter table hallgato
	drop constraint DF__HALLGATO__Neme__0CBAE877;
go

alter table hallgato
	drop constraint ck_NemeErtekei;
go
alter table hallgato
	drop column neme;
go

-- Masodik valtozat:
alter table hallgato
	add Neme char(1)  null 
	constraint df_neme default 'F',
	constraint ck_NemeErtekei check (neme in ('F','f','N','n'));
go

alter table hallgato
	drop constraint df_neme;
go

alter table hallgato
	drop constraint ck_NemeErtekei;
go

alter table hallgato
	drop column neme;
go


-- Harmadik valtozat:
alter table hallgato
	add Neme char(1)  null 
	constraint df_neme default 'F'
	constraint ck_NemeErtekei check (neme in ('F','f','N','n'));
go

/*
3./Módosítsa a Hallgató SzülDátum mezőjét dátum-idő típusúra, 
	és ne legyen kötelező kitölteni. (1 pont).
*/

alter table hallgato
	alter /*column*/ SZULDATUM datetime null;
go

/*
4./Készítsen utólagos megszorítást a kitöltendő Jegy mezőre, 
	uc_jegyalsókorlát néven: a Jegy értéke minimálisan 5 lehet. 
	(1 pont).
*/

alter table dbo.EREDMENY
	add constraint uc_jegyalsókorlát
	check (jegy>4);
go

-- hallgatoi megoldas (nem mukodik):
alter table dbo.EREDMENY
	drop constraint uc_jegyalsókorlát
	add constraint uc_jegyalsókorlát
	check (jegy>4);
go





/*
5./Módosítsa a kitöltendő Jegy mező felső értékkorlátját 
	10-ről 9-re. (1 pont).
*/

-- Elso valtozat (ha letezik ck_jegyfelsokorlat):
alter table dbo.EREDMENY
	drop constraint ck_jegyfelsokorlat;
go

alter table dbo.EREDMENY
	add constraint ck_jegyfelsokorlat check (jegy < 10);
go

-- Masodik valtozat (ha nem letezik ck_jegyfelsokorlat):

alter table dbo.EREDMENY
	add constraint ck_jegyfelsokorlat check (jegy < 10);
go


/*
6./ Töltse fel a létrehozott (bal oldali) táblákat egy-egy sorral, 
	a megfelelő sorrendben (a hivatkozási épség - referential 
	integrity - megsértése nélkül, TanulmProgAzon=1 és TárgyAzon=1). 
	Ügyeljen arra, hogy a táblák megváltoztak a létrehozásuk óta! 
	(2 pont)
*/	

insert HALLGATO
	values ('E-495','Mekk','Elek','1994-10-01','F');
go

select * from HALLGATO


insert VIZSGAIDOSZAK
	values ('17/18',2,'2017-10-25','2017-11-03');
go

select * from VIZSGAIDOSZAK	

/* sledece dve tabele se ne moraju popunjavati na kolokvijumu-pocetak*/
insert TANULMANYIPROGRAM
	values (1,'Tanulmanyiprogram-1',2014,2021);
go

insert TARGY
	values (1,1,'Adatbazisok',3,2,2);
go

/* sledece dve tabele se ne moraju popunjavati na kolokvijumu-pocetak*/

insert EREDMENY
	values (1,1,'E-495','17/18',2,9,default);
go

insert EREDMENY (TANULMPROGAZON,TARGYAZON,INDEXSZAM,ISKOLAEV,
			VIZSGAIDOSZAK,JEGY)
	values (1,1,'E-495','17/18',2,9);
go

select * from EREDMENY
delete EREDMENY


/*
7./ Írjon be egy új sort az Eredmény táblába 10-es osztályzattal, 
	a korlátok törlése és módosítása nélkül! Mi az új sor beírásának feltétele
	az eredménytáblába? (1 pont)
*/

/* 
	Az új sor beírása az Eredmény táblába megköveteli 
	a) egy új sor beírását a Hallgató táblába
	VAGY
	b) egy új sor beírását a Vizsgaidőszak táblába
	VAGY
	c) egy új sor beírását a Tárgy táblába
*/

insert HALLGATO
values ('E-494','Jo','Aron','1994-05-25','F');
go
alter table dbo.EREDMENY
	nocheck constraint ck_jegyfelsokorlat;
insert EREDMENY
	values (1,1,'E-494','17/18',2,10,default);
go	
alter table dbo.EREDMENY
	check constraint ck_jegyfelsokorlat;
go

select * from EREDMENY	


/*
8./ Törölje az Eredmény táblából a 10-es osztályzatot 
	tartalmazó sorokat! (1 pont).
*/

delete EREDMENY
	where JEGY=10;
go

/*
9./ Módosítsa az Eredmény tábla 9-es osztályzatot tartalmazó soraiban 
	a dátumot az aktuális dátumra, az osztályzatot pedig 10-re! 
	(1 pont)
*/

alter table dbo.EREDMENY
	nocheck constraint ck_jegyfelsokorlat;
go

update EREDMENY
	set JEGYBEIRDATUM=GETDATE(),
		JEGY=10
	where JEGY=9;
go

alter table dbo.EREDMENY
	check constraint ck_jegyfelsokorlat;
	
/*
10./ Hozzon létre nézettáblát Hallgatónézet néven, amely a 
	következő adatokat tartalmazza (az adott sorrendben): 
	Vezetéknév, Név, Indexszám, SzülDátum. (1 pont).
*/

create view Hallgatonezet (Vezetéknév,Név,Indexszám,Szüldátum) as
	select VEZETEKNEV,NEV,INDEXSZAM,SZULDATUM 
		from  dbo.HALLGATO;
go
select * from Hallgatonezet

drop view Hallgatonezet

create view Hallgatonezet (Hallgató_vezetékneve,Hallgató_Neve,
				Leckekönyv_száma,Születési_dátum) as
	select VEZETEKNEV,NEV,INDEXSZAM,SZULDATUM 
		from  dbo.HALLGATO;
go

create view Hallgatonezet ("Hallgató vezetékneve","Hallgató neve",
				"Leckekönyv száma","Születési dátum") as
	select VEZETEKNEV,NEV,INDEXSZAM,SZULDATUM 
		from  dbo.HALLGATO;
go

-- A pótkollokviumról
create view TanulmányiProgramnezet ("Tanulmányi program neve",
	"Programazonosító","Bevezetés éve","Érvényesség vége",
	"Érvényességi évek száma") as
	select TANULMPROGNEV,TANULMPROGAZON,TANULMPROGBEVEZEVE,
		TANULMPROGERVVEGE,TANULMPROGERVVEGE-TANULMPROGBEVEZEVE 
		from  dbo.TANULMANYIPROGRAM;
go

select * from TanulmányiProgramnezet