首页 > 数据库 >数据库SQL Server语言 练习题合集

数据库SQL Server语言 练习题合集

时间：2024-11-07 21:20:11浏览次数：3  
标签：练习题 sno Server course student SQL sc WHERE SELECT

文章目录

一、“小学生”题库

  1. 查询计算机系和文传系的女生姓名，性别，宿舍号

    SELECT sname, sex, ssh 
FROM student 
WHERE sdept IN ('计算机系', '文传系') AND sex='女';

  2. 查询住在 1 号楼 1 楼的所有男生名单及宿舍号

    SELECT sname, ssh 
FROM student 
WHERE student.ssh LIKE '11%' AND sex='男';

  3. 查询住 2 号楼 4 层的女生宿舍长学号，及宿舍号

    SELECT shuse.ssz, ssh 
FROM shuse 
WHERE sslb='女' AND LEFT(shuse.ssh, 2)='24';

  4. 查询选修了 1 号课或 3 号课学生的学号

    SELECT sno 
FROM sc 
WHERE cno IN ('1', '3');

  5. 查询房间号是 02 的宿舍号有哪些

    SELECT ssh 
FROM shuse 
WHERE shuse.ssh LIKE '__02%';

  6. 查询住在 2201 房间的学生名单及宿舍号

    SELECT sname, ssh 
FROM student 
WHERE student.ssh = '2201';

  7. 请查询职称是讲师的黄姓教师姓名及所在系和职称

    SELECT tname, tsdept, tglass 
FROM teacher 
WHERE tname LIKE '黄%' AND tglass = '讲师';

  8. 查询选修了 3 号课的学生的学号和成绩

    SELECT sno, grade 
FROM sc 
WHERE cno = '3';

  9. 请查询含有海字的教师姓名及所在系

    SELECT tname, tsdept 
FROM teacher 
WHERE tname LIKE '%海%';

  10. 查询 1 楼 2009 级的宿舍长的学号

    SELECT shuse.ssz 
FROM shuse 
WHERE shuse.ssh LIKE '1%' AND ssz LIKE '2009%';

  11. 查询 1989 年以前出生的学生学号和姓名和出生日期

    SELECT sno, sname, csrq 
FROM student 
WHERE YEAR(csrq) < 1989;

  12. 查询含有涛字的年龄大于 35 的副教授姓名及职称

    SELECT tname, tglass 
FROM teacher 
WHERE tname LIKE '%涛%' AND tglass = '副教授' AND 2011 - YEAR(tcsrq) > 35;

  13. 查询名字中最后一字为涛的非信息系老师姓名，及系

    SELECT tname, tsdept 
FROM teacher 
WHERE tname LIKE '%涛' AND tsdept NOT LIKE '信息系';

  14. 查询住 1 楼的所有李姓男生姓名，性别及宿舍号

    SELECT sname, sex, ssh 
FROM student 
WHERE sname LIKE '李%' AND sex = '男' AND student.ssh LIKE '_1%';

  15. 查询住二楼的女生学生分别是哪些系的

    SELECT sdept 
FROM student 
WHERE ssh LIKE '_2%' AND sex = '女';

  16. 请查询所有教授的年龄及所在系 (当前年 2011)

    SELECT tsdept, 2011 - YEAR(tcsrq) AS age 
FROM teacher 
WHERE tglass = '教授';

  17. 查询住在 1 号楼信息系的所有学生姓名，系，及宿舍号

    SELECT sname, sdept, ssh 
FROM student 
WHERE ssh LIKE '1%' AND sdept = '信息系';

  18. 请查询管理系高级职称(副教授以上)的教师有哪些

    SELECT * 
FROM teacher 
WHERE tglass IN ('副教授', '教授') AND tsdept = '管理系';

  19. 查询信息系和计算机系女生情况

    SELECT * 
FROM student 
WHERE sex = '女' AND sdept IN ('信息系', '计算机系');

  20. 查询 80 后的高级职称教师的姓名及职称

    SELECT tname, tglass 
FROM teacher 
WHERE tglass IN ('副教授', '教授') AND YEAR(tcsrq) >= 1980;

  21. 查询 2010 级宿舍长所住的宿舍号

    SELECT ssh 
FROM shuse 
WHERE ssz LIKE '2010%';

  22. 查询 1 号楼选修了数据库的同学名单

    SELECT sname 
FROM student, course, sc 
WHERE student.sno = sc.sno AND sc.cno = course.cno AND 
      student.ssh LIKE '1%' AND cname = '数据库';

  23. 查询李海龙老师所教的课程中成绩在 80 分以上的学生名单及成绩

    SELECT sname, grade 
FROM student, course, teacher, sc 
WHERE course.tno = teacher.tno AND student.sno = sc.sno AND 
      sc.sno = student.sno AND tname = '李海龙' AND grade > 80;

  24. 查询计算机系 2009 级男生所在的宿舍长姓名

    SELECT sname 
FROM shuse, student 
WHERE shuse.ssz = student.sno AND sdept = '计算机系' AND 
      ssz LIKE '2009%' AND sex = '男';

  25. 查询计算机系的女老师所带的课程名及学分

    SELECT cname, ccredit 
FROM course, teacher 
WHERE tsdept = '计算机系' AND tsex = '女' AND teacher.tno = course.tno;

  26. 查询当宿舍长的李姓同学的姓名

    SELECT sname 
FROM shuse, student 
WHERE shuse.ssz = student.sno AND sname LIKE '李%';

  27. 查询给计算机系上课的老师中副教授的姓名、职称、系及课程名

    SELECT DISTINCT tname, tglass, tsdept, cname 
FROM course, teacher 
WHERE teacher.tno = course.tno AND tglass = '副教授' AND tsdept = '计算机系';

  28. 查询封小文同学的所有任课老师名单

    SELECT DISTINCT tname 
FROM teacher, student, sc, course 
WHERE student.sno = sc.sno AND sc.cno = course.cno AND 
      course.tno = teacher.tno AND sname = '封小文';

  29. 查询所有职称是副教授的上课课程名及教师姓名

    SELECT cname, tname 
