[Oracle] Notes set in June

-- 1. The following table shows the information of all students, sorted in ascending order of student numbers, and displayed in alphabetical order of course names for the same student number. -- Student ID Student name Course name score selectst. studno, st. studname, sum (decode (cc. coursename, JAVA, SC. grade, 0) JAVA, sum (decode (cc. c

-- 1. The following table shows the information of all students, sorted in ascending order of student numbers, and displayed in alphabetical order of course names for the same student number. -- Student ID Student name Course name score select st. studno, st. studname, sum (decode (cc. coursename, 'java', SC. grade, 0) JAVA, sum (decode (cc. c

-- 1. The following table shows the information of all students, sorted in ascending order of student numbers, and displayed in alphabetical order of course names for the same student number.
-- Student ID Student name Course name score


Select st. studno, st. studname,
Sum (decode (cc. coursename, 'java', SC. grade, 0) "JAVA ",
Sum (decode (cc. coursename, 'jsp ', SC. grade, 0) "JSP ",
Sum (decode (cc. coursename, 'struts ', SC. grade, 0) "Struts ",
Sum (decode (cc. coursename, 'oracle ', SC. grade, 0) "Oracle ",
Sum (decode (cc. coursename, 'spring', SC. grade, 0) "Spring ",
Sum (decode (cc. coursename, 'economic management', SC. grade, 0) "Economic management ",
Sum (decode (cc. coursename, 'International trade ', SC. grade, 0) "International trade ",
Sum (decode (cc. coursename, 'accounting principles ', SC. grade, 0) "Accounting Principles ",
Sum (decode (cc. coursename, 'foreign Trade Correspondence ', SC. grade, 0) "Foreign Trade Correspondence ",
Sum (decode (cc. coursename, 'marxist principles ', SC. grade, 0) "Marxist principles"
From student st, score SC, course cc where st. studno = SC. studno and cc. courseid = SC. courseid group by st. studno, st. studname;


-- 2. query and display the maximum score of a single subject

-- Student ID: Student name: Course name: Maximum score of a single subject


Select st. studno, st. studname, s. s_k, cc. coursename
From (select s. studno sno, max (s. grade) over (partition by s. studno) s_k
From score s) s, score SC, student st, course cc
Where SC. grade = s_k and s. sno = st. studno and cc. courseid = SC. courseid and st. studno = SC. studno;

-- 3. Check whether the student passes the course or fails.
-- Student ID: Student name Course name: examination passed status

Select st. studno, st. studname, cc. coursename,
Case
When SC. grade> = 60 then 'pass'
Else 'failed'
End "exam passed status"
From student st, score SC, course cc where st. studno = SC. studno and cc. courseid = SC. courseid;


-- 4. count the number of student assignments
-- Student ID: Number of course selections by Student name

Select st. studno, st. studname,
Count (coursename) over (partition by st. studno order by st. studname) course_count
From student st, score SC, course cc where st. studno = SC. studno and cc. courseid = SC. courseid;

-- 5. query the information of the students whose single score exceeds the average score of the course, and list the student numbers, student names, course names, and course scores.

Select st. studno, st. studname, cc. coursename, SC. grade
From student st, score SC, course cc, (select avg (grade) gav, studno from score group by studno) avg_s
Where st. studno = SC. studno and cc. courseid = SC. courseid and SC. grade> avg_s.gav group by st. studno, st. studname, cc. coursename, SC. grade;

-- 6. the query displays the student ID, Student name, and course name of the student to be retake.
Select st. studno, st. studname, cc. coursename
From student st, score SC, course cc
Where exists (select 1 from score s where s. grade <60)
And st. studno = SC. studno and cc. courseid = SC. courseid and SC. grade <60 group by st. studno, st. studname, cc. coursename, SC. grade;


-- 7. Calculate the average score of each subject. The course number, course name, and average score are displayed.

Select avg (grade) avg_s, s. courseid from score s, course cc
Where s. courseid = cc. courseid group by s. courseid;

-- 8. query the student information of the java course.

Select st .*
From student st, course cc, score SC where st. studno = SC. studno and cc. courseid = SC. courseid and cc. coursename = 'java ';

-- 9. query student information that does not take the JAVA Course

Select st .*
From student st, course cc, score SC where st. studno = SC. studno and cc. courseid = SC. courseid and cc. coursename! = 'Java ';

-- 10. query information about students who have selected the instructor Li Ke Course

Select st .*
From student st, teacherinfo tt, courseplan cp where st. studno = cp. studno and tt. teachid = cp. teachid and tt. teachname = 'Li Ke ';

-- 11. query the arrangement of the students who have both A01 and A02 courses. The student ID, Student name, class number, course number, instructor, and course date are displayed.
Select all_stu. *, st. studno from (
Select st. studno sno, st. studname, st. batchcode, cp. courseid, tt. teachname, cp. coursedt
From student st, teacherinfo tt, courseplan cp where
St. studno = cp. studno
And
Cp. courseid = 'a02'
Union
Select st. studno sno, st. studname, st. batchcode, cp. courseid, tt. teachname, cp. coursedt
From student st, teacherinfo tt, courseplan cp where
St. studno = cp. studno
And
Cp. courseid = 'a01') all_stu, student st
Where st. studno = all_stu.sno order by st. studno;

-- 12. query which courses are offered in Class 1 and at which time any teacher will teach

Select tt. teachname, cp. coursedt, cc. coursename
From teacherinfo tt, courseplan cp, course cc, student st where st. batchcode = '000000' and st. studno = cp. studno and tt. teachid = cp. teachid;

-- 13. query the classes that do not attend classes on Monday

Select st. batchcode, cp. coursedt
From teacherinfo tt, courseplan cp, course cc, student st where cp. coursedt! = 'Monday' and st. studno = cp. studno and tt. teachid = cp. teachid;

-- 14. query the names of instructors attending classes on Thursday.

Select cp. coursedt, tt. teachname
From teacherinfo tt, courseplan cp
Where cp. coursedt = 'thurs' and tt. teachid = cp. teachid;

-- 15. query A02 course instructors and course time

Select cc. coursename, tt. teachname, cp. coursedt
From teacherinfo tt, courseplan cp, course cc where cp. courseid = 'a02'
And tt. teachid = cp. teachid order by cp. courseid;

-- 16. Count the percentage of failed students in each subject

Select count (*)
From student st,

Select round (no_grade.n_g/all_grade.a_g) * 100,2) | '%' geade_perce, cc. coursename, st. studno
From (select count (*) n_g, cc. coursename from score SC, course cc, student st
Where grade <60 and SC. courseid = cc. courseid and st. studno = SC. studno group by cc. coursename) no_grade,
(Select count (*) a_g, cc. coursename from score SC, course cc, student st
Where SC. courseid = cc. courseid and st. studno = SC. studno) all_grade, score SC, course cc, student st where SC. courseid = cc. courseid and st. studno = SC. studno;

-- 17. Count the percentage of all failed students in total

Select round (no_grade.n_g/all_grade.a_g) * 100,2) | '%' geade_perce
From (select count (*) n_g from score SC, course cc
Where grade <60 and SC. courseid = cc. courseid) no_grade, (select count (*) a_g from score SC, course cc
Where SC. courseid = cc. courseid) all_grade;


-- 18. Which class is the student with a single subject score of 90 or above, and who is the instructor?

Select cp. courseid, tt. teachname
From student st, courseplan cp, teacherinfo tt, score SC
Where SC. grade> 90 and st. studno = SC. studno and cp. courseid = SC. courseid and tt. teachid = cp. teachid;

-- 19. Who are the instructors of the industrial engineering class?

Select tt. teachname, bb. batchname
From courseplan cp, teacherinfo tt, student st, bbatch bb
Where bb. batchname like '% Industrial Engineering %'
And tt. teachid = cp. teachid and st. batchcode = bb. batchcode;

-- 20. query at what time students on the 12th have classes?

Select cp. coursedt
From courseplan cp, teacherinfo tt, student st, bbatch bb
Where st. studno = 1058 and tt. teachid = cp. teachid and st. batchcode = bb. batchcode;

-- 21. query which students scored more than 90 points.

Select st. studname
From courseplan cp, teacherinfo tt, student st, bbatch bb, score SC
Where SC. grade> 90 and tt. teachid = cp. teachid and st. batchcode = bb. batchcode group by st. studname;

-- 22. query teachers with more than two courses at the same time

Select tt. teachname
From courseplan cp, teacherinfo tt, student st, bbatch bb, score SC
Where (select count (cc. coursename) from courseplan cp, teacherinfo tt, course cc
Where tt. teachid = cp. teachid and cc. courseid = cp. courseid)> 2
And tt. teachid = cp. teachid and st. batchcode = bb. batchcode group by tt. teachname;


-- 23. Summarize the total score of each student in the examination and rank each student side by side. Show student ID, Student name, class number, total score

-------------------------
Select * from (
Select DENSE_RANK () over (order by all_grade.sum_grade desc) rk, all_grade .*
From
(Select sum (SC. grade) sum_grade, st. studno sno, st. studname sna, st. batchcode sba, bb. batchname
From student st, score SC, bbatch bb
Where bb. batchcode = st. batchcode and st. studno = SC. studno group by st. studno, st. studname, st. batchcode, bb. batchname
Order by st. studno, sum_grade desc
) All_grade, student st where st. batchcode = all_grade.sba
)
Where rk <= 10;
--------------------------


-- 24. The student ID, Student name, and total score are displayed in the group by class, and ranked by the total score in a class.
Select * from (
Select DENSE_RANK () over (partition by all_grade.sba order by all_grade.sum_grade desc) rk, all_grade .*
From
(Select sum (SC. grade) sum_grade, st. studno sno, st. studname sna, st. batchcode sba, bb. batchname
From student st, score SC, bbatch bb
Where bb. batchcode = st. batchcode and st. studno = SC. studno group by st. studno, st. studname, st. batchcode, bb. batchname
Order by st. studno, sum_grade desc
) All_grade, student st where st. batchcode = all_grade.sba
)
Where rk <= 3;