Practice Center – inner join

Practice SQL inner joins online against a real database. Read the summary of each exercise carefully, enter the corresponding SQL query in the text box underneath and click on the submit button. When you click on submit, you will get an immediate feedback whether your query is right or wrong.

Exercise 1

Display in one query:
  • the client id (column client_id) and the phone number (column phone_number) of clients (table clients) that have a salesperson associated (table salespersons)
  • the first name and last name of the salesperson associated (salesperson_id from clients corresponds to id from salesperson)

In this query, do not display the salespersons without any clients



Submit

Exercise 2

Display in one query:
  • the client id (column client_id) and the phone number (column phone_number) of clients (table clients) that have no associated salesperson (salesperson_id=0) but a salesperson (table salespersons) in the same state (if there is more than one salesperson in the same state, put display more than one line per client)
  • the first name and last name of the salesperson in the same state


Submit

Exercise 3

Display in one query:
  • the client id (column client_id) of clients (table clients) that have no associated salesperson (salesperson_id=0, table salespersons) but a salesperson in the same state that has an amount of sales (column total_sales) strictly lower than 33000
  • the first name, last name, state and total sales of the salesperson associated


Submit

Exercise 4

Same as above, but be careful:
  • first_name of table salespersons and last_name of table salespersons appear as first_name_salesperson and last_name_salesperson in the result header
  • client_id appears as client_id and state as state


Submit

Exercise 5

Same as above, but your query must contain “from salespersons” instead of “from clients” and return the same result.

Submit

To practice SQL outer join: go to the SQL outer join practice.
For more exercises using a real database: full list of SQL practice exercise.
If you like the challenge: try to practice group by.

 
Share on:

Something not clear? Ask your question here:

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

 
 

8 questions on “Practice Center – inner join

  1. Hi,

    Great website.
    I have question in regards to Joints. Why full joint does not work ?
    inner/left and right are fine….

    You have an error in your SQL syntax near ‘full outer join salespersons as two on cl.salesperson_id=two.id’ at line 10

    select

    cl.first_name as client_name,
    cl.last_name as client_surname,
    cl.email_address as client_email,
    two.first_name as seller_name

    from clients as cl

    full outer join
    salespersons as two

    on
    cl.salesperson_id=two.id

    • Hi Sebastian,

      I am happy you enjoyed this site. The practice center is connecting to a mySQL database. As mySQL is not supporting full outer joins, the practice center returned a syntax error.
      An alternative to full outer join in that case is to combine right and left outer join using the keyword union:

      select cl.first_name as clientfirstname,
      cl.last_name as clientlastname,
      sa.first_name as salesfirstname,
      sa.last_name as saleslastname
      from clients as cl
      right outer join salespersons as sa
      on cl.salesperson_id = sa.id
      union
      select cl.first_name as clientfirstname,
      cl.last_name as clientlastname,
      sa.first_name as salesfirstname,
      sa.last_name as saleslastname
      from clients as cl
      left outer join salespersons as sa
      on cl.salesperson_id = sa.id

      Union is concatenating the result of the first query with the result of the second query and eliminates duplicates (union all does the same but does not eliminate duplicates).

      Note that full outer joins are supported on other databases like SQL server. This article shows how to download a free version to train on your computer:
      http://www.graspsql.com/blog/sql-server-installation-free-version/

      Hope it helps.

      Kind regards
      Vincent

  2. Exercise 3
    Display in one query:

    the client id (column client_id) of clients (table clients) that have no associated salesperson (salesperson_id=0, table salespersons) but a salesperson in the same state that has an amount of sales (column total_sales) strictly lower than 33000
    the first name, last name and total sales of the salesperson associated

    y dont we have salesperson_id=0 check in where like we had in exercise 2

    according to me there should have being 2 conditions in where salespersonid= 0 and total_sales < 33000

    if not can you please make me understand

    • Hi Sweta,

      You are right, we should add the condition salesperson_id = 0. I corrected the expected results. If you refresh your page you will have the correct expected result when clicking on “show solution”.

      Thank you and congratulations :)

      Vincent

  3. Exercise 3 says to display the salesperson’s total sales, but doing so is not part of the correct solution. Additionally, Exercise 2 and 3 require the user to display the state column, but the instructions to mention them.

    The directions are misleading.


Author:
Last udpated: September 15, 2017
Created: December 21, 2014