Help!! Trouble with database query!

[noisyassassin]

Hi guys, I am busy doing an database assignment for a hardware supplies company and i am having trouble with two queries. Could anyone help?

The queries are what i am trying to do -

a )
Write a query to list the customer name and the number of orders they have placed. Include any customers that have not placed orders. Sequence the list by the customer number.

b)
Write a query to list parts (identity number and first 20 characters of the description) and the total quantity ordered for each part and total value relating to those quantities. Include any parts that have not been ordered. Sequence the list in alphabetical order of description.


Thanks in advance!

[obsidian]

[!--quoteo(post=348780:date=Feb 23 2006, 03:52 PM:name=noisyassassin)--][div class=\'quotetop\']QUOTE(noisyassassin @ Feb 23 2006, 03:52 PM) [snapback]348780[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Hi guys, I am busy doing an database assignment for a hardware supplies company and i am having trouble with two queries. Could anyone help?

The queries are what i am trying to do -

a )
Write a query to list the customer name and the number of orders they have placed. Include any customers that have not placed orders. Sequence the list by the customer number.

b)
Write a query to list parts (identity number and first 20 characters of the description) and the total quantity ordered for each part and total value relating to those quantities. Include any parts that have not been ordered. Sequence the list in alphabetical order of description.
Thanks in advance!
[/quote]

welcome to the forums! i hope we can be of help to you here.

let's say you have 2 tables: 1) customers and 2) orders. you have one entry in orders for each order placed with an id that refers back to your customers table... well, you'd run your first query something like this:
[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] b.id, b.name, [color=blue]COUNT[/color](a.id) [color=green]AS[/color] count [color=green]FROM[/color] [color=orange]orders[/color] a [color=green]LEFT[/color] [color=green]JOIN[/color] customers b ON a.customer [color=orange]=[/color] b.id GROUP BY b.id, b.name [color=green]ORDER BY[/color] b.id
[!--sql2--][/div][!--sql3--]

the second one would be next to impossible for us to help you with without knowing at least your database structure. if you can give us some more details, i'm sure we'll be able to help you with your quandry.

hope this is of some help to you.

[noisyassassin]

Thanks obsidian, below i have included the data structure of the tables if that would allow you to help me with the question?

Also i am reasonably new to this, using the data included below would you be able to word the answer you have given me to the first question using the data included?

Thanks for the speedy response anyway :-)


create table customer
(
Cust_No number primary key,
name varchar2(30) not null,
address1 varchar2(40),
address2 varchar2(40),
area varchar2(40),
postcode varchar2(8),
phone varchar2(14),
credit_limit number default 100
) pctfree 0
storage (
initial 0K
next 2k
pctincrease 0);

create table part
(
part_number varchar2(13) primary key,
description varchar2(40),
UOM varchar2(10) default 'EACH' not null,
PricePerUnit number
) pctfree 0
storage (
initial 0K
next 2k
pctincrease 0);


create table SalesOrder
(
Order_No number primary key,
cust_no number not null references customer,
date_placed date default sysdate not null,
date_completed date
) pctfree 0
storage (
initial 0K
next 2k
pctincrease 0);

create table SalesOrderLine
(
order_no number,
line_no number,
part_no varchar2(13) not null references part,
Qty_ordered number default 1 not null,
Qty_shipped number default 0 not null,
date_shipped date,
primary key(order_no,line_no)
) pctfree 0
storage (
initial 0K
next 2k
pctincrease 0);

[wickning1]

For question 2 -
You want a list of all parts, and how many have been sold. SalesOrderLine is the table that lists parts and quantities, so you want to join the parts table with that one. Since you want all parts regardless of whether any have been sold, that means you want a LEFT JOIN.

You want to join parts with line items having the same part number, that goes after ON. So far we have:

[code]SELECT * FROM part p LEFT JOIN SalesOrderLine l ON l.part_no=p.part_number[/code]
Now, you want to have MySQL sum up the quantities and values for each part number, so you want to use GROUP BY and some group functions like SUM:

[code]SELECT p.*, SUM(l.Qty_Ordered) as cnt, SUM(l.Qty_Ordered * p.PricePerUnit) as valu FROM part p LEFT JOIN SalesOrderLine l ON l.part_no=p.part_number GROUP BY p.part_number[/code]
Finally, you are told to return 20 characters of the description and the part number and sort the list, so we'll clean up our return values a bit more and add the sort command:

[code]SELECT p.part_number, MID(p.description, 0, 20) as descr, SUM(l.Qty_Ordered) as cnt, SUM(l.Qty_Ordered * p.PricePerUnit) as valu FROM part p LEFT JOIN SalesOrderLine l ON l.part_no=p.part_number GROUP BY p.part_number ORDER BY descr[/code]
That should do it.

[noisyassassin]

Thanks both to

obsidian
and
wickning1,

for the help that explained well, and due to that i have now managed to get the oracle mysql queries working.