tea_stu_score_course

文章转载自 https://blog.csdn.net/tu451953337/article/details/45147757

数据库表4张 ：

　　1,学生表

　　create table t_student(

　　　　stuid number(10,2) primary key,

　　　　name varchar2(100), 

　　　　age number(3,0), sex char(1)

　　); 

　　

　　2，课程表

　　create table t_course(

　　　　cid number(10,2) primary key, 

　　　　name varchar2(100), 

　　　　teacherid number(10, 2)

　　);

　　

　　3，分数表

　　create table t_sc(

　　　　stuid number(10, 2), 

　　　　cid number(10,2), 

　　　　score number(10,2)

　　);  

　　

　　4，教师表

　　create table t_teacher(

　　　　tid number(10,2) primary key,

　　　　tname varchar2(100)

　　); 

　　

 

sql 编写

--面试记忆 查询男生数目>1的班级

SELECT    
    student_class , COUNT(sex)
FROM 
    student WHERE sex='男' 
GROUP BY 
    student_class 
HAVING 
    COUNT(sex) > 1

--面试记忆 查询男生数目>1的班级
SELECT 
    DISTINCT stu1.student_class 
FROM
    student stu1
INNER JOIN 
    (SELECT 
        student_class, COUNT(*) AS man_count 
    FROM 
        student stu2 
    WHERE 
        sex = '男' 
    GROUP BY 
        student_class
    ) AS stu2
ON 
    stu1.student_class = stu2.student_class
WHERE 
    stu2.man_count > 1

--1、查询“1”课程比“2”课程成绩高的所有学生的学号  
select 
　　a.stuid  
from 
　　(select * from t_sc s where s.cid = 1) a,  
    (select * from t_sc s where s.cid = 2) b  
where
 　　a.stuid = b.stuid  
and 
　　a.score > b.score;  

--2、查询平均成绩大于80分的同学的学号和平均成绩；  
select 
　　stuid, avg(score) 
from 
　　t_sc 
group by 
　　stuid 
having 
　　avg(score)>80;  

 

--3、查询所有同学的学号、姓名、选课数、总成绩；  
select 
　　s.stuid, s.name, c.xks, c.zcj  
from 
　　t_student s 
left outer join  
    (select 
　　 　　stuid, count(cid) xks, sum(score) zcj  
     from 
　　　　　t_sc  
     group by 
　　　　　stuid
　　 ) c  
on 
　　s.stuid = c.stuid;  

--4、查询姓“李”的老师的个数；  
select 
    count(tid) 
from 
    t_teacher 
where 
    tname like '李%';  

--5、查询没学过“叶平”老师课的同学的学号、姓名；  
select 
    s.stuid,s.name 
from
    t_student s, t_course c, t_teacher t, t_sc sc 
where 
    s.stuid=sc.stuid 
and 
    c.cid=sc.cid 
and 
    c.teacherid=t.tid 
and 
    t.tname!='叶平'; 

--6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；  
select 
    s.stuid,s.name 
from 
    t_student s, t_sc c 
where 
    c.cid=1 
and 
    s.stuid=c.stuid 
and exists 
    (select 
        * 
    from 
        t_sc sc 
    where 
        sc.stuid=s.stuid 
    and
        sc.cid=2
    );        

--7、查询学过“叶平”老师所教的所有课的同学的学号、姓名；  
select 
    s.stuid,s.name 
from 
    t_student s, t_sc sc 
where 
    s.stuid = sc.stuid 
and 
    sc.cid in(  
                select 
                    c.cid 
                from 
                    t_teacher t, t_course c 
                where 
                    t.tid=c.teacherid 
                and 
                    t.tname='Mr.kay'
                );

--9、查询所有课程成绩小于60分的同学的学号、姓名；  
select 
    s.stuid, s.name 
from 
    t_student s 
where 
    s.stuid not in (
    select 
        sc.stuid 
    from 
        t_student s, t_sc sc 
    where 
        sc.score>=60 
    and 
        s.stuid=sc.stuid
    );     

--10、查询没有学全所有课的同学的学号、姓名;  
select 
    s.stuid, s.name 
from 
    t_student s,t_sc sc 
where 
    s.stuid=sc.stuid 
group by 
    s.stuid,s.name 
having 
    count(sc.cid) != (select count(cid) from t_course); 

--11、查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名；  
select 
    distinct s.stuid, s.name 
from 
    t_student s, t_sc sc 
where 
    s.stuid=sc.stuid 
and 
    s.stuid!=1 
and  
    sc.cid in (select cid from t_sc where stuid=1) 

--13、把“t_SC”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩；  
update 
    t_sc sc1 
set 
    sc1.score =   
    (select 
        avg(score) 
    from 
        t_sc 
    group by 
        cid 
    having 
        cid in (select 
                    cid 
                 from 
                     t_teacher t, t_course c 
                 where
                     t.tid=c.teacherid 
                 and 
                     t.tname='Mr.mao') 
    and 
        cid=sc1.cid
    );  

--14、查询和“1002”号的同学学习的课程完全相同的其他同学学号和姓名；  
select 
    * 
from 
    t_sc where stuid in(  
    select 
        stuid  
    from 
        t_sc  
    group by 
        stuid  
    having 
        count(*) = (select count(*) from t_sc where stuid = 6)  
    )  
and 
    cid in (select cid from t_sc where stuid=6);  

--15、删除学习“叶平”老师课的SC表记录  
delete 
    t_sc 
where 
    cid in(  
    select 
        cid 
    from 
        t_course c, t_teacher t 
    where 
        c.teacherid=t.tid 
    and 
        t.tname='Mr.mao'
    );