4 SQL questions - 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

4 SQL questions

Author

Topic

Obsivus
Starting Member

17 Posts

Posted - 2010-10-20 : 21:09:05

Greetings everyone!

there is 4 SQL exercises I have problems with.. I would appreciate alot for some help.

These are the two tables I need to use for this exercises.

________________
Invoices Table

InvoiceID |
VendorID |
InvoiceNumber |
InvoiceDate |
InvoiceTotal |
PaymentTotal |
CreditTotal |
TermsID |
InvoiceDueDate |
PaymentDate |
________________|

________________
Vendors table

VendorID |
VendorName |
VendorAdress1 |
VendorAdress2 |
VendorCity |
VendorState |
VendorZipCode |
VendorPhone |__
VendorContactLName |
VendorContactFName |
DefaultTermsID |
DefaultAccountNo |
___________________|

First question is that I need to type a select statement that returns the 4 colums below for all the Vendors that only have one Invoice from the invoice table.

Vendorname
InvoiceNumber
InvoiceDate
InvoiceTotal

I have been reading and trying for like 6 hours today and I just cant figure it out I came this far:

SELECT Vendorname, InvoiceNumber, InvoiceDate, Invoicetotal
FROM Vendors join Invoices ON Vendors.VendorID = Invoices.VendorID
ORDER BY Vendorname

What i get as result from this Select statement is all the vendors that have Invoices, but not Vendors with one Invoice only.

The second question is:

a Select statement that gives result on VendorID and Vendorname that doesnt have Invoice, and FULL JOIN need to be used on this exercise.

SELECT Vendorname, InvoiceNumber, InvoiceDate, Invoicetotal
FROM Vendors FULL JOIN Invoices (ON Vendors.VendorID = Invoices.VendorID) AND (Vendors.Vendorsid is null)
ORDER BY Vendorname

Thats what I came up with but I think its far away from the questions answer.

the third Question is:

Type a select statement that returns the 3 colums below from Vendors table

VendorName
VendorCity
VendorState

For the persons that lives in a city in a state, where atleast one person or more persons lives at.(Same state and city)

This is what I came up with not sure if its the right answer

Select V1.VendorName, V1.Vendorcity, V1.VendorState
from Vendors as V1 join Vendors as V2
where (V1.VendorID <> V2.VendorsID)
AND (V1.Vendorcity = V2.Vendorcity)
AND (V1.VendorState = V2.VendorState)
Order by Vendorname

Last question:

Write a SELECT statement that returns four columns:

VendorName
InvoiceNumber
InvoiceDate
InvoiceTotal

Return one row per vendor representing the vendor’s invoice with the earliest date.