FROM teacher, course 
WHERE course.tno = teacher.tno AND tglass = '副教授';

  30. 查询选修了数据库课，成绩 80 分以上的计算机系学生姓名及任课教师名单

    SELECT sname, tname 
FROM teacher, student, course, sc 
WHERE sc.sno = student.sno AND sc.cno = course.cno AND 
      course.tno = teacher.tno AND grade > 80 AND 
      cname = '数据库' AND sdept = '计算机系';

  31. 查询 2 号楼 1 层和 1 号楼 3 层的同学都在哪个系

    SELECT DISTINCT sdept 
FROM student 
WHERE ssh LIKE '21%' OR ssh LIKE '13%';

  32. 查询 1 号楼 1 层计算机系选修数学的学生名单

    SELECT sname 
FROM student, sc, course 
WHERE student.sno = sc.sno AND sc.cno = course.cno AND 
      ssh LIKE '11%' AND cname = '数学' AND sdept = '计算机系';

  33. 查询 2011 级所有 2 号楼的宿舍长名单

    SELECT sname 
FROM student, shuse 
WHERE student.sno = shuse.ssz AND ssz LIKE '2011%' AND shuse.ssh LIKE '2%';

  34. 查询为计算机系学生上课的 3 个学分以上课程的任课教师姓名

    SELECT DISTINCT tname 
FROM course, teacher 
WHERE ccredit > 3 AND tsdept = '计算机系' AND course.tno = teacher.tno;

二、“硕士”题库

  1. 查询计算机系和文传系的女生姓名，性别，宿舍号

    SELECT sname, sex, ssh 
FROM student 
WHERE sdept IN ('计算机系', '文传系') AND sex = '女';

  2. 查询住在 1 号楼 1 层的所有男生名单及宿舍号（宿舍号第 1 位为楼号，第 2 位为楼层号，最后两位为房间号）

    SELECT sname, ssh 
FROM student 
WHERE student.ssh LIKE '11%' AND sex = '男';

  3. 查询住 2 号楼 4 层的女生宿舍长学号，及宿舍号（宿舍号第 1 位为楼号，第 2 位为楼层号，最后两位为房间号）

    SELECT shuse.ssz, ssh 
FROM shuse 
WHERE sslb = '女' AND LEFT(shuse.ssh, 2) = '24';

  4. 查询选修了 1 号课或 3 号课学生的学号

    SELECT sno 
FROM sc 
WHERE cno IN ('1', '3');

  5. 查询房间号是 02 的宿舍号有哪些？（宿舍号第 1 位为楼号，第 2 位为楼层号，最后两位为房间号）

    SELECT ssh 
FROM shuse 
WHERE shuse.ssh LIKE '__02%';

  6. 查询住在 2201 房间的学生名单及宿舍号

    SELECT sname, ssh 
FROM student 
WHERE student.ssh = '2201';

  7. 请查询职称是讲师的黄姓教师姓名及所在系和职称

    SELECT tname, tsdept, tglass 
FROM teacher 
WHERE tname LIKE '黄%' AND tglass = '讲师';

  8. 查询选修了 3 号课的学生的学号和成绩

    SELECT sno, grade 
FROM sc 
WHERE cno = '3';

  9. 请查询含有海字的教师姓名及所在系

    SELECT tname, tsdept 
FROM teacher 
WHERE tname LIKE '%海%';

  10. 查询 1 楼 2009 级的宿舍长的学号

    SELECT shuse.ssz 
FROM shuse 
WHERE shuse.ssh LIKE '1%' AND ssz LIKE '2009%';

  11. 查询 1989 年以前出生的学生学号和姓名和出生日期（提示请用 year(csrq)函数来取出生年号）

    SELECT sno, sname, csrq 
FROM student 
WHERE YEAR(csrq) < 1989;

  12. 请查询含有涛字的年龄大于 35 的副教授姓名及职称（设当前年为 2011）

    SELECT tname, tglass 
FROM teacher 
WHERE tname LIKE '%涛%' AND tglass = '副教授' AND 2011 - YEAR(tcsrq) > 35;

  13. 请查询名字中最后一字为涛的非信息系老师姓名，及系。

    SELECT tname, tsdept 
FROM teacher 
WHERE tname LIKE '%涛' AND tsdept NOT LIKE '信息系';

  14. 查询住 1 楼的所有李姓男生姓名，性别及宿舍号（宿舍号第 1 位为楼号，第 2 位为楼层号，最后两位为房间号）

    SELECT sname, sex, ssh 
FROM student 
WHERE sname LIKE '李%' AND sex = '男' AND student.ssh LIKE '_1%';

  15. 查询住二楼的女生学生分别是哪些系的（宿舍号第 1 位为楼号，第 2 位为楼层号，最后两位为房间号）

    SELECT sdept 
FROM student 
WHERE ssh LIKE '_2%' AND sex = '女';

  16. 查询所有教授的年龄及所在系(当前年 2011)

    SELECT tsdept, 2011 - YEAR(tcsrq) AS age 
FROM teacher 
WHERE tglass = '教授';

  17. 查询住在 1 号楼信息系的所有学生姓名，系，及宿舍号（宿舍号第 1 位为楼号，第 2 位为楼层号，最后两位为房间号）

    SELECT sname, sdept, ssh 
FROM student 
WHERE ssh LIKE '1%' AND sdept = '信息系';

  18. 请查询管理系高级职称(副教授以上)的教师有哪些,把情况显示出来

    SELECT * 
FROM teacher 
WHERE tglass IN ('副教授', '教授') AND tsdept = '管理系';

  19. 查询信息系和计算机系女生情况

    SELECT * 
FROM student 
WHERE sex = '女' AND sdept IN ('信息系', '计算机系');

  20. 查询 80 后的高级职称教师的姓名及职称

    SELECT tname, tglass 
FROM teacher 
WHERE tglass IN ('副教授', '教授') AND YEAR(tcsrq) >= 1980;

  21. 查询 2010 级宿舍长所住的宿舍号

    SELECT ssh 
FROM shuse 
WHERE ssz LIKE '2010%';

  22. 查询 1 号楼选修了数据库的同学名单

    SELECT sname 
