Oracle 练习

实验内容：
一、 用SQL语句建表：
Student,sc,course
学生表：Student(Sno,Sname,Ssex,Sdept)
        Student由学号(Sno)、姓名（Sname）、性别（Ssex）、年龄（Sage）、
所在系（Sdept）五个属性组成，其中Sno为主码，sage取值范围为(18~40),Ssex 默认为男。

 

create table student(
sno  number(5) constraint student_pk primary key,
snmae varchar2(20),
ssex  varchar2(10) default '男',
sage   number(2),
sdept  varchar2(50),
constraint ck_sage check(sage between 18 and 40));

课程表：Course(Cno,Cname,Cpno,Ccredit)
       Course由课程号（Cno）、课程名(Cname)、选修课号（Cpno）、学分(Ccredit)
四个属性组成，其中Cno为主码。

create table course(
cno int primary key,
cname varchar2(20),
cpno int,
ccredit int);

学生选课表：SC（Sno,Cno,Grade）
     SC由学号(Sno)、课程号（Cno）、成绩(Grade)三个属性组成，主码为（Sno,Cno）。
Grade 取值范围为0~100。

create table sc(
sno int ,
cno int,
grade int,
constraint sc_pk primary key(sno,cno),
constraint ck_grade check(grade between 0 and 100))

 二、用SQL语句实现将下列数据插入到相关表中。
student
Sno Sname Ssex Sage Sdept
98001 李勇 男  20 CS（计算机系）
98002 刘晨 女  19 IS（信息系）
98003 王敏 女  18 MA（数学系）
98004 王芳 女  20 IS（信息系）
Course
Cno   Cname         Cpno  Ccredit
 1 数据库          5        4
 2 数学         null    2
 3 信息系统    1    4
 4 操作系统    6    3
 5 数据结构    7    4
 6 数据处理   null    2
 7 PASCAL语言    6    4
Sc

Sc
Sno     Cno      Grade
 98001      1       95
 98001      2       89
 98002      2       90
三、用SQL语句完成以下功能
 1、把98001学生姓名改为王晶。
update student set sname='王晶' where sno=98001;
2、把7号课程名称改为C
update course set cname='C' where cno=7
3、删除98004学生的基本情况
delete from student where sno=98004;

4、删除表course，删除sc中全部的内容
drop table course;

delete from sc;

truncate table sc;

5、给表sc增加字段Scome 日期型
alter table sc add scome date;

6、将student表中sage改为 number型
alter table students modify sage number;

7、删除表student中字段Ssex

alter table student drop(ssex)

8、给student中sno上加入primary key
alter table student add constraint student_pk primary key(sno);

9、给sc上sno加上外键约束，参照student1中的sno
alter table sc add constraint sc_fk foreign key(sno) references student1(sno) on delete cascade|on delete set null;

10、给student 中的sname 加唯一约束
alter table student add constraint  un_sname  unique(sname);

 

11、给 student中的sname加非空约束
alter table student modify sname not null;

12、删除student中的sname 唯一约束
alter table student drop constraint un_sname;

13、给student中年龄增加check约束，取值范围为大于18 ~ 40 
alter table student add constraint ck_sage check(sage between 18 and 40);
14、删除sc表中列scome
alter table sc drop(scome);
---------------------------------------------------------------------------------------------------------------------------

1、查找部门30中员工的详细信息。
select *
from emp
where deptno=30;
2、找出从事clerk工作的员工的编号、姓名、部门号。
select empno,ename,deptno
from emp
where job=upper('clerk');

3、检索出奖金多于基本工资的员工信息。
select *
from emp
where comm >sal

4、检索出奖金多于基本工资60%的员工信息。
select *
from emp
where comm>sal*0.6;

5、找出10部门的经理、20部门的职员 的员工信息。
select *
from emp
where deptno=10 and job='MANAGER'
 or   deptno=20 and job='CLERK'

6、找出10部门的经理、20部门的职员 或者既不是经理也不是职员但是工资高于2000元的员工信息。
select *
from emp
where (deptno=10 and job='MANAGER')
 or  ( deptno=20 and job='CLERK')
 or  ( job not in ('MANAGER','CLERK') and sal>2000)

7、找出获得奖金的员工的工作。
select * from emp where comm is not null;

8、找出奖金少于100或者没有获得奖金的员工的信息。
select * from emp where comm is null or comm<100;

9、查找员工雇佣日期中当月的最后一天雇佣的。
select * from emp where hiredate=last_day(hiredate);

10、检索出雇佣年限超过12年的员工信息。
select * from emp where months_between(sysdate,hiredate)/12>12;

11、找出姓名以A、B、S开始的员工信息。
select * from emp where ename like 'A%' OR ENAME LIKE 'B%' OR ENAME LIKE 'S%';
select * from emp where substr(ename,1,1) in ('A','B','S');

12、找到名字长度为7个字符的员工信息。
select * from emp where length(ename)=7;
select * from emp where ename like '______';

13、名字中不包含R字符的员工信息。
select * from emp where ename not like '%R%';
select * from emp where instr(ename,'R')=0;

14、找出员工名字的前3个字符。
select substr(ename,1,3) from emp;

15、将名字中A改为a。
select replace(ename,'A','a') from emp;

16、将员工的雇佣日期拖后10年。
select add_months(hiredate,120) from emp

17、返回员工的详细信息并按姓名排序。
select * from emp order by ename;

18、返回员工的信息并按员工的工作年限降序排列。
select * from emp order by hiredate asc;

19、返回员工的信息并按工作降序工资升序排列。
select * from emp order by job desc,sal asc;
20、返回员工的姓名、雇佣年份和月份并且按月份和雇佣日期排序。
select ename,to_char(hiredate,'yyyy-mm')
from emp
order by to_char(hiredate,'mm'),hiredate;

21、计算员工的日薪(按30天)。
select floor(sal/30) from emp

22、找出2月份雇佣的员工。
select * from emp where to_char(hiredate,'mm')='02';

23、至今为止，员工被雇佣的天数。
select sysdate-hiredate from emp;

24、找出姓名中包含A的员工信息。
select * from emp where ename like '%A%';

25、计算出员工被雇佣了多少年、多少月、多少日。
select months_between(sysdate,hiredate)/12 year,
       months_between(sysdate,hiredate) month,
       sysdate-hiredate day
from emp;
26、显示如下效果

当前时间是
--------------------------------------------
2010年01月13日 下午  15点39分13秒  星期三

 select to_char(sysdate,'yyyy"年"mm"月"dd"日" am hh24"点"mi"分"ss"秒" day') 当前时间是
 from dual;