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)--查询有重复的
