Using the queries completed in the Topic 4 assignment complete the steps below and address the questions.

  1. Create a basic temporary table from work done in Topic 4, question 5 and call it “MyTempTable.” Write a SELECT statement to query the new data.

CREATETABLE #MyTempTable

(

SalesOrderID int,

Quantity int,

ProductID int,

ProductName Varchar (100),

ListPrice decimal (5,2)notnull,

OrderDate date,

);

INSERTINTO #MyTempTable(SalesOrderID, Quantity, ProductID, ProductName, ListPrice, OrderDate)

Values

(44763, 1, 723,’LL Road Frame-Black, 60′, 297.63,’2011-10-31′),

(44763, 1,723,’LL Road Frame-Black, 60′, 306.56,’2011-10-31′),

(44763, 1, 723,’LL Road Frame-Black, 60′, 337.22,’2011-10-31′),

(46662, 4, 723,’LL Road Frame-Black, 60′, 297.63,’2012-05-30′),

(46614, 2, 723,’LL Road Frame-Black, 60′, 306.56,’2012-05-30′),

(46607, 2, 723,’LL Road Frame-Black, 60′, 306.56,’2012-05-30′),

(46660, 5, 723,’LL Road Frame-Black, 60′, 306.56,’2012-06-30′),

(46643, 5, 723,’LL Road Frame-Black, 60′, 337.22,’2012-05-30′),

(53538, 2, 723,’LL Road Frame-Black, 60′, 297.63,’2013-05-30′),

(53616, 3, 723,’LL Road Frame-Black, 60′, 306.56,’2013-07-31′),

(53570, 2, 723,’LL Road Frame-Black, 60′, 337.22,’2013-07-31′)

 

Select*FROM #MyTempTable

 

 

 

 

 

 

 

 

2.Order the results on MyTempTable by SalesOrderID.

Select*FROM #MyTempTable

Order By SalesOrderIDASC

 

Query Output:

 

SalesOrderID Quantity ProductID ProductName ListPrice OrderDate
44763 1 723 LL Road Frame-Black, 60 297.63 10/31/2011
44763 1 723 LL Road Frame-Black, 60 306.56 10/31/2011
44763 1 723 LL Road Frame-Black, 60 337.22 10/31/2011
46607 2 723 LL Road Frame-Black, 60 306.56 5/30/2012
46614 2 723 LL Road Frame-Black, 60 306.56 5/30/2012
46643 5 723 LL Road Frame-Black, 60 337.22 5/30/2012
46660 5 723 LL Road Frame-Black, 60 306.56 6/30/2012
46662 4 723 LL Road Frame-Black, 60 297.63 5/30/2012
53538 2 723 LL Road Frame-Black, 60 297.63 5/30/2013
53570 2 723 LL Road Frame-Black, 60 337.22 7/31/2013
53616 3 723 LL Road Frame-Black, 60 306.56 7/31/2013

 

 

3.Add a timestamp field called “DateRan” and update it to the current date.

 

  • CREATETABLE #MyTempTable

(

SalesOrderID int,

Quantity int,

ProductID int,

ProductName Varchar (100),

ListPrice decimal(5,2)notnull,

OrderDate date,

DateRan DateTime NOT NULL,

);

 

INSERTINTO#MyTempTable(SalesOrderID, Quantity, ProductID, ProductName, ListPrice, OrderDate, DateRan)

Values (44763, 1, 723,’LL Road Frame-Black, 60′, 297.63,’2011-10-31′,GETDATE())

Select*From #MyTempTable

—————————————————————————————–

Query Output:

 

SalesOrderID  Quantity      ProductID     ProductName ListPrice OrderDate DateRan

44763  1      723    LL Road Frame-Black, 60 297.63    2011-10-31 2017-03-01 11:25:53.147

 

 

  1. Explain how to drop the table and start over?

IF OBJECT_ID,

— DROP THE TABLE IF EXIST

IFOBJECT_ID(‘tempdb.dbo.#MyTempTable ‘,’U’)ISNOTNULL

DROPTABLE #MyTempTable;

 

 

  1. How can the table be re-added?

Tables can be added back by above mentioned drop command or simply follow the above-mentioned step 1 to 3, see below the sql command.

— DROP THE TABLE IF EXIST

IFOBJECT_ID(‘tempdb.dbo.MyTempTable’,’U’)ISNOTNULL

DROPTABLE #MyTempTable;

CREATETABLE #MyTempTable

(

SalesOrderID int,

Quantity int,

ProductID int,

ProductName Varchar (100),

ListPrice decimal (5,2)notnull,

OrderDate date,

);

 

INSERTINTO #MyTempTable(SalesOrderID, Quantity, ProductID, ProductName, ListPrice, OrderDate)

Values (44763, 1, 723,’LL Road Frame-Black, 60′, 297.63,’2011-10-31′),

(44763, 1, 723,’LL Road Frame-Black, 60′, 306.56,’2011-10-31′),

(44763, 1, 723,’LL Road Frame-Black, 60′, 337.22,’2011-10-31′),

(46662, 4, 723,’LL Road Frame-Black, 60′, 297.63,’2012-05-30′),

(46614, 2, 723,’LL Road Frame-Black, 60′, 306.56,’2012-05-30′),

(46607, 2, 723,’LL Road Frame-Black, 60′, 306.56,’2012-05-30′),

(46660, 5, 723,’LL Road Frame-Black, 60′, 306.56,’2012-06-30′),

(46643, 5, 723,’LL Road Frame-Black, 60′, 337.22,’2012-05-30′),

(53538, 2, 723,’LL Road Frame-Black, 60′, 297.63,’2013-05-30′),

(53616, 3, 723,’LL Road Frame-Black, 60′, 306.56,’2013-07-31′),

(53570, 2, 723,’LL Road Frame-Black, 60′, 337.22,’2013-07-31′)

 

Select*FROM #MyTempTable

ORDERBY SalesOrderID

 

                                                                                                                                  Order Now

Place Order

Order Custom Essay Papers from us and enjoy discounted prices!