editat perDarrera edicióDiferènciesRestaurar Revisió
daniel herrera24/08/2018 19:34
@@ -1,7 +1,7 @@
 
 Llenguatges SQL: DML i DDL
 SQL Exercicis 4: Visualització de dades de vàries taules
-A la base de dades [AdventureWorks (Ms-PL, Microsoft Public License)](https://msftdbprodsamples.codeplex.com/):
+A la base de dades [AdventureWorks (Ms-PL, Microsoft Public License)](https://github.com/Microsoft/sql-server-samples/releases/tag/adventureworks):
 
 * [Introducció exercici Visualització de dades de Vàries taules](https://youtu.be/yAVgreAOC4Y) YT 31'
 
Restaurar
daniel herrera15/01/2018 19:20
@@ -23,6 +23,12 @@
     where
        c.name = 'Bikes'
 
+3.1) Selecciona el nom de la persona (nom i cognom ) amb els seus números de telèfon ( t'apareixerà un telèfon per filera, és ok ). Nota: has de fer join de *persones* amb *telèfons* mitjançant el camp `BusinessEntityID`
+
+3.2) Selecciona el nom de la persona (nom i cognom ) amb les seves adreces de correu electrònic. Nota: has de fer join de *persones* amb *emails* mitjançant el camp `BusinessEntityID`
+
+3.3) Modifica 3.1 per tal que aparegui, a més, el tipus de telèfon `PhoneNumberType`. Nota: has de fer el join de *telèfons* amb *tipus de telèfon* mitjançant el camp `PhoneNumberTypeID`
 3) Els telefons de contacte de tipus 'Feina' (`[Person].[PhoneNumberType]`) de les persones ( `[Person].[Person]` ) amb un email ( `[Person].[EmailAddress]` ) que comenci per 'e'. Mostrar nom de la persona i cognom i número de telèfon. No posar repetits. Nota: has de fer join de *persones* amb *emails* (mitjançant `BusinessEntityID`), *persones* amb *telèfons* (mitjançant `BusinessEntityID`) i *telèfons* amb *tipus de telèfon* (mitjançant `PhoneNumberTypeID`):
 
     :::sql
Restaurar
daniel herrera12/01/2018 17:02
@@ -41,7 +41,7 @@
 
 4) El nom i cognom de les 10 persones `.[Person].[Person]` que han canviat el password `.[Person].[Password]` més recentment. Fes el join amb `[BusinessEntityID]`
 
-5) Entre producte i foto del producte hi ha una relació N:M. Fes una consulta per averiguar quan és va fer la darrera sessió de fotos ( ordenant les fotos per la data de manera descendent ) Llavors mostra els productes que apareixen a les fotos que es van fer aquell dia.
+5) Entre producte i foto del producte hi ha una relació N:M. **Primera part**: Fes una consulta per averiguar quan és va fer la darrera sessió de fotos ( ordenant les fotos per la data de manera descendent ) Apunta la data en un paper. **Segona part**: Ara fés una nova consulta per mostrar els productes que apareixen a les fotos que es van fer a la data trobada a la primera part. **Nota**: Al ser una interrelació N:M entre foto i producte, has de trobar una tercera taula que contindrà FKs a les dues taules esmentades.
 
 6) Crea una consulta per mostrar els productes que es van posar a la venda després del producte amb codi 'WB-H098'
 
Restaurar
daniel herrera12/01/2018 16:43
@@ -23,7 +23,21 @@
     where
        c.name = 'Bikes'
 
-3) Els telefons de contacte de tipus 'Feina' (`[Person].[PhoneNumberType]`) de les persones ( `[Person].[Person]` ) amb un email ( `[Person].[EmailAddress]` ) que comenci per 'e'. Mostrar nom de la persona i cognom i número de telèfon. No posar repetits.
+3) Els telefons de contacte de tipus 'Feina' (`[Person].[PhoneNumberType]`) de les persones ( `[Person].[Person]` ) amb un email ( `[Person].[EmailAddress]` ) que comenci per 'e'. Mostrar nom de la persona i cognom i número de telèfon. No posar repetits. Nota: has de fer join de persones amb emails (mitjançant BusinessEntityID), persones amb telèfons (mitjançant BusinessEntityID) i telèfons amb tipus de telèfon (mitjançant PhoneNumberTypeID):
+
+    :::sql
+    select distinct 
+           P.FirstName, p.LastName, F.PhoneNumber
+    from
+       [Person].[Person] P
+    inner join [Person].[EmailAddress] E
+       on P.BusinessEntityID = E.BusinessEntityID
+    inner join [Person].[PersonPhone] F
+       on F.BusinessEntityID = P.BusinessEntityID
+    inner join [Person].[PhoneNumberType] NT
+       on F.PhoneNumberTypeID = NT.PhoneNumberTypeID
+    WHERE E.EmailAddress like 'e%'
+          and NT.Name = 'Work'
 
 4) El nom i cognom de les 10 persones `.[Person].[Person]` que han canviat el password `.[Person].[Password]` més recentment. Fes el join amb `[BusinessEntityID]`
 
Restaurar
daniel herrera12/01/2018 16:10
@@ -3,6 +3,7 @@
 SQL Exercicis 4: Visualització de dades de vàries taules
 A la base de dades [AdventureWorks (Ms-PL, Microsoft Public License)](https://msftdbprodsamples.codeplex.com/):
 
+* [Introducció exercici Visualització de dades de Vàries taules](https://youtu.be/yAVgreAOC4Y) YT 31'
 
 1) Escriu una consulta per mostrar el correu de la persona que fa el review, el nom del producte ( `[Production].[Product]` ) i el rating per a tots els reviews ( `[Production].[ProductReview]`) amb rating de 5 estrelles i que l'adreça de correu comenci per l.
 
Restaurar
daniel herrera08/01/2018 19:07
@@ -24,9 +24,13 @@
 
 3) Els telefons de contacte de tipus 'Feina' (`[Person].[PhoneNumberType]`) de les persones ( `[Person].[Person]` ) amb un email ( `[Person].[EmailAddress]` ) que comenci per 'e'. Mostrar nom de la persona i cognom i número de telèfon. No posar repetits.
 
-4) Crea una consulta per mostrar elproductes que es van posar a la venda després del producte amb codi 'WB-H098'
+4) El nom i cognom de les 10 persone`.[Person].[Person]` que han canviat el password `.[Person].[Password]` més recentment. Fes el join amb `[BusinessEntityID]`
 
-5) **Bonus point** Totes les persones que han coincidit amb Laura Norman en algun departament, cal posar les dates en que va treballar Laura, les dates en que va treballar l'altre persona i el nom de departament.
+5) Entre producte i foto del producte hi ha una relació N:M. Fes una consulta per averiguar quan és va fer la darrera sessió de fotos ( ordenant les fotos per la data de manera descendent ) Llavors mostra els productes que apareixen a les fotos que es van fer aquell dia.
+
+6) Crea una consulta per mostrar els productes que es van posar a la venda després del producte amb codi 'WB-H098'
+
+7) **Bonus point** Totes les persones que han coincidit amb Laura Norman en algun departament, cal posar les dates en que va treballar Laura, les dates en que va treballar l'altre persona i el nom de departament.
 
 **Exemple de consulta**
 
Restaurar
daniel herrera30/01/2017 10:32
@@ -1,6 +1,9 @@
 
 Llenguatges SQL: DML i DDL
 SQL Exercicis 4: Visualització de dades de vàries taules
+A la base de dades [AdventureWorks (Ms-PL, Microsoft Public License)](https://msftdbprodsamples.codeplex.com/):
+
+
 1) Escriu una consulta per mostrar el correu de la persona que fa el review, el nom del producte ( `[Production].[Product]` ) i el rating per a tots els reviews ( `[Production].[ProductReview]`) amb rating de 5 estrelles i que l'adreça de correu comenci per l.
 
 2) Crea un llistat únic de tots els productes de la categoria 'Bikes'. Inclou a la consulta el nom de la categoria i la subcategoria.
