Assignment on joins
1. From the following tables write a SQL query to find the
salesperson and customer who belongs to same city. Return
Salesman, cust_name and city
select [Link],[Link] "sales_city",[Link]
"customer_city",customer.customer_name from salesman
-> inner join
-> customer
-> on(salesman.salesman_id=customer.salesman_id);
[Link] the following tables write a SQL query to find those orders where order
amount exists between 500 and 2000. Return ord_no, purch_amt, cust_name,
city.
select
orders.order_no,orders.puch_amt,customer.customer_name,custo
[Link] from orders inner join
-> customer on(orders.customer_id=customer.customer_id)
-> where puch_amt between 500 and 2000;
[Link] the following tables write a SQL query to find the salesperson(s) and
the customer(s) he handle. Return Customer Name, city, Salesman,
commission.
select
customer.customer_name,[Link],[Link],salesman.c
ommission from customer
-> inner join
-> salesman on (customer.salesman_id=salesman.salesman_id);
[Link] the following tables write a SQL query to find those salespersons who
received a commission from the company more than 12%. Return Customer
Name, customer city, Salesman, commission.
select
customer.customer_name,[Link],[Link],[Link]
n from customer
-> inner join
-> salesman on (customer.salesman_id=salesman.salesman_id)
-> where commission>0.12;
[Link] the following tables write a SQL query to find those salespersons do
not live in the same city where their customers live and received a commission
from the company more than 12%. Return Customer Name, customer city,
Salesman, salesman city, commission.
select
customer.customer_name,[Link],[Link],[Link],salesm
[Link] from customer
-> inner join
-> salesman on(customer.salesman_id=salesman.salesman_id)
-> where [Link]<>[Link] and commission>0.12;
[Link] the following tables write a SQL query to find the details of an order.
Return ord_no, ord_date, purch_amt, Customer Name, grade, Salesman,
commission.
select
orders.order_no,orders.order_date,orders.puch_amt,customer.customer_nam
e,[Link],[Link],[Link] from orders
-> join
-> customer on(orders.customer_id=customer.customer_id)
-> join salesman on(orders.salesman_id=salesman.salesman_id);
[Link] a SQL statement to make a join on the tables salesman, customer and
orders in such a form that the same column of each table will appear once and
only the relational rows will come
SELECT *
-> FROM orders
-> NATURAL JOIN customer
-> NATURAL JOIN salesman;
[Link] the following tables write a SQL query to display the cust_name,
customer city, grade, Salesman, salesman city. The result should be ordered
by ascending on customer_id.
select
customer.customer_name,[Link],[Link],[Link],sale
[Link] from customer
-> inner join salesman on(customer.salesman_id=salesman.salesman_id)
order by customer_id;
[Link] the following tables write a SQL query to find those customers whose
grade less than 300. Return cust_name, customer city, grade, Salesman,
saleman city. The result should be ordered by ascending customer_id
select
customer.customer_name,[Link],[Link],salesman.n
ame,[Link] from customer
-> inner join salesman
on(customer.salesman_id=salesman.salesman_id)
-> where grade<300 order by customer_id;
[Link] a SQL statement to make a report with customer name, city, order
number, order date, and order amount in ascending order according to the
order date to find that either any of the existing customers have placed no
order or placed one or more orders.
select
customer.customer_name,[Link],orders.order_no,[Link]
er_date,orders.puch_amt from customer
-> left join orders on(orders.customer_id=customer.customer_id)
order by order_date;
