Y2 基于SSH框架的企业级应用开发 第一章 上机+课后

select * from employee
--上机2
SQL>CREATE TABLE employee
    (empno NUMBER(4) NOT NULL   ,--员工编号
     ename VARCHAR2(10),--员工姓名
     job VARCHAR2(9),--员工工种
     mgr NUMBER (4),--上级经理编号
     hiredate DATE ,--受雇日期
     sal NUMBER(7,2),--员工薪水
     comm NUMBER(7,2),--福利
     deptno NUMBER(2)--部门编号
    );
    INSERT INTO employee VALUES(1000,'张三','销售',2000,to_date('1996-12-03','yyyy-mm-dd') ,3223,222,10);
    INSERT INTO employee VALUES(1001,'李四','推销',2001,to_date('1923-07-21','yyyy-mm-dd'),3432,2122,11);
    INSERT INTO employee VALUES(1002,'王五','售票',2002,to_date('1965-11-23','yyyy-mm-dd'),2312,4322,12);
    INSERT INTO employee VALUES(1003,'赵柳','销售',2003,to_date('1696-04-03','yyyy-mm-dd') ,5432,1234,10);
    INSERT INTO employee VALUES(1004,'小王','推广',2004,to_date('2018-02-21','yyyy-mm-dd'),4444,123,11);
    INSERT INTO employee VALUES(1005,'丽丽','服务',2005,to_date('1233-12-14','yyyy-mm-dd'),3422,1232,12);
    INSERT INTO employee VALUES(1006,'小美','销售',2003,to_date('1696-04-03','yyyy-mm-dd') ,5432,343,10);
    INSERT INTO employee VALUES(1007,'sd','ds',2005,to_date('1233-12-31','yyyy-mm-dd'),2222,22,12);
    
    CREATE TABLE dept
    (deptno NUMBER(2) not　null   ,
     dname VARCHAR2(9)
      );
     alter table employee add constraint fk_deptno foreign key(deptno) references dept(deptno) --外键约束
     alter table employee   add constraint pk_empno primary key (empno);--主键约束
     ALTER TABLE employee ADD (empTel_no VARCHAR2(12), empAddress VARCHAR2(20));--添加列
     ALTER TABLE employee drop(empTel_no,empAddress);--删除列
     SELECT * from employee order by sal desc --降序查询
--上机3 
--select * from
--   (select a.*,rownum r from
--      (select * from employee e order by e.sal desc) a
--   ) b 
--where b.r between 5 and 6--分页查询

 select * from 
(select t.*,rownum rn from 
(select e.empno,e.sal from employee e order by sal desc) t
where rownum <= 8) tt where rn>4

--上机4
select * from (
select ename,deptno,sal,
      dense_rank() over (partition by deptno order by sal desc) r
      from employee
)e  where e.r=2
     

 
--上机5
1)
select ename 姓名  ,hiredate , to_char(round(hiredate,'year'),'yyyy') 年度   from employee 

2)

select deptno,ename,count(deptno) from employee group by deptno,ename having count(deptno)>=1
3)

  select *  from employee where sal>(select sal from employee where ename='张三')
 
4)
select e.ename ,d.dname from employee e   join dept d on e.deptno=d.deptno and e.job='销售'


5)
select  job , min(sal) from employee  group by job having min(sal)>1500;
6) 
select  * from employee where hiredate = last_day(hiredate);




--简答1
1)
create table orders(
  order_id number(12) not null primary key,
  order_date date not null,
  order_mode varchar2(8),
  customer_id number(6) not null,
  order_status number(2),
  order_total number(8,2),
  sales_rep_id number(6),
  promotion_id number(6)
);
create table customers(
  customers_id number(6) not null,
  cust_first_name varchar2(20) not null,
  cust_last_name varchar2(20) not null,
  nls_language varchar2(3),
  nls_territory varchar2(30),
  credit_limit number(9,2),
  cust_email varchar(30),
  account_mgr_id number(6),
  marital_status varchar2(30),
  gender char(1)
);

2)  
select nls_territory from customers group by nls_territory having count(NLS_TERRITORY)=1
3)
select o.order_id,c.cust_last_name,e.ename from orders o join customers c on o.customer_id=c.customers_id 
join employee e on e.empno=c.account_mgr_id where o.order_total between 21 and 2222
4)
select e.ename,e.sal from customers c join employee e on e.empno=c.account_mgr_id where  c.nls_territory='中国'
5）
select customers_id,nls_language from customers where nls_territory='中国' or nls_territory='英国'
6)

7)select * from customers where cust_last_name like 'f%'
-- SELECT * FROM customers ORDER BY NLSSORT('z','NLS_SORT = SCHINESE_PINYIN_M') 
8)
select cust_last_name || '' || cust_first_name 姓名,customers_id 订单编号 from customers 

--简答2
1)
select empno, max(sal),min(sal) from employee where  sal between 4444 and 5555 group by empno

--简答3
select deptno,job ,max(sal) from employee group by deptno,job



课上补充
--删除重复的数据只保留一条
delete from stuinfo5 where rowid not in
(select max(rowid) from stuinfo5
group by stuname, stuage
having count(stuname)=1--查询只有一个的
union
select max(rowid)  from stuinfo5
group by stuname, stuage
having count(stuname)>1)--查询有重复的