-- 1. Create Table SQL: -- Student table: -- CreatetablecreatetableSTUDENT (SIDNUMBERnotnull, SNAMENVARCHAR2 (40) values (initial64minextents1maxextentsunlimited); -- Create Table -- Createtablecreat
-- 1. Create Table SQL: -- STUDENT table: -- Createtable createtable STUDENT (SID NUMBERnotnull, SNAME NVARCHAR2 (40) tablespace CABLESCD pctfree10 initrans1 maxtrans255 storage (initial64 minextents1 limit ); -- allow table -- Createtable creat
-- 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 score where cid = (select cid fromstudengscore wherecscore = (
Selectmax (cscore) fromstudengscore where sid = 3 ));