Answer to Chapter 7 of O'Reilly Learning Sql on SQL Server 2005

7.10. Review Questions
1. What are the major differences between the UNION operation and the JOIN operation?
In a UNION, all the rows in the resulting tables (sets) being unioned have to be compatible; in a JOIN, only the joining columns of the tables being joined have to be compatiblethe other columns may be different.

In a UNION, no "new" columns can be added to the final result of the UNION; in a JOIN, new columns can be added to the result of the JOIN.

In a UNION, the number of columns in the result set has to be the same as the number of columns in the sets being unioned; in a JOIN, the number of columns in the result set may vary.

2. What is the major difference between the UNION and the UNION ALL?
UNION will remove all duplicates items, but UNION ALL keeps all occurrences or records

3. What major set operator does SQL Server 2005 not have? How can these problems be resolved?
Although SQL Server 2005 does not have the MINUS (difference) operator, it does have an IN predicate and its negation, the NOT..IN, which enables us to create differences

4. What does union compatibility mean?
The data types of the columns of the two sets being unioned do not necessarily have to be exactly the same, meaning that they may differ in length and even type, but they have to be "well-matched." For union compatibility, the three basic data types are numeric, string, and dates. All numeric columns are compatible with one another, all string columns are compatible with one another, and all date columns are compatible with one another. For numbers, SQL will convert integers, floating-point numbers, and decimals into a numeric data type, to make them compatible with one another. So any numeric column (for example, integers) can be unioned with any other numeric column (for example, decimals). Likewise, any fixed-length character column and any variable-length character column will be converted to a character data type, and take on the larger size of the character columns being unioned. Similarly, date columns will be combined to a date data type.

5. What data types are union-compatible?
numeric, string, and dates

6. What is the maximum number of rows that can result from a UNION of two tablesone with 5 rows and the other with 6 rows?
11

7. What is the maximum number of rows that can result from a JOIN of two tablesone with 5 rows and the other with 6 rows?
30

8. How can a UNION be used to implement an outer join? Explain.
create a full outer join by writing a union of the left outer join and the right outer join, like this:

    SELECT with right outer join
    UNION
    SELECT with left outer join

9. Does SQL Server 2005 support the MINUS operation? How can this be resolved? Give examples.

NOT ... IN

10. What is a full outer join? Does SQL Server 2005 directly support a full outer join?
The FULL OUTER JOIN includes the rows that are equi-joined from both tables, plus the remaining rows from the first table and the remaining rows from the second table. Yes.

11. Do you need the same number of columns to perform a union?
Yes.

12. Do you need the same data types to perform a union?
No. But they should be compatible.

13. Do you need the same number of columns to perform a join?
No

14. From the examples given in the chapter, what does the UNION JOIN appear to do?

15. If a VARCHAR column were unioned with a CHAR column, what would the resulting column be? (Hint: refer to the "Data Type Precedence" section in Chapter 6.)
VARCHAR

16. What does set compatibility mean?
Union compatibility, the commonly used SQL terminology for set compatibility, means that when using set operations, the two sets (in this case, the results of two SELECTs) being unioned have to have the same number of similar columns and the columns have to have compatible data types.

17. What is the maximum number of rows that can result from a INTERSECT of two tablesone with 5 rows and the other with 6 rows?
5

18. Do you need the same number of columns to perform an INTERSECT operation?
Yes.

19. Do you need the same data types to perform an INTERSECT operation?
Not same, but must be compatible.

7.11. Exercises
Unless specified otherwise, use the Student_course database to answer the following questions. Also, use appropriate column headings when displaying your output.
1. In this exercise, you'll test the UNION statement. Having seen how the UNION statement works, demonstrate some permutations to see what will work "legally" and what won't. First, create two tables as follows:
Table 1 
A B
x1 y1
r1 s1

Table 2
A B C D
x2 y2 z2 w2
r2 s2 t2 u2

Make the type of As and Bs CHAR(2). Let the type of C in Table2 be VARCHAR(2) and D in Table2 be VARCHAR(3).

CREATE TABLE Table1
(
A CHAR(2),
B CHAR(2)
)

INSERT Table1 VALUES ('x1', 'y1');
INSERT Table1 VALUES ('r1', 's1');

CREATE TABLE Table2
(
A CHAR(2),
B CHAR(2),
C VARCHAR(2),
D VARCHAR(3)
)

INSERT Table2 VALUES ('x2', 'y2', 'z2', 'w2');
INSERT Table2 VALUES ('r2', 's2', 't2', 'u2');

Try the following statements and note the results:
    SELECT * FROM Table1 UNION SELECT * FROM Table2

Msg 205, Level 16, State 1, Line 1
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

    SELECT * FROM Table1 UNION SELECT A,B FROM Table2

    SELECT * FROM Table1 UNION SELECT B,A FROM Table1

    SELECT * FROM Table1 UNION SELECT A,C FROM Table2

    SELECT * FROM Table1 UNION SELECT A,D FROM Table2

    CREATE VIEW viewx AS
    SELECT A,B
    FROM Table2

    SELECT *
    FROM Table1
      UNION
    SELECT *
    FROM viewx

