sql练习

select distinct b.EmployeeID,count(*) countSub
from employees a,employees b 
where a.ReportsTo=b.EmployeeID
group by b.EmployeeID

 

select sname
from stu
where not exists(select * 
from cou
where  teacher = '张老师' and not exists
(select *
from sc
 where sc.sno=stu.sno and sc.cno=cou.cno
))

 

select university,count(question_id)/count(distinct question_practice_detail.device_id) 'avg_answer_cnt'
from  user_profile,question_practice_detail
where user_profile.device_id=question_practice_detail.device_id
group by university

 

 

update student
set avg_grade=(
    select avg(grade)
    from score
    where student.sno=score.sno
    group by sno
)