查询"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;

希望以上查询语句能够对您有所帮助。需要注意的是，以上查询语句仅供参考，具体实现需要根据实际情况进行调整和修改。

mysql为数据库SchoolDB创建学生年级表Grade、信息表Student、课程表subject和成绩表Result；

在MySQL中，为了创建数据库名为"SchoolDB"的数据表，你需要分别执行以下SQL命令：

1. 学生年级表 Grade (假设年级字段包括grade_id, grade_name):

CREATE TABLE SchoolDB.Grade (
    grade_id INT PRIMARY KEY,
    grade_name VARCHAR(50) NOT NULL
);

2. 学生机息表 Student (可能包含student_id, student_name, grade_id等字段):

CREATE TABLE SchoolDB.Student (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(100),
    grade_id INT,
    FOREIGN KEY (grade_id) REFERENCES Grade(grade_id)
);

3. 课程表 Subject (假设包含subject_id, subject_name):

CREATE TABLE SchoolDB.Subject (
    subject_id INT PRIMARY KEY,
    subject_name VARCHAR(100)
);

4. 成绩表 Result (包含result_id, student_id, subject_id, score等字段):

CREATE TABLE SchoolDB.Result (
    result_id INT PRIMARY KEY,
    student_id INT,
    subject_id INT,
    score DECIMAL(3,1),
    FOREIGN KEY (student_id) REFERENCES Student(student_id),
    FOREIGN KEY (subject_id) REFERENCES Subject(subject_id)
);

这些建表语句会按照需求为"SchoolDB"数据库建立基础的表结构，以便存储学生、年级、课程以及成绩信息。