Restaurar
daniel herrera12/12/2016 20:33
@@ -3,7 +3,21 @@
 SQL Exercicis 4: Visualització de dades de vàries taules
 1. Escriu una consulta per mostrar el correu de la persona que fa el review, el nom del producte ( `[Production].[Product]` ) i el rating per a tots els reviews ( `[Production].[ProductReview]`) amb rating de 5 estrelles i que l'adreça de correu comenci per l.
 
-2. Crea un llistat únic de tots els productes de la categoria 'Bike. Inclou a la consulta el nom de la categoria i la subcategoria.
+2. Crea un llistat únic de tots els productes de la categoria 'Bikes'. Inclou a la consulta el nom de la categoria i la subcategoria.
+
+    :::sql
+    select top 100
+       c.name, s.name, p.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
+    where
+       c.name = 'Bikes'
 
 3. Els telefons de contacte de tipus 'Feina' (`[Person].[PhoneNumberType]`) de les persones ( `[Person].[Person]` ) amb un email ( `[Person].[EmailAddress]` ) que comenci per 'e'. 
 
Restaurar
daniel herrera12/12/2016 19:58
@@ -1,7 +1,7 @@
 
 Llenguatges SQL: DML i DDL
 SQL Exercicis 4: Visualització de dades de vàries taules
-1. Escriu una consulta per mostrar el correu de la persona que fa el review, el nom del producte i el rating per a tots els reviews amb rating de 5 estrelles i que l'adreça de correu comenci per l.
+1. Escriu una consulta per mostrar el correu de la persona que fa el review, el nom del producte i el rating per a tots els reviews ( `[Production].[ProductReview]`) amb rating de 5 estrelles i que l'adreça de correu comenci per l.
 
 2. Crea un llistat únic de tots els productes de la categoria 'Bike. Inclou a la consulta el nom de la categoria i la subcategoria.
 
Restaurar
daniel herrera12/12/2016 19:49
@@ -1,15 +1,40 @@
 
 Llenguatges SQL: DML i DDL
-SQL Exercicis 4:
+SQL Exercicis 4: Visualització de dades de vàries taules
 1. Escriu una consulta per mostrar el correu de la persona que fa el review, el nom del producte i el rating per a tots els reviews amb rating de 5 estrelles i que l'adreça de correu comenci per l.
 
 2. Crea un llistat únic de tots els productes de la categoria 'Bike. Inclou a la consulta el nom de la categoria i la subcategoria.
 
-3. Els telefons de contacte de tipus 'Feina' ([Person].[PhoneNumberType]) de les persones ( [Person].[Person] ) amb un email que comenci per 'e'.
+3. Els telefons de contacte de tipus 'Feina' ([Person].[PhoneNumberType]) de les persones ( [Person].[Person] ) amb un email ( [Person].[EmailAddress] ) que comenci per 'e'. 
 
 4. Crea una consulta per mostrar els productes que es van posar a la venda després del producte amb codi 'WB-H098'
 
 5. **Bonus point** Totes les persones que han coincidit amb Laura Norman en algun departament, cal posar les dates en que va treballar Laura, les dates en que va treballar l'altre persona i el nom de departament.
+
+**Exemple de consulta**
+
+Treballadors acompanyats del departament on estan treballant actualment, ordenat per depatament i dins de departament per nom del treballador:
+
+
+    :::sql
+    select p.FirstName, d.Name
+    from [HumanResources].[Department] D
+    inner join [HumanResources].[EmployeeDepartmentHistory] h
+      on d.DepartmentID = h.[DepartmentID] 
+    inner join [HumanResources].[Employee] e
+      on e.[BusinessEntityID] = h.[BusinessEntityID]
+    inner join [Person].[Person] p
+      on e.[BusinessEntityID] = p.[BusinessEntityID]
+    where
+      h.EndDate is null
+    order by
+      d.Name, p.FirstName 
+
+**Outer joins**
+
+a. Modifica l'exercici 1 per tal que apareguin totes les categories encara que no tinguin subcategoria o producte.
+
+b. Modifica l'exercici 3 per a que apareguin també les persones que no tinguin telèfon de tipus feina.
 Continguts: 
 
 Resultats d'aprenentatge: 
Restaurar
Consulteu nota legal i condicions