题目是:
用SQL语句完成下列查询:
(1)查询和张三同学选择了相同科目且该门科目成绩优于张三的同学姓名、选修课程及成绩数据库的建立语句都已经写好
请教各位大牛查询怎么写,万分感谢附:
数据库建立语句:
开始:
create table student(
Sno char(7) PRIMARY KEY,
Sname char(10) NOT NULL,
Ssex char(2),
Sage tinyint CHECK(Sage>16 AND Sage<40),
Sdept char(20)
)create table course(
Cno char(10) PRIMARY KEY,
CName char(20) NOT NULL,
Cteacher char(7)
)create table sc(
Sno char(7) NOT NULL,
Cno char(10) NOT NULL,
Grade tinyint check (Grade>=0 and Grade<=100),
PRIMARY KEY(Sno,Cno),
FOREIGN KEY(Sno) REFERENCES Student(Sno),
FOREIGN KEY(Cno) REFERENCES course(Cno)
)delete from student;
insert into student values (101,'张三','男',18,'计算机');
insert into student values (102,'李四','男',16,'数学');
insert into student values (103,'王玲','女',17,'中文');
insert into student values (105,'李飞','男',19,'计算机');
insert into student values (109,'赵四','女',18,'历史');
insert into student values (110,'李平','男',20,'化学');
delete from course;
insert into course values (203,'操作系统','程羽');
insert into course values (279,'高等数学','王备');
insert into course values (210,'现代文学','王丹');
insert into course values (243,'有机化学','沈同');
insert into course values (204,'数据结构','张青');delete from sc;
insert into sc values (101,203,82);
insert into sc values (105,203,59);
insert into sc values (102,279,90);
insert into sc values (101,279,88);
insert into sc values (105,279,82);
insert into sc values (110,279,68);
insert into sc values (109,210,72);
insert into sc values (103,210,90);
insert into sc values (110,243,92);
insert into sc values (101,204,85);
insert into sc values (105,204,91);
insert into sc values (102,204,56);终止
用SQL语句完成下列查询:
(1)查询和张三同学选择了相同科目且该门科目成绩优于张三的同学姓名、选修课程及成绩数据库的建立语句都已经写好
请教各位大牛查询怎么写,万分感谢附:
数据库建立语句:
开始:
create table student(
Sno char(7) PRIMARY KEY,
Sname char(10) NOT NULL,
Ssex char(2),
Sage tinyint CHECK(Sage>16 AND Sage<40),
Sdept char(20)
)create table course(
Cno char(10) PRIMARY KEY,
CName char(20) NOT NULL,
Cteacher char(7)
)create table sc(
Sno char(7) NOT NULL,
Cno char(10) NOT NULL,
Grade tinyint check (Grade>=0 and Grade<=100),
PRIMARY KEY(Sno,Cno),
FOREIGN KEY(Sno) REFERENCES Student(Sno),
FOREIGN KEY(Cno) REFERENCES course(Cno)
)delete from student;
insert into student values (101,'张三','男',18,'计算机');
insert into student values (102,'李四','男',16,'数学');
insert into student values (103,'王玲','女',17,'中文');
insert into student values (105,'李飞','男',19,'计算机');
insert into student values (109,'赵四','女',18,'历史');
insert into student values (110,'李平','男',20,'化学');
delete from course;
insert into course values (203,'操作系统','程羽');
insert into course values (279,'高等数学','王备');
insert into course values (210,'现代文学','王丹');
insert into course values (243,'有机化学','沈同');
insert into course values (204,'数据结构','张青');delete from sc;
insert into sc values (101,203,82);
insert into sc values (105,203,59);
insert into sc values (102,279,90);
insert into sc values (101,279,88);
insert into sc values (105,279,82);
insert into sc values (110,279,68);
insert into sc values (109,210,72);
insert into sc values (103,210,90);
insert into sc values (110,243,92);
insert into sc values (101,204,85);
insert into sc values (105,204,91);
insert into sc values (102,204,56);终止
解决方案 »
- 输出符合要求的字段
- (求助)升级到mysql5,无法分页
- 印象中,sqlserver还是oracle中的同步/复制,其中有一种方式可以指定延迟时间,比如指定3秒从服务器为完成操作就认为同步/复制失败了。MySQL有类似
- 在线等待!!!sqlserver2000中如何实现如下需求,请大虾们指教,多谢!!!
- mysqldump导出的数据恢复问题
- 帮我看看这个mysql存储过程哪里错了,急,分数现结,不够另开帖加分,谢谢
- access2000导入MSSQL中,表间关系怎么导入,急,在线等待
- 关于数据删除的问题。
- 求old_character_set_client这个变量的作用-------------------------------
- win7 64位系统,安装odbc-5.2.7-win64驱动 失败
- 【高分】PowerDesigner15如何设置MySql的自增列的起始值和步长?
- HTTP Status 500 - could not execute query
建议你列出你的表结构,并提供测试数据以及基于这些测试数据的所对应正确结果。
参考一下这个贴子的提问方式http://topic.csdn.net/u/20091130/20/8343ee6a-417c-4c2d-9415-fa46604a00cf.html
1. 你的 create table xxx .. 语句
2. 你的 insert into xxx ... 语句
3. 结果是什么样,(并给以简单的算法描述)
4. 你用的数据库名称和版本(经常有人在MS SQL server版问 MySQL)
这样想帮你的人可以直接搭建和你相同的环境,并在给出方案前进行测试,避免文字描述理解上的误差。
inner join student a on a.Sno = c.Sno
inner join course b on b.Cno = c.Cno
inner join (
select c.Cno as Cno,c.Grade as Grade from sc c
inner join student a on a.Sno = c.Sno
inner join course b on b.Cno = c.Cno
where a.Sname = '张三') as d on c.Cno = d.Cno
where c.Grade > d.Grade
FROM student a ,sc c,
(
SELECT c.Cno,c.Grade ,b.CName
FROM student a,course b ,sc c
WHERE
a.Sno=c.Sno
AND b.Cno=c.Cno
AND a.Sname='张三'
) d
WHERE
a.Sno=c.Sno
AND c.Cno=d.Cno
AND c.Grade>d.Grade;