4回答

• 

  more一点

  2019-11-03 22:33:47

  已采纳

  drop table sc;

  drop table course;

  drop table student;

  drop table teacher;

  drop table dep;

  
  

  create table dep

  (

  dno number(2),

  dname varchar2(30),

  director number(4),

  tel varchar2(8)

  );

  
  

  create table teacher 

  (

  tno number(4),

  tname varchar2(10),

  title varchar2(20),

  hiredate date,

  sal number(7,2),

  bonus number(7,3),

  mgr number(4),

  deptno number(2)

  );

  create table student

  (

  sno number(6),

  sname varchar2(8),

  sex varchar2(2),

  birth date,

  passwd varchar2(8),

  dno number(2)

  );

  create table course(

  cno varchar2(8),

  cname varchar2(20),

  credit number(1),

  ctime number(2),

  quota1 number(3)

  );

  
  

  create table sc

  (sno number(6),

  cno varchar2(8),

  grade number(3));

  alter table dep add (constraint pk_deptno primary key(dno));

  alter table dep add (constraint dno_number_check check(dno>=10 and dno<=50));

  alter table dep modify(tel default 62795032);

  alter table student add (constraint pk_sno primary key(sno ));

  alter table student add (constraint sex_check check(sex='男' or sex='女'));

  alter table student modify(birth default sysdate);

  alter table course add (constraint pk_con primary key(cno));

  alter table sc add (constraint pk_key primary key(cno,sno));

  alter table teacher add (constraint pk_tno primary key(tno));

  alter table sc add (foreign key(cno) references course(cno));

  alter table sc add (foreign key(sno) references student(sno));

  alter table student add (foreign key(dno) references dep(dno));

  alter table teacher add (foreign key(deptno) references dep(dno));

  
  

  insert into dep values(10,'计算机系',9469,'12345678');

  insert into dep values(20,'自动化系',9581,'12345678');

  insert into dep values(30,'无线电系',9791,'12345678');

  insert into dep values(40,'信息管理系',9611,'12345678');

  insert into dep values(50,'微纳电子系',2031,'12345678');

  
  

  insert into teacher values(9468,'charles','professor','17-12月-2004',8000,1000,null,10);

  insert into teacher values(9469,'smith','professor','17-12月-2004',5000,1000,9468,10);

  insert into teacher values(9470,'allen','associate professor','20-2月-2003',4200,500,9469,10);

  insert into teacher values(9471,'word','professor','17-12月-2004',8000,1000,9469,20);

  insert into teacher values(9581,'jones','professor','2-4月-2003',6500,1000,9468,20);

  insert into teacher values(9582,'martin','associate professor','28-9月-2005',4000,800,9581,20);

  insert into teacher values(9583,'blake','lecture','1-5月-2006',3000,300,9581,30);

  insert into teacher values(9791,'clark','professo','9-6月-2003',5500,null,9468,30);

  insert into teacher values(9792,'scott','assciate professor','09-12月-2004',4500,null,9791,30);

  insert into teacher values(9793,'baggy','lecturer','17-11月-2004',3000,null,9791,30);

  insert into teacher values(9611,'turner','professor','8-9月-2005',6000,1000,9468,40);

  insert into teacher values(9612,'adams','assciate professor','12-1月-2004',4800,80,9611,40);

  insert into teacher values(9613,'james','lecturer','3-12月-2006',2800,200,9611,40);

  insert into teacher values(2031,'ford','rofessor','3-12月-2005',5500,null,9468,50);

  insert into teacher values(2032,'milier','assciate professor','12-1月-2005',4300,null,2031,40);

  insert into teacher values(2033,'migeal','lecturer','23-1月-2006',2900,null,2031,50);

  insert into teacher values(2034,'peggy','lecturer','23-1月-2007',2500,null,2031,50);

  
  

  insert into student(birth,sno,sname,sex,passwd,dno) values('01-8月 -10',1,'John','男','123456',10);

  insert into student(birth,sno,sname,sex,passwd,dno) values('02-8月 -10',2,'Jacob','男','123456',10);

  insert into student(birth,sno,sname,sex,passwd,dno) values('03-8月 -10',3,'Michael','男','123456',10);

  insert into student(birth,sno,sname,sex,passwd,dno) values('04-8月 -10',4,'Joshua','男','123456',10);

  insert into student(birth,sno,sname,sex,passwd,dno) values('05-8月 -10',5,'Ethan','男','123456',10);

  insert into student(birth,sno,sname,sex,passwd,dno) values('06-8月 -10',6,'Matthew','男','123456',20);

  insert into student(birth,sno,sname,sex,passwd,dno) values('07-8月 -10',7,'Daniel','男','123456',20);

  insert into student(birth,sno,sname,sex,passwd,dno) values('08-8月 -10',8,'Chris','男','123456',20);

  insert into student(birth,sno,sname,sex,passwd,dno) values('09-8月 -10',9,'Andrew','男','123456',30);

  insert into student(birth,sno,sname,sex,passwd,dno) values('10-8月 -10',10,'Anthony','男','123456',30);

  insert into student(birth,sno,sname,sex,passwd,dno) values('11-8月 -10',11,'William','男','123456',30);

  insert into student(birth,sno,sname,sex,passwd,dno) values('12-8月 -10',12,'Joseph','男','123456',40);

  insert into student(birth,sno,sname,sex,passwd,dno) values('13-8月 -10',13,'Alex','男','123456',40);

  insert into student(birth,sno,sname,sex,passwd,dno) values('14-8月 -10',14,'David','男','123456',40);

  insert into student(birth,sno,sname,sex,passwd,dno) values('15-8月 -10',15,'Ryan','男','123456',40);

  insert into student(birth,sno,sname,sex,passwd,dno) values('16-8月 -10',16,'Noah','男','123456',40);

  insert into student(birth,sno,sname,sex,passwd,dno) values('17-8月 -10',17,'James','男','123456',40);

  insert into student(birth,sno,sname,sex,passwd,dno) values('18-8月 -10',18,'Nicholas','男','123456',50);

  insert into student(birth,sno,sname,sex,passwd,dno) values('19-8月 -10',19,'Tyler','男','123456',50);

  insert into student(birth,sno,sname,sex,passwd,dno) values('20-8月 -10',20,'Logan','男','123456',50);

  insert into student(birth,sno,sname,sex,passwd,dno) values('21-8月 -10',21,'Emily','女','123456',10);

  insert into student(birth,sno,sname,sex,passwd,dno) values('22-8月 -10',22,'Emma','女','123456',10);

  insert into student(birth,sno,sname,sex,passwd,dno) values('23-8月 -10',23,'Madis','女','123456',10);

  insert into student(birth,sno,sname,sex,passwd,dno) values('24-8月 -10',24,'Isabe','女','123456',10);

  insert into student(birth,sno,sname,sex,passwd,dno) values('25-8月 -10',25,'Ava','女','123456',10);

  insert into student(birth,sno,sname,sex,passwd,dno) values('26-8月 -10',26,'Abigail','女','123456',20);

  insert into student(birth,sno,sname,sex,passwd,dno) values('27-8月 -10',27,'Olivia','女','123456',20);

  insert into student(birth,sno,sname,sex,passwd,dno) values('28-8月 -10',28,'Hannah','女','123456',20);

  insert into student(birth,sno,sname,sex,passwd,dno) values('29-8月 -10',29,'Sophia','女','123456',30);

  insert into student(birth,sno,sname,sex,passwd,dno) values('30-8月 -10',30,'Samant','女','123456',30);

  insert into student(birth,sno,sname,sex,passwd,dno) values('31-8月 -10',31,'Elizab','女','123456',30);

  insert into student(birth,sno,sname,sex,passwd,dno) values('01-7月 -10',32,'Ashley','女','123456',40);

  insert into student(birth,sno,sname,sex,passwd,dno) values('02-7月 -10',33,'Mia','女','123456',40);

  insert into student(birth,sno,sname,sex,passwd,dno) values('11-8月 -10',34,'Alexis','女','123456',40);

  insert into student(birth,sno,sname,sex,passwd,dno) values('12-8月 -10',35,'Sarah','女','123456',40);

  insert into student(birth,sno,sname,sex,passwd,dno) values('13-8月 -10',36,'Natalie','女','123456',40);

  insert into student(birth,sno,sname,sex,passwd,dno) values('14-8月 -10',37,'Grace','女','123456',40);

  insert into student(birth,sno,sname,sex,passwd,dno) values('15-8月 -10',38,'Chloe','女','123456',50);

  insert into student(birth,sno,sname,sex,passwd,dno) values('16-8月 -10',39,'Alyssa','女','123456',50);

  insert into student(birth,sno,sname,sex,passwd,dno) values('17-8月 -10',40,'Brianna','女','123456',50);         

  
  

  insert into course values('c001','数据结构',3,10,100);

  insert into course values('c002','Java语言',2,20,100);

  insert into course values('c003','数字电路',3,30,100);

  insert into course values('c004','模拟电路',3,40,100);

  insert into course values('c005','信号与系统',4,50,100);

  insert into course values('c006','C语言',3,60,100);

  insert into course values('c007','高等数学',5,70,100);

  insert into course values('c008','自动原理',1,80,100);

  insert into course values('c009','数理方程',3,90,100);

  insert into course values('c010','大学语文',2,61,100);

  insert into course values('c011','机械制图',3,52,100);

  insert into course values('c012','微机原理',3,43,100);

  insert into course values('c013','通信基础',3,74,100);

  insert into course values('c014','计算机原理',5,35,100);

  insert into course values('c015','数据库',3,86,100);

  insert into course values('c016','编译原理',2,97,100);

  insert into course values('c017','大学物理',2,38,100);

  insert into course values('c018','统计基础',4,50,100);

  insert into course values('c019','线性代数',4,70,100);

  insert into course values('c020','Linux基础',3,60,100);

  
  

  insert into sc values(6,'c002',60);

  insert into sc values(6,'c015',60);

  insert into sc values(6,'c010',61);

  insert into sc values(27,'c010',65);

  insert into sc values(6,'c001',60);

  insert into sc values(6,'c011',61);

  insert into sc values(6,'c018',70);

  insert into sc values(8,'c007',65);

  insert into sc values(27,'c020',65);

  insert into sc values(27,'c015',65);       

  insert into sc values(26,'c015',55);   

  insert into sc values(25,'c015',59);      

  insert into sc values(1,'c017',65);

  insert into sc values(2,'c017',66);

  insert into sc values(3,'c017',67);

  insert into sc values(4,'c017',68);

  insert into sc values(5,'c017',69);

  insert into sc values(6,'c017',70);

  insert into sc values(7,'c017',71);

  insert into sc values(8,'c017',72);

  insert into sc values(9,'c017',73);

  insert into sc values(10,'c017',74);

  insert into sc values(11,'c017',75);

  insert into sc values(12,'c017',76);

  insert into sc values(13,'c017',77);

  insert into sc values(14,'c017',78);

  insert into sc values(15,'c017',79);

  insert into sc values(16,'c017',80);

  insert into sc values(17,'c017',81);

  insert into sc values(18,'c017',82);

  insert into sc values(19,'c017',83);

  insert into sc values(20,'c017',84);

  insert into sc values(21,'c017',85);

  insert into sc values(22,'c017',86);

  insert into sc values(23,'c017',87);

  insert into sc values(24,'c017',88);

  insert into sc values(25,'c017',89);

  insert into sc values(26,'c017',90);

  insert into sc values(27,'c017',89);

  insert into sc values(28,'c017',88);

  insert into sc values(29,'c017',87);

  insert into sc values(30,'c017',86);

  insert into sc values(31,'c017',85);

  insert into sc values(32,'c017',84);

  insert into sc values(33,'c017',83);

  insert into sc values(34,'c017',82);

  insert into sc values(35,'c017',81);

  insert into sc values(36,'c017',80);

  insert into sc values(37,'c017',79);

  insert into sc values(38,'c017',78);

  insert into sc values(39,'c017',77);

  insert into sc values(40,'c017',76);

  commit;

  
  

  0 0
• 

  宝慕林731788

  2021-07-27 22:41:50

  学习用，感谢

  0 0
• 

  qq_心扉_0

  2018-07-09 17:11:43

  student.sql在哪儿

  
  

  0 0
• 

  陈郑

  2018-05-23 16:31:14

  回答 下面有数据

  0 0