一．创建用户在YGGL数据库中创建一个用户MANAGER，授予DBA角色和SYSDBA系统权限，它可以替代system系统用户。

CONNECT SYSTEM/123456 AS SYSDABCREATE USER MANAGERPROFILE "DEFAULT"IDENTIFIED BY managerDEFAULT TABLESPACE "USERS"TEMPORARY TABLESPACE "TEMP"ACCOUNT UNLOCK;GRANT SYSDBA TO "MANAGER";GRANT "COUNTNECT" TO "MANAGER";GRANT "DBA" TO "MANAGER";ALTER user MANGER GRANT CONNECT THROUGH SYSTEM;二：创建角色在YGGL数据库中创建一个角色ADMIN，授予DBA角色和SYSDBA系统权限。

CREATE ROLE ADMINIDENTIFILED BY "manager";GRANT SYSDAB TO ADMIN ;GRANT SYSOPER TO ADMIN;GRANT "CONNECT" TO ADMIN;GRANT "DBA" TO "ADMIN";一．创建概要文件在YGGL数据库中创建概要文件YGGL_PROFILE并分配给用户MANAGER。

CREATE PROFILE "XSCJ _PROFILE"LIMITCOMPOSITE_LIMIT DEFAULT FAILED_LOGIN_ATTEMPTS 3PASSWORD_LOCK_TIME 20 PASSWORD_GRACE_TIME 60PASSWORD_LIFE_TIME 30 PASSWORD_REUSE_MAX UNLIMITED PASSWORD_REUSE_TIME 120 PASSWORD_VERIFY_FUNCTION DEFAULT （1）使用命令方式分别向YGGL数据库个表中插入一行记录INSERT INTO ZPEMPLOYEES V ALUES('011112','罗林',TO_DATE('26-06-1973','DD-MM-YYYY'),1,'解放路100 ',210002,4055663,5); INSERT INTO ZPDEPARTMENTS V ALUES('6','人力资源',NULL);INSERT INTO ZPSALARYV ALUES ('011112',1200.09,50)（2）修改表salry的记录值UPDATE ZPSALARY SET InCome=2890 WHERE EmployeeID='011112';（3）删除Salary中的一行记录DELETE FROM ZPSALARY WHERE EmployeeID='01112';3.索引和完整性(1).对YGGL数据库中的Employees表的DepartmentID列建立索引CREATE INDEX PK_XS_BAK ON ZPEMPLOYEES(DepartmentID)TABLESPACE USERS PCTFREE 48 INITRANS 10 MAXTRANS 100STORAGE (INITIAL 64K NEXT 64K MINEXTENTS 5 MAXEXTENTS 20PCTINCREASE 10 FREELISTS 1 FREELIST GROUPS 1)PARALLEL(DEGREE DEFAULT);(2).实体完整性使用SQL语句创建表Departments，DepartmentId列为主键CREATE TABLE ZPDEPARTMENTS(DepartmentID CHAR(3) NOT NULL,DepartmentName CHAR(20) NOT NULL,Note VARCHAR2(100) NULLCONTRAINT PK_ZPDEPARTMENTS PRIMARY KEY(DepartmentID));(3).实现参照完整性为Employees表中的DepartmentID列建立外键ALTER TABLE ZPEMPLOYEESADD(CONSTRAINT FK_ZPEMPLOYEES FOREIGN KEY (DepartmentID)REFERENCES ZPDEPARTMENTS (DepartmentID));1.SELECT语句的基本使用（1）SELECT * FROM ZPEMPLOYEES;（2）查询每个雇员的地址和电话SELECT Address,PhoneNumber FROM ZPEMPLOYEES；（3）查询EmployeeId为000001的雇员的地址和电话SELECT Address,PhoneNumber FROM ZPEMPLOYEES; WHERE EmployeeID='000001' （4）查询Employees表中所有女雇员的地址和电话。

