4 西洋参 SU（进货表）
GOODSID（主键） SUQTY 1 60 2 70 SA（销售表）
GOODSID（主键） SAQTY 3 80 4 90
要求一：进货记录，给出 SQL 达到以下结果
GOODSID（主键） GOODSNAME SUQTY 1 青霉素 60 2 西瓜霜 70 3 创可贴 0 4 西洋参 0 要求二：进销对比，给出 SQL 达到以下结果
GOODSID（主键） GOODSNAME SUQTY SAQTY 1 青霉素 60 0 2 西瓜霜 70 70 3 创可贴 0 80 要求三：将 GOODS.MEMO 更新为[进货数量 SU.SUQTY] 解答：
1）select g.goodsid,g.goodsname,s.quqty from goods g inner join su s on g.goodsid=s.goodsid; 2 ） select g.goodsid,g.goodsname,s.quqty,a.saqty from goods g, su s,sa a on
g.goodsid=s.goodsid and g.goodsid=a.goodsid;
3)update goods set demo=(select s.suqty from su s where s.goodsId=goods.goodsId) 14.表结构:
1) 表名:apply
字段(字段名/类型/长度):
applyno varchar 8;//申请单号(关键字) applydate bigint 8;//申请日期 state varchar 2;//申请状态 2) 表名:applydetail
字段(字段名/类型/长度):
applyno varchar 8;//申请单号(关键字) name varchar 30;//申请人姓名
idcard varchar 18;//申请人身份证号 state varchar 2;//申请状态
其中，两个表的关联字段为申请单号。 题目:
1)查询身份证号码为 440401430103082 的申请日期
2)查询同一个身份证号码有两条以上记录的身份证号码及记录个数 3)删除 applydetail 表中所有姓李的记录
解答：
1) Select applydate from apply a join applydetail d on a.applyno=d.applyno and Idcard='440401430103082';
2) select idcard,count(*) from applydetail group by idcard having count(*)>2; 3) delete from applydetail where name='李%';
15. 在 system 方案中建立表 table1,表中包含如下字段 字段名称 数据类型 要求 name Varchar2 非空 id Number 非空 age Number sex Varchar2 salary Number 解答：
Create table system.tablel1 ( Id number not null,
Name varchar(8) not null, Age number, Sex varchar(2), Salary number );
16 、 某 公 司 的 机 构 结 构 为 树 型 结 构 ， 对 应 的 表 结 构 为 TableCompany(ComCode— 机 构 代 码 ，
UpperComCode—上级机构代码)，如何查询出总公司的所有下级机构？(java 或者 SQL 均可)。你觉得
这种思维和设计是否合理？有什么好建议的？
答：select t1.* from TableCompany t1, TableCompany t2 Where t1.ComCode = t2.UpperComCode
这种设计比较容易让人理解，但是表中的数据联系过于紧密，数据量很大，会给后期维护造成不
便，如果根据第三范式要求，将每一子公司独立成一张表，对于关系的维护和数据的管理都会变得比 较方便。
17、一个简单的论坛系统，以数据库存储如下数据：
用户名，发帖标题，发帖内容，回复标题，回复内容。 每天论坛访问量 200 万左右，更新帖子 10 万左右。
请给出数据库表结构设计，并结合范式简要说明设计思路。 答：用户表：存储用户信息；
用户所发的帖子表：存储用户所发的帖子； 回复表：存储对帖子所做的回复。 设计： User：
Create table tb_user( id number(10) primary key,
Uname varchar2(20) not null unique
);
Comments:
Create table tb_comments( id number(10),
comments_id number(20) not null unique, title varchar2(20) not null,
comments varchar2(255) not null, foreign key(id) references tb_user(id) );
Replay:
Create table tb_replay( id number(10),
comments varchar2(255) not null,
foreign key(id) references tb_comments(comments_id) );
思路：因为此应用所要存储的数据量比较大，所以为了避免数据的冗余，表的设计依托于第三范式。
18、有一个数据表 userinfo，包含 userid，username 字段，其中 userid 是唯一的，username 可能
重复，请写一句 sql 查询语句，把重复的记录全部取出来。 userid username 1 老王 2 老王 3 老李 4 老李 5 小张 要求返回记录集 userid username 1 老王 2 老王 3 老李 4 老李 答：
select * from userinfo where username in (select username from userinfo group by username having count(username)>1); 19、建表 Department 部门
字段名 中文名称 类型 长度 备注
depid 部门号 变长字符 10 主键
depname 部门名称 变长字符
depcj 部门平均成绩 浮点型保留 2 位小数 表 Employee 人员表
字段名 中文名称 类型 长度
备注
empid 员工号 变长字符 10 主键
name 姓名 变长字符 10 depid 部门号 变长字符 10 Cj 成绩 浮点型保留 2 位小数 xorder 名次 整型 实现表中的记录备下面相关题目使用 Department 表中嵌入记录 部门号 部门名称 A001 人力资源部 A002 财务部 Employee 表中嵌入记录
员工号 姓名 部门号 成绩 001 张三 A001 90 002 李四 A001 90 003 王五 A001 80 004 张飞 A002 70 005 刘备 A002 60 006 关羽 A002 50 1）写出建表以及嵌入记录语句
2）显示 A001 部门员工的姓名、成绩
3）显示所有员工的员工号、姓名、部门名称、成绩 4）将关羽的成绩修改成 52 分
5）按要求写视图 VdepEmpMax 求各部门的最高分，显示部门号、最高分成绩
6）按要求写存储过程 SP_Calc 求各部门的平均成绩，并更新到 Department 表 depcj 字段中 7）按要
求写存储过程 SP_Order 求员工的名次，并更新到 Employee 表 xorder 字段中 8）按要求写视图 VdepEmp2，求各部门的前 2 名，显示部门号、员工号、成绩 排序规则如下：
员工 部门 分数 名次 张三 A001 90 1 李四 A001 90 1 张飞 A002 70 1 刘备 A002 60 2 答： 1）
create table Department(depid varchar2(20) primary key, depname varchar2(20), depcj number(10,2));
create table Employee(empid varchar2(20) primary key, name varchar2(20), depid varchar2(20), cj number(10,2),
xorder number(10));
insert into Department(depid,depname) values('A001','人力资源部'); insert into Department(depid,depname) values('A002','财务部');
insert into Employee(empid, name, depid, cj) values('001','张三','A001',90); insert into Employee(empid, name, depid, cj) values('002','李四','A001',90); insert into Employee(empid, name, depid, cj) values('003','王五','A001',80); insert into Employee(empid, name, depid, cj) values('004','张飞','A002',70); insert into Employee(empid, name, depid, cj) values('005','刘备','A002',60); insert into Employee(empid, name, depid, cj) values('006','关羽','A002',50); 2）select name,cj from employee where depid='A001';
3）select e.empid,e.name,d.depname,e.cj from employee e, department d where e.depid=d.depid; 4）update employee set cj=52 where name=' 关羽'; 5）
create view VdepEmpMax as (select deptid,max(cj) from employee e group by deptid) 6）
create or replace procedure SP_Calc as begin
update department d set depcj=(
select nvl(avg(cj),0) from employee e where e.depid(+)=d.depid); end; 7）
create or replace procedure SP_Order as begin
update employee w set xorder =(select b.rn from
(select empid,rank() over (partition by depid order by cj desc ) rn from employee) b where w.empid=b.empid); end; 8）
create or replace view VdepEmp2 as select depid,name,cj,rn from
(select e.*,rank() over (partition by depid
order by cj desc) rn from employee e) where rn<3; 20、数据库基础:
1)使用 SQL 语句创建学生表 students
字段: 学号:s_id 姓名:s_name 年龄:age 班级:class 辅导员:assistant (请设计各字段类型 与长度)
2)查询学生表中年龄大于 20 的所有学生的学号与姓名 3)删除 0201 班的所有同学
4)查询 0302 班姓李的学生的个数