oracle练习题之单表查询

合集下载

Oracle查询练习及答案

Oracle查询练习及答案分类：技术文档2012-09-16 15:30 383人阅读评论(0) 收藏举报oracletableinsertdelete工作null--1 显示所有部门名select dnamefrom dept--2 显示所有雇员名及其全年收入(工资+补助)，并指定列别名"年收入"select ename,nvl2(comm,sal+comm,sal) as 年收入from emp--3 显示存在雇员的所有部门号select distinct deptnofrom emp--4 显示工资超过2850的雇员名和工资select ename,salfrom empwhere sal > 2850--5 显示工资不在1500到2850之间的所有雇员名及工资select ename,salfrom empwhere sal not between 1500 and 2850--6 显示雇员代码为7566的雇员名及所在部门号select ename,deptnofrom empwhere empno = 7566--7 显示部门代码为10和30中工资超过1500的雇员名及工资select ename,salfrom empwhere sal > 1500 and deptno in (10,30)--8 显示无管理者的雇员名及岗位select ename,jobfrom empwhere mgr is null--9 显示所有雇员的平均工资、总计工资、最高工资、最低工资select avg(sal) as 平均工资,sum(sal) as 总计工资max(sal) as 最高工资min(sal) as 最低工资from emp--10 显示每种岗位的雇员总数、平均工资select job,count(*),avg(sal)from empgroup by job--11 显示雇员总数，以及获得补助的雇员数select count(*),count(comm)from emp--12 显示管理者的总人数select count(distinct mgr)from emp--13 显示雇员工资的最大差额select max(sal) - min(sal)from emp--14 显示部门代码为20的部门号，以及该部门的所有雇员名、雇员工资及岗位select ename,sal,jobfrom empwhere deptno = 20--15 显示获得补助的所有雇员名、补助额以及所在部门号select deptno,ename,commfrom empwhere comm is not null--16 显示所有雇员的姓名、部门编号、工资，并且列名要显示为中文select ename as 姓名,deptno as 部门编号,sal as 工资from emp--17 显示每个部门每个岗位的平均工资、每个部门的平均工资、每个岗位的平均工资select deptno,job,avg(sal)from empgroup by cube(deptno,job)--18 显示工资大于1500的雇员名和工资，并且按照工资的降序排列select ename,salfrom empwhere sal > 1500order by sal desc--19 显示雇员部门编号为10或20的信息（要求使用IN关键字）select *from empwhere deptno in (10,20)--20 显示雇员名的第二个字母为A的信息select enamefrom empwhere ename like '_A%'--21 显示没有发放补助的雇员信息select *from empwhere comm is null--22 显示雇员表中记录总数select count(*)from emp以scott/tiger登录数据库，完成以下题目--1 显示所有雇员名、雇员工资及所在部门名select d.dname,e.ename,e.salfrom dept d,emp ewhere d.deptno = e.deptno--2 显示部门代码为20的部门名，以及该部门的所有雇员名、雇员工资及岗位select d.dname,e.ename,e.salfrom dept d,emp ewhere d.deptno = e.deptnoand d.deptno = 20--3 显示所有雇员名、雇员工资及工资级别select e.ename,e.sal,s.gradefrom emp e,salgrade swhere e.sal >= s.losal and e.sal <= s.hisal--4 显示雇员"SCOTT"的管理者名select m.enamefrom emp e,emp mwhere e.mgr = m.empnoand e.ename = 'SCOTT'--5 显示获得补助的所有雇员名、补助额以及所在部门名select d.dname,e.ename,mfrom dept d,emp ewhere d.deptno = e.deptnoand m is not null--6 查询EMP表和SALGRADE表，显示部门代码为20的雇员名、工资及其工资级别select e.ename,e.sal,s.gradefrom emp e,salgrade swhere e.sal >= s.losal and e.sal <= s.hisaland e.deptno = 20--7 显示部门代码为10的所有雇员名、部门名，以及其他部门名select e.ename,d.dnamefrom dept d,emp ewhere d.deptno = e.deptno(+)and e.deptno(+) = 10--8 显示部门代码为10的所有雇员名、部门名，以及其他雇员名select e.ename,d.dnamefrom dept d,emp ewhere d.deptno(+) = e.deptnoand d.deptno(+) = 10--9 显示部门代码为10的所有雇员名、部门名，以及其他部门名和雇员名select e.ename,d.dnamefrom dept d full join emp eon d.deptno = e.deptnoand d.deptno = 10--10显示"BLAKE"同部门的所有雇员，但不显示"BLAKE"select e.ename,e.deptnofrom emp e,emp bwhere e.deptno = b.deptnoand e.ename <> 'BLAKE'and b.ename = 'BLAKE'--01 按以下格式显示下面的信息，条件是工资大于1500的。

oracle数据库查询练习任务

简单查询1.查询customers表中的所有记录的c_name, c_truename, c_address,c_mobile列。

SELECT c_name, c_truename, c_address, c_mobile FROM Customers2.在会员信息表中查询年龄在20岁到30之间的会员信息。

SELECT*from Customers year(getdate())-year(birthdate)between 20 and303.查询会员所有的地址，即不重复的地址。

sELECT DISTINCT c_Address FROM Customers4.查询会员电话区号为0731的会员信息。

SELECT*FROM Customers WHERE c_Phone LIKE'0731%'5.查询VIP会员信息。

SELECT*FROM Customers where c_Type='VIP'6.统计商品类别数。

SELECT count(*)FROM Types7.在商品信息表中查询三星的产品信息。

SELECT*FROM Goods where g_Name like'三星_%'8.在商品信息表中查询价格在2000-3000区间的商品信息。

SELECT*FROM Goods WHERE g_Price between 2000 and 30009.在商品信息表以价格降序查询商品信息。

SELECT*FROM Goods ORDER BY g_Price DESC10.在商品信息表中查询商品类别为02的所有商品的商品名称，商品单价，并根据商品价格进行升序排序。

SELECT g_Name g_Price FROM Goods WHERE t_ID like'02%'ORDER BY g_Price ASC11.在商品信息表中查询三星和海尔品牌的商品的详细信息。

Oracle基础练习题及答案（基本查询）

Oracle基础练习题及答案（基本查询）练习题使用SQL PLUS工具登陆，用户名用scott。

1.1 在emp表中查询出所有记录的姓名、部门编号、薪水，并且列名要显示为中文。

select empno"员工编号",ename"员工姓名",job"职位",mgr"上级领导",hiredate"入职日期",sal"薪资",comm"奖金",deptno"部门编号" from emp;1.2 在emp表中查询出薪水大于1500的记录，并且按照薪水的降序排列。

select * from emp where sal>1500order by sal desc;1.3 在emp表中查询出comm字段为空值的记录。

select * from emp where comm is null;1.4 查询出emp表中含有几个部门的记录。

（用DISTINCT去除重复记录）select distinct deptno from emp;1.5 在emp表中查询出部门编号为10或20的记录（要求使用IN 关键字）select * from emp where deptno in(10,20);1.6 在emp表中查询出姓名的第二个字母为A的记录。

select ename from emp where ename like'_A%';1.7 查询出emp表中总共有多少条记录。

select count(*) from emp;1.8 查询emp表中出每个部门的部门代码、薪水之和、平均薪水。

select deptno,sum(sal),avg(sal) from emp group by deptno;使用scott用户登录，利用原有的四张表完成如下作业1.选择部门30的所有员工select * from emp where deptno=30;2.列出所有办事员（CLERK）的姓名，编号和部门编号select ename,empno,deptno from emp where job='CLERK';3.找出佣金高于薪金的所有员工select * from emp where comm>sal;4.找出佣金高于薪金30%的所有员工select * from emp where comm>(sal*0.3);5.找出部门10中所有经理（MANAGER）和部门20中所有办事员（CLERK）的详细资料select* from emp where(deptno=10and job='MANAGER')or(deptno=20and job='CLERK');6.找出部门10中所有经理（MANAGER），部门20 中所有办事员（CLERK）,既不是经理又不是办事员（CLERK）但其薪金大于或等于2000的所有员工的详细资料。

oracle数据库单表查询相关练习题及答案写法