FROM student, course, sc 
WHERE student.sno = sc.sno AND sc.cno = course.cno AND student.ssh LIKE '1%' AND cname = '数据库';

  23. 查询 1 号楼各层的房间数及应住的人数并用楼层，房间数，人数表示出来

    SELECT COUNT(*), SUM(cws), MID(ssh, 2, 1) 
FROM shuse 
WHERE LEFT(ssh, 1) = '1' AND '' <> '楼层房间数人数' 
GROUP BY MID(ssh, 2, 1);

  24. 查询住了奇数个学生的宿舍号（假设一个宿舍最多安排 4 个人）

    SELECT ssh 
FROM student 
WHERE ssh 
GROUP BY ssh 
HAVING COUNT(ssh) = 1 OR COUNT(ssh) = 3;

  25. 查询平均成绩大于 80 分的同学姓名

    SELECT sname 
FROM student 
WHERE sno IN (SELECT sno FROM sc GROUP BY sno HAVING AVG(grade) > 80);

  26. 查询 1 号楼 1 层选修课超过 2 门课的同学姓名及选修的课程名

    SELECT sname 
FROM student, sc, course 
WHERE student.sno = sc.sno AND sc.cno = course.cno AND ssh LIKE '11%' 
GROUP BY sname HAVING COUNT(cname) > 2;

  27. 查询住在 1 号楼的所有宿舍长中选了李海龙老师课的学生名单

    SELECT sname 
FROM student, sc, course, teacher, shuse 
WHERE student.sno = sc.sno AND course.cno = sc.cno AND teacher.tno = course.tno 
AND shuse.ssz = student.sno AND shuse.ssh LIKE '1%' AND tname = '李海龙';

  28. 查询李海龙老师所教的与其在同一系的学生人数

    SELECT COUNT(*) AS rs 
FROM sc, course, student 
WHERE course.cno = sc.cno AND student.sno = sc.sno AND tno = (SELECT tno FROM teacher WHERE tname = '李海龙') 
AND sdept = (SELECT tsdept FROM teacher WHERE tname = '李海龙');

  29. 查询 2 号楼平均成绩 85 分以上的宿舍长姓名及平均成绩

    SELECT sname, AVG(grade) 
FROM sc, student, shuse 
WHERE shuse.ssz = student.sno AND sc.sno = student.sno AND shuse.ssh LIKE '2%' 
GROUP BY sname HAVING AVG(grade) >= 85;

  30. 查询还没有安排学生住人的宿舍号显示出来

    SELECT ssh 
FROM shuse 
WHERE ssh NOT IN (SELECT ssh FROM student);

  31. 查询选 1 号课的女生姓名，课程号及成绩，并按课程号升序，成绩降序排序

    SELECT sname, course.cno, grade 
FROM course, sc, student 
WHERE student.sno = sc.sno AND sc.cno = course.cno AND sex = '女' 
AND course.cno = '1' ORDER BY course.cno ASC, sc.grade DESC;

  32. 查询李海龙教师教了不同系学生的系的个数

    SELECT COUNT(sdept) AS rs 
FROM sc, teacher, course, student 
WHERE course.cno = sc.cno AND student.sno = sc.sno AND course.tno = teacher.tno 
AND teacher.tno IN (SELECT tno FROM teacher WHERE tname = '李海龙');

  33. 查询与李勇一个系的没选课的学生人数

    SELECT COUNT(*) 
FROM student 
WHERE sno NOT IN (SELECT sno FROM sc) AND sdept IN (SELECT sdept FROM student WHERE sname = '李勇');

  34. 查询范冰倩教师所教的班中的平均成绩

    SELECT AVG(grade) AS cj 
FROM sc, course 
WHERE course.cno = sc.cno AND tno = (SELECT tno FROM teacher WHERE tname = '范冰倩');

  35. 查询封小文所在系的所有教师名单

    SELECT tname 
FROM teacher 
WHERE tsdept IN (SELECT sdept FROM student WHERE sname = '封小文');

  36. 查询男生宿舍长中选课 2 门及以上的的学生名单及课程数

    SELECT sname 
FROM student, sc, course, shuse 
WHERE course.cno = sc.cno AND student.sno = sc.sno AND student.sno = shuse.ssz 
AND sslb = '男' GROUP BY sname HAVING COUNT(cname) >= 2;

  37. 查询选了李海龙教师课程的所有学生的平均成绩，最低，最高成绩。

    SELECT AVG(grade) AS pj, MIN(grade) AS zd, MAX(grade) AS zg 
FROM sc, course, teacher 
WHERE course.cno = sc.cno AND teacher.tno = course.tno AND tname = '李海龙';

  38. 查询只给管理系学生上过课的计算机系教师名单

    SELECT DISTINCT tname 
FROM sc, teacher, course, student 
WHERE course.cno = sc.cno AND student.sno = sc.sno AND course.tno = teacher.tno 
AND tsdept = '计算机系' AND sdept = '管理系';

  39. 查询 1 号楼各层各系的人数（注分用楼层及系人数表示出来）

    SELECT MID(ssh, 2, 1) AS 楼层, sdept, COUNT(*) 
FROM student 
WHERE ssh LIKE '1%' 
GROUP BY MID(ssh, 2, 1), sdept;

  40. 查询与李勇同一宿舍的其它同学的姓名及选修课程名

    SELECT sname, cname 
FROM student, sc, course 
WHERE sc.cno = course.cno AND student.sno = sc.sno 
AND ssh IN (SELECT ssh FROM student WHERE sname = '李勇') 
AND sname <> '李勇';

  41. 查询按年级（如 2009，2010）分别统计男生宿舍长的个数，并用年级及宿舍长个数表示出来（不含空宿舍）

    SELECT COUNT(*) AS 宿舍长个数, LEFT(ssz, 4) AS 年级 
FROM shuse 
WHERE sslb = '男' 
GROUP BY LEFT(ssz, 4);

  42. 查询所有选修过课的学生的姓名，课程名及成绩

    SELECT sname, cname, grade 
FROM student, sc, course 
WHERE student.sno = sc.sno AND sc.cno = course.cno;

