SQL Homework–UNION, INTERSECT, EXCEPT

This homework will run from my training server, shaioshin.hopto.org.  If you do not currently have access to this server, please let me know and I’ll set up credentials so you can complete the following homework assignment.

In the AdventureWorks database on my server I have two tables: example.person and person.contact.  Take a look at the following two queries, and their results.

   1: SELECT  

   2:     fname, lname

   3: FROM example.person

   4:  

   5: SELECT    

   6:     firstName, LastName

   7: FROM person.contact

Based on the first and last name columns of both tables, write and execute the queries that would give me the results I’m asking for.

  1. Give me a list of all the first and last names in both tables.  If the person exists in both tables I do not want to see the name duplicated.  If they are duplicated in each table separately, I don’t mind the duplication.  Order the list by last name, a to z.
  2. Repeat the last query, this time I do want to see duplicates.
  3. Show me the first and last names that are in both tables.
  4. Show me all the first and last names from person.contact that are not in example.person.
  5. Insert one record from person.contact that is not in example.person.  Do this without using a JOIN statement.

I’ll be posting the answers Wednesday.  If you want feedback on your answers, please email me your solutions before then!

If you have any problems with these questions, let me know!  I’m here to help.

Tags:

No comments yet.

Leave a Reply

Notify me of followup comments via e-mail. You can also subscribe without commenting.