SQL Chapter 6

As you do the exercises, unless it is stated otherwise, you will be using the tables from our standard Student- Course database. Also, as you do the exercises, it will be a good idea to copy/paste your query as well as your query result into a word processor.

6-1. In this exercise, you will test the UNION operator. Having seen how the union operator works, demonstrate some permutations to see what will work legally and what will not. First, create two tables:

Table1 Table2

AB ABCD x1 y1 x2 y2 z2 w2 r1 s1 r2 s2 T2 u2

Let the type of As and Bs be CHAR(2) Let the type of C in Table2 be VARCHAR2(2) and D in Table2 be VARCHAR2(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 OR REPLACE VIEW viewx AS SELECT A,B FROM Table2; SELECT * FROM Table1UNION SELECT * FROM viewx;

##

. Create and print the result of a query that generates the name, class and course numbers of students who have Bs in computer science courses. Store this query as Q62. (Be sure to store the result too.) Then, revise Q62 to delete from the result set those students who are sophomores (class = 2) using NOT..IN to SELECT sophomores. Repeat this exercise using MINUS instead of NOT..IN.

##

. Find the student names, grades, and course numbers of students who have earned As in computer science or math courses. Create a join of 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 numbers MATH____ and A.

Hint: Start with the query to get names, grades, and course numbers for COSC____ and A, then turn this 141 into a view. Do the same for MATH____ and A, then execute the UNION statement like this (using your view names):

SELECT * FROM view1a UNION SELECT * FROM view1b;