editat perDarrera edicióDiferènciesRestaurar Revisió
daniel herrera26/01/2018 17:33
@@ -43,7 +43,7 @@
     
 7) Escriu una consulta per conèixer la diferència entre el preu més alt i el més baix.Tingues en compte només aquells productes amb preu superior a 100. Etiqueta la columna com a diferència.
 
-8) Mostra el preu mig dels productes de cada categoria. No incloguis les categories amb un preu mig per producte inferior a 300. Ordena de més petit a més gran.
+8) Mostra el preu mig dels productes de cada categoria. No incloguis les categories amb un preu mig per producte inferior a 300. (Mira l'[ajuda clàusula having](https://youtu.be/SqfbEtfDXzA) ) Ordena de més petit a més gran.
 
     :::SQL
     SELECT AVG( P.LISTPRICE) AS "Preu Mig", c.Name
Restaurar
daniel herrera30/01/2017 10:33
@@ -1,6 +1,9 @@
 
 Llenguatges SQL: DML i DDL
 SQL Exercicis 5.1: Agregació de dades fent servir funcions de grup
+A la base de dades [AdventureWorks (Ms-PL, Microsoft Public License)](https://msftdbprodsamples.codeplex.com/):
+
+
 1) Les funcions de grup passen per moltes fileres per produir un resultat per grup. Cert o fals?
 
 2) Tractament sistemàtic dels valors Nulls:
Restaurar
daniel herrera20/12/2016 19:43
@@ -88,6 +88,29 @@
 
 
 12) Crea una matriu on aparegui el nom de cada categoria i la quantitat de productes posats a la venda els anys 2011,2012 i 2013. Crea una columna per cada any amb el nom. Hauràs de fer servir el `case`.
+
+
+
+    :::sql
+    SELECT c.name as "Categoria",
+           count(  case when year(p.sellstartdate)=2005 then 1 else null end  )  AS "2005", 
+           count(  case when year(p.sellstartdate)=2006 then 1 else null end  ) AS "2006", 
+           count(  case when year(p.sellstartdate)=2007 then 1 else null end  ) AS "2007"
+    FROM Production.Product p
+    INNER JOIN Production.ProductSubcategory s
+       ON P.ProductSubcategoryID = S.ProductSubcategoryID
+    INNER JOIN Production.ProductCategory C
+       ON C.ProductCategoryID = S.ProductCategoryID
+    GROUP BY  c.name
+    ORDER BY c.name
+
+Resultat exemple:
+
+    Categoria 2005 2006 2007
+    Accessories 3 7 19
+    Bikes 30 23 44
+    Clothing 7 16 12
+    Components 32 39 61
 Continguts: 
 
 Resultats d'aprenentatge: 
Restaurar
daniel herrera20/12/2016 19:27
@@ -80,6 +80,13 @@
 
 11) Mostra la quantitat mitjana de telèfons de contacte de tots treballadors. Inclús els que no tenen telèfons de contacte.
 
+    :::sql
+    select p.FirstName, p.LastName, count(*)
+    from Person.Person p left outer join Person.PersonPhone t
+       on p.BusinessEntityID = t.BusinessEntityID
+    group by p.FirstName, p.LastName
+
+
 12) Crea una matriu on aparegui el nom de cada categoria i la quantitat de productes posats a la venda els anys 2011,2012 i 2013. Crea una columna per cada any amb el nom. Hauràs de fer servir el `case`.
 Continguts: 
 
Restaurar
daniel herrera20/12/2016 19:20
@@ -65,6 +65,19 @@
 
 10) Mostra el número total de productes posat en venda cada categoria i any. Ordenat per categoria i any.
 
+    :::sql
+    SELECT c.name as "Categoria",
+           YEAR(p.SellStartDate) AS "ANY", 
+           COUNT( * ) AS "NUMERO DE PRODUCTES POSATS EN VENDA"
+    FROM Production.Product p
+    INNER JOIN Production.ProductSubcategory s
+       ON P.ProductSubcategoryID = S.ProductSubcategoryID
+    INNER JOIN Production.ProductCategory C
+       ON C.ProductCategoryID = S.ProductCategoryID
+    GROUP BY YEAR(p.SellStartDate), c.name
+    ORDER BY c.name, YEAR(p.SellStartDate)
+
+
 11) Mostra la quantitat mitjana de telèfons de contacte de tots treballadors. Inclús els que no tenen telèfons de contacte.
 
 12) Crea una matriu on aparegui el nom de cada categoria i la quantitat de productes posats a la venda els anys 2011,2012 i 2013. Crea una columna per cada any amb el nom. Hauràs de fer servir el `case`.
