东华软件股份有限公司 2018笔试题

 

 

 

 

 

 

 

 

 1、delete from score where score <60;

 2、select count(*) from teacher where tname like '李%';

 3、select row_number() over(order by s.score desc) rn,
(select sname from student st where st.sno=s.sno) sname,score
 from score s ,course c where s.cno=c.cno
and c.cname='数学';

4、select cno,max(score) 最高分,min(score) 最低分
  from score s group by cno  ;

5、select * from score where score<=60-5;

 

6、select cno,cname from course  c 
where not exists( select 1 from score s 
         where c.cno=s.cno);

 

7.select  t1.sno,t1.sname,count(*) as ct,

              sum(t2.score) as zongfen

from student t1 left join score t2

on t1.sno = t2.sno

group by t1.sno,t1.sname 

 

8： select sno,sname
    from score ,student 
    where sno in (select sno 
                  from score t 
                  where t.cno='011')
         and cno ='002'
         and score.sno = student.sno

 

9.select t1.sno,t1.sname

from student t1 inner join score t2

on t1.sno=t2.sno

group by t1.sno,t1.sname

having max(t2.score)<60

 

10、insert into score(sno,cno,score)
select sno,
(select cno from course c where c.cname='历史'),
80
from student s   

 

11、SELECT T1.sno,T1.SNAME,T2.AVG_SC,T3.AVG_SC,
T4.AVG_SC,T5.NUM,t5.savg FROM Student T1 
LEFT JOIN 
(SELECT T1.sno,T1.cno,T2.CNAME,AVG(SCORE) AS AVG_SC 
FROM SC T1 ,COURSE T2
WHERE T1.cno= T2.cno
GROUP BY T1.sno,T1.cno,T2.CNAME
)    T2 ON T1.sno = T2.sno    AND T2.CNAME = '数据库' 
LEFT JOIN
(SELECT T1.sno,T1.cno,T2.CNAME,AVG(SCORE) AS AVG_SC 
FROM SC T1 ,COURSE T2
WHERE T1.cno= T2.cno
GROUP BY T1.sno,T1.cno,T2.CNAME
)    T3 ON T1.sno = T3.sno    AND T3.CNAME = '企业管理'
LEFT JOIN
(SELECT T1.sno,T1.cno,T2.CNAME,AVG(SCORE) AS AVG_SC 
FROM SC T1 ,COURSE T2
WHERE T1.cno= T2.cno
GROUP BY T1.sno,T1.cno,T2.CNAME
)    T4 ON T1.sno = T3.sno    AND T3.CNAME = '英语'    
LEFT JOIN
(SELECT T1.sno,COUNT(*) AS NUM ,avg(score) savg
FROM SC T1 
GROUP BY T1.sno
)    T5 ON T1.sno = T3.sno