1.
Creacion de Bases de Datos
A. Eliminar la Base de datos INVENTARIO y MICRO si existen
B: Ejecutar el archivo de creacion de la Base de Datos MICRO
C. Ejecutar la query de INVENTARIO
2. Query con una sola tabla
Select CodigoCargo As Codigo,
NombreCargo as Cargo
From [Link] c
Order by NombreCargo
---------------
Select CodigoCargo As Codigo,
NombreCargo as Cargo
From [Link] c
order by NombreCargo desc
--------------------------
Select CodigoEmpleado,
Apellido1Empleado,
Apellido2Empleado,
NombresEmpleado,
SalarioEmpleado
From [Link]
order by SalarioEmpleado desc
-----------------------------
Select NombresEmpleado,
Apellido1Empleado,
Apellido2Empleado
From [Link]
Where Apellido1Empleado LIKE '%ado%'
---------------------------------------
Select min(salarioEmpleado) as minimo,
max(salarioEmpleado) as maximo,
avg(SalarioEmpleado) as Promedio,
sum(SalarioEmpleado) as Total,
count(1) as [Total Empleado]
From [Link]
-------------------------------------
Select [Link],
count(1) as cantidad
From [Link] d
inner join [Link] e
ON [Link] = [Link]
group by [Link]
order by cantidad desc
------------------------------------------
Select year([Link]) as [Año],
month([Link]) as mes,
sum([Link]) as [Total Credito]
From [Link] c
group by year([Link]),month([Link])
3. CONSULTAS CON 2 TABLAS
Select [Link],
[Link],
e.Apellido1Empleado,
e.Apellido1Empleado,
[Link]
From Empleados e
inner join Cargos c
ON [Link] = [Link]
order by [Link] desc,e.Apellido1Empleado,e.Apellido2Empleado
----------------------------------------------------
Select [Link], min([Link]) as Minimo,
max([Link]) as Maximo,
Avg([Link]) as Promedio
From [Link] d
Inner join [Link] e
ON [Link] = [Link]
group by [Link]
-------------------------------------------
Select [Link] as Nombres,
e.Apellido1Empleado + ' '+ e.Apellido2Empleado as Apellidos,
[Link],
sum([Link]) as Total
From [Link] e
inner join [Link] c
ON [Link] = [Link]
group by [Link] ,
e.Apellido1Empleado +' '+ e.Apellido2Empleado,
[Link]
-----------------------------------------
Select [Link],
sum([Link]) as total
From Dependencias d
Inner join Empleados e
On [Link] = [Link]
inner join Creditos c
On [Link] = [Link]
group by [Link]
--------------------------------------------------
Select [Link],
[Link],
e.Apellido1Empleado,
e.Apellido2Empleado,
sum([Link]) as total
From Empleados e
inner join Creditos c
On [Link] = [Link]
group by
[Link],[Link],e.Apellido1Empleado,e.Apellido2Empleado
having sum([Link])>5000
4. SUB CONSULTA
Select [Link],
[Link]
From [Link] c
Where [Link] not in (select cargoid from empleados )
--------------------------
set dateformat ymd
Select [Link],
[Link],
e.Apellido1Empleado,
e.Apellido2Empleado
From Empleados e
where not exists(select 1 from abonos a where [Link] = [Link] and [Link]
between '2020/03/01' and '2020/03/31')
--------------------------------------------------------
Select [Link],
[Link]
From [Link] c
Where not Exists(select cargoid from empleados e Where [Link] = [Link] )
5. CONSULTA CON 3 TABLAS
Select [Link],
[Link],
e.Apellido1Empleado,
e.Apellido2Empleado,
[Link],
[Link],
[Link]
From Empleados e
inner join Dependencias d
ON [Link] = [Link]
inner join Cargos c
ON [Link] = [Link]
order by [Link],[Link],[Link] desc
----------------------------------------------
Select [Link],
[Link],
e.Apellido1Empleado,
e.Apellido2Empleado,
[Link],
(Select sum([Link]) from creditos c where [Link] = [Link]) as
TotalCredito,
(select sum([Link]) from abonos a where [Link] = [Link]) as
totalAbono,
(Select sum([Link]) from creditos c where [Link] = [Link])-
(select sum([Link]) from abonos a where [Link] = [Link]) as saldo
From Empleados e
------------------------------------------
6. FUNCIONES DE VENTANA
A
USE MICRO
GO
/*Paso 1 Sin particion por cargo*/
select ROW_NUMBER() OVER(ORDER BY [Link] ASC) as Numero,
[Link],[Link]
from [Link] E inner join [Link] C
on [Link]=[Link]
B
/*Paso 2 particionado por cargo*/
select ROW_NUMBER() OVER(PARTITION BY [Link] ORDER BY [Link] ASC) as
numero,
[Link],[Link]
from [Link] E inner join [Link] C
on [Link]=[Link]
Order by NombreCargo
select
[Link],[Link],sum([Link]) as TotalCreditos
from [Link] E
inner join [Link] C
on [Link]=[Link]
inner join [Link] CD
on [Link]=[Link]
group by [Link],[Link]
select
[Link],[Link],sum([Link]) as TotalCreditos,
RANK() OVER(ORDER BY [Link] ASC) as Numero
from [Link] E
inner join [Link] C
on [Link]=[Link]
inner join [Link] CD
on [Link]=[Link]
group by [Link],[Link]
order by numero
select
[Link],[Link],sum([Link]) as TotalCreditos,
DENSE_RANK() OVER(ORDER BY [Link] ASC) as Numero
from [Link] E
inner join [Link] C
on [Link]=[Link]
inner join [Link] CD
on [Link]=[Link]
group by [Link],[Link]
order by numero
/* Estan ordenados por creditos de los empleados por cargos */
/* los empleados que mas creditos sacaron aparecen de primeros si hay empate*/
/*Aparecen los dos de primero y el siguiente seria el 3 porque ya hay dos registro
en 1*/
select
[Link],[Link],sum([Link]) as TotalCreditos,
RANK() OVER(PARTITION BY [Link] ORDER BY sum([Link]) desc) as
Numero
from [Link] E
inner join [Link] C on [Link]=[Link]
inner join [Link] CD on [Link]=[Link]
group by [Link],[Link]
order by NombreCargo,Numero