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!