Feel free to experiment with any other combinations that you deem appropriate or that you wonder about.

2. Create and print the result of a query that generates the names, class, and course numbers of students who have earned Bs in computer science courses. Store this query as Q7_2. Then, revise Q7_2 to delete from the result set those students who are sophomores (class = 2). Use NOT..IN to select those students who are sophomores.

SELECT Student.STNO, student.SNAME, student.CLASS, Section.COURSE_NUM
  FROM  [Section] INNER JOIN [Grade_report]
 ON Section.SECTION_ID = Grade_report.SECTION_ID
 INNER JOIN [Student]
 ON Grade_report.STUDENT_NUMBER = Student.STNO
  WHERE GRADE = 'B' AND COURSE_NUM like 'COSC%'
 
SELECT Student.STNO, student.SNAME, student.CLASS, Section.COURSE_NUM
  FROM  [Section] INNER JOIN [Grade_report]
 ON Section.SECTION_ID = Grade_report.SECTION_ID
 INNER JOIN [Student]
 ON Grade_report.STUDENT_NUMBER = Student.STNO
  WHERE GRADE = 'B' AND COURSE_NUM like 'COSC%'
  AND CLASS NOT IN (1, 3, 4) 

 

3. Find the names, grades, and course numbers of students who have earned As in computer science or math courses. Join the Section and Grade_report tables (be careful to not create the Cartesian product). Then, UNION the set of "course numbers COSC% and A" with the set of "course number MATH% and A."
Hint: Start with the query to get names, grades, and course numbers for COSC% and A, and then turn this into a view. Do the same for MATH% and A, and then execute the UNION statement as follows (using your view names):
    SELECT *
    FROM view1a
      UNION
    SELECT *
    FROM view1b
   
Create VIEW view1a AS
SELECT student.SNAME, Section.COURSE_NUM, Grade_report.GRADE
  FROM  [Section] INNER JOIN [Grade_report]
 ON Section.SECTION_ID = Grade_report.SECTION_ID
 INNER JOIN [Student]
 ON Grade_report.STUDENT_NUMBER = Student.STNO
  WHERE GRADE = 'A' AND COURSE_NUM like 'COSC%'

Create VIEW view1b AS 
SELECT student.SNAME, Section.COURSE_NUM, Grade_report.GRADE
  FROM  [Section] INNER JOIN [Grade_report]
 ON Section.SECTION_ID = Grade_report.SECTION_ID
 INNER JOIN [Student]
 ON Grade_report.STUDENT_NUMBER = Student.STNO
  WHERE GRADE = 'A' AND COURSE_NUM like 'MATH%'
    
   

4. Find the names and majors of students who have made a C in any course. Make the "who have made a C in any course" a subquery for which you use IN.

SELECT *
FROM Student s
WHERE s.STNO IN (
 SELECT g.STUDENT_NUMBER
 FROM Grade_report g
 WHERE g.GRADE = 'C'
 )
 

5. A less-obvious example of a difference query is to find a difference that is not based on simple, easy-to-get sets. Suppose that set A is the set of student names who have made As and Bs in computer science (COSC) courses. Suppose further that set B is the set of students who have taken math courses (regardless of what grade they earned).
Then, set A minus set B would contain names of students who have made As or Bs in computer science courses, less those who have taken math courses. Similarly, set B minus set A would be the set of students who took math courses, less those who took COSC courses and made an A or a B in some COSC course.
Build these queries into set difference queries as views based on student numbers and execute them, as follows:
a. Write a query that gives the student number, name, course, and grade for each set. Save each query as Q7_5a and Q7_5b.

Q7_5a
SELECT DISTINCT student.SNAME
  FROM  [Section] INNER JOIN [Grade_report]
 ON Section.SECTION_ID = Grade_report.SECTION_ID
 INNER JOIN [Student]
 ON Grade_report.STUDENT_NUMBER = Student.STNO
  WHERE GRADE in ('A', 'B') AND COURSE_NUM like 'COSC%'

Q7_5b
SELECT DISTINCT student.SNAME
  FROM  [Section] INNER JOIN [Grade_report]
 ON Section.SECTION_ID = Grade_report.SECTION_ID
 INNER JOIN [Student]
 ON Grade_report.STUDENT_NUMBER = Student.STNO
  WHERE COURSE_NUM like 'MATH%'

b. Reconstruct each query into a view of just student numbers, verify that it works, and then create views to create set A and set B. Verify that you have the same number of tuples in set A as you have in Q7_5a, and the same number of tuples in set B as you have in Q7_5b.

CREATE VIEW vwQ7_5a AS
SELECT DISTINCT student.SNAME, student.STNO
  FROM  [Section] INNER JOIN [Grade_report]
 ON Section.SECTION_ID = Grade_report.SECTION_ID
 INNER JOIN [Student]
 ON Grade_report.STUDENT_NUMBER = Student.STNO
  WHERE GRADE in ('A', 'B') AND COURSE_NUM like 'COSC%'
 
