SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Help please!
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Crima
Starting Member

USA
17 Posts

Posted - 11/01/2012 :  20:28:29  Show Profile  Reply with Quote
Can someone look over my homework please?
Please let me know what I should change, my professor was sick today so I couldn't get help -_- and campus is closed tomorrow and that's when it's due!


-- 1. Write SELECT INTO statements to create two test tables named VendorCopy
--and InvoiceCopy that are complete copies of the Vendors and Invoices tables. If
--VendorCopy and InvoiceCopy already exist, first code two DROP TABLE
--statements to delete them.

SELECT InvoiceID, VendorID, InvoiceNumber, InvoiceDate, InvoiceTotal, PaymentTotal, CreditTotal, TermsID, InvoiceDueDate, PaymentDate
INTO InvoiceCopy
FROM Invoices;
SELECT VendorID, VendorName, VendorAddress1, VendorAddress2, VendorCity, VendorState, VendorZipCode, VendorPhone, VendorContactLName, VendorContactFName,
DefaultTermsID, DefaultAccountNo
INTO VendorCopy
FROM Vendors;

-- 2. Write an INSERT statement that adds a row to the InvoiceCopy table with the
--following values:
--VendorID: 32 InvoiceTotal: $434.58 TermsID: 2
--InvoiceNumber: AX-014-027 PaymentTotal: $0.00
--InvoiceDueDate: 11/8/08
--InvoiceDate: 10/21/08 CreditTotal: $0.00 PaymentDate: null

INSERT INTO InvoiceCopy
VALUES (VendorID = '32', InvoiceTotal = '$434.58', TermsID = '2',
Invoicenumber = 'AX-014-027', PaymentTotal = '$0.00',
InvoiceDueDate = '11/8/08', InvoiceDate = '10/21/08',
CreditTotal = '$0.00', PaymentDate = 'null')
SELECT *
FROM InvoiceCopy


-- 3.Write an INSERT statement that adds a row to the VendorCopy table for each
--non-California vendor in the Vendors table. (This will result in duplicate
--vendors in the VendorCopy table.)

set IDENTITY_INSERT vendorcopy ON
INSERT INTO VendorCopy
(VendorID, VendorName, VendorAddress1, VendorAddress2, VendorCity, VendorState, VendorZipCode, VendorPhone, VendorContactLName, VendorContactFName,
DefaultTermsID, DefaultAccountNo)
SELECT VendorID, VendorName, VendorAddress1, VendorAddress2, VendorCity, VendorState, VendorZipCode, VendorPhone, VendorContactLName, VendorContactFName,
DefaultTermsID, DefaultAccountNo
FROM Vendors AS vnd
WHERE (VendorState <> 'CA')


--4. Write an UPDATE statement that modifies the VendorCopy table. Change the
--default account number to 403 for each vendor that has a default account
--number of 400.

UPDATE VendorCopy
SET DefaultAccountNo = 403
WHERE (DefaultAccountNo = 400)


--5. Write a DELETE statement that deletes all vendors in the state of Minnesota
--from the VendorCopy table.

DELETE FROM VendorCopy
WHERE (VendorState = 'MN')

--6.Write a SELECT statement that returns four columns based on the InvoiceTotal
--column of the Invoices table:
--Use the CAST function to return the first column as data type decimal with 2
--digits to the right of the decimal point.
--Use CAST to return the second column as a varchar.
--Use the CONVERT function to return the third column as the same data type as
--the first column.
--Use CONVERT to return the fourth column as a varchar, using style 1.

select
cast(invoicetotal as decimal) as Column1,
cast(invoicetotal as varchar) as Column2,
convert(decimal,invoicetotal) as column3,
convert(Varchar,invoicetotal) as column4
from invoices





and and all help would be great!

- Crima

khtan
In (Som, Ni, Yak)

Singapore
17658 Posts

Posted - 11/01/2012 :  20:54:11  Show Profile  Reply with Quote
Firstly, do you have a SQL Server to test your code, if not, install the free SQL Server Express edition

1. you missed this part
quote:
If VendorCopy and InvoiceCopy already exist, first code two DROP TABLE statements to delete them.


if exists
   (
       select	*
       from	INFORMATION_SCHEMA.TABLES
       where	TABLE_NAME	= 'InvoiceCopy'
   )
begin
    drop table InvoiceCopy
end


2.

INSERT INTO InvoiceCopy (VendorID, InvoiceTotal, TermsID, InvoiceNumber, PaymentTotal, InvoiceDueDate, InvoiceDate,  CreditTotal, PaymentDate)
VALUES (VendorID = '32', InvoiceTotal = '$ 434.58, TermsID = '2',
Invoicenumber = 'AX-014-027', PaymentTotal = '$ 0.00,
InvoiceDueDate = '11/8/08', InvoiceDate = '10/21/08',
CreditTotal = ' $0.00, PaymentDate = ' null)
SELECT *
FROM InvoiceCopy


OR (after cleaning it up)

INSERT INTO InvoiceCopy (VendorID, InvoiceTotal, TermsID, InvoiceNumber, PaymentTotal, InvoiceDueDate, InvoiceDate,  CreditTotal, PaymentDate)
VALUES ( '32', 434.58, '2', 'AX-014-027', 0.00, '11/8/08', '10/21/08', 0.00, null )


3. Let the identity does its work. Don't pass in the VendorID or you will result in error

set IDENTITY_INSERT vendorcopy ON
INSERT INTO VendorCopy
(VendorID, VendorName, VendorAddress1, VendorAddress2, VendorCity, VendorState, VendorZipCode, VendorPhone, VendorContactLName, VendorContactFName,
DefaultTermsID, DefaultAccountNo)
SELECT VendorID, VendorName, VendorAddress1, VendorAddress2, VendorCity, VendorState, VendorZipCode, VendorPhone, VendorContactLName, VendorContactFName,
DefaultTermsID, DefaultAccountNo
FROM Vendors AS vnd
WHERE (VendorState <> 'CA')


