use master
go
if exists (select name from sys.databases where name = 'PersonelBilgileriDB')
begin
alter database PersonelBilgileriDB set single_user with rollback immediate -- veritabanı bağlantısını koparmak için özel sorgu
drop database PersonelBilgileriDB -- veritabanını silen esas sorgu
end
go
create database PersonelBilgileriDB
go
USE [PersonelBilgileriDB]
GO
/****** Object: Table [dbo].[Maas] Script Date: 21.06.2023 01:37:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Maas](
[Id] [int] IDENTITY(1,1) NOT NULL,
[PersonelId] [int] NOT NULL,
[Ucret] [money] NOT NULL,
[Yil] [char](4) NOT NULL,
CONSTRAINT [PK_Maas] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[Personel] Script Date: 21.06.2023 01:37:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Personel](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Ad] [varchar](50) NOT NULL,
[Soyad] [varchar](50) NOT NULL,
[KimlikNo] [char](11) NULL,
[DogumTarih] [date] NOT NULL,
CONSTRAINT [PK_Personel] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[PersonelSehirIletisim] Script Date: 21.06.2023 01:37:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Iletisim](
[Id] [int] IDENTITY(1,1) NOT NULL,
[PersonelId] [int] NOT NULL,
[SehirId] [int] NOT NULL,
[CepTelefon] [varchar](15) NOT NULL,
[EvTelefon] [varchar](15) NULL,
[Eposta] [varchar](300) NULL,
[Adres] [varchar](1000) NULL,
CONSTRAINT [PK_Iletisim] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[Sehir] Script Date: 21.06.2023 01:37:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Sehir](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Ad] [varchar](50) NOT NULL,
CONSTRAINT [PK_Sehir] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Maas] ON
GO
INSERT [dbo].[Maas] ([Id], [PersonelId], [Ucret], [Yil]) VALUES (1, 1, 5000.0000, N'2019')
GO
INSERT [dbo].[Maas] ([Id], [PersonelId], [Ucret], [Yil]) VALUES (2, 1, 5500.0000, N'2020')
GO
INSERT [dbo].[Maas] ([Id], [PersonelId], [Ucret], [Yil]) VALUES (3, 2, 3000.0000, N'2019')
GO
INSERT [dbo].[Maas] ([Id], [PersonelId], [Ucret], [Yil]) VALUES (4, 2, 3500.0000, N'2020')
GO
INSERT [dbo].[Maas] ([Id], [PersonelId], [Ucret], [Yil]) VALUES (5, 3, 6000.0000, N'2019')
GO
INSERT [dbo].[Maas] ([Id], [PersonelId], [Ucret], [Yil]) VALUES (6, 3, 6500.0000, N'2020')
GO
INSERT [dbo].[Maas] ([Id], [PersonelId], [Ucret], [Yil]) VALUES (7, 4, 8500.0000, N'2019')
GO
INSERT [dbo].[Maas] ([Id], [PersonelId], [Ucret], [Yil]) VALUES (8, 4, 9000.0000, N'2020')
GO
INSERT [dbo].[Maas] ([Id], [PersonelId], [Ucret], [Yil]) VALUES (9, 5, 2500.0000, N'2019')
GO
INSERT [dbo].[Maas] ([Id], [PersonelId], [Ucret], [Yil]) VALUES (10, 5, 3500.0000, N'2020')
GO
SET IDENTITY_INSERT [dbo].[Maas] OFF
GO
SET IDENTITY_INSERT [dbo].[Personel] ON
GO
INSERT [dbo].[Personel] ([Id], [Ad], [Soyad], [KimlikNo], [DogumTarih]) VALUES (1, N'Çağıl', N'Alsaç', N'12345678901', CAST(N'1980-01-01' AS Date))
GO
INSERT [dbo].[Personel] ([Id], [Ad], [Soyad], [KimlikNo], [DogumTarih]) VALUES (2, N'Leo', N'Alsaç', N'98765432101', CAST(N'2014-12-15' AS Date))
GO
INSERT [dbo].[Personel] ([Id], [Ad], [Soyad], [KimlikNo], [DogumTarih]) VALUES (3, N'Ali', N'Veli', NULL, CAST(N'2010-04-16' AS Date))
GO
INSERT [dbo].[Personel] ([Id], [Ad], [Soyad], [KimlikNo], [DogumTarih]) VALUES (4, N'Yasemin', N'Zeynep', NULL, CAST(N'1990-06-30' AS Date))
GO
INSERT [dbo].[Personel] ([Id], [Ad], [Soyad], [KimlikNo], [DogumTarih]) VALUES (5, N'Cem', N'Tan', N'11122233344', CAST(N'2000-10-20' AS Date))
GO
SET IDENTITY_INSERT [dbo].[Personel] OFF
GO
SET IDENTITY_INSERT [dbo].Iletisim ON
GO
INSERT [dbo].[Iletisim] ([Id], [PersonelId], [SehirId], [CepTelefon], [EvTelefon], [Eposta], [Adres]) VALUES (1, 1, 1, N'5321112233', N'3129998877', N'cagil@alsac.com', N'Çankaya')
GO
INSERT [dbo].[Iletisim] ([Id], [PersonelId], [SehirId], [CepTelefon], [EvTelefon], [Eposta], [Adres]) VALUES (2, 2, 1, N'5559876543', NULL, N'leo@alsac.com', N'Yenimahalle')
GO
INSERT [dbo].[Iletisim] ([Id], [PersonelId], [SehirId], [CepTelefon], [EvTelefon], [Eposta], [Adres]) VALUES (3, 4, 2, N'5559182736', NULL, N'yasemin@zeynep.com', N'Kadıköy')
GO
INSERT [dbo].[Iletisim] ([Id], [PersonelId], [SehirId], [CepTelefon], [EvTelefon], [Eposta], [Adres]) VALUES (4, 4, 3, N'5331928376', NULL, NULL, N'Çeşme')
GO
INSERT [dbo].[Iletisim] ([Id], [PersonelId], [SehirId], [CepTelefon], [EvTelefon], [Eposta], [Adres]) VALUES (5, 5, 3, N'5443332211', NULL, N'cem@tan.com', N'Konak')
GO
SET IDENTITY_INSERT [dbo].Iletisim OFF
GO
SET IDENTITY_INSERT [dbo].[Sehir] ON
GO
INSERT [dbo].[Sehir] ([Id], [Ad]) VALUES (1, N'Ankara')
GO
INSERT [dbo].[Sehir] ([Id], [Ad]) VALUES (2, N'İstanbul')
GO
INSERT [dbo].[Sehir] ([Id], [Ad]) VALUES (3, N'İzmir')
GO
SET IDENTITY_INSERT [dbo].[Sehir] OFF
GO
ALTER TABLE [dbo].[Maas] WITH CHECK ADD CONSTRAINT [FK_Maas_Personel] FOREIGN KEY([PersonelId])
REFERENCES [dbo].[Personel] ([Id])
GO
ALTER TABLE [dbo].[Maas] CHECK CONSTRAINT [FK_Maas_Personel]
GO
ALTER TABLE [dbo].[Iletisim] WITH CHECK ADD CONSTRAINT [FK_Iletisim_Personel] FOREIGN KEY([PersonelId])
REFERENCES [dbo].[Personel] ([Id])
GO
ALTER TABLE [dbo].[Iletisim] CHECK CONSTRAINT [FK_Iletisim_Personel]
GO
ALTER TABLE [dbo].[Iletisim] WITH CHECK ADD CONSTRAINT [FK_Iletisim_Sehir] FOREIGN KEY([SehirId])
REFERENCES [dbo].[Sehir] ([Id])
GO
ALTER TABLE [dbo].[Iletisim] CHECK CONSTRAINT [FK_Iletisim_Sehir]
GO