Scott_OR3.docx - Scott Kruschke COP4710 202001 Database...

Scott Kruschke
COP4710 202001
Database Systems
April 25, 2020
OR Chapter 3
3-1
a. Display the Cartesian product (no WHERE clause). Use SELECT * .... How many rows did
you get? How many rows will you always get when combining two tables with n and m rows in
them (Cartesian product)?
b. Display an equi-join of Stu and Major on majorCode (show this both ways: first using an
appropriate WHERE clause; and second, using ANSI SQL standard syntax). Use table aliases.
How many rows did you get?

c. Leave off the column qualifiers (the aliases) on the equi-join in step b. What do you get? This
will give an error because of ambiguous column names.
d. Use the COUNT(*) function instead of SELECT * in the query. Use COUNT to show the
number of rows in the result set of the equi-join and the Cartesian product. Do the equi-join first
with COUNT, then comment out the WHERE clause for the second answer (put -- in front of the
word WHERE).
e. Add two more major codes to the Major table as follows: <’IT’, ‘Information Technology’>
and <’ST’, ‘Statistics’>. Display all the student names (snames) and majorDescs, but I would
also like to see all the majors listed, even if there are no students taking that major. Show your
query in both ways: first, using ANSI SQL standard syntax, and then using Oracle’s driving
table(+) concept.