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 :
Visual presentation :
N.B. the column "Order No." has not been shown in the picture.
Practice Online
Query Visualization:
Duration:
Rows:
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.
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
- Weekly Trends
- Python Interview Questions and Answers: Comprehensive Guide
- Scala Exercises, Practice, Solution
- Kotlin Exercises practice with solution
- MongoDB Exercises, Practice, Solution
- SQL Exercises, Practice, Solution - JOINS
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join
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