oracle数据库单表查询相关练习题及答案写法drop table PRODUCT cascade constraints;create table PRODUCT(id NUMBER not null, --主键pcode VARCHAR2(20) not null, --商品编号pname VARCHAR2(20), --商品名称inprice NUMBER(7,2), --进价outprice NUMBER(7,2), --售价toma VARCHAR2(20), --管理员名称lastcou NUMBER, --剩余库存数量ptype VARCHAR2(5), --商品类型mark NUMBER, --有效标志createtime DATE --⽣产⽇期);alter table PRODUCTADD CONSTRAINT pk_product primary key (ID);insert into PRODUCT (id, pcode, pname, inprice, outprice, toma, lastcou, ptype, mark, createtime)values (1, 'f-qq45', '⾹蕉', 2.8, 4, '张三', 12, 'a', 1, to_date('05-06-2017', 'dd-mm-yyyy'));insert into PRODUCT (id, pcode, pname, inprice, outprice, toma, lastcou, ptype, mark, createtime)values (2, 'f-56tt', '苹果', 3.4, 5, '张三', 33, 'a', 1, to_date('16-06-2016', 'dd-mm-yyyy'));insert into PRODUCT (id, pcode, pname, inprice, outprice, toma, lastcou, ptype, mark, createtime)values (3, 'f-332', '梨', 2.7, 4, '张三', 76, 'a', 1, to_date('23-09-2016', 'dd-mm-yyyy'));insert into PRODUCT (id, pcode, pname, inprice, outprice, toma, lastcou, ptype, mark, createtime)values (4, 'f-20', '猪⾁', 14, 16, '张三', 56, 'b', 1, to_date('31-12-2013', 'dd-mm-yyyy'));insert into PRODUCT (id, pcode, pname, inprice, outprice, toma, lastcou, ptype, mark, createtime)values (5, 'f-qq46', '圆珠笔', 3, 5, '王五', 55, 'e', 1, to_date('14-09-2016', 'dd-mm-yyyy'));insert into PRODUCT (id, pcode, pname, inprice, outprice, toma, lastcou, ptype, mark, createtime)values (6, 'f-57tt', '樱桃', 8, null, '王五', 12, 'a', 1, to_date('22-05-2006', 'dd-mm-yyyy'));insert into PRODUCT (id, pcode, pname, inprice, outprice, toma, lastcou, ptype, mark, createtime)values (7, 'f-292', '三⽂鱼', 5, 8, '王五', 23, 'b', 1, to_date('31-07-2001', 'dd-mm-yyyy'));insert into PRODUCT (id, pcode, pname, inprice, outprice, toma, lastcou, ptype, mark, createtime)values (8, 'f-604', '螺丝⼑', 12, 15, '王五', 72, 'c', 1, to_date('24-02-2013', 'dd-mm-yyyy'));insert into PRODUCT (id, pcode, pname, inprice, outprice, toma, lastcou, ptype, mark, createtime)values (9, 'f-qq47', '扳⼿', 14, 19, '王五', 90, 'c', 1, to_date('14-05-2015', 'dd-mm-yyyy'));insert into PRODUCT (id, pcode, pname, inprice, outprice, toma, lastcou, ptype, mark, createtime)values (10, 'f-58tt', '钳⼦', 10, 13, '张三', 97, 'c', 1, to_date('28-03-2012', 'dd-mm-yyyy'));insert into PRODUCT (id, pcode, pname, inprice, outprice, toma, lastcou, ptype, mark, createtime) values (11, 'f-916', '矿泉⽔', 2.5, 3, '李四', 44, 'd', 1, to_date('21-07-2012', 'dd-mm-yyyy'));insert into PRODUCT (id, pcode, pname, inprice, outprice, toma, lastcou, ptype, mark, createtime) values (12, 'f-1228', '杏仁露', 4, 5, '李四', 38, 'd', 1, to_date('22-02-2013', 'dd-mm-yyyy'));insert into PRODUCT (id, pcode, pname, inprice, outprice, toma, lastcou, ptype, mark, createtime) values (13, 'f-59tt', '钢笔', null, 43, '李四', 22, 'e', 1, to_date('30-09-2013', 'dd-mm-yyyy'));insert into PRODUCT (id, pcode, pname, inprice, outprice, toma, lastcou, ptype, mark, createtime) values (14, 'f-1540', '铅笔', .5, .8, '赵六', 72, 'e', 1, to_date('14-05-2018', 'dd-mm-yyyy'));insert into PRODUCT (id, pcode, pname, inprice, outprice, toma, lastcou, ptype, mark, createtime) values (15, 'f-1852', '⼤马哈鱼', 1.5, 2, '赵六', 76, 'b', 1, to_date('17-05-2012', 'dd-mm-yyyy'));insert into PRODUCT (id, pcode, pname, inprice, outprice, toma, lastcou, ptype, mark, createtime) values (16, 'f-2164', '鸡蛋', 2.7, 4, '赵六', 56, 'b', 1, to_date('14-09-2010', 'dd-mm-yyyy'));commit;-- 使⽤insert into任意添加三条数据(数据信息⾃定)加到这个表中-- 删除'猪⾁'的那条记录-- 将扳⼿和钳⼦的商品名称分别改为'16号扳⼿'和'⼩号钳⼦', 并将这两个商品的管理⼈员都改为'赵六'-- 查询商品类型为'a'的所有商品-- 查询王五负责的c类商品-- 查询剩余数量⼩于50的所有商品-- 查询剩余数量在60-80之间的-- 查询商品名称带'笔'的和带'鱼'的记录-- 查询商品编号中带'tt'的记录-- 查询商品类型为a, d, c的所有商品-- 查询所有'e'类型商品, 并按照剩余库存数量排序-- 查询管理商品数少于4的管理员名称和管理的商品数量-- 查询所有的管理员名称和其⼿上所管理的所有商品数量-- 查询所有的管理员名称和其⼿上所管理的所有'a'类商品名称的数量-- 查询所有商品中剩余数量最少的⼀个-- 查询⽣产⽇期在2015/5/31之前的数据-- 假设所有商品的有效期是⼀年, 以当前系统时间为标准, 查询所有商品的信息, 并且加⼀列标注是否过期-- 查询每个管理员所管理的商品的平均进价价格和售价价格-- 查询每个管理员⼿上的商品的利润的平均值-- 查询假设每个管理员把所有商品售完后各⾃的利润总和答案：--SELECT * FROM product/*INSERT INTO productVALUES(50,'qqwweerr','⾖腐',2.5, 3, '张三', 30, 'b', 1, SYSDATE);COMMIT;*//*DELETE product p WHERE p.pname='猪⾁';COMMIT;*//*UPDATE product p SET p.pname='16号扳⼿', p.toma='赵六' WHERE p.pname='扳⼿';UPDATE product p SET p.pname='⼩号钳⼦', p.toma='赵六'WHERE p.pname='钳⼦';COMMIT;*/--SELECT * FROM product p WHERE p.ptype='a';--SELECT * FROM product p WHERE p.ptype='c' AND p.toma='王五';--SELECT * FROM product p WHERE stcou<50;--SELECT * FROM product p WHERE stcou <80 AND stcou > 60; --SELECT * FROM product p WHERE stcou BETWEEN 60 AND 80;/*SELECT * FROM product pWHERE p.pname LIKE '%笔%' OR p.pname LIKE '%鱼%';*/--SELECT * FROM product p WHERE p.pcode LIKE '%tt%';/*SELECT * FROM product p WHERE p.ptype IN ('a','c','d');SELECT * FROM product p WHERE p.ptype = 'a'OR p.ptype = 'c'OR p.ptype = 'd'*/--SELECT * FROM product p--WHERE p.ptype='e' ORDER BY stcou ASC或者desc/*SELECT p.toma, COUNT(p.pname) 管理的商品数FROM product p GROUP BY p.tomaHAVING COUNT(p.pname) < 4*//*SELECT p.toma, COUNT(p.pname) 管理的商品数FROM product p GROUP BY p.toma*//*SELECT p.toma, SUM(DECODE(p.ptype, 'a', 1, 0))FROM product p GROUP BY p.tomaSELECT p.toma, COUNT(p.ptype) FROM product pWHERE p.ptype='a' GROUP BY p.toma*//*SELECT * FROM product pWHERE stcou<=ALL(SELECT lastcou FROM product)*//*SELECT * FROM product pWHERE stcou=(SELECT MIN(lastcou) FROM product)*//*SELECT * FROM product pWHERE p.createtime < to_date('20150531','yyyymmdd')*//*SELECT p.pname, CASE WHEN (SYSDATE-p.createtime) > 365 THEN '过期' ELSE '正常' END 是否过期FROM product p*//*SELECT p.toma, AVG(p.inprice), round(AVG(p.outprice), 2) FROM product pGROUP BY p.toma*//*SELECT p.toma, AVG(p.outprice-p.inprice)FROM product p GROUP BY p.toma*/SELECT p.toma, SUM((p.outprice-p.inprice)* stcou) 利润总和FROM product p GROUP BY p.toma。

oracle简单查询练习

单表查询练习：针对雇员表进行练习雇员表中记录的是一个个的雇员的基本信息。

雇员表（EMP）No. 字段类型描述1 EMPNO NUMBER(4) 表示雇员编号，是唯一编号2 ENAME V ARCHAR2(10) 表示雇员姓名3 JOB V ARCHAR2(9) 表示工作职位4 MGR NUMBER(4) 表示一个雇员的领导编号5 HIREDA TE DA TE 表示雇佣日期6 SAL NUMBER(7,2) 表示月薪，工资7 COMM NUMBER(7,2) 表示奖金，或者称为佣金8 DEPTNO NUMBER(2) 部门编号1、选择部门30中的所有员工。

Select * from emp where DEPTNO=30;2、列出所有办事员（CLERK）的姓名，编号和部门编号。

Select ename,empno,deptno from emp where job=’CLERK’;3、找出佣金高于薪金的员工。

Select * from emp where comm>sal;4、找出佣金高于薪金的60%的员工。

Select * from emp where comm>sal*0.6;5、找出部门10中所有经理（MANAGER）和部门20中所有办事员（CLERK）的详细资料。

Select * from emp where deptno=10 and job=’MANAGER’or deptno=20 and job=’CLERK’;6、找出部门10中所有经理（MANAGER），部门20中的办事员（CLERK）,既不是经理又不是办事员但其薪水大于或者等于2000的所有员工的详细资料。

