参照学生-课程数据库,两人一组(队友出题,你给出SQL语句)完成下列任务: (1)利用系统内置函数完成.查询学生表中所有学生的姓名和年龄,并将年龄显示转换为出生日期。
时间: 2024-02-24 10:57:42 浏览: 33
假设学生表的表名为`student`,包含`name`和`age`两个字段,可以使用MySQL内置函数`DATE_SUB()`来将年龄转换为出生日期,如下所示的SQL语句可以实现该查询:
```
SELECT name, DATE_SUB(NOW(), INTERVAL age YEAR) AS birthdate FROM student;
```
其中`NOW()`函数返回当前日期和时间,`INTERVAL`关键字用于指定时间间隔,这里使用`age`字段来指定时间间隔的年份,最终使用`DATE_SUB()`函数将当前日期减去指定的年份,得到出生日期。
相关问题
参照学生-课程数据库,两人一组(队友出题,你给出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;
```
参照学生-课程数据库,两人一组(队友出题,你给出SQL语句)完成下列任务: (4)创建(INSETEAD OF)触发器 ,出五道题
1. 创建一个INSTEAD OF触发器,当向学生表中插入一条记录时,自动向选课表中插入一条对应的记录。
```
CREATE TRIGGER insert_student_trigger
INSTEAD OF INSERT ON student
FOR EACH ROW
BEGIN
INSERT INTO enrollment (student_id, course_id) VALUES (NEW.student_id, NEW.course_id);
END;
```
2. 创建一个INSTEAD OF触发器,当向选课表中插入一条记录时,自动向成绩表中插入一条对应的记录。
```
CREATE TRIGGER insert_enrollment_trigger
INSTEAD OF INSERT ON enrollment
FOR EACH ROW
BEGIN
INSERT INTO grade (student_id, course_id, grade) VALUES (NEW.student_id, NEW.course_id, NULL);
END;
```
3. 创建一个INSTEAD OF触发器,当向成绩表中插入一条记录时,自动更新学生表中该学生的平均成绩。
```
CREATE TRIGGER insert_grade_trigger
INSTEAD OF INSERT ON grade
FOR EACH ROW
BEGIN
UPDATE student SET avg_grade = (SELECT AVG(grade) FROM grade WHERE student_id = NEW.student_id) WHERE student_id = NEW.student_id;
END;
```
4. 创建一个INSTEAD OF触发器,当从学生表中删除一条记录时,自动从选课表和成绩表中删除对应的记录。
```
CREATE TRIGGER delete_student_trigger
INSTEAD OF DELETE ON student
FOR EACH ROW
BEGIN
DELETE FROM enrollment WHERE student_id = OLD.student_id;
DELETE FROM grade WHERE student_id = OLD.student_id;
END;
```
5. 创建一个INSTEAD OF触发器,当从选课表中删除一条记录时,自动从成绩表中删除对应的记录。
```
CREATE TRIGGER delete_enrollment_trigger
INSTEAD OF DELETE ON enrollment
FOR EACH ROW
BEGIN
DELETE FROM grade WHERE student_id = OLD.student_id AND course_id = OLD.course_id;
END;
```