【Oracle】基础查询练习题详解

--创建班级信息表
create table classInfo(
    cid int primary key,  --班级编号
    cname varchar2(50)  --班级名称
);

--创建学生信息表
create table student(
   sid int primary key, --学号
   sname varchar2(50),  --姓名
   age int   --年龄
       constraint CK_student_age check(age between 18 and 28),
   sex varchar2(2) default '男' --性别
       constraint CK_student_sex check(sex='男' or sex='女'), 
   cid int  --所在班级
       constraint FK_cid  references classInfo(cid),   
   addr varchar2(100)  --住址
);

--创建序列
create sequence seq_cid start with 1001 increment by 1;
create sequence seq_sid start with 1001 increment by 1;

insert into classInfo values(seq_cid.nextval,'YC01班');
insert into classInfo values(seq_cid.nextval,'YC02班');
insert into classInfo values(seq_cid.nextval,'YC03班');
insert into classInfo values(seq_cid.nextval,'YC04班');

insert into student values(seq_sid.nextval,'张三',20,'男',1001,'湖南省长沙市');
insert into student values(seq_sid.nextval,'张三丰',21,'男',1001,null);
insert into student values(seq_sid.nextval,'李四',19,'男',1001,'湖南省益阳市');
insert into student values(seq_sid.nextval,'王五',20,'女',1001,'湖南省邵阳市');
insert into student values(seq_sid.nextval,'赵柳',21,'女',1001,null);
insert into student values(seq_sid.nextval,'钱七',22,'女',1001,'湖南省岳阳市');
insert into student values(seq_sid.nextval,'王八',23,'男',1001,'湖南省衡阳市');
insert into student values(seq_sid.nextval,'田玖',24,'男',1001,null);
insert into student values(seq_sid.nextval,'穷十',20,'男',1001,'黑龙江省哈尔滨市');
insert into student values(seq_sid.nextval,'琪琪',19,'女',1002,'湖南省岳阳市');
insert into student values(seq_sid.nextval,'盼盼',20,'女',1002,'湖南省衡阳市');
insert into student values(seq_sid.nextval,'多多',21,'男',1002,'湖南省益阳市');
insert into student values(seq_sid.nextval,'天天',21,'男',1002,null);
insert into student values(seq_sid.nextval,'点点',23,'男',1002,'湖南省益阳市');
insert into student values(seq_sid.nextval,'Tom',24,'男',1003,null);
insert into student values(seq_sid.nextval,'Smith',21,'女',1003,'湖南省邵阳市');
insert into student values(seq_sid.nextval,'Tony',22,'女',1003,'湖南省长沙市');
insert into student values(seq_sid.nextval,'John',21,'男',1003,'湖南省衡阳市');
insert into student values(seq_sid.nextval,'Beckham',24,'男',1003,'湖南省长沙市');
insert into student values(seq_sid.nextval,'Rank',25,'男',1003,'湖南省益阳市');
insert into student values(seq_sid.nextval,'Block',25,'男',1003,'黑龙江省齐齐哈尔市');

 select * from classInfo for update;
select * from student;
commit;

--查询指定列表
select cid as 班级编号 ,cname 班级名称 from classInfo;
--查询所有男生
select * from student where sex='男';
--查询员工信息表
select * from emp;
--查询员工底薪+奖金
select empno,ename,sal+comm as 金额 from emp;
--员工底薪加500
select empno,ename,sal+500 底薪 from emp;
--查询年龄一年后小于21的学生
select * from student where age+1<21;
--查询学生id为1004的信息
select * from student where sid=1004;
--查询班级不等于1002的所有学生
select * from student where cid!=1002;
select * from student where cid<>1002;
--查询年龄大于22的所有学生
select * from student where age>=22;
--查询年龄在20-22之间的所有学生
select * from student where age<=22and age>=20;
select * from student where age between 20 and 22;
select * from student where age in(20,21,22);
--查询班级为1001和1003的所有学生
select * from student where cid in(1001,1003);
--查询班级不为1001和1003的所有学生
select * from student where cid !=1001 and cid!=1003;
--查询姓张的所有学生
select * from student where sname like '张%';
--查询叫张某的学生
select * from student where sname like '张_';
--查询地址为空的学生
select * from student where addr is null;

--逻辑操作：and、 or、 not
--查询年龄大于23的所有男生
select * from student where age>23 and sex='男';
--查询地址为湖南省衡阳市或益阳市的所有学生信息
select * from student where addr='湖南省衡阳市' or addr='湖南省益阳市';
select * from student where addr in('湖南省衡阳市','湖南省益阳市');
--查询所有家庭住址不为空的学生信息
select * from student where addr is not null;

 --连接操作符 union、 union all、 intersect、minus
