?? 數據庫實驗1.txt
字號:
CREATE TABLE emp046
(empno NUMBER NOT NULL,
ename CHAR(10) ,
job CHAR(9),
mgr NUMBER (4),
hiredate DATE ,
sal NUMBER(10,2),
comm NUMBER(9,0) ,
deptno NUMBER(4) NOT NULL);
---------------------------------------------------------------------
insert into EMP046(empno,ename,job,mgr,hiredate,sal,deptno)
values(7369,'SMITH','CLERK',7902,'17-12月-1990',13750,20);
insert into EMP046(empno,ename,job,mgr,hiredate,sal,comm,deptno)
values(7499,'ALLEN','SALESMAN',7698,'20-2月-1989',19000,6400,30);
insert into EMP046(empno,ename,job,mgr,hiredate,sal,comm,deptno)
values(7521,'WARD','SALESMAN',7698,'22-2月-1993',18500,4250,30);
insert into EMP046(empno,ename,job,mgr,hiredate,sal,deptno)
values(7566,'JONES','MANAGER',7839,'02-4月-1989',26850,20);
insert into EMP046(empno,ename,job,mgr,hiredate,sal,comm,deptno)
values(7654,'MARTIN','SALESMAN',7698,'28-9月-1997',15675,3500,30);
insert into EMP046(empno,ename,job,mgr,hiredate,sal,deptno)
values(7698,'BLAKE','MANAGER',7839,'01-5月-1990',24000,30);
insert into EMP046(empno,ename,job,mgr,hiredate,sal,deptno)
values(7782,'CLARK','MANAGER',7839,'09-6月-1988',27500,10);
insert into EMP046(empno,ename,job,mgr,hiredate,sal,deptno)
values(7788,'SCOTT','ANALYST',7566,'19-4月-1987',19500,20);
insert into EMP046(empno,ename,job,hiredate,sal,deptno)
values(7839,'KING','PRESIDENT','17-11月-1983',82500,10);
insert into EMP046(empno,ename,job,mgr,hiredate,sal,comm,deptno)
values(7844,'TURNER','SALESMAN',7698,'08-9月-1992',18500,6250,30);
insert into EMP046(empno,ename,job,mgr,hiredate,sal,deptno)
values(7876,'ADAMS','CLERK',7788,'23-5月-1996',11900,20);
insert into EMP046(empno,ename,job,mgr,hiredate,sal,deptno)
values(7900,'JAMES','CLERK',7698,'03-12月-1995',12500,30);
insert into EMP046(empno,ename,job,mgr,hiredate,sal,deptno)
values(7902,'FORD','ANALYST',7566,'03-12月-1991',21500,20);
insert into EMP046(empno,ename,job,mgr,hiredate,sal,deptno)
values(7934,'MILLER','CLERK',7782,'23-1月-1995',13250,10);
insert into EMP046(empno,ename,job,mgr,hiredate,sal,comm,deptno)
values(3258,'GREEN','SALESMAN',4422,'24-7月-1995',18500,2750,50);
insert into EMP046(empno,ename,job,mgr,hiredate,sal,deptno)
values(4422,'STEVENS','MANAGER',7839,'14-1月-1994',24750,50);
insert into EMP046(empno,ename,job,mgr,hiredate,sal,deptno)
values(6548,'BARNES','CLERK',4422,'16-1月-1995',11950,50);
----------------------------------------------------------------------
insert into EMP046(empno,ename,job,mgr,hiredate,sal, comm,deptno)
values(7369,'SMITH','CLERK',7902,'17-12月-1990',13750,0,20);
insert into EMP046(empno,ename,job,mgr,hiredate,sal,comm,deptno)
values(7499,'ALLEN','SALESMAN',7698,'20-2月-1989',19000,6400,30);
insert into EMP046(empno,ename,job,mgr,hiredate,sal,comm,deptno)
values(7521,'WARD','SALESMAN',7698,'22-2月-1993',18500,4250,30);
insert into EMP046(empno,ename,job,mgr,hiredate,sal,comm,deptno)
values(7566,'JONES','MANAGER',7839,'02-4月-1989',26850,0,20);
insert into EMP046(empno,ename,job,mgr,hiredate,sal,comm,deptno)
values(7654,'MARTIN','SALESMAN',7698,'28-9月-1997',15675,3500,30);
insert into EMP046(empno,ename,job,mgr,hiredate,sal,comm,deptno)
values(7698,'BLAKE','MANAGER',7839,'01-5月-1990',24000,0,30);
insert into EMP046(empno,ename,job,mgr,hiredate,sal,comm,deptno)
values(7782,'CLARK','MANAGER',7839,'09-6月-1988',27500,0,10);
insert into EMP046(empno,ename,job,mgr,hiredate,sal,comm,deptno)
values(7788,'SCOTT','ANALYST',7566,'19-4月-1987',19500,0,20);
insert into EMP046(empno,ename,job,hiredate,sal,comm,deptno)
values(7839,'KING','PRESIDENT','17-11月-1983',82500,0,10);
insert into EMP046(empno,ename,job,mgr,hiredate,sal,comm,deptno)
values(7844,'TURNER','SALESMAN',7698,'08-9月-1992',18500,6250,30);
insert into EMP046(empno,ename,job,mgr,hiredate,sal,comm,deptno)
values(7876,'ADAMS','CLERK',7788,'23-5月-1996',11900,0,20);
insert into EMP046(empno,ename,job,mgr,hiredate,sal,comm,deptno)
values(7900,'JAMES','CLERK',7698,'03-12月-1995',12500,0,30);
insert into EMP046(empno,ename,job,mgr,hiredate,sal,comm,deptno)
values(7902,'FORD','ANALYST',7566,'03-12月-1991',21500,0,20);
insert into EMP046(empno,ename,job,mgr,hiredate,sal,comm,deptno)
values(7934,'MILLER','CLERK',7782,'23-1月-1995',13250,0,10);
insert into EMP046(empno,ename,job,mgr,hiredate,sal,comm,deptno)
values(3258,'GREEN','SALESMAN',4422,'24-7月-1995',18500,2750,50);
insert into EMP046(empno,ename,job,mgr,hiredate,sal,comm,deptno)
values(4422,'STEVENS','MANAGER',7839,'14-1月-1994',24750,0,50);
insert into EMP046(empno,ename,job,mgr,hiredate,sal,comm,deptno)
values(6548,'BARNES','CLERK',4422,'16-1月-1995',11950,0,50);
---------------------------------------------------------------------
CREATE TABLE dept046
(deptno NUMBER NOT NULL,
dname CHAR(10),
loc CHAR(10));
----------------------------------------------------------------------
insert into DEPT046(deptno,dname,loc)
values(10,'ACCOUNTING','LONDON');
insert into DEPT046(deptno,dname,loc)
values(20,'RESEARCH ','PRESTON');
insert into DEPT046(deptno,dname,loc)
values(30,'SALES ','LIVERPOOL');
insert into DEPT046(deptno,dname,loc)
values(40,'OPERATIONS','STAFFORD');
insert into DEPT046(deptno,dname,loc)
values(50,'MARKETING', 'LUTON');
----------------------------------------------------------------------
EX1
1 SELECT *FROM emp046
2 SELECT*FROM dept046
3 SELECT ename,empno,sal,deptno FROM emp046
4 SELECT *FROM emp046
WHERE deptno=10 OR deptno=30
5
SELECT DISTINCT job FROM emp046
6. SELECT ename FROM emp046
WHERE sal<20000
----------------------------------------------------------------------
EX2
1 SELECT ename,sal FROM emp046, dept046
WHERE emp046.deptno = dept046.deptno
AND loc = 'LUTON';
2 SELECT * FROM emp046, dept046
WHERE emp046.deptno = dept046.deptno
ORDER BY dept046.deptno;
3 SELECT ename FROM emp046, dept046
WHERE emp046.deptno = dept046.deptno
AND job = 'SALESMAN'
AND dname='SALES';
----------------------------------------------------------------------
EX3
6 SELECT AVG(comm) FROM emp046
SELECT AVG(comm) FROM emp46
9 SELECT TRUNC(SAL/22, 0) FROM emp046
WHERE deptno=30;
SELECT TRUNC(SAL/176, 0) FROM emp046
WHERE deptno=30;
----------------------------------------------------------------------
EX4
4 SELECT ename FROM emp046
WHERE TO_CHAR(HIREDATE,'DAY')='星期二'
----------------------------------------------------------------------
5 SELECT ename FROM EMP046
WHERE MONTHS_BETWEEN(sysdate, hiredate)>12*16;
----------------------------------------------------------------------
8 SELECT hiredate,
next_day(last_day(NEXT_day(ADD_MONTHS(hiredate,1),6))-7,6)
FROM emp046;
----------------------------------------------------------------------
EX5
1 SELECT deptno,AVG(SAL) FROM emp046
GROUP BY deptno
---------------------------------------------------------
(3) SELECT dname,COUNT(empno),AVG(sal*12) FROM emp046,dept046
WHERE emp046.deptno=dept046.deptno
GROUP BY DNAME,JOB
---------------------------------------------------------
(5)SELECT dname FROM emp046,dept046
WHERE EMP046.DEPTNO=DEPT046.DEPTNO
GROUP BY DNAME
HAVING AVG(COMM)>0.25*AVG(SAL)
---------------------------------------------------------
(6)SELECT DNAME,12*AVG(SAL) FROM EMP046,DEPT046
WHERE EMP046.DEPTNO=DEPT046.DEPTNO
AND JOB NOT IN ('MANAGERS' , 'PRESIDENT')
GROUP BY DNAME
------------------------------------------------------------
EX6
(1) SELECT ename,job FROM emp046
WHERE job=(SELECT JOB FROM emp046
WHERE ename='JONES')
AND ename!='JONES'
-----------------------------------------------------------------
(2) SELECT ename FROM EMP046
WHERE JOB IN(SELECT JOB FROM EMP046
WHERE DEPTNO=30)
AND DEPTNO=10
--------------------------------------------------------------------
(4) SELECT ename FROM emp046
WHERE
JOB IN(SELECT JOB FROM EMP046,DEPT046
WHERE emp046.deptno=dept046.deptno
AND DNAME='SALES')
AND DEPTNO=10
--------------------------------------------------------------------
(5) SELECT ename FROM emp046,dept046
WHERE emp046.deptno=dept046.deptno
AND JOB IN(SELECT JOB FROM EMP046
WHERE ename='ALLEN')
AND LOC='LIVERPOOL'
GROUP BY ename
?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -