参照学生-课程数据库，两人一组（队友出题，你给出SQL语句）完成下列任务： (1)利用系统内置函数完成(出五道题并写出sql)

好的，以下是五道题目及对应的SQL语句。

1. 查询学生表中所有学生的姓名和年龄，并将年龄显示转换为出生日期。

SELECT name, DATE_SUB(NOW(), INTERVAL age YEAR) AS birthdate FROM student;

2. 查询选修了课程编号为1或2的学生的姓名和选课情况。

SELECT s.name, c.course_name, sc.score
FROM student AS s
INNER JOIN score AS sc ON s.id = sc.student_id
INNER JOIN course AS c ON sc.course_id = c.id
WHERE c.id IN (1, 2);

3. 查询所有选修了课程的学生的姓名和选课数目。

SELECT s.name, COUNT(sc.course_id) AS num_courses
FROM student AS s
LEFT JOIN score AS sc ON s.id = sc.student_id
GROUP BY s.id;

4. 查询选修了所有课程的学生的姓名和课程数目。

SELECT s.name, COUNT(sc.course_id) AS num_courses
FROM student AS s
LEFT JOIN score AS sc ON s.id = sc.student_id
WHERE NOT EXISTS (
    SELECT c.id FROM course AS c
    WHERE NOT EXISTS (
        SELECT sc2.course_id FROM score AS sc2
        WHERE sc2.student_id = s.id AND sc2.course_id = c.id
    )
)
GROUP BY s.id;

5. 查询平均成绩大于等于80分的课程名称和平均成绩。

SELECT c.course_name, AVG(sc.score) AS avg_score
FROM course AS c
INNER JOIN score AS sc ON c.id = sc.course_id
GROUP BY c.id
HAVING AVG(sc.score) >= 80;