Help please! - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help please!

Author

Topic

Crima
Starting Member

17 Posts

Posted - 2012-11-01 : 20:28:29

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)

17689 Posts

Posted - 2012-11-01 : 20:54:11

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


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
[spoiler]Time is always against us[/spoiler]

chadmat
The Chadinator

1974 Posts

Posted - 2012-11-01 : 21:02:12

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

chadmat
The Chadinator

1974 Posts

Posted - 2012-11-01 : 21:03:23

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


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
[spoiler]Time is always against us[/spoiler]

Or we could just do it for you

-Chad

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-11-01 : 22:18:56

i am feeling very kind today

KH
[spoiler]Time is always against us[/spoiler]

waterduck
Aged Yak Warrior

982 Posts

Posted - 2012-11-01 : 22:54:36

*like*

Crima
Starting Member

17 Posts

Posted - 2012-11-02 : 02:46:09

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

A witty student

chadmat
The Chadinator

1974 Posts

Posted - 2012-11-02 : 02:58:38

http://msdn.microsoft.com/en-us/library/ms187746.aspx

-Chad

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-11-02 : 03:08:46

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

Crima
Starting Member

17 Posts

Posted - 2012-11-02 : 14:05:19

Thank you so much! you made me understand what my professor could not.

A witty student

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-11-05 : 01:17:20

Welcome

--
Chandu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources