Practice Center – Outer Join

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 salespersons)
  • the client id (column client_id) and the phone number (column phone_number) of clients (table clients) that have no salesperson associated

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


Exercise 2

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


Exercise 3

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
  • the first name and last name of the salesperson associated (salesperson_id from clients corresponds to id from salespersons)
  • the first name and last name of the salesperson that have no associated clients

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


Exercise 4

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
  • the first name, last name and total sales (column total_sales) of the salesperson associated (salesperson_id from clients corresponds to id from salespersons)
  • the client id (column client_id) and the phone number (column phone_number) of clients (table clients) that have no salesperson associated
  • the first name and last name of the salesperson that have no associated clients

Be careful to use the keyword “union” (you are using a mysql database)


Exercise 5

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 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
  • the first name and last name of the salesperson that have no clients in the same state
  • do not display the client_id and phone_number of clients having no salesperson in the same state



Exercise 6

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 in the same state (if there is more than one salesperson in the same state, put display more than one line per client) that has an amount of sales (column total_sales) strictly smaller than 13000
  • the first name, last name and total sales of the salesperson associated
  • the first name and last name of the salesperson that have no associated clients but fulfilling the condition about total sales

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


 
Share on:


Author:
Last udpated: March 1, 2016
Created: February 28, 2016