CREATE VIEW vwQ7_5b AS
SELECT DISTINCT student.SNAME, student.STNO
  FROM  [Section] INNER JOIN [Grade_report]
 ON Section.SECTION_ID = Grade_report.SECTION_ID
 INNER JOIN [Student]
 ON Grade_report.STUDENT_NUMBER = Student.STNO
  WHERE COURSE_NUM like 'MATH%' 

c. Display the student numbers of students in each set differenceshow (set A minus set B) and (set B minus set A). Look at the original queries, Q7_5a and Q7_5b, to verify your result.

A-B
SELECT *
FROM  vwQ7_5a a
WHERE a.STNO NOT IN (
 SELECT STNO FROM vwQ7_5b)
 
B-A
SELECT *
FROM  vwQ7_5b b
WHERE b.STNO NOT IN (
 SELECT STNO FROM vwQ7_5a)
 

6. Create two tables, T1 and T2, that contain a name and a salary column. In the first table, order the columns by name, and then by salary. In the second table, order the columns by salary, and then by name. Use the same data types for each - VARCHAR(20), NUMBER, for example. Populate the tables with two tuples each.

CREATE TABLE T1
(
 [c_name] VARCHAR(20),
 [c_number] INTEGER
)

CREATE TABLE T2
(
 [c_number] INTEGER,
 [c_name] VARCHAR(20)

)

7. Can you UNION the two tables in the preceding question with the following query?
    SELECT *
    FROM T1
      UNION
    SELECT *
    FROM T2

Why or why not? If not, can you force the union of the two tables? Illustrate how. Be sure to DROP the tables when you are finished.
NO. Type mismatch.
    SELECT cname, cnumber
    FROM T1
      UNION
    SELECT cname, cnumber
    FROM T2

8. Using the Instructor table you created in this chapter (as well as the tables supplied in the Student_course database), find the following (use the UNION or INTERSECT operator if you feel it is appropriate):
a. All departments that have instructors. First do this using an IN predicate, and then using a regular join.
SELECT DISTINCT c.OFFERING_DEPT
FROM Course c
WHERE c.COURSE_NUMBER IN
(SELECT s.COURSE_NUM
 FROM Section s
 WHERE s.INSTRUCTOR IS NOT NULL)

SELECT DISTINCT c.OFFERING_DEPT
FROM Course c INNER JOIN  Section s
ON c.COURSE_NUMBER = s.COURSE_NUM
WHERE INSTRUCTOR IS NOT NULL

b. Find all students who are also instructors.

SELECT s.INSTRUCTOR
 FROM Section s
 WHERE s.INSTRUCTOR IS NOT NULL
INTERSECT
SELECT sname
FROM Student

c. Find all instructors who are not students.

SELECT DISTINCT INSTRUCTOR
 FROM Section
 WHERE INSTRUCTOR NOT IN
 (
 SELECT sname
  FROM Student
  
 )

d. Find all students who are not instructors.
SELECT sname
FROM Student
WHERE SNAME NOT IN
(
SELECT INSTRUCTOR
 FROM Section
 WHERE INSTRUCTOR IS NOT NULL
)

e. Find all students as well as instructors.
SELECT DISTINCT INSTRUCTOR
 FROM Section
 WHERE INSTRUCTOR IN
 (
 SELECT sname
  FROM Student
  
 )

9. Using the Student table, find all the students who major in math and are seniors. Hint: Use the INTERSECT operator for this.

SELECT sname
 FROM Student
 WHERE MAJOR = 'MATH'
 
INTERSECT
 
SELECT sname
 FROM Student
 WHERE CLASS = 3

 

7.12. Optional Exercise
1. De Morgan's Theorem.In the binary case, DeMorgan's Theorem tells us that [not(A and B)] = [not(A) or not(B)]. For example, suppose that A is the set of rows where students are juniors and B is the set of rows where students are females. And suppose that you were asked the question, "Find the students who are not (female and juniors)." Clearly this is the set [not(A and B)]. You can answer this question by finding the set of students who are not juniors [not(A)] and then or-ing this with the set of students who are not females [not(B)]. At times it is easier to find one or the other of the results via a query, and the point here is that the two methods of finding a result is equivalent.
Question: Find the result set for all sections that are offered in building 13 and call this set A. Find the result set for all sections that are offered in building 36 and call this set B. Construct the SQL to find the following result sets:
a. The result of set A OR set B (use WHERE building = 13 or building = 36).

SELECT *
 FROM Section
 WHERE BLDG = 13 OR BLDG =36

b. The result of the complement of (a): NOT(set A OR set B).

SELECT *
 FROM Section
 WHERE NOT ( BLDG = 13 OR BLDG =36)
 

c. The result of NOT(set A) AND NOT(set B).

SELECT *
 FROM Section
 WHERE (NOT BLDG = 13) AND (NOT BLDG =36)
 
d. The count of all rows in the Section table.
Is the count in d = a + b? Is the result of c the same as the result of b? Explain why or why not in each case (Hint: You may apply the De Morgan's Theorem which states that NOT(set A or set B) = NOT(set A) and NOT(set b).

No. Because there are 3 rows containg NULL BLDG balue.