SELECT Address AS 地址 ,PhoneNumber AS 电话FROM ZPEMPLOYEES WHERE sex=0;（5)计算每个雇员的实际收入SELECT EMPLOYEEID,INCOME-OUTCOME AS 实际收入FROM ZPSALARY;（6）找出所有姓王的的雇员的部门号SELECT DEPARTMENTID FROM ZPEMPLOYEES WHERE NAME LIKE'王%';（7）找出所有收入在2000元-3000元之间的雇员号码SELECT EMPLOYEEID FROM ZPSALARY WHERE INCOME BETWEEN 2000 AND 3000;2.子查询的使用（1）查找在财务部工作的雇员的情况SELECT * FROM ZPEMPLOYEES WHERE DEPARTMENTID =(SELECT DEPARTMENTIDFROM ZPDEPARTMENTSWHERE DEPARTMENTNAME = '财务部');（2）查找财务部年龄不低于所有研发部雇员年龄的雇员的姓名SELECT NAME FROM ZPEMPLOYEES WHERE DEPARTMENTID IN(SELECT DEPARTMENTID FROM ZPDEPARTMENTSWHERE DEPARTMENTNAME = '财务部')ANDBIRTHDAY <=ALL(SELECT BIRTHDAYFROM ZPEMPLOYEESWHERE DEPARTMENTID IN(SELECT DEPARTMENTID FROM DEPARTMENTS WHERE DEPARTMENTNAME = '研发部' ));（3）查找所有比财务部的雇员收入都高的雇员的姓名SELECT NAME FROM ZPEMPLOYEESWHERE EMPLOYEEID IN(SELECT EMPLOYEEID FROM ZPSALARY WHERE INCOME >ALL(SELECT INCOME FROM ZPSALARY WHERE EMPLOYEEIDIN (SELECT EMPLOYEEIDFROM ZPEMPLOYEESWHERE DEPARTMENTID =(SELECT DEPARTMENTID FROM ZPDEPARTMENTSWHERE DEPARTMENTNAME = '财务部'))));3.连接查询的使用（1）查询每个雇员的情况以及其薪水的情况SELECT ZPEMPLOYEES.* ,ZPSALARY.*FROM ZPEMPLOYEES,ZPSALARYWHERE ZPEMPLOYEES.EMPLOYEEID = ZPSALARY.EMPLOYEEID;（2）查找财务部收入在2200元以上的雇员姓名及其薪水详情SELECT NAME,INCOME,OUTCOMEFROM ZPEMPLOYEES,ZPSALARY,ZPDEPARTMENTSWHERE ZPEMPLOYEES.EMPLOYEEID = ZPSALARY.EMPLOYEEID ANDZPEMPLOYEES.DepartmentID = ZPDEPARTMENTS.DepartmentID ANDDEPARTMENTNAME = '财务部'AND INCOME>2200;4.数据汇总（1）求财务部雇员的平均收入SELECT AVG(INCOME) AS 财务部平均收入 FROM ZPSALARYWHERE EMPLOYEEID IN(SELECT EMPLOYEEID FROM ZPEMPLOYEESWHERE DEPARTMENTID =(SELECT DEPARTMENTID FROM ZPDEPARTMENTSWHERE DEPARTMENTNAME = '财务部'));（2）求财务部雇员的平均实际收入SELECT AVG (INCOME-OUTCOME) AS 财务部平均实际收入 FROM ZPSALARYWHERE EMPLOYEEID IN(SELECT EMPLOYEEID FROM ZPEMPLOYEESWHERE DEPARTMENTID =(SELECT DEPARTMENTID FROM ZPDEPARTMENTSWHERE DEPARTMENTNAME = '财务部'));（3）财务部雇员的总人数SELECT COUNT(EMPLOYEEID) FROM ZPEMPLOYEESWHERE EMPLOYEEID IN(SELECT EMPLOYEEID FROM ZPEMPLOYEESWHERE DEPARTMENTID =(SELECT DEPARTMENTID FROM ZPDEPARTMENTSWHERE DEPARTMENTNAME = '财务部'));5.GROUP BY和ORDER BY子句的使用（1）求各部门的雇员数SELECT COUNT(EMPLOYEEID)FROM ZPEMPLOYEESGROUP BY DEPARTMENTID;6.使用视图（1）创建视图限制查看雇员的某些情况CREATE OR REPLACE VIEW cx_employeesASselect EMPLOYEEID,Name,BIRTHDAY,SEX,DEPARTMENTIDFROM ZPEMPLOYEES;限制各部门经理只能查找本部雇员的薪水情况，如限制财务部经理想查看自己部门雇员姓名及其薪水详情CREATE OR REPLACE VIEW cx_salaryASSELECT NAME,INCOME,OUTCOMEFROM ZPEMPLOYEES,ZPSALARY,ZPDEPARTMENTSWHERE ZPEMPLOYEES.EMPLOYEEID=ZPSALARY.EMPLOYEEID AND ZPEMPLOYEES.EMPLOYEEID=ZPDEPARTMENTS.DEPARTMENTID AND DEPARTMENTNAME='财务部'；（2）使用视图查询财务部雇员信息SELECT *FROM cx_employees;查询财务部雇员薪水情况SELECT * FROM cx_salary向表ZPEMPLOYEES中插入一条记录INSERT INTO cx_employees VALUES ( '510888','张无忌',TO_DATE('19780823','YYYYMMDD'),1,'3');将张无忌从经理办公室转到市场部UPDATE cx_employees SET DEPARTMENTID='5'WHERE NAME='张无忌'；把张无忌从表ZPEMPLOYEES中删除DELETE FROM cx_employees WHERE Name='张无忌';1.条件结构的使用（1）查询总学分大于50的学生人数。