x

Need some help Please

I have two questions left for an assignment and I can not figure them out.

Here they are:

List the territory_id and the territory_name of all territories where end tables have been sold.

List the total sales for each sales territory by salesperson.

Any advise would be greatly appreciated.

Thanks!

I dont even know how to start these...I have completed the rest of my assignment...The problem with the first one is there is not a territory_id or territory_name under the order table so Im supposed to use a bunch of different tables...
customer_t
order_line_t
Order_t
product_t
territory_t
does_business_in_t

I believe I am supposed to look at the order_line_t table then see if there is any end tables sold...but my problem comes when There is no territory_id in order_line_t or Order_t...Its only in does_business_in_t or territory_t.

more ▼

asked Apr 22, 2010 at 12:08 AM in Default

Dan 1 gravatar image

Dan 1
11 1 1 1

Homework? And my advice would be provide details about table names, etc.
Apr 22, 2010 at 12:17 AM Rob Farley
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

Your question is asking you to learn about using JOINS in order to connect data in different tables in order to satisfy business related questions.

for example:
you might have a table of authors that contains all the information about those authors and you have another table that contains all the information about publishers and another that has information about books.

Every author has an ID in their row in the table, this is their unique identifier, it is special to them and only them. This is a Primary Key. Also in their row is a column called publisher_ref, this is the ID of their publisher. we dont store all the information about the publisher in the Authors table, we have the publishers table for that. Lots of authors will share the same publisher so the publisher_ref will be seen many times in the authors table. This is a foreign key - it is a link to the primary key of another table

Every publisher has an ID in their row in the table, this is their unique identifier, it is special to them and only them. This is the primary key of the publishers table and is in a relationship with the authors table via the relationship of the publishers primary key to the authors foreign key.

We can get information about an author by writing a script that selects information from the authors table:

SELECT a.fname, a.sname, a.dateofbirth, a.state FROM AUTHORS as a

If we want to gather information about authors and publisher we have to JOIN the two tables:

SELECT a.fname, a.sname, a.dateofbirth, a.state, p.publishername  
FROM AUTHORS as a
INNER JOIN publishers as p ON a.publisher_ref = p.publisherID

It is wholly possible to build a string of JOINS to connect information from lots of tables, simply by identifying the relationships and repeating the INNER JOIN line so if there was a titles table and a vendors table we could get information about the books an author has written, who published them, what they were called and where they were sold with a query like:

SELECT a.fname, a.sname, p.publishername, b.title, v.outletname
FROM AUTHORS as a
INNER JOIN publishers as p ON a.publisher_ref = p.publisherID
INNER JOIN titles as t ON p.publisherID = t.publisher_ref
INNER JOIN vendors as v on t.titleID = v.title_ref

Hope this and the other answers have helped to explain the way you need to answer your assignment.

more ▼

answered Apr 22, 2010 at 05:23 AM

Fatherjack gravatar image

Fatherjack ♦♦
42.4k 75 78 108

Well done and +1 (wish I coud give it +2)... an education without actually doing the assignment.
May 14, 2010 at 09:29 PM Jeff Moden
(comments are locked)
10|1200 characters needed characters left
select t.territory_id, t.territory_name
from dbo.territories as t
where exists (select * 
              from dbo.Sales s 
              where s.product_id = 17 --'or whichever one you're looking for, better would be @product_id
              and s.territory_id = t.territory_id
             );

and

select territory_id, salesperson_id, count(*) as Numsales
from dbo.Sales
group by territory_id, salesperson_id;

You may like to join to other tables to get more info:

with groupeddata as ( select territory_id, salesperson_id, count(*) as Numsales from dbo.Sales group by territory_id, salesperson_id ) select g.NumSales, t.territory_name, s.salesperson_name from groupeddata g join dbo.territories t on t.territory_id = g.territory_id join dbo.salespeople s on s.salesperson_id = g.salesperson_id; 
more ▼

answered Apr 22, 2010 at 12:17 AM

Rob Farley gravatar image

Rob Farley
5.7k 15 18 20

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x985
x369
x116
x74

asked: Apr 22, 2010 at 12:08 AM

Seen: 1085 times

Last Updated: Apr 22, 2010 at 04:26 AM