SQL Challenges-1: Combine two tables
SQL Challenges-1: Exercise-1 with Solution
From the following tables, write a SQL query to find the information on each salesperson of ABC Company. Return name, city, country and state of each salesperson.
Input:
Table: salespersons
Structure:
Field | Type | Null | Key | Default | Extra |
---|---|---|---|---|---|
salesperson_id | int(11) | YES | |||
first_name | varchar(255) | YES | |||
last_name | varchar(255) | YES |
Data:
salesperson_id | first_name | last_name |
---|---|---|
1 | Green | Wright |
2 | Jones | Collins |
3 | Bryant | Davis |
Table : address
Sturucture:
Field | Type | Null | Key | Default | Extra |
---|---|---|---|---|---|
address_id | int(11) | YES | |||
salesperson_id | int(11) | YES | |||
city | varchar(255) | YES | |||
state | varchar(255) | YES | |||
country | varchar(255) | YES |
Data:
address_id | salesperson_id | city | state | country |
---|---|---|---|---|
1 | 2 | Los Angeles | California | USA |
2 | 3 | Denver | Colorado | USA |
3 | 4 | Atlanta | Georgia | USA |
Sample Solution:
SQL Code(MySQL:
CREATE TABLE IF NOT EXISTS salespersons(salesperson_id int, first_name varchar(255), last_name varchar(255));
CREATE TABLE IF NOT EXISTS address (address_id int, salesperson_id int, city varchar(255), state varchar(255), country varchar(255));
TRUNCATE TABLE address;
INSERT INTO salespersons (salesperson_id, first_name, last_name) VALUES ('1', 'Green', 'Wright');
INSERT INTO salespersons (salesperson_id, first_name, last_name) VALUES ('2', 'Jones', 'Collins');
INSERT INTO salespersons (salesperson_id, first_name, last_name) VALUES ('3', 'Bryant', 'Davis');
TRUNCATE TABLE address;
INSERT INTO address (address_id, salesperson_id, city, state, country) VALUES ('1', '2', 'Los Angeles','California', 'USA');
INSERT INTO address (address_id, salesperson_id, city, state, country) VALUES ('2', '3', 'Denver', 'Colorado','USA');
INSERT INTO address (address_id, salesperson_id, city, state, country) VALUES ('3', '4', 'Atlanta', 'Georgia','USA');
select * from address;
select * from salespersons;
SELECT first_name, last_name, city, state
FROM salespersons LEFT JOIN address
ON salespersons.salesperson_id = address.salesperson_id;
Sample Output:
first_name|last_name|city |state | ----------|---------|-----------|----------| Jones |Collins |Los Angeles|California| Bryant |Davis |Denver |Colorado | Green |Wright | | |
SQL Code Editor:
Contribute your code and comments through Disqus.
Previous: SQL Challenges-1, Exercises Home.
Next: Third Highest Sale.
SQL: Tips of the Day
MySQL export schema without data
mysqldump -h yourhostnameorIP -u root -p --no-data dbname > schema.sql
Ref: https://bit.ly/3xzB9dS
- Weekly Trends
- 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
- JavaScript functions Exercises
- Python Tutorial
- Python Array Exercises
- SQL Cross Join
- C# Sharp Array Exercises
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