三、“博士”题库

  1. 请将 student1 表中魏玲铃同学的姓名改为魏玲玲，并将其系改为计算机系

    UPDATE student1 SET sname='魏玲玲', sdept='计算机系' WHERE sname='魏玲铃';

  2. 将 student 表文传系的 1987 年前出生的女生信息插入到 student1 表中

    INSERT INTO student1 SELECT * 
FROM student 
WHERE YEAR(csrq) < 1987 AND sex='女' AND sdept='文传系';

  3. 将 student1 表中比文传系平均年龄都小的计算机系学生删除

    DELETE FROM student1 WHERE sdept='计算机系' AND (2010 - YEAR(csrq)) < 
(SELECT AVG(2010 - YEAR(csrq)) FROM student1 WHERE sdept = '文传系');

  4. 将计算机系年龄小于 20 岁的同学从 student 表插入到 student1 表中 (当前年 2010)

    INSERT INTO student1 SELECT * FROM student WHERE (2010 - YEAR(csrq)) < 20 AND sdept='计算机系';

  5. 请将所有选了数据库的成绩低于 70 分的同学的数据库课程成绩加上 5 分

    UPDATE sc SET grade = grade + 5 
WHERE sno IN (SELECT student.sno FROM student, course, sc 
WHERE student.sno = sc.sno AND sc.cno = course.cno AND cname = '数据库' AND grade < 70);

  6. 删除 STUDENT 表中学号那些没选过课的学生记录

    DELETE FROM student1 WHERE sno NOT IN (SELECT DISTINCT sno FROM sc);

  7. 请将 student 表中林颖颖同学的姓名改为林倩颖，并将其系改为文传系

    UPDATE student1 SET sname='林倩颖', sdept='文传系' WHERE sname='林颖颖';

  8. 插入住在男生宿舍的文传系学生的宿舍信息

    INSERT INTO shuse(ssh, sslb, ssz) 
SELECT RIGHT(sno, 3), sex, sno 
FROM student 
WHERE sex = '男' AND sdept = '文传系';

  9. 将 student1 表中平均成绩小于 80 分的计算机系同学的系改为文传系

    UPDATE student1 SET sdept = '文传系' 
WHERE sno IN (SELECT sc.sno FROM student1, sc 
WHERE student1.sno = sc.sno AND sdept = '计算机系' 
GROUP BY sc.sno HAVING AVG(grade) < 80);

  10. 将 sc 表中重复选了同一门课的学生重复记录删除

    DELETE FROM sc 
WHERE sno IN (SELECT sno FROM sc GROUP BY sno, cno HAVING COUNT(*) > 1);

  11. 将选修了刚好 3 门课程的同学从 sc 表插入到 sc1 表中

    INSERT INTO sc1 SELECT * FROM sc 
WHERE sno IN (SELECT sno FROM sc GROUP BY sno HAVING COUNT(*) = 3);

  12. 将和李勇同学一个系的职称是副教授的老师的职称改为教授

    UPDATE teacher SET tglass = '教授' 
WHERE tdept IN (SELECT sdept FROM student WHERE sname = '李勇') 
AND tglass = '副教授';

  13. 更新选修英语系课程的学生的成绩

    UPDATE sc1 SET grade = grade + 10 
WHERE sno IN (SELECT sno FROM student WHERE sname LIKE '___' AND sdept = '英语系');

  14. 将 student1 表中所有计算机系女生的系别改为信息系

    UPDATE student1 SET sdept = '信息系' 
WHERE sdept = '计算机系' AND sex = '女';

  15. 将 sc 表中文传系所有成绩小于 80 分的选课记录插入到 sc1 表中，并且成绩置为空

    INSERT INTO sc1(sno, cno) 
SELECT sc.sno, cno 
FROM student, sc 
WHERE student.sno = sc.sno 
AND sdept = '文传系' AND grade < 80;

  16. 将 sc1 表中管理系内个人平均成绩比本系总平均成绩低的同学每门课均加 10 分

    UPDATE sc1 SET grade = grade + 10 
WHERE sno IN (SELECT sc.sno FROM student, sc 
WHERE student.sno = sc.sno AND sdept = '管理系' 
GROUP BY sc.sno HAVING AVG(grade) < (SELECT AVG(grade) 
FROM student, sc WHERE student.sno = sc.sno AND sdept = '管理系'));

  17. 删除 student1 表中选课数超过 5 记录

    DELETE FROM student1 WHERE sno IN (SELECT sno FROM sc GROUP BY sno HAVING COUNT(*) > 5);

  18. 删除 student1 表中平均成绩低于 75 分的同学的记录

    DELETE FROM student1 WHERE sno IN (SELECT sno FROM sc GROUP BY sno HAVING AVG(grade) < 75);

  19. 将 sc1 表中计算机系年龄最小的同学的各科成绩加 5 分 (当前年 2010)

    UPDATE sc1 SET grade = grade + 5 
WHERE sno IN (SELECT sno FROM student WHERE sdept = '计算机系' 
AND (2010 - YEAR(csrq)) = (SELECT MIN(2010 - YEAR(csrq)) FROM student WHERE sdept = '计算机系'));

  20. 删除 student1 表中平均成绩低于 80 分的同学的记录

    DELETE FROM student1 WHERE sno IN (SELECT sno FROM sc GROUP BY sno HAVING AVG(grade) < 80);

  21. 从 SC 表中将个人平均成绩大于 80 的选课记录插入到 sc1 表中

    INSERT INTO sc1 SELECT * 
FROM sc 
WHERE sno IN (SELECT sno FROM sc GROUP BY sno HAVING AVG(grade) > 80);

四、“博士后”题库

  1. 查询所有选过课的但没选过张涛老师课的学生姓名

    SELECT sname 
FROM student 
WHERE sno NOT IN (SELECT sc.sno 
                   FROM sc, course, teacher 
                   WHERE sc.cno = course.cno 
                   AND course.tno = teacher.tno 
                   AND tname = '张涛');

  2. 查询没有住满的学生宿舍号显示出来

    SELECT ssh 
