| Author |
 Topic  |
|
|
Crima
Starting Member
USA
17 Posts |
 Posted - 11/01/2012 : 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)
Singapore
16769 Posts |
Posted - 11/01/2012 : 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
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
|
 |
|
|
chadmat
The Chadinator
USA
1962 Posts |
Posted - 11/01/2012 : 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
USA
1962 Posts |
Posted - 11/01/2012 : 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
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 |
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16769 Posts |
Posted - 11/01/2012 : 22:18:56
|
i am feeling very kind today 
KH
Time is always against us
|
|
|
|
waterduck
Aged Yak Warrior
Malaysia
806 Posts |
Posted - 11/01/2012 : 22:54:36
|
| *like* |
|
|
|
Crima
Starting Member
USA
17 Posts |
Posted - 11/02/2012 : 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
USA
1962 Posts |
|
|
bandi
Flowing Fount of Yak Knowledge
India
1718 Posts |
Posted - 11/02/2012 : 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
USA
17 Posts |
Posted - 11/02/2012 : 14:05:19
|
Thank you so much! you made me understand what my professor could not.
A witty student |
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1718 Posts |
Posted - 11/05/2012 : 01:17:20
|
Welcome
--
Chandu |
|
|
| |
Topic |
|
|
|
Help please!
