查询"01"课程比"02"课程成绩高的学生的信息及课程分数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','王五');

查询"01"课程比"02"课程成绩高的学生的信息及课程分数：

SELECT s_id, s_name, s_sex, s_birth, s_score
FROM student, score
WHERE student.s_id = score.s_id
AND score.c_id = '01'
AND score.s_score > (SELECT s_score FROM score WHERE c_id = '02' AND student.s_id = score.s_id);

相关问题

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;