数据库原理习题课-new

1、数据库原理习题课主讲：王新颖主讲：王新颖E-mail：吉林大学软件学院关系代数表达式1、基本的关系代数运算2、附加的关系代数运算3、扩展的关系代数运算4、数据库修改Copyright: Xinying Wang 根据下述关系数据库，写查询表达式 employee(person-name,street,city) works(person-name,company-name,salary) company(company-name,city) manages(person-name,manager-name)Copyright: Xinying Wang 找出First Bank Corpora

2、tion的所有员工的姓名person-name (company-name=“First Bank Corporation” (works)找出First Bank Corporation的所有员工的姓名和居住城市person-name, city (company-name=“First Bank Corporation” (employee works)找出First Bank Corporation的所有年收入在10,000美元一时的员工的姓名和居住的街道、城市person-name, street,city (salary10000 (company-name=“First Bank

3、Corporation” (employee works)找出所有居住地与工作的公司在同一城市的员工姓名person-name (employee )Copyright: Xinying Wang 找出与其经理居住在同一城市同一街道的所有员工的姓名person-name ( manager-name=employee2.person-name manages.person-name=employee.person-name employee.street = employee2.street employee.city = employee2.city (employee (manages (

4、employee2 (employee)6. 找出比Small Bank Corporation所有员工收入都高的所有的员工的姓名person-name (works) (works.person-name (works.salary works2.salary pany-name=“Small Bank Corporation” (works (works2(works)Copyright: Xinying Wang 7.找出数据库中不在First Bank Corporation工作的所有员工的姓名每个人只能在一个公司工作（不允许兼职）person-name (company-name“F

5、irst Bank Corporation” (works)每个人可以在不只一个公司工作（允许兼职）person-name (employee) person-name (company-name = “First Bank Corporation”(works)Copyright: Xinying Wang 8. 修改数据库，使Jones现在居住在NewTownemployee employee -person-name=“Jones”(employee)employee employee(“Jones”, null, “Newtown”)9. 为工资不高于100,000的经理提高工资10%

6、，高于100,000的提高工资3%t1 person-name, company-name, salary (person-name= manager-name(employeeworks)t2 person-name, company-name, salary *1.1 (salary 100000 (t1)t3 person-name, company-name, salary*1.03 ( salary 100000 (t1) works (works - t1) t2 t3Copyright: Xinying Wang 10. 删除works关系中Small Bank Corporat

7、ion的员工的所有元组t1 person-name, company-name, salary ( company-name=“Small Bank Corporation” (works)works (works - t1)11.找出员工最多的公司r1 company-namegcount (person-name) as person-number (works)r2 gmax(person-number) (r1)company-name ( person-number=r2 (r1)Copyright: Xinying Wang SQL1、数据定义2、集合运算3、聚集函数4、空值5、嵌

8、套子查询6、复杂查询7、视图8、数据库修改9、完整性约束Copyright: Xinying Wang 基本表的定义create tablecreate table 表名（ 列名 数据类型 default default 缺省值 notnot nullnull ，列名 数据类型 default default 缺省值 notnot nullnull ，primary keyprimary key（列名 ，列名 ） ，foreign key foreign key （列名 ，列名 ） references references 表名 （列名 ，列名 ） ，checkcheck（条件）Copyri

9、ght: Xinying Wang 索引的定义create create uniqueunique/distinctdistinct clustercluster index index 索引名 on on 表名 (列名 ascasc/descdesc , 列名ascasc/descdesc)Copyright: Xinying Wang SQL数据查询selectselect A1 , A2 , , Anfromfrom r1 , r2 , , rmwhere where P A1 , A2 , , An(p(r1 r2 rm)Copyright: Xinying Wang select子句

10、需注意的问题目标列形式通配符*、算数表达式、聚集函数语法约束allall：保留重复元组 distinctdistinct或uniqueunique：去掉重复元组Copyright: Xinying Wang where子句语法成分比较运算符 、 、 、=、 逻辑运算符 andand，oror，notnotbetween条件 判断表达式的值是否在某范围内Copyright: Xinying Wang 更名运算格式old_name old_name as as new_name new_name 为关系和属性重新命名，可出现在select和from子句中注：asas可选Copyright: Xin

11、ying Wang 字符串操作命令格式格式列名 notnot likelike “字符串”匹配规则“%” 匹配零个或多个字符“_”匹配任意单个字符转义字符“”Copyright: Xinying Wang 分组和聚集函数分组命令group bygroup by 列名 havinghaving 条件表达式 group by将表中的元组按指定列上值相等的原则分组，然后在每一分组上使用聚集函数，得到单一值having则对分组进行选择，只将聚集函数作用到满足条件的分组上Copyright: Xinying Wang 分组和聚集函数 select select SNO，avgavg(SCORE) fro

12、mfrom SC group bygroup by SNO havinghaving avgavg(SCORE) = 60selectselect SNO，avgavg(SCORE) fromfrom SCwherewhere SCORE =60 group bygroup by SNOCopyright: Xinying Wang 分组和聚集函数聚集函数平均值：avgavg最小值：minmin最大值：maxmax总和：sumsum记数：countcountCopyright: Xinying Wang 空值空值测试表达式表达式 is is notnot nullnull注意事项除is not

13、 null之外，空值不满足任何查找条件如果null参与算术运算，则该算术表达式的值为null如果null参与比较运算，则结果可视为false如果null参与聚集运算，则除count(*)之外其它聚集函数都忽略nullCopyright: Xinying Wang 其他常见问题元组显示顺序order by order by 列名 ascasc | descdesc集合操作集合并：unionunion集合交：intersectintersect集合差：exceptexceptCopyright: Xinying Wang 嵌套子查询in 子查询表达式表达式 notnot inin （子查询）（子查

14、询）some/all子查询表达式表达式 比较运算符比较运算符 some some （子查询）（子查询） 表达式的值至少与子查询结果中的一个值相比满足比较运算符 表达式表达式 比较运算符比较运算符 allall （子查询）（子查询） 表达式的值与子查询结果中的所有的值相比都满足比较运算符Copyright: Xinying Wang 集合基数的测试*测试集合是否为空表达式 notnot existsexists （子查询）判断子查询的结果集合中是否有任何元组存在测试集合是否存在重复元组 表达式 unique unique (子查询）如果子查询结果中没有重复元组，则返回trueCopyright:

15、 Xinying Wang SQL的数据修改功能插入insert into 表名 (列名,列名) values (值 ,值)insert into 表名 (列名,列名) (子查询)删除delete from 表名 where 条件表达式修改update 表名 set 列名 = 表达式 | 子查询 列名 = ，表达式 | 子查询where 条件表达式Copyright: Xinying Wang 视图viewcreate view 视图名称 as select 查询Copyright: Xinying Wang 根据员工数据库，写SQL表达式employee(employee-name, str

16、eet, city)works(employee-name, company-name, salary)company(company-name, city) Copyright: Xinying Wang 找出所有为First Bank Corporation工作的员工的名字select employee-name select employee-name from works from works where company-name=where company-name=First Bank CorporationFirst Bank Corporation 找出各个公司员工的平均工资，

17、并按照公司名称排序（逆序） Select company-name, avg (salary) Select company-name, avg (salary) From works From works Group by company-nameGroup by company-name Order by company-name desc Order by company-name desc Copyright: Xinying Wang 找出First Bank Corporation的所有年收入在10,000美元以上的员工的姓名和居住的街道、城市 select a.employee-

18、name, street, city from works as a, employee as b where company-name=First Bank Corporation and a.employee-name=b.employee-name and salary10000找出所有居住地与工作的公司在同一城市的员工姓名 select a.employee-name from works a, employee b, company c where pany_name= pany_name and a.employee-name=b.employee-name and b.city=

19、c.cityCopyright: Xinying Wang 找出与其经理居住在同一城市同一街道的所有员工的姓名 select a.employee_name from employee as a, manager as b , (select a.employee_name , street , city from employee as a, manages as b where a.employee_name=b.manager_name) as mg(manager_name,street,city) where a.employee_name=mg.employee_nameand b

20、.manager_name=mg.manager_nameand a.city=b.city and a.street=b.streetCopyright: Xinying Wang 找出数据库中不在First Bank Corporation工作的所有员工的姓名每个人只能在一个公司工作（不允许兼职） select employee-name from works where company-nameFirst Bank Corporation每个人只能在一个公司工作（不允许兼职）select employee-name from worksexceptselect employee-name

21、 from works where company-name=First Bank CorporationCopyright: Xinying Wang 找出比Small Bank Corporation所有员工收入都高的所有的员工的姓名select employee_name from works where salary all (select max(salary) from works where company_name=S.B.C.)假设一个公司可以位于几个城市中，找出位于Small Bank Corporation所在的城市有分公司的所有公司select company-name

22、 from company where city in (select city from company where company_name=S.B.C.)Copyright: Xinying Wang 假设一个公司可以位于几个城市中，找出位于Small Bank Corporation所在的各个城市的所有公司 select company-name from company as c where not exists ( select city from company where company-name=S. B. C except select city from company

23、as a where pany_name=pany_name)Copyright: Xinying Wang S B C AC找出工资高于其所在公司员工平均工资的所有员工 select employee_name from works as w where salary all (select avg(salary) from works as a where pany_name= pany_name) 找出员工最多的公司 select company_name，max(countmax(count(*) as n from works group by company_nameCopyrig

24、ht: Xinying Wang 找出平均工资高于First Bank Corporation平均工资的所有公司 select company_name，avg(salary) as avgSalary from works group by company_name having avgSalary =allall (select avg(salary) from work where company_name =F.B.Cgroup by company_name)Copyright: Xinying Wang 为First Bank Corporation所有员工增加10%的薪水 Upd

25、ate works Set salary=salary*1.1 where company-name= First Bank Corporation 为数据库中所有的经理都提高工资10% update works set salary=salary*1.1 where employee-name in (select manager-name from manages)Copyright: Xinying Wang 为工资不高于100,000的经理提高工资10%，高于100,000的提高工资3% update works set salary=case when salary100000 th

26、en salary*1.03 else salary*1.1 end where company-name=F. B. C.删除works关系中Small Bank Corporation的员工的所有元组 delete from works where company-name=S. B. C.Copyright: Xinying Wang 创建带有“经理名字”和其下属员工的平均工资的视图 create view v_m_avg as select manager_name , avg(salary) from works as a, manages as b where a.employee

27、_name=b.employee_name group by manager_nameCopyright: Xinying Wang 数据模型数据库结构的基础描述数据、数据联系、数据语义以及一致性约束的概念工具集合分类基于对象的逻辑模型：ER模型基于记录的逻辑模型：关系模型物理模型Copyright: Xinying Wang E-R模型Copyright: Xinying Wang 实体：现实世界中，可以区别其他对象的一个“事件”或者“物体”联系：实体之间的关联实体集：同一类实体的集合联系集：同一类联系的集合组成n矩形：实体n椭圆形：属性n菱形：联系实体集具有相同类型或相同属性的实体集合实体

28、集可以相交实体通过属性来表示，属性是实体集中每个成员具有的描述性性质简单属性和复合属性单值属性和多值属性NULL属性派生属性Copyright: Xinying Wang 联系集联系集是多个实体集间的数学关系，是同类联系的集合联系也可以具有描述性属性参与联系的实体集的个数称为联系集的度。二元关系的度为2，三元关系的度为3Copyright: Xinying Wang 码存在依赖关系：实体x存在依赖于实体y超码：可以唯一表示一个实体的属性集合候选码：最小的超码主码：被选来在实体集中区分不同实体的候选码Copyright: Xinying Wang E-R模型Copyright: Xinying

29、Wang 主要构件n矩形：实体集n椭圆形：属性n菱形：联系集n线段：属性链接实体集或实体集链接联系集n双椭圆：多值属性n虚椭圆：派生属性n双线：一个实体全部参与到联系集当中n箭头：多对一，多对多，一对多关系n双矩形：弱实体集（没有主码）关系模型关系表：（二维表）表示数据和数据之间的联系每个表有多个列，列名唯一一行称为一条记录（元组）；一列称为一个属性（域）超码、候选码、主码的区别Copyright: Xinying Wang 1 为车辆保险公司设计一个E-R图 该公司有很多客户 每个客户有一辆或多辆车 每辆车可能发生0次或多次交通事故Copyright: Xinying Wang Copyri

30、ght: Xinying Wang personnameaddressDriver-idyearmodellicensecarownsaccidentReport-numlocationdateDamage-amountparticipated设计方法1person (driver-id, name, address)car (license, model, year) accident (report-number, location, date)owns (license,driver-id)participated (license, report-number, driver-id,

31、damage-amount)Copyright: Xinying Wang 设计方法2person (driver-id, name, address)car (license, model, year,driver-id) accident (report-number, location, date)owns (license, driver-id)participated (license, report-number, driver-id, damage-amount)Copyright: Xinying Wang Copyright: Xinying Wang 2 为大学设计一个教务

32、管理E-R图包括以下实体： 课程：课程编号、名称、学分、课程提纲和选修条件 课程提供：课程编号、年度、学期、节数、教室、时间、教室 学生：学号、姓名、计划 教师：教师代码、姓名、院系、职称Is offeredstudentprogramsidnameinstructoriidtitledeptnameyearroomsecnotimesemestercourse-offeringenrolsgraderequiresprerequisitemaincousecoursecreditscoursenosyllabustitleteachesCopyright: Xinying Wang 3 3

33、将上述将上述E-RE-R图转换成表图转换成表studentstudent ( (student-idstudent-id, name, program), name, program)instructorinstructor ( (instructor-idinstructor-id, name, dept, , name, dept, title) title) course course ( (coursenocourseno, title, syllabus, credits), title, syllabus, credits)course-offering course-offering ( (coursenocourseno, , secnosecno, , yearyear, , semestersemester, time, room), time, room)enrols enrols ( (student-idstudent-id, , coursenocourseno, , secnosecno, , semestersemester,