Oracle 12c: SQL

Chapter Nine Assignment

To perform the following assignments, refer to the tables created in the New Build Script Chapter 9.sql (http://www.mediafire.com/file/faucds37eooucu0/New_Build_Script_Chapter_9.sql) script in Black Board Learn/Course Content/ Chapter 9 Joining Data from Multiple Tables. Generate and test two SQL, queries for each of the following asks:

The SQL statement needed to perform the stated task with the traditional approach.

The SOL statement needed to perform the stated task with the JOIN keyword.

1. Create a list of all students (first name and last name separated by a comma) listed as Student, and their professors name (first name and last name separated by a comma) listed as Professor.

2. Create a distinct list of all professors (first name and last name separated by a space) listed as Professor, and their courses listed as Courses.

3. Create a distinct list of all professors (first name and last name separated by a space) listed as Professor, and the description of the courses they teach listed as Courses.

4. Create a list of students (first name and last name separated by a comma) listed as Student enrolled in course, also list the professors name (first name and last name separated by a comma) listed as Professor of the courses, the course description listed as Course Description and the students grade for the course listed as Grade.

5. Create a list of all the stores and their managers names (first and last separated by a space including there title) list the stores even if there is no manager. Hint: There is only one way to do this problem and it in involves outer joins, but DO NOT use the JOIN keyword.