SQL masters - need help

[cirrrocco]

My nephew is going to a job interview, it is a motsly operations type of role with some SQL involved. There is a pre screening question . I was wondering if any of you can help

Assume a table named order .It has orderID and itemID
An order can have multiple items; order can have multiple items 1:many
The question is to find the number of orders in each item count.
The output of this
query would be something like 100 orders had 1 item, 70 orders had 2 items, 30
had 3 items, and
so on.

Is my answer correct
select orderid, count(itemID) from order group by (orderid);


2. number of orders whose total value is in the range of $0 -$10, $11-$20, > $21

Output should be

Range Count
0-10 1
11-20 8
Over 21 10

I don't have a clue how to help him with that. I am able to get sum or orders by using
select orderid, sum(amt) from order group by (orderid);

I get something like this
orderid sum(amt)
O123 800
O124 700
o432 22

How do I get the damm ranges in output


3. the third question is to get the total number of orders in 100increment

Output should be

Range Count
0-100 1
101-200 5
201-300 2

 

[nakedfrog]

Uh, I don't think it would be doing any favors to help him fake his way through the interview.

 

[cirrrocco]

hahah it is a business operations type role with heavy excel usage. But the company that prescreens them sent him this. He got all the excel and pivot table stuff done..haha