FROM shuse 
WHERE ssh IN (SELECT ssh FROM shuse WHERE ssz = '') 
OR ssh IN (SELECT shuse.ssh 
           FROM student, shuse 
           WHERE student.ssh = shuse.ssh 
           GROUP BY shuse.ssh, cws 
           HAVING cws > COUNT(*));

  3. 查询李海龙老师所教的课中成绩比本门课平均成绩低的学生名单

    SELECT sname, grade 
FROM student, sc, course, teacher 
WHERE course.cno = sc.cno 
AND student.sno = sc.sno 
AND course.tno = teacher.tno 
AND tname = '李海龙' 
AND grade < (SELECT AVG(grade) 
             FROM sc, course, teacher 
             WHERE sc.cno = course.cno 
             AND course.tno = teacher.tno 
             AND tname = '李海龙');

  4. 查询比李勇年龄大一倍及以上的任课教师姓名（当前年 2011）

    SELECT tname, 2011 - YEAR(tcsrq) 
FROM teacher 
WHERE (2011 - YEAR(tcsrq)) / (SELECT 2011 - YEAR(csrq) 
                              FROM student 
                              WHERE sname = '李勇') >= 2;

  5. 查询 1 号楼 4 层的房间数及应住的人数和实际住的人数，并用房间数，应住人数，实住人数表示出来

    SELECT COUNT(ssh) AS 房间数, SUM(cws) AS 应住人数, 
       (SELECT COUNT(*) FROM student WHERE ssh LIKE '14%') AS 实住人数 
FROM shuse 
WHERE ssh LIKE '14%';

  6. 查询李海龙教师班上学分总数超过 10 个的同学名单及学分数

    SELECT sname, SUM(ccredit) 
FROM student, sc, course 
WHERE student.sno = sc.sno 
AND sc.cno = course.cno 
AND student.sno IN (SELECT sno 
                     FROM sc, course, teacher 
                     WHERE course.cno = sc.cno 
                     AND course.tno = teacher.tno 
                     AND tname = '李海龙') 
GROUP BY sname 
HAVING SUM(ccredit) > 10;

  7. 查询至少选了 2 门课并且平均成绩在 80 分以上的学生姓名及平均成绩

    SELECT sname, AVG(grade) 
FROM student, sc 
WHERE student.sno = sc.sno 
AND sc.sno IN (SELECT sc.sno 
               FROM student, sc 
               WHERE student.sno = sc.sno 
               GROUP BY sc.sno 
               HAVING COUNT(sc.sno) >= 2) 
GROUP BY sname 
HAVING AVG(grade) > 80;

  8. 查询计算机系没有选李海龙教师的课的学生姓名

    SELECT sname 
FROM student 
WHERE sno NOT IN (SELECT sc.sno 
                   FROM sc, course, teacher 
                   WHERE sc.cno = course.cno 
                   AND course.tno = teacher.tno 
                   AND tname = '李海龙') 
AND sdept = '计算机系';

  9. 查询李海龙老师所教的课程的成绩比张涛老师所教课程的平均成绩高的学生姓名，成绩

    SELECT sname, grade 
FROM student, sc, course, teacher 
WHERE course.cno = sc.cno 
AND student.sno = sc.sno 
AND course.tno = teacher.tno 
AND tname = '李海龙' 
AND grade > (SELECT AVG(grade) 
             FROM sc, course, teacher 
             WHERE sc.cno = course.cno 
             AND course.tno = teacher.tno 
             AND tname = '张涛');

  10. 查询选的课程数均比李明多的同学姓名

    SELECT sname 
FROM student 
WHERE sno IN (SELECT sno 
              FROM sc 
              GROUP BY sno 
              HAVING COUNT(*) > (SELECT COUNT(*) 
                                 FROM sc, student 
                                 WHERE student.sno = sc.sno 
                                 AND sname = '李明'));

  11. 查询各年龄段所选的学分数（如 20 岁学分总数多少，21 岁学分总数多少…设当前年 2010)

    SELECT 2010 - YEAR(csrq), SUM(ccredit) AS 学分总数
FROM sc, student, course 
WHERE sc.sno = student.sno 
AND sc.cno = course.cno 
GROUP BY (2010 - YEAR(csrq));

  12. 查询与张立同学选修的课程数量一样的其它同学姓名及课程数量

    SELECT sname, COUNT(cno) 
FROM student, sc 
WHERE student.sno = sc.sno 
AND sname <> '张立' 
GROUP BY sname 
HAVING COUNT(cno) = (SELECT COUNT(cno) 
                     FROM student, sc 
                     WHERE student.sno = sc.sno 
                     AND sname = '张立');

  13. 查询所有副教授的姓名及所教的课程的人数和平均成绩

    SELECT tname, COUNT(*), AVG(grade) 
FROM teacher, sc, course 
WHERE teacher.tno = course.tno 
AND sc.cno = course.cno 
AND tglass = '副教授' 
GROUP BY tname;

  14. 查询一门课也没选过的学生名单

    SELECT sname 
FROM student 
WHERE sno NOT IN (SELECT sc.sno 
                  FROM sc, course 
                  WHERE sc.cno = course.cno);

  15. 查询同时选修了 1 号课和 3 号课的同学的姓名及系

    SELECT sname, sdept 
FROM student 
WHERE sno IN (SELECT sno 
              FROM sc 
              WHERE sno IN (SELECT sno 
                            FROM sc 
                            WHERE sno IN (SELECT sno 
                                          FROM sc 
                                          WHERE cno = '1') 
                            AND cno = '3'));

  16. 查询王名和王名明两位同学各自的平均成绩

    SELECT sc.sno, AVG(grade) AS 平均成绩
FROM sc, student 
WHERE student.sno = sc.sno 
AND sname IN ('王名', '王名明') 
GROUP BY sc.sno;

  17. 查询李海龙老师所教的班中年龄最小的学生姓名和出生日期

    SELECT sname, csrq 
FROM student 
WHERE csrq = (SELECT MAX(csrq) 
              FROM student, sc, course, teacher 
              WHERE sc.cno = course.cno 
              AND course.tno = teacher.tno 
              AND student.sno = sc.sno 
              AND tname = '李海龙');

  18. 查询王名和王名明两位同学各自所修的学分总数

    SELECT sc.sno, SUM(ccredit) AS 学分总数
