查询"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','王五');
时间: 2023-03-29 15:04:05 浏览: 49
查询"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 if exists student ( id int PRIMARY KEY, name VARCHAR(20) );
好的,以下是创建名为 `student` 的表的 SQL 语句:
```sql
CREATE TABLE IF NOT EXISTS student (
id INT PRIMARY KEY,
name VARCHAR(20)
);
```
这个 SQL 语句将创建一个名为 `student` 的表,该表包含两列:`id` 和 `name`。`id` 列是整数类型,并被设置为主键(PRIMARY KEY),`name` 列是 VARCHAR 类型,最大长度为 20。
如果名为 `student` 的表不存在,则会创建该表。如果该表已经存在,则不会执行任何操作。
请注意,这只是 SQL 语句的示例,请根据你的实际需求进行适当修改。
相关推荐
![zip](https://img-home.csdnimg.cn/images/20210720083736.png)
![zip](https://img-home.csdnimg.cn/images/20210720083736.png)
![doc](https://img-home.csdnimg.cn/images/20210720083327.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)