Notes

/*
NAME: prashima Grg
ID: 18413647
WEEK:9
DATE: 29 Dec
*/
--to describe
DESC courses;
DESC subject_areas;

--display matched items in subject_areas with course
SELECT s.subject_id,s.subject_name,c.course_id,c.title
FROM subject_areas s
INNER JOIN courses c
ON s.subject_id = c.subject_id;

--Select without using join clause
SELECT s.subject_id,s.subject_name,c.course,c.title
FROM subject_areas s,courses c;

--To describe structure of instructors ,offerings,courses
DESC instructors;
DESC offerings;
DESC courses;

--To display the courses and tutors name
SELECT c.title, i.instructor_name
FROM courses c
INNER JOIN offerings o
ON c.course_id = o.course_id
INNER JOIN instructors i
ON o.instructor_id= i.instructor_id;

--Select courses and instructors with cost
SELECT c.title,i.instructor_name, c.cost
FROM courses c
INNER JOIN offerings o
ON c.course_id = o.course_id
INNER JOIN instructors i
ON o.instructor_id= i.instructor_id;

--TO display column format of title
COLUMN title FORMAT A15
COLUMN cost FORMAT 9999.9
--To display courses and instructors with cost
SELECT c.title,i.instructor_name, c.cost
FROM courses c
INNER JOIN offerings o
ON c.course_id = o.course_id
INNER JOIN instructors i
ON o.instructor_id= i.instructor_id;
ORDER BY c.cost,o.instructor_id;

-- To describe the structure of students and attendance
DESC students;
DESC attendance;


--display the students whether they attend the course or not
SELECT s.student_id,s.student_firstname, s.student_surname, a.evaluation
FROM students s
LEFT JOIN attendance a
ON s.student_id=a.student_id;

--displaying the students whether they attend the course or not using right outer join
SELECT s.student_id,s.student_firstname, s.student_surname, a.evaluation
FROM attendance a
RIGHT JOIN students s
ON s.student_id=a.student_id;

--display all courses,offerings and sites and their relationship
SELECT s.site_id, o.offering_id, c.course_id, c.title
FROM sites s
FULL OUTER JOIN offerings o
ON s.site_id=o.site_id
FULL JOIN courses c
ON o.course_id=c.course_id;

--all the locations and respective taught subjects
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;