南邮数据库系统实验(1)

实验一

实验名称：SQL语言

一、 实验目的和要求
(1) 通过上机实践，熟悉Oracle的SQL * Plus环境及使用方法
(2) 掌握SQL语言，能熟练运用SQL语言进行数据定义和数据操纵
(3) 加深对关系数据模型的数据结构和约束的理解

二、实验原理及内容
实验内容如下：
1． 了解并掌握SQLPlus环境的使用
(1) 以管理员身份登录到Oracle SQLPlus；
CREATE USER B1704 IDENTIFIED BY XXX；
(2) 以学号为用户名创建一个用户，并授予其权限；
Grant DBA to XXX；
(3) 管理员退出Oracle，重新用新创建的用户登录Oracle。
Connect B1704

2．用SQL的DDL语句图书管理系统创建基表

CREATE TABLE B
   	(B#   CHAR(4) NOT NULL,
   	CLASS  CHAR(15) NOT NULL,
  	BNAME   CHAR(50),
	AUTHER  CHAR(25),
	PUBLISH  CHAR(25),
   	VALUE    FLOAT(4),
   	PRIMARY  KEY(B#));


CREATE TABLE R
   (R#   CHAR(4) NOT NULL,
   RNAME    CHAR(20) NOT NULL,
   COMPANY  CHAR(50),
   POSITION  CHAR(20), 
   PRIMARY  KEY(R#));


 CREATE TABLE BR
   (B#     CHAR(4),
   R#     CHAR(4),
   BDATE DATE,
   OTHER CHAR(50),
	FOREIGN KEY(B#)REFERENCES B(B#),
	FOREIGN KEY(R#)REFERENCES R(R#));  

3．为基表“读者”补充定义：职称只能取初级、中级、高级之一。
ALTER TABLE R ADD CHECK(POSITION IN(‘初级’,’中级’,’高级’));

4．用SQL的DML语句向上述基表中增加、修改和删除数据

(1) 向三个基表中插入数据
  INSERT INTO B VALUES (‘0001’,’TP31’,’计算机组成’,’WANG’,’人民教育’,66.00);
  INSERT INTO B VALUES (‘0002’,’TP32’,’数据库系统概论’,NULL,’高等教育’,50.00);
  INSERT INTO B VALUES (‘0003’,’TP33’,’数据结构’,NULL,NULL,30.50);
  
  INSERT INTO R VALUES (‘T201’,’LIXIN’,’计算机系’,’中级’);
  INSERT INTO R VALUES (‘L001’,’ZHANGHUA’,’计算机系’,’高级’);
 
  INSERT INTO BR VALUES (’0001’, ‘Z003’,to_date(‘20XX-3-3’,’yyyy-mm-dd’),NULL);
  INSERT INTO BR VALUES (’0001’, ‘T201’, to_date(‘20XX-3-6’,’yyyy-mm-dd’),NULL);
  INSERT INTO BR VALUES (’0005’, ‘L001’, to_date(‘20XX-4-11’,’yyyy-mm-dd’),NULL);

(2) 数据的修改和删除
写出对应的SQL语句
①	UPDATE B SET AUTHER=’LIUWEI’, PUBLISH=’高等教育’ WHERE B#=0001;
②	UPDATE B SET CLASS#=’TP50’ WHERE BNAME LIKE ‘%数据库%’;
③	UPDATE B SET VALUE=VALUE*1.10;
④	DELETE FROM BR WHERE R# LIKE ‘s%’;
DELETE FROM R WHERE R# LIKE ‘s%’;

5．用SQL的QL语句完成查询

(1) SELECT BNAME, AUTHER FROM B;
(2) SELECT RNAME,POSITION FROM R,BR 
WHERE R.R#=BR.R# AND COMPANY=‘南京邮电大学’;
(3) SELECT * FROM B 
WHERE VALUE>ALL(SELECT VALUE FROM B WHERE AUTHER=’LIWEI’) ;
(4) SELECT MAX(VALUE),MIN(VALUE),AVG(VALUE) FROM B GROUP BY AUTHER;
(5) SELECT RNAME,COMPANY FROM R
  WHERE R# IN (SELECT R# FROM BR GROUP BY R# HAVING COUNT(*)>=3);

6．用SQL的DDL语句创建视图、索引

(1) CREATE VIEW V1(COMP,R_NUM,BORROW_NUM)
AS SELECT R.COMPANY,COUNT(DISTINCT B.B#),COUNT (B.B#) FROM B,BR,R
WHERE B.B#=BR.B#  GROUP BY COMPANY;
 (2) SELECT * FROM V1;
(3)CREATE INDEX I1 ON B(PUBLISH DESC);

7．为当前用户创建一张结构与scott用户的员工表emp表相同的员工表。

Connect scott/tiger
Grant all privileges on emp to wang;
Connect wang/root
Create table employee as select * from scott.emp;