查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩create table Student(s_id varchar(100),s_name varchar(200),s_birth date,s_sex varchar(100)); create table Course(c_id varchar(100),c_name varchar(200),t_id varchar(100)); insert into Course values('01','语文','02'); insert into Course values('02','数学','01'); insert into Course values('03','英语','03'); insert into student values('01','赵雷','1990-01-01','男'); insert into student values('02','钱电','1990-12-21','男'); insert into student values('03','孙风','1990-05-20','男'); insert into student values('04','李云','1990-08-06','男'); insert into student values('05','周梅','1991-12-01','女'); insert into student values('06','吴兰','1992-03-01','女'); insert into student values('07','郑竹','1989-07-01','女'); insert into student values('08','王菊','1990-01-20','女'); create table Teacher(t_id varchar(100),t_name varchar(100)); create table Score (s_id varchar(100), c_id varchar(100),s_score int); insert into score values('01','01',80); insert into score values('01','02',90); insert into score values('01','03',99); insert into score values('02','01',70); insert into score values('02','02',60); insert into score values('02','03',80); insert into score values('03','01',80); insert into score values('03','02',80); insert into score values('03','03',80); insert into score values('04','01',50); insert into score values('04','02',30); insert into score values('04','03',20); insert into score values('05','01',76); insert into score values('05','02',87); insert into teacher values('01','张三'); insert into teacher values('02','李四'); insert into teacher values('03','王五');
时间: 2023-03-29 15:04:15 浏览: 167
我可以回答这个问题。查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩的 SQL 语句如下:
SELECT s_id, s_name, AVG(s_score) AS avg_score
FROM student, score
WHERE student.s_id = score.s_id
GROUP BY student.s_id, student.s_name
HAVING AVG(s_score) >= 60;
相关问题
create table Course( C_ID varchar(10) primary key,-- 课程编号 C_Name varchar(20) not null unique-- 课程名称 ) comment '课程表'; create table SC( S_ID varchar(20),-- 学号 C_ID varchar(10),-- 课程编号 Score int check(Score>=0 and Score<=100)-- 成绩 ) comment '成绩表'; create table Student( S_ID varchar(20) primary key,-- 学生编号 S_Name varchar(20) not null,-- 姓名 S_Age int,-- 年龄 S_Sex enum('男','女') default '男'-- 性别 ) comment '学生表';
感谢提供数据表结构,以下是查询各个课程及相应的选修人数、课程表、学生表和成绩表的 SQL 查询语句:
查询各个课程及相应的选修人数:
```
SELECT C_Name, COUNT(*) AS Num
FROM Course
JOIN SC ON Course.C_ID = SC.C_ID
GROUP BY Course.C_ID;
```
查询课程表:
```
SELECT *
FROM Course;
```
查询学生表:
```
SELECT *
FROM Student;
```
查询成绩表:
```
SELECT *
FROM SC;
```
希望以上查询语句能够对您有所帮助。需要注意的是,以上查询语句仅供参考,具体实现需要根据实际情况进行调整和修改。
-- 创建学生表 CREATE TABLE students ( id INT PRIMARY KEY, name VARCHAR(50), age INT, gender VARCHAR(10) ); -- 创建课程表 CREATE TABLE courses ( id INT PRIMARY KEY, name VARCHAR(50), credits INT ); -- 创建成绩表 CREATE TABLE grades ( student_id INT, course_id INT, grade INT, PRIMARY KEY (student_id, course_id), FOREIGN KEY (student_id) REFERENCES students(id), FOREIGN KEY (course_id) REFERENCES courses(id) ); -- 创建教师表 CREATE TABLE teachers ( id INT PRIMARY KEY, name VARCHAR(50) ); -- 创建教师学生表 CREATE TABLE teachers_students ( id INT PRIMARY KEY, name VARCHAR(50) ); -- 创建教师课程表 CREATE TABLE teachers_courses ( id INT PRIMARY KEY, name VARCHAR(50) ); 查询学生表中的所有学生信息。 查询选修了课程名为 "Math" 的学生的姓名和成绩。 查询所有选修了至少一门课程的学生的学号和姓名。 查询没有选修任何课程的学生的姓名和年龄。 查询课程表中学分最高的课程名。 查询每个课程的平均成绩。 查询选修了课程数超过3门的学生的姓名和选修课程数。 查询每个教师所教授的学生人数。 查询成绩表中成绩为最高分的学生的姓名和课程名。 查询没有分配教师的课程名。
查询学生表中的所有学生信息:
```
SELECT * FROM students;
```
查询选修了课程名为 "Math" 的学生的姓名和成绩:
```
SELECT students.name, grades.grade FROM students INNER JOIN grades ON students.id = grades.student_id INNER JOIN courses ON courses.id = grades.course_id WHERE courses.name = "Math";
```
查询所有选修了至少一门课程的学生的学号和姓名:
```
SELECT students.id, students.name FROM students INNER JOIN grades ON students.id = grades.student_id;
```
查询没有选修任何课程的学生的姓名和年龄:
```
SELECT students.name, students.age FROM students LEFT JOIN grades ON students.id = grades.student_id WHERE grades.student_id IS NULL;
```
查询课程表中学分最高的课程名:
```
SELECT name FROM courses WHERE credits = (SELECT MAX(credits) FROM courses);
```
查询每个课程的平均成绩:
```
SELECT courses.name, AVG(grades.grade) FROM courses INNER JOIN grades ON courses.id = grades.course_id GROUP BY courses.name;
```
查询选修了课程数超过3门的学生的姓名和选修课程数:
```
SELECT students.name, COUNT(*) FROM students INNER JOIN grades ON students.id = grades.student_id GROUP BY students.id HAVING COUNT(*) > 3;
```
查询每个教师所教授的学生人数:
```
SELECT teachers.name, COUNT(*) FROM teachers INNER JOIN teachers_students ON teachers.id = teachers_students.id INNER JOIN grades ON grades.student_id = teachers_students.id GROUP BY teachers.name;
```
查询成绩表中成绩为最高分的学生的姓名和课程名:
```
SELECT students.name, courses.name FROM students INNER JOIN grades ON students.id = grades.student_id INNER JOIN courses ON courses.id = grades.course_id WHERE grades.grade = (SELECT MAX(grade) FROM grades);
```
查询没有分配教师的课程名:
```
SELECT courses.name FROM courses LEFT JOIN teachers_courses ON courses.id = teachers_courses.id WHERE teachers_courses.id IS NULL;
```
阅读全文