Consultas sql.

...

by [Order Details].OrderID

select * from [Order Details]

/*11.- mostrar el total de cada orden de los clientes de la región norte.*/

select [Order Details].OrderID as Id,Customers.Region,Quantity,UnitPrice,(unitprice*quantity)-discount as total from [Order Details] inner join Orders

on Orders.OrderID=[Order Details].OrderID inner join Customers

on Customers.CustomerID=Orders.CustomerID

where Region=’or’ /* NORTE */

order by id

/*12.- mostrar el total de cada orden de los clientes de la región norte y de los

empleados del a región norte.*/

select [Order Details].OrderID as Id,(unitprice*quantity)-discount as total,ContactName as cliente,FirstName as empleado,Customers.Region as REGION_CLIE,Employees.Region as REGION_EMP from [Order Details] inner join Orders

on Orders.OrderID=[Order Details].OrderID inner join Customers

on Customers.CustomerID=Orders.CustomerID inner join Employees

on Employees.EmployeeID=Orders.EmployeeID

where Customers.region=’WA’ and Employees.Region=’WA’ /* NORTE*/

order by REGION_CLIE

/*13.- Mostrar el nombre de los clientes que realizaron las compras mayores.*/

SELECT TOP(1) WITH TIES (MAX((unitprice*quantity)-discount))AS TOTAL,Customers.ContactName AS CLIENTE from [Order Details] inner join Orders

on Orders.OrderID=[Order Details].OrderID inner join Customers

on Customers.CustomerID=Orders.CustomerID

group by ContactName,UnitPrice

order by TOTAL desc

/*14.- Mostrar cuantos clientes realizaron las compras menores.*/

SELECT TOP(1) WITH TIES (MAX((unitprice*quantity)-discount))AS TOTAL,Customers.ContactName AS CLIENTE from [Order Details] inner join Orders

on Orders.OrderID=[Order Details].OrderID inner join Customers

on Customers.CustomerID=Orders.CustomerID

group by ContactName,UnitPrice

order by TOTAL asc

/*15.- Mostrar cuantas órdenes fueron mayores a 300 dls. -> having. !!*/

select ((unitprice*quantity)-(unitprice*quantity*discount))AS TOTAL,Customers.ContactName AS CLIENTE from [Order Details] inner join Orders

on Orders.OrderID=[Order Details].OrderID inner join Customers

on Customers.CustomerID=Orders.CustomerID

group by ContactName,Quantity,UnitPrice,Discount

HAVING (((unitprice*quantity)-(unitprice*quantity*discount))) > 300

order by TOTAL asc

/*16.- Mostrar el total de venta de cada empleado. */

select distinct ((unitprice*quantity)-(unitprice*quantity*discount)) as total,FirstName as empleado from [Order Details] inner join Orders

on Orders.OrderID=[Order Details].OrderID inner join Employees

on Employees.EmployeeID=Orders.EmployeeID

group by [Order Details].OrderID,FirstName,UnitPrice,Quantity,Discount

order by total desc

/*2.1 mostrar una relación de jefes con sus empleados. */

--Consulta Recursiva para conocer todos los jefes de un subordinado, hasta el jefe más alto en el organigrama

SELECT E.firstname as jefes,m.firstname as subordinados

FROM Employees as E join employees as M on

E.EmployeeID = M.ReportsTo

order by jefes

select * from Employees

select * from Employees

UPDATE Employees

SET EmployeeBoss=’PATRON’

WHERE FirstName = ’ANNE’

DELETE

FROM employees

WHERE EmployeeBoss=’Jefemayor’

/*2.2 mostrar el nombre de los empleados que realizaron la venta mayor.*/

select top(1) WITH TIES ((unitprice*quantity)-(unitprice*quantity*discount)) as venta_mayores,FirstName as empleadO from [Order Details] inner join Orders

on Orders.OrderID=[Order Details].OrderID inner join Customers

on Customers.CustomerID=Orders.CustomerID inner join Employees

on Employees.EmployeeID=Orders.EmployeeID

group by orders.OrderID,[Order Details].OrderID,UnitPrice,Quantity,Discount,FirstName

Order by venta_mayores desc

select Region from Customers where region=’or’

select * from Employees

select * from Products

select * from Orders

select * from Suppliers

select * from [Order Details]