Can someone look this over? - 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

Can someone look this over?

Author

Topic

Crima
Starting Member

17 Posts

Posted - 2012-11-01 : 21:37:41

Can someone look over my extra credit (Teacher calls it extra credit but it's part of our grade "You have to do extra for an A")
Thanks :)

-- Extra Credit --

--1. Write an UPDATE statement that modifies the InvoiceCopy table. Change the
--PaymentDate to today’s date and the PaymentTotal to the balance due for each
--invoice with a balance due. Set today’s date with a literal date string, or use the
--GETDATE() function.

UPDATE invoicecopy
SET PaymentDate = GETDATE()
UPDATE invoicecopy
SET PaymentTotal = CreditTotal
WHERE (CreditTotal > 0)

--2. Write an UPDATE statement that modifies the InvoiceCopy table. Change
--TermsID to 2 for each invoice that’s from a vendor with a DefaultTermsID of 2.
--Use a subquery

SELECT COUNT(*)
FROM InvoiceCopy
WHERE VendorID IN (SELECT VendorID
FROM VendorCopy
WHERE DefaultTermsID = 2)
AND TermsID <> 2

--3. Solve exercise 2 using a join rather than a subquery.

--4. Write a DELETE statement for the VendorCopy table. Delete the vendors that
--are located in states from which no vendor has ever sent an invoice.
--Hint: Use a subquery coded with “SELECT DISTINCT VendorState”
--introduced with the NOT IN operator.

DELETE FROM VendorCopy WHERE vendorState NOT IN
(SELECT DISTINCT VendorState FROM VendorCopy)

--5. Write a SELECT statement that returns two columns based on the Vendors
--table. The first column, Contact, is the vendor contact name in this format: first
--name followed by last initial (for example, “John S.”) The second column,
--Phone, is the VendorPhone column without the area code. Only return rows for
--those vendors in the 559 area code. Sort the result set by first name, then last
--name.
select
(vendorcontactfname+' '+vendorcontactLname) as contact,
substring(vendorphone,6,15) as phone
from vendors
where patindex('%559%',substring(vendorphone,1,5))>0
order by vendorcontactfname,vendorcontactLname

--6. Write a SELECT statement that returns the InvoiceNumber and balance due for
--every invoice with a non-zero balance and an InvoiceDueDate that’s less than
--30 days from today.

SELECT InvoiceNumber
FROM Invoices

Eagle_f90
Constraint Violating Yak Guru

424 Posts

Posted - 2012-11-01 : 22:13:34

Here is my input with out giving code as it's your homework to do.

1. Your first update needs a where clause or it will update all paymentdates
2. You did not write an update statment.
3. No code to review given
4 & 5 Look good to me
6. You have no constraint on your SELECT so it is going to pull everything.

--
If I get used to envying others...
Those things about my self I pride will slowly fade away.
-Stellvia

khtan
In (Som, Ni, Yak)

17689 Posts

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

1. it suppose to be ONE update statement. Not TWO.


UPDATE invoicecopy
SET PaymentDate = GETDATE() ,
UPDATE invoicecopy
SET PaymentTotal = CreditTotal
WHERE (CreditTotal > 0)

 
UPDATE InvoiceCopy
SET    TermsID = 2
WHERE  VendorID IN 
       (
          SELECT VendorID
          FROM   VendorCopy
          WHERE  DefaultTermsID = 2
       )
AND    TermsID  <> 2

3. i will show you how the SELECT with INNER JOIN, try to convert it to the UPDATE statement


SELECT < column list here >
FROM   InvoiceCopy AS i
       INNER JOIN VendorCopy AS v  ON i.VendorID = v.VendorID
WHERE  < where condition here >

4. Question does not make sense to me. the NOT IN should be checking from InvoiceCopy table. InvoiceCopy does not contain the VendorState column. So i am not sure how to make use of "SELECT DISTINCT VendorState"
Also, your delete statement there will not delete anything. the subquery will return all distinct VendorState value from VendorCopy table and the DELETE is to delete NOT IN from VendorCopy. Try this and see anyting return


DELETE SELECT * FROM VendorCopy WHERE vendorState NOT IN
(SELECT DISTINCT VendorState FROM VendorCopy)

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

Crima
Starting Member

17 Posts

Posted - 2012-11-02 : 01:31:08

I suppose I could use a "AND" instead of two updates.
On 3 what do you mean column list?

A witty student

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-11-02 : 03:09:24

quote:
Originally posted by Crima

I suppose I could use a "AND" instead of two updates.
On 3 what do you mean column list?

A witty student

i mean you have to type out the list of column name that you wish to see

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources