数据库表中常用的查询实验

简介: 数据库表中常用的查询实验

实验1

练习1、请查询表DEPT中所有部门的情况。

select * from dept;

练习2、查询表DEPT中的部门号、部门名称两个字段的所有信息。

select deptno,dname from dept;

练习3、请从表EMP中查询10号部门工作的雇员姓名和工资。

select ename,sal from emp where deptno=10;

练习4、请从表EMP中查找工种是职员CLERK或经理MANAGER的雇员姓名、工资。

select ename,sal from emp where job='CLERK' or job='MANAGER';

练习5、请在EMP表中查找部门号在10-30之间的雇员的姓名、部门号、工资、工作。

select ename,deptno,sal,job from emp where deptno between 10 and 30;

练习6、请从表EMP中查找姓名以J开头所有雇员的姓名、工资、职位。

select ename,sal,job from emp where ename like 'J%';

练习7、请从表EMP中查找工资低于2000的雇员的姓名、工作、工资,并按工资降序排列。

select ename,job,sal from emp where sal<=2000 order by sal desc;

练习8、请从表中查询工作是CLERK的所有人的姓名、工资、部门号、部门名称以及部门地址的信息。

select ename,sal,emp.deptno,dname,loc from emp,dept where emp.deptno=dept.deptno and job=’CLERK’;

练习9、查询表EMP中所有的工资大于等于2000的雇员姓名和他的经理的名字。

select a.ename,b.ename from emp a,emp b where a.mgr=b.empno(+) and a.sal>=2000;

练习10、在表EMP中查询所有工资高于JONES的所有雇员姓名、工作和工资。

select ename,job,sal from emp where sal>(select sal from emp where ename=’JONES’);

练习11、列出没有对应部门表信息的所有雇员的姓名、工作以及部门号。

select ename,job,deptno from emp where deptno not in (select deptno from dept);

练习12、查找工资在1000~3000之间的雇员所在部门的所有人员信息

select * from emp where deptno in (select distinct deptno from emp where sal between 1000 and 3000);

练习13、雇员中谁的工资最高。

select ename from emp where sal=(select max(sal) from emp);

select ename from (select * from emp order by sal desc) where rownum<=1;

练习14、雇员中谁的工资第二高(考虑并列第一的情况,如何处理)。

select ename from (select ename ,sal from (select * from emp order by sal desc) where rownum<=2 order by sal) where rownum<=1;

实验2

1.查询所有雇员的姓名、SAL与COMM之和。

select ename,sal+nvl(comm,0) “sal-and-comm” from emp;

2.查询所有81年7月1日以前来的员工姓名、工资、所属部门的名字

select ename,sal,dname from emp,dept where emp.deptno=dept.deptno and hiredate>=to_date(‘1981-07-01’,’yyyy-mm-dd’);

3.查询各部门中81年1月1日以后来的员工数

select deptno,count(*) from emp where hiredate>=to_date(‘1981-01-01’,’yyyy-mm-dd’) group by deptno;

4.查询所有在CHICAGO工作的经理MANAGER和销售员SALESMAN的姓名、工资

select ename,sal from emp where (job=’MANAGER’ or job=’SALES’) and deptno in (select deptno from dept where loc=’CHICAGO’);

5.查询列出来公司就职时间超过24年的员工名单

select ename from emp where hiredate<=add_months(sysdate,-288);

6.查询于81年公司所有员工的总收入(SAL和COMM)

select sum(sal+nvl(comm,0)) from emp where to_char(hiredate,’yyyy’)=’1981’;

7.查询显示每个雇员加入公司的准确时间,按××××年××月××日时分秒显示。

select ename,to_char(hiredate,'yyyy-mm-dd hh24:mi:ss') from emp;

8.查询公司中按年份月份统计各地的录用职工数量

select to_char(hiredate,'yyyy-mm'),loc,count(*) from emp,dept

where emp.deptno=dept.deptno group by to_char(hiredate,'yyyy-mm'),loc;

9.查询列出各部门的部门名和部门经理名字

select dname,ename from emp,dept where emp.deptno=dept.deptno and job=’MANAGER’;

10.查询部门平均工资最高的部门名称和最低的部门名称

select dname from dept where deptno=(select deptno from (select deptno from emp group by deptno order by avg(sal) ) where rownum<=1) union all select dname from dept where deptno=(select deptno from (select deptno from emp group by deptno order by avg(sal) desc ) where rownum<=1);

11.查询与雇员号为7521员工的最接近的在其后进入公司的员工姓名及其所在部门名