No clue =(

I would appreciate alot if someone could help me with these Select statement.

Thanks alot

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-10-21 : 01:46:59

Homework?

--
Gail Shaw
SQL Server MVP

Sachin.Nand

2937 Posts

Posted - 2010-10-21 : 01:58:08

quote:

First question is that I need to type a select statement that returns the 4 colums below for all the Vendors that only have one Invoice from the invoice table.

Vendorname
InvoiceNumber
InvoiceDate
InvoiceTotal

I have been reading and trying for like 6 hours today and I just cant figure it out.

You are so near.Try this




SELECT Vendorname, InvoiceNumber, InvoiceDate, Invoicetotal
FROM Vendors join Invoices ON Vendors.VendorID = Invoices.VendorID
GROUP BY Vendorname, InvoiceNumber, InvoiceDate, Invoicetotal
HAVING COUNT(InvoiceNumber)=1
ORDER BY Vendorname

PBUH

Sachin.Nand

2937 Posts

Posted - 2010-10-21 : 02:02:11

quote:
Originally posted by GilaMonster

Homework?

--
Gail Shaw
SQL Server MVP

Atleast he is giving a try.

Not like other's who just post their homework questions putting the onus on us to solve it.

PBUH

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-10-21 : 04:14:20

True, I just want him to say whether it is or not. If it is, I'll give suggestions and hints, not answers.

--
Gail Shaw
SQL Server MVP

Obsivus
Starting Member

17 Posts

Posted - 2010-10-21 : 07:11:33

quote:
Originally posted by GilaMonster

Homework?

--
Gail Shaw
SQL Server MVP

no, it's exercises from an old exam i am looking in to

Sachin.Nand

2937 Posts

Posted - 2010-10-21 : 07:37:10

The second question :

Hint :Try Exists clause or a Left Join

The third Question :

Hint :Try Having Count Clause

The last Question :

Hint :Try Max Clause

PBUH

Obsivus
Starting Member

17 Posts

Posted - 2010-10-21 : 07:58:09

quote:
Originally posted by Sachin.Nand

The second question :

Hint :Try Exists clause or a Left Join

The third Question :

Hint :Try Having Count Clause

The last Question :

Hint :Try Max Clause

PBUH

Awesome thanks for the great hints, I will be doing the exercises when I get home..

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-10-21 : 09:21:27

quote:
Originally posted by Sachin.Nand

The last Question :

Hint :Try Max Clause

Min, since it's the earliest date.

Re question 2, the q says to use full join, but that's a silly thing to use.

--
Gail Shaw
SQL Server MVP

Obsivus
Starting Member

17 Posts

Posted - 2010-10-21 : 10:31:17

quote:
Originally posted by Sachin.Nand

quote:

First question is that I need to type a select statement that returns the 4 colums below for all the Vendors that only have one Invoice from the invoice table.

Vendorname
InvoiceNumber
InvoiceDate
InvoiceTotal

I have been reading and trying for like 6 hours today and I just cant figure it out.

You are so near.Try this

SELECT Vendorname, InvoiceNumber, InvoiceDate, Invoicetotal
FROM Vendors join Invoices ON Vendors.VendorID = Invoices.VendorID
GROUP BY Vendorname, InvoiceNumber, InvoiceDate, Invoicetotal
HAVING COUNT(InvoiceNumber)=1
ORDER BY Vendorname

PBUH

I tried that Select Statement and it gave me 114 row, the result was vendors that have one and more then one invoices.

I tried

SELECT VendorID, COUNT(*) as InvoiceQTY
FROM Invoices
GROUP by VendorID
HAVING COUNT(vendorid)=1

By this select statement I did get 22 rows and all vendors that only have 1 invoice which is perfect, but the problem is that the exercise wants me to return those 4 specific columns and when I try to add them with a JOIN it just doesnt work =(

Sachin.Nand

2937 Posts

Posted - 2010-10-21 : 11:47:58

try this



SELECT Vendorname, max(InvoiceNumber), max(InvoiceDate), sum(Invoicetotal)
FROM Vendors join Invoices ON Vendors.VendorID = Invoices.VendorID
GROUP BY Vendorname
HAVING COUNT(InvoiceNumber)=1
ORDER BY Vendorname

PBUH

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-10-21 : 13:24:40

SELECT <desired column list>
  FROM Vendors 
    INNER JOIN 
      (SELECT VendorID
       FROM Invoices
       GROUP by VendorID
       HAVING COUNT(vendorid)=1
      ) sub ON Vendors.VendorID = sub.VendorID
  ORDER BY VendorName

--
Gail Shaw
SQL Server MVP

Obsivus
Starting Member

17 Posts

Posted - 2010-10-21 : 16:17:50

Great both worked fine thanks :)

Im doing this one now:

a Select statement that gives result on VendorID and Vendorname that doesnt have Invoice, and FULL JOIN need to be used on this one.

I mean how am I suppose to find vendors that doesnt have any invoice? I tried to run:

SELECT Vendorname, InvoiceNumber, InvoiceDate, Invoicetotal
FROM Vendors FULL JOIN Invoices (ON Vendors.VendorID = Invoices.VendorID)
ORDER BY Vendorname

But I dont see any NULL values in the result set :S Im so confused

I tried this statement

SELECT Vendors.VendorID, VendorName
FROM Vendors FULL JOIN Invoices
ON Vendors.VendorsID = Invoices.VendorID
WHERE Invoices.VendorsID IS NULL

Which gave me 88 Rows as result.

When I change this line "WHERE Invoices.VendorsID IS NULL" to IS NOT NULL I get 114 Rows. Does it mean that this SQL statement is the right answer?

Obsivus
Starting Member

17 Posts

Posted - 2010-10-21 : 17:12:27

I think I accomplished the LAST exercise wooohoo! Could someone judge it if its right or wrong?

SELECT VendorName,MAX(Invoicenumber) AS Invoicenumber,MAX(invoicetotal) AS Invoicetotal,MIN(invoicedata) AS Earlydate
FROM Vendors JOIN Invoices
ON Vendors.VendorID = Invoices.VendorID
GROUP BY VendorName
ORDER BY EarlyDate DESC

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-10-21 : 18:17:04

quote:
Originally posted by Obsivus

Great both worked fine thanks :)

