1 Return the BusinessEntityID and SalesYTD column from Sales

1) Return the BusinessEntityID and SalesYTD column from Sales.SalesPerson table. Join this table to the Sales.SalesTerritory table in such a way that every in Sales.SalesPerson will be returned regardless of whether or not they are assigned to a territory. Also, return the Name column from Sales.SalesTerritory.

2) Using the previous example as your foundation, join to the Person.Person table to return the sales person’s first name and last name. Now, only include those rows where the territory’s name is either “Northeast” or “Central”

3) Return the Actor name and Actor ID from the Actors table. Join this table to the ActorsMoviesBridge table in such a way that every Actor Name and ID in Actors table will be returned regardless of whether or not they are assigned to a movie. Then join to the Movies table to return the movie names for the actors.

4) Take the previous question as a base and then group the actors, ID and show how many movie each actor has acted in. Order it by the Actor name.

5. HAVING Clause

Find the total sales by territory for all rows in the Sales.SalesOrderHeader table. Return only those territories that have exceeded $10 million     in historical sales. Return the total sales and the TerritoryID column.

Solution

SELECT name, ID, moviename FROM Actor OUTER JOIN ActorsMoviesBridge ON ActorID=ActorsMoviesBridgeID INNER JOIN Movies ON Actor.moviesID=Movies.moviesIDSELECT name, ID, moviename FROM Actor OUTER JOIN ActorsMoviesBridge ON ActorID=ActorsMoviesBridgeID INNER JOIN Movies ON Actor.moviesID=Movies.moviesIDSELECT BusinessEntityID, SalesYTDn Name FROM Sales.SalesPerson FULL OUTER JOIN  Sales.SalesTerritory ON Sales.SalesPersonID=Sales.SalesTerritoryID

The given query will output BusinessEntityID and SalesYTD column and Name atfer joining Sales.SalesPersonID and Sales.SalesTerritoryID

2)

SELECT Fname, Lname FROM Sales.SalesPerson INNER JOIN  Sales.SalesTerritory ON Sales.SalesPersonID=Sales.SalesTerritoryID WHERE Teritory name=  “Northeast” or “Central”

3)

SELECT Actor.name, Actor.ID, Movies.moviename FROM Actor OUTER JOIN ActorsMoviesBridge ON ActorID=ActorsMoviesBridgeID INNER JOIN Movies ON Actor.moviesID=Movies.moviesID

4)

SELECT Actor.name, Count(Actor.ID), Movies.moviename FROM Actor OUTER JOIN ActorsMoviesBridge ON ActorID=ActorsMoviesBridgeID INNER JOIN Movies ON Actor.moviesID=Movies.moviesID WHERE GROUP BY Actor.ID ORDER BY Actor.name