-- Scripts Sess #03

select * from [dbo].[Categories]

from [dbo].[Categories]
select * 

-- Operadores aritmeticos

select 13 + 45
select 34 - 56
select 45 * 5
-- precision del tipo de dato
select 34 / 8
select 34.0 / 8
select 34 / 8.0
select (( 34 / 8) * ( 34 / 8)) / 2.3

-- Asignacion de valor
declare @Edad int	-- Declarar una variable de tipo INT
set @Edad = 18		-- Asignacion de valor
select @Edad

declare @nombre varchar(50), @edad int, @ciudad varchar(5)

-- Array
nombre(0) = Victor
nombre(1) = Luis

-- Operadores de comparacion
select 1 = 2

select * from [Order Details] where UnitPrice = 12
select * from [Order Details] where UnitPrice > 12
select * from [Order Details] where UnitPrice < 12
select * from [Order Details] where UnitPrice >= 12
select * from [Order Details] where UnitPrice <= 12
select * from [Order Details] where UnitPrice <> 12
select * from [Order Details] where UnitPrice != 12

-- comparacion no es por la cadena es por cada caracter que forma cadena
select * from [dbo].[Orders] where [ShipName] = 'Toms Spezialitäten'
select * from [dbo].[Orders] where [ShipName] > 'Toms Spezialitäten'

select * from orders where OrderDate = '2019-07-04'
select * from orders where OrderDate > '2019-30-06' 2019 + 30 + jun
-- The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
select * from orders where orderdate < '2020-01-01'

-- AND / OR / NOT
select * from orders 
where orderdate < '2020-01-01' 
and ShipCountry = 'USA'
and CustomerId = 'OLDWO'
and Freight < 100

select * from orders 
where orderdate < '2020-01-01' 
and ( ShipCountry = 'USA' or ShipCountry = 'France' or ShipCountry = 'Brazil')
and CustomerId = 'OLDWO'
and Freight < 100

select * from orders 
where ShipCountry = 'USA' or ShipCountry = 'France' or ShipCountry = 'Brazil'

-- excluir las ordenes desde el 31/12/2019 hacia atras y TAMPOCO considerar 
-- los paises de USA / France / Brazila
select * from orders 
where NOT orderdate < '2020-01-01' 
and NOT ( ShipCountry = 'USA' or ShipCountry = 'France' or ShipCountry = 'Brazil')

-- Operacion de tipo OR debe ser reemplazado por una instruccion IN
-- IN con constantes USA / France / Brazil
select * from orders 
where orderdate < '2020-01-01' 
and ShipCountry in ('USA','France','Brazil')

select * from orders 
where orderdate in ('2019-07-04','2019-07-05')

select * from orders
where Freight in ( 32.38, 11.61)

-- BetWeen
select * from Orders where OrderDate between '2020-01-01' and '2020-12-31'
select * from Orders where OrderDate between '2020-12-01' and '2020-12-31'
select * from Orders where Freight between 80 and 100
select * from Orders where ShipCity between 'Bräcke' and 'Cork'

-- Oracle
select * from Orders where OrderDate between to_date('01-01-2020','yyyy-mm-dd')

-- Like exclusivo CADENAS
select * from [dbo].[Customers] where CompanyName like 'A%'		-- cuando inicia la cadena
select * from [dbo].[Customers] where CompanyName like '%a'		-- cuando termina la cadena
select * from [dbo].[Customers] where CompanyName like '%od%'	-- contiene la cadena

Declare @Cuenta varchar(50)
set @Cuenta = '141012342354'
select SUBSTRING (@Cuenta,10,2)

select * from [dbo].[Customers] where CompanyName like 'A%n%j%'	-- contiene la cadena

-- Order BY
select * from Orders
where orderdate between '2020-01-01' and '2020-12-31'
order by OrderDate DESC, Freight asc

create table ventas (
tienda varchar(25),
fecha date,
producto varchar(50),
cliente varchar(100),
importe money) -- soporta decimales

-- Opción #1 Especificar las columnas en los valores y la tabla
INSERT INTO ventas (tienda, fecha, producto, cliente, importe)
VALUES ('Lima','01/01/2019','Papel Bond A4','Librería Lápiz y Papel',250);

--Opción #2 Especificar las columnas en los valores
-- se debe respetar el orden de las columnas fisicas
INSERT INTO ventas
VALUES ('Arequipa','01/01/2020','Papel Bond A4','Librería Lápiz y Papel',250);

select * from ventas

-- DWH Carga / Load
--Opción #3 Insertar desde una consulta
-- Tabla de destino con la estructura del SELECT a resolver

create table stg_ventas (
tienda varchar(25),
fecha date,
producto varchar(50),
cliente varchar(100),
importe money) -- soporta decimales

-- pasando los valores de todas las columna de la tabla ventas hacia la tabla stg_ventas
insert stg_ventas
select * from ventas where fecha between '2019-01-01' and '2019-12-31'

select * from stg_ventas