select ename,dname from (select ename,deptno from (select ename,deptno from emp where hiredate>(select hiredate from emp where empno=7521) order by hiredate ) where rownum<=1) e,dept where e.deptno=dept.deptno

实验3

1.查询显示EMP表各雇员的工作类型,并翻译为中文显示(用decode函数)

select empno,ename,decode(job,'clerk','职员','MANAGER','经理','ANALYST','工程师','其他') from my_emp;

EMPNO ENAME DECODE(JOB,'CLERK','职员','MAN


7799 YUAN 职员

7566 JONES 经理

7782 CLARK 经理

7788 SCOTT 工程师

7839 KING 其他

7876 ADAMS 其他

7902 FORD 工程师

7934 MILLER 其他

2301 kkkkk 职员

2.建立一个视图myV_emp,视图包括myEMP表的empno、ename、sal,并按sal从大到小排列。

create view myV_emp as select empno,ename,sal from my_emp order by sal desc;

3.在myEMP表中建立ename的唯一性索引。

create unique index index_ename on my_emp(ename);

4.计算EMP表中COMM最高与最低的差值,COMM值为空时按0计算。

SQL> declare

2 masal int;

3 misal int;

4 intersectsal int;

5 begin

6 select max(nvl(sal,0)) into masal from emp ;

7 select min(nvl(sal,0)) into misal from emp;

8 intersectsal:=masal-misal;

9 dbms_output.put_line(intersectsal);

10 end;

11 /

3900

PL/SQL procedure successfully completed

5.根据表myEMP中deptno字段的值,为姓名‘JONES’的雇员修改工资;若部门号为10,则工资加100;部门号为20,加200;其他部门加300。

selectdecode(deptno,10,sal+100,20,sal+200,sal+300)from empwhere ename='YUAN';

6.查找部门编号和职位都不同的职员信息

SQL> select distinct empno,job from emp;

EMPNO JOB


7566 MANAGER
7799 clerk
7788 ANALYST
7934 CLERK
7839 PRESIDENT
7876 CLERK

说明:distinct同时作用于empno,job两个字段;

实验4

1.找出emp表中的ename第三个字母是A的员工信息

select *from empwhere enamelike'__A%';

2.找出emp表中员工姓名中含有A和的员工姓名

select enamefrom empwhere enamelike'%A%'and enamelike'%N%';

3.找出所有有佣金的员工,列出姓名、工资、佣金,显示结果按工资从小到大,佣金从大到小排序

select sal+nvl(comm,0)as sc,ename,sal,nvl(comm,0)from emp orderby salasc,scdesc;

4.找出部门号是20的职位类型

select distinct jobfrom emp where deptno=20;

5.显示工资不在1500和2000之间的员工信息:姓名、工资,并按工资从大到小排序

select ename,salfrom empwhere salnot between1500and2000orderby saldesc;

6.把一个职工号所对应的奖学金 ,一个一个读取出来,然后重复的不要

select zgh,to_char(WMSYS.WM_CONCAT(distinct(a.jlqk)))

from t_jxjagroupbya.zgh;

7.列出至少有一个员工的所有部门

select *from empwhere sal>(select salfrom empwhere ename='YUAN');

8.列出所有员工的姓名及其直接上级的姓名

select e1.ename yuang_name,e2.ename boss_name from scott.emp e1,scott.emp e2 where e1.mgr=e2.empno;

9.列出受雇日期早于其直接上级的所有员工

select t1.enamefrom scott.emp t1,scott.emp t2 where t1.hiredate<t2.hiredateand t1.mgr=t2.empno;

10.列出部门名称和这些部门的员工信息,同事列出那些没有员工的部门

select d.deptno,d.dname,d.loc,e.*from scott.dept d left join scott.empe on d.deptno=e.deptno;

11.列出最低薪资大于1500的各种工作

select job from scott.empe groupbye.jobhavingmin(sal)>1500;

12.列出在部门‘SALES’‘工作的员工的姓名,假定不知道部门编号

selecte.enamefrom scott.empe wheree.deptnoin

(select deptnofrom scott.dept d where d.dname='SALES');

13.列出薪资高于公司平均水平的所有员工

select *from scott.empe where(e.sal+nvl(e.comm,0))>(selectavg(e1.sal+nvl(e1.comm,0))from scott.emp e1);

14.列出与’SCOTT''从事相同工作的所有员工

select enamefrom scott.emp e1where e1.job=(selecte.jobfrom scott.empewhere ename='SCOTT');

实验5

1.问题描述:

test表中有ID(人员编号),A(考核标准),B(实际得分),C(课程编号)四个字段,一个ID可能会有多个科目的评分,如果一个ID中存在A=B,则合格,求合格的人员编号。

2.需求分析:

要得到的结果为:

ID 是否合格

1011 合格

1012 合格

1013 合格

1014 不合格

1015 合格

如果直接用decode()函数,则会出现同一id有合格和不合格的成绩,错误

故合格产品满足以下两个条件:(1)ID不能重复—》distinct (2)同一id存在A=B

3.解答过程:

(1)查出合格的

selectdistinctid,'合格' PJ fromtestwhereidin(selectidfromtestwhere a=b)

(2)查出不合格的

selectdistinctid,'不合格' PJ fromtestwhereidnotin(selectidfromtestwhere a=b)

(3)使用union联接

selectdistinctid,'合格' PJ fromtestwhereidin(selectidfromtestwhere a=b)union

selectdistinctid,'不合格' PJ fromtestwhereidnotin(selectidfromtestwhere a=b);

  1. SQL代码:

selectdistinctid,'合格' PJ fromtestwhereidin(selectidfromtestwhere a=b)union

selectdistinctid,'不合格' PJ fromtestwhereidnotin(selectidfromtestwhere a=b);

或者

SELECT 'yes',ID FROM TEST WHERE A=BGROUP BY ID ORDER BY ID

SELECT 'no',ID FROM TEST WHERE ID NOT IN (SELECT ID FROM TEST WHERE A=B) GROUP BY ID ORDER BY ID

5.联想扩展:

假设只有A,B两列数据,如果存在A=B,则显示匹配成功(即根据A来判断)

select t3.xx,decode(t3.xx,t3.yy,'success','fail')匹配情况from

(select*from(selectdistinct A xx fromtest) t1leftjoin

(selectdistinct A yy fromtestwhere A=B) t2 on t1.xx=t2.yy) t3

实验6

1.问题描述:

为什么第一个SQL没有数据,第二个SQL有数据?

SQL1:

SELECT t.*

FROM alx_material_types_intf_v t

WHERE t.material_level = 3

AND t.material_type NOT IN

   (SELECT a.parent_type FROM alx_material_types_intf_v a); --无数据

SQL2:

SELECT t.*

FROM alx_material_types_intf_v t

WHERE t.material_level = 3

AND t.material_type NOT IN

   (SELECT a.parent_type FROM alx_material_types_intf_v a WHERE a.parent_type = t.material_type); --有数据


2.需求分析:

对比两个SQL语句,区别在于第二个SQL语句多出了‘WHERE a.parent_type = t.material_type’。

3.解答过程:

第一句SQL的子查询SELECT a.parent_type FROM alx_material_types_intf_v a中parent_type有空值的话,not in (null)的结果是null,不是true。所以没有数据、

第二个SQL里面,因为多了个“=”的条件,导致无论如何结果集内不会出现空值。

实验7

1.问题描述:

有一个商品信息表,该表反应了各种商品的销售情况,一个产品是按照gid和gname两个字段来区分的,一个产品可能会有多个型号。

create table T_Goods
(
Id int primary key,
GId varchar2(10) not null,
GName varchar2(20) not null,
GColour varchar2(10),
GWithin int,
GSize varchar2(10),
GNumber int
)

CREATE SEQUENCE seq_goods

 INCREMENT BY 1     
 START WITH 1     
 NOMAXVALUE       
 NOCYCLE          
 CACHE 10;  


现要将各种商品各种型号的销售情况进行汇总统计,达到如下效果:

2.需求分析:

分类统计--->说明要用到group by 和sum()函数

group by 分两种情况:(1)group by GId, gname, gcolour, gwithin, gsize 得到的是每种型号的销售量

                                     (2)group by gid, gname 得到的是每种产品的销售量

3.解答过程:

(1)求每种型号的销售量

(2)求每种产品的销售量

(3)求商品的总销售量

(4)将以上3个结果集联合在一起

(5)用decode()函数精简gid和gname,用row_number() over(partition by ) 函数来排序

4.SQL代码:

select rownum seq,

   decode(rn, 1, gid) gid,
   decode(rn, 1, gname) gname,
   gcolour,
   gwithin,
   gsize,
   gnumber

from (select t.*, row_number() over(partition by gid,gname order by gnumber) rn

      from (select GId,
                   gname,
                   gcolour,
                   gwithin,
                   gsize,
                   sum(gnumber) gnumber
              from t_goods
             group by GId, gname, gcolour, gwithin, gsize
            union all
            select gid, gname, null, null, '小计', sum(gnumber)
              from t_goods
             group by gid, gname
            union all
            select null, null, null, null, '总计', sum(gnumber)
              from t_goods) t);

实验8

1.问题描述:

有一store_fee表,表中有四个字段(会员卡编号、办卡店编号、消费情况、消费店编号)

现要统计各店的办卡总计和消费总计

2.需求分析:

在A店办卡的会员,可能会在其他店里进行消费

3.解答过程:

(1)求各店的办卡统计情况

(2)求各店的消费统计情况

(3)将以上2个结果集联合起来

4.SQL代码:

select t1.dept_no, t1.办卡统计, t2.消费统计
from (select dept_no, count(*) 办卡统计 from store_fee group by dept_no) t1
left join (select deptno_no2, sum(fee) 消费统计

           from store_fee
          group by deptno_no2) t2
on t1.dept_no = t2.deptno_no2

order by dept_no;

目录
相关文章
|
3月前
|
人工智能 安全 机器人
无代码革命:10分钟打造企业专属数据库查询AI机器人
随着数字化转型加速,企业对高效智能交互解决方案的需求日益增长。阿里云AppFlow推出的AI助手产品,借助创新网页集成技术,助力企业打造专业数据库查询助手。本文详细介绍通过三步流程将AI助手转化为数据库交互工具的核心优势与操作指南,包括全场景适配、智能渲染引擎及零代码配置等三大技术突破。同时提供Web集成与企业微信集成方案,帮助企业实现便捷部署与安全管理,提升内外部用户体验。
427 12
无代码革命:10分钟打造企业专属数据库查询AI机器人
|
5月前
|
Cloud Native 关系型数据库 分布式数据库
|
5月前
|
并行计算 关系型数据库 MySQL
如何用 esProc 将数据库表转储提速查询
当数据库查询因数据量大或繁忙变慢时,可借助 esProc 将数据导出为文件进行计算,大幅提升性能。以 MySQL 的 3000 万行订单数据为例,两个典型查询分别耗时 17.69s 和 63.22s。使用 esProc 转储为二进制行存文件 (btx) 或列存文件 (ctx),结合游标过滤与并行计算,性能显著提升。例如,ctx 并行计算将原查询时间缩短至 0.566s,TopN 运算提速达 30 倍。esProc 的简洁语法和高效文件格式,特别适合历史数据的复杂分析场景。
|
6月前
|
SQL 关系型数据库 MySQL
如何优化SQL查询以提高数据库性能?
这篇文章以生动的比喻介绍了优化SQL查询的重要性及方法。它首先将未优化的SQL查询比作在自助餐厅贪多嚼不烂的行为,强调了只获取必要数据的必要性。接着,文章详细讲解了四种优化策略:**精简选择**(避免使用`SELECT *`)、**专业筛选**(利用`WHERE`缩小范围)、**高效联接**(索引和限制数据量)以及**使用索引**(加速搜索)。此外,还探讨了如何避免N+1查询问题、使用分页限制结果、理解执行计划以及定期维护数据库健康。通过这些技巧,可以显著提升数据库性能,让查询更高效流畅。
|
6月前
|
数据库 Python
【YashanDB知识库】python驱动查询gbk字符集崖山数据库CLOB字段,数据被驱动截断
【YashanDB知识库】python驱动查询gbk字符集崖山数据库CLOB字段,数据被驱动截断
|
6月前
|
数据库
【YashanDB知识库】数据库用户所拥有的权限查询
【YashanDB知识库】数据库用户所拥有的权限查询
|
6月前
|
存储 运维 监控
百万指标,秒级查询,零宕机——时序数据库 TDengine 在 AIOps 中的硬核实战
本篇文章详细讲述了七云团队在运维平台中如何利用 TDengine 解决海量时序数据存储与查询的实际业务需求。内容涵盖了从数据库选型、方案落地到业务挑战及解决办法的完整过程,特别是分享了升级 TDengine 3.x 时的实战经验,给到有需要的小伙伴参考阅读。
207 1
|
6月前
|
缓存 NoSQL 关系型数据库
WordPress数据库查询缓存插件
这款插件通过将MySQL查询结果缓存至文件、Redis或Memcached,加速页面加载。它专为未登录用户优化,支持跨页面缓存,不影响其他功能,且可与其他缓存插件兼容。相比传统页面缓存,它仅缓存数据库查询结果,保留动态功能如阅读量更新。提供三种缓存方式选择,有效提升网站性能。
110 1
|
6月前
|
数据库