04

Same tables as 03.

Queries¶

1. Write a query to find salesmen with all information who lives in the city where any of the customers lives.

select salesman.name as Salesperson
from salesman
where salesman_id is not null
 and salesman.name is not null
    and salesman.city is not null
    and salesman.commission is not null
 and salesman.city i some(
     select customer.city from customer
  );

1. Write a query to display all the orders that had amounts that were greater than at least one of the orders on September 10^th 2012.

select *
from orders
where orders.purch_amt > some(
  select purch_amt
  from orders
  where ord_date = 2012-09-10
);

1. Write a query to display all orders with an amount smaller than any amount for a customer in London.

select *
from orders
where orders.purch_amt < some (
  select purch_amt
  from orders
  where orders.customer_id in (
     select customer.customer_id
    from customer
    where city = "London"
  )
);

1. Write a query to display only those customers whose grade are, in fact, higher than every customer in New York.

select cust_name as Customer
from customer
where grade = (
  select max(grade)
  from customer
  where city = "New York"
);

1. Write a query to get all the information for those customers whose grade is not as the grade of customer who belongs to the city London.

select *
from customer
where grade not in (
  select grade
  from customer
  where city = "London"
);

1. Write a query to display all the orders from the orders table issued by the salesman 'Paul Adam'.

select *
from orders
where salesman_id = (
 select salesman_id
  from salesman
  where name = "Paul Adam"
);

1. Write a query to display all the orders for the salesman who belongs to the city London.

select *
from orders
where salesman_id = (
 select salesman_id
  from salesman
  where city = "London"
);

1. Write a query to display all the orders which values are greater than the average order value for 10^th October 2012.

select *
from orders
where purch_amt > (
 select avg(purch_amt)
  from orders
  where ord_date = "2012-10-10"
);

1. Write a query to display the commission of all the salesmen servicing customers in Paris.

select distinct commission
from salesman
where city = "Paris";

1. Write a query to display all customers with orders on October 5, 2012.

   select cust_name as Customer
   from customer
   where customer.customer_id in (
       select orders.customer_id
     from orders
     where ord_date = "2012-10-05"
   );
   

Comments