FROM sc, student, course 
WHERE sc.sno = student.sno 
AND sc.cno = course.cno 
AND sname IN ('王名', '王名明') 
GROUP BY sc.sno;

  19. 查询计算机系所有学生学的学分总数比文传系所有学生学的学分总数多多少

    SELECT SUM(ccredit) - (SELECT SUM(ccredit) 
                        FROM student, sc, course 
                        WHERE sc.cno = course.cno 
                        AND student.sno = sc.sno 
                        AND sdept = '文传系') 
FROM student, sc, course 
WHERE sc.cno = course.cno 
AND student.sno = sc.sno 
AND sdept = '计算机系';

  20. 查询个人各门课程平均成绩低于张涛教师课程平均成绩的非张涛老师班上所有学生姓名，平均成绩

    SELECT sname, AVG(grade) 
FROM student, sc 
WHERE student.sno = sc.sno 
AND sc.sno NOT IN (SELECT sno 
               FROM sc, course, teacher 
               WHERE course.cno = sc.cno 
               AND course.tno = teacher.tno 
               AND tname = '张涛') 
               GROUP BY sname 
               HAVING AVG(grade) > (SELECT AVG(grade) 
                 FROM sc, course, teacher 
                 WHERE sc.cno = course.cno 
                 AND course.tno = teacher.tno 
                 AND tname = '张涛');

五、“博导”题库

  1. 查询计算机系那些选了数据库课成绩比计算机系整个数据库课平均成绩低的同学的所学学分总和

    SELECT SUM(ccredit) 
FROM course, sc 
WHERE course.cno = sc.cno 
AND sc.sno IN (
    SELECT sc.sno 
    FROM student, sc, course 
    WHERE student.sno = sc.sno 
    AND sdept = '计算机系' 
    AND course.cno = sc.cno 
    AND cname = '数据库' 
    AND grade < (
        SELECT AVG(grade) 
        FROM student, sc, course 
        WHERE student.sno = sc.sno 
        AND sdept = '计算机系' 
        AND course.cno = sc.cno 
        AND cname = '数据库'
    )
);

  2. 查询选修学分总数最多的系比最低的系的学分差额是多少

    SELECT SUM(ccredit) - (
    SELECT SUM(ccredit) 
    FROM sc, student, course 
    WHERE sc.sno = student.sno 
    AND sc.cno = course.cno 
    GROUP BY sdept 
    HAVING SUM(ccredit) <= ALL (
        SELECT SUM(ccredit) 
        FROM sc, student, course 
        WHERE sc.cno = course.cno 
        AND sc.sno = student.sno 
        GROUP BY sdept
    )
) 
FROM sc, course, student 
WHERE sc.cno = course.cno 
AND sc.sno = student.sno 
GROUP BY sdept 
HAVING SUM(ccredit) >= ALL (
    SELECT SUM(ccredit) 
    FROM sc, student, course 
    WHERE sc.sno = student.sno 
    AND sc.cno = course.cno 
    GROUP BY sdept
);

  3. 查询选修的课程中平均成绩超过 80 分并且选的人数超过 3 个人的, 选修的系不包含信息系的课程名：

    SELECT cname 
FROM student, sc, course 
WHERE student.sno = sc.sno 
AND sc.cno = course.cno 
AND sdept <> '信息系' 
GROUP BY cname 
HAVING COUNT(*) > 3 
AND AVG(grade) > 80;

  4. 查询所有生日相同的任课教师姓名及生日

    SELECT tname, tcsrq 
FROM teacher 
WHERE STR(MONTH(tcsrq)) + STR(DAY(tcsrq)) IN (
    SELECT STR(MONTH(tcsrq)) + STR(DAY(tcsrq)) 
    FROM teacher 
    GROUP BY STR(MONTH(tcsrq)) + STR(DAY(tcsrq)) 
    HAVING COUNT(*) >= 2
) 
ORDER BY STR(MONTH(tcsrq)) + STR(DAY(tcsrq));

  5. 查询管理系个人选课平均成绩大于 80 分的人数有多少个

    SELECT COUNT(*) AS 人数 
FROM (
    SELECT sc.cno 
    FROM student, sc 
    WHERE student.sno = sc.sno 
    AND sdept = '管理系' 
    GROUP BY sc.cno 
    HAVING AVG(grade) > 80
);

  6. 查询管理系选了数据库课程的成绩最高的同学比计算机系选了数据库课程成绩最高的同学差多少分

    SELECT MAX(grade) - (
    SELECT MAX(grade) 
    FROM student, sc, course 
    WHERE student.sno = sc.sno 
    AND sc.cno = course.cno 
    AND sdept = '计算机系' 
    AND cname = '数据库'
) 
FROM student, sc, course 
WHERE student.sno = sc.sno 
AND sc.cno = course.cno 
AND sdept = '管理系' 
AND cname = '数据库';

  7. 查询没有选过课人数最多的那个系的没选课同学的平均年龄

    SELECT AVG(2010 - YEAR(csrq)) 
FROM student 
WHERE sno NOT IN (
    SELECT sno 
    FROM sc
) 
AND sdept IN (
    SELECT sdept 
    FROM student 
    WHERE sno NOT IN (
        SELECT sno 
        FROM sc
    ) 
    GROUP BY sdept 
    HAVING COUNT(*) >= ALL (
        SELECT COUNT(*) 
        FROM student 
        WHERE sno NOT IN (
            SELECT sno 
            FROM sc
        ) 
        GROUP BY sdept
    )
);

  8. 查询选修了李海龙老师课程的年龄比李海龙老师小 15 岁的学生姓名及年龄（当前年 2011）

    SELECT sname, 2011 - YEAR(csrq) 
FROM teacher, sc, course, student 
WHERE teacher.tno = course.tno 
AND sc.cno = course.cno 
AND student.sno = sc.sno 
AND tname = '李海龙' 
AND (2011 - YEAR(csrq) + 15) < (
    SELECT (2011 - YEAR(csrq)) 
    FROM teacher 
    WHERE tname = '李海龙'
);

  9. 查询女生楼还可以再安排几个学生居住（即查询空的床位数）

    SELECT (
    SELECT SUM(cws) 
    FROM shuse 
    WHERE sslb = '女'
) - COUNT(*) 
FROM student 
WHERE sex = '女' 
AND ssh <> '';

  10. 查询李勇选的课的数量比王名选课数量多多少

