yUv41HHi

1DROP DATABASE IF EXISTS BetanuloDB;
2GO
3CREATE DATABASE BetanuloDB;
4GO
5USE BetanuloDB;
6GO
7-- ########## Táblák ##########
8DROP TABLE IF EXISTS Osztaly
9CREATE TABLE Osztaly(
10[ID] int IDENTITY(1, 1) NOT NULL,
11	[Nev] varchar(30),
12	[Elhelyezkedes] char(20),
13	[evfolyam] int 
14);
15GO
16
17DROP TABLE IF EXISTS Tanulo
18CREATE TABLE Tanulo(
19[ID] int IDENTITY(1, 1) NOT NULL,
20	[Nev] varchar(30),
21	[Neme] varchar(20),
22	[osztalyID] int,
23	[lakhely] varchar(20),
24	[szuldatum] date, 
25	[Azonosito] varchar(30)
26
27);
28GO
29
30DROP TABLE IF EXISTS Naplo
31CREATE TABLE Naplo(
32[ID] int IDENTITY(1, 1) NOT NULL,
33	[TantargyID] int,
34	[Tanulo] int,
35	[jegy] int,
36	[datum] date
37);
38GO
39
40DROP TABLE IF EXISTS Tantargy
41CREATE TABLE Tantargy(
42[ID] int IDENTITY(1, 1) NOT NULL,
43	[Nev] varchar(20)
44);
45
46-- ######## Tárolt eljárások ############
47
48-- Osztaly INSERT
49IF OBJECT_ID ('insertIntoOsztaly', 'P') IS NOT NULL
50DROP PROC insertIntoOsztaly
51GO
52CREATE PROC insertIntoOsztaly @Nev varchar(30), @Elhelyezkedes char(20), @evfolyam int
53AS
54INSERT INTO Osztaly(Nev, Elhelyezkedes, evfolyam)
55VALUES (@Nev, @Elhelyezkedes, @evfolyam);
56GO
57-- Osztaly DELETE
58IF OBJECT_ID ('dbo.deleteFromOsztaly', 'P') IS NOT NULL
59DROP PROC dbo.deleteFromOsztaly
60GO
61CREATE PROC dbo.deleteFromOsztaly @ID int
62AS
63DELETE FROM Osztaly WHERE Osztaly.ID = @ID
64AND NOT EXISTS (SELECT osztalyID FROM Tanulo);
65GO
66-- Tanulo INSERT
67IF OBJECT_ID ('dbo.insertIntoTanulo', 'P') IS NOT NULL
68DROP PROC dbo.insertIntoTanulo
69GO
70CREATE PROC insertIntoTanulo @Nev varchar(30), @Neme varchar(20), @lakhely varchar(20), @szuldatum date, @Azonosito varchar(30)
71AS
72INSERT INTO Tanulo(Nev,Neme,lakhely,szuldatum,Azonosito)
73VALUES (@Nev, @Neme, @lakhely, @szuldatum, @Azonosito);
74GO
75-- Tanulo DELETE
76IF OBJECT_ID ('dbo.deleteFromTanulo', 'P') IS NOT NULL
77DROP PROC dbo.deleteFromTanulo
78GO
79CREATE PROC dbo.deleteFromTanulo @ID int
80AS
81DELETE FROM Tanulo WHERE Tanulo.ID = @ID;
82GO
83-- Naplo INSERT
84IF OBJECT_ID ('insertIntoNaplo', 'P') IS NOT NULL
85DROP PROC insertIntoNaplo
86GO
87CREATE PROC insertIntoNaplo @jegy int, @datum date
88AS
89INSERT INTO Naplo(jegy, datum)
90VALUES (@jegy, @datum);
91GO
92-- Naplo DELETE
93IF OBJECT_ID ('dbo.deleteFromNaplo', 'P') IS NOT NULL
94DROP PROC dbo.deleteFromNaplo
95GO
96CREATE PROC dbo.deleteFromNaplo @ID int
97AS
98DELETE FROM Naplo WHERE Naplo.ID = @ID;
99GO
100-- Tantargy INSERT
101IF OBJECT_ID ('insertIntoTantargy', 'P') IS NOT NULL
102DROP PROC insertIntoTantargy
103GO
104CREATE PROC insertIntoTantargy @Nev varchar(20)
105AS
106INSERT INTO Tantargy(Nev)
107VALUES (@Nev);
108GO
109-- Tantargy DELETE
110IF OBJECT_ID ('dbo.deleteFromTantargy', 'P') IS NOT NULL
111DROP PROC dbo.deleteFromTantargy
112GO
113CREATE PROC dbo.deleteFromTantargy @ID int
114AS
115DELETE FROM Naplo WHERE Naplo.ID = @ID;
116GO
117
118EXEC [dbo].[deleteFromOsztaly] @ID = 1;
119EXEC [dbo].[deleteFromTanulo] @ID = 1;
120EXEC [dbo].[insertIntoOsztaly] @Nev = 'István', @Elhelyezkedes = 'Székesfehérvár', @evfolyam = 15;
121EXEC [dbo].[insertIntoTanulo] @Nev = 'István', @Neme = 'Férfi', @lakhely = 'Székesfehérvár', @szuldatum = '1997-03-12', @Azonosito = '116-112-123-334'
122SELECT * FROM Osztaly;
123SELECT * FROM Tanulo;
124SELECT * FROM Tantargy;