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


   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.

By Shannon Lowder

Shannon Lowder is the Database Engineer you've been looking for! Look no further for expertise in: Business Analysis to gather the business requirements for the database; Database Architecting to design the logical design of the database; Database Development to actually build the objects needed by the business logic; finally, Database Administration to keep the database running in top form, and making sure there is a disaster recovery plan.

Leave a comment

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