Combine MyProduct

Combine MyProduct

Combine MyProduct and MyPriceHistory displaying the fields into a new temporary table called MyProductPriceHistory. Display ProductID, Name, EndDate, and List Price.

[AdventureWorks2014]

table #MyProductPriceHistory

DisplayProductID int not null,

Name 50

EndDate

ListPrice

into #MyProductPriceHistory

[production].[product].productid, name, enddate, [production].[ProductListPriceHistory].listprice from [production].[product] inner join [production].[ProductListPriceHistory] on [production].[product].productid = [production].[ProductListPriceHistory].ProductID

* from #MyProductPriceHistory

drop table #MyProductPriceHistory

There are a few problems with this query. It pulls in too many results that are not needed. We know we should be looking for Product #723.

Actually, your joins look good above. Had you simply created the first two temp tables and queried them instead of the source tables you would have had it. To your point, you could filter the results for just Product #723 by using a WHERE clause in your initial queries.

Combine MyProduct and MySalesOrderDetail displaying the fields into a new temporary table called MySalesOrderDetailbyName. Display ProductID, Name, SalesOrderID, and Quantity.

use [AdventureWorks2014]

GO

create table #MySalesOrderDetailbyName

(DisplayProductID int not null,

Name nvarchar(50),

SalesOrderID int not null,

Quantity smallint not null)

insert into #MySalesOrderDetailbyName

select [production].[product].productid, name, salesorderid, orderqty FROM Production.Product INNER JOIN Sales.SalesOrderDetail on Production.Product.ProductID=Sales.SalesOrderDetail.ProductID

select * from #MySalesOrderDetailbyName

drop table #MySalesOrderDetailbyName

Like the previous question, there are too many values in this query. . I was given the advice to use SELECT INTO instead of CREATE TABLE to make it run faster, but I did not notice a difference in running this. I would imagine that the SELECT INTO would work well for larger queries, but the first few times I tried it, it did not work.

Combine MySalesOrderDetailbyName and ALLSalesOrderDates displaying the fields into a new temporary table called MySalesHistory. Display ProductID, Name, OrderDate, and SumOfQuantity. This will require you using the SUM and GROUP BY statements.

USE [AdventureWorks2014]

GO

create table #MySalesOrderDetailbyName

(DisplayProductID int not null,

Name nvarchar(50),

SalesOrderID int not null,

Quantity smallint not null)

insert into #MySalesOrderDetailbyName

select [production].[product].productid, name, salesorderid, orderqty FROM Production.Product INNER JOIN Sales.SalesOrderDetail on Production.Product.ProductID=Sales.SalesOrderDetail.ProductID

create table #MySalesHistory

(DisplayProductID int not null,

Name nvarchar(50),

OrderDate datetime not null,

SumOfQuantity int not null)

insert into #MySalesHistory

select #MySalesOrderDetailbyName.DisplayProductID, #MySalesOrderDetailbyName.Name, Sales.SalesOrderHeader.OrderDate, sum(#MySalesOrderDetailbyName.Quantity) FROM #MySalesOrderDetailbyName

INNER JOIN Sales.SalesOrderHeader on #MySalesOrderDetailbyName.SalesOrderID = Sales.SalesOrderHeader.SalesOrderID group by DisplayProductID, name, OrderDate

select * from #MySalesHistory order by DisplayProductID

drop table #MySalesHistory

drop table #MySalesOrderDetailbyName and MyPriceHistory displaying the fields into a new temporary table called MyProductPriceHistory. Display ProductID, Name, EndDate, and List Price.

[AdventureWorks2014]
table #MyProductPriceHistory
DisplayProductID int not null,
Name 50

EndDate

ListPrice

into #MyProductPriceHistory

[production].[product].productid, name, enddate, [production].[ProductListPriceHistory].listprice from [production].[product] inner join [production].[ProductListPriceHistory] on [production].[product].productid = [production].[ProductListPriceHistory].ProductID

* from #MyProductPriceHistory

drop table #MyProductPriceHistory

There are a few problems with this query. It pulls in too many results that are not needed. We know we should be looking for Product #723.

Actually, your joins look good above. Had you simply created the first two temp tables and queried them instead of the source tables you would have had it. To your point, you could filter the results for just Product #723 by using a WHERE clause in your initial queries.

Combine MyProduct and MySalesOrderDetail displaying the fields into a new temporary table called MySalesOrderDetailbyName. Display ProductID, Name, SalesOrderID, and Quantity.

use [AdventureWorks2014]

GO
create table #MySalesOrderDetailbyName
(DisplayProductID int not null,
Name nvarchar(50),
SalesOrderID int not null,
Quantity smallint not null)
insert into #MySalesOrderDetailbyName

select [production].[product].productid, name, salesorderid, orderqty FROM Production.Product INNER JOIN Sales.SalesOrderDetail on Production.Product.ProductID=Sales.SalesOrderDetail.ProductID

select * from #MySalesOrderDetailbyName

drop table #MySalesOrderDetailbyName

Like the previous question, there are too many values in this query. . I was given the advice to use SELECT INTO instead of CREATE TABLE to make it run faster, but I did not notice a difference in running this. I would imagine that the SELECT INTO would work well for larger queries, but the first few times I tried it, it did not work.

Combine MySalesOrderDetailbyName and ALLSalesOrderDates displaying the fields into a new temporary table called MySalesHistory. Display ProductID, Name, OrderDate, and SumOfQuantity. This will require you using the SUM and GROUP BY statements.

use [AdventureWorks2014]

GO

create table #MySalesOrderDetailbyName

(DisplayProductID int not null,

Name nvarchar(50),

SalesOrderID int not null,

Quantity smallint not null)

insert into #MySalesOrderDetailbyName

select [production].[product].productid, name, salesorderid, orderqty FROM Production.Product INNER JOIN Sales.SalesOrderDetail on Production.Product.ProductID=Sales.SalesOrderDetail.ProductID

create table #MySalesHistory

(DisplayProductID int not null,

Name nvarchar(50),

OrderDate datetime not null,

SumOfQuantity int not null)

insert into #MySalesHistory

select #MySalesOrderDetailbyName.DisplayProductID, #MySalesOrderDetailbyName.Name, Sales.SalesOrderHeader.OrderDate, sum(#MySalesOrderDetailbyName.Quantity) FROM #MySalesOrderDetailbyName

INNER JOIN Sales.SalesOrderHeader on #MySalesOrderDetailbyName.SalesOrderID = Sales.SalesOrderHeader.SalesOrderID group by DisplayProductID, name, OrderDate

select * from #MySalesHistory order by DisplayProductID

drop table #MySalesHistory

drop table #MySalesOrderDetailbyName
Powered by