资源描述：
《201258014009杨玉玲实验2》由会员上传分享，免费在线阅读，更多相关内容在工程资料-天天文库。
1、实验序号2实验名称SQL语言基础实验过程及步骤1.登录Oracle，进入HR方案，使用DESC和SELECT命令查看employees,departments表的结果和所有数据；connhr/hr@orcl;descemployees;descdepartments;select*fromdepartments;select*fromemployees;yearsmortgagehousing;4.mortgageregistrationformalitiesarecompleted.(D)pledge1.bor
2、rower(includingthepledgor)between18-65yearsofage,withfullcivilcapacity;2.collateral1.创建一个名为manager的表,它由编号EMPLOYEE_ID，名FIRST_NAME，姓LAST_NAME，邮箱EMAIL，电话号码PHONE_NUMBER，部门编号JOB_ID，薪资SALARY，部门经理编号MANAGER_ID八个属性组成；createtablemanagerasselectEMPLOYEE_ID,FIRST_NAME,LA
3、ST_NAME,EMAIL,PHONE_NUMBER,JOB_ID,SALARY,MANAGER_IDfromemployeeswhere1>2;2.向manager表中增加“性别”列：altertablemanageraddsexvarchar2(2);yearsmortgagehousing;4.mortgageregistrationformalitiesarecompleted.(D)pledge1.borrower(includingthepledgor)between18-65yearsofage,w
4、ithfullcivilcapacity;2.collateral1.查看jobs表中每个工种最高工资和最低工资之间的差距,把单位换算为万元；selectJOB_TITLEas工种,(MAX_SALARY-MIN_SALARY)/10000as工资差fromJOBS;2.编写一个查询,查询所有第二个字母为“r”的国家的名称；selectCOUNTRY_NAMEfromCOUNTRIESwheresubstr(COUNTRY_NAME,2,1)='r';3.查询出所有属于IT部门（DEPARTMENT_ID=60）
5、,从事过程序员(job_id='IT_PROG')工作的雇员号；selectEMPLOYEE_IDfromemployeeswhereJOB_ID='IT_PROG';4.使用GROUPyearsmortgagehousing;4.mortgageregistrationformalitiesarecompleted.(D)pledge1.borrower(includingthepledgor)between18-65yearsofage,withfullcivilcapacity;2.collateralBY
6、子句对薪金记录进行分组,使用SQL函数计算每个JOB_ID的平均薪金(AVG)、所有薪金的总和(SUM),以及最高薪金(MAX)和各组的行数；selectJOB_ID,avg(SALARY),sum(SALARY),max(SALARY),count(JOB_ID)fromemployeesgroupbyJOB_ID;1.使用子查询查看所有部门在某一地区（1700）的雇员信息：select*fromemployeeswhereDEPARTMENT_IDin(selectDEPARTMENT_IDfromdepar
7、tmentswhereLOCATION_ID=1700);2.查询将EMPLOYEES表和DEPARTMENT表相连接,生成一个笛卡儿积：selecte.*,d.*fromemployeese,departmentsd;yearsmortgagehousing;4.mortgageregistrationformalitiesarecompleted.(D)pledge1.borrower(includingthepledgor)between18-65yearsofage,withfullcivilcapaci
8、ty;2.collateral1.查询EMPLOYEES表,将薪金大于本职位平均薪金的雇员信息显示出来：select*fromemployeeseleftjoin(selectJOB_ID,avg(SALARY)asavg_salaryfromemployeesgroupbyJOB_ID)bone.JOB_ID=b.JOB_IDwhereSALARY>avg_salary