4. OK

5. OK

6. You should specify the size or precision of the data type

select
       cast(invoicetotal as decimal(10,2)) as Column1,
       cast(invoicetotal as varchar(10)) as Column2,
       convert(decimal(10,2),invoicetotal) as column3,
       convert(Varchar(10),invoicetotal, 1) as column4
from   invoices 



KH
Time is always against us

Go to Top of Page

chadmat
The Chadinator

USA
1974 Posts

Posted - 11/01/2012 :  21:02:12  Show Profile  Visit chadmat's Homepage  Reply with Quote
1. You didn't include the conditional drop statements.
2. Syntax is incorrect http://msdn.microsoft.com/en-us/library/ms174335.aspx
Select not needed
3. Looks pretty much correct...you may not need the SET IDENTITY INSERT ON
4. Looks correct
5. Remove FROM
6. Decimal needs more info (precision and scale), varchar needs more info (length)

-Chad
Go to Top of Page

chadmat
The Chadinator

USA
1974 Posts

Posted - 11/01/2012 :  21:03:23  Show Profile  Visit chadmat's Homepage  Reply with Quote
quote:
Originally posted by khtan

Firstly, do you have a SQL Server to test your code, if not, install the free SQL Server Express edition

1. you missed this part
quote:
If VendorCopy and InvoiceCopy already exist, first code two DROP TABLE statements to delete them.


if exists
   (
       select	*
       from	INFORMATION_SCHEMA.TABLES
       where	TABLE_NAME	= 'InvoiceCopy'
   )
begin
    drop table InvoiceCopy
end


2.

INSERT INTO InvoiceCopy (VendorID, InvoiceTotal, TermsID, InvoiceNumber, PaymentTotal, InvoiceDueDate, InvoiceDate,  CreditTotal, PaymentDate)
VALUES (VendorID = '32', InvoiceTotal = '$ 434.58, TermsID = '2',
Invoicenumber = 'AX-014-027', PaymentTotal = '$ 0.00,
InvoiceDueDate = '11/8/08', InvoiceDate = '10/21/08',
CreditTotal = ' $0.00, PaymentDate = ' null)
SELECT *
FROM InvoiceCopy


OR (after cleaning it up)

INSERT INTO InvoiceCopy (VendorID, InvoiceTotal, TermsID, InvoiceNumber, PaymentTotal, InvoiceDueDate, InvoiceDate,  CreditTotal, PaymentDate)
VALUES ( '32', 434.58, '2', 'AX-014-027', 0.00, '11/8/08', '10/21/08', 0.00, null )


3. Let the identity does its work. Don't pass in the VendorID or you will result in error

set IDENTITY_INSERT vendorcopy ON
INSERT INTO VendorCopy
(VendorID, VendorName, VendorAddress1, VendorAddress2, VendorCity, VendorState, VendorZipCode, VendorPhone, VendorContactLName, VendorContactFName,
DefaultTermsID, DefaultAccountNo)
SELECT VendorID, VendorName, VendorAddress1, VendorAddress2, VendorCity, VendorState, VendorZipCode, VendorPhone, VendorContactLName, VendorContactFName,
DefaultTermsID, DefaultAccountNo
FROM Vendors AS vnd
WHERE (VendorState <> 'CA')


4. OK

5. OK

6. You should specify the size or precision of the data type

select
       cast(invoicetotal as decimal(10,2)) as Column1,
       cast(invoicetotal as varchar(10)) as Column2,
       convert(decimal(10,2),invoicetotal) as column3,
       convert(Varchar(10),invoicetotal, 1) as column4
from   invoices 



KH
Time is always against us





Or we could just do it for you

-Chad
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17658 Posts

Posted - 11/01/2012 :  22:18:56  Show Profile  Reply with Quote
i am feeling very kind today


KH
Time is always against us

Go to Top of Page

waterduck
Aged Yak Warrior

Malaysia
965 Posts

Posted - 11/01/2012 :  22:54:36  Show Profile  Reply with Quote
*like*
Go to Top of Page

Crima
Starting Member

USA
17 Posts

Posted - 11/02/2012 :  02:46:09  Show Profile  Reply with Quote
Could you explain a little more on 6, " You should specify the size or precision of the data type" ?

A witty student
Go to Top of Page

chadmat
The Chadinator

USA
1974 Posts

Posted - 11/02/2012 :  02:58:38  Show Profile  Visit chadmat's Homepage  Reply with Quote
http://msdn.microsoft.com/en-us/library/ms187746.aspx

-Chad
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2218 Posts

Posted - 11/02/2012 :  03:08:46  Show Profile  Reply with Quote
quote:
Originally posted by Crima

Could you explain a little more on 6, " You should specify the size or precision of the data type" ?

A witty student



6. You should specify the size or precision of the data type


select
cast(invoicetotal as decimal(10,2)) as Column1,
cast(invoicetotal as varchar(10)) as Column2,
convert(decimal(10,2),invoicetotal) as column3,
convert(Varchar(10),invoicetotal, 1) as column4
from invoices

First you should know the actual size of invoicetotal column in the table invoices, then replace that size with this red marked part

sp_help invoices -- To verify size of columns



--
Chandu
Go to Top of Page

Crima
Starting Member

USA
17 Posts

Posted - 11/02/2012 :  14:05:19  Show Profile  Reply with Quote
Thank you so much! you made me understand what my professor could not.

A witty student
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2218 Posts

Posted - 11/05/2012 :  01:17:20  Show Profile  Reply with Quote
Welcome

--
Chandu
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000