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 salespeople and customers who live in the 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 Brad Guzan Pit Alex London
Click me to see the solution with pictorial presentation
2. From the following tables, write a SQL query to locate all the customers and 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 Brad Davis James Hoog Graham Zusi Nail Knite Julian Green Nail Knite Fabian Johnson Mc Lyon Geoff Cameron Lauson Hen Jozy Altidor Paul Adam Brad Guzan Pit Alex
Click me to see the solution with pictorial presentation
3. From the following tables, write a SQL query to find those salespeople who generated orders for their customers but are 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 locate the 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 70009 Brad Guzan 70002 Nick Rimando 70004 Geoff Cameron 70005 Brad Davis 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 is served by a salesperson who belongs to a city. Return cust_name as "Customer", grade as "Grade". 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| 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|
Click me to see the solution with pictorial presentation
6. From the following table, write a SQL query to find those customers who are served by a salesperson and the salesperson earns commission in the range of 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 all orders executed by the salesperson and 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 70005 Brad Davis 0.15 360.0900 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 placed 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
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
- New Content published on w3resource:
- HTML-CSS Practical: Exercises, Practice, Solution
- Java Regular Expression: Exercises, Practice, Solution
- Scala Programming Exercises, Practice, Solution
- Python Itertools exercises
- Python Numpy exercises
- Python GeoPy Package exercises
- Python Pandas exercises
- Python nltk exercises
- Python BeautifulSoup exercises
- Form Template
- Composer - PHP Package Manager
- PHPUnit - PHP Testing
- Laravel - PHP Framework
- Angular - JavaScript Framework
- Vue - JavaScript Framework
- Jest - JavaScript Testing Framework