SELECT COUNT(*) - (
    SELECT COUNT(*) 
    FROM student 
    WHERE sname = '王名'
) AS 相差选课数 
FROM student, sc, course 
WHERE student.sno = sc.sno 
AND sc.cno = course.cno 
AND sname = '李勇';

六、“校长”题库

  1. 在 SC1 表中求出每个同学的学号及选课数量（要求要滤掉重复选的课程）

    SELECT sno, COUNT(*) 
FROM (SELECT DISTINCT sno, cno FROM sc1) 
GROUP BY sno;

  2. 请在 SC1 表中找出那些重复选课的同学学号和课程号和重复次数

    SELECT sno, cno, COUNT(*) 
FROM sc1 
GROUP BY sno, cno;

  3. 查询管理系个人选课平均成绩大于 80 分的人数有多少个

    SELECT COUNT(*) 
FROM sc, student 
WHERE sc.sno = student.sno 
AND sdept = '管理系' 
GROUP BY sdept 
HAVING AVG(grade) > 80;

  4. 查询管理系男生平均成绩比文传系男生的平均成绩高的人数有多少？

    SELECT COUNT(*) AS 相差人数 
FROM (
    SELECT sc.sno 
    FROM student, sc 
    WHERE student.sno = sc.sno 
    AND sdept = '管理系' 
    AND sex = '男' 
    GROUP BY sc.sno 
    HAVING AVG(grade) > (
        SELECT AVG(grade) 
        FROM student, sc 
        WHERE student.sno = sc.sno 
        AND sdept = '文传系' 
        AND sex = '男'
    )
);

  5. 查询和王名同学选的课一样的其它同学名单(包含王名同学的课程即可)

    SELECT sname 
FROM student 
WHERE sno IN (
    SELECT DISTINCT sno 
    FROM sc scx 
    WHERE NOT EXISTS (
        SELECT * 
        FROM sc scy 
        WHERE scy.sno IN (
            SELECT student.sno 
            FROM student 
            WHERE sname = '王名'
        ) 
        AND NOT EXISTS (
            SELECT * 
            FROM sc scz 
            WHERE scz.sno = scx.sno 
            AND scz.cno = scy.cno
        )
    )
) 
AND sname <> '王名';

七、“院士”题库

  1. 查询选修的课程既包含了王名，又包含了刘晨两人选修的课程的同学的姓名和课程名

    SELECT sname, cname 
FROM student, sc, course 
WHERE student.sno = sc.sno 
AND sc.cno = course.cno 
AND sc.sno IN (
    SELECT DISTINCT sno 
    FROM sc scx 
    WHERE NOT EXISTS (
        SELECT * 
        FROM sc scy 
        WHERE scy.sno IN (
            SELECT student.sno 
            FROM student 
            WHERE sname IN ('王名', '刘晨')
        ) 
        AND NOT EXISTS (
            SELECT * 
            FROM sc scz 
            WHERE scz.sno = scx.sno 
            AND scz.cno = scy.cno
        )
    )
);

  2. 统计一下和王名选的课程一样的学生人数（不含王名）

    SELECT count(*) 
FROM (
    SELECT sname 
    FROM student 
    WHERE sno IN (
        SELECT DISTINCT sno 
        FROM SC SCX 
        WHERE NOT EXISTS (
            SELECT * 
            FROM sc scy 
            WHERE scy.sno IN (
                SELECT student.sno 
                FROM student 
                WHERE sname='王名'
            ) 
            AND NOT EXISTS (
                SELECT * 
                FROM sc scz 
                WHERE scz.sno = scx.sno 
                AND scz.cno = scy.cno
            )
        )
    ) 
    AND sname <> '王名'
);

  3. 选了管理系学生没选过的全部选修课的其它的系名（也就是这个系的学生选了管理系学生所有没选过的选修课）

    SELECT sdept 
FROM student 
WHERE sno IN (
    SELECT sno 
    FROM student 
    WHERE NOT EXISTS (
        SELECT * 
        FROM sc scx 
        WHERE cno NOT IN (
            SELECT cno 
            FROM student, sc scz 
            WHERE student.sno = scz.sno 
            AND sdept = '管理系'
        ) 
        AND NOT EXISTS (
            SELECT * 
            FROM sc scy 
            WHERE scx.cno = scy.cno 
            AND student.sno = scy.sno
        )
    )
);

  4. 查询在 SC1 表中某学生重复选修课程的重复次数、重复学分总数、多选的学分数

    SELECT count(*), sum(ccredit) - sum(ccredit) / count(*) 
FROM sc, course 
WHERE sc.cno = course.cno 
GROUP BY sc.sno, sc.cno 
HAVING count(*) >= 2;

  5. 查询管理系个人选课平均成绩比计算机系总平均成绩多 5 分的同学姓名及平均成绩

    SELECT sname, avg(grade) 
FROM student, sc, course 
WHERE student.sno = sc.sno 
AND sc.cno = course.cno 
AND sdept = '管理系' 
GROUP BY sname 
HAVING (avg(grade) - (
        SELECT avg(grade) 
        FROM student, sc, course 
        WHERE student.sno = sc.sno 
        AND sc.cno = course.cno 
        AND sdept = '计算机系'
    )) > 5;

总结

这些查询语句涉及多个数据库操作，主要用于处理学生、课程、教师等多表数据，以下是对这些查询的总结：

1. 多表联接查询：

  • 很多查询依赖于将 student, sc, course, teacher 等表联合起来，通过适当的联接条件（如 sno, cno, tno）提取所需的数据。
  • 例如，查询选修课程的学生姓名、成绩等，往往需要通过 sc 表（选课表）与 student（学生表）及 course（课程表）进行联合。

