w3resource

SQL Exercises: Sort out the customer who made at least an order

SQL Query on Multiple Tables: Exercise-5 with Solution

From the following tables, write a SQL query to find those customers where each customer has a grade and is served by a salesperson who belongs to a city. Return cust_name as "Customer", grade as "Grade".

Sample table: salesman


Sample table: customer


Sample table: orders


Sample Solution:

SELECT customer.cust_name AS "Customer",
customer.grade AS "Grade",orders.ord_no AS "Order No."
FROM orders, salesman, customer
WHERE orders.customer_id = customer.customer_id
AND orders.salesman_id = salesman.salesman_id
AND salesman.city IS NOT NULL
AND customer.grade IS NOT NULL;

Output of the query:

Customer      |Grade|Order No|
--------------|-----|--------|
Nick Rimando  |  100|   70002|
Geoff Cameron |  100|   70004|
Brad Davis    |  200|   70005|
Nick Rimando  |  100|   70008|
Fabian Johnson|  300|   70010|
Geoff Cameron |  100|   70003|
Jozy Altidor  |  200|   70011|
Nick Rimando  |  100|   70013|
Graham Zusi   |  200|   70001|
Graham Zusi   |  200|   70007|
Julian Green  |  300|   70012|

Code Explanation:

The said query in SQL that joins the 'orders', 'salesman', and 'customer' tables. The result set includes the customer name (cust_name) with an alias "Customer", customer grade (grade) with an alias "Grade", and order number (ord_no) with an alias "Order No."
The WHERE clause specifies multiple conditions for the join. A join condition is specified by the first and second conditions between the orders table, and the customer and salesman table, respectively, which states that the customer_id and salesman_id columns of the orders table must match the columns of the customer table and the salesman table, respectively.
The third and fourth conditions specify that the city and grade columns of the salesman and customer tables must not be null, respectively.

Explanation :

Syntax of sort out the customer their grade and order no who made at least an order

Visual presentation :

Result of customer their grade and order no who made at least an order
N.B. the column "Order No." has not been shown in the picture.

Practice Online



Query Visualization:

Duration:

Query visualization of sort out the customer their grade and order no who made at least an order - Duration

Rows:

Query visualization of sort out the customer their grade and order no who made at least an order - Rows

Cost:

Query visualization of sort out the customer their grade and order no who made at least an order - Cost

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous SQL Exercise: Find out customers who made the order.
Next SQL Exercise: Customers served by a salesman and commission.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Follow us on Facebook and Twitter for latest update.

SQL: Tips of the Day

Grouped LIMIT in PostgreSQL: Show the first N rows for each group?

db=# SELECT * FROM xxx;
 id | section_id | name
----+------------+------
  1 |          1 | A
  2 |          1 | B
  3 |          1 | C
  4 |          1 | D
  5 |          2 | E
  6 |          2 | F
  7 |          3 | G
  8 |          2 | H
(8 rows)

I need the first 2 rows (ordered by name) for each section_id, i.e. a result similar to:

id | section_id | name
----+------------+------
  1 |          1 | A
  2 |          1 | B
  5 |          2 | E
  6 |          2 | F
  7 |          3 | G
(5 rows)

PostgreSQL v9.3 you can do a lateral join

select distinct t_outer.section_id, t_top.id, t_top.name from t t_outer
join lateral (
    select * from t t_inner
    where t_inner.section_id = t_outer.section_id
    order by t_inner.name
    limit 2
) t_top on true
order by t_outer.section_id;

Database: PostgreSQL

Ref: https://bit.ly/3AfYwZI

 





We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook