學生表sid,sname,成績表cid,cname,學產生績表sid,cid,cscore,要求輸出特地考生最高成績的課程名稱,snamecscore
--1、建表SQL:
--學生表:
-- Createtable
createtable STUDENT
(
SID NUMBERnotnull,
SNAME NVARCHAR2(40)
)
tablespace CABLESCD
pctfree10
initrans1
maxtrans255
storage
(
initial64
minextents1
maxextentsunlimited
);
--成績表
-- Createtable
createtable SCORE
(
CID NUMBERnotnull,
CNAME NVARCHAR2(40)notnull
)
tablespace CABLESCD
pctfree10
initrans1
maxtrans255
storage
(
initial64
minextents1
maxextentsunlimited
);
--學產生績表
-- Createtable
createtableSTUDENGSCORE
(
SID NUMBERnotnull,
CID NUMBERnotnull,
CSCORE NUMBER(4,2)
)
tablespace CABLESCD
pctfree10
initrans1
maxtrans255
storage
(
initial64
minextents1
maxextentsunlimited
);
--2、插入SQL語句:
insert into student
(sid, sname)
values
(001,'ZHAOHY');
insert into student
(sid, sname)
values
(002, 'ZHANGQL');
insert into student
(sid, sname)
values
(003, 'ZHAOHB');
insert into score
(cid, cname)
values
(100, '數電');
insert into score
(cid, cname)
values
(200, '模電');
insert into score
(cid, cname)
values
(300, '英語');
insert into score
(cid, cname)
values
(400, '政治');
select * from score
(001, 100, '60');
insert into studengscore
(sid, cid, cscore)
values
(002, 100, '70');
insert into studengscore
(sid, cid, cscore)
values
(003, 100, '80');
insert into studengscore
(sid, cid, cscore)
values
(003, 200, '90');
insert into studengscore
(sid, cid, cscore)
values
(003, 300, '99');
insert into studengscore
(sid, cid, cscore)
values
(003, 400, '98');
select * from studengscore
--3、查詢SQL
select *fromstudengscore;
select *from student;
select *from score;
select cnamefrom scorewhere cid =(select cidfromstudengscorewherecscore=(
selectmax(cscore)fromstudengscorewhere sid=3 ));
現在有學生表student(sid,sname),課程表crouse(cid,cname),成績表grade(sid,cid,grade)現在要查詢所
select s.sid,s.sName from s,c,g
where s.sid=g.gid and c.cid=g.cid
and grade>60
sql查詢問題,有4個表,學生表(sid,sname,cid)課程表(課程ID,KCNAME)成績表(sid,kcid,score),班級表
這是思路 不是答案
假設 班級是a表 班級對應的學生是b表
select a.*,b.chengji form a inner join b on a.xuehao=b.xuehao where 條件 order by id asc