2. 嵌套查询（子查询）：

  • 很多查询涉及嵌套查询，如查询选课数量、学生成绩、选修课程等，这些子查询通常用于过滤某些特定条件下的数据。例如，查询与特定学生选课相同的其他学生，或是查询某课程的成绩大于某个学生的平均成绩。
  • 嵌套查询也用于计算某些数据的平均值、总和等，比如在查询计算机系学生的学分总数时，需要嵌套查询计算总学分。

3. 去重与计数：

  • 使用 DISTINCT 关键字去除重复数据，尤其在统计选修课程数量时，使用了 DISTINCT sno, cno 来确保每门课程只计算一次。
  • 查询重复选课的同学时，通过 GROUP BY 和 HAVING 子句，计算某学生重复选修课程的次数。

4. 条件筛选与分组：

  • GROUP BY 用于将数据按某一字段分组，如按学生分组统计选课数、成绩等。
  • 通过 HAVING 子句过滤组数据，特别是根据条件筛选出某些学生或课程。
  • 使用 NOT EXISTS 过滤条件，以避免重复或不符合条件的数据，例如查询与王名选修相同课程的学生，排除了王名自己。

5. 比较与计算：

  • 很多查询比较不同系别或不同学生的成绩或选修课程数量。例如，查询管理系学生的平均成绩与计算机系学生的平均成绩差异，或比较王名与其他学生选课数量的差异。
  • 对成绩等字段进行算术运算，如计算课程的学分总和、平均成绩等，进一步进行条件筛选和分组。

6. 实际应用：

  • 这些查询可用于实际的学生管理系统中，帮助管理员或教师获取关于学生选课、成绩等的信息。
  • 特别是一些关于学分、课程选择、成绩分析的查询，有助于了解不同系、不同教师、不同学生的表现，进而为教学和管理决策提供数据支持。

总结：

这些 SQL 查询展示了如何使用多个表的联接、子查询、去重、计数、分组等技术来分析学生选课情况、成绩、学分等信息。这些查询能够帮助我们从数据中提取出有价值的信息，并为进一步的分析或决策提供依据。

标签：练习题,sno,Server,course,student,SQL,sc,WHERE,SELECT
From： https://blog.csdn.net/lylybb_/article/details/143607480

相关文章

  • 安装MySQL：从新手到专家的第一步
    引言在现代应用开发中，MySQL是最广泛使用的关系型数据库之一。无论是用于小型网站的后台支持，还是大型企业级应用数据管理，掌握MySQL的安装与配置都是每个开发者的必备技能。然而，对于初学者来说，安装MySQL的过程有时会遇到各种坑。本文将带你逐步了解如何在不同操作系统上安装MySQ......
  • MySQL基本命令：新手必备的数据库操作技巧
    引言MySQL是全球最流行的开源关系型数据库管理系统之一，广泛应用于各种应用项目中。对于新手来说，掌握MySQL的基本命令是非常重要的，能够帮助你高效进行数据管理和操作。这篇文章将介绍MySQL的一些基本命令，指出新手容易踩的坑，并为你提供实用的操作示例，让你在使用MySQL时更加得心......
  • sql练习专场-02
    题目1：这道题是查询出至少连续出现3次的数字createtablesql1_5(  idint,  numint)rowformatdelimitedfieldsterminatedby'';  INSERTINTOsql1_5(id,num)VALUES(1,1),(2,1),(3,1),(4,2),(5,1),(6,2),(7,2);sql如下：witht1as......
  • SparkSQL
    一、概述1.1、什么是sparksql  SparkSQL是Spark中用于处理结构化数据的模块，它提供了两个主要的编程抽象：DataFrame和DataSet，并且还可以作为分布式SQL查询引擎使用。SparkSQL的设计目的是简化结构化数据的处理和查询，同时提高执行效率。  传统的HiveSQL通过......
  • 免费送源码：python+Django+MySQL Django实验室管理系统 计算机毕业设计原创定制
    摘 要本论文主要论述了如何使用python语言、Django框架开发一个实验室管理系统，本系统将严格按照软件开发流程，进行各个阶段的工作，面向对象编程思想进行项目开发。在引言中，作者将论述该系统的当前背景以及系统开发的目的，后续章节将严格按照软件开发流程，对系统进行各个阶段分......
  • repmgr搭建一主+一从+一witness的PostgreSQL高可用集群
    测试环境环境如下：OS：ubuntu20PostgreSQL：PostgreSQL16.4repmgr:repmgr5.3.3三台测试环境，均已安装好PostgreSQL16.4，数据库实例信息如下IP         postgresql实例名  身份192.168.152.100：postgresql9000：  主节点192.168.152.101：postgresql9000：  从节点192.16......
  • 20. 使用MySQL之更新和删除数据
    1.更新数据为了更新（修改）表中的数据，可使用UPDATE语句。可采用两种方式使用UPDATE：更新表中特定行；更新表中所有行。注意：不要省略WHERE子句：在使用UPDATE时一定要注意细心。因为稍不注意，就会更新表中所有行。UPDATE与安全：可以限制和控制UPDATE语句的使用，更多内容请......
  • MySQL高级语言（第一篇）
    1.SQL高级语言是什么？SQL（StructuredQueryLanguage）本身是一种用于管理和操作关系数据库的标准编程语言，它并不是传统意义上的“高级语言”如C++、Java或Python等。然而，在数据库管理和开发的语境中，人们有时会提到“SQL高级语言”或“SQL的高级特性”，这通常指的是SQL中那些更为复......
  • mysql常见的一些配置项
    MySQL有许多配置选项，可以用来调整其行为以满足特定的需求。以下是一些常见的配置选项，除了大小写敏感之外，这些配置选项也经常被调整：1.字符集和排序规则character_set_server:设置服务器的默认字符集。collation_server:设置服务器的默认排序规则。character_set_client......
  • C# SqlSugar学习笔记
    前言今天介绍一款C#ORM框架什么是ORM？来看一下百度术语：对象关系映射（英语：ObjectRelationalMapping，简称ORM，或O/RM，或O/Rmapping），是一种程序技术，用于实现面向对象编程语言里不同类型系统的数据之间的转换 通俗理解ORM我们只需要知道ORM是一种技术，使用了ORM之后我们就不必在......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99