question

Dan 1 avatar image
Dan 1 asked

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.

t-sqlqueryjoinshomework
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Rob Farley avatar image Rob Farley commented ·
Homework? And my advice would be provide details about table names, etc.
0 Likes 0 ·
Rob Farley avatar image
Rob Farley answered
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;
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image
Fatherjack answered

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.

1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Jeff Moden avatar image Jeff Moden commented ·
Well done and +1 (wish I coud give it +2)... an education without actually doing the assignment.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.