Oracle备课笔记
一、了解数据库管理系统的不同类型
1、数据存储系统
将数据存储在计算机上的概念就叫数据存储系统 。
数据以文本、数字、图片或声音等形式存储。
2、数据库是一组在逻辑上相关的信息的集合 。
表以行和列的形式保存数据。
实现数据库管理任务的软件叫做数据库管理系统(DBMS)。
3、DBMS的不同类型为 ：RDBMS和ORDBMS
RDBMS是由E.F.Codd博士在1970年首先提出的。
RDBMS 的例子包括 Sybase, Microsoft Access, Ingres, Oracle, 及 SQL Server。
在RDBMS的表中 ，每个属性叫做一列，每条记录被称为一行。
4、ORDBMS
ORDBMS将面向对象的前端与RDBMS集成起来 。
5、面向对象的概念
在面向对象的方法中，系统被当作一组真实世界中对象的集合 。
这些对象以类来分类 ，类就是拥有共同属性和行为的一组对象 。
面向对象方法具有以下特性 ：抽象、继承、封装、多态性
二、了解Oracle9i的特性
可以使用结构化查询语言(SQL)或面向对象的编程语言来访问数据 。
可使用内置数据类型在表中存储数据存储。
将复合结构当作单个实体进行存储 。
使用ORDBMS 的继承特性 。
允许创建许多同名函数。
可扩展性使得用户能够定义新的数据类型来存储复合数据。
允许创建对象视图。
三、了解ER 图的组成
1、实体 - 关系(ER)图: 以图形方式表示数据库的逻辑结构，可被看作是数据库的蓝图 。
2、画ER图的指南是：
(1)确定各实体
(2)确定实体的属性
(3)确定实体之间的关系,联系的类型: 一对一、一对多或多对一、多对多
四、ER图映射成表的规则：
1、独立存在的实体转换成一个表
2、属性转换成列（字段），可唯一标识一行的属性设置为主键
3、一对一关系，可在两表中互设为主键，也可将两表合并
例如：部门和部分负责人
4、一对多关系，第一个表的主键可作为第二个表的外键
例如：班级和学生
5、多对多关系，关系转换成表，原来的两表主键在新表中用做外键
例如：学生和课程
四、使用 SQL*Plus 语句
1、SQL语句介绍
SQL用来检索和维护数据库中数据 。
使用SQL的优点 ：
SQL是所有RDBMS使用的公共语言 。
SQL不遵循任何特定的执行模式，它一次可以访问多个记录 。
SQL使用简单的维护数据的命令。
SQL语句非常接近英语自然语言，因此，易学易懂。
2、查询使用类似英语的语句来从数据库中检索信息 。
SQL SQL语句和查询命令用来 ：
创建和维护数据库 。
创建和维护表 。
删除数据库和表 。
基于条件查询数据。
按所需格式提供查询输出。
创建视图 ，报告，和表单。
3、数据定义语言 (DDL) ：包含一组命令，用来创建数据库对象，如表等 。
DDL可用于： 创建对象、改变对象 、撤消对象
4、数据操纵语言 (DML) ：用来操纵表数据 ，包含INSERT、UPDATE和 DELETE等命令 。
DML 可用于： 存储数据、更新数据、删除数据
5、SELECT 语句
用于检索表中所有数据，或特定范围的数据。
语法
SELECT {* | select_list} FROM tablename;
五、SQLPlus的使用介绍
一、显示格式化的查询输出
1、显示表的指定列
语法如下：
SELECT column_name[, column_name] FROM tablename;
2、使用列别名
可以给这些列取些友善的标题来取代表中缺省列标题。其语法是：
SELECT column_name column_heading [, column_name…] FROM tablename;
举例：
SELECT cName "Recruiter Name" FROM CONTRACTRECRUITER
或者使用
SELECT cName AS "Recruiter Name" FROM CONTRACTRECRUITER
3.DISTINCT子句
在SELECT 语句中用来观看表列中唯一的值
语法如下::
SELECT DISTINCT column_name FROM tablename;
二、识别不同类型的运算符
算术运算符：+、-、*、/、%
比较运算符：=、<、>、>=、<=、!=
并置运算符：||
逻辑运算符：AND、OR、NOT
其他运算符：BETWEEN...AND、IN
三、检索表中所选的行
1、SELECT … WHERE 语句
用于检索满足特定条件的数据
语法
SELECT select_list FROM tablename WHERE condition
2、检索带匹配模式的数据
为用匹配字符模式来检索数据，你就要在查询中使用模式匹配运算符
可以和模式匹配运算符一起使用的是两个特殊的字符:
通配符(Wild Card Character)：%,匹配任意数量的任意字符
定位标记字符(Position Marker Character):_，匹配单个的任意字符
举例：
SELECT vFirstName FROM Employee WHERE vFirstName LIKE '%a%a%';
SELECT vFirstName FROM Employee WHERE vFirstName LIKE ‘_s%’;
3、检索包含空白值的行
IS NULL关键字:
用来检查表中是否包含NULL值的空白列或行.
语法
SELECT select_list FROM tablename WHERE column_name IS NULL;
SELECT select_list FROM tablename WHERE column_name IS NOT NULL;
四、对查询输出进行排序
ORDER BY 子句：
在 SELECT 语句中用于以特定次序检索数据。
语法
SELECT select_list FROM tablename [ORDER BY column_name][ASC] or [DESC];
举例：
SELECT cPositionCode, vDescription FROM Position ORDER BY cPositionCode ASC;
一、使用SELECT语句中的函数
1、函数介绍 :函数 用于在数据上执行某些操作，如修改显示的数据、转换数据类型或进行计算等。
可分类为：
单行函数
多行函数
2、字符串函数
INITCAP
LOWER
UPPER
LTRIM
RTRIM
CONCAT
SUBSTR
INSTR
LPAD
RPAD
LENGTH
3、数值函数
ABS
CEIL
FLOOR
POWER
MOD
ROUND
4、日期函数
ADD_MONTHS
GREATEST
LEAST
LAST_DAY
MONTHS_BETWEEN
NEXT_DAY
二、使用转换函数
1、隐式转换
此转换类型中，数据类型将根据SELECT语句中函数的要求自动进行转换。
2、显式转换
此转换类型中，数据类型的转换通过一些预定义函数完成。转换函数有：
日期格式化函数
数值格式化函数
3、日期格式化函数
TO_CHAR
TO_DATE
ROUND
TRUNC
4、数值格式化函数
TO_NUMBER
5、使用通用格式化函数
NVL 函数
用来以某个确定的值来替代在NUMBER、DATE、CHAR或VARCHAR2等数据类型的列中存储的空值。
语法
NVL(value, substitute)
6、Coalesce函数
用来显示基于存储在表中NULL值的记录。
语法
Coalesce (exp_name1, exp_name2….. exp_n)
三、使用条件表达式
用来依据条件来执行语句。
1、IF…THEN…ELSE 子句
用来据指定条件的运算结果来执行SQL语句
语法
IF boolean_expression {sql_statement | statement_block}
ELSE boolean_ expression {sql_statement | statement_block}
2、CASE子句
一、了解各种联接类型
1、联接引言：联接用来检索多表中记录.
2、语法
SELECT table1.column_name, table2.column_name FROM table1, table2 WHERE table1.column1 join_operator table2.column2;
3、Oracle提供的不同联接类型有:
EQUIJOIN（等值联接）
NONEQUIJOIN（非等值联接）
NATURAL JOIN （自然联接）
CROSS JOIN （交叉联接）
OUTER JOIN （外部联接）
SELF JOIN （自联接）
二、使用不同类型的检索数据的联接
(一)EQUIJOIN（等值联接）
(1)在比较出现在公共列的值后才显示多表中数据,又称为简单联接或内部联接.
语法
SELECT column_name FROM table1 JOIN table2 ON table1.ref_column_name join_operator table2.ref_column_name;
(2)使用 WHERE子句
当基于公共列联接两个表时，使用WHERE 子句来指出条件.
例子
SELECT cNewspaperName,vRegion, Country.cCountry FROM NewsPaper, Country WHERE NewsPaper.cCountryCode = Country.cCountryCode AND cNewspaperName = 'Texas Times';
(3)联接多个表
用联接来联接两个以上的表.
例子
SELECT e.vFirstName, c.cName, u.cCountry FROM ExternalCandidate e , ContractRecruiter c, Country u WHERE e.cContractRecruiterCode = c.cContractRecruiterCode AND e.cCountryCode = u.cCountryCode;
(二)NONEQUIJOIN（非等值联接）
联接两个或多个表时，在条件中使用非等号（=）的运算符..
使用如>、 <、 >=、 <=、 !=、between…and或 in等比较运算符.
例子
SELECT ExternalCandidate.vFirstName, vLastName, Rating.cCandidateRating FROM ExternalCandidate, Rating WHERE ExternalCandidate.nRating BETWEEN Rating.nLowerRating AND Rating.nHigherRating;
(三)NATURAL JOIN （自然联接）
基于两个表的同名的一个或多个列..
例子
SELECT cEmployeeCode, vFirstName, nAnnualSalary, nYear FROM Employee NATURAL JOIN AnnualSalary;
用USING子句检索记录
如果两个表中你有同名的列，那么你在联接语句里可以使用USING子句.
例子
SELECT cEmployeeCode, vFirstName, nAnnualSalary, nYear FROM Employee JOIN AnnualSalary USING (cEmployeeCode);
(四) CROSS JOIN（交叉联接）
使用关键字CROSS的包含多个表的联接.
例子
SELECT * FROM Requisition CROSS JOIN Position;
(五)OUTER JOIN （外部联接）
OUTER JOIN
用于检索一个表的所有记录和另一表中的匹配行.
语法
SELECT column_name, column_name [,column_name] FROM table_name [LEFT | RIGHT | FULL] OUTER JOIN table_name ON table_name.ref_column_name join_operator table_name.ref_column_name
(六)SELF JOIN （自联接）
SELF JOIN
用于联接在同一个表内的行.
语法
SELECT a.vFirstName “Employee”, b.vFirstName “Supervisor” FROM Employee a, Employee b WHERE a.cSupervisorCode = b.cEmployeeCode;
一、了解多行函数的不同类型
用来组合查询中多行.
多行函数的类型有:
AVG
返回n个数值的平均值.
SUM
n个值的总和.
COUNT
显示查询返回的行数
MAX
返回一个列中的最大值.
MIN
返回一个列中的最小值.
STDDEV
返回一组值的标准偏差.
VARIANCE
返回一组值的方差.
举例：SELECT COUNT (cNewspapercode) “No of Newspapers” FROM Newspaper;
二、对查询结果进行分组
GROUP BY子句
用来对表中的行进行分组..
语法
SELECT column_name1, column_name2
FROM table_name
WHERE search_condition
GROUP BY group_expression
HAVING search_condition;
在GROUP BY子句里使用组合函数
多行或组合函数可以和GROUP BY 子句一起使用.
三、检索表中被选的组合数据
HAVING 字句
用来指定GROUP BY子句执行的分组操作的条件.
语法
SELECT column_name, column_name
FROM table_name
WHERE search_condition
GROUP BY group_expression
HAVING search_condition
set serveroutput on
spool tableLog.txt
/* -- Creating Objects for Recruitment */
create table Recruitmentusers
(
cUserName char(10),
cPassword char(10)
) ;
create table Department
(
cDepartmentCode char(4) constraint dt_pk primary key,
vDepartmentName varchar(25) ,
vDepartmentHead varchar(25),
vLocation varchar(20)
);
create table Position
(
cPositionCode char(4) constraint ptv_pk primary key,
vDescription varchar(35),
nBudgetedStrength number(3),
nYear number(4),
nCurrentStrength number(2)
);
------
create table Country
(
cCountryCode char(3) constraint c_pk primary key,
cCountry char(35) not null
) ;
create table Newspaper
(
cNewspaperCode char(4) constraint np_pk primary key,
cNewspaperName char(20) not null,
vRegion varchar(20),
vTypeOfNewspaper varchar(20),
vContactPerson varchar(35),
vHOAddress varchar(35),
cCity char(20),
cState char(20),
cZip char(10),
cCountryCode char(3) references Country(cCountryCode),
cFax char(15),
cPhone char(15)
) ;
create table NewsAd
(
cNewsAdNo char(4) constraint nat_pk primary key,
cNewspaperCode char(4) references Newspaper(cNewspaperCode),
dAdStartDate date,
dDeadline date
) ;
create table JobFair
(
cJobFairCode char(4) constraint jft_pk primary key ,
vLocation varchar(35),
vJobFairCompany varchar(40),
nFee number(10,2),
dFairDate date
) ;
create table ContractRecruiter
(
cContractRecruiterCode char(4) constraint crtp_pk primary key,
cName char(35),
vAddress varchar(35),
cCity char(20),
cState char(15),
cZip char(10),
cFax char(15),
cPhone char(15),
nPercentageCharge number(2),
nTotalPaid number(10,2)
) ;
create table RecruitmentAgencies
(
cAgencyCode char(4) constraint rat_pk primary key,
cName char(20),
vAddress varchar(35),
cCity char(15),
cState char(15),
cZip char(10),
cPhone char(15),
cFax char(15),
nPercentageCharge number(2),
nTotalPaid number(10,2)
) ;
create table College
(
cCollegeCode char(4) constraint ct_pk primary key,
cCollegeName char(30) not null,
vCollegeAddress varchar(35),
cCity char(20),
cState char(20),
cZip char(10),
cPhone char(15)
) ;
create table CampusRecruitment
(
cCampusRecruitmentCode char(4) constraint cr_pk primary key,
cCollegeCode char(4) references College(cCollegeCode),
dRecruitmentStartDate date,
dRecruitmentEndDate date
) ;
create table InternalJobPosting
(
cInternalJobPostingCode char(4) constraint ijp_pk primary key,
cPositionCode char(4), /* references, Position(cPositionCode), */
nNoOfVacancies number(3) not null,
vRegion varchar(20),
dNoticeReleaseDate date not null,
dDeadline date
) ;
create table InternalCandidate
(
cCandidateCode char(6) ,
cEmployeeCode char(6) constraint emp_unq unique,
cInternalJobPostingCode char(4) references InternalJobPosting(cInternalJobPostingCode),
cPositionCodeAppliedFor char(4) references Position(cPositionCode) ,
dDateOfApplication date,
dTestDate date,
nTestScore number(3),
dInterviewDate date,
cInterviewer char(25),
vInterviewComments varchar(256),
cRating char,
cStatus char,
constraint ict_pk primary key(cCandidateCode,cEmployeeCode,cInternalJobPostingCode)
) ;
create table Employee
(
cEmployeeCode char(6) constraint etv_pk primary key,
vFirstName varchar(20),
vLastName varchar(20),
cCandidateCode char(6),
vAddress varchar(35),
cCity char(20),
cState char(20),
cZip char(10),
cCountryCode char(3) references Country(cCountryCode),
cPhone char(15),
vQualification varchar(20),
dBirthDate date,
cSex char,
cCurrentPosition char(20),
cDesignation char(20),
cEmailId char(20),
cDepartmentCode char(4) references Department(cDepartmentCode),
cRegion char(20),
vPhoto varchar(30),
dJoiningDate date,
dResignationDate date,
cSocialSecurityNo char(15) unique,
cSupervisorCode char(6)
) ;
create table MonthlySalary
(
cEmployeeCode char(6)references Employee(cEmployeeCode),
nMonthlySalary number(10,2),
dPayDate date,
nReferralBonus number(10,2),
constraint mst_pk primary key(cEmployeeCode,dPayDate)
);
create table AnnualSalary
(
cEmployeeCode char(6) references Employee(cEmployeeCode),
nAnnualSalary number(10,2),
nYear number(4),
constraint ast_pk primary key(cEmployeeCode,nYear)
);
create table ExternalCandidate
(
cCandidateCode char(6) constraint ectv_pk primary key,
vFirstName varchar(20),
vLastName varchar(20),
vAddress varchar(35),
cCity char(20),
cState char(15),
cZip char(10),
cCountryCode char(3) references Country(cCountryCode),
cPhone char(15),
cPositionCode char(4) references Position(cPositionCode),
dDateOfApplication date,
cEmployeeReferralNo char(6),
cNewsAdNo char(4) references NewsAd(cNewsAdNo),
cAgencyCode char(4) references RecruitmentAgencies(cAgencyCode),
cContractRecruiterCode char(4) references ContractRecruiter(cContractRecruiterCode),
cJobFairCode char(4) references JobFair(cJobFairCode),
cCampusRecruitmentCode char(4) references CampusRecruitment(cCampusRecruitmentCode),
cExEmployeeCode char(6),
vQualification varchar(20),
nPrevWorkExperience number,
dBirthDate date,
cSex char,
cCollegeCode char(4),
nPrevAnnualSalary number(10,2),
vPhotograph varchar(30),
vEmailId varchar(20),
cStatus char,
dTestDate date,
nTestScore number(3),
dInterviewDate date,
cInterviewer char(20),
vInterviewComments varchar(256),
nRating number(2)
);
create table EmployeeReferrals
(
cEmployeeReferralNo char(6) constraint ert_pk primary key,
cEmployeeCode char(6) references Employee(cEmployeeCode),
cCandidateCode char(6) references ExternalCandidate(cCandidateCode)
) ;
create table Skill
(
cSkillCode char(4) constraint stv_pk primary key,
vSkill varchar(35)
);
create table EmployeeSkill
(
cEmployeeCode char(6) references Employee(cEmployeeCode),
cSkillCode char(4)references Skill(cSkillCode),
constraint vest_pk primary key(cEmployeeCode,cSkillCode)
);
create table Requisition
(
cRequisitionCode char(6),
cPositionCode char(4)references position(cPositionCode),
dDateofRequisition date,
dDeadline date,
cDepartmentCode char(4),
vRegion varchar(20),
nNoOfVacancy number(3),
constraint RTP_PK primary key(cRequisitionCode,cPositionCode)
) ;
create table CandidateSkill
(
cCandidateCode char(6) references ExternalCandidate(cCandidateCode),
cSkillCode char(4) references Skill(cSkillCode),
constraint sctv_pk primary key(cCandidateCode,cSkillCode)
);
create table PositionSkill
(
cPositionCode char(4) references Position(cPositionCode),
cSkillCode char(4) references Skill(cSkillCode),
constraint pstv_pk primary key(cPositionCode,cSkillCode)
);
create table Payment
(
cSourceCode char(6) ,
nAmount number(10,2),
cChequeNo char(12),
dDate date,
constraint tp_pks primary key(cSourceCode,cChequeNo,dDate)
);
create table Rating
(
cCandidateRating char(10),
nLowerRating number(2),
nHigherRating number(2)
);
insert into recruitmentusers values ('Sandra','Adams');
insert into recruitmentusers values ('William','Perez');
insert into recruitmentusers values ('David','Cooper');
insert into department values('0001','Production','Samuel Moore','New York');
insert into department values('0002','Sales','Donald Fleming','Houston');
insert into department values('0003','Marketing','Sandra Hawkins','Addison');
insert into department values('0004','Human Resource','Bill Mayse','Abilene');
insert into department values('0005','Quality Assurance','Robin Dmello','Hampstead');
insert into department values('0007','Training','Will Smith','Columbus');
insert into department values('0008','Transportation','Dabb Mike','Columbus');
insert into department values('0009','Finance','Faber Michel','New Jersy');
insert into department values('0010','Inventory','Taber James','New Jersy');
insert into position values('0001','Sales Manager',100,1997,82 );
insert into position values('0002','Maketing Manager',100,1997,83 );
insert into position values('0003','Financial Analyst',50 ,1997,30 );
insert into position values('0004','Training Co-ord',20 ,1997,15 );
insert into position values('0005','Database Analyst',10 ,1997,8 );
insert into position values('0006','Staff Accountant',20 ,1997,18 );
insert into position values('0007','Audit Manager',20 ,1997,18 );
insert into position values('0008','Telephone Operator',20 ,1997,18 );
insert into position values('0009','Office Clerk',20 ,1997,18 );
insert into position values('0010','Legal Secretary',20 ,1997,18 );
insert into position values('0011','Administrative Assistant',20 ,1997,18 );
insert into position values('0012','Senior Receptionist',20 ,1997,18 );
insert into position values('0013','Consultant',20 ,1997,18 );
insert into position values('0014','Maintenance Technician',20 ,1997,18 );
insert into position values('0015','Receptionist',21,1997,18 );
insert into position values('0016','Sales Executive',20,1997,18 );
insert into Country values('001','United States of America');
insert into Country values('002','Albania');
insert into Country values('003','Andorra');
insert into Country values('004','Argentina');
insert into Country values('005','Austria');
insert into Country values('006','Bangladesh');
insert into Country values('007','Belgium');
insert into Country values('008','Bosnia');
insert into Country values('009','Bulgaria');
insert into Country values('010','Czech Republic');
insert into Country values('011','Denmark');
insert into Country values('012','Yugoslavia ');
insert into country values('013','France');
insert into country values('014','Gibraltar');
insert into country values('015','Greece');
insert into country values('016','Georgia');
insert into country values('017','Croatia');
insert into country values('018','Estonia');
insert into country values('019','Iceland');
insert into country values('020','Italy');
insert into country values('021','Israel');
insert into country values('022','India');
insert into country values('023','Cyprus');
insert into country values('024','Russia');
insert into country values('025','Liechtenstein');
insert into country values('026','Great Britain');
insert into country values('027','Lithuania');
insert into country values('028','Luxembourg');
insert into country values('029','Yugoslavia');
insert into country values('030','Malta');
insert into country values('031','Moldova');
insert into country values('032','Monaco');
insert into country values('033','Netherlands');
insert into country values('034','Norway');
insert into country values('035','Poland');
insert into country values('036','Portugal');
insert into country values('037','Romania');
insert into country values('038','San Marino');
insert into country values('039','Slovak Republic');
insert into country values('040','Slovenia');
insert into country values('041','Finland');
insert into country values('042','Spain');
insert into country values('043','Switzerland');
insert into country values('044','Turkey');
insert into country values('045','Ukraine');
insert into country values('046','Hungary');
insert into country values('047','Germany');
insert into country values('048','Sweden');
insert into country values('049','Latvia');
insert into country values('050','India');
/* data for newspaper*/
insert into newspaper values('0001','Texas Times','Texas','General','Jackson Demello','4723 West Alabama','Houston','Texas','77015-4568','001','(713)451-6797','(713)451-6850');
insert into newspaper values('0002','Bussiness Journal','Norton','Bussiness','Pamela Desouza','900 N State St Norton','Norton','Kansas','76754-2219','001','(785)877-2344','(785)877-2363');
insert into newspaper values('0003','Daily News','Mentor','General','Jack Miller','4896 11th ST Hill Avenue','Mentor','Ohio','44060-5102','001','(440)257-2934','(440)257-2935');
insert into newspaper values('0004','Georgia Chronicle','Georgia','General','Abair Duane','4483 victory drive','Columbus','Georgia','31905-1189','001','(706)687-0686','(706)687-0688');
insert into newspaper values('0005','Nevada Update','Nevada','general','Jack Phil','7765 La Cienega St ','Las Vegas','Nevada','89123-1707','001','(702)263-0704','(702)263-0724');
insert into newspaper values('0006','Morning News','New Jersy','General','Renard Andrew','125 Shark River Rd ','Middle Town','New Jersy','07748-3325','001','(732)708-0249','(732)708-0235');
insert into newspaper values('0007','Nevada News','Nevada','General','Ray Dean','Cambridge St','Las Vegas','Nevada','89109-4072','001','(702)369-6592','(702)369-6408');
insert into newspaper values('0008','Kansas Today','Kansas','Genral','Robin Paul','1925 Shawnee Dr ','Kansas City','Kansas','66106-3025','001','(913)362-9529','(913)362-9515');
insert into newspaper values('0009','Star Texas Times','Texas','Genral','Juan Smith','322 Highway 39','Ingram','Texas','63206-3025','001','(803)432-9512','(720)342-9235');
insert into newspaper values('0010','Los Angeles News','California','Genral','Frank Robertson','123 Pier Avenue','Santa Monica','California','90423-3025','001','(303)426-2212','(303)623-6345');
insert into newspaper values('0011','Sonoma Valley News','California','Genral','John Atkinson','120 S. Main St. Suit','Sebastopol','California','97678-3224','001','(707)234-5512','(707)864-9565');
/* data for newsad */
insert into newsad values('0001','0003','03-4月-1999','12-4月-1999');
insert into newsad values('0002','0002','15-3月-1999','12-4月-1999');
insert into newsad values('0003','0005','03-4月-1999','22-4月-1999');
insert into newsad values('0004','0005','05-4月-1999','14-4月-1999');
insert into newsad values('0005','0005','06-5月-1999','15-5月-1999');
insert into newsad values('0006','0001','07-5月-1999','16-5月-1999');
insert into newsad values('0007','0002','08-6月-1999','17-6月-1999');
insert into newsad values('0008','0003','01-6月-1999','18-6月-1999');
insert into newsad values('0009','0002','02-7月-1999','13-7月-1999');
insert into newsad values('0010','0003','03-7月-1999','14-7月-1999');
insert into newsad values('0011','0005','04-7月-1999','15-7月-1999');
insert into newsad values('0012','0001','05-7月-1999','16-7月-1999');
insert into newsad values('0013','0002','06-7月-1999','17-7月-1999');
insert into newsad values('0014','0001','07-7月-1999','18-7月-1999');
insert into newsad values('0015','0001','08-7月-1999','13-7月-1999');
/* data for jobfair */
insert into jobfair values('0001','Wright State Uni.Dayton, OH','National Def. Asso.', 18,'11-5月-1999');
insert into jobfair values('0002','Tulsa Mar. South Hills 1902','CFG', 20,'30-5月-1999');
insert into jobfair values('0003','211 Ninian Ave Alexandria','ADF', 10,'25-5月-1999');
insert into jobfair values('0004','23 Pitt Rd Spring Field Ohio','DDC', 22,'22-5月-1999');
insert into jobfair values('0005','36 Mcgrath Dr Dublin','PLC', 20,'21-5月-1999');
insert into contractrecruiter values('0001','Jack Paul','2250 Lakeside Ave.','Alexandria','Virginia','22310-1117','(703)922-2654','(703)922-2662',15,1200);
insert into contractrecruiter values('0002','Abbott Mike','3688 Mcgrath Dr','Dublin','Georgia','43016-4171','(614)761-7743','(614)761-7745',7,1500);
insert into contractrecruiter values('0003','Facundo Michel','1150 Prince St.','Alexandria','Virginia','22314-2947','(703)684-1696','(703)684-1680',9,800);
insert into contractrecruiter values('0004','Taber Kyle','1225 Effingham Ct','Columbus','Georgia','31909-3165','(706)565-9463','(706)565-9464',7,700);
insert into contractrecruiter values('0005','Joe Bryan','28 Pitt Rd','Spring Field','Ohio','07081-2634','(973)467-1682','(973)467-1682',9,900);
insert into contractrecruiter values('0006','George Craig','5202 Ninian Ave ','Alexandria','Virginia','22310-1117','(703)922-3466','(703)922-3467',9,1200);
/* data for recruitment agencies */ -- drop table recruitmentagencies
Insert into recruitmentagencies values('0001','ASTEC Inc ','11715 Administration Dr Suite 230','Hampstead','New York','11550-3425','(516)433-1426','(516)433-1427',8, 1200);
Insert into recruitmentagencies values('0002','CDI Corporation West','9301 Oakdale Ave Suite 114 ','New Orleans','Louisinia','70131-1265','(504)422-5608','(504)422-5734', 9, 800);
Insert into recruitmentagencies values('0003','ETS Inc ','5717 E Thomas Rd Suite 3 ','New Orleans','Louisinia','70115-2365','(504)323-4322','(504)323-4342', 9, 1100);
Insert into recruitmentagencies values('0004','FSK Associates Inc ','PO Box 1078 ','Houston ','Texas','77015-4568','(713)451-2847','(713)451-2852', 6, 1200);
Insert into recruitmentagencies values('0006','Hall Kinion ','19925 Stevens Creek Blvd Suite 180','Houston','Texas','77016-3448','(713)642-7253','(713)642-7250', 8, 800);
Insert into recruitmentagencies values('0007','Manpower Technical ','517 Fifth Ave ','Alexandria','Virginia','22302-1608','(703)214-4423','(703)214-4425', 6, 1200);
Insert into recruitmentagencies values('0009','Sysdyne Corporation ','1660 S Highway 100 Suite 424 ','Clevland','Ohio','44106-1523','(440)885-1980','(440)885-1978', 6, 800);
/* data for college */
Insert into college values('0001','Westend College ','900N Main Ave','Hampstead','New York','11550-1648','(516)423-4591');
Insert into college values('0002','Cromwell College ','4010 Gartner Ave.','Abilene','Texas','79605-4123','(915)692-6628');
Insert into college values('0003','Schneider College ','2100 New Blvd','Los Angeles','California','70131-3184','(504)271-2187');
Insert into college values('0004','Trent College ','240N Grand Ave.','Abilene','Texas','79605-4415','(915)368-2561');
Insert into college values('0005','Wiley College ','711 Wiley College','Columbus','Ohio','43220-2821','(614)451-1005');
Insert into college values('0006','Benard College ','3009 broad way','New York City','New York','10128-4018','(212)969-9826');
Insert into college values('0007','Deman College ','4380 Main Street','Hampstead ','New York','11550-4321','(516)532-3728');
Insert into college values('0008','Monterey College ','30 Emmons park drive','Taft','California','93268-1648','(661)761-4591');
Insert into college values('0009','Evergreen College ','2000 College Heights Blvd','Ridgecrest','California','93555-1648','(760)761-4591');
Insert into campusrecruitment values('0001','0003','13-5月-1999','28-5月-1999');
Insert into campusrecruitment values('0002','0005','23-5月-1999','07-6月-1999');
Insert into campusrecruitment values('0003','0002','13-5月-1999','28-5月-1999');
Insert into InternalJobPosting values('0001','0002',6,'New York','13-3月-1999','12-4月-1999');
Insert into InternalJobPosting values('0002','0004',9,'Texas','15-3月-1999','12-4月-1999');
Insert into InternalJobPosting values('0003','0001',7,'New York','02-4月-1999','22-4月-1999');
Insert into InternalJobPosting values('0004','0002',12,'Texas','13-3月-1999','12-4月-1999');
Insert into InternalJobPosting values('0005','0004',2,'New York','13-3月-1998','12-4月-1998');
Insert into InternalJobPosting values('0006','0002',4,'New York','13-3月-1998','12-4月-1998');
Insert into InternalCandidate values('000001','000012','0001','0002','02-4月-1999','06-4月-1999',96,'09-4月-1999',null,null,'5',null);
Insert into InternalCandidate values('000002','000002','0001','0002','02-4月-1999','06-4月-1999',67,'09-4月-1999',null,null,'6',null);
Insert into InternalCandidate values('000003','000011','0003','0001','02-4月-1999','06-4月-1999',88,'09-4月-1999',null,null,'9',null);
Insert into InternalCandidate values('000004','000004','0003','0001','02-4月-1999','06-4月-1999',83,'09-4月-1999',null,null,'7',null);
Insert into InternalCandidate values('000005','000008','0001','0002','02-4月-1999','06-4月-1999',43,'09-4月-1999',null,null,'3',null);
Insert into InternalCandidate values('000011','000020','0001','0002','02-4月-1998','06-4月-1998',46,'09-4月-1998',null,null,'4',null);
Insert into InternalCandidate values('000012','000022','0001','0002','02-4月-1998','06-4月-1998',57,'09-4月-1998',null,null,'5',null);
Insert into InternalCandidate values('000013','000021','0003','0001','02-4月-1998','06-4月-1998',48,'09-4月-1998',null,null,'4',null);
Insert into InternalCandidate values('000014','000024','0003','0001','02-4月-1998','06-4月-1998',53,'09-4月-1998',null,null,'5',null);
Insert into InternalCandidate values('000018','000003','0001','0002','02-2月-1999','06-3月-1999',null,'09-4月-1999',null,null,'8',null);
Insert into InternalCandidate values('000019','000016','0001','0002','02-2月-2001','06-3月-2001',null,'09-4月-2001',null,null,'8',null);
Insert into InternalCandidate values('000020','000017','0003','0001','02-4月-2002','06-4月-2002',88,'09-4月-2002',null,null,'9',null);
Insert into InternalCandidate values('000021','000018','0001','0002','12-4月-2002','16-4月-2002',96,'19-4月-2002',null,null,'5',null);
Insert into InternalCandidate values('000022','000019','0001','0002','02-4月-2002','26-4月-2002',43,'29-4月-2002',null,null,'3',null);
insert into externalcandidate values('000001','Angela','Smith','16223 Radiance Court','Kansas City ','Kansas','66101-2945','001','(913)227-2344 ','0001','02-5月-1997',null,'0001',null,null,null,null,null ,'Graduate',4 ,'12-2月-1969','F','0001', 30000.00,null,'angelas@qmail.com','P','09-5月-97',80,'15-5月-97',null,null,8 ) ;
insert into externalcandidate values('000002','Barbara','Johnson','227 Beach Ave.','Alexandria ','Virginia','22304-3700','001','(703)123-5673 ','0005','14-8月-1997',null,null,'0001',null,null,null,null ,'BBA',3 ,'15-1月-1965','F','0003', 40000.00,null,'barba@speedmail.com','P','21-8月-97',82,'28-8月-97','000001','Good Communication skill',9);
insert into externalcandidate values('000003','Betty','Williams','1 Tread Road','Dublin ','Georgia','43017-3004','001','(614)458-3299 ','0010','02-5月-1998',null,null,'0001',null,null,null,null ,'Graduate',2 ,'6-8月-1971','F','0007', 30000.00,null,'bettyw@dpeedmil.cm','P','09-5月-98',70,'20-5月-98','000001','Good presentation skill',8 );
insert into externalcandidate values('000004','Carol','Jones','765 Furling Rd Apt','Norton ','Kansas','67654-1255','001','(785)678-4544 ','0012','02-5月-1998',null,null,null,'0001',null,null,null ,'Graduate',4 ,'10-6月-1979 ','F','0005', 0.00,null,NULL,'P','09-5月-98',45,'24-5月-98','000001','Quick Learner',6 ) ;
insert into externalcandidate values('000005','Catherine','Roberts','5508 Aquiline Court','Norton ','Kansas','67654-1255','001','(785)445-2256 ','0007','05-5月-1998','000001',null,null,null,null,null,null ,'BBA',5 ,'10-10月-1962','F','0001', 0.00,null,NULL,'P','09-5月-98',74,'28-5月-98','000001','Good at planning',7 ) ;
insert into externalcandidate values('000010','David','Moore','8808 Joviality Drive','Houston ','Texas','77063-5564','001','(281)982-5577 ','0001','07-8月-1998','000002',null,null,null,null,null,null ,'Graduate',1 ,'23-11月-1967','M','0003', 25000.00,null,'davidm@qmail.com','P','14-8月-98',92,'21-8月-98','000006','Takes lots of Initiative',9 ) ;
insert into externalcandidate values('000011','Deborah','Taylor','2199- Fairfax Drive ','Seattle ','Washington','98122-2514','001','(206)889-2235 ','0007','07-8月-1998',null,null,null,null,'0001',null,null,'BBA',4 ,'14-10月-1964','F','0004', 31000.00,null,'deboraht@qmail.com','P','14-8月-98',88,'21-8月-98','000006','Has worked as good team member',8 );
insert into externalcandidate values('000012','Donna','Anderson','7930 Orange St. ','Mentor ','Ohio','44060-4514','001','(440)845-2323 ','0010','07-8月-1999',null,'0003',null,null,null,null,null ,'MBA',4,'18-7月-1971','F','0012', 0.00,null,'Donnaa@speedmail.com','P','14-8月-98',83,'21-8月-98','0000011','Hard working',8 );
insert into externalcandidate values('000014','Elizabeth','Jackson','598 Apex Avenue #2','Dublin ','Georgia','43017-3004','001','(614)545-9078 ','0013','07-9月-1998',null,null,null,null,'0002',null,null ,'Graduate',4 ,'12-10月-1969','F','0002', 28000.00,null,'elizabethj@qmail.com','P','13-9月-1998',65,'21-9月-1998','000001','Good presentation skills',6 );
insert into externalcandidate values('000016','Helen','White','Fleet Street','New York City ','New York','10024-7401','001','(212)585-7796 ','0015','10-12月-1998',null,null,null,null,null,null,null ,'Graduate',4 ,'1-2月-1972 ','F','0001', 40000.00,null,'helenw@spedmail.com','P','15-12月-1998',94,'20-12月-1998','000006','Quick Learner',9 ) ;
insert into externalcandidate values('000018','Jennifer','Martin','9812 76th Street ','Abilene ','Texas','79603-2485','001','(915)569-7789 ','0015','16-12月-1998',null,null,null,'0006',null,'0001',null ,'BBA',4 ,'22-5月-1966','F','0004', 26000.00,null,'jenniferm@qmail.com','P','20-12月-1998',97,'23-12月-1998','000006','Good at planning and good analytical skills',9 ) ;
insert into externalcandidate values('000021','Joseph','Martinez','995 Rind Street ','San Diego ','California','92122-2017','001','(619)456-9032 ','0014','16-1月-1999',null,'0005',null,null,null,null,null ,'Graduate',4 ,'13-4月-1971','M','0005', 0.00,null,'josephm@qmail.com','P','22-1月-1999',81,'26-1月-1999','000006','Commited and Hard working',8 );
insert into externalcandidate values('000022','Joyce','Phillips','535 Darwin Avenue ','Columbus ','Georgia','31904-7624','001','(706)789-6905 ','0001','16-1月-1999',null,null,'0002',null,null,null,null ,'BBA',4 ,'14-3月-1966','M','0006', 0.00,null,'joycep@speedmail.com','P','22-1月-1999',61,'26-1月-1999','000001','Long term potential',6 ) ;
insert into externalcandidate values('000026','Linda','Lewis','1524 Patagonia Lane ','Columbus ','Georgia','31904-7624','001','(706)459-4563 ','0014','16-1月-1999',null,'0005',null,null,null,null,null ,'Graduate',4 ,'06-11月-1969','F','0006', 31000.00,null,'lindal@qmail.com','P','22-1月-1999',69,'26-1月-1999','000006','Helpfull in nature',6 ) ;
insert into externalcandidate values('000029','Maria','Hall','936 Midland Drive ','Columbus ','Georgia','31904-7624','001','(706)345-8764 ','0008','01-2月-1999',null,null,null,'0004',null,null,null ,'MBA',4,'21-10月-1968','F','0006', 35000.00,null,'Mariah@speedmail.com','P','07-2月-1999',57,'09-2月-1999','000011','Customer oriented',8 ) ;
insert into externalcandidate values('000033','Nancy','King','429 Ash Birch Lane ','Middle Town ','New Jersy','07748-3224','001','(732)563-2298 ','0006','10-2月-1999',null,'0005',null,null,null,null,null ,'Graduate',4,'6-5月-1970','F','0011', 25000.00,null,'nancyk@qmail.com','P','07-3月-1999',76,'06-3月-1999','000001','Needs to improve in communication,but commited to the job',7 ) ;
insert into externalcandidate values('000035','Paul','Lopez','13459 Campton Street','Spring Field ','Ohio','07081-1442','001','(973)912-7905 ','0006','14-2月-1999',null,null,null,'0002',null,null,null ,'Graduate',4,'1-11月-1968','M','0008', 28000.00,null,'paull@qmail.com','P','08-3月-1999',96,'14-3月-1999','000001','Hard working but needs to improve in communication skill',9 ) ;
insert into externalcandidate values('000038','Ruth','Green','459 Ridge Road ','Middle Town ','New Jersy','07748-3224','001','(732)347-9082 ','0003','01-3月-1999',null,null,null,null,'0001',null,null ,'MBA',4 ,'02-12月-1968','M','0006', 21000.00,null,'ruthg@speedmail.com','P','08-3月-1999',88,'10-3月-1999','000011','Helpfull in nature',8 );
insert into externalcandidate values('000039','Sandra','Adams','12454 Boggy Blvd. ','Middle Town ','New Jersy','07748-3224','001','(732)982-9503 ','0003','12-2月-1999',null,null,'0002',null,null,null,null ,'Graduate',4,'22-5月-1969','F','0004', 32000.00,null,'Sandra@qmail.com','P','14-2月-1999',55,'20-2月-1999','000001','Long term potential',6 ) ;
insert into externalcandidate values('000040','Sarah','Baker','2394 Emerald Street ','Middle Town ','New Jersy','07748-3224','001','(732)439-2309 ','0002','12-2月-1999',null,null,null,'0004',null,null,null ,'BBA',4 ,'17-11月-1971','F','0004', 28000.00,null,'sarahb@qmail.com','P','09-5月-2002',67,'21-2月-2002','000011','Needs to improve in communication skills',6 ) ;
insert into externalcandidate values('000045','William','Perez','987 Tetragon Avenue ','Las Vegas ','Nevada','89123-2514','001','(702)234-8764 ','0002','02-3月-1999',null,'0004',null,null,null,null,null ,'Graduate',4 ,'7-2月-1969','M','0005', 26000.00,null,'williamp@qmail.com','P','09-5月-2002',78,'14-4月-2002','000006','Customer oriented',8 ) ;
insert into externalcandidate values('000046','David','Cooper','5614 Raffle Court ','Las Vegas ','Nevada','89123-2514','001','(702)678-3458 ','0003','06-3月-1999',null,'0004',null,null,null,null,null ,'MBA',4 ,'08-4月-1966','M','0005', 0.00,null,'davidc@speedmail.com','P','30-3月-2002',89,'07-7月-2002','000006','Long term potential, commited and hardworking',9 ) ;
insert into externalcandidate values('000047','Peter','Prescott','340 Miasma Drive','Mentor ','Ohio','44060-4514','001','(440)890-4084 ','0015','10-3月-1999',null,'0002',null,null,null,null,null ,'Graduate',4,'07-8月-1971','M','0006', 29000.00,null,NULL,'P','30-3月-1999',55,NULL,null,null,5 ) ;
insert into externalcandidate values('000049','Jane','Schaffer','521 State Street','Mentor ','Ohio','44060-4514','001','(440)280-9999 ','0002','04-5月-1999',null,null,null,'0003',null,null,null ,'Graduate',4,'15-9月-1971','F','0004', 32000.00,null,NULL,'P','16-5月-1999',84,NULL,null,null,8 ) ;
--update externalcandidate
--set dtestdate=ddateofapplication+7
--go
--update externalcandidate
--set dinterviewdate=ddateofapplication+14
--go
Insert into skill values('0001','Communication skill');
Insert into skill values('0002','Visual basic');
Insert into skill values('0003','Database Administration');
Insert into skill values('0005','Data Entry');
Insert into skill values('0006','Team leading');
Insert into skill values('0007','Power Builder');
Insert into skill values('0008','Record Keeping');
Insert into skill values('0009','Presentation Skill');
Insert into skill values('0010','Convincing');
Insert into skill values('0011','Learning');
Insert into skill values('0012','Planning');
Insert into skill values('0013','Project Management');
Insert into skill values('0014','Time Keeping');
Insert into skill values('0015','Analytical');
Insert into skill values('0016','MS-Ofice');
Insert into skill values('0017','Initiative');
Insert into skill values('0018','Self Motivation');
Insert into skill values('0019','Scheduling');
Insert into skill values('0020','Interpersonal Skill');
Insert into skill values('0021','Scheduling');
/* data for employee rtable */
Insert into employee values('000001','Angela','Smith','000001','16223 Radiance Court','Kansas City ','Kansas','66101-2945','001','(913)227-2344 ','Graduate','12-2月-1969','F','0001','Senior Manager','angelas@qmail.com','0002','Kansas City ',null,'16-6月-1997',null,'627-89-6952',null);
Insert into employee values('000002','Betty','Williams','000003','1 Tread Road','Dublin ','California','43017-3004','001','(614)458-3299 ','Graduate','06-8月-1971','F','0010','Assistant secretary','bettyw@dpeedmil.cm','0005','Dublin ',null,'16-6月-1998',null,'617-40-3628',null) ;
Insert into employee values('000003','Carol','Jones','000004','765 Furling Rd Apt','Norton ','Kansas','67654-1255','001','(785)678-4544 ','Graduate','10-6月-1979','F','0012','Senior Receptionist','carolj@qmail.com','0003','Norton ',null,'19-6月-1998',null,'806-84-8173',null) ;
Insert into employee values('000004','Catherine','Roberts','000005','5508 Aquiline Court','Norton ','kansas','67654-1255','001','(785)445-2256 ','BBA','10-10月-1962','F','0007','Junior manager','catheriner@qmail.com','0009','Norton ',null,'29-6月-1998',null,'763-26-5478',null) ;
Insert into employee values('000005','David','Moore','000010','8808 Joviality Drive','Houston ','Texas','77063-5564','001','(281)982-5577','Graduate','23-11月-1967','M','0001','Junior manager','davidm@qmail.com','0002','Houston',null,'29-6月-1998',null,'732-43-5067',null);
Insert into employee values('000006','Elizabeth','Jackson','000014','598 Apex Avenue #2','Dublin ','Georgia','43017-3004','001','(614)545-9078 ','Graduate','12-10月-1969','F','0013','Assistant consultant','elizabethj@qmail.com','0007','Dublin ',null,'21-7月-1998',null,'356-05-5459',null) ;
Insert into employee values('000007','Helen','White','000016','Fleet Street','New York City ','New York','10024-7401','001','(212)585-7796 ','Graduate','01-2月-1972','F','0015','Junior Receptionist','helenw@spedmail.com','0003','New York City ',null,'29-8月-1998',null,'326-69-3560',null) ;
Insert into employee values('000008','Jennifer','Martin','000018','9812 76th Street','Dublin','California','49603-2485','001','(617)569-7789 ','BBA','22-5月-1966','F','0015','Junior Receptionist','jenniferm@qmail.com','0004','Dublin ',null,'30-8月-1998',null,'343-26-6693',null) ;
Insert into employee values('000009','Linda','Lewis','000026','1524 Patagonia Lane ','Columbus ','Georgia','31904-7624','001','(706)459-4563 ','Graduate','06-11月-1969','F','0014','Assistant Technician','lindal@qmail.com','0010','Columbus ',null,'08-9月-1998',null,'384-08-6778',null) ;
Insert into employee values('000010','Maria','Hall','000029','936 Midland Drive ','Columbus ','Georgia','31904-7624','001','(706)345-8764 ','MBA','21-10月-1968','F','0008','Junior operator','Mariah@speedmail.com','0003','Columbus ',null,'08-9月-1998',null,'783-06-7914',null) ;
Insert into employee values('000011','Paul','Lopez','000035','13459 Campton Street','Spring Field ','Ohio','07081-1442','001','(973)912-7905 ','Graduate','01-11月-1968','M','0006','Assistant Manager','paull@qmail.com','0009','Spring Field ',null,'21-9月-1998',null,'990-35-4788',null) ;
Insert into employee values('000012','Sandra','Adams','000039','12454 Boggy Blvd. ','Middle Town ','New Jersy','07748-3224','001','(732)982-9503 ','Graduate','22-5月-1969','F','0003','Senior Analyst','Sandra@qmail.com','0009','Middle Town ',null,'26-9月-1998',null,'499-03-5817',null) ;
Insert into employee values('000013','William','Perez','000045','987 Tetragon Avenue ','Las Vegas ','Nevada','89123-2514','001','(702)234-8764 ','Graduate','07-2月-1969','M','0002','Junior Manager','williamp@qmail.com','0003','Las Vegas ',null,'05-10月-1998',null,'517-82-4993',null) ;
Insert into employee values('000014','David','Cooper','000046','5614 Raffle Court ','Las Vegas ','Nevada','89123-2514','001','(702)678-3458 ','MBA','08-4月-1966','M','0003','Junior analyst','davidc@speedmail.com','0009','Las Vegas ',null,'05-10月-1998',null,'499-03-5816',null) ;
Insert into employee values('000015','Peter','Prescott','000047','340 Miasma Drive','Mentor ','Ohio','44060-4514','001','(440)890-4084 ','Graduate','07-9月-1971','M','0015','Junior Receptionist','peterp@qmail.com','0008','Mentor ',null,'08-11月-1998',null,'216-22-6414',null) ;
Insert into employee values('000016','Tom','Allen','000019','345 Miasma Drive','Mentor ','Ohio','44060-4514','001','(440)890-4085 ','Graduate','07-3月-1975','M','0015','Junior Receptionist','peterp@qmail.com','0008','Mentor ',null,'08-11月-1998',null,'216-22-5414',null);
Insert into employee values('000017','Ron','Floyd','000020','445 Miasma Drive','Mentor ','Ohio','44060-4514','001','(440)890-4075 ','Graduate','27-3月-1969','M','0015','Junior Receptionist','rf@qmail.com','0008','Mentor ',null,'08-12月-1999',null,'216-22-5455',null);
Insert into employee values('000018','Susan','Ward','000021','558 Apex Avenue #2','Dublin ','Georgia','43017-3004','001','(614)545-9098 ','Graduate','12-10月-1971','F','0013','Assistant consultant','susanw@qmail.com','0007','Dublin ',null,'21-6月-1997',null,'356-05-5779',null);
Insert into employee values('000019','Ken','Burton','000022','51254 Boggy Blvd. ','Middle Town ','New Jersy','07748-3224','001','(732)982-9559 ','Graduate','22-5月-1973','M','0003','Senior Analyst','KenB@qmail.com','0009','Middle Town ',null,'16-9月-1999',null,'499-03-5822',null);
/* data for annual salary */
insert into annualsalary values('000001',46000 ,1997);
insert into annualsalary values('000001',48000 ,1998);
insert into annualsalary values('000002',36000 ,1998);
insert into annualsalary values('000003',30000 ,1998);
insert into annualsalary values('000004',48000 ,1998);
insert into annualsalary values('000005',48000 ,1998);
insert into annualsalary values('000006',48000 ,1998);
insert into annualsalary values('000007',24000 ,1998);
insert into annualsalary values('000008',24000 ,1998);
insert into annualsalary values('000009',30000 ,1998);
insert into annualsalary values('000010',24000 ,1998);
insert into annualsalary values('000011',24000 ,1998);
insert into annualsalary values('000012',36000 ,1998);
insert into annualsalary values('000013',48000 ,1998);
insert into annualsalary values('000014',36000 ,1998);
insert into annualsalary values('000015',24000 ,1998);
--
insert into annualsalary values('000001',48000 ,1999);
insert into annualsalary values('000002',36000 ,1999);
insert into annualsalary values('000003',30000 ,1999);
insert into annualsalary values('000004',48000 ,1999);
insert into annualsalary values('000005',48000 ,1999);
insert into annualsalary values('000006',48000 ,1999);
insert into annualsalary values('000007',24000 ,1999);
insert into annualsalary values('000008',24000 ,1999);
insert into annualsalary values('000009',30000 ,1999);
insert into annualsalary values('000010',24000 ,1999);
insert into annualsalary values('000011',24000 ,1999);
insert into annualsalary values('000012',36000 ,1999);
insert into annualsalary values('000013',48000 ,1999);
insert into annualsalary values('000014',36000 ,1999);
insert into annualsalary values('000015',24000 ,1999);
/* data for monthly salary */
insert into monthlysalary values('000001', 3833.33,'01-7月-1997',0);
insert into monthlysalary values('000001', 3833.33,'01-8月-1997',0);
insert into monthlysalary values('000001', 3833.33,'01-9月-1997',0);
insert into monthlysalary values('000001', 3833.33,'01-10月-1997',0);
insert into monthlysalary values('000001', 3833.33,'01-11月-1997',0);
insert into monthlysalary values('000001', 3833.33,'01-12月-1997',0);
insert into monthlysalary values('000001', 4000.00,'01-1月-1998',0);
insert into monthlysalary values('000001', 4000.00,'01-2月-1998',0);
insert into monthlysalary values('000001', 4000.00,'01-3月-1998',0);
insert into monthlysalary values('000001', 4000.00,'01-4月-1998',0);
insert into monthlysalary values('000001', 4000.00,'01-5月-1998',0);
insert into monthlysalary values('000001', 4000.00,'01-6月-1998',0);
insert into monthlysalary values('000001', 4000.00,'01-7月-1998',0);
--------------------------------------------------------------------
insert into monthlysalary values('000002', 3000.00,'01-7月-1998',0);
insert into monthlysalary values('000004', 4000.00,'01-7月-1998',0);
insert into monthlysalary values('000005', 4000.00,'01-7月-1998',0);
--------------------------------------------------------------------
insert into monthlysalary values('000001', 4000.00,'01-8月-1998',0);
insert into monthlysalary values('000002', 3000.00,'01-8月-1998',0);
insert into monthlysalary values('000004', 4000.00,'01-8月-1998',0);
insert into monthlysalary values('000005', 4000.00,'01-8月-1998',0);
insert into monthlysalary values('000006', 4000.00,'01-8月-1998',0);
--------------------------------------------------------------------
insert into monthlysalary values('000001', 4000.00,'01-9月-1998',0);
insert into monthlysalary values('000002', 3000.00,'01-9月-1998',0);
insert into monthlysalary values('000004', 4000.00,'01-9月-1998',0);
insert into monthlysalary values('000005', 4000.00,'01-9月-1998',0);
insert into monthlysalary values('000006', 4000.00,'01-9月-1998',0);
insert into monthlysalary values('000007', 2000.00,'01-9月-1998',0);
insert into monthlysalary values('000008', 2000.00,'01-9月-1998',0);
--------------------------------------------------------------------
insert into monthlysalary values('000001', 4000.00,'01-10月-1998',0);
insert into monthlysalary values('000002', 3000.00,'01-10月-1998',0);
insert into monthlysalary values('000004', 4000.00,'01-10月-1998',0);
insert into monthlysalary values('000005', 4000.00,'01-10月-1998',0);
insert into monthlysalary values('000006', 4000.00,'01-10月-1998',0);
insert into monthlysalary values('000007', 2000.00,'01-10月-1998',0);
insert into monthlysalary values('000008', 2000.00,'01-10月-1998',0);
insert into monthlysalary values('000009', 2500.00,'01-10月-1998',0);
insert into monthlysalary values('000010', 2000.00,'01-10月-1998',0);
insert into monthlysalary values('000011', 2000.00,'01-10月-1998',0);
insert into monthlysalary values('000012', 3000.00,'01-10月-1998',0);
--------------------------------------------------------------------
insert into monthlysalary values('000001', 4000.00,'01-11月-1998',0);
insert into monthlysalary values('000002', 3000.00,'01-11月-1998',0);
insert into monthlysalary values('000004', 4000.00,'01-11月-1998',0);
insert into monthlysalary values('000005', 4000.00,'01-11月-1998',0);
insert into monthlysalary values('000006', 4000.00,'01-11月-1998',0);
insert into monthlysalary values('000007', 2000.00,'01-11月-1998',0);
insert into monthlysalary values('000008', 2000.00,'01-11月-1998',0);
insert into monthlysalary values('000009', 2500.00,'01-11月-1998',0);
insert into monthlysalary values('000010', 2000.00,'01-11月-1998',0);
insert into monthlysalary values('000011', 2000.00,'01-11月-1998',0);
insert into monthlysalary values('000012', 3000.00,'01-11月-1998',0);
insert into monthlysalary values('000013', 4000.00,'01-11月-1998',0);
insert into monthlysalary values('000014', 3000.00,'01-11月-1998',0);
--------------------------------------------------------------------
insert into monthlysalary values('000001', 4000.00,'01-12月-1998',0);
insert into monthlysalary values('000002', 3000.00,'01-12月-1998',0);
insert into monthlysalary values('000004', 4000.00,'01-12月-1998',0);
insert into monthlysalary values('000005', 4000.00,'01-12月-1998',0);
insert into monthlysalary values('000006', 4000.00,'01-12月-1998',0);
insert into monthlysalary values('000007', 2000.00,'01-12月-1998',0);
insert into monthlysalary values('000008', 2000.00,'01-12月-1998',0);
insert into monthlysalary values('000009', 2500.00,'01-12月-1998',0);
insert into monthlysalary values('000010', 2000.00,'01-12月-1998',0);
insert into monthlysalary values('000011', 2000.00,'01-12月-1998',0);
insert into monthlysalary values('000012', 3000.00,'01-12月-1998',0);
insert into monthlysalary values('000013', 4000.00,'01-12月-1998',0);
insert into monthlysalary values('000014', 3000.00,'01-12月-1998',0);
insert into monthlysalary values('000015', 2000.00,'01-12月-1998',0);
--------------------------------------------------------------------
insert into monthlysalary values('000001', 4000.00,'01-1月-1999',0);
insert into monthlysalary values('000002', 3000.00,'01-1月-1999',0);
insert into monthlysalary values('000004', 4000.00,'01-1月-1999',0);
insert into monthlysalary values('000005', 4000.00,'01-1月-1999',0);
insert into monthlysalary values('000006', 4000.00,'01-1月-1999',0);
insert into monthlysalary values('000007', 2000.00,'01-1月-1999',0);
insert into monthlysalary values('000008', 2000.00,'01-1月-1999',0);
insert into monthlysalary values('000009', 2500.00,'01-1月-1999',0);
insert into monthlysalary values('000010', 2000.00,'01-1月-1999',0);
insert into monthlysalary values('000011', 2000.00,'01-1月-1999',0);
insert into monthlysalary values('000012', 3000.00,'01-1月-1999',0);
insert into monthlysalary values('000013', 4000.00,'01-1月-1999',0);
insert into monthlysalary values('000014', 3000.00,'01-1月-1999',0);
insert into monthlysalary values('000015', 2000.00,'01-1月-1999',0);
--------------------------------------------------------------------
insert into monthlysalary values('000001', 4000.00,'01-2月-1999',0);
insert into monthlysalary values('000002', 3000.00,'01-2月-1999',0);
insert into monthlysalary values('000004', 4000.00,'01-2月-1999',0);
insert into monthlysalary values('000005', 4000.00,'01-2月-1999',0);
insert into monthlysalary values('000006', 4000.00,'01-2月-1999',0);
insert into monthlysalary values('000007', 2000.00,'01-2月-1999',0);
insert into monthlysalary values('000008', 2000.00,'01-2月-1999',0);
insert into monthlysalary values('000009', 2500.00,'01-2月-1999',0);
insert into monthlysalary values('000010', 2000.00,'01-2月-1999',0);
insert into monthlysalary values('000011', 2000.00,'01-2月-1999',0);
insert into monthlysalary values('000012', 3000.00,'01-2月-1999',0);
insert into monthlysalary values('000013', 4000.00,'01-2月-1999',0);
insert into monthlysalary values('000014', 3000.00,'01-2月-1999',0);
insert into monthlysalary values('000015', 2000.00,'01-2月-1999',0);
--------------------------------------------------------------------
insert into monthlysalary values('000001', 4000.00,'01-3月-1999',0);
insert into monthlysalary values('000002', 3000.00,'01-3月-1999',0);
insert into monthlysalary values('000004', 4000.00,'01-3月-1999',0);
insert into monthlysalary values('000005', 4000.00,'01-3月-1999',0);
insert into monthlysalary values('000006', 4000.00,'01-3月-1999',0);
insert into monthlysalary values('000007', 2000.00,'01-3月-1999',0);
insert into monthlysalary values('000008', 2000.00,'01-3月-1999',0);
insert into monthlysalary values('000009', 2500.00,'01-3月-1999',0);
insert into monthlysalary values('000010', 2000.00,'01-3月-1999',0);
insert into monthlysalary values('000011', 2000.00,'01-3月-1999',0);
insert into monthlysalary values('000012', 3000.00,'01-3月-1999',0);
insert into monthlysalary values('000013', 4000.00,'01-3月-1999',0);
insert into monthlysalary values('000014', 3000.00,'01-3月-1999',0);
insert into monthlysalary values('000015', 2000.00,'01-3月-1999',0);
--------------------------------------------------------------------
insert into monthlysalary values('000001', 4000.00,'01-4月-1999',0);
insert into monthlysalary values('000002', 3000.00,'01-4月-1999',0);
insert into monthlysalary values('000004', 4000.00,'01-4月-1999',0);
insert into monthlysalary values('000005', 4000.00,'01-4月-1999',0);
insert into monthlysalary values('000006', 4000.00,'01-4月-1999',0);
insert into monthlysalary values('000007', 2000.00,'01-4月-1999',0);
insert into monthlysalary values('000008', 2000.00,'01-4月-1999',0);
insert into monthlysalary values('000009', 2500.00,'01-4月-1999',0);
insert into monthlysalary values('000010', 2000.00,'01-4月-1999',0);
insert into monthlysalary values('000011', 2000.00,'01-4月-1999',0);
insert into monthlysalary values('000012', 3000.00,'01-4月-1999',0);
insert into monthlysalary values('000013', 4000.00,'01-4月-1999',0);
insert into monthlysalary values('000014', 3000.00,'01-4月-1999',0);
insert into monthlysalary values('000015', 2000.00,'01-4月-1999',0);
--------------------------------------------------------------------
insert into monthlysalary values('000001', 4000.00,'01-5月-1999',0);
insert into monthlysalary values('000002', 3000.00,'01-5月-1999',0);
insert into monthlysalary values('000004', 4000.00,'01-5月-1999',0);
insert into monthlysalary values('000005', 4000.00,'01-5月-1999',0);
insert into monthlysalary values('000006', 4000.00,'01-5月-1999',0);
insert into monthlysalary values('000007', 2000.00,'01-5月-1999',0);
insert into monthlysalary values('000008', 2000.00,'01-5月-1999',0);
insert into monthlysalary values('000009', 2500.00,'01-5月-1999',0);
insert into monthlysalary values('000010', 2000.00,'01-5月-1999',0);
insert into monthlysalary values('000011', 2000.00,'01-5月-1999',0);
insert into monthlysalary values('000012', 3000.00,'01-5月-1999',0);
insert into monthlysalary values('000013', 4000.00,'01-5月-1999',0);
insert into monthlysalary values('000014', 3000.00,'01-5月-1999',0);
insert into monthlysalary values('000015', 2000.00,'01-5月-1999',0);
--------------------------------------------------------------------
insert into monthlysalary values('000001', 4000.00,'01-6月-1999',0);
insert into monthlysalary values('000002', 3000.00,'01-6月-1999',0);
insert into monthlysalary values('000004', 4000.00,'01-6月-1999',0);
insert into monthlysalary values('000005', 4000.00,'01-6月-1999',0);
insert into monthlysalary values('000006', 4000.00,'01-6月-1999',0);
insert into monthlysalary values('000007', 2000.00,'01-6月-1999',0);
insert into monthlysalary values('000008', 2000.00,'01-6月-1999',0);
insert into monthlysalary values('000009', 2500.00,'01-6月-1999',0);
insert into monthlysalary values('000010', 2000.00,'01-6月-1999',0);
insert into monthlysalary values('000011', 2000.00,'01-6月-1999',0);
insert into monthlysalary values('000012', 3000.00,'01-6月-1999',0);
insert into monthlysalary values('000013', 4000.00,'01-6月-1999',0);
insert into monthlysalary values('000014', 3000.00,'01-6月-1999',0);
insert into monthlysalary values('000015', 2000.00,'01-6月-1999',0);
/* data for employee skill */
insert into employeeskill values('000001','0001');
insert into employeeskill values('000001','0006');
insert into employeeskill values('000001','0009');
insert into employeeskill values('000001','0010');
insert into employeeskill values('000001','0012');
insert into employeeskill values('000001','0015');
insert into employeeskill values('000001','0018');
insert into employeeskill values('000001','0020');
insert into employeeskill values('000001','0021');
insert into employeeskill values('000001','0013');
--
insert into employeeskill values('000002','0001');
insert into employeeskill values('000002','0009');
insert into employeeskill values('000002','0010');
insert into employeeskill values('000002','0012');
insert into employeeskill values('000002','0015');
insert into employeeskill values('000002','0018');
--
insert into employeeskill values('000003','0001');
insert into employeeskill values('000003','0006');
insert into employeeskill values('000003','0016');
insert into employeeskill values('000003','0020');
--
insert into employeeskill values('000004','0001');
insert into employeeskill values('000004','0006');
insert into employeeskill values('000004','0009');
insert into employeeskill values('000004','0010');
insert into employeeskill values('000004','0012');
insert into employeeskill values('000004','0015');
insert into employeeskill values('000004','0018');
insert into employeeskill values('000004','0020');
insert into employeeskill values('000004','0021');
insert into employeeskill values('000004','0013');
--
insert into employeeskill values('000005','0001');
insert into employeeskill values('000005','0006');
insert into employeeskill values('000005','0009');
insert into employeeskill values('000005','0010');
insert into employeeskill values('000005','0012');
insert into employeeskill values('000005','0015');
insert into employeeskill values('000005','0018');
insert into employeeskill values('000005','0020');
insert into employeeskill values('000005','0021');
insert into employeeskill values('000005','0013');
--
insert into employeeskill values('000006','0001');
insert into employeeskill values('000006','0006');
insert into employeeskill values('000006','0009');
insert into employeeskill values('000006','0010');
insert into employeeskill values('000006','0012');
insert into employeeskill values('000006','0015');
insert into employeeskill values('000006','0018');
insert into employeeskill values('000006','0020');
insert into employeeskill values('000006','0021');
insert into employeeskill values('000006','0013');
--
insert into employeeskill values('000007','0001');
insert into employeeskill values('000007','0016');
insert into employeeskill values('000007','0020');
--
insert into employeeskill values('000008','0001');
insert into employeeskill values('000008','0016');
insert into employeeskill values('000008','0020');
--
insert into employeeskill values('000009','0001');
insert into employeeskill values('000009','0018');
insert into employeeskill values('000009','0017');
insert into employeeskill values('000009','0020');
--
insert into employeeskill values('000010','0001');
insert into employeeskill values('000010','0016');
insert into employeeskill values('000010','0020');
--
insert into employeeskill values('000011','0001');
insert into employeeskill values('000011','0016');
insert into employeeskill values('000011','0020');
insert into employeeskill values('000011','0014');
insert into employeeskill values('000011','0008');
--
insert into employeeskill values('000012','0001');
insert into employeeskill values('000012','0006');
insert into employeeskill values('000012','0009');
insert into employeeskill values('000012','0010');
insert into employeeskill values('000012','0012');
insert into employeeskill values('000012','0015');
insert into employeeskill values('000012','0018');
insert into employeeskill values('000012','0020');
insert into employeeskill values('000012','0021');
insert into employeeskill values('000012','0013');
--
insert into employeeskill values('000013','0001');
insert into employeeskill values('000013','0006');
insert into employeeskill values('000013','0009');
insert into employeeskill values('000013','0010');
insert into employeeskill values('000013','0012');
insert into employeeskill values('000013','0015');
insert into employeeskill values('000013','0018');
insert into employeeskill values('000013','0020');
insert into employeeskill values('000013','0021');
insert into employeeskill values('000013','0013');
--
insert into employeeskill values('000014','0001');
insert into employeeskill values('000014','0006');
insert into employeeskill values('000014','0009');
insert into employeeskill values('000014','0010');
insert into employeeskill values('000014','0012');
insert into employeeskill values('000014','0015');
insert into employeeskill values('000014','0018');
insert into employeeskill values('000014','0020');
insert into employeeskill values('000014','0021');
insert into employeeskill values('000014','0013');
--
insert into employeeskill values('000015','0001');
insert into employeeskill values('000015','0016');
insert into employeeskill values('000015','0020');
/* data for external candidate */
insert into candidateskill values('000001','0001');
insert into candidateskill values('000001','0006');
insert into candidateskill values('000001','0009');
insert into candidateskill values('000001','0010');
insert into candidateskill values('000001','0012');
insert into candidateskill values('000001','0013');
insert into candidateskill values('000001','0015');
insert into candidateskill values('000001','0018');
insert into candidateskill values('000001','0020');
insert into candidateskill values('000001','0021');
insert into candidateskill values('000003','0001');
insert into candidateskill values('000003','0009');
insert into candidateskill values('000003','0010');
insert into candidateskill values('000003','0012');
insert into candidateskill values('000003','0015');
insert into candidateskill values('000003','0018');
insert into candidateskill values('000004','0001');
insert into candidateskill values('000004','0006');
insert into candidateskill values('000004','0016');
insert into candidateskill values('000004','0020');
insert into candidateskill values('000005','0001');
insert into candidateskill values('000005','0006');
insert into candidateskill values('000005','0009');
insert into candidateskill values('000005','0010');
insert into candidateskill values('000005','0012');
insert into candidateskill values('000005','0013');
insert into candidateskill values('000005','0015');
insert into candidateskill values('000005','0018');
insert into candidateskill values('000005','0020');
insert into candidateskill values('000005','0021');
insert into candidateskill values('000010','0001');
insert into candidateskill values('000010','0006');
insert into candidateskill values('000010','0009');
insert into candidateskill values('000010','0010');
insert into candidateskill values('000010','0012');
insert into candidateskill values('000010','0013');
insert into candidateskill values('000010','0015');
insert into candidateskill values('000010','0018');
insert into candidateskill values('000010','0020');
insert into candidateskill values('000010','0021');
insert into candidateskill values('000014','0001');
insert into candidateskill values('000014','0006');
insert into candidateskill values('000014','0009');
insert into candidateskill values('000014','0010');
insert into candidateskill values('000014','0012');
insert into candidateskill values('000014','0013');
insert into candidateskill values('000014','0015');
insert into candidateskill values('000014','0018');
insert into candidateskill values('000014','0020');
insert into candidateskill values('000014','0021');
insert into candidateskill values('000016','0001');
insert into candidateskill values('000016','0016');
insert into candidateskill values('000016','0020');
insert into candidateskill values('000018','0001');
insert into candidateskill values('000018','0016');
insert into candidateskill values('000018','0020');
insert into candidateskill values('000026','0001');
insert into candidateskill values('000026','0017');
insert into candidateskill values('000026','0018');
insert into candidateskill values('000026','0020');
insert into candidateskill values('000029','0001');
insert into candidateskill values('000029','0016');
insert into candidateskill values('000029','0020');
insert into candidateskill values('000035','0001');
insert into candidateskill values('000035','0008');
insert into candidateskill values('000035','0014');
insert into candidateskill values('000035','0016');
insert into candidateskill values('000035','0020');
insert into candidateskill values('000039','0001');
insert into candidateskill values('000039','0006');
insert into candidateskill values('000039','0009');
insert into candidateskill values('000039','0010');
insert into candidateskill values('000039','0012');
insert into candidateskill values('000039','0013');
insert into candidateskill values('000039','0015');
insert into candidateskill values('000039','0018');
insert into candidateskill values('000039','0020');
insert into candidateskill values('000039','0021');
insert into candidateskill values('000045','0001');
insert into candidateskill values('000045','0006');
insert into candidateskill values('000045','0009');
insert into candidateskill values('000045','0010');
insert into candidateskill values('000045','0012');
insert into candidateskill values('000045','0013');
insert into candidateskill values('000045','0015');
insert into candidateskill values('000045','0018');
insert into candidateskill values('000045','0020');
insert into candidateskill values('000045','0021');
insert into candidateskill values('000046','0001');
insert into candidateskill values('000046','0006');
insert into candidateskill values('000046','0009');
insert into candidateskill values('000046','0010');
insert into candidateskill values('000046','0012');
insert into candidateskill values('000046','0013');
insert into candidateskill values('000046','0015');
insert into candidateskill values('000046','0018');
insert into candidateskill values('000046','0020');
insert into candidateskill values('000046','0021');
insert into candidateskill values('000047','0001');
insert into candidateskill values('000047','0016');
insert into candidateskill values('000047','0020');
insert into positionskill values('0001','0001');
insert into positionskill values('0001','0006');
insert into positionskill values('0002','0001');
insert into positionskill values('0002','0006');
insert into positionskill values('0003','0001');
insert into positionskill values('0004','0001');
insert into positionskill values('0004','0002');
insert into positionskill values('0004','0003');
insert into positionskill values('0004','0005');
insert into positionskill values('0004','0006');
insert into positionskill values('0005','0001');
insert into positionskill values('0005','0005');
insert into positionskill values('0005','0006');
insert into positionskill values('0006','0001');
insert into employeereferrals values('000001','000001','000021');
insert into employeereferrals values('000002','000002','000018');
insert into employeereferrals values('000003','000001','000014');
INSERT into Requisition
VALUES('000001','0003','03-3月-99','04-4月-99','0009','Texas',10);
INSERT into Requisition
VALUES('000002','0002','06-5月-99','07-6月-99','0002','Texas',11);
--INSERT into Requisition
--VALUES('000004','0015',getdate(),getdate()+7,'0003','Texas',12);
insert into rating values('Average', 1,5) ;
insert into rating values('Good',6,8) ;
insert into rating values('Excellent',9,10) ;
exit ;
spool off
一、子查询概念
是在另一个查询内指出的查询。
在主查询内进行处理。
应用圆括弧括起来。
语法
SELECT outer_select_list
FROM outer_table_name
WHERE expression (SELECT inner_select_list
FROM inner_table_name);
一、使用单行子查询
创建子查询指南
(1)所有的子查询必须用圆括弧括起来。
(2)为了增加可读性，子查询应置于比较条件的右方。
(3)单行子查询可以使用单值或多值运算符。
在子查询中使用GROUP BY 和HAVING 子句
除了可以在WHERE子句里用子查询外，你还可以在HAVING 和 GROUP BY 子句里使用子查询。
例子
SELECT cCollegeCode, AVG(nTestScore)
FROM ExternalCandidate
GROUP BY cCollegeCode
HAVING AVG(nTestScore)>(SELECT AVG(nTestScore)FROM ExternalCandidate);
在子查询中使用WITH子句
如果SELECT语句中某部分查询块需要复杂查询中多次用到，那么，你可以使用WITH子句。
使用WITH子句指南：
WITH子句只能和SELECT语句一起使用。
WITH子句可包含多个查询，各查询之间以逗号分隔。
如果其查询名与表名同名，则查询块名优先于表名。
例子：
With EMP_SAL AS(SELECT cEmployeecode, SUM(nMonthlysalary) AS EMP_TOTAL
FROM Monthlysalary GROUP BY cEmployeecode)
SELECT * FROM EMP_SAL WHERE EMP_TOTAL>(SELECT AVG(EMP_TOTAL) FROM EMP_SAL);
二、使用多行子查询
(1)返回多个行，必须包含一个多行运算符。
(2)下表给出多行运算符：IN、ANY、ALL、NOT IN
(3)使用IN运算符的多行子查询
SELECT vFirstName, vLastName, vAddress FROM Employee
WHERE cCandidatecode IN
(SELECT cCandidatecode FROM Employee
WHERE cCity=‘Columbus’ OR cCity=‘Norton’);
(4)使用ANY 运算符的多行子查询
一些用法：
<ANY 小于列表中的最大值
>ANY 大于列表中的最小值
=ANY 等于列表中任意一值。这等价于IN运算符
例子：
SELECT vFirstname, vLastName, cCity
FROM Employee WHERE dJoiningDate > ANY (SELECT dJoiningDate FROM Employee
WHERE cCity='Norton');
(5)使用ALL 运算符的多行子查询
<ALL 小于列表中的最小值
>ALL 大于列表中的最大值
例子：
SELECT vFirstname, vLastName, cCity
FROM Employee WHERE dJoiningDate > ALL (SELECT dJoiningDate FROM Employee
WHERE cCity='Norton');
(6)处理子查询中的NULL值
当返回NULL 值的多行子查询与NOT IN运算符一起使用时，外层查询将在执行时不返回任何值。
若使用IN运算符，子查询中的NULL值就不成问题了。
三、使用嵌套子查询
包含一个或多个子查询。
例子
SELECT vFirstname, vLastName, vAddress FROM ExternalCandidate
WHERE cEmployeeReferralNo IN
(SELECT cEmployeeReferralNo FROM EmployeeReferrals
WHERE cEmployeeCode=(SELECT cEmployeeCode FROM Employee WHERE vFirstname='Angela'));
四、相关子查询
涉及到查询的求值。
语法
SELECT outer_select_list
FROM outer_tablename alias
WHERE expression (SELECT inner_select_list FROM inner_tablename WHERE column_name=alias.column_name);
五、相关DELETE
用来基于另一个表的相关数据来删除表中数据。
语法
DELETE FROM table1 alias1
WHERE expression (SELECT column FROM table2 alias2
WHERE alias1.column = alias2.column);
一、识别各种数据库对象
(一)数据库对象的功能包括：
1.以行列形式存储数据(表)
2.按逻辑次序表示数据或数据的子集(视图)
3.自动生成顺序的数值(序列号)
4.增强查询性能(索引)
5.为对象分配备选名字(同义词)
(二)表
1.用来存储数据。
2.由行和列组成。
3.行表示记录。
4.列表示属性。
5.两种类型的表:
(1)用户表
(2)数据字典
(三)视图
1.用来显示存储在一个或多个表里的全部或部分数据。
2.就象是访问和修改表中数据的一个窗口。
3.可象表一样被引用。
4.并不实际存储它所显示的数据。
5.有两种类型：
(1)简单视图
(2)复杂视图
(四)序列号
1.用来为列生成唯一的数字。
2.典型地被用作主键值。
4.可用在多个表里。
5.是单独生成和存储在数据库里。
(五)索引
1.确保对存储在表中的信息进行直接、快速的数据访问的内部表结构。
2.类似于书籍的索引。
(六)同义词
1.为数据库对象提供备选的名字。
2.可用于表、视图、和序列号。
二、创建表
CREATE TABLE语句
用来创建数据库中表。
语法
CREATE TABLE table_name(column_name datatype [NULL | NOT NULL] column_name datatype …);
三、更改表
1.添加列
语法
ALTER TABLE tablename ADD(columnname datatype [DEFAULT expression],[columnname datatype]);
举例：将C1列添加到表T1
ALTER TABLE T1 ADD C1 CHAR(10);
2.修改列
语法
ALTER TABLE tablename MODIFY (columnname datatype [DEFAULT expression],[columnname datatype]);
举例：将表T1中的C1列更改为char(10)
ALTER TABLE T1 MODIFY(C1 CHAR(10));
3.撤消列
撤消列
语法
ALTER TABLE tablename DROP COLUMN columnname;
举例：将表T1中的C1列撤消
ALTER TABLE T1 DROP COLUMN C1;
四、重命名表
重命名表
语法
RENAME <old_table_name> TO <new_table_name>;
举例：将表T1改名为T2
RENAME T1 TO T2;
五、撤消表
DROP TABLE语句
用来从数据库中除去表。
语法
DROP TABLE table_name
举例：删除表emp
DROP TAABLE emp;
一、添加表中行
语法：
INSERT [INTO] table_name(Column_List)
VALUES(values_list);
举例：
表Student有两列：nID int,vName varchar(10)
方法一：INSERT INTO Student(nID,vName) VALUES(100,'TOM')
方法二：INSERT INTO Student VALUES(100,'TOM')
方法三：INSERT INTO Student SELECT 1,'TOM' FROM DUAL;
一次插入多条记录：
INSERT INTO Student
SELECT 1,'TOM' FROM DUAL
UNION ALL
SELECT 2,'MARY' FROM DUAL
UNION ALL
SELECT 3,'JOHN' FROM DUAL;
二、修改表中行
Update语句
用于改变表中存储的数据。
语法
UPDATE tablename SET columnname = value [, columnname = value][WHERE condition];
举例：
将表Employee中职员代号是000015的职员代号改为0003
UPDATE Employee SET cDepartmentCode ='0003'
WHERE cEmployeeCode ='000015';
三、删除表中行
DELETE语句
表中冗余行可被用删除。
语法
DELETE table_name WHERE condition;
举例：
将表Employee中职员代号是000015的职员记录删除
DELETE Employee
WHERE cEmployeeCode ='000015';
四、合并表中行
Merge语句:
在检查条件后更新表或把一个表的数据插入到另一个表。
举例：
合并将成绩不低于5分的所有应聘者材料的查询,
将合并行的表名是PreferredCandidate,
将合并的行所在的表名是InternalCandidate
MERGE INTO PreferredCandidate c
USING InternalCandidate p
ON (p.cStatus > 4)
WHEN MATCHED THEN
UPDATE SET
c.cEmployeeCode= p.cEmployeeCode,
c.cInternalJobPostingCode = p.cInternalJobPostingCode,
c.cPositionCodeAppliedFor = p.cPositionCodeAppliedFor,
c.dDateOfApplication = p.dDateOfApplication,
c.dTestDate = p.dTestDate,
c.nTestScore = p.nTestScore,
c.dInterviewDate = p.dInterviewDate,
c.cInterviewer = p.cInterviewer,
c.vInterviewComments = p.vInterviewComments,
c.cRating = p.cRating,
c.cStatus = p.cStatus
WHEN NOT MATCHED THEN INSERT Values
五、理解数据完整性
(一)数据完整性的不同类型有：
1.实体完整性
2.域完整性
3.引用完整性
4.用户定义完整性
六、约束
(一)用途
用于实施数据完整性，以确认表中的数据。
可从表中加入，撤消，删除，禁用和启用。
约束是可在表级或列级上说明的规则
(二)约束的不同类型有：
1.主键约束
2.外键约束
3.唯一性约束
4.检查约束
5.Not Null 约束
(三)创建约束可使用CREATE TABLE或ALTER TABLE语句。
(四)ENABLE 和 DISABLE子句用于禁用或启用约束。
(五)撤消约束可使用DROP CONSTRAINT或CASCADE CONSTRAINTS子句。
(六)级联约束
ALTER TABLE 语句的CASCADE CONSTRAINTS子句用于删除一个列上的约束、删除列本身、和删除所有引用该列的约束。
语法
ALTER TABLE table_name DROP (col) CASCADE CONSTRAINTS;
一、事务的定义
把一串一起执行的操作作为单个逻辑工作单元处理.
二、事务的性质是:
原子性 (Atomicity)
一致性 (Consistency)
隔离性 (Isolation)
持久性 (Durability)
1、原子性：在事务结束的时候， 事务中的操作要么全做完了， 要么什么也没做
例：
银行转账
2、一致性：
(i) 事务在执行前：数据库应当处于一致状态
(ii) 事务结束后： 数据库又回到一致性状态。
ACID角度：数据库的每一行、值与所描述的现实保持一致， 且满足所有约束的要求。
例：
order(写入)与orderDetail(没写入)
3、隔离性：
每个事务与其他事务 产生的结果互相隔离
隔离性是两个事务之间的屏障
检查方法：在相同初始的集合上多次执行一组特定的事务集合， 每次得到相同结果
例：
用户一：正在更新100行数据
用户二：要删除 用户一所修改的数据中的一行
如果成功，则隔离性不够。
4、事务的持续性意味着：
只有经过commit ， 操作才会完成。
例：
如果事务在操作过程中出现了故障， 即使存放数据的驱动器融化了， 在数据恢复的时候， 也要回到融化前该事务提交时的瞬间状态。
三、使用事务的优点:
它们保证了数据的一致性.
使用事务使数据修改更灵活而且修改过程是可控的.
即使在用户处理失败或者系统发生故障时数据仍然是安全的.
事务保证DML数据操纵语言）语句对数据所做的变动是一致的.
四、事务的组成
一条或多条DML语句
一条DDL语句
一条DCL语句
五、事务的开始与结束
事务由第一条DML语句开始
遇到下列情况终止
接受或拒绝事务所做的变动
使用DDL语句创建数据对象
执行一条DCL语句
机器发生故障或系统崩溃
六、显式事务
对所做的数据修改明确地表示接受或丢弃的事务.
COMMIT语句用来接受对数据的修改.
提交语句
COMMIT语句完成显式事务,并使所有的修改是持久有效的.
COMMIT语句使你能:
保证数据的一致性
可在永久地更新数据前预览修改.
将逻辑相关的所有操作组合起来.
语法：
SQL statement1;
SQL statement2;
COMMIT;
七、隐式事务
是自动地提交数据变动的系统事务.
使用AUTOCOMMIT语句实现隐式事务.
AUTOCOMMIT语句可设置为ON 或 OFF。只有当AUTOCOMMIT被设置为ON时，才可进行数据修改
缺省方式：OFF
显示AUTOCOMMIT状态：SHOW AUTOCOMMIT
设置AUTOCOMMIT状态
SET AUTOCOMMIT ON
SET AUTOCOMMIT value
若AUTOCOMMIT启用,执行DDL语句和DCL语句时，自动提交修改.
八、回滚事务
ROLLBACK语句终止当前事务，使数据库返回到以前的状态.
使用ROLLBACK语句的优点有:
对数据所做的修改是一致的，因为使用ROLLBACK语句，缓冲区里的数据将被除去.
可恢复数据到以前的状态.
如果不小心删错了需要的行，你可检索被删除的行.
万一执行SQL 查询时发生异常，原始数据仍可得到恢复.
语法：
SQL statement1;
SQL statement2;
ROLLBACK;
自动回滚
在下列情况下，数据修改自动被回滚:
系统崩溃或发生故障.
SQL*Plus 意外终止.
语句级回滚
九、事务保存点：在这类事务中,单独回滚出错的DML语句SAVEPOINT
用来在当前的事务中创建标记.
语法：
SQL statement1;
SAVEPOINT savepoint_name; SQL statement2;
ROLLBACK to savepoint_name;
十、上锁
上锁防止多个用户同时修改数据.
上锁可以是隐式或显式的.
上锁的一些基本内容:
上锁可预防并发事务之间的破坏性的交互.
上锁是自动施行的，无须用户干预.
上锁把操作限制到可能的最小粒度.
只有在事务结束后，上锁才被解除.
按缺省方式，Oracle执行隐式上锁.
两种上锁模式是:
共享锁
排他锁
死锁是这样一种情形，即两个用户（或事务）在各自的对象上都有锁，同时每个用户正在等待对方对象上的锁.
一、视图的定义
是从一个或多个表（或视图）中导出的行或列的子集.
二、视图的优点:
视图可用来检索表中所选的列.
用视图创建简单的查询，可容易地检索需要频繁调看的结果.
视图可用来从多个表中检索数据.
用户或用户组可根据视图里指定的准则来访问数据.
视图可在不需要的时候被除去，而不影响数据库.
三、视图的类型
简单视图：从单个表中导出数据.
复杂视图：从多个表导出数据.
四、创建视图
创建视图可使用CREATE VIEW语句.
语法：
CREATE [FORCE|NOFORCE] VIEW view_name [(alias [, alias]…)] AS SQLQuery
[WITH CHECK OPTION [CONSTRAINT constraint_name]]
WITH READ ONLY [CONSTRAINT constraint_name]];
举例：
CREATE VIEW Emp_Vu AS SELECT vFirstName, vLastName, vQualification, vSkill
FROM Employee JOIN PositionSkill
ON Employee.cCurrentPosition= PositionSkill.cPositionCode
JOIN Skill ON PositionSkill.cSkillCode=Skill.cSkillCode;
五、创建视图的指南
包含联接、组合和子查询的SELECT语句可被包含在定义视图的子查询内.
创建视图时不能使用ORDER BY子句。但为检索视图中数据，可用这条子句.
OR REPLACE选项用于修改视图定义，而无须撤消或重新创建它.
如果创建附带WITH CHECK 选项的视图时没有指定约束的名称，系统按SYS_Cn 格式分配一个名字。在SYS_Cn 格式里，n是一个整数，它使得约束名在系统内是唯一的.
六、通过视图显示数据
可以使用SELECT语句通过视图访问数据.
当通过视图访问数据时，Oracle服务器执行以下步骤:
a、它从数据字典表里回复视图定义的材料.
b、它检查基表的访问权限.
c、它把在视图上的查询转换为在基表上的等价操作.
七、更改、重命名和撤消视图
（一）更改视图
更改视图可使用CREATE OR REPLACE VIEW语句.
语法：
CREATE OR REPLACE VIEW view_name;
AS subquery;
（二）重命名视图
RENAME语句用于给视图重新命名.
语法
RENAME <old_view_name> TO <new_view_name>;
（三）撤消视图
DROP VIEW语句用于从数据库里撤消现有视图.
语法：
DROP VIEW view_name;
八、通过视图修改数据
包含下列内容的视图结构不可被修改 ：
组合函数
GROUP BY子句
DISTINCT关键字
伪列ROWNUM，用于为子查询返回的每个行分配序列值
由表达式定义的列
九、内联视图
是SQL语句内的子查询,在主查询的FROM子句中使用它.
使用 Top-n分析法
Top-n 分析法基于条件显示表中最上面n条记录或最下面n条记录.
Top-n 查询包含以下内容:
一个用于排序数据的内联视图.
使用ORDER BY子句或DESC 参数的子查询.
一个外层查询，由它决定最终记录集中行的数目。这包括ROWNUM伪列和用于比较运算符的WHERE子句..
语法
SELECT ROWNUM, columnlist FROM (SELECT columnlist FROM tablename
ORDER BY Top-n_columnname)
WHERE ROWNUM <= N;
