Consultas De BD Northwind Por Jrodrigo Ramirez_FISI_UNAP

USANDO LA BD Nortwnid select top 5 * from Customers select FirstName + '' + lastname as 'nombre completo' from Employees select * from Products select productname Producto , case when discontinued = 1 then 'si' els e 'no' end as suspendido from Products ---------------------------------Hacer que me muestre los suspendidos y no suspendidos select SUM (case when discontinued = 1 then 1 else 0 end) as suspendido s, SUM (case when discontinued = 0 then 1 else 0 end) as 'no suspendidos' from Products ------------PARA ver la ganacia SELECT PRODUCTNAME, UNITPRICE, UNITSINSTOCK, UNITPRICE* UNITSINSTOCK AS GANANCIA FROM PRODUCTS ------para calcular la cantidad de registros en la tabla productos select COUNT(*) from products ------ el avg calcula la suma y lo divide entre la cantidad establecida select AVG(unitprice) from Products

---- para ver el de mayor y menor precio

select MAX(unitprice), MIN(unitprice) from Products ------cuantos animales o paises existen sin repetir para eso es asi: select distinct country from Customers ------ seleccionar la cantidad de clientes cuantos clientes de cada pais existen select distinct country, COUNT(country) from Customers group by Country order by COUNT(country) desc ------ selccionar la cantidad de clientes por paises mayor que 5 select distinct country, COUNT(country) from Customers group by Country having COUNT(country) <= 5

------------usando la tabla detallez

el without te permite sumar las filas como una sumatoria select ORDERID, SUM(UNITPRICE*QUANTITY) from [Order Details] GROUP BY ORDERID WITH ROLLUP ----SELECcionAR EL COD DEL CLIENTE PERO UCON TODA LA FECHA Y ORDENADO ASOI:SELECT CUSTOMERID, CONVERT(CHAR(12),ORDERDATE,103) FROM ORDERS

----seleccionar las edades SELECT FIRSTNAME + '' + LASTNAME AS 'NOMBRE COMPLETO', DATEDIFF(yyyy,BIRTHDATE, GETDATE()) AS EDAD FROM EMPLOYEES ORDER BY EDAD DESC ----- para que me muestre el nombre del producto y el nombre de la categoria select Products.productname,CATEGORIES.CATEGORYNAME from products inner join categories on Products.productname = CATEGORIES.CATEGORYNAME select p.productname producto, c.categoryname categoria from products p inner join Categories c on p.CategoryID = c.CategoryID order by c.categoryname ------------QUE ME ORDENE POR PRODUCTO Y ORDEN select O.ORDERID, P.PRODUCTNAME FROM [Order Details] O INNER JOIN PRODUCTS P on P.PRODUCTID = O.ProductID ORDER BY O.OrderID ------CON UN WHERE select O.ORDERID, P.PRODUCTNAME FROM [Order Details] O INNER JOIN PRODUCTS P on P.PRODUCTID = O.ProductID WHERE O.ORDERID = 10248