More Queries

Find Region name which has most customers.

Ans: select region from (select count(customerid) "count1",region from customers where region is not null
group by region order by "count1" desc) where rownum=1;

Find the best sales month in northwind?

Ans: select "BestMonth" from (select count(orderid) "totalvalue",to_char(orderdate,'month') "BestMonth"
from orders group by to_char(orderdate,'month')order by "totalvalue" desc) where rownum=1;

Create customer report(customer-id - customer name-address-total order value)

Ans: select c.customerid,c.companyname,c.address,sum( orderdetails.unitprice * quantity) as totalvalue from customers c join orders on c.customerid=orders.customerid
join orderdetails on orders.orderid=orderdetails.orderid
group by c.customerid,c.companyname,c.address

Employees list with(total order values) from high to low.

Ans: select e.firstname,sum(quantity* unitprice) "total" from employees e,orders,orderdetails
where orders.employeeid=e.employeeid and orderdetails.orderid =orders.orderid group by firstname order by "total" desc