• Author / Uploaded
• jhon_wilfredo

• Categories
• SQL
• Servidor SQL de Microsoft
• Bases de datos
• Gestión de datos
• Software de gestión de datos

Citation preview

ÚLTIMAS PUBLICACIONES Home / base de datos / EJERCICIOS SQL SERVER 2014 utilizando NORTHWIND

EJERCICIOS SQL SERVER 2014 UTILIZANDO NORTHWIND TODOPROGRAMACIÓNTECNOLOGÍA Y MÁS 20:00:00 BASE DE DATOS

EJERCICIOS SQL SERVER 2014 utilizando NORTHWIND

TEMAS: Select, where, inner join, group by y funciones agregadas.

1.

Calcular el número de empleados en la empresa.

2.

Calcular el número de clientes y proveedores de la empresa

3.

Calcular el número de productos vendidos por cada empleado.

4.

Calcular el número de productos que nos vende cada proveedor.

5.

Mostrar la categoría que tienen más productos.

6.

Calcular el total de cada orden de venta.

7.

Calcular el monto promedio de todas las ventas.

8.

Muestre los proveedores y el precio del producto más caro y más barato.

9.

Mostrar los empleados con la cantidad de ventas que tiene cada uno.

10.

Mostrar el producto más vendido.

11.

Mostrar el producto menos vendido.

12.

Calcular la mejor venta realizada en la historia de la tienda.

13.

Muestre cada cliente y cuantas órdenes de compra ha realizado en toda la

vida de la empresa. 14.

Mostrar los 10 mejores clientes de la tienda basado en el monto total

de órdenes. 15.

Mostrar 3 empleados , aquellos con mayores ventas.

16.

Mostrar cada empleado y sus montos vendidos por cada año.

17.

Mostrar las ventas agrupadas por año y por mes.

SOLUCIÓN --1 select count(*) as numeroEmpleados from Employees

select * from Customers select * from Suppliers

--2 select COUNT(*) from Customers select COUNT(*) from Suppliers

--3 select e.EmployeeID, LastName+','+FirstName as NOMBRES, count(p.Pro ductID) ascantProductos from Employees as e inner join Orders as o on o.EmployeeID = e.EmployeeID inner join [Order Details] as od on od.OrderID = o.OrderID inner join Products as p on p.ProductID = od.ProductID group by e.EmployeeID, LastName+','+FirstName order by e.EmployeeID

--4 select s.SupplierID, CompanyName, count(ProductID) from Suppliers as s inner join Products as p on p.SupplierID = s.SupplierID group by s.SupplierID, CompanyName

select s.SupplierID, CompanyName, ProductID

from Suppliers as s inner join Products as p on p.SupplierID = s.SupplierID order by s.SupplierID

--5 select top 1 CategoryID, count(ProductID) from Products group by CategoryID order by count(ProductID) desc

--6 select OrderID, sum(UnitPrice*Quantity) as total from [Order Details] group by OrderID

--7 select avg(UnitPrice*Quantity) from [Order Details]

--8 select s.SupplierID, CompanyName, max(UnitPrice) as maxPrecio, min(U nitPrice) asminPrecio from Suppliers as s inner join Products as p on p.SupplierID = s.SupplierID

group by s.SupplierID, CompanyName

--9 select e.EmployeeID, LastName+','+FirstName as NOMBRES, sum(UnitPri ce*Quantity) from Employees as e inner join Orders as o on o.EmployeeID = e.EmployeeID inner join [Order Details] as od on od.OrderID = o.OrderID group by e.EmployeeID, LastName+','+FirstName

--10 select top 1 p.ProductID, sum(od.UnitPrice*Quantity) from Products as p inner join [Order Details] as od on od.ProductID = p.ProductID group by p.ProductID order by sum(od.UnitPrice*Quantity) desc

--11 select top 1 p.ProductID, sum(od.UnitPrice*Quantity) from Products as p inner join [Order Details] as od on od.ProductID = p.ProductID group by p.ProductID order by sum(od.UnitPrice*Quantity)

--12 select top 1 OrderDate, sum(UnitPrice*Quantity) as VENTAS from Orders as o inner join [Order Details] as od on od.OrderID = o.OrderID group by OrderDate order by sum(UnitPrice*Quantity) desc

--13 select c.CustomerID, CompanyName, sum(UnitPrice*Quantity) COMPRAS from Customers as c inner join Orders as o on o.CustomerID = c.CustomerID inner join [Order Details] as od on od.OrderID = o.OrderID group by c.CustomerID, CompanyName

--14 select top 10 c.CustomerID, CompanyName, sum(UnitPrice*Quantity) COMPRAS from Customers as c inner join Orders as o on o.CustomerID = c.CustomerID inner join [Order Details] as od on od.OrderID = o.OrderID group by c.CustomerID, CompanyName

order by COMPRAS desc

--15 select top 3 e.EmployeeID, LastName+','+FirstName as NOMBRES, sum(Quantity*Uni tPrice) from Employees as e inner join Orders as o on o.EmployeeID = e.EmployeeID inner join [Order Details] as od on od.OrderID = o.OrderID group by e.EmployeeID, LastName+','+FirstName order by sum(Quantity*UnitPrice) desc

--16 select e.EmployeeID, LastName+','+FirstName as NOMBRES, year(Order Date) as ANIO,sum(Quantity*UnitPrice) as VENTAS from Employees as e inner join Orders as o on o.EmployeeID = e.EmployeeID inner join [Order Details] as od on od.OrderID = o.OrderID group by e.EmployeeID, LastName+','+FirstName, year(OrderDate) order by e.EmployeeID

--17

select month(OrderDate) as MES, year(OrderDate) as ANIO, sum(UnitPric e*Quantity) asVENTAS from orders as o inner join [Order Details Extended] as od on od.OrderID = o.OrderID group by month(OrderDate), year(OrderDate)