create table Student
(
Sno char (9 ) primary key ,
Sname varchar2(8 ),
Ssex char (2 ) not null ,
Sage smallint not null ,
Sdept varchar2(4 ) not null
);
create table Course
(
Cno char (4 ) primary key ,
Cname varchar2(20 ) not null ,
Cpno char (4 ),
Ccredit smallint not null ,
foreign key (Cpno) references Course(Cno)
);
create table SC
(
Sno char (9 ),
Cno char (4 ),
Grade smallint ,
primary key (Sno,Cno),
foreign key (Sno) references Student(Sno),
foreign key (Cno) references Course(Cno)
);
select * from v$nls_parameters t where t.PARAMETER='NLS_CHARACTERSET' ;
alter user scott account unlock;
alter user sys account unlock;
alteruseruser_name account unlock identifiedby new_password;
insert into Student values ('201215121' ,'李勇' ,'男' ,20 ,'CS' );
insert into Student values ('201215122' ,'刘晨' ,'女' ,19 ,'CS' );
insert into Student values ('201215123' ,'王敏' ,'女' ,18 ,'MA' );
insert into Student values ('201215125' ,'张立' ,'男' ,19 ,'IS' );
insert into Student values ('201215121' ,'李白' ,'男' ,20 ,'CS' );
insert into Student values ('201215122' ,'刘备' ,'女' ,19 ,'CS' );
insert into Student values (null ,'张三' ,'男' ,19 ,'CS' );
insert into Student values (null ,'李四' ,'男' ,20 ,'IS' );
insert into Student values ('201215126' ,'张立' ,'男' ,null ,'IS' );
insert into Student values ('201215126' ,'张立' ,null ,30 ,'IS' );
select * from Student;
drop table Student;
insert into Course values ('2' ,'数学' ,null ,2 );
insert into Course values ('6' ,'数据处理' ,null ,2 );
insert into Course values ('4' ,'操作系统' ,'6' ,3 );
insert into Course values ('7' ,'PASCAl语言' ,'6' ,4 );
insert into Course values ('5' ,'数据结构' ,'7' ,4 );
insert into Course values ('1' ,'数据库' ,'5' ,4 );
insert into Course values ('3' ,'信息系统' ,'1' ,4 );
insert into Course values ('1' ,'数据库' ,'5' ,5 );
insert into Course values ('3' ,'信息系统' ,'1' ,4 );
insert into Course values (null ,'数据库' ,'5' ,4 );
insert into Course values (null ,'数据结构' ,'7' ,4 );
insert into Course values ('8' ,'数据结构' ,'20' ,4 );
insert into Course values ('9' ,'数据结构' ,'11' ,4 );
insert into Course values ('10' ,'数据结构' ,'7' ,null );
insert into Course values ('13' ,'操作系统' ,'6' ,null );
insert into Course values ('2' ,'数学' ,null ,2 );
insert into Course values ('6' ,'数据处理' ,null ,2 );
select * from Course;
drop table Course;
insert into SC values ('201215121' ,'1' ,92 );
insert into SC values ('201215121' ,'2' ,85 );
insert into SC values ('201215121' ,'3' ,88 );
insert into SC values ('201215122' ,'2' ,90 );
insert into SC values ('201215122' ,'3' ,80 );
insert into SC values ('201215122' ,'9' ,100 );
insert into SC values ('201400644' ,'1' ,70 );
insert into SC values ('201215122' ,'3' ,90 );
insert into SC values ('201215121' ,'1' ,92 );
insert into SC values ('201215122' ,null ,90 );
insert into SC values (null ,'2' ,70 );
select * from SC;
drop table SC;
select Sno 学生学号,Sname 学生姓名,Sage 学生年龄,Sdept 所在院系
from Student
where (Sage<20 ) and (Sdept like 'CS' );
select Sname 学生姓名,Ssex 学生性别
from Student
where Sdept='CS' or Sdept='MA' or Sdept='IS' ;
select Sname 学生姓名 ,Ssex 学生性别,Sage 学生年龄
from Student
where Sdept in ('CS' ,'MA' ,'IS' );