4. Joint query of Table 4: student course selection query

Question and Description:
Students and achievement table students are the main table and achievement is the sub table
Curriculum and score table curriculum is the main table and score is the sub table
Teacher and curriculum teacher are the main table, curriculum is the sub table

• 1. student list
  Student (Sා, Sname, Sage, Ssex) - Sාstudent number, Sname student name, Sage birth date, Ssex student gender
• 2. course schedule
  Course (C × name, T × name) - C × course number, Cname course name, T × teacher number
• 3. teacher list
  Teacher (T Chen, Tname) - T Chen teacher number, Tname teacher name
• 4. score sheet
  SC (S, C, score) - S student number, C course number, score

1. Create table and insert data

In order to facilitate the later query, there will be some unreasonable data to cater to the problem, but as long as the purpose can be achieved

--1.Create student table Student(S#,Sname,Sage,Ssex) 
--S# Student number, Sname student name, Sage date of birth, Ssex student gender
create table student(
   s#  varchar2(5) primary key ,  --Because it is an exercise, the primary key constraint here is written directly after the field
   sname  varchar2(20) not null, 
   sage  date,  
   ssex  char(3) default 'male' check(ssex = 'male' or ssex = 'female')
);
--Insert student data
insert into student values('s101','Zhao Ling er','01-5 month-1996','female');
insert into student values('s102','Lin Yue Ju','05-7 month-1994','female');
insert into student values('s103','Li Xiaoyao','16-9 month-1994','male');
insert into student values('s104','Wine Sword Fairy','12-12 month-1985','male');
insert into student values('s105','Mao Mao','14-4 month-1996','male');
insert into student values('s106','Mao Mao','14-5 month-1996','male');
insert into student values('s107','Zhao Ling er','01-5 month-1996','female');
insert into student values('s108','Zhao Ling er','01-5 month-1990','female');

--2.Create Curriculum  Course(C#,Cname,T#)
--C# --Course number,Cname Course title,T# Teacher number
create table course(
   c#  varchar2(5),
   cname  varchar2(50) not null, 
   t#  varchar2(5) references teacher(t#) on delete set null,      --Foreign key constraint
   constraints pk_course primary key(c#)-- this writing method is recommended. You can name the primary key    
);
--Insert course data
insert into course values('c10','Chinese','t110');
insert into course values('c20','Mathematics','t111');
insert into course values('c30','English?','t112');
insert into course values('c40','Sports','t113');
insert into course values('c50','Art','t114');

--3.Create teacher table Teacher(T#,Tname) --T# Teacher number, Tname teacher name 
create table teacher(
   t#  varchar2(5),
   tname  varchar2(20) not null,
   constraints pk_teacher primary key(t#)  
);
--Insert teacher data
insert into teacher values('t110','Purple Xuan');
insert into teacher values('t111','Cynanchum paniculate');
insert into teacher values('t112','Tang Xue Jian');
insert into teacher values('t113','Li Zhen Xing');
insert into teacher values('t114','Zhang San');

--4.Create score sheet SC(S#,C#,score) --S# Student number,C# Course number, score
create table sc(
   s#  varchar2(5),  
   c#  varchar2(5),
   score  number(3),
   constraints pk_sc primary key(s#,c#),
   constraints ck_sc_score check(score>=0 and score<=100),
   constraints fk_sc_s# foreign key(s#) references student(s#) 
   on delete set null,   
   constraints fk_sc_c# foreign key(c#) references course(c#) 
   on delete set null
);
--Insert achievement data to facilitate data query
insert into sc values('s101','c10',98);
insert into sc values('s101','c20',95);
insert into sc values('s101','c30',100);
insert into sc values('s101','c40',100);
insert into sc values('s102','c10',88);
insert into sc values('s102','c20',68);
insert into sc values('s102','c30',70);
insert into sc values('s102','c50',90);
insert into sc values('s103','c10',55);
insert into sc values('s103','c20',75);
insert into sc values('s103','c30',59);
insert into sc values('s103','c40',89);
insert into sc values('s103','c50',98);
insert into sc values('s104','c10',90);
insert into sc values('s104','c20',100);
insert into sc values('s104','c30',60);
insert into sc values('s105','c10',56);
insert into sc values('s105','c20',30);
insert into sc values('s105','c30',60);
insert into sc values('s105','c40',65);

The inquiry content and reference code are as follows

Because it's a beginner's study, there may be some imprecise and wrong places. You are welcome to correct them

--1,query"c10"Curriculum ratio"c20"Information and course scores of students with high course scores
select s.*,sc1.score as c10 achievement,sc2.score as c20 achievement       --sql 92 Writing method
       from student s,sc sc1,sc sc2       
       where sc1.s# = sc2.s# 
       and s.s# = sc1.s#
       and (sc1.c# = 'c10' and sc2.c# = 'c20') 
       and sc1.score > sc2.score;
                                                         --sql 99 Writing method
select s.*,sc1.score as c10 achievement,sc2.score as c20 achievement from student s join sc sc1 
       on s.s# = sc1.s#
       join sc sc2 on sc1.s# = sc2.s#
       where sc1.c# = 'c10' and sc2.c# = 'c20' and sc1.score > sc2.score;     

--2,query"c10"Curriculum ratio"c20"Information and course scores of students with low course scores
select s.*,sc1.score as c10 achievement,sc2.score as c20 achievement from student s join sc sc1
       on s.s# = sc1.s#
       join sc sc2 on sc1.s# = sc2.s#
       where sc1.c# = 'c10' and sc2.c# = 'c20' and sc1.score < sc2.score;

--3,Query student number, student name and average score of students with an average score of 60 or above
select s.s#,sname,avg_sc from student s join 
       (select s#,avg(score) avg_sc from sc 
       group by s# having avg(score) >= 60) t
       on s.s# = t.s#
       order by s.s#;

--4,Query student number, student name and average score of students with an average score of less than 60
select s.s#,sname,avg_sc from student s join 
       (select s#,avg(score) avg_sc from sc group by s# having avg(score) < 60) t
       on s.s# = t.s#;

--5,Query all students' student numbers, student names, total number of courses selected, and total scores of all courses
select s.s#, sname, total number of courses selected, total score from student s join
       (select s#,count(c#) as Total number of elective courses,sum(score) as Total score from sc group by s#) t
       on s.s# = t.s#
       order by s.s#;

--6,query"Plum"Number of last name teachers
select count(tname) as Teacher Li from teacher group by tname having tname like 'Plum%';

--7,Query learning"Zhang San"Information of students taught by teachers
select s.* from student s join sc on s.s# = sc.s#
       join course c on c.c# = sc.c#
       join teacher t on t.t# = c.t#
       where tname = 'Zhang San';

--8,Inquiry has not been learned"Zhang San"Information of students taught by teachers
select  s.* from student s where s.s# not in 
       (select s.s# as tid from student s join sc on s.s# = sc.s# --Students who have studied Mr. Zhang San's course
       join course c on c.c# = sc.c#
       join teacher t on t.t# = c.t#
       where tname = 'Zhang San');

--9,Inquiry learning No"c40"And I learned the number"c50"Information of students in the course of
select s.* from student s join sc sc1 on s.s# = sc1.s#
       join sc sc2 on sc1.s# = sc2.s#
       where sc1.c# = 'c40' and sc2.c# = 'c50';

--10,Inquiry learning No"c40"But I haven't learned the number"c50"Information of students in the course of
select s.* from student s 
       where s# in (select s# from sc sc1 where c# = 'c40') --Learned c40
       and s# not in (select sc.s# from sc where c# = 'c50'); --Never learned. c50     

--11,Query the information of students who did not complete all courses
select distinct s.* from student s join sc 
       on s.s# = sc.s#
       where s.s# in(select s# from sc group by s# having count(c#) < 5);

--12,Query at least one course and student No"s101"Of students learn the same information
select distinct s.* from student s join sc sc1 on s.s# = sc1.s#
       where sc1.c# in (select c# from sc where s# = 's101')
       and s.s# <> 's101';

--13,Query and"s101"Students of No. 1 study the same course information of other students   --It's a little complicated to solve the conditions in sections. There should be some simple writing methods

select s.* from student s where s.s# in
  (select distinct s# from sc where s# <> 's101'         --The number is not. s101                      
       and c# in (select c# from sc where s# = 's101')   --Elective courses in s101 In the elective courses of 
       and s# not in (select s# from sc where c# not in (select c# from sc where s# = 's101'))                                                  
       and s# in (select s#  from sc group by s#         --All the electives in s101
       having count(c#) = (select count(c#) from sc where s# = 's101')));   
                                                         --Number of elective courses and s101 Same student number

--14,Inquiry has not been learned"Zhang San"Student name of any course taught by the teacher
select  s.* from student s where s.s# not in 
       (select s.s# as tid from student s join sc on s.s# = sc.s#  --Students who have studied Mr. Zhang San's course
       join course c on c.c# = sc.c#
       join teacher t on t.t# = c.t#
       where tname = 'Zhang San');

--15,Check the student number, name and average score of students who fail two or more courses
select s.s#,sname,t.avg_score from student s 
       join (select s#,avg(score) as avg_score from sc group by s#) t
       on s.s# = t.s#        
       where t.s# in (select s# from sc where score < 60 group by s# having count(s#) >= 2);

--16,retrieval"c10"Student information in descending order with course score less than 60
select s.*,sc.score as c1_score from student s join sc on s.s# = sc.s#
       where sc.c# = 'c10' and score < 60 order by score desc;

--17,Query the top three records of each subject by subject c10 reach c50 with c10 take as an example
select t.s#,s.sname,t.c#,t.score from student s join 
       (select * from sc where c# = 'c10' order by score desc) t
       on s.s# = t.s#
       where rownum <= 3; 

--18,Query the number of students selected for each course       
select t.c#,cname,t.count_s# from course c 
       join (select c#,count(s#) as count_s# from sc group by c#) t
       on c.c# = t.c#;

--19,Find out the student number and name of all students with only two courses  --Change to 4 courses
select distinct s.s#,sname from student s join sc on s.s# = sc.s#
       where s.s# in (select s# from sc group by s# having count(c#) = 4);

--20,Query the number of boys and girls
select ssex,count(s#) from student group by ssex;

--21,Query name contains"wind"Change the student information of the word to month
select * from student where sname like '%month%';

--22,Query the list of students with the same name and count the number of students with the same name

select sname ,count(sname) as Number from         --Number of persons of the same name
   (select sname,ssex as sse from student s where sname in 
   (select sname from student group by sname having count(sname) >1 /*Same name*/)) --Homonymous homosexuality
   group by sname ;

--23,Check the list of students born in 1990(Note: Student In the table Sage The type of column is datetime)    
select * from student where trunc(to_date(sage),'YEAR') = '1-1 month-1990';


--24,Query the average scores of each course, and the results are arranged in descending order of average scores. When the average scores are the same, the results are arranged in ascending order of course number
select c#,avg(score) from sc group by c# order by avg(score) desc,c#;

--25,Query student ID, name and average score of all students with an average score of 85 or higher
select s.s#, sname ,avg_score from student s join 
       (select s#, avg(score) as avg_score from sc group by s#) t on s.s# = t.s# 
       where avg_score >= 85;

--26,Query course name as"Mathematics"，Names and scores of students with scores below 60
select sname, score from student s join sc on s.s# = sc.s#
       join course c on sc.c# = c.c#
       where c.cname = 'Mathematics' and score < 60;

--27,Query all students' courses and scores;
select sname, cname, score from student s join sc on s.s# = sc.s#
       join course c on sc.c# = c.c# ;

--28,Query the name, course name and score of any course with score above 70;
select sname, cname, score from student s join sc on s.s# = sc.s#
       join course c on sc.c# = c.c# 
       where sc.score > 70;

--29,Query failed courses
select sname, cname, score from student s join sc on s.s# = sc.s#
       join course c on sc.c# = c.c# 
       where sc.score < 60;

--30,Query course No c10 Student number and name of the student whose course score is more than 80;
select s.s# ,sname from student s join sc on s.s# = sc.s#      
       where sc.c# = 'c10' and sc.score > 80;

--31,Number of students for each course
select c.c# ,cname, count_s# from course c 
       join (select c# ,count(s#) as count_s# from sc group by c#) t
       on c.c# = t.c#;

--32,Query and elective"Zhang San"Among the students in the course taught by the teacher, the student information with the highest score and their scores   
--Common practice      
select sname ,sc.score from student s join sc on s.s# = sc.s#
       join course c on c.c# = sc.c#
       join teacher t on t.t# = c.t#
       where t.tname = 'Zhang San'  --Find a classmate who chose Mr. Zhang San
       and sc.score =         --Match with Mr. Zhang San's student with the highest score
      (select max(score) from student s join sc on s.s# = sc.s#
       join course c on c.c# = sc.c#
       join teacher t on t.t# = c.t#
       where t.tname = 'Zhang San') ;
--Use rownum       
select sname,sc_score from (select sname,sc.score as sc_score from student s 
       join sc on s.s# = sc.s#
       join course c on c.c# = sc.c#
       join teacher t on t.t# = c.t#
       where t.tname = 'Zhang San' 
       order by score desc) 
       where rownum = 1

--33,Query student number, course number and student score of students with the same score in different courses
select s.s#,s.sname, sc1.c# ,sc1.score from student s join sc sc1 
       on s.s# = sc1.s#
       join sc sc2 on sc1.s# = sc2.s#
       where  sc1.c# <> sc2.c#      --Different courses
       and sc1.score = sc2.score;   --Same score


--34,Check whether the top two students in each course have the best scores c10 take as an example
select t.s#,s.sname,t.c#,t.score from student s join 
       (select * from sc where c# = 'c10' order by score desc) t
       on s.s# = t.s#
       where rownum <= 2; 

--35,Count the number of students in each course (only for courses with more than 5 students). It is required to output course number and number of electives,
--   The query results are arranged in descending order of the number of people. If the number of people is the same, they are arranged in ascending order of the course number 
select c.c# ,cname, count_s# from course c 
       join (select c# ,count(s#) as count_s# from sc group by c#) t
       on c.c# = t.c#
       where count_s# >=5
       order by count_s# desc,t.c#;


--36,Retrieve student ID for at least two courses
select s.s# ,sname, count_c# from student s 
       join (select s#,count(c#) as count_c# from sc group by s# having count(c#) >= 2) t
       on t.s# = s.s#
       order by t.count_c# desc,s.s#;       

--37,Query the information of students who have taken all courses
select s.s#, sname, count_c# from student s 
       join (select s#,count(c#) as count_c# from sc group by s# ) t
       on t.s# = s.s#
       where count_c# = (select count(c#) from course);  --Take all courses

--38,Query the age of each student

select sname, months_between(trunc(sysdate,'YEAR'),trunc(to_date(sage),'YEAR'))/12 
      as age from student;

The path of practice is long and obstructed. Let's encourage each other