Lab 4

Unless specified otherwise, use the Student_course database to answer the following questions. Also, use appropriate column headings when displaying your output.

1. Create two tables, Stu(name, majorCode) and Major(majorCode, majorDesc), with the following data. Use VARCHAR for the codes and appropriate data types for the other columns.

   Stu
   name	majorCode
   Jones	CS
   Smith	AC
   Evans	MA
   Adams	CS
   Sumon

   
   

   Major
   majorCode	majorDesc
   AC	Accounting
   CS	Computer Science
   MA	Math
   HI	History

   
   

   1. Display the Cartesian product (no WHERE clause) of the two tables. 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)?

   2. Display an equi-join of the Stu and Major tables on majorCode. First do this using the INNER JOIN, and then display the results using the equi-join with an appropriate WHERE clause. Use appropriate table aliases. How many rows did you get?

   3. Display whatever you get if you leave off the column qualifiers (the aliases) on the equi-join in question 1b. (Note: This will give an error because of ambiguous column names.)

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

   5. Display the name, majorCode, and majorDesc of all students regardless of whether or not they have a declared major (even if the major column is null). (Hint: You need to use a LEFT OUTER JOIN here if Stu is the first table in your equi-join query.)

   6. Display a list of majorDescs available (even if the majorDesc does not have students yet) and the students in each of the majors. (Hint: You need to use a RIGHT OUTER JOIN here.)

   7. Display the Cartesian product of the two tables using a CROSS JOIN.

2. Create two tables, T1(name, jobno) and T2(jobno, jobdesc). Let jobno be data type INT, and use appropriate data types for the other columns. Put three rows in T1 and two rows in T2. Give T1.jobno values 1, 2, 3 for the three rows: <..., 1>,<..., 2,>,<..., 3>, where ... represents any value you choose. Give T2.jobno the values 1, 2: <1,...>,<2,...>.

   1. How many rows are in the equi-join (on jobno) of T1 and T2?

   2. If the values of T2.jobno were <2,...>, <2,...> (with different jobdesc values), how many rows would you expect to get, and why? Why would the rows have to have different descriptions?

   3. If the values of T2.jobno were 4, 5 as in <4,...>,<5,...>, how many rows would you expect to get?

   4. If the values of T1.jobno were <..., 1>,<..., 1>,<..., 1> (with different names) and the values of T2.jobno were <1,...>,<1...> with different descriptions, how many rows would you expect to get?

   5. If you have two tables, what is the number of rows you may expect from an equi-join operation (and with what conditions)? A Cartesian product?

   6. The number of rows in an equi-join of two tables, whose sizes are m and n rows, is from ___ to ____ depending on these conditions: _________ .

3. Use tables T1 and T2 in this exercise. Create another table called T3(jobdesc, minpay). Let minpay be of data type SMALLMONEY. Populate the table with at least one occurrence of each jobdesc from table T2 plus one more jobdesc that is not in T2. Write and display the result of a triple equi-join of T1, T2, and T3. Use an appropriate comment on each of the lines of the WHERE clause on which there are equi-join conditions. (Note: You will need two equi-join conditions.)

   1. How many rows did you get in the equi-join?

   2. Use the COUNT(*) function and display the number of rows in the equi-join.

   3. How many rows would you get in this meaningless, triple Cartesian product (use COUNT(*))?

   4. In an equi-join of n tables, you always have _______ _ equi-join conditions in the WHERE clause.

      In the preceding three exercises, you created tables T1, T2, T3, Stu, and Major. When you have completed the three exercises, delete these tables.

      Answer questions 4 through 8 by using the Student_course database.

4. Display a list of course names for all of the prerequisite courses.

5. Use a JOIN or INNER JOIN to join the Section and Course tables.

   1. List the course names, instructors, the semesters and years they were teaching in.

   2. List the instructor, course names, and offering departments of each of the courses the instructors were teaching.

6. Use a LEFT OUTER JOIN to join the Section and Course tables.

   1. List the course names, instructors, and the semesters and years they were teaching in. Sort in descending order by instructors.

   2. List the instructor, course names, and offering departments of each of the courses the instructors were teaching.

7. Use a RIGHT OUTER JOIN to join the Section and Course tables.

   1. For each instructor, list the name of each course they teach and the semester and year in which they teach that course.

   2. For each course, list the name of the instructor and the name of the department in which it is offered.

8. 1. Are there any differences in the answers for questions 5, 6, and 7? Why? Explain.

   2. Use a FULL OUTER JOIN to join the Section and Course tables. How do the results vary from the results of questions 5, 6, and 7?

9. Discuss the output that the following query would produce:

   SELECT *
   
   FROM Course AS c, Prereq AS p
   
   WHERE c.course_number<>p.course_number
   
   

   
   

10. Find all the sophomores who are more senior than other students. (Hint: Use a self-join.)

11. Find all the courses that have more credit hours than other courses. (Hint: Use a self-join.)

12. Display a list of the names of all students who have dependents, the dependents name, relationship and age, ordered by the age of the dependent.