上机

--上机二
--创建employee表
CREATE TABLE employee
(empno NUMBER(4) NOT null,
empname VARCHAR(10),
job VARCHAR(9) ,
mgr NUMBER(4),
hiredate DATE,
sal NUMBER(7,2),
comm NUMBER(7,2),
deptno NUMBER(2)
);
--添加数据
INSERT INTO employee select * from scott.emp ;
--添加约束
ALTER TABLE employee
ADD constraint FK_deptno
FOREIGN KEY(deptno) REFERENCES dept(deptno);
--添加列
ALTER TABLE employee
add(
qq VARCHAR(32),
ww varchar(32)
);
--删除列
ALTER TABLE employee
DROP COLUMN ww;
--薪水的排序
select * from employee
ORDER BY sal DESC
--上机三
--分页
select * from (
select empname,ROWNUM rn from
(
select * from employee
ORDER BY sal DESC
)tmpe
where ROWNUM<=10
) tmpes
where rn>=6;
--上机四
select * from
(
select empname,sal,
ROW_NUMBER() over(PARTITION BY DEPTNO ORDER BY SAL DESC) aaa
FROM employee
)tmpe
where aaa=2