﻿ SQL Query on Multiple Tables - Exercises, Practice, Solution - w3resource

# SQL Exercises, Practice, Solution - Query on Multiple Tables

## SQL [ 8 exercises with solution]

[An editor is available at the bottom of the page to write and execute the scripts.]

1. From the following tables, write a SQL query to find the salespersons and customers who live in same city. Return customer name, salesperson name and salesperson city.  Go to the editor

Sample table: salesman

Sample table: customer

Sample Output:

```cust_name	name		city
Nick Rimando	James Hoog	New York
Brad Davis	James Hoog	New York
Julian Green	Pit Alex	London
Fabian Johnson	Mc Lyon		Paris
Fabian Johnson	Nail Knite	Paris
```

Click me to see the solution with pictorial presentation

2. From the following tables, write a SQL query to find all the customers along with the salesperson who works for them. Return customer name, and salesperson name.   Go to the editor

Sample table: customer

Sample table: salesman

Sample Output:

```cust_name	name
Nick Rimando	James Hoog
Graham Zusi	Nail Knite
Julian Green	Nail Knite
Fabian Johnson	Mc Lyon
Geoff Cameron	Lauson Hen
```

Click me to see the solution with pictorial presentation

3. From the following tables, write a SQL query to find those sales people who generated orders for their customers but not located in the same city. Return ord_no, cust_name, customer_id (orders table), salesman_id (orders table).   Go to the editor

Sample table: salesman

Sample table: customer

Sample table: orders

Sample Output:

```ord_no	cust_name	customer_id	salesman_id
70004	Geoff Cameron	3009		5003
70003	Geoff Cameron	3009		5003
70011	Jozy Altidor	3003		5007
70001	Graham Zusi	3005		5002
70007	Graham Zusi	3005		5002
70012	Julian Green	3008		5002
```

Click me to see the solution with pictorial presentation

4. From the following tables, write a SQL query to find those orders made by customers. Return order number, customer name.   Go to the editor

Sample table: orders

Sample table: customer

Sample Output:

```ord_no	cust_name
70002	Nick Rimando
70004	Geoff Cameron
70008	Nick Rimando
70010	Fabian Johnson
70003	Geoff Cameron
70011	Jozy Altidor
70013	Nick Rimando
70001	Graham Zusi
70007	Graham Zusi
70012	Julian Green
```

Click me to see the solution with pictorial presentation

5. From the following tables, write a SQL query to find those customers where each customer has a grade and served by at least a salesperson who belongs to a city. Return cust_name as "Customer", grade as "Grade" and order_no as "Order No.".    Go to the editor

Sample table: salesman

Sample table: customer

Sample table: orders

Sample Output:

```Customer      |Grade|Order No|
--------------|-----|--------|
Nick Rimando  |  100|   70002|
Geoff Cameron |  100|   70004|
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|
```

Click me to see the solution with pictorial presentation

6. 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".  Go to the editor

Sample table: salesman

Sample table: customer

Sample Output:

```Customer	City		Salesman	commission
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
```

Click me to see the solution with pictorial presentation

7. From the following tables, write a SQL query to find those orders executed by the salesperson, ordered by the customer whose grade is greater than or equal to 200. Compute purch_amt*commission as "Commission". Return customer name, commission as "Commission%" and Commission.  Go to the editor

Sample table: salesman

Sample table: customer

Sample table: orders

Sample Output:

```ord_no	cust_name	Commission%	Commission
70010	Fabian Johnson	0.14		277.6802
70011	Jozy Altidor	0.13		9.7877
70001	Graham Zusi	0.13		19.5650
70007	Graham Zusi	0.13		123.3050
70012	Julian Green	0.13		32.5585
```

Click me to see the solution with pictorial presentation

8.From the following table, write a SQL query to find those customers who made orders on October 5, 2012. Return customer_id, cust_name, city, grade, salesman_id, ord_no, purch_amt, ord_date, customer_id and salesman_id.  Go to the editor

Sample table: salesman

Sample table: customer

Sample table: orders

Sample Output:

```customer_id	cust_name	city		grade	salesman_id	ord_no	purch_amt	ord_date	customer_id	salesman_id
3002		Nick Rimando	New York	100	5001		70002	65.26		2012-10-05	3002		5001
3005		Graham Zusi	California	200	5002		70001	150.50		2012-10-05	3005		5002
```

Click me to see the solution with pictorial presentation

## Practice Online

More to Come !

Query visualizations are generated using Postgres Explain Visualizer (pev).

Do not submit any solution of the above exercises at here, if you want to contribute go to the appropriate exercise page.

﻿

## SQL: Tips of the Day

How to restore a dump file from mysqldump?

It should be as simple as running this:

```mysql -u <user> -p < db_backup.dump
```

If the dump is of a single database you may have to add a line at the top of the file:

```USE <database-name-here>;
```

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