INSERT INTO ventas (tienda, fecha, producto, cliente, importe) VALUES ('Lima','01/01/2019','Papel Bond
A4','Librería Lápiz y Papel',250);
INSERT INTO ventas (tienda, fecha, producto, cliente, importe) VALUES
('Lima','01/01/2019','Teclados','Librería Centro',100);
INSERT INTO ventas (tienda, fecha, producto, cliente, importe) VALUES ('Lima','01/01/2019','Mouse','Librería
Culqui',200);
INSERT INTO ventas (tienda, fecha, producto, cliente, importe) VALUES ('Arequipa','01/01/2019','Papel Bond
A4','La casa de papel',850);
INSERT INTO ventas (tienda, fecha, producto, cliente, importe) VALUES
('Arequipa','01/01/2019','Teclados','Cabinas Arequipa',75);
INSERT INTO ventas (tienda, fecha, producto, cliente, importe) VALUES
('Arequipa','01/01/2019','Mouse','Centro Técnico Azul',25);
INSERT INTO ventas (tienda, fecha, producto, cliente, importe) VALUES ('Iquitos','01/01/2019','Papel Bond
A4','Librería Los Amigos',1500);
INSERT INTO ventas (tienda, fecha, producto, cliente, importe) VALUES
('Iquitos','01/01/2019','Teclados','Cabinas de la Selva',50);
INSERT INTO ventas (tienda, fecha, producto, cliente, importe) VALUES
('Iquitos','01/01/2019','Mouse','Oficentro',150);

-- Alias a las columnas = Cambiar el nombre de la columna de salida
select [OrderID], [CustomerID], [EmployeeID], [OrderDate]
from Orders

select [OrderID] as NroOrden, [CustomerID], [EmployeeID], [OrderDate]
from Orders

select [OrderID] [# Orden], [CustomerID] [Codigo Cliente], [EmployeeID], [OrderDate]
from Orders

-- Columnas Calculadas  = No Existen / Solo se obtienen al momento de ejecutar el script
select * from [dbo].[Employees]

-- mostrar el codigo empleado y concaternar (sumar) Apellido y el nombre
select EmployeeId, LastName + ', ' + FirstName [Apellidos y Nombres], LastName , FirstName
from Employees

select EmployeeId, LastName + ', ' + FirstName [Apellidos y Nombres], LastName , FirstName
from Employees
where LastName + ', ' + FirstName = 'Suyama, Michael'

-- CTAS = Create Table As Select = Crear una tabla desde un Query
select EmployeeId, LastName + ', ' + FirstName [Apellidos y Nombres], LastName , FirstName
INTO Empleados -- Creamos la tabla empleados 
from Employees

select *
from [dbo].[Empleados]
where [Apellidos y Nombres] = 'Suyama, Michael'

-- Calculadas de numeros
select * , (Quantity * UnitPrice) / 2.5 SubTotal
from [Order Details]

-- Ver la fecha de vencimiento de la Orden, a partir de 30 dias
select [OrderID] as NroOrden, [CustomerID], [EmployeeID], [OrderDate],
orderdate + 30 FecVenc,  orderdate - 10 PreFactura
from Orders

-- copia a la tabla
select * into ventas_copia from ventas 

select * from ventas
-- Udate jamas debe ir sin WHERE
update ventas set tienda  = 'Tumbes'

update ventas set importe = importe * 1.12
where producto = 'Papel Bond A4'

-- funcion concat
SELECT [EmployeeID]
,concat ([LastName],' ', [FirstName])
,[Title]
,[Country]
FROM [dbo].[Employees]
WHERE [Country] = 'USA'

-- CASE

SELECT OrderID, Quantity,
CASE
WHEN Quantity > 30 THEN 'The quantity is greater than 30'
WHEN Quantity = 30 THEN 'The quantity is 30'
ELSE 'The quantity is under 30'
END AS QuantityText
FROM [dbo].[Order Details];

select [OrderID] as NroOrden, [CustomerID], [EmployeeID], [OrderDate], ShippedDate,
[OrderDate] - ShippedDate, 
case when orderdate - ShippedDate < 5 then 'Optimo'
when orderdate - ShippedDate < 15 then 'Bueno'
	else 'Por Revisar' end flgFechas
from Orders

-- Funciones de agregacion
-- el importe de ventas total
select sum(quantity * unitprice) TotalVentas
from [Order Details]

select ProductId, sum(quantity * unitprice) TotalVentas
from [Order Details]
group by ProductId

-- count
select * from Orders
select count('Hola') from Orders	-- 830
select count(1) from Orders			-- 830

select customerId, count(1) NroPedidos
from Orders
group by customerId

select customerId, count(1) NroPedidos
from Orders
where OrderDate between '2020-01-01' and '2020-12-31'
group by customerId

select customerId, count(1) NroPedidos
from Orders
where OrderDate between '2020-01-01' and '2020-12-31'
and count(1) = 3
group by customerId
-- An aggregate may not appear in the WHERE clause unless it is in a subquery contained 
-- in a HAVING clause or a select list, and the column being aggregated is an outer reference.

select customerId, count(1) NroPedidos
from Orders
where OrderDate between '2020-01-01' and '2020-12-31'
group by customerId
having count(1) > 3

select * from Orders

-- la 1ra compra y cuando fue la ultima compra
select CustomerId, min(OrderDate) [1ra Compra], max(OrderDate) [Ult Compra]
from Orders
group by CustomerID

-- Obtener el detalle de pedidos que supere le promedio de la cantidad
select * from [Order Details]

select avg(quantity) from [Order Details]
-- 23
-- #1 Constante
select * from [Order Details] where Quantity > 23
-- #2 Variable
declare @Promedio int
select @promedio = avg(quantity) from [Order Details]
select * from [Order Details] where Quantity > @Promedio