Solved Homework: Question: Pane (40 points) Based on the data given (shown below) for the Academic Database, complete the fo…

I have used the table names as you they are named in the photos. Also, after every table name i have given it an alias name so that it is easy to use them in queries. For instance in query 2, ALIAS name of course_details is c and ALIAS name of academic_session_details is a.

Here are your queries:-

1) SELECT course_name FROM course_details;

2) SELECT c.course_name FROM course_details c JOIN academic_session_details a ON a.session_id = c.session_id AND a.session_name LIKE ‘Fall Session’;

#display course details, department and student details who have enrolled in that course

3) SELECT c.course_id, c.course_name, c.session_id, c.department_id, d.department_name, d.head_of_department, sd.first_name, sd.student_reg_year FROM (((course_details c
JOIN department_details d ON c.department_id = d.department_id)
JOIN student_course_details sc ON c.course_id = sc.course_id )
JOIN student_details sd ON sd.student_id = sc.student_id);

4) SELECT c.course_id, c.course_name, c.session_id, c.department_id, d.department_name, d.head_of_department, sd.first_name, sd.student_reg_year FROM (((course_details c
JOIN department_details d ON c.department_id = d.department_id)
JOIN student_course_details sc ON c.course_id = sc.course_id )
JOIN student_details sd ON sd.student_id = sc.student_id) WHERE c.department_id = 30;

# SELECT COURSE_NAME, MARKS, EXAM_ID FROM COURSE d JOIN EXAM_RESULTS a USING (a.COURSE_ID);

5) SELECT C.COURSE_NAME, M.MARK, M.EXAM_ID FROM COURSE C
JOIN EXAM_RESULTS_DETAILS M ON C.Course_id = M.course_id;

#marks of students who have course id between 190 to 195

6) SELECT STUDENT_ID, MARK FROM EXAM_RESULTS_DETAILS WHERE Course_id between 190 AND 195;

#rows from exam results table even if no matching records in course between

7) SELECT e.student_id, e.exam_id, e.course_id, e.mark from EXAM_RESULTS_DETAILS e
WHERE e.course_id NOT IN (SELECT course_id FROM course_details);

#select * from EXAM cross join Exam_type

8) It will create a cartesian product of both the tables. Every row of Table 1 will be associated with every row of table 2. There is no WHERE CLAUSE in cross join