222222

 

1. 查询学号为"E21514014"的学生信息

SELECT * FROM students WHERE student_id = 'E21514014';

2. 查询姓名为"王”的同学信息

SELECT * FROM students WHERE name LIKE '%王%';

3. 查询"计算机学院”有多少名学生

SELECT COUNT(*) FROM students WHERE college = '计算机学院';

4. 查询"数学学院”所有的教授信息

SELECT * FROM teachers WHERE college = '数学学院' AND title = '教授';

5. 查询"李菲倩”同学学习的课程信息

SELECT s.student_id, s.name, c.course_id, c.course_name

FROM students s

JOIN enrollments e ON s.student_id = e.student_id

JOIN courses c ON e.course_id = c.course_id

WHERE s.name = '李菲倩';

6. 列出教师"张”所教授的"C语言程序设计”这门课的成绩单

SELECT s.student_id, s.name, c.course_name, e.score

FROM students s

JOIN enrollments e ON s.student_id = e.student_id

JOIN courses c ON e.course_id = c.course_id

JOIN teachers t ON c.teacher_id = t.teacher_id

WHERE t.name = '张' AND c.course_name = 'C语言程序设计'

ORDER BY e.score DESC;

7. 列出"于欣”老师所教授的所有课程的信息

SELECT c.course_name, c.course_id, c.course_type, c.credits, t.name

FROM courses c

JOIN teachers t ON c.teacher_id = t.teacher_id

WHERE t.name = '于欣';

8. 查询"崔涛华”同学已经获得的学分信息

SELECT s.name, c.course_id, c.course_name, c.credits

FROM students s

JOIN enrollments e ON s.student_id = e.student_id

JOIN courses c ON e.course_id = c.course_id

WHERE s.name = '崔涛华' AND e.score >= 60;

9. 计算"苗薇度”老师讲授的"软件测试”课程的平均分

SELECT c.course_name, t.name, AVG(e.score) AS average_score

FROM courses c

JOIN teachers t ON c.teacher_id = t.teacher_id

JOIN enrollments e ON c.course_id = e.course_id

WHERE t.name = '苗薇度' AND c.course_name = '软件测试'

GROUP BY c.course_name, t.name;

10. 查询"数据库原理”这门课前3个最高成绩

SELECT s.student_id, s.name, e.score

FROM students s

JOIN enrollments e ON s.student_id = e.student_id

JOIN courses c ON e.course_id = c.course_id

WHERE c.course_name = '数据库原理'

ORDER BY e.score DESC

LIMIT 3;