Restaurar
daniel herrera20/12/2016 19:13
@@ -40,7 +40,19 @@
     
 7) Escriu una consulta per conèixer la diferència entre el preu més alt i el més baix.Tingues en compte només aquells productes amb preu superior a 100. Etiqueta la columna com a diferència.
 
-8) Mostra el preu mig dels productes de cada categoria. No incloguis les categories amb un preu mig per producte inferior a 1000. Ordena de més petit a més gran.
+8) Mostra el preu mig dels productes de cada categoria. No incloguis les categories amb un preu mig per producte inferior a 300. Ordena de més petit a més gran.
+
+    :::SQL
+    SELECT AVG( P.LISTPRICE) AS "Preu Mig", c.Name
+    FROM Production.Product p
+    INNER JOIN Production.ProductSubcategory s
+       ON P.ProductSubcategoryID = S.ProductSubcategoryID
+    INNER JOIN Production.ProductCategory C
+       ON C.ProductCategoryID = S.ProductCategoryID
+    GROUP BY c.Name
+    HAVING AVG(P.LISTPRICE) >= 300
+    ORDER BY  AVG( P.LISTPRICE) 
+
 
 9) Mostra el número total de productes posat en venda cada any. Ordenat per any.
 
Restaurar
daniel herrera19/12/2016 20:35
@@ -38,7 +38,7 @@
     select count( distinct productsubcategoryId )
     from Production.Product
     
-7) Escriu una consulta per conèixer la diferència entre el preu més alt i el més baix pe que sigui superior a 100. Etiqueta la columna com a diferència.
+7) Escriu una consulta per conèixer la diferència entre el preu més alt i el més baix.Tingues en compte només aquells productes amb preu superior a 100. Etiqueta la columna com a diferència.
 
 8) Mostra el preu mig dels productes de cada categoria. No incloguis les categories amb un preu mig per producte inferior a 1000. Ordena de més petit a més gran.
 
Restaurar
daniel herrera19/12/2016 20:30
@@ -17,6 +17,21 @@
 5) Modifica la consulta anterior per mostrar la quantitat de productes, el preu més alt, el més baix (diferent de 0), la suma i el preu mitjà de tots els productes agrupats per categoria.
 
 
+    :::sql
+     select c.[Name],
+            count( * ) as productes,
+            max( p.listprice ) as [preu màxim],
+     min(p.listPrice ) as [ preu mínim],
+     sum(p.listPrice) as total,
+     round( avg( p.listPrice),3 ) as [preu mitjà]
+     from Production.product p 
+     inner join [Production].[ProductSubcategory] S
+        on p.ProductSubcategoryID = s.ProductSubcategoryID
+     inner join [Production].[ProductCategory] C
+        on c.ProductCategoryID = S.ProductCategoryID
+     where p.listPrice > 0
+     group by c.Name
+
 6) Calcula quantes subcategories diferents tenen producte sense enumerar-les. Utilitza la clau forana de producte a subcategoria per a fer el recompte.
 
 7) Escriu una consulta per conèixer la diferència entre el preu més alt i el més baix però que sigui superior a 100. Etiqueta la columna com a diferència.
Restaurar
daniel herrera19/12/2016 20:21
@@ -12,7 +12,7 @@
 3) La clàusula where restringeix les fileres abans de la seva inclusió al càlcul de grup. Cert/Fals.
 
 
-4) Mostra el la quantitat de productes, preu més alt, el més baix (diferent de 0), la suma i el preu mitjà de tots els productes. Arrodoneix el resultat a les centenes.
+4) Mostra el la quantitat de productes, preu més alt, el més baix, la suma i el preu mitjà de tots els productes amb preu superior a 0. Arrodoneix el resultat a les centenes.
 
 5) Modifica la consulta anterior per mostrar la quantitat de productes, el preu més alt, el més baix (diferent de 0), la suma i el preu mitjà de tots els productes agrupats per categoria.
 
Restaurar
daniel herrera14/12/2016 19:36
@@ -1,6 +1,6 @@
 
 Llenguatges SQL: DML i DDL
-SQL Exercicis 5: Agregació de dades fent servir funcions de grup
+SQL Exercicis 5.1: Agregació de dades fent servir funcions de grup
 1) Les funcions de grup passen per moltes fileres per produir un resultat per grup. Cert o fals?
 
 2) Tractament sistemàtic dels valors Nulls:
Restaurar
Consulteu nota legal i condicions