SQL JOINS
Practice Questions
1. From the following tables write a SQL query to nd
the salesperson and customer who reside in the
same city. Return Salesman, cust_name and city.
fi
1. From the following tables write a SQL query to nd
the salesperson and customer who reside in the
same city. Return Salesman, cust_name and city.
SELECT salesman.name AS "Salesman", customer.cust_name,
customer.city
FROM salesman, customer
WHERE salesman.city = customer.city;
Salesman cust_name city
James Hoog Nick Rimando New York
James Hoog Brad Davis New York
Pit Alex Julian Green London
Mc Lyon Fabian Johnson Paris
Nail Knite Fabian Johnson Paris
Pit Alex Brad Guzan London
fi
2. From the following tables write a SQL query to nd
those orders where the order amount exists between 500
and 2000. Return ord_no, purch_amt, cust_name, city.
fi
2. From the following tables write a SQL query to nd
those orders where the order amount exists between 500
and 2000. Return ord_no, purch_amt, cust_name, city.
SELECT a.ord_no, a.purch_amt,
b.cust_name, b.city
FROM orders a, customer b
WHERE a.customer_id = b.customer_id
AND a.purch_amt BETWEEN 500 AND 2000;
ord_no purch_amt cust_name city
70007 948.50 Graham Zusi California
70010 1983.43 Fabian Johnson Paris
fi
3. From the following tables write a SQL query to nd
the salesperson(s) and the customer(s) he represents.
Return Customer Name, city, Salesman, commission.
fi
3. From the following tables write a SQL query to nd
the salesperson(s) and the customer(s) he represents.
Return Customer Name, city, Salesman, commission.
SELECT a.cust_name AS "Customer Name",
a.city,
b.name AS "Salesman",
b.commission
FROM customer a
INNER JOIN salesman b
ON a.salesman_id = b.salesman_id;
Customer Name city Salesman commission
Nick Rimando New York James Hoog 0.15
Brad Davis New York James Hoog 0.15
Graham Zusi California Nail Knite 0.13
Julian Green London Nail Knite 0.13
Fabian Johnson Paris Mc Lyon 0.14
Geoff Cameron Berlin Lauson Hen 0.12
Jozy Altidor Moscow Paul Adam 0.13
Brad Guzan London Pit Alex 0.11
fi
4. From the following tables write a SQL query to nd
salespeople who received commissions of more than 12
percent from the company. Return Customer Name,
customer city, Salesman, commission.
fi
4. From the following tables write a SQL query to nd
salespeople who received commissions of more than 12
percent from the company. Return Customer Name,
customer city, Salesman, commission.
SELECT a.cust_name AS "Customer Name",
a.city,
b.name AS "Salesman",
b.commission
FROM customer a
INNER JOIN salesman b
ON a.salesman_id = b.salesman_id
WHERE b.commission > 0.12;
fi
5. From the following tables write a SQL query to locate those
salespeople who do not live in the same city where their customers live
and have received a commission of more than 12% from the company.
Return Customer Name, customer city, Salesman, salesman city,
commission.
5. From the following tables write a SQL query to locate those
salespeople who do not live in the same city where their customers live
and have received a commission of more than 12% from the company.
Return Customer Name, customer city, Salesman, salesman city,
commission.
SELECT a.cust_name AS "Customer Name",
a.city,
b.name AS "Salesman",
b.city, b.commission
FROM customer a
INNER JOIN salesman b
ON a.salesman_id = b.salesman_id
WHERE b.commission > 0.12
AND a.city <> b.city;
6. From the following tables write a SQL query to display
the customer name, customer city, grade, salesman,
salesman city. The results should be sorted by ascending
customer_id.
6. From the following tables write a SQL query to display
the customer name, customer city, grade, salesman,
salesman city. The results should be sorted by ascending
customer_id.
SELECT a.cust_name, a.city, a.grade,
b.name AS "Salesman", b.city
FROM customer a
LEFT JOIN salesman b
ON a.salesman_id = b.salesman_id
ORDER BY a.customer_id;
7. From the following tables write a SQL query to nd
those customers with a grade less than 300. Return
cust_name, customer city, grade, Salesman, salesmancity.
The result should be ordered by ascending customer_id.
fi
7. From the following tables write a SQL query to nd
those customers with a grade less than 300. Return
cust_name, customer city, grade, Salesman, salesmancity.
The result should be ordered by ascending customer_id.
SELECT a.cust_name, a.city, a.grade,
b.name AS "Salesman", b.city
FROM customer a
LEFT OUTER JOIN salesman b
ON a.salesman_id = b.salesman_id
WHERE a.grade < 300
ORDER BY a.customer_id;
fi
8. Write 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 determine whether any of
the existing customers have placed an order or not.
8. Write 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 determine whether any of
the existing customers have placed an order or not.
SELECT a.cust_name, a.city, b.ord_no,
b.ord_date, b.purch_amt AS "Order Amount"
FROM customer a
LEFT OUTER JOIN orders b
ON a.customer_id = b.customer_id
ORDER BY b.ord_date;
9. Write a SQL statement to generate a list in ascending
order of salespersons who work either for one or more
customers or have not yet joined any of the customers.
9. Write a SQL statement to generate a list in ascending
order of salespersons who work either for one or more
customers or have not yet joined any of the customers.
SELECT a.cust_name, a.city, a.grade,
b.name AS "Salesman", b.city
FROM customer a
RIGHT OUTER JOIN salesman b
ON b.salesman_id = a.salesman_id
ORDER BY b.salesman_id;
10. Write a SQL query to combine each
row of the salesman table with each row
of the customer table.
10. Write a SQL query to combine each
row of the salesman table with each row
of the customer table.
SELECT *
FROM salesman a
CROSS JOIN customer b;
