CREATE DATABASE Folyoiratok
GO
USE Folyoiratok
GO
CREATE TABLE [ELOFIZ](
	[EKOD] [smallint] NOT NULL,
	[EKDATUM] [date] NOT NULL CONSTRAINT [DF_ELOFIZ_EKDATUM]  DEFAULT (getdate()),
	[EVDATUM] [date] NOT NULL,
	[FAZON] [smallint] NOT NULL,
	[EFIZDB] [smallint] NOT NULL
) ON [PRIMARY]

GO
CREATE TABLE [ETORZS](
	[EKOD] [smallint] IDENTITY(1,10) NOT NULL,
	[ENEV] [nchar](40) NOT NULL,
	[ESZDAT] [date] NOT NULL,
	[EIRSZ] [char](4) NOT NULL,
	[ETELEP] [nvarchar](50) NOT NULL,
	[EUTCA] [nvarchar](100) NOT NULL,
 CONSTRAINT [PK_ETORZS] PRIMARY KEY CLUSTERED 
(
	[EKOD] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
CREATE TABLE [FTORZS](
	[FAZON] [smallint] IDENTITY(1,1) NOT NULL,
	[FTIPUS] [nchar](1) NOT NULL,
	[FCIM] [nvarchar](50) NOT NULL,
	[FKIAD] [nvarchar](50) NOT NULL,
	[FHDIJ] [smallmoney] NOT NULL,
 CONSTRAINT [PK_FTORZS] PRIMARY KEY CLUSTERED 
(
	[FAZON] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
INSERT [ELOFIZ] ([EKOD], [EKDATUM], [EVDATUM], [FAZON], [EFIZDB]) VALUES (1, CAST(N'2019-01-01' AS Date), CAST(N'2020-01-01' AS Date), 1, 2)
INSERT [ELOFIZ] ([EKOD], [EKDATUM], [EVDATUM], [FAZON], [EFIZDB]) VALUES (1, CAST(N'2018-02-01' AS Date), CAST(N'2019-02-01' AS Date), 2, 1)
INSERT [ELOFIZ] ([EKOD], [EKDATUM], [EVDATUM], [FAZON], [EFIZDB]) VALUES (1, CAST(N'2019-11-01' AS Date), CAST(N'2020-05-01' AS Date), 3, 1)
INSERT [ELOFIZ] ([EKOD], [EKDATUM], [EVDATUM], [FAZON], [EFIZDB]) VALUES (11, CAST(N'2019-03-01' AS Date), CAST(N'2020-03-01' AS Date), 2, 1)
INSERT [ELOFIZ] ([EKOD], [EKDATUM], [EVDATUM], [FAZON], [EFIZDB]) VALUES (11, CAST(N'2018-04-01' AS Date), CAST(N'2019-04-01' AS Date), 4, 1)
INSERT [ELOFIZ] ([EKOD], [EKDATUM], [EVDATUM], [FAZON], [EFIZDB]) VALUES (11, CAST(N'2018-02-01' AS Date), CAST(N'2018-08-01' AS Date), 5, 2)
INSERT [ELOFIZ] ([EKOD], [EKDATUM], [EVDATUM], [FAZON], [EFIZDB]) VALUES (21, CAST(N'2019-01-01' AS Date), CAST(N'2019-07-01' AS Date), 6, 2)
INSERT [ELOFIZ] ([EKOD], [EKDATUM], [EVDATUM], [FAZON], [EFIZDB]) VALUES (21, CAST(N'2019-11-01' AS Date), CAST(N'2020-11-01' AS Date), 7, 1)
INSERT [ELOFIZ] ([EKOD], [EKDATUM], [EVDATUM], [FAZON], [EFIZDB]) VALUES (31, CAST(N'2019-01-01' AS Date), CAST(N'2019-07-01' AS Date), 8, 3)

INSERT [ELOFIZ] ([EKOD], [EKDATUM], [EVDATUM], [FAZON], [EFIZDB]) VALUES (1, CAST(N'2020-01-01' AS Date), CAST(N'2021-01-01' AS Date), 1, 2)
INSERT [ELOFIZ] ([EKOD], [EKDATUM], [EVDATUM], [FAZON], [EFIZDB]) VALUES (1, CAST(N'2020-02-01' AS Date), CAST(N'2021-02-01' AS Date), 2, 1)
INSERT [ELOFIZ] ([EKOD], [EKDATUM], [EVDATUM], [FAZON], [EFIZDB]) VALUES (1, CAST(N'2019-04-01' AS Date), CAST(N'2020-11-01' AS Date), 3, 1)
INSERT [ELOFIZ] ([EKOD], [EKDATUM], [EVDATUM], [FAZON], [EFIZDB]) VALUES (21, CAST(N'2020-01-01' AS Date), CAST(N'2020-12-31' AS Date), 6, 2)
INSERT [ELOFIZ] ([EKOD], [EKDATUM], [EVDATUM], [FAZON], [EFIZDB]) VALUES (21, CAST(N'2019-12-01' AS Date), CAST(N'2020-12-31' AS Date), 8, 1)
INSERT [ELOFIZ] ([EKOD], [EKDATUM], [EVDATUM], [FAZON], [EFIZDB]) VALUES (31, CAST(N'2020-06-01' AS Date), CAST(N'2021-05-01' AS Date), 8, 3)

INSERT [ELOFIZ] ([EKOD], [EKDATUM], [EVDATUM], [FAZON], [EFIZDB]) VALUES (1, CAST(N'2021-01-01' AS Date), CAST(N'2022-01-01' AS Date), 1, 2)
INSERT [ELOFIZ] ([EKOD], [EKDATUM], [EVDATUM], [FAZON], [EFIZDB]) VALUES (1, CAST(N'2021-02-01' AS Date), CAST(N'2022-02-01' AS Date), 2, 1)
INSERT [ELOFIZ] ([EKOD], [EKDATUM], [EVDATUM], [FAZON], [EFIZDB]) VALUES (1, CAST(N'2021-11-01' AS Date), CAST(N'2022-05-01' AS Date), 3, 1)
INSERT [ELOFIZ] ([EKOD], [EKDATUM], [EVDATUM], [FAZON], [EFIZDB]) VALUES (11, CAST(N'2021-03-01' AS Date), CAST(N'2023-03-01' AS Date), 2, 1)
INSERT [ELOFIZ] ([EKOD], [EKDATUM], [EVDATUM], [FAZON], [EFIZDB]) VALUES (11, CAST(N'2021-04-01' AS Date), CAST(N'2024-04-01' AS Date), 4, 1)
INSERT [ELOFIZ] ([EKOD], [EKDATUM], [EVDATUM], [FAZON], [EFIZDB]) VALUES (11, CAST(N'2021-02-01' AS Date), CAST(N'2022-08-01' AS Date), 5, 2)
INSERT [ELOFIZ] ([EKOD], [EKDATUM], [EVDATUM], [FAZON], [EFIZDB]) VALUES (21, CAST(N'2021-01-01' AS Date), CAST(N'2022-07-01' AS Date), 6, 2)
INSERT [ELOFIZ] ([EKOD], [EKDATUM], [EVDATUM], [FAZON], [EFIZDB]) VALUES (21, CAST(N'2021-11-01' AS Date), CAST(N'2022-11-01' AS Date), 7, 1)
INSERT [ELOFIZ] ([EKOD], [EKDATUM], [EVDATUM], [FAZON], [EFIZDB]) VALUES (31, CAST(N'2021-01-01' AS Date), CAST(N'2022-07-01' AS Date), 8, 3)

SET IDENTITY_INSERT [ETORZS] ON 

INSERT [ETORZS] ([EKOD], [ENEV], [ESZDAT], [EIRSZ], [ETELEP], [EUTCA]) VALUES (1, N'Kiss Sándor                             ', CAST(N'1983-10-24' AS Date), N'8700', N'Tapolca', N'Kocsis utca 34.')
INSERT [ETORZS] ([EKOD], [ENEV], [ESZDAT], [EIRSZ], [ETELEP], [EUTCA]) VALUES (11, N'Tóth István Péter                       ', CAST(N'1975-04-12' AS Date), N'9026', N'Győr', N'Baross utca 76.')
INSERT [ETORZS] ([EKOD], [ENEV], [ESZDAT], [EIRSZ], [ETELEP], [EUTCA]) VALUES (21, N'Balla Tóni                              ', CAST(N'1991-10-11' AS Date), N'9024', N'Győr', N'Magyar u 87.')
INSERT [ETORZS] ([EKOD], [ENEV], [ESZDAT], [EIRSZ], [ETELEP], [EUTCA]) VALUES (31, N'Fábián Márkus                           ', CAST(N'1956-12-12' AS Date), N'8286', N'Gyulakeszi', N'Kiss utca 12/c.')
INSERT [ETORZS] ([EKOD], [ENEV], [ESZDAT], [EIRSZ], [ETELEP], [EUTCA]) VALUES (41, N'Mészáros Lőrincz                        ', CAST(N'1984-03-10' AS Date), N'6002', N'Kecskemét', N'Baranyai utca 66.')
SET IDENTITY_INSERT [ETORZS] OFF
SET IDENTITY_INSERT [FTORZS] ON 

INSERT [FTORZS] ([FAZON], [FTIPUS], [FCIM], [FKIAD], [FHDIJ]) VALUES (1, N'1', N'Petőfi népe', N'Petőfi Kiadó', 3000.0000)
INSERT [FTORZS] ([FAZON], [FTIPUS], [FCIM], [FKIAD], [FHDIJ]) VALUES (2, N'1', N'Vass népe', N'Vassi Kiadó', 2500.0000)
INSERT [FTORZS] ([FAZON], [FTIPUS], [FCIM], [FKIAD], [FHDIJ]) VALUES (3, N'2', N'Nők Lapja', N'Kossuth Kiadó', 5000.0000)
INSERT [FTORZS] ([FAZON], [FTIPUS], [FCIM], [FKIAD], [FHDIJ]) VALUES (4, N'2', N'LapPaprika', N'Főző Stúdió', 2500.0000)
INSERT [FTORZS] ([FAZON], [FTIPUS], [FCIM], [FKIAD], [FHDIJ]) VALUES (5, N'3', N'PCWorld', N'Project029 Média', 6000.0000)
INSERT [FTORZS] ([FAZON], [FTIPUS], [FCIM], [FKIAD], [FHDIJ]) VALUES (6, N'3', N'PCnews', N'News Kiadó', 6700.0000)
INSERT [FTORZS] ([FAZON], [FTIPUS], [FCIM], [FKIAD], [FHDIJ]) VALUES (7, N'4', N'Móricka', N'Vicc kiadó', 1800.0000)
INSERT [FTORZS] ([FAZON], [FTIPUS], [FCIM], [FKIAD], [FHDIJ]) VALUES (8, N'4', N'IPM', N'Gondolat Kiadó', 5600.0000)
SET IDENTITY_INSERT [FTORZS] OFF
ALTER TABLE [ELOFIZ]  WITH CHECK ADD  CONSTRAINT [FK_ELOFIZ_ETORZS] FOREIGN KEY([EKOD])
REFERENCES [ETORZS] ([EKOD])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [ELOFIZ] CHECK CONSTRAINT [FK_ELOFIZ_ETORZS]
GO
ALTER TABLE [ELOFIZ]  WITH CHECK ADD  CONSTRAINT [FK_ELOFIZ_FTORZS] FOREIGN KEY([FAZON])
REFERENCES [FTORZS] ([FAZON])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [ELOFIZ] CHECK CONSTRAINT [FK_ELOFIZ_FTORZS]
GO
ALTER TABLE [ELOFIZ]  WITH CHECK ADD  CONSTRAINT [CK_ELOFIZ_EFIZDB] CHECK  (([EFIZDB]>=(1) AND [EFIZDB]<=(999)))
GO
ALTER TABLE [ELOFIZ] CHECK CONSTRAINT [CK_ELOFIZ_EFIZDB]
GO
ALTER TABLE [ETORZS]  WITH CHECK ADD  CONSTRAINT [CK_ETORZS_EIRSZ] CHECK  (([EIRSZ] like '[1-9][0-9][0-9][0-9]'))
GO
ALTER TABLE [ETORZS] CHECK CONSTRAINT [CK_ETORZS_EIRSZ]
GO
ALTER TABLE [ETORZS]  WITH CHECK ADD  CONSTRAINT [CK_ETORZS_ESZDAT] CHECK  ((datediff(year,[ESZDAT],getdate())>=(18)))
GO
ALTER TABLE [ETORZS] CHECK CONSTRAINT [CK_ETORZS_ESZDAT]
GO
ALTER TABLE [FTORZS]  WITH CHECK ADD  CONSTRAINT [CK_FTORZS_FHDIJ] CHECK  (([FHDIJ]>=(100) AND [FHDIJ]<=(99999)))
GO
ALTER TABLE [FTORZS] CHECK CONSTRAINT [CK_FTORZS_FHDIJ]
GO
ALTER TABLE [FTORZS]  WITH CHECK ADD  CONSTRAINT [CK_FTORZS_FTIPUS] CHECK  (([FTIPUS] like '[1-4]'))
GO
ALTER TABLE [FTORZS] CHECK CONSTRAINT [CK_FTORZS_FTIPUS]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'18éven aluli nem fizethet elő!' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ETORZS', @level2type=N'CONSTRAINT',@level2name=N'CK_ETORZS_ESZDAT'
GO