DML、建表、约束、查询综合练习11

DML练习
1.往emp表中插入empno,ename,sal数据（111,'1',1000)(222,'2',2000)
insert into emp(empno,ename,sal)
values(111,'DOWNEY',1000);
insert into emp(empno,ename,sal)
values(222,'JACK',2000);
2.把empno=111的员工comm改成100
update emp set comm=100 where empno=111;

3.往dept表中插入dept表中deptno=100的数据
insert into dept select * from dept where deptno=100;

4.删除empno=111的数据
delete from emp where empno=111;

5.为所有人长工资，标准是：10部门长10%；20部门长15%；30部门长20%其他部门长18%（要求用DECODE函数）
update emp set sal =decode(deptno, '10',sal*1.1,
'20',sal*1.15,
'30',sal*1.2,
sal*1.18);

6.根据工作年限长工资，标准是：为公司工作了几个月就长几个百分点。
update emp set sal=round(sal*(1+(sysdate-hiredate)/365*12/100),2);


建表练习
1.scott下面创建一个表
emp1
empno number(10)
ename varchar2(50)
create table emp1(empno number(10),
ename varchar2(50));
2.添加一个字段sal number(10,2)
alter table emp1 add (sal number(10,2));
3.修改字段 ename varchar(100)
alter table emp1 modify(ename varchar2(100));
4.删除字段sal
drop emp1 sal
5.把表emp1改成emp2
rename emp1 to emp2;


约束练习
1.创建一张表student
id number
name varchar2(10)
age number(10)
tel varchar2（10）
create table student(id number primary key,
name varchar2(10) not null,
age number(10) check(age>18),
tel varchar2(10) unique not null);
给id字段添加主键约束
给name字段添加非空约束
给age字段添加check约束（age必须大于18岁)
给tel添加唯一 非空 约束



2.创建一张学员兴趣爱好表 hobby
id number(10)
hobby_name varchar2(10)
sid number --学生id
给sid字段添加外键约束，并且要带级联删除
create table hobby(id number(10),
hobby_name varchar2(10),
sid number foreign key);


3.删除掉student表中tel字段的唯一约束（先写出查看该表约束的sql)
alter table student
drop constraint unique(tel);
4.手动添加student表中tel字段的唯一约束（约束名为：MY_CONSTRAINT_1)
alter table student
add my_constraint_1 unique(tel);
5.禁用约束MY_CONSTRAINT_1
alter table student
disable unique my_constraint_1;
6.启用约束MY_CONSTRAINT_1
alter table student
able unique my_constraint_1;



建表、约束、查询综合练习

1、题目背景
有某个学生运动会比赛信息的数据库，保存了如下的表：
. 运动员sporter（运动员编号sporterid，运动员姓名name，运动员性别sex，所

属系号department）
. 项目item（项目编号itemid，项目名称itemname，项目比赛地点location）
. 成绩grade（运动员编号sporterid，项目编号itemid，积分mark）
create table sporter(sporterid number(10) primary key,
name varchar2(10) not null,
sex char(2),
department varchar2(10) not null);
create table item(itemid varchar2(10) primary key,
itemname varchar2(10) not null,
location varchar2(10));
create table grade(sporterid number(10) primary key,
itemid varchar2(10) not null unique,
mark number(20) check(mark is null || mark=decode(mark,6,'第一',4,'第二',2,'第三',0,'其他名次')));

2、功能要求
2.1、建表
（1）、定义各个表的主码外码约束

（2）、运动员的姓名和所属系别不能为空值。

（3）、积分要么为空值，要么为6,4,2,0, 分别代表第一，二，三名和其他名次的积分。





2.2、数据

运动员（
1001, 李明，男，计算机系
1002，张三，男，数学系
1003, 李四，男，计算机系
1004, 王二，男，物理系
1005, 李娜，女，心理系
1006, 孙丽，女，数学系）
insert into sporter(sporterid,name,sex,department)values(1001, '李明','男','计算机系');
insert into sporter(sporterid,name,sex,department)values(1002, '张三','男','数学系');
insert into sporter(sporterid,name,sex,department)values(1003, '李四','男','计算机系');
insert into sporter(sporterid,name,sex,department)values(1004, '王二','男','物理系');
insert into sporter(sporterid,name,sex,department)values(1005, '李娜','女','心理系');
insert into sporter(sporterid,name,sex,department)values(1001, '孙丽','女','数学系');
项目（
x001, 男子五千米，一操场
x002，男子标枪，一操场
x003, 男子跳远，二操场
x004, 女子跳高，二操场
x005, 女子三千米，三操场）
insert into item(itemid,itemname,location)values('x001','男子五千米','一操场');
insert into item(itemid,itemname,location)values('x002','男子标枪','一操场');
insert into item(itemid,itemname,location)values('x003','男子跳远','二操场');
insert into item(itemid,itemname,location)values('x004','女子跳高','二操场');
insert into item(itemid,itemname,location)values('x005','女子三千米','三操场');
积分（
1001, x001, 6 insert into grade(sporterid,itemid,mark)values(1001,'x001',6);
1002, x001, 4 insert into grade(sporterid,itemid,mark)values(1002,'x001',4);
1003, x001, 2 insert into grade(sporterid,itemid,mark)values(1003,'x001',2);
1004, x001, 0 insert into grade(sporterid,itemid,mark)values(1004,'x001',0);
1001, x003, 4 insert int

o grade(sporterid,itemid,mark)values(1001,'x003',4);
1002, x003, 6 insert into grade(sporterid,itemid,mark)values(1002,'x003',6);
1004, x003, 2 insert into grade(sporterid,itemid,mark)values(1004,'x003',2);
1005, x004, 6 insert into grade(sporterid,itemid,mark)values(1005,'x004',6);
1006, x004, 4) insert into grade(sporterid,itemid,mark)values(1006,'x004',4);




3、要求
1、 求出目前总积分最高的系名，及其积分。
? 所有的系名都在sporter 表中，所以此时肯定与grade 表进行关联查询。
select s.department,max(g.mark)
from sporter s,grade g
where s.sportid=g.sportid
? 实际上以上的结果一排序，就可以求出总积分最高的。
order by mark;

? 使用ROWNUM 最省事


? 不使用ROWNUM 呢？



2、 找出在一操场进行比赛的各项目名称及其冠军的姓名
? 首先确定出一操场中的全部项目和每个项目的最高成绩
select itemname,name(select itemname,max(mark) from sporter,grade where location='一操场');


? 根据以上的结果求出最高分



3、 找出参加了张三所参加过的项目的其他同学的姓名
? 找出张三参加过的项目编号

select name

? 根据此编号再从表中查询符合此编号项目的运动员编号，再从运动员表中取出姓名


4、 经查张三因为使用了违禁药品，其成绩都记0 分，请在数据库中作出相应修改。
? 首先应该确定好张三的运动员编号，之后以此编号作为更新的条件。



5、 经组委会协商，需要删除女子跳高比赛项目。
? 直接执行delete 语句即可