Construct SQL queries using Oracle for the following questions. 1) List the numb
ID: 3849986 • Letter: C
Question
Construct SQL queries using Oracle for the following questions.
1) List the number of courses (how many) taught by each teacher (teacherid is enough no name needed).
2) Now, list the teacherID, teacherName along with the number of courses taught by each teacher. (Use a Nested SELECT if possbile for this question)
3) List the teacher name, teacher ID, and courses that contain the word ‘Management’
4) List all the courses taught by the professors who draw greater than the average salary. Include the courseId, courseName, teahcerID, teacherId, teacherName, salary in your listing.
The code for creating the tables is provided below:
CREATE TABLE r_professors(TeacherID CHAR(2) PRIMARY KEY,
TeacherName VARCHAR2(25),
Salary NUMBER(10,2),
DeptID CHAR(2));
INSERT INTO r_professors VALUES ('11','R',66000,'01'); INSERT INTO r_professors VALUES ('12','Hackbarth',65000,'01');
INSERT INTO r_professors VALUES ('13','Frank Braun',67000,'01');
INSERT INTO r_professors VALUES ('14','John Maddoc',58000,'02');
INSERT INTO r_professors VALUES ('15',' Brandi Neal ',68000,'03');
CREATE TABLE r_courses (CourseID CHAR(6) PRIMARY KEY,
CourseName VARCHAR2(25), TeacherID CHAR(2) REFERENCES r_professors(TeacherID));
INSERT INTO r_courses VALUES ('INF282','Database','11');
INSERT INTO r_courses VALUES ('MBI677',’ABAP','11');
INSERT INTO r_courses VALUES ('MBI650','Project Management','12');
INSERT INTO r_courses VALUES ('MBI665','Knowledge Management','13');
INSERT INTO r_courses VALUES ('BUS610','Law and Public Policy','14');
INSERT INTO r_courses VALUES ('BUS330','Ethics','14');
INSERT INTO r_courses VALUES ('INF382','IT Security','13');
INSERT INTO r_courses VALUES ('INF480','Systems Analysis','12');
INSERT INTO r_courses VALUES ('INF420','Web Development','15');
CourseID Course Name Teacher ID 282 Database 11 MB1677 ABAP 11 12 MB1650 Project Management MBI665 Knowledge Management 13 BUS610 Law & Public Policy 14 BUS330 ethics 14 INF382 IT Security 12 INF480 Systems Analysis INF420 Web Development 15 Professors Teacher ID TeacherName Salary DeptID 11 Raghavan 66000 01 12 Hackbarth 65000 01 13 Frank Braun 67000 01 14 John Maddoc 58000 02 15 Brandi Neal 68000 03Explanation / Answer
1) SELECT TEACHERID,COUNT(COURSENAME) FROM R_COURSES GROUPBY TEACHERID;
EXPLANATION: FIRST THE QUERY WILL GROUP ALL TEACHERS AS ONE GROUP AND COUNTS THE COURSES TAUGHT BY THEM.
2) SELECT C.TEACHERID,P.TEACHERNAME,COUNT(P.COURSENAME) FROM R_COURSES C,R_PROFESSORS P WHERE C.TEACHER_ID IN (SELECT DISTINCT(C2.TEACHERID) FROM R_PROFESSORS C2) GROUP BY C.TEACHER_ID
EXPLANATION: FIRST WE WILL RETRIEVE ALL INFORMATION ABOUT TEACHERS THEN USING NESTED QUERY WE WILL JOIN THEM.
3) SELECT P.TEACHERNAME,P.TEACHERID,C.COURSENAME FROM R_COURSES C,R_PROFESSORS P WHERE P.TEACHERID=C.TEACHERID AND C.COURSENAME LIKE '%MANAGEMENT%'
EXPLANATION: WE WILL GROUP ALL THE REQUIRED INFORMATION BASED ON THE COURSE MANAGEMENT.
4) SELECT C.COURSEID,C.COURSENAME,P.TEACHERID,P.TEACHERNAME FROM R_COURSES C,R_PROFESSORS P WHERE P.TEACHERID=R.TEACHERID AND P.SALARY>(SELECT AVG(P1.SALARY) FROM R_PROFESSORS P1)
EXPLANATION: FIRST IT WILL GROUP THE SALARIES BASED ON AVERAGE SALARY THEN WILL RETRIEVE THE REQUIRED INFORMATION