Please note, this is a STATIC archive of website www.w3resource.com from 19 Jul 2022, cach3.com does not collect or store any user information, there is no "phishing" involved.
w3resource

SQL Exercises: Sort out the customer's grade and order number for at least one 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|

Explanation :

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

Pictorial 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: From the following tables, write a SQL query to find those orders made by customers. Return order number, customer name.
Next: From the following table, write a SQL query to find those customers who served by a salesperson and the salesperson works at the commission in the range 12% to 14% (Begin and end values are included.). Return cust_name AS "Customer", city AS "City".

Test your Programming skills with w3resource's quiz.

What is the difficulty level of this exercise?



SQL: Tips of the Day

SQL Server SELECT into existing table. 

INSERT INTO dbo.TABLETWO
SELECT col1, col2
  FROM dbo.TABLEONE
 WHERE col3 LIKE @search_key

This assumes there's only two columns in dbo.TABLETWO - you need to specify the columns otherwise:

INSERT INTO dbo.TABLETWO
  (col1, col2)
SELECT col1, col2
  FROM dbo.TABLEONE
 WHERE col3 LIKE @search_key

Database: SQL Server

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