Select * from emp where deptno=10 and job= ‘MANAGER’or deptno=20 and job=’CLERK’or job!=’ CLERK’and job!=’ MANAGER’and sal>=2000;7、找出收取佣金的员工的不同工作。

单表查询

一、单表查询：1.Select * from 表名例子：slelect * from xs2.Select 字段1,字段2,… from 表名例：select 学号,姓名,性别from 学生3.Select 字段1 as 别名1，字段2 as 别名2，…from 表名select 课程号as kch,学号as xh from cj或者Select 字段1 别名1，字段2 别名2，…from 表名4.Select 字段1，字段2，… from 表名as 表的别名例子：select * from cj as 成绩表或者Select 字段1，字段2，… from 表名表的别名5.select Top n 字段1，字段2，…from 表名order by 字段1，字段2，…默认是ASC（升序）例：select top 3 * from cj order by 成绩asc降序是DESC例：select top 3 * from cj order by 成绩desc多个字段进行排序的时候：6.合并重复记录Select Distinct 字段1，字段2，….from 表名6.混合时的注意事项Select Distinct Top 字段1，字段2，… from表名order by 字段7.带条件查询：1）select * from 表名where 条件例：select * from cj where 成绩>802）select * from 表名where 条件1 and/or 条件2 and/or 条件3例：select distinct * from cj where 成绩>80 and 学号="x001"3）select * from 表名where 条件between ..and …例：select * from cj where 成绩>=70 and 成绩<=90select * from cj where 成绩between 70 and 90 select * from cj where 成绩not between 70 and 904) select * from 表名where 条件in()例：select distinct * from cj where 学号="x001" or 学号="x002"select distinct * from cj where 学号in("x001","x002")5) select * from 表名where 条件is NULL / not is null 例：select * from cj where 成绩is null (或者=null)select * from cj where 成绩not is null 错误的SQL文。

Oracle查询入门练习

Oracle查询入门练习Oracle查询入门练习练习针对Oracle的SCOTT方案============查询及函数部分==============第一部分：1、使用基本查询语句.(1)查询DEPT表显示所有部门名称.(2)查询EMP表显示所有雇员名及其全年收入(月收入=工资+补助),处理NULL行,并指定列别名为"年收入"。

（NVL(comm,0) comm 取空值时用0替代）(3)查询显示不存在雇员的所有部门号。

2、限制查询数据(1)查询EMP表显示工资超过2850的雇员姓名和工资。

(2)查询EMP表显示工资不在1500～2850之间的所有雇员及工资。

(3)查询EMP表显示代码为7566的雇员姓名及所在部门代码。

(4)查询EMP表显示部门10和30中工资超过1500的雇员名及工资。

(5)查询EMP表显示第2个字符为"A"的所有雇员名其工资。

(6)查询EMP表显示补助非空的所有雇员名及其补助。

3、排序数据(1)查询EMP表显示所有雇员名、工资、雇佣日期，并以雇员名的升序进行排序。

(2)查询EMP表显示在1981年2月1日到1981年5月1日之间雇佣的雇员名、岗位及雇佣日期，并以雇佣日期进行排序。

(3)查询EMP表显示获得补助的所有雇员名、工资及补助，并以工资升序和补助降序排序。

第二部分：1．列出至少有一个雇员的所有部门。

2．列出薪金比“SMITH”多的所有雇员。

3．列出所有雇员的姓名及其上级的姓名。

4．列出入职日期早于其直接上级的所有雇员。

5．列出部门名称和这些部门的雇员，同时列出那些没有雇员的部门。

6．列出所有“CLERK”（办事员）的姓名及其部门名称。

7．列出各种岗位的最低薪金，并显示最低薪金大于1500所有工作岗位及其最低薪资。

8．列出从事“SALES”（销售）工作的雇员的姓名，假定不知道销售部的部门编号。

9．列出薪金高于公司平均的所有雇员。

10．列出与“SCOTT”从事相同工作的所有雇员。