I got the answer from somewhere, but some of the answers were incorrect. I made some changes here. I passed the test. There are two questions that I can't answer.^^
1. Query the number of all students whose grades are higher in the course of "c001" than in the course of "c002";
SELECT a.sno FROM
(SELECT * FROM sc WHERE CNO='c001') a,
(SELECT * FROM sc WHERE CNO='c002') b
WHERE a.sno=b.sno AND a.score >= b.score;2. Query the number and average score of students whose average score is more than 60 points.
SELECT SNO AS xuehao,AVG(score) AS pingjunfen FROM sc GROUP BY SNO HAVING AVG(score) >60;3. Query the number, name, number of courses selected and total scores of all the students.
– SELECT a.SNO,a.SNAME,b.cnoc,b.ssc FROM student a,(SELECT b.SNO AS SNO, COUNT(CNO) AS CNOC,SUM(score) AS SSC FROM sc b GROUP BY SNO) b WHERE a.sno=b.SNO;
SELECT S.SNAME,B.* FROM (SELECT SNO, COUNT(CNO),SUM(score) FROM sc b GROUP BY SNO) b,STUDENT S WHERE B.SNO=S.SNO;4. Number of teachers whose surname is "liu";
SELECT COUNT(*) FROM TEACHER WHERE TNAME LIKE 'liu%';5. Inquire about the number and name of the students who have not learned the lesson of "zhanyan".
SELECT A.SNO,A.SNAME FROM STUDENT A
WHERE A.SNO NOT IN
(SELECT DISTINCT B.SNO FROM SC B WHERE B.CNO IN
(SELECT S.CNO FROM COURSE S WHERE S.TNO=
(SELECT TNO FROM TEACHER WHERE TNAME='zhanyan')
)
)
select a.sno,a.sname from student a
where a.sno
not in
(select distinct s.sno
from sc s,
(select c.*
from course c ,
(select tno
from teacher t
where tname='zhanyan')t
where c.tno=t.tno) b
where s.cno = b.cno )6. Inquire the number and name of the students who have studied "c001" and have also studied the course number "c002";
SELECT DISTINCT( ST.SNO ),ST.SNAME FROM STUDENT ST ,
(SELECT S.SNO,COUNT(S.SNO) FROM SC S WHERE S.CNO='c001' OR S.CNO='c002' GROUP BY S.SNO HAVING COUNT(S.SNO)=2) S WHERE ST.SNO=S.SNO;select * from sc a
join sc b on a.sno=b.sno
join student st
on st.sno=a.sno
where a.cno='c001' and b.cno='c002' and st.sno=a.sno;select st.sno,st.sname from sc a
join sc b on a.sno=b.sno
join student st
on st.sno=a.sno
where a.cno='c001' and b.cno='c002' and st.sno=a.sno;7. Inquire the number and name of the students who have learned all the lessons taught by the "Jian Yan" teacher.
SELECT SNO,SNAME FROM STUDENT WHERE SNO IN
(SELECT A.SNO FROM SC A
JOIN COURSE B ON A.CNO=B.CNO
JOIN TEACHER C ON C.TNO=B.TNO
AND C.TNAME='zhanyan')select DISTINCT st.* from student st
join sc s on st.sno=s.sno
join course c on s.cno=c.cno
join teacher t on c.tno=t.tno
where t.tname='zhanyan'8. Inquire the number of the course "c002" and the names of all the students whose grades are lower than the number of the course "c001";
SELECT SNO,SNAME FROM STUDENT WHERE SNO IN
(SELECT A.SNO FROM SC A
JOIN SC B ON A.CNO='c001' AND B.CNO='c002' AND A.SNO=B.SNO
AND A.SCORE>B.SCORE);
select * from student st
join sc a on st.sno=a.sno
join sc b on st.sno=b.sno
where a.cno='c002' and b.cno='c001' and a.score < b.score9. Inquire the number and name of all the students whose grades are less than 60;
SELECT SNO,SNAME FROM STUDENT WHERE SNO IN
(SELECT A.SNO FROM SC A WHERE A.SCORE<60);
select st.*,s.score from student st
join sc s on st.sno=s.sno
join course c on s.cno=c.cno
where s.score <60;10. Inquire the number and name of the students who have not studied all the courses.
SELECT ST.SNO,ST.SNAME FROM STUDENT ST
LEFT JOIN SC S ON ST.SNO=S.SNO
GROUP BY ST.SNO,ST.SNAME
HAVING COUNT(S.CNO) < (SELECT COUNT(C.CNO) FROM COURSE C);
select stu.sno,stu.sname,count(sc.cno) from student stu
left join sc on stu.sno=sc.sno
group by stu.sno,stu.sname
having count(sc.cno)<(select count(distinct cno)from course)11. Inquire about the student's number and name of at least one course which is the same as that of the student whose number is "s001";
SELECT ST.SNO,ST.SNAME FROM STUDENT ST WHERE ST.SNO IN
(SELECT S.SNO FROM SC S WHERE S.CNO IN (SELECT CNO FROM SC WHERE SNO='s001') AND S.SNO!='s001')
SELECT DISTINCT ST.SNO,ST.SNAME FROM STUDENT ST
JOIN SC S ON S.SNO=ST.SNO AND S.CNO IN (SELECT CNO FROM SC WHERE SNO='s001') AND S.SNO!='s001'
select st.* from student st,
(select distinct a.sno from
(select * from sc) a,
(select * from sc where sc.sno='s001') b
where a.cno=b.cno) h
where st.sno=h.sno and st.sno<>'s001'12. Inquire about the number and name of other students who have studied at least one course of "s001";
- Ibid.
13. Change the grade of the class taught by the "Jian Yan" teacher in the "SC" table to the average grade of the course.
What the devil
- 14, inquiry and "s001" students learn the same course number and name of other students;
What the devil
15. Delete the record of SC table for learning "Jian Yan" teacher's lessons.
DELETE FROM SC S WHERE S.CNO IN (
SELECT C.CNO FROM COURSE C
JOIN TEACHER T ON T.TNO=C.TNO AND T.TNAME='zhanyan');
COMMIT;
DELETE FROM SC S WHERE S.SCORE='79.23';
SELECT * FROM SC S WHERE S.SCORE='79.23';
COMMIT;
insert into sc values ('s001','c001',78.9);
insert into sc values ('s002','c001',80.9);
insert into sc values ('s003','c001',81.9);
insert into sc values ('s004','c001',60.9);
insert into sc values ('s001','c002',82.9);
insert into sc values ('s002','c002',72.9);
insert into sc values ('s003','c002',81.9);
insert into sc values ('s001','c003',59);
insert into sc values ('s005','c003',59);
commit;
select * from sc;
select a.username, a.sid,b.SQL_TEXT, b.SQL_FULLTEXT
from v$session a, v$sqlarea b
where a.sql_address = b.address16. Insert some records into the SC table, which meet the following requirements: students who have not attended the course number "c002" and the average score of the course "c002";
INSERT INTO SC
SELECT * FROM
(SELECT DISTINCT ST.SNO FROM STUDENT ST
MINUS
SELECT S.SNO FROM SC S WHERE S.CNO='c002') STNO
LEFT JOIN (SELECT SC.CNO,AVG(SC.SCORE) FROM SC SC WHERE SC.CNO='c002' GROUP BY SC.CNO) SCC ON 1=1;
COMMIT;
INSERT INTO SC
SELECT STNO.SNO,'c002',(SELECT AVG(SC.SCORE) FROM SC SC WHERE SC.CNO='c002') FROM
(SELECT DISTINCT ST.SNO FROM STUDENT ST
MINUS
SELECT S.SNO FROM SC S WHERE S.CNO='c002') STNO
select distinct st.sno,sc.cno,(select avg(score)from sc where cno='c002')
from student st,sc
where not exists
(select * from sc where cno='c002' and sc.sno=st.sno) and sc.cno='c002';17. Search for the highest and lowest scores in all subjects: in the form of course ID, highest and lowest scores
SELECT SC.CNO AS CID,MAX(SC.SCORE) AS HS,MIN(SC.SCORE) AS LS FROM SC GROUP BY SC.CNO;18. Ranking by the percentage of the average grade of each subject from low to high and passing rate from high to low
SELECT SC.CNO, AVG(SC.SCORE),sum(case when score>=60 then 1 else 0 end)/count(*)
as jige FROM SC SC
GROUP BY SC.CNO
ORDER BY AVG(SC.SCORE),jige DESC;
select cno,avg(score),sum(case when score>=60 then 1 else 0 end)/count(*)
as jige
from sc group by cno
order by avg(score) , jige desc19. Query the average scores of different courses taught by different teachers from high to low
SELECT DISTINCT * FROM TEACHER T
JOIN COURSE C ON C.TNO=T.TNO
JOIN
(SELECT SC.CNO,AVG(SC.SCORE) FROM SC SC GROUP BY SC.CNO ORDER BY AVG(SC.SCORE) DESC) SCC ON SCC.CNO=C.CNO ;
select max(t.tno),max(t.tname),max(c.cno),max(c.cname),c.cno,avg(score) from sc , course c,teacher t
where sc.cno=c.cno and c.tno=t.tno
group by c.cno
order by avg(score) descStatistical Printing of Achievements and Number of Scores: Course ID, Course Name, [100-85], [85-70], [70-60], [< 60]
SELECT S.CNO,C.CNAME,
SUM(CASE WHEN SCORE BETWEEN 85 AND 100 THEN 1 ELSE 0 END) AS "85-100",
SUM(CASE WHEN SCORE BETWEEN 70 AND 85 THEN 1 ELSE 0 END) AS "70-85",
SUM(CASE WHEN SCORE BETWEEN 60 AND 70 THEN 1 ELSE 0 END) AS "60-70",
SUM(CASE WHEN SCORE<60 THEN 1 ELSE 0 END) AS "<60"
FROM SC S,COURSE C
WHERE S.CNO=C.CNO
GROUP BY S.CNO,C.CNAME;
select sc.cno,c.cname,
sum(case when score between 85 and 100 then 1 else 0 end) AS "[100-85]",
sum(case when score between 70 and 85 then 1 else 0 end) AS "[85-70]",
sum(case when score between 60 and 70 then 1 else 0 end) AS "[70-60]",
sum(case when score <60 then 1 else 0 end) AS "[<60]"
from sc, course c
where sc.cno=c.cno
group by sc.cno ,c.cname;21. Query the records of the top three grades in each subject: (without considering the juxtaposition of grades)
select * from (
SELECT SNO,CNO,SCORE,row_number()over(partition by cno order by score desc) as rn FROM SC) res WHERE res.rn<4;
select * from
(select sno,cno,score,row_number()over(partition by cno order by score desc) rn from sc)
where rn<422. Query the number of students selected for each course
SELECT COUNT(SNO),CNO FROM SC GROUP BY CNO;23. Find out the number and name of all the students who took only one course.
SELECT ST.SNO,ST.SNAME FROM STUDENT ST
WHERE ST.SNO IN (
SELECT S.SNO FROM SC S GROUP BY S.SNO HAVING COUNT(S.CNO)=1)
select sc.sno,st.sname from student st
join sc
on sc.sno=st.sno
group by st.sname,sc.sno having count(cno)=1;24. Number of boys and girls
SELECT SSEX,COUNT(SSEX) FROM STUDENT GROUP BY SSEX;25. Query the list of students surnamed Zhang
SELECT SNAME FROM STUDENT WHERE SNAME LIKE 'zhang%';26. Query the list of students with the same name and count the number of students with the same name
SELECT SNAME,COUNT(SNAME) FROM STUDENT GROUP BY SNAME HAVING COUNT(SNAME)>1;List of students born in 1981 and 27 (Note: The type of Sage column in the Student table is number)
SELECT SNO,SNAME,TO_CHAR(SYSDATE,'YYYY') FROM STUDENT WHERE TO_CHAR(SYSDATE,'YYYY')-SAGE=1994;
select sno,sname,sage,ssex from student t where to_char(sysdate,'yyyy')-sage =198828. Query the average scores of each course. The results are arranged in ascending order according to the average scores. When the average scores are the same, they are arranged in descending order according to the course number.
SELECT CNO,AVG(SCORE) FROM SC GROUP BY CNO ORDER BY AVG(SCORE),CNO DESC;29. Query the number, name and average score of all students whose average score is greater than 85
SELECT ST.SNO,ST.SNAME,AVG(S.SCORE) FROM STUDENT ST
JOIN SC S ON S.SNO=ST.SNO
GROUP BY ST.SNO,ST.SNAME,S.SNO HAVING AVG(S.SCORE)>80;
SELECT S.SNO,(SELECT ST.SNAME FROM STUDENT ST WHERE ST.SNO=S.SNO) AS NAME,AVG(S.SCORE) AS AVGS FROM SC S GROUP BY S.SNO HAVING AVG(S.SCORE)>80;30. Query the name of the course "database" and the names and scores of students with scores below 60
SELECT ST.SNAME,S.SCORE FROM STUDENT ST
JOIN SC S ON S.SNO=ST.SNO
AND S.SCORE<80
JOIN COURSE C ON C.CNO=S.CNO AND C.CNAME='Java Web';
select sname,score from student st,sc,course c
where st.sno=sc.sno and sc.cno=c.cno and c.cname='Java Web' and sc.score<8031. Inquire about all students'choices of courses;
SELECT ST.SNO,ST.SNAME,C.CNAME FROM STUDENT ST
JOIN SC S ON S.SNO=ST.SNO
JOIN COURSE C ON C.CNO=S.CNO;
select st.sno,st.sname,c.cname from student st,sc,course c
where sc.sno=st.sno and sc.cno=c.cno;32. Query the name, course name and score of any course with a score of more than 70.
SELECT ST.SNAME,C.CNAME,S.SCORE FROM STUDENT ST ,SC S,COURSE C
WHERE S.SCORE>70 AND S.CNO=C.CNO AND ST.SNO=S.SNO;
SELECT ST.SNAME,C.CNAME,S.SCORE FROM STUDENT ST
JOIN SC S ON S.SNO=ST.SNO
AND S.SCORE>70
JOIN COURSE C ON C.CNO=S.CNO;33. Query the failed courses and rank them according to the number of courses.
SELECT S.CNO,S.SCORE,C.CNAME FROM SC S,COURSE C WHERE S.SCORE<60 AND C.CNO=S.CNO ORDER BY S.CNO DESC;
SELECT S.CNO,S.SCORE,C.CNAME FROM SC S
JOIN COURSE C ON C.CNO=S.CNO AND S.SCORE<60
ORDER BY S.CNO DESC;34. Inquire the number and name of the student whose course number is c001 and the course score is above 80;
SELECT ST.SNO,ST.SNAME FROM STUDENT ST
JOIN SC S ON S.SNO=ST.SNO
AND S.SCORE>80 AND S.CNO='c001';
SELECT ST.SNO,ST.SNAME FROM STUDENT ST,SC S WHERE S.SNO=ST.SNO AND S.SCORE>80 AND S.CNO='c001';35. Number of students seeking courses
SELECT COUNT(DISTINCT S.SNO) FROM SC S;36. Inquire about the names and achievements of the students who have the highest scores in the courses offered by the "Jian Yan" teacher.
SELECT ST.SNAME,S.SCORE FROM STUDENT ST,SC S,COURSE C,TEACHER T
WHERE S.SNO=ST.SNO AND C.CNO=S.CNO AND T.TNO=C.TNO AND T.TNAME='zhanyan' AND
S.SCORE=(SELECT MAX(SCORE) FROM SC WHERE SC.CNO=C.CNO);
SELECT * FROM (SELECT ST.SNAME,MAX(S.SCORE) FROM STUDENT ST
JOIN SC S ON S.SNO=ST.SNO
JOIN COURSE C ON C.CNO=S.CNO
JOIN TEACHER T ON T.TNO=C.TNO
AND T.TNAME='zhanyan'
GROUP BY ST.SNAME,C.CNO ORDER BY MAX(S.SCORE) DESC) INFO WHERE ROWNUM=1;
select st.sname,score from student st,sc ,course c,teacher t
where st.sno=sc.sno and sc.cno=c.cno and c.tno=t.tno
and t.tname='zhanyan' and sc.score=
(select max(score)from sc where sc.cno=c.cno)37. Query the number of courses and corresponding Electives
SELECT C.CNAME,COUNT(*) FROM COURSE C,SC S
WHERE C.CNO=S.CNO GROUP BY C.CNAME;
select cno,count(sno) from sc group by cno;38. Query the number, course number and student achievement of students with the same results in different courses.
SELECT S1.* FROM SC S1
JOIN SC S2 ON S1.SCORE=S2.SCORE AND S1.CNO!=S2.CNO;
select a.* from sc a ,sc b where a.score=b.score and a.cno<>b.cno39. Find out the top two students with the best grades in each subject
SELECT * FROM (SELECT S.SNO,S.SCORE,ROW_NUMBER()OVER(PARTITION BY S.CNO ORDER BY S.SCORE DESC) AS RN FROM SC S) RES WHERE RES.RN<3;40. Statistics on the number of students taking each course (more than 10 courses are counted). The output of course number and elective number is required. The inquiry results are arranged in descending order according to the number of students. If the number of students is the same, they are arranged in ascending order according to the course number.
SELECT S.CNO,COUNT(S.CNO) FROM SC S
GROUP BY S.CNO HAVING COUNT(S.CNO)>3 ORDER BY COUNT(S.CNO) DESC,S.CNO;Retrieval of student numbers for at least two elective courses
SELECT S.SNO FROM SC S
GROUP BY S.SNO HAVING COUNT(S.CNO)>=2;42. Query the course number and course name of the course that all students take.
SELECT C.CNO,C.CNAME FROM COURSE C
WHERE C.CNO=(SELECT SX.CNO FROM (SELECT S.CNO,COUNT(S.SNO),ROW_NUMBER()OVER(ORDER BY COUNT(SNO) DESC) AS RN FROM SC S GROUP BY S.CNO) SX WHERE SX.RN=1);
select cno,cname from course c
where c.cno in
(select cno from sc group by cno)43. Search for the names of students who have not taken any of the courses taught by the "Jian Yan" teacher.
SELECT ST.SNAME FROM STUDENT ST
MINUS
SELECT ST.SNAME FROM STUDENT ST
JOIN SC S ON S.SNO=ST.SNO
JOIN COURSE C ON C.CNO=S.CNO
JOIN TEACHER T ON T.TNO=C.TNO AND T.TNAME='zhanyan';44. Query the number of students who have failed two or more courses and their average scores
SELECT ASNO.SNO,ASNO.AVGS FROM (SELECT S.SNO FROM SC S WHERE S.SCORE<60 GROUP BY S.SNO HAVING COUNT(S.SNO)>1) SSNO
JOIN (SELECT S.SNO,AVG(S.SCORE) AS AVGS FROM SC S GROUP BY S.SNO) ASNO ON SSNO.SNO=ASNO.SNO;
select sno,avg(score)from sc
where sno in
(select sno from sc where sc.score<60
group by sno having count(sno)>1
) group by sno;45. Search "c003" course scores less than 60, in descending order by the number of students
SELECT S.SNO FROM SC S WHERE S.SCORE<60 AND S.CNO='c003' ORDER BY S.SCORE DESC;46. Delete the grade of "c001" course for "s002" students
DELETE FROM SC WHERE SNO='s002' AND CNO='c001';
COMMIT;