Great. Do you understand why?

quote:
a Select statement that gives result on VendorID and Vendorname that doesnt have Invoice, and FULL JOIN need to be used on this one.

To be quite honest, a full outer join is a silly thing to use there. Typically one would use Left join, not in or not exists. If I ever saw someone using full outer join for that I'd seriously question if they knew what they were doing.

These two may be of interest for this:
http://sqlinthewild.co.za/index.php/2010/02/18/not-exists-vs-not-in/
http://sqlinthewild.co.za/index.php/2010/03/23/left-outer-join-vs-not-exists/

--
Gail Shaw
SQL Server MVP

Obsivus
Starting Member

17 Posts

Posted - 2010-10-21 : 20:14:00

quote:
Originally posted by GilaMonster

quote:
Originally posted by Obsivus

Great both worked fine thanks :)

Great. Do you understand why?

quote:
a Select statement that gives result on VendorID and Vendorname that doesnt have Invoice, and FULL JOIN need to be used on this one.

To be quite honest, a full outer join is a silly thing to use there. Typically one would use Left join, not in or not exists. If I ever saw someone using full outer join for that I'd seriously question if they knew what they were doing.

These two may be of interest for this:
http://sqlinthewild.co.za/index.php/2010/02/18/not-exists-vs-not-in/
http://sqlinthewild.co.za/index.php/2010/03/23/left-outer-join-vs-not-exists/

--
Gail Shaw
SQL Server MVP

I see but im still confused since I have to use FULL JOIN on this exercise But I dont know how to get the right answer.

I did

SELECT Vendors.VendorID, VendorName
FROM Vendors FULL JOIN Invoices
ON Vendors.VendorID = Invoices.VendorID
WHERE NOT EXISTS (SELECT Invoices.VendorID
                  FROM Invoices
                  WHERE Invoices.VendorID = Vendors.VendorID)

I got 88 Rows as result same as the above :S

edit: My wrong 88 Rows as result is the right answer on that exercise

Sachin.Nand

2937 Posts

Posted - 2010-10-22 : 01:05:07

quote:

Originally posted by GilaMonster

Or

SELECT <desired column list>
  FROM Vendors 
    INNER JOIN 
      (SELECT VendorID
       FROM Invoices
       GROUP by VendorID
       HAVING COUNT(vendorid)=1
      ) sub ON Vendors.VendorID = sub.VendorID
  ORDER BY VendorName

--
Gail Shaw
SQL Server MVP

I dont think it will be necessary as it will increase unecessary overhead because any Vendor whose count(InvoiceNumber)=1 will always have only one Invoicenumber,Invoicetotal and Invoicedata.So any aggregate function on those column will do the job.No need for the JOIN.

PBUH

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-10-22 : 10:40:20

No need for what join?
There are exactly the same number of joins as your query, all I've done is move the aggregate into a subquery. The two queries should perform pretty much the same. I can test if you like.

--
Gail Shaw
SQL Server MVP

Obsivus
Starting Member

17 Posts

Posted - 2010-10-22 : 18:24:51

quote:
Originally posted by GilaMonster

No need for what join?
There are exactly the same number of joins as your query, all I've done is move the aggregate into a subquery. The two queries should perform pretty much the same. I can test if you like.

--
Gail Shaw
SQL Server MVP

its not possible to use columns from two tables with that statement as result :/ Invoice table and Vendors Table.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-10-23 : 05:40:49

It is possible, providing the columns you want from the Invoices table are specified in the subquery.

It's no different to the query that Sachin.Nand wrote, it's just in a different form. That's why I asked if you understood it. I'm guessing from your last post the answer to that is 'no'

--
Gail Shaw
SQL Server MVP

Obsivus
Starting Member

17 Posts

Posted - 2010-10-23 : 09:21:42

Well I tried to execute your SQL Statement and I get errors on the column list since Invoicenumber,Invoicetotal,Invoicedate are from Invoice table.

SELECT VendorName,InvoiceNumber,Invoicetotal,Invoicedate
FROM Vendors JOIN (SELECT Vendorid
                   FROM Invoices
                   GROUP BY VENDORID
                   HAVING COUNT (Vendorid)=1
                  ) Sub ON Vendors.Vendorid = Sub.Vendorid
ORDER BY Vendorid

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources