精华内容
下载资源
问答
  • sql查询语句练习

    2012-03-28 20:58:09
    sql查询语句练习——主要是典型的SQL语句练习
  • SQL 查询语句练习

    2019-11-22 20:22:35
    SQL 查询语句练习 班级表batch: batchcode 班级编号 batchname 班级名 教师信息表teacherinfo: teachid 教师编号 teachname 教师名 教学计划表courseplan: studno 学生编号 courseid...

    SQL 查询语句练习

    班级表batch:

    batchcode 班级编号
    batchname 班级名

    教师信息表teacherinfo:

    teachid 教师编号
    teachname 教师名

    教学计划表courseplan:

    studno 学生编号
    courseid 课程编号
    teachid 教师编号
    coursedt 教学日期

    信息表course:

    courseid 课程编号
    coursename 课程名

    成绩表score:

    studno 学生编号
    courseid 课程编号
    grade 成绩

    学生表student:

    studno 学生编号
    studname 学生名
    batchcode 班级编号

    –1.显示全部学生的信息,按照学生编号的升序排列,对同一学号按照课程名称的字母顺序显示

    SELECT st.studno as 学生编号,st.studname as 学生姓名,co.coursename as 课程名称,sc.grade as 成绩
    FROM student st 
    LEFT JOIN score sc 
    ON (st.studno = sc.studno)
    RIGHT JOIN course co
    ON (co.courseid = sc.courseid)
    order by st.studno,co.coursename;
    

    –2.查询显示单科最高成绩

    SELECT MAX(sc.grade)
    FROM score sc
    GROUP BY sc.courseid;
    

    –3、查询显示学生课程及格还是不及格

    SELECT st.studname,co.coursename,
    CASE WHEN sc.grade>'60' THEN '及格' ELSE '不及格' END
    FROM student st
    LEFT JOIN score sc
    ON (st.studno = sc.studno)
    LEFT JOIN course co
    ON (co.courseid = sc.courseid)
    ORDER BY st.studno;
    

    –4、统计学生选科的数量

    SELECT c.coursename,COUNT(s.courseid)
    FROM course c
    LEFT JOIN score s
    ON (c.courseid = s.courseid)
    GROUP BY s.courseid,c.coursename;
    

    –5、查询单科成绩超过课程平均成绩的学生的信息,列出学生编号,学生姓名,课程名称和课程成绩

    SELECT st.studno,st.studname,co.coursename,sc.grade
    FROM student st
    LEFT JOIN score sc
    ON (st.studno = sc.studno)
    LEFT JOIN course co
    ON (sc.courseid = co.courseid)
    WHERE sc.grade >
          (
          SELECT AVG(sc2.grade)
          FROM score sc2
          WHERE sc.courseid = sc2.courseid
          GROUP BY sc2.courseid
          );
    

    –6、查询显示需要补考的学生的学生编号,学生姓名和课程名称

    SELECT st.studno,st.studname,co.coursename
    FROM student st
    LEFT JOIN score sc
    ON (st.studno = sc.studno)
    LEFT JOIN course co
    ON (sc.courseid = co.courseid)
    WHERE sc.grade <= 60;
    

    –7、统计各科成绩平均分,显示课程编号,课程名称,平均分

    SELECT co.courseid,co.coursename,ROUND(AVG(sc.grade),2)
    FROM course co
    LEFT JOIN score sc
    ON (co.courseid = sc.courseid)
    GROUP BY co.courseid,co.coursename
    ORDER BY co.courseid;
    

    –8、查询选修了java课程的学生信息

    SELECT *
    FROM student st
    LEFT JOIN score sc
    ON(st.studno = sc.studno)
    LEFT JOIN course co
    ON (sc.courseid = co.courseid)
    WHERE co.coursename = 'JAVA';
    

    –9、查询没有选修JAVA课程的学生信息

    SELECT *
    FROM student st
    LEFT JOIN score sc
    ON(st.studno=sc.studno)
    LEFT JOIN course co
    ON (sc.courseid=co.courseid)
    WHERE co.coursename != 'JAVA';
    

    –10、查询选修了教师李可课程的学生信息

    SELECT *
    FROM student st
    LEFT JOIN courseplan co
    ON (st.studno = co.studno)
    LEFT JOIN teacherinfo te
    ON (co.teachid = te.teachid)
    WHERE te.teachname = '李可';
    

    –11、查询同时选修了A01和A02这两门课的学生的上课安排,显示学生编号,学生姓名、班级编号、课程编号、授课教师、上课日期

    SELECT st.studno, st.studname, st.batchcode, co.courseid, te.teachname, co.coursedt
    FROM student st
    LEFT JOIN courseplan co
    ON (st.studno = co.studno)
    RIGHT JOIN teacherinfo te
    ON (co.teachid = te.teachid)
    WHERE (co.courseid = 'A01') AND (co.courseid = 'A02');
    

    –12、查询96571班都有哪些课程,在什么时间有哪位教师授课

    SELECT DISTINCT(ce.coursename), cn.coursedt, te.teachname
    FROM courseplan cn
    RIGHT JOIN student st
    ON (cn.studno = st.studno)
    RIGHT JOIN teacherinfo te
    ON (cn.teachid = te.teachid)
    RIGHT JOIN course ce
    ON (cn.courseid = ce.courseid)
    WHERE cn.studno = (
          SELECT st1.studno
          FROM student st1
          WHERE st1.batchcode = '96571' AND cn.studno = st1.studno);
    

    –13、查询周一不上课的班级

    SELECT DISTINCT(bh.batchname)
    FROM batch bh
    LEFT JOIN student st
    ON (bh.batchcode = st.batchcode)
    LEFT JOIN courseplan cn
    ON (st.studno = cn.studno)
    WHERE cn.coursedt != '周一';
    

    –14、查询周四上课的教师姓名

    SELECT DISTINCT(te.teachname)
    FROM teacherinfo te
    LEFT JOIN courseplan cn
    ON (te.teachid = cn.teachid)
    WHERE cn.coursedt = '周四';
    

    –15、查询A02课程的授课教师和上课时间

    SELECT distinct(te.teachname), cn.coursedt
    FROM teacherinfo te
    LEFT JOIN courseplan cn
    ON (te.teachid = cn.teachid)
    WHERE cn.courseid = 'A02';
    

    –16、统计各个科目不及格人数占这个科目考生人数的百分比

    SELECT c1.coursename, ROUND(NVL((temp1.t1_num/temp2.t2_num),0),4)*100||'%'
    FROM
    (SELECT s1.courseid AS t1_id, COUNT(s1.studno) AS t1_num
    FROM score s1
    WHERE s1.grade<60
    GROUP BY s1.courseid
    ORDER BY s1.courseid) temp1 
    LEFT JOIN
    (SELECT s1.courseid AS t2_id, COUNT(s1.studno) AS t2_num
    FROM score s1
    GROUP BY s1.courseid
    ORDER BY s1.courseid) temp2
    ON (temp1.t1_id = temp2.t2_id)
    RIGHT JOIN course c1
    ON (temp1.t1_id = c1.courseid);
    

    –17、统计所有不及格人数占考生总数的百分比

    SELECT ROUND((temp1.t1_num/temp2.t2_num),4)*100||'%'
    FROM
    (SELECT COUNT(s1.grade) AS t1_num
    FROM score s1
    LEFT JOIN course c1
    ON (s1.courseid = c1.courseid)
    WHERE s1.grade < 60) temp1,
    (SELECT COUNT(s2.grade) AS t2_num
    FROM score s2
    LEFT JOIN course c2
    ON (s2.courseid = c2.courseid)) temp2;
    

    –18、查询单科成绩在90分以上的学生是哪个班级的,授课教师是谁?

    SELECT st.studname, ba.batchname, te.teachname
    FROM score sc
    LEFT JOIN student st
    ON (sc.studno = st.studno)
    LEFT JOIN batch ba
    ON (st.batchcode = ba.batchcode)
    LEFT JOIN courseplan cn
    ON (cn.studno = st.studno)
    LEFT JOIN teacherinfo te
    ON (cn.teachid = te.teachid)
    WHERE sc.grade > 90;
    

    –19、查询工业工程班的授课教师都是谁?

    SELECT DISTINCT(t1.teachname)
    FROM teacherinfo t1
    LEFT JOIN courseplan c1
    ON (t1.teachid = c1.teachid)
    LEFT JOIN student s1
    ON (c1.studno = s1.studno)
    LEFT JOIN batch b1
    ON (s1.batchcode = b1.batchcode)
    WHERE b1.batchname LIKE '%工业工程%';
    

    –20、查询1068号学生在什么时间都有课?

    SELECT c1.coursedt
    FROM student s1
    LEFT JOIN courseplan c1
    ON (s1.studno = c1.studno)
    WHERE s1.studno = 1068;
    

    –21、查询哪些同学的考试成绩都在90分以上

    SELECT s1.studname
    FROM student s1
    LEFT JOIN score s2
    ON (s1.studno = s2.studno)
    LEFT JOIN courseplan c1
    ON (s2.studno = c1.studno)
    WHERE s2.grade > 90;
    

    –22、查询同时代课超过两门课程的教师

    SELECT tt.teachname
    FROM
         (SELECT COUNT(temp1.te1_id) AS te2_id
          FROM
                (SELECT (c1.teachid) AS te1_id
                 FROM teacherinfo t1
                 LEFT JOIN courseplan c1
                 ON (t1.teachid = c1.teachid)
                 GROUP BY c1.teachid
                 ORDER BY c1.teachid) temp1
          GROUP BY temp1.te1_id) temp2
    LEFT JOIN teacherinfo tt
    ON (temp2.te2_id = tt.teachid)
    WHERE temp2.te2_id > 1;
    

    –23、汇总各个学生考试成绩的总分,并排名次。显示学生编号,学生姓名,班级编号,总分

    SELECT st.studno, st.studname, st.batchcode, temp1.t_sum
    FROM student st
    LEFT JOIN 
    (SELECT s1.studno AS t_no, SUM(s1.grade) AS t_sum
    FROM score s1
    GROUP BY s1.studno
    ) temp1
    ON (st.studno = temp1.t_no)
    ORDER BY temp1.t_sum DESC,temp1.t_no ASC;
    
    展开全文
  • sql查询语句练习.rar

    2019-07-09 08:10:50
    sql查询语句练习
  • SQL查询语句练习

    2021-04-12 22:09:06
    基于数据表基于第一次分享的SQL语句练习 点击跳转第一次练习 SnumSnameSsexSageSphoneDnameS001王明男1986824571数学系S002李勇男2389454321物理系S003刘燕女21计算机系S004王萍女23电子系S005王佳男...

    基于数据表基于第一次分享的SQL语句联系

    Snum Sname Ssex Sage Sphone Dname
    S001 王明 19 86824571 数学系
    S002 李勇 23 89454321 物理系
    S003 刘燕 21 计算机系
    S004 王萍 23 电子系
    S005 王佳 24 13098765892 计算机系
    S006 赵婷 20 物理系

    学生表S

    Snum Cnum Score
    S001 C1 83
    S001 C2 89
    S001 C3 NULL
    S001 C4 85
    S001 C5 69
    S002 C3 78
    S002 C4 75
    S003 C1 85
    S004 C2 NULL
    S005 C1 92
    S005 C3 76

    选课表SC

    Cnum Cname Cfreq
    C1 数据库系统原理 4
    C2 C程序设计 4
    C3 计算机体系结构 3
    C4 自动控制原理 2
    C5 数据结构 4

    课程表C

    下面是题目的分享:

    1. 简单查询操作。

    1)求数学系学生的学号和姓名。

    2)求选修了课程的学生学号。

    3)求选修课程号为‘C1’的学生号和成绩,并要求对查询结果按成绩的降序排列,如果成绩相同按学号的升序排列。

    4)求选修课程号为‘C1’且成绩在80~90之间的学生学号和成绩,成绩乘以0.8输出。

    5)求数学系或计算机系姓王的学生的信息。

    6)求缺少了成绩的学生的学号和课程号。

    7)查询有选课记录的所有学生的学号,用DISTINCT限制结果中学号不重复。

    2. 连接查询操作。

    1)查询每个学生的情况以及他所选修的课程。

    2)求学生的学号、姓名、选修的课程及成绩。

    3)求选修课程号为‘C1’且成绩在90以上的学生学号、姓名和成绩。

    解题步骤如下,为个人代码,均成功测试过,仅供参考

    --(1)
    SELECT Snum,Sname FROM 学生 WHERE Dname = '数学系'
    
    --(2)
    SELECT Snum FROM 学生 WHERE Snum IN (SELECT Snum FROM 选课)
    
    --(3)
    SELECT Snum,Score FROM 选课 WHERE Cnum = 'C1' ORDER BY Score DESC, Snum;
    
    --(4)
    SELECT Snum,Score*0.8 as Score FROM 选课 WHERE Cnum = 'C1' AND Score Between 80 AND 90;
    
    --(5)
    SELECT Snum,Sname,Ssex,Sage,Sphone,Dname FROM 学生 
    WHERE Sname Like '王%' AND Dname IN ('数学系','计算机系')
    
    --(6)
    SELECT Snum,Cnum FROM 选课 WHERE Score IS NULL
    
    --(7)
    SELECT DISTINCT Snum FROM 选课
    
    --(1)
    SELECT 学生.Snum,学生.Sname,学生.Ssex,学生.Sage,学生.Sphone,学生.Dname,课程.Cname 
    FROM 学生 LEFT JOIN 选课 on 学生.Snum = 选课.Snum LEFT JOIN 课程 ON 课程.Cnum = 选课.Cnum
    
    --(2)
    SELECT 学生.Snum,学生.Sname,课程.Cname,选课.Score
    FROM 学生 join 选课 on 学生.Snum = 选课.Snum join 课程 ON 课程.Cnum = 选课.Cnum
    
    --(3)
    SELECT 学生.Snum,学生.Sname,选课.Score
    FROM 学生 join 选课 on 学生.Snum = 选课.Snum 
    WHERE 选课.Cnum = 'C1' AND 选课.Score>= 90
    
    展开全文

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 2,119
精华内容 847
关键字:

sql查询语句练习