--获取系统中所有的用户信息（包括学生信息和员工信息）的编号和姓名
select * from student where not exists (select * from classInfo where classInfo.cid=1001 and classInfo.cid=student.cid);
select sid, sname,'学生信息'用户信息 from student
union
select empno,ename,'员工信息'用户信息 from emp;
--union会自动剔除重复的信息（要求是所有的列的值都相同时才会剔除），而union all不会
select sid,sname from student
union all
select empno,ename from emp;
--获取这两个表中的相同的数据（交集）
create table a(
id number(4) primary key,
aname varchar2(10)
)
create table b(
id number(4) primary key,
bname varchar2(5)
)
insert into a values(1,'张三');
insert into a values(2,'李四');
insert into b values(1,'张三');
select id, aname from a
minus
select id,bname from b
--返回第一个查询语句中的所有数据，但是如果这些数据在第二个查询结果中出现，则这条数据在结果中不显示
--第一个结果集减去第一个结果集与第二个结果集的交集
select * from student where exists(select * from classInfo where classInfo.cid=1001 and classInfo.cid=student.cid);
--exists 存在  查询所有属于1001班的学生信息
select * from student s,classInfo c where s.cid=c.cid and c.cid=1001
--not exists不存在 查询所有不属于1001班的学生信息
select * from student where not exists(select * from classInfo where classInfo.cid=1001 and classInfo.cid=student.cid);
--any,all,some
--求出1003班学生中年龄比1001班任何一个都大的学生信息
select * from student where cid =1003 and age>any(select age from student where cid=1001);
select * from student where cid=1003;
select * from student where cid=1001;
--求出其他班中，有比1001班学生年龄小的学生信息->小于最大者
select * from student where cid!=1001 and age<(select max(age) from student where cid=1001);

--函数
--数值运算
--1.求员工信息表每个员工日平均基本工资（按每个月30天算），要求忽略小数部分
select ename,sal/30 sal,floor(sal/30) 日平均工资 from emp;
--2.求员工信息表每个员工日平均基本工资 保留两位小数返回
select ename,sal/30 sal,trunc(sal/30) 日平均工资 from emp;
--3.求员工信息表每个员工日平均基本工资 向上取整返回
select ename,sal/30 sal,ceil(sal/30) 日平均工资 from emp;
--4.求员工信息表每个员工日平均基本工资 四舍五入后返回
select ename,sal/30 sal,round(sal/30) 日平均基本工资 from emp;

--字符函数
--1.将所有员工的名字转换成小写字母显示
select lower(ename) from emp;
--2.将所有员工的名字转换成大写字母显示
select upper(ename) from emp; 
--3.显示员工信息表中员工姓名正好为5个字符的员工信息
select ename from emp where length(ename)=5;
--4.以首字母大写其他字母小写的方式显示所有员工的姓名
select substr('HelloWorld',0,2)value from dual;
select substr('HelloWorld',1,2)value from dual;
select upper(substr(ename,1,1))||lower(substr(ename,2,length(ename)-1))from emp;
--5.从学生信息表中查询显示所有学生的学号、姓名、省份             instr(addr,'省',1,1)   从第1号位开始，查找第1次出现“省”的位置，
select sid 学号,sname 姓名,substr(addr,1,instr(addr,'省',1,1))省份 from student;
--6.将学生信息表中地址为长沙市的学生显示地址改为常德市
select sid,sname,replace(addr,'长沙市','常德市') 地址 from student;

--转换函数
--1.修改员工日期
select hiredate from emp where empno=7788;
--2.显示员工的入职年份和月份
update emp set hiredate=to_date('2018/02/27','yyy/mm/dd') where empno=7788;
--3.显示员工工资，保留两个有效小数，工资为底薪+提成
select extract(month from hiredate) 月,extract (year from hiredate) 年 from emp;

--日期函数
--1.从emp表中查找已经入职10年的员工
select * from emp where add_months(hiredate,12*10)<sysdate;
--2.求每个员工的入职天数
select ename,trunc(sysdate-hiredate) from emp;
--3.找出每个月倒数第2天入职的员工
select * from emp where hiredate=last_day(hiredate)-1;
--4.获取每个员工入职的月份
select ename,hiredate,extract(month from hiredate)from emp;

--聚合函数
--求员工的最高底薪、最低底薪、平均底薪和底薪总和
select max(sal),min(sal),avg(sal),sum(sal) from emp;
--按部门统计，每个部门的最高底薪、最低底薪、平均底薪和底薪总和
select de.dname,ep.ename from emp ep,dept de where de.deptno=ep.deptno group by de.dname,ep.ename;
select deptno,max(sal),min(sal),avg(sal),sum(sal) from emp group by deptno;
--all 查询某列所有的值 
--查询所有学生的地址
select all addr from student;
-- distinct 过滤重复数据
-- 统计学生来自哪些地区
select distinct addr from student; 
--count 统计总共有多少个学生
select count(*) from student;
--统计学生来自多少个地区
select count(distinct addr)from student；

--其它函数
--为空赋值函数
--查询学生姓名和住址，如果住址为空则显示地址不详
select sname,nvl(addr,'不详') 地址 from student;

--分析函数
--排序:  按某个列进行升降序显示
--语法:  order by  列名1 [desc/asc], 列名2 [desc/asc],
--问题:
--1.按学号名排序
select * from student order by sid;
--2.按学号降序排序
select * from student order by sid desc;
--3.按学号升序排序
select * from student order by sid asc;
--4.先按年龄降序排序，再按学号升序排序
select * from student order by age asc;
--row_number()    排序:   如果排序字段的值相等，序号也不中断  1、2、3、4、5、6、7
select empno,ename,sal,job,row_number() over(order by sal desc) from emp;
--RANK 具有相等值的行排位相同，序数随后跳跃 1、2、2、4、5、5、7
select empno,ename,sal,job,RANK() over(order by sal desc) from emp;
--DENSE_RANK 具有相等值的行排位相同，序号是连续的 1、2、2、3、4、5、5、6、7
select empno,ename,sal,job,DENSE_RANK() over(order by sal desc) from emp;