Notes

--@E:Database218417139Week8week8.txt
/*
Name:Sudip Bagale
ID:18417139
week:9
*/


--checking subject_areas and courses;
DESC subject_areas;
DESC courses;


--quering to join subject_areas and courses
SELECT sa.subject_id,sa.subject_name,course_id
FROM subject_areas sa
JOIN courses c
ON sa.subject_id = c.subject_id;


--quering with out using join clause
SELECT s.subject_id,s.subject_name,c.course_id,c.title
FROM subject_areas s,courses c;

--checking courses INSTRUCTORS and offering;
DESC courses;
DESC instructors;
DESC offerings;

--quering to see courses including their title, and the instructors by name who have taught them
SELECT i.instructor_id,i.instructor_name,o.offering_id,c.title,c.course_id
FROM instructors i
JOIN offerings o
ON i.instructor_id=o.instructor_id
JOIN courses c
ON c.course_id=o.course_id
ORDER BY instructor_id;


--including cost of courses
SELECT i.instructor_id,i.instructor_name,o.offering_id,c.title,c.cost
FROM instructors i
JOIN offerings o
ON i.instructor_id=o.instructor_id
JOIN courses c
ON c.course_id=o.course_id
ORDER BY c.cost;

COLUMN cost FORMAT 9999.99
COLUMN title FORMAT A15


--checking students and ATTENDANCE
DESC students;
DESC ATTENDANCE;
--quering to view the students attendance
--right join
SELECT s.student_id,s.student_firstname,s.student_surname,a.evaluation
FROM students s RIGHT JOIN attendance a
ON s.student_id = a.student_id;

--quering to view the students attendance
--left join
SELECT s.student_id,s.student_firstname,s.student_surname,a.evaluation
FROM attendance a LEFT JOIN students s
ON s.student_id = a.student_id;


--checking All courses, offerings and sites, and their relationship to each other
DESC courses;
DESC offerings;
DESC sites;

SELECT c.course_id,c.title,o.offering_id,s.site_id,s.location
FROM courses c
FULL JOIN offerings o
ON c.course_id=o.course_id
FULL JOIN sites s
ON s.site_id=o.site_id;

--checking table subject_areas,courses and sites
SELECT s.site_id,s.location,sa.subject_name,o.offering_id,c.title
FROM subject_areas sa JOIN courses c
ON c.subject_id =sa.subject_id
JOIN offerings o
ON c.course_id=o.course_id
RIGHT JOIN sites s
ON s.site_id = o.site_id;