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).

Try the following statements and note the results:

    SELECT * FROM Table1 UNION SELECT * FROM Table2
    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.

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.

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

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.

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:

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.

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.

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):

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