数据库增删改查案例(精)

1 用sqlplus连接数据库时,为什么会出Oracle not available错误? oracle server (即通常所说的数据库)是否启动,ORACLE_SID是否正确设置。 2 找出员工的姓中(last_name)第三个字母是a的员工名字 select last_name from s_emp where last_name like '__a%' 3 找出员工名字中含有a和e的 select first_name from s_emp where first_name like '%a%' and first_name like '%e%' 比较 select first_name from s_emp where first_name like '%a%e%' 4 列出每个员工的名字,工资、涨薪后工资(涨幅为8%),元为单位进行四舍五入 select first_name,salary,round(salary*1.08 from s_emp 5 找出谁是最高领导,将名字按大写形式显示 select upper(first_name from s_emp where manager_id is null 6 找出所有有提成的员工,列出名字、工资、提出,显示结果按工资从小到大,提成从小到大 select first_name,salary,commission_pct from s_emp where commission_pct is not null order by salary desc,commission_pct 7 找出31、41部门中工资在1000到1500之间的员工select first_name,dept_id,salary from s_emp where dept_id in (31,41 and salary between 1000 and 1500 8 改变NLS_LANG的值,让select to_char(salary*12,’L99,999.99’ from s_emp 输出结果的货币单位是￥或$ setenv NLS_LANG 'SMIPLIFIED CHINESE_CHINA.ZHS16GBK' (￥ setenv NLS_LANG AMERICAN_AMERICA.US7ASCII 9 哪些员工的工资高于他直接上司的工资,列出员工的名字和工资,上司的名字和工资 select e.first_name,e.salary,m.first_name,m.salary from s_emp e,s_emp m where e.manager_id = m.id and e.salary > m.salary 10 42部门有哪些职位 select distinct title from s_emp where dept_id = 42 11 哪些员工的入职日期比Ben晚 select first_name,start_date from s_emp where start_date < (select start_date from s_emp where first_name = 'Ben' 若只有一个名为'Ben'的。 select first_name,start_date from s_emp where start_date 0 17 各个部门平均、最大、最 小工资、人数,按照部门号升序排列 select dept_id,max(salary,min(salary,count(* from s_emp group by dept_id order by dept_id 18 工资大于1500的部门的人数(各个部门中工资大于1500的员工人数)se select dept_id,count(* from s_emp where salary > 1500 group by dept_id 19 各个部门平均工资和人数,按照部门名字升序排列 select max(d.name dname,max(r.name rname,avg(e.salary,count(* from s_emp e,s_dept d,s_region r where e.dept_id = d.id and d.region_id = r.id group by dept_id order by dname,rname 20 是否有员工在同一个部门,而且工资相同,列出这样的部门号和工资、人数(每个部门中多少有同样工资的员工,列出他们的部门号,工资,人数) select e1.dept_id,avg(e1.salary,count(* from s_emp e1 ,s_emp e2 where e1.dept_id = e2.dept_id and e1.salary = e2.salary and e1.first_name <> e2.first_name group by e1.dept_id 21 哪些部门工资高于1000的人数超过2人,列出部门名字 select max(d.name,max(r.name from s_emp e,s_dept d,s_region r where e.dept_id = d.id and d.region_id = r.id and salary > 1000 group by dept_id having count(* > 2 select d.name,r.name from s_dept d,s_region r, (select dept_id from s_emp where salary > 1000 group by dept_id having count(* > 2 a where a.dept_id = d.id and d.region_id = r.id 22 哪些员工和Biri(last_name同部门 select last_name,dept_id from s_emp where dept_id = (select dept_id from s_emp where last_name = 'Biri' and last_name <> 'Biri' select last_name,dept_id from s_emp where dept_id in (select dept_id from s_emp where last_name = 'Biri' and last_name <> 'Biri' 23 哪些员工的工资,高于整个公司的平均工资,列出员工的名字和工资(降序) select first_name,salary from s_emp where salary > (select avg(salary from s_emp order by salary desc 24 哪些员工的工资,介于32和33部门(33高些)平均工资之间select first_name,salary from s_emp where salary between (select avg(salary from s_emp where dept_id = 32 and (select avg(salary from s_emp where dept_id = 33 25 所在部门平均工资高于1500的员工名字 select first_name,dept_id from s_emp where dept_id in (select dept_id from s_emp group by dept_id having avg(salary > 1500 26 列出各个部门的ID,以及这个部门的最高工资员工名字和工资 select dept_id,first_name,salary from s_emp where (dept_id,salary in (select dept_id,max(salary from s_emp group by dept_id 27 哪个员工是整个公司的最高工资 select first_name,salary from s_emp where salary = (select max(salary from s_emp 28 部门平均工资最高的是多少 select max(avg(salary from s_emp group by dpet_id 29 哪些部门的人数 比32号部门的人数多 select dept_id,count(* from s_emp group by dept_id having count(* > (select count(* from s_emp where dept_id = 32 1 完成s_emp,s_dept,s_region 三张

表

关于同志近三年现实表现材料材料类招标技术评分表图表与交易pdf视力表打印pdf用图表说话 pdf

的E-R图 2 讨论E-R图在基于数据库的开发中的重要作用 3 重新理解怎样完成多表查询 1. 工资在1500至3000之间的员工,各自工作的年限(四舍五入,降序)。 select first_name,salary,round((sysdate-start_date/365 from s_emp where salary between 1500 and 3000 2. 公司规定,每个员工在工作25年的第一个周五,可以申请退休,查询每个员工的这天显示

格式

pdf格式笔记格式下载页码格式下载公文格式下载简报格式下载

:2010-01-01:MON select first_name,to_char(add_months(start_date,25*12,'yyyy-mm-dd' from s_emp 3. 讨论为什么说数据库里流淌的是一个一个的事务?为什么说程序员写的是事务,而不是DML语句? 4. 在完成基于数据库的开发中,程序员主要操作的数据库对象是什么?对其主要做什么操作?哪些操作是频繁使用的?课后练习(After Course Exercise) 1. 讨论索引的作用,索引和查询的关系? 2. 在表和视图上可以定义多少种类型的约束?计费项目 1. 根据E-R图完成以下表结构的

设计

领导形象设计圆作业设计ao工艺污水处理厂设计附属工程施工组织设计清扫机器人结构设计

:管理员表、模块信息表、管理员权限表、用户信息表、用户业务帐号信息表、资源信息表 2. 完成一个脚本,包含对上述表的建表语句。 3. 追加脚本,完成上述表的维护操作。 4. 完成以下查询:每个管理员有哪些权限?哪些管理员有所有权限?没有某项权限的管理员有哪些?用户选择了哪些业务?用户选择的业务的资费情况如何? /***************************************************** t_adm 管理员表(用于保存管理员信息) ******************************************************/ drop table t_adm; create table t_adm( id number primary key, -- 管理员编码 name varchar2(32, -- 管理员姓名 login_name varchar2(10 unique not null, -- 登录名 login_password varchar2(32, -- 登录密码 phone varchar2(32, -- 联系电话 email varchar2(32, -- E-Mail enroll_date date, -- 注册日期 close_date date -- (不用字段 ; insert into t_adm values(0,'tarena','tarena','tarena','62132839','tarena@tarena.com.cn',sysdate,null; /***************************************************** t_module 模块表(用于保存模块信息) ******************************************************/ drop table t_module; create table t_module( id number primary key, -- 模块编号 name varchar2(32, -- 模块名称 user_flag char -- 使用者标记:0——管理员;1——用户; ; insert into t_module values(1, '管理员管理', 0; insert into t_module values(2, '资费管理', 0; insert into t_module values(3, '用户管理', 0; insert into t_module values(4, '账务查询', 0; insert into t_module values(5, '账单查询', 0; insert into t_module values(6, '用户自服务', 1; /***************************************************** t_adm_rights 管理员权限表(用于保存管理员对模块的访问权限) ******************************************************/ drop table t_adm_rights; create table t_adm_rights( adm_id number references t_adm(id, -- 管理员编码 module_id number references t_module(id, -- 模块编号 primary key(adm_id, module_id ; /***************************************************** t_pricing 资费表 ******************************************************/ drop table t_pricing; create table t_pricing( pricing_id number primary key, -- 资费编码(pk pricing_name varchar2(32 not null unique, -- 资费名称 base_fee number(5,2, -- 月租费rate_fee number(4,2, -- 每小时费用 pricing_desc varchar2(64 -- 资费描述 ; /************************************************************* t_users 用户表(用于保存用户账务帐号信息及个人信息) **************************************************************/ drop table t_users; create table t_users ( id number(32 primary key, -- 主键编码 name varchar2(32 not null, -- 用户姓名 login_name varchar2(10 unique, -- 用户账务帐号 login_password varchar2(32 not null, -- 用户账务帐号密码 status number(2 not nu ll, -- 账务帐号状态 (0:开通;1:暂停;2:删除) phone varchar2(32, -- 联系电话 email varchar2(32, -- email enroll_date date, -- 开通日期 close_date date, -- 暂停日期 payment_style number(2, -- 付款方式(0:现金;1:银行转帐;2:邮局汇款;3:其他 career varchar2(32, -- 职业 nationality varchar2(32, -- 国籍(系统中在本字段记录省份 gender varchar2(8, -- 性别 company varchar2(32, -- 公司名称 address varchar2(32, -- 地址post_code varchar2(16 -- 邮编 ; /****************************************************************** t_users_services 用户业务帐号表(用于保存用户业务帐号信息) *******************************************************************/ drop table t_user_servic es; create table t_user_services ( id number(32 primary key, -- 主键编码 user_id number(32 references t_users(id, -- 用户表主键编码 lab_login_name varchar2(10, -- 用户业务帐号 lab_login_password varchar2(32, -- 用户业务帐号密码 lab_ip varchar2(32 not null, -- 服务器编码 user_status number(2, -- 业务帐号状态(0:正常;1:暂停; 2:删除) pricing_id number references t_pricing(pricing_id, -- 资费编码 enroll_date date, -- 开通日期 close_date date -- 暂停日期 ; create unique index ind_us on t_user_services(lab_login_name, lab_ip 1. 根据三张表S_EMP、S_DEPT、S_REGION 三张表完成各个部门的平均工资,显示不要部门号而是该部门的详细部门名称,要求两种写法? select max(d.name,max(r.name,round(avg(e.salary,2 from s_emp e,s_dept d,s_region r where e.dept_id = d.id and d.region_id = r.id group by e.dept_id 2. 用多种写法完成哪些人是领导? select distinct m.first_name from s_emp e,s_emp m where e.manager_id = m.id select first_name from s_emp where id in (select manager_id from s_emp select first_name from s_emp m where exists ( select 1 from s_emp e where m.id = e.manager_id 3. 用多种写法完成哪些人是员工? select m.first_name from s_emp e,s_emp m where e.manager_id(+ = m.id and e.first_name is null select first_name from s_emp where id not in (select manager_id from s_emp where manager_id is not null select first_name from s_emp m where not exists (select 1 from s_emp e where m.id = e.manager_id 4. 用多种写法完成哪些部门有员工? select distinct d.dname from emp e,dept d where e.deptno = d.deptno select dname from dept where deptno in (select deptno from emp select dname from dept d where exists (select 1 from emp e where d.deptno = e.deptno 5. 用多种写法完成哪些部门没有员工? select dname from emp e,dept d where e.deptno(+ = d.deptno and e.deptno is null select dname from dept where deptno not in (select deptno from emp where deptno is not null select dname from dept d where not exists (select 1 from emp e where d.deptno = e.deptno 6. 用多种写法完成哪些部门没有名字是‘Ben’的员工? select id from s_dept where id not in (select dept_id from s_emp where first_name = 'Ben' select d.id from s_emp e,s_dept d where e.dept_id(+ = d.id and e.first_name(+ = 'Ben' and e.first_name is null select id from s_dept d where not exists (select 1 from s_emp e where d.id = e.dept_id and e.first_name = 'Ben' 7. 用多种写法完成哪些地区有员工? select distin ct r.name from s_emp e,s_dept d,s_region r where e.dept_id = d.id and d.region_id = r.id select name from s_region where id in (select region_id from s_emp e,s_dept d where e.dept_id = d.id select name from s_region r where exists (select 1 from s_emp e,s_dept d where e.dept_id = d.id and d.region_id = r.id select name from s_region r where exists (select 1 from s_dept d where exists (select 1 from s_emp e where e.dept_id = d.id and d.region_id = r.id 8. 讨论内连接、外连接、非关联子查询(in/not in)、关联子查询(exists/not exists)能解决哪些类似的问题? 9. 用多种写法完成哪些学生选修了所有课程? select max(name from stu s,stu_cou sc where s.id = sc.sid group by s.id having count(sc.cid = (select count(id from cou select a.name from (select max(name name,count(sc.sid cnt from stu s,stu_cou sc where s.id = sc.sid group by s.id a,(select count(id cnt from cou b where a.cnt = b.cnt select name from stu s where not exists (select 1 from cou c where not exists (select 1 from stu_cou sc where s.id = sc.sid and c.id = sc.cid 10. 用多种写法完成哪些地区没有员工? select name from s_region where id not in (select region_id from s_emp e,s_dept d where e.dept_id = d.id select r.name from s_region r,(select d.region_id from s_emp e,s_dept d where e.dept_id = d.id a where r.id = a.region_id(+ and a.region_id is null select name from s_region r where not exists (select 1 from (select d.region_id from s_emp e,s_dept d where e.dept_id = d.id a where r.id = a.region_id 11. 42部门员工的纳税情况,税率如下: 0.00 ——1999.99 0.00 2000.00——3999.99 0.09 4000.00——5999.99 0.20 6000.00——7999.99 0.30 8000.00——9999.99 0.40 10000.00——11999.99 0.42 12000.00——139999.99 0.44 140000.00 or greater 0.45 分别用decode和case when实现。 select first_name,salary,decode(trunc(salary/2000,0,0, 1,0.09, 2,0.20, 3,0.30, 4,0.40, 5,0.42, 6,0.44, 0.45 from s_emp select first_name,salary,(case when salary between 0 and 1999.99 then 0.00 when salary between 2000 and 3999.99 then 0.09 when salary between 4000 and 5999.99 then 0.20 when salary between 6000 and 7999.99 then 0.30 when salary between 8000 and 9999.99 then 0.40 when salary between 10000 and 11999.99 then 0.42 when salary between 12000 and 13999.99 then 0.44 else 0.45 end tax from s_emp 14. 从emp表和dept表中完成如下查询:各个部门不同职位的工资总和?输出如下: JOB DEPT_10 DEPT_20 DEPT_30 DEPT_40 --------- ---------- ---------- ---------- ---------- --------- ---------- ---------- --- CLERK 1 2 1 SALESMAN 4 PRESIDENT 1 MANAGER 1 1 1 ANALYST 2 select count(deco de(deptno,10,sal,count(decode(deptno,20,sal, count(decode(deptno,30,sal,count(decode (deptno,40,sal from emp group by job select job,max(decode(deptno,10,cnt dept_10, max(decode(deptno,20,cnt deptno_20, max(decode(deptno,30,cnt dept_30, max(decode(deptno,40,cnt deptno_40 from (select job,deptno,count(sal cnt from emp group by job,deptno group by job 15. 数据如下:日期事务所标章NO.(varchar2 12/12 北京 1 12/12 北京 2 12/12 北京 3 12/13 河北 51 12/13 河北 52 12/13 河北 53 12/13 河北 60 12/13 河北 70 输出结果如下: 只要日期和事务所一样,标章号连续就把记录合并日期事务所标章NO.(varchar2 12/12 北京 1-3 12/13 河北 51-53 12/13 河北 60 12/13 河北 70 select b.日期,b.事务所,min(b.标章号,max(b.标章号 from ( select a.*,to_number(a.标章号-rownum cc from ( select * from t order by 日期,事务所,标章号 a b group by b.日期,b.事务所,b.cc 16. 有一个权限表XT_QXMX:(权限序号,权限名称 QXXH QXMC 1 发文填写 2 发文审批 3 收文填写 4 收文审批权限-人员对应表XT_QXRY,如果某人有某个权限,则在此表有条记录 (序号,权限序号,用户名称 XH QXXH YHMC 1 2 张三 2 3 张三 (张三有两个权限,发文审 批和收文填写写一个查询:列出张三有哪些权限,同时列出所有的权限.查询结果如下: QXXH QXMC 是否有此权限 1 发文填写 false 2 发文审批 true 3 收文填写 true 4 收文审批 false select m.qxxh,m.qxmc,decode(r.yhmc,'张三','true','false' from xt_qxmx m ,xt_qxry r where m.qxxh = r.qxxh(+ and r.yhmc(+ = '张三' order by m.qxxh