Oracle 12c: SQL Chapter Nine Assignment To perform the following assignments, re

Question

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 student’s 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.

Explanation / Answer

Please upvote if you find the answer satisfactory :) (Please comment if you face any issues)

/*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”.

*/
select concat(stu_fname, concat(',',stu_lname)) as Student , concat(PROFESSOR_FIRST_NAME, concat(',',PROFESSOR_LAST_NAME)) as Professor from student s, professor p where p.prof_num=s.prof_num;

/*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”.

*/
select concat(pfn, concat(',',pln)) as Professor,LISTAGG(coursedesc, ',') WITHIN GROUP (ORDER BY coursedesc) as courses from (select distinct crs.CRS_DESCRIPTION coursedesc, p.prof_num as pnum,p.Professor_last_name as pln,p.Professor_first_name as pfn from class c, professor p,course crs where p.prof_num=c.prof_num and c.crs_code=crs.crs_code) group by pnum,pfn,pln;

/*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”.
*/
select concat(pfn, concat(',',pln)) as Professor,LISTAGG(coursecode, ',') WITHIN GROUP (ORDER BY coursecode) as courses from (select distinct crs.crs_code coursecode, p.prof_num as pnum,p.Professor_last_name as pln,p.Professor_first_name as pfn from class c, professor p,course crs where p.prof_num=c.prof_num and c.crs_code=crs.crs_code) group by pnum,pfn,pln;

/*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 student’s grade for the course listed as “Grade”.*/

select concat(stu_fname, concat(',',stu_lname)) as Student , concat(PROFESSOR_FIRST_NAME, concat(',',PROFESSOR_LAST_NAME)) as Professor, crs.CRS_DESCRIPTION as "Course Description", enroll_grade as Grade from student s, professor p, class c, course crs, ENROLL e where p.prof_num=s.prof_num and c.CLASS_CODE=e.CLASS_CODE and crs.CRS_CODE=c.CRS_CODE and e.STU_NUM=s.STU_NUM;

/*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.*/
with emp as (select EMP_LNAME eln,EMP_FNAME efn,e.STORE_CODE from EMPLOYEE_X e, store s, JOB j where e.STORE_CODE=s.STORE_CODE(+) and e.job_code(+)=j.job_code and j.job_code='MGR' ) select s1.store_name,emp.eln,emp.efn from store s1, emp where emp.STORE_CODE(+)=s1.STORE_CODE;

Related Questions

Navigate

• Browse (All)
• Browse O
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.