w3resource

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:

FieldTypeNullKeyDefaultExtra
salesperson_idint(11)YES
first_namevarchar(255)YES
last_namevarchar(255)YES

Data:

salesperson_idfirst_namelast_name
1GreenWright
2Jones Collins
3BryantDavis

Table : address

Sturucture:

FieldTypeNullKeyDefaultExtra
address_idint(11)YES
salesperson_idint(11)YES
cityvarchar(255)YES
statevarchar(255)YES
countryvarchar(255)YES

Data:

address_idsalesperson_idcitystatecountry
12Los AngelesCaliforniaUSA
23 DenverColoradoUSA
34AtlantaGeorgiaUSA

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

How to select the nth row in a SQL database table?

Basically, PostgreSQL and MySQL supports the non-standard:

SELECT...
LIMIT y OFFSET x 

Oracle, DB2 and MSSQL supports the standard windowing functions:

SELECT * FROM (
  SELECT
    ROW_NUMBER() OVER (ORDER BY key ASC) AS rownumber,
    columns
  FROM tablename
) AS foo
WHERE rownumber <= n

Database: SQL

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