Question

java数据库题 急急急

Answer 1

select a.sno from
(select * from score where con=1) a join (select * from score where con=2) b on a.sno=b.sno
where a.score > b.score;
select a.sno, sname, ke, total from student a left join (select sno, count(sno) as ke, sum(score) as total from score group by sno) b on a.sno = b.sno;
select a.sno, sname from student a left join (select sno, count(*) co from score group by sno) b on a.sno = b.sno where co is null or co < 3;

Answer 2

-- 查询课程分数比课程分数高的所有学生编号
SELECT T1.SNO
FROM (SELECT * FROM SCORE A WHERE A.CNO = 1) T1
INNER JOIN (SELECT * FROM SCORE B WHERE B.CNO = 2) T2
ON T1.SNO = T2.SNO
WHERE T1.SCORE > T2.SCORE ;
-- 查询所有学生的学号，姓名，选修课数量，总成绩
SELECT A.SNO, A.SNAME, COUNT(B.CNO), SUM(B.SCORE)
FROM STUDENT A, SCORE B
WHERE A.SNO = B.SNO
GROUP BY A.SNO, A.SNAME;
-- 查询没有学完所有课程的学生学号、姓名
SELECT A.SNO, A.SNAME
FROM STUDENT A
WHERE A.SNO IN (SELECT B.SNO
FROM SCORE B
GROUP BY B.SNO
HAVING COUNT(1) > (SELECT COUNT(1) FROM COURSE))