zhang.txt

这是数据库系统的实验训练题

DROP TABLE STUD_219; 
DROP TABLE DEPT_219;
CREATE TABLE DEPT_219(DEPT_NO CHAR(10) PRIMARY KEY,DEPT_NAME VARCHAR2(15),
DEAN CHAR(8),TEL CHAR(8) CHECK(SUBSTR(1,3)='887'));
CREATE TABLE STUD_219(STUDENT_NO CHAR(12) PRIMARY KEY,STUDENT_NAME CHAR(12),birthday date,NATIVE_PLACE varchar2(18),DEPT_NO CHAR(10) REFERENCES DEPT_219(DEPT_NO));
DELETE FROM STUD_219;
DELETE FROM DEPT_219;
INSERT INTO DEPT_219 VALUES('D01','信息系','王大明','8879626');
INSERT INTO DEPT_219 VALUES('D02','计算机系','张大奔','8879726');
INSERT INTO DEPT_219 VALUES('D03','自动化系','李水明','8879826');
INSERT INTO DEPT_219 VALUES('D04','临床系','欧阳卫红','8879926');
INSERT INTO DEPT_219 VALUES('D05','数学系','宽左右','8879526');

INSERT INTO STUD_219 VALUES('0901050201','袁银才',SYSDATE-365*22,'安徽六安','D01');
INSERT INTO STUD_219 VALUES('0901050202','李争光',SYSDATE-365*23,'湖南长沙','D01');
INSERT INTO STUD_219 VALUES('0901050203','张志伟',SYSDATE-365*23,'湖南长沙','D01');
INSERT INTO STUD_219 VALUES('0901050204','李阳',SYSDATE-365*21,'湖南长沙','D01');
INSERT INTO STUD_219 VALUES('0902040205','王汉中',SYSDATE-365*24,'湖南长沙','D02');
INSERT INTO STUD_219 VALUES('0902040606','高强',SYSDATE-365*21,'江苏徐州','D02');
INSERT INTO STUD_219 VALUES('0902040707','王博',SYSDATE-365*22,'浙江杭州','D02');
INSERT INTO STUD_219 VALUES('0902040808','无名氏',SYSDATE-365*21,'月球黑洞','D02');
INSERT INTO STUD_219 VALUES('0903020609','张三', SYSDATE-365*23,'湖南株洲','D03');
INSERT INTO STUD_219 VALUES('0903020710','李四', SYSDATE-365*24,'甘肃天水','D03');
INSERT INTO STUD_219 VALUES('0903020811','郭靖',SYSDATE-365*21,'内蒙古包头','D03');
INSERT INTO STUD_219 VALUES('0903020812','黄蓉',SYSDATE-365*21,'河北石家庄','D03');
INSERT INTO STUD_219 VALUES('0904020813','杨过',SYSDATE-365*21,'湖北黄石','D04');
INSERT INTO STUD_219 VALUES('0904020814','龙啸天',SYSDATE-365*21,'河南洛阳','D04');
INSERT INTO STUD_219 VALUES('0904020815','张学友',SYSDATE-365*21,'四川重庆','D04');
INSERT INTO STUD_219 VALUES('0904020816','刘德华',SYSDATE-365*21,'辽宁抚顺','D04');
INSERT INTO STUD_219 VALUES('0905020817','梁咏琪',SYSDATE-365*21,'香港九龙','D05');
INSERT INTO STUD_219 VALUES('0905020818','昂晓峰',SYSDATE-365*21,'山西河曲','D05');
INSERT INTO STUD_219 VALUES('0905020819','江哲民',SYSDATE-365*21,'湖南长沙','D05');
INSERT INTO STUD_219 VALUES('0905020820','赵傻',SYSDATE-365*21,'台湾台北','D05')

col student_no heading'学号' justify center 
col student_name heading'姓名' justify center 
col birthday heading'生日' justify center 
col native_place heading'籍贯' justify center 
col dept_no heading'系号' justify center 
/
select * from dept_219;
select * from stud_219;
select * from dept_219 order by dept_no asc;
select * from stud_219 where student_name='张志伟';
select student_no,student_name,birthday from stud_219
where student_name='张三';
select STUDENT_NO,STUDENT_NAME,birthday,NATIVE_PLACE,stud_219.DEPT_NO from stud_219,dept_219
where dept_name='计算机系'and stud_219.dept_no=dept_219.dept_no;

select birthday,count(*) from stud_219,dept_219
where dept_name='计算机系'and stud_219.dept_no=dept_219.dept_no
group by birthday;

select student_no,birthday,dept_219.dept_no,dept_name


CREATE or replace VIEW S_D_219 AS SELECT Student_no,Student_NAME,birthday,STUD_219.Dept_NO,Dept_Name,TEL FROM STUD_219,DEPT_219
WHERE STUD_219.Dept_NO=DEPT_219.Dept_NO AND TO_CHAR(BIRTHDAY,'YYYY')>='1985';

select*from s_d_219;