The student table sid, sname, and student table cid, cname, and student transcript table sid, cid, and cscore. The name of the course with the highest score required for the student, snamecscore
-- 1. Table creation SQL:
-- Student table:
-- Createtable
Createtable STUDENT
(
SID NUMBERnotnull,
SNAME NVARCHAR2 (40)
)
Tablespace CABLESCD
Pctfree10
Initrans1
Maxtrans255
Storage
(
Initial64
Minextents1
Maxextentsunlimited
);
-- Tables Table
-- Createtable
Createtable SCORE
(
CID NUMBERnotnull,
CNAME NVARCHAR2 (40) notnull
)
Tablespace CABLESCD
Pctfree10
Initrans1
Maxtrans255
Storage
(
Initial64
Minextents1
Maxextentsunlimited
);
-- Student renewal table
-- Createtable
CreatetableSTUDENGSCORE
(
SID NUMBERnotnull,
CID NUMBERnotnull,
Cscore number (4, 2)
)
Tablespace CABLESCD
Pctfree10
Initrans1
Maxtrans255
Storage
(
Initial64
Minextents1
Maxextentsunlimited
);
-- 2. Insert an SQL statement:
Insert into student
(Sid, sname)
Values
(001, 'zhaohy ');
Insert into student
(Sid, sname)
Values
(002, 'hangsql ');
Insert into student
(Sid, sname)
Values
(003, 'zhaoph ');
Insert into score
(Cid, cname)
Values
(100, 'batter ');
Insert into score
(Cid, cname)
Values
(200, 'Die-electric ');
Insert into score
(Cid, cname)
Values
(300, 'English ');
Insert into score
(Cid, cname)
Values
(400, 'political ');
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. query SQL
Select * fromstudengscore;
Select * from student;
Select * from score;
Select cnamefrom scorewhere cid = (select cidfromstudengscorewherecscore = (
Selectmax (cscore) fromstudengscorewhere sid = 3 ));
Currently, student (sid, sname), crouse (cid, cname), and secondary tables (sid, cid, grade) are available.
Select s. sid, s. sName from s, c, g
Where s. sid = g. gid and c. cid = g. cid
And grade> 60
SQL query problems: there are 4 tables, student table (sid, sname, cid) curriculum (course ID, KCNAME) Student table (sid, kcid, score), Class table
This is not the answer.
Assume that the class is table a, and the student corresponding to the class is table B.
Select a. *, B. chengji form a inner join B on a. xuehao = B. xuehao where condition order by id asc