USE [master]
GO

CREATE DATABASE [RecursosHumanos2]
GO

USE [RecursosHumanos2]
GO

CREATE TABLE [dbo].[cargo](
	[idcargo] [char](3) NOT NULL,
	[nombre] [varchar](50) NOT NULL,
	[sueldo_min] [money] NOT NULL,
	[sueldo_max] [money] NOT NULL,
 CONSTRAINT [pk_cargo] PRIMARY KEY CLUSTERED 
(
	[idcargo] 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 [dbo].[control](
	[parametro] [varchar](20) NOT NULL,
	[valor] [varchar](20) NOT NULL,
 CONSTRAINT [pk_control] PRIMARY KEY CLUSTERED 
(
	[parametro] 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
SET ANSI_PADDING OFF
GO

CREATE TABLE [dbo].[departamento](
	[iddepartamento] [int] NOT NULL,
	[nombre] [varchar](100) NOT NULL,
	[idubicacion] [char](3) NOT NULL,
 CONSTRAINT [pk_departamento] PRIMARY KEY CLUSTERED 
(
	[iddepartamento] 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 [dbo].[empleado](
	[idempleado] [char](5) NOT NULL,
	[apellido] [varchar](50) NOT NULL,
	[nombre] [varchar](50) NOT NULL,
	[fecingreso] [smalldatetime] NOT NULL,
	[email] [varchar](50) NULL,
	[telefono] [varchar](20) NULL,
	[idcargo] [char](3) NOT NULL,
	[sueldo] [money] NOT NULL,
	[comision] [money] NULL,
	[jefe] [char](5) NULL,
	[estado] [char](1) NULL,
 CONSTRAINT [pk_empleado] PRIMARY KEY CLUSTERED 
(
	[idempleado] 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 [dbo].[ubicacion](
	[idubicacion] [char](3) NOT NULL,
	[ciudad] [varchar](50) NOT NULL,
	[direccion] [varchar](100) NOT NULL,
 CONSTRAINT [pk_ubicacion] PRIMARY KEY CLUSTERED 
(
	[idubicacion] 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
SET ANSI_PADDING OFF
GO
INSERT [dbo].[cargo] ([idcargo], [nombre], [sueldo_min], [sueldo_max]) VALUES (N'C01', N'Gerente General', 15000.0000, 50000.0000)
INSERT [dbo].[cargo] ([idcargo], [nombre], [sueldo_min], [sueldo_max]) VALUES (N'C02', N'Gerente', 10000.0000, 15000.0000)
INSERT [dbo].[cargo] ([idcargo], [nombre], [sueldo_min], [sueldo_max]) VALUES (N'C03', N'Empleado', 7000.0000, 10000.0000)
INSERT [dbo].[cargo] ([idcargo], [nombre], [sueldo_min], [sueldo_max]) VALUES (N'C04', N'Analista', 5000.0000, 7000.0000)
INSERT [dbo].[cargo] ([idcargo], [nombre], [sueldo_min], [sueldo_max]) VALUES (N'C05', N'Vendedor', 3000.0000, 5000.0000)
INSERT [dbo].[cargo] ([idcargo], [nombre], [sueldo_min], [sueldo_max]) VALUES (N'C06', N'Oficinista', 1500.0000, 3000.0000)
INSERT [dbo].[cargo] ([idcargo], [nombre], [sueldo_min], [sueldo_max]) VALUES (N'C07', N'Programador', 2500.0000, 6000.0000)
INSERT [dbo].[cargo] ([idcargo], [nombre], [sueldo_min], [sueldo_max]) VALUES (N'C08', N'Investigador', 6000.0000, 8000.0000)
INSERT [dbo].[cargo] ([idcargo], [nombre], [sueldo_min], [sueldo_max]) VALUES (N'C09', N'Digitador', 1000.0000, 1500.0000)
INSERT [dbo].[cargo] ([idcargo], [nombre], [sueldo_min], [sueldo_max]) VALUES (N'C10', N'Anfitriona', 1300.0000, 1800.0000)
INSERT [dbo].[control] ([parametro], [valor]) VALUES (N'Cargo', N'10')
INSERT [dbo].[control] ([parametro], [valor]) VALUES (N'Departamento', N'107')
INSERT [dbo].[control] ([parametro], [valor]) VALUES (N'Empleado', N'22')
INSERT [dbo].[control] ([parametro], [valor]) VALUES (N'Empresa', N'PeruDev')
INSERT [dbo].[control] ([parametro], [valor]) VALUES (N'Ubicacion', N'4')
INSERT [dbo].[departamento] ([iddepartamento], [nombre], [idubicacion]) VALUES (100, N'Gerencia', N'U01')
INSERT [dbo].[departamento] ([iddepartamento], [nombre], [idubicacion]) VALUES (101, N'Contabilidad', N'U01')
INSERT [dbo].[departamento] ([iddepartamento], [nombre], [idubicacion]) VALUES (102, N'Investigacion', N'U03')
INSERT [dbo].[departamento] ([iddepartamento], [nombre], [idubicacion]) VALUES (103, N'Ventas', N'U01')
INSERT [dbo].[departamento] ([iddepartamento], [nombre], [idubicacion]) VALUES (104, N'Operaciones', N'U01')
INSERT [dbo].[departamento] ([iddepartamento], [nombre], [idubicacion]) VALUES (105, N'Sistemas', N'U04')
INSERT [dbo].[departamento] ([iddepartamento], [nombre], [idubicacion]) VALUES (106, N'Recursos Humanos', N'U04')
INSERT [dbo].[departamento] ([iddepartamento], [nombre], [idubicacion]) VALUES (107, N'Finanzas', N'U01')
INSERT [dbo].[empleado] ([idempleado], [apellido], [nombre], [fecingreso], [email], [telefono], [idcargo], [sueldo], [comision], [jefe], [estado]) VALUES (N'E0001', N'Coroneles', N'Gustavo', CAST(N'2000-01-04 00:00:00' AS SmallDateTime), N'gcoronelc@gmail.com', N'996-664-457', N'C02', 25000.0000, NULL, NULL, N'A')
INSERT [dbo].[empleado] ([idempleado], [apellido], [nombre], [fecingreso], [email], [telefono], [idcargo], [sueldo], [comision], [jefe], [estado]) VALUES (N'E0002', N'Fernandez 123', N'Claudia', CAST(N'2000-01-05 00:00:00' AS SmallDateTime), N'cfernandez@perudev.com', N'9345-8365', N'C02', 8000.0000, NULL, N'E0001', N'A')
INSERT [dbo].[empleado] ([idempleado], [apellido], [nombre], [fecingreso], [email], [telefono], [idcargo], [sueldo], [comision], [jefe], [estado]) VALUES (N'E0003', N'Matsukawa 21', N'Sergio', CAST(N'2000-01-04 00:00:00' AS SmallDateTime), N'smatsukawa@perudev.com', N'9772-8369', N'C02', 15000.0000, NULL, N'E0001', N'A')
INSERT [dbo].[empleado] ([idempleado], [apellido], [nombre], [fecingreso], [email], [telefono], [idcargo], [sueldo], [comision], [jefe], [estado]) VALUES (N'E0004', N'Diaz', N'Mariela', CAST(N'2000-10-04 00:00:00' AS SmallDateTime), N'mdiaz@perudev.com', N'8654-6734', N'C06', 1800.0000, NULL, N'E0003', N'A')
INSERT [dbo].[empleado] ([idempleado], [apellido], [nombre], [fecingreso], [email], [telefono], [idcargo], [sueldo], [comision], [jefe], [estado]) VALUES (N'E0005', N'Martinez', N'Roberto', CAST(N'2000-05-04 00:00:00' AS SmallDateTime), N'rmartinez@perudev.com', NULL, N'C08', 9000.0000, 500.0000, N'E0003', N'A')
INSERT [dbo].[empleado] ([idempleado], [apellido], [nombre], [fecingreso], [email], [telefono], [idcargo], [sueldo], [comision], [jefe], [estado]) VALUES (N'E0006', N'Espinoza', N'Miguel', CAST(N'2000-06-04 00:00:00' AS SmallDateTime), N'mespinoza@perudev.com', NULL, N'C08', 7500.0000, 500.0000, N'E0003', N'A')
INSERT [dbo].[empleado] ([idempleado], [apellido], [nombre], [fecingreso], [email], [telefono], [idcargo], [sueldo], [comision], [jefe], [estado]) VALUES (N'E0007', N'Ramos', N'Vanessa', CAST(N'2002-06-04 00:00:00' AS SmallDateTime), N'vramos@perudev.com', N'9456-3456', N'C08', 7000.0000, 500.0000, N'E0003', N'A')
INSERT [dbo].[empleado] ([idempleado], [apellido], [nombre], [fecingreso], [email], [telefono], [idcargo], [sueldo], [comision], [jefe], [estado]) VALUES (N'E0008', N'Flores', N'Julio', CAST(N'2000-01-04 00:00:00' AS SmallDateTime), N'jflores@perudev.com', NULL, N'C07', 3500.0000, 1000.0000, N'E0003', N'A')
INSERT [dbo].[empleado] ([idempleado], [apellido], [nombre], [fecingreso], [email], [telefono], [idcargo], [sueldo], [comision], [jefe], [estado]) VALUES (N'E0009', N'Marcelo', N'Ricardo', CAST(N'2000-01-04 00:00:00' AS SmallDateTime), N'rmarcelo@perudev.com', N'9936-2966', N'C02', 15000.0000, NULL, N'E0001', N'A')
INSERT [dbo].[empleado] ([idempleado], [apellido], [nombre], [fecingreso], [email], [telefono], [idcargo], [sueldo], [comision], [jefe], [estado]) VALUES (N'E0012', N'Valencia', N'Hugo', CAST(N'2000-01-05 00:00:00' AS SmallDateTime), N'hvalencia@perudev.com', N'9732-5601', N'C02', 18000.0000, NULL, N'E0001', N'A')
INSERT [dbo].[empleado] ([idempleado], [apellido], [nombre], [fecingreso], [email], [telefono], [idcargo], [sueldo], [comision], [jefe], [estado]) VALUES (N'E0013', N'Veliz', N'Fortunato', CAST(N'2000-05-05 00:00:00' AS SmallDateTime), N'fveliz@perudev.com', N'9826-7603', N'C04', 6000.0000, NULL, N'E0012', N'A')
INSERT [dbo].[empleado] ([idempleado], [apellido], [nombre], [fecingreso], [email], [telefono], [idcargo], [sueldo], [comision], [jefe], [estado]) VALUES (N'E0016', N'Villarreal', N'Nora', CAST(N'2000-01-05 00:00:00' AS SmallDateTime), N'nvillarreal@perudev.com', N'9371-3641', N'C02', 15000.0000, NULL, N'E0001', N'A')
INSERT [dbo].[empleado] ([idempleado], [apellido], [nombre], [fecingreso], [email], [telefono], [idcargo], [sueldo], [comision], [jefe], [estado]) VALUES (N'E0017', N'Romero', N'Alejandra', CAST(N'2000-03-05 00:00:00' AS SmallDateTime), N'aromero@perudev.com', N'8345-9526', N'C03', 7500.0000, NULL, N'E0016', N'A')
INSERT [dbo].[empleado] ([idempleado], [apellido], [nombre], [fecingreso], [email], [telefono], [idcargo], [sueldo], [comision], [jefe], [estado]) VALUES (N'E0018', N'Valdiviezo', N'Jennifer', CAST(N'2000-12-06 00:00:00' AS SmallDateTime), N'jvaldiviezo@perudev.com', N'9263-5172', N'C06', 2000.0000, NULL, N'E0016', N'A')
INSERT [dbo].[empleado] ([idempleado], [apellido], [nombre], [fecingreso], [email], [telefono], [idcargo], [sueldo], [comision], [jefe], [estado]) VALUES (N'E0019', N'Muchotrigo', N'Omar', CAST(N'2000-12-05 00:00:00' AS SmallDateTime), N'omuchotrigo@perudev.com', N'9963-5542', N'C05', 3500.0000, 500.0000, N'E0016', N'A')
INSERT [dbo].[ubicacion] ([idubicacion], [ciudad], [direccion]) VALUES (N'U01', N'Lima', N'Av. Benavides 534 - Miraflores')
INSERT [dbo].[ubicacion] ([idubicacion], [ciudad], [direccion]) VALUES (N'U02', N'Trujillo', N'Calle Primavera 1235 - Cercado')
INSERT [dbo].[ubicacion] ([idubicacion], [ciudad], [direccion]) VALUES (N'U03', N'Arequipa', N'Av. Central 2517 - Cercado')
INSERT [dbo].[ubicacion] ([idubicacion], [ciudad], [direccion]) VALUES (N'U04', N'Lima', N'Av. La Marina 3456 - San Miguel')
ALTER TABLE [dbo].[departamento]  WITH CHECK ADD  CONSTRAINT [fk_departamento_ubicacion] FOREIGN KEY([idubicacion])
REFERENCES [dbo].[ubicacion] ([idubicacion])
GO
ALTER TABLE [dbo].[departamento] CHECK CONSTRAINT [fk_departamento_ubicacion]
GO
ALTER TABLE [dbo].[empleado]  WITH CHECK ADD  CONSTRAINT [fk_empleado_cargo] FOREIGN KEY([idcargo])
REFERENCES [dbo].[cargo] ([idcargo])
GO
ALTER TABLE [dbo].[empleado] CHECK CONSTRAINT [fk_empleado_cargo]
GO
ALTER TABLE [dbo].[empleado]  WITH CHECK ADD  CONSTRAINT [fk_empleado_empleado] FOREIGN KEY([jefe])
REFERENCES [dbo].[empleado] ([idempleado])
GO
ALTER TABLE [dbo].[empleado] CHECK CONSTRAINT [fk_empleado_empleado]
GO

