SQL的语句及习题

设某商业集团关于商店销售商品的数据库中有三个基本表： 商店 SHOP（S#，SNAME，AREA，MGR_NAME） 其属性是商店编号，商店名称，区域名，经理姓名。 销售 SALE（S#，G#，QUANTITY）其属性是商店编号，商品编号，销售数量。 商品 GOODS（G#，GNAME，PRICE） 其属性是商品编号，商品名称，单价。36．试写出下列查询的关系代数表达式、元组表达式和关系逻辑规则： 检索销售“冰箱”的商店的编号和商店名称。37．试写出上面第36题的SELECT语句表达形式。并写出该查询的图示形式。38．试写出下列操作的SQL语句： 从SALE表中，把“开开商店”中销售单价高于1000元的商品的销售元组全部删除。39．写一个断言，要求区域名为“EAST”的商店里销售商品的单价不能低于100元。40．试写出下列操作的SQL语句： 统计区域名为“EAST”的所有商店销售的每一种商品的总数量和总价值。 要求显示（G#，GNAME，SUM_QUANTITY，SUM_VALUE），其属性为商品编号、商品名称、销售数量、销售价值。37．解：SELECT语句如下： SELECTA.S#，SNAME FROMSHOPA，SALEB，GOODSCWHEREA.S#=B.S#ANDB.G#=C.G#ANDGNAME='冰箱'； 该查询语句的图示形式如下： SHOPS#SNAMEAREAMGR_NAMEP．_XP.SALES#G#QUANTITYGOODSG#GNAMEPRICE_X_Y_Y冰箱38．解：DELETEFROMSALE WHERES#IN（SELECTS# FROMSHOP WHERESNAME='开开商店'） ANDG#IN（SELECTG#FROMGOODS WHEREPRICE>1000）；39．解：CREATEASSERTIONASSE8CHECK （100<=ALL（SELECTPRICE FROMSHOPA，SALEB，GOODSC WHEREA.S#=B.S#ANDB.G#=C.G#ANDAREA='EAST'））； 或CREATEASSERTIONASSE8CHECK （NOTEXISTS（SELECT* FROMSHOPA，SALEB，GOODSC WHEREA.S#=B.S#ANDB.G#=C.G#ANDAREA='EAST'ANDPRICE<100））；40．解：SELECTC.G#，GNAME，SUM（QUANTITY）ASSUM_QUANTITY，PRICE*SUM（QUANTITY）ASSUM_VALUE FROMSHOPA，SALEB，GOODSC WHEREA.S#=B.S#ANDB.G#=C.G#ANDAREA='EAST' GROUPBYC.G#，GNAME； （注：SELECT子句中的属性C.G#，GNAME应在分组子句中出现）36．设数据库中有两个基本表：职工表EMP（E#，ENAME，AGE，SALARY，D#），其属性分别表示职工工号、姓名、年龄、工资和工作部门的编号。部门表DEPT（D#，DNAME，MGR#）,其属性分别表示部门编号、部门名称和部门经理的职工工号。试指出每个表的主键和外键。并写出每个表创建语句中的外键子句。在第36题的两个基本表中，写出下列查询的关系代数表达式和SQL语句：检索每个部门经理的工资，要求显示其部门编号、部门名称、经理工号、经理姓名和经理工资。36．答：EMP表的主键为E#，外键为D#。 DEPT表的主键为D#，外键为MGR# 在EMP表的创建语句中,可写一个外键子句： FOREIGNKEYD#REFERENCESDEPT（D#）； 在DEPT表的创建语句中,可写一个外键子句： FOREIGNKEYMGR#REFERENCESEMP（E#）；MGR#=E#37．答：关系表达式为：πDEPT.D#，DNAME，MGR#，ENAME，SALARY（DEPT⋈EMP）MGR#=E# SELECT语句为： SELECTDEPT.D#，DNAME，MGR#，ENAME，SALARY FROMDEPT，EMP WHEREMGR#=E#；31．设某商业集团为仓库存储商品设计了三个基本表： 仓库 STORE（S#，SNAME，SADDR），其属性是仓库编号、仓库名称和地址。 存储 SG（S#，G#，QUANTITY），其属性是仓库编号、商品编号和数量。 商品 GOODS（G#，GNAME，PRICE）， 其属性是商品编号、商品名称和单价 现检索仓库名称为“莘庄”的仓库里存储的商品的编号和名称。试写出相应的关系代数表达式、元组表达式、关系逻辑规则和SELECT语句。32．在第31题的基本表中，检索存储全部种类商品的仓库的编号及名称。试写出相应的关系代数表达式、元组表达式、关系逻辑规则和SELECT语句。33．在第31题的基本表中，检索每个仓库存储商品的总价值。试写出相应的SELECT语句。要求显示（S#，SUM_VALUE），其属性为仓库编号及该库存储商品的总价值。34．在第31题的基本表中，写一个断言，规定每个仓库存储商品的单价为1万元以上的商品种类最多为20种。31．解：关系代数表达式为：πG#，GNAME（σSNAME='莘庄'（STORE⋈SG⋈GOODS）） 元组表达式为：{t|(u)(v)(w)(GOODS(u)∧SG(v)∧STORE（w）∧u[1]=v[2]∧v[1]=w[1]∧w[2]='莘庄'∧t[1]=u[1]∧t[2]=u[2])}关系逻辑规则如下：W（x，y）GOODS（x，y，a）∧SG（b，x，c）∧STORE（b，'莘庄'，d）SELECT语句为：SELECTA.G#，GNAMEFROMGOODSASA，SGASB，STOREASCWHEREA.G#=B.G#ANDB.S#=C.S#ANDSNAME='莘庄'；32．解：关系代数表达式为： πS#，SNAME（STORE⋈（πS#，G#（SG）÷πG#（GOODS））） 元组表达式为：{t|(u)(v)(w)(STORE(u)∧GOODS（v）∧SG(w)∧w[1]=u[1]∧w[2]=v[1]∧t[1]=u[1]∧t[2]=u[2])}关系逻辑规则如下：W（x，y）←STORE（x，y，a）∧┐GOODS（b，c，d）∧┐SG（x，b，e）SELECT语句为：SELECTS#，SNAMEFROMSTOREWHERENOTEXISTS（SELECT*FROMGOODSWHERENOTEXISTS（SELECT*FROMSGWHERESG.S#=STORE.S#ANDSG.G#=GOODS.G#））；33．解：SELECT语句为： SELECTS#，SUM（QUANTITY*PRICE）ASSUM_VALUE FROMSG，GOODS WHERESG.G#=GOODS.G# GROUPBYS#；34．解：CREATEASSERTIONASSE6CHECK （20>=ALL（SELECTCOUNT（SG.G#）FROMSG，GOODSWHERESG.G#=GOODS.G#ANDPRICE>10000GROUPBYS#））；现有关系数据库如下：部门（部门编号，部门名称，电话）员工(员工编号，姓名，性别，部门编号，职务)项目(项目编号，项目名称，预算)施工(员工编号，项目编号，工时)用关系代数表达式实现下列1—2小题：1．检索“王小毛”的员工的部门编号和职务；2．检索工时超过100小时的项目名称和员工的姓名；用SQL语言实现下列3—7小题：3．查询每个员工的姓名，并按职称降序排列；4．查询总工时在480小时以上（含480小时）的员工编号和总工时；5．检索职称为“高级工程师”，同时至少参加了一预算在100万以上的项目的员工的姓名和部门名称；6．定义一个工作量视图，包含的属性有员工编号、姓名、项目名称和工时。7．对项目名称为“高教新区项目”的施工工时增加50小时；1.π部门编号，职务(σ姓名=‘王小毛’(员工))2.π项目名称，姓名(σ工时)100(施工)项目员工))3.SELECT姓名FROM员工ORDERBY职称DESC4．SELECT员工编号，SUM（工时）FROM施工GROUPBY员工编号HAVINGSUM（工时）>=4805.SELECT姓名，部门名称FROM员工，部门，项目，施工WHERE部门.部门编号=员工.部门编号AND员工.员工编号=施工.员工编号AND施工.项目编号=项目.项目编号AND职称=‘高级工程师’AND预算>10000006.CREATEVIEW工作量(员工编号，姓名，项目名称，工时) ASSELECT员工.员工编号，姓名，项目名称，工时 FROM员工，项目，施工WHERE员工.员工编号=施工.员工编号AND施工.项目编号=项目.项目编号7.UPDATE施工SET工时=工时+50WHERE项目编号IN(SELECT项目编号FROM项目WHERE项目名称=‘高教新区项目’)37.图书出版管理数据库中有两个基本表：图书(书号，书名，作者编号，出版社，出版日期)作者(作者编号，作者名，年龄，地址)试用SQL语句写出下列查询：检索年龄低于作者平均年龄的所有作者的作者名、书名和出版社。39.学校有多名学生，财务处每年要收一次学费。为财务处收学费工作设计一个数据库，包括两个关系：学生(学号，姓名，专业，入学日期)收费(学年，学号，学费，书费，总金额)假设规定属性的类型：学费、书费、总金额为数值型数据；学号、姓名、学年、专业为字符型数据；入学日期为日期型数据。列的宽度自定义。试用SQL语句定义上述表的结构。(定义中应包括主键子句和外键子句)37.SELECT作者名，书名，出版社FROM图书，作者WHERE图书.作者编号=作者.作者编号AND年龄<=(SELECTAVG(年龄)FROM作者)；39.CREATETABLE学生(学号CHAR(8)，姓名CHAR(8)，专业CHAR(13)，入学日期DATE，PRIMARYKEY(学号))；CREATETABLE收费(学年CHAR(10)，学号CHAR(8)，学费NUMERIC(4)，书费NUMERIC(5，2)，总金额NUMERIC(7，2)，PRIMARYKEY(学年，学号)，FOREIGNKEY(学号)REFERENCES学生(学号))；现有关系数据库如下：学生(学号，姓名，性别，专业，奖学金)课程(课程号，名称，学分)学习(学号，课程号，分数)5.检索不学课程号为“C135”课程的学生信息，包括学号，姓名和专业；6.检索至少学过课程号为“C135”和“C219”的学生信息，包括学号、姓名和专业；7.从学生表中删除成绩出现过0分的所有学生信息；8.定义“英语”专业学生所学课程的信息视图AAA，包括学号、姓名、课程号和分数。5.SELECT学号，姓名，专业FROM学生WHERE学号NOTIN(SELECT学号FROM学习WHERE课程号='C135')SELECT学号，姓名，专业FROM学生WHERE学号IN(SELECTX.学号FROM学习

X，学习YWHEREX.学号=Y.学号ANDX.课程号='C135'ANDY.课程号=’C219’)7.DELETEFROM学生WHERE学号IN(SELECT学号FROM学习WHERE分数=0)8.CREATEVIEWAAA(学号，姓名，课程号，分数)ASSELECT学号，姓名，课程号，分数FROM学生，学习WHERE学生.学号=学习.学号AND专业=’英语’用SQL的有关语句定义学生关系Student，包括学号Sno、姓名SN、年龄SA；课程关系Course，包括课程号Cno、课程名CN、任课教师CT；学生选课关系，包括Sno、Cno和成绩G。注意：说明主键码和外键码（如果有的话），并在键码属性上建立索引。 （12分）按题1的学生关系模式，用SQL的有关语句授予赵华对学生关系Student的插入和修改权限；插入任意3个学生的完整信息；修改所有学生的年龄（加1）。（12分）结合题1定义的学生选课数据库，用SQL语句查询计算机系学生数据库课的成绩，输出学生姓名和成绩，按成绩排序（降序），若成绩相同，再按学号排序