Skip to content

SQL Homework—UNION, INTERSECT, EXCEPT—Solutions

2011 March 23
tags:
by Shannon Lowder

Here are the solutions to the homework problems I sent out.  Compare my queries with yours.  If they’re different, and you’d like some explanation on why they’re different.  Let me know.  I’ll be happy to explain any differences with you.

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.

   1: SELECT  

   2:     fname, lname

   3: FROM example.person

   4: UNION

   5: SELECT    

   6:     firstName, LastName

   7: FROM person.contact

2. Repeat the last query, this time I do want to see duplicates.

   1: SELECT  

   2:     fname, lname

   3: FROM example.person

   4: UNION ALL

   5: SELECT    

   6:     firstName, LastName

   7: FROM person.contact

3. Show me the first and last names that are in both tables.

   1:  

   2: SELECT  

   3:     fname, lname

   4: FROM example.person

   5: INTERSECT

   6: SELECT    

   7:     firstName, LastName

   8: FROM person.contact

4. Show me all the first and last names from person.contact that are not in example.person.

   1:  

   2: SELECT    

   3:     firstName, LastName

   4: FROM person.contact

   5: EXCEPT

   6: SELECT  

   7:     fname, lname

   8: FROM example.person

5.Insert one record from person.contact that is not in example.person.  Do this without using a JOIN statement.

   1: INSERT INTO example.person

   2: (fname, lname)

   3: SELECT TOP 1 FirstName, LastName

   4: FROM (

   5:     SELECT     

   6:         firstName, LastName

   7:     FROM person.contact

   8:     EXCEPT

   9:     SELECT  

  10:         fname, lname

  11:     FROM example.person) x

  12:     

Again, if you have any questions, please let me know!

No comments yet

Leave a Reply

Note: You can use basic XHTML in your comments. Your email address will never be published.

Subscribe to this comment feed via RSS