SQL Homework—UNION, INTERSECT, EXCEPT—Solutions

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!

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 *