set termout off
rem ===================================================================
rem Script to create or repair the following tables:
rem - dept table
rem - emp table
rem - salgrade table
rem First, drop the tables whether existing or not.
rem Tables are created including constraints as relevant for the exercises.
rem
rem
rem Siegersma/De Bruyn 2010
rem
rem This script is created for Oracle 7 or later (at least Oracle 10).
rem ===================================================================
alter session
set nls_date_format = 'dd-mm-yyyy';
set linesize 120;
set pagesize 20;
drop table emp cascade constraints;
drop table dept cascade constraints;
drop table salgrade cascade constraints;
set termout on
Prompt Creating the DEPT table...
set termout off
set termout on
Prompt Creating the EMP table....
set termout off
create table emp
( empno varchar2(4) constraint PK_EMP primary key
, ename varchar2(10) constraint NN_ENAME not null
constraint UC_ENAME
check (ename = upper(ename))
, job varchar2(9) constraint NN_job not null
constraint UC_job
check (job = upper(job))
, mgr varchar2(4) constraint FK_MGR
references emp(empno) disable
, hiredate date default sysdate
constraint NN_HIREDATE not null
, sal number(7,2) constraint NN_SAL not null
, comm number(7,2)
, deptno number(2) constraint NN_DEPTNO not null
constraint FK_DEPTNO references dept
, constraint COMM_CHK check
((job = 'SALESMAN' and comm is not null)
or
(job != 'SALESMAN' and comm is null))
, constraint MGR_CHK check
((job = 'PRESIDENT' and mgr is null)
or
(job != 'PRESIDENT' and mgr is not null))
) ;
insert into emp values
(7369,'SMITH','CLERK',7902,TO_DATE('12/17/1980','MM/DD/YYYY'),800,NULL,20) ;
insert into emp values
(7499,'ALLEN','SALESMAN',7698, TO_DATE('02/20/1981','MM/DD/YYYY'),1600,300,30) ;
insert into emp values
(7521,'WARD','SALESMAN',7698, TO_DATE('02/22/1981','MM/DD/YYYY'),1250,500,30) ;
insert into emp values
(7566,'JONES','MANAGER',7839, TO_DATE('04/02/1981','MM/DD/YYYY'),2975,NULL,20) ;
insert into emp values
(7654,'MARTIN','SALESMAN',7698,TO_DATE('09/28/1981','MM/DD/YYYY'),1250,1400,30);
insert into emp values
(7698,'BLAKE','MANAGER',7839, TO_DATE('05/01/1981','MM/DD/YYYY'),2850,NULL,30) ;
insert into emp values
(7782,'CLARK','MANAGER',7839,TO_DATE('06/09/1981','MM/DD/YYYY'),2450,NULL,10) ;
insert into emp values
(7788,'SCOTT','ANALYST',7566, TO_DATE('12/09/1982','MM/DD/YYYY'),3000,NULL,20) ;
insert into emp values
(7839,'KING','PRESIDENT',NULL,TO_DATE('11/17/1981','MM/DD/YYYY'),5000,NULL,10) ;
insert into emp values
(7844,'TURNER','SALESMAN',7698, TO_DATE('09/08/1981','MM/DD/YYYY'),1500,0,30) ;
insert into emp values
(7876,'ADAMS','CLERK',7788,TO_DATE('01/12/1983','MM/DD/YYYY'),1100,NULL,20) ;
insert into emp values
(7900,'JAMES','CLERK',7698, TO_DATE('12/03/1981','MM/DD/YYYY'),950,NULL,30) ;
insert into emp values
(7902,'FORD','ANALYST',7566,TO_DATE('12/03/1981','MM/DD/YYYY'),3000,NULL,20) ;
insert into emp values
(7934,'MILLER','CLERK',7782, TO_DATE('01/23/1982','MM/DD/YYYY'),1300,NULL,10) ;
rem Nu pas deze constraint aanzetten, want anders was de volgorde voor
rem de invoer van werknemers van belang voor het invoeren.
alter table emp
enable constraint fk_mgr ;
set termout on
Prompt Creating the SALGRADE table.....
set termout off
create table salgrade
( grade number constraint PK_salgrade primary key
, losal number constraint NN_losal not null
, hisal number constraint NN_hisal not null
) ;
insert into salgrade values
( 1, 700, 1200 ) ;
insert into salgrade values
( 2, 1201, 1400 ) ;
insert into salgrade values
( 3, 1401, 2000 ) ;
insert into salgrade values
( 4, 2001, 3000 ) ;
insert into salgrade values
( 5, 3001, 9999 ) ;
set termout on
********************************************************************************************
รบกวนช่วยหาคำตอบของคำถามให้ทีครับผม ผมไม่ได้เรื่องเลยเรื่องเขียนคำสั่ง
ด้านบนคือ สคริปส์ ที่ผมเรียนอยู่ครับคำถามมีอยู่ว่า
0. What date is it now?
1. Create a query that return the date 60 days ago
2. Create a query that return the department name and the average salary of that department
3. Create a complete list of department and the number of employee that work in the department
Load balance : Server 02
