SQL复习题

1.已知有图书借阅管理数据库，包含三张表，如下图，针对这三张表设计SQL语句进行答题。Book表：列名数据类型描述约束IDChar（10）图书编号主码NameVarChar（30）书名非空AuthorVarChar（10）作者PublishVarChar（20）出版社默认值“东软出版社”PriceDecimal（6,2）价格取值大于0Reader表：列名数据类型描述约束IDChar（10）读者编号主码NameVarChar（8）读者姓名非空SexVarChar（1）读者性别取值“男”或者“女”BirthDateDatetime出生日期Borrow表：列名数据类型描述约束Book_IdChar(10)图书编号BookID号引用Book的外码Reader_IdChar(10)读者编号（BookIDReaderID）是联合主码且ReaderID是引用Reader的外码BorrowDateDATETIME借阅时间（1）写出创建BOOk的SQL语句CreatetableBook(IDchar(10)primarykey,Namevarchar(30)notnull,Authorvarchar(10)Publishvarchar(20)default‘东软出版社’,Pricedecimal(6,2)check(price0))(2)向Book表添加记录（‘A12DT90888’,’专业外语’,’冯刚’,’东软出版社’,42）InsertintoBookvalues(‘A12DT90888’,’专业外语’,’冯刚’,’东软出版社’,42)(3)更新Book表，将‘东软电子出版社’的书涨价10%。Updatebooksetprice=price*110%wherepublish=’东软出版社’(4)查询所有读者信息，按照读者编号升序排列Select*fromreaderorderbyidasc(5)查找图书价格在20到50之间的所有信息Select*frombookwherepricebetween20and50(6)查询出版图书数量最多的出版社的名称Selectpublishfrombookgroupbypublishhavingcount(*)=all(Selectcount(*)frombookgroupbypublish)(7)在Book表的定价列Price上创建一个名字为IdxPrice的序列索引CreateindexIndexPriceonBook(desc)(8)创建视图viewAll通过此视图可以查看每个读者的编号，读者姓名，所借图书编号，书名，借阅时期CreateviewviewAll(读者标号，读者姓名，所借图书编号，书名，借阅时期)asSelectreader.id,reader.name,book_id,book_name,borrowdateFromreaderjoinborrowonreader.id=borrow.readerId,joinBookonBook.ID=Borrow.BookId(9)通过上面的视图，ViewAll查询读者=“胡晓丽”借阅“数据库原理”的借阅时间Select借阅日期fromViewAllwhere读者姓名=“胡晓丽”and书名=“数据库原理”(10)在Borrow表上创建触发器TrBorrow,限制每个读者最多能借阅3本书CreatetriggerTrBorrowonBorrowafterinsertAsBeginDeclare@numintSelect@num=count(*)fromborrowwherereaderidin(selectreaderidfrominserted)If(@num3)BeginRollbackEndEnd2.设有关系职工表（职工号，职工名，部门号，工资）和部门表（部门号，部门名，主任），期中职工表的主码是职工号，部门表的主码是部门号，职工表的部门号是外码，参照部门表的主码部门号，用SQL或TSQL完成下列要求：(1)根据下列要求创建职工表职工号：3位字符串，主码；职工名：8位字符串，非空；部门号：2位字符串，外码，参照部门表的主码部门号；工资：decimal（7,2），大于0.Createtable职工（职工号char（3）primarykey，职工名char（8）notnull，部门号char（2）foreignkey（部门号）references部门（部门号），工资decimal（7,2）check（工资0））(2)向职工表中插入行（‘025’，‘王芳’，‘03’，1000）。Insertinto职工(职工号,职工名，部门号，工资)values(‘025’，‘王芳’，‘03’，1000)(3)从职工表中删除人事处的所有员工。Deletefrom职工where部门号=(select部门号from部门where部门名=‘人事处’)(4)将职工号为’001’的员工工资改为700元钱。Update职工set工资=700where职工号=’001’(5)查询所有员工的最高工资SelectMAX(工资)from职工(6)查询所有员工信息，结果先按部门号升序排列，再按职工号降序排列Select*from职工orderby部门号asc,职工号desc(7)查询“王芳”所在部门的部门主任Select主任from部门where部门号=(select部门号from职工where职工名=’王芳’)(8)建立公司所有部门的公共视图ViewAll,其中包含部门号、部门名、职工号、职工名和工资信息。CreateviewViewAllasselect职工.部门号，部门名，职工号，职工名，工资from职工join部门on职工.部门号=部门.部门号(9)从部门职工视图ViewAll中查询财务处员工工资高于800元的员工信息Select*fromViewAllwhere部门名=’财务处’and工资800(10)创建触发器T1，限定当一个部门没有员工时，才允许删除该部门。提示（创建部门表的Delete触发器）。CretetriggerT1on部门insteadofdeleteasBeginDeclare@eCountintSelect@eCount=count(*)from职工where部门号=（select部门号fromdeleted）If@eCount=0BeginDeletefrom部门where部门号=（select部门号fromdeleted）EndEnd3.已知有顾客购买商品信息的三张表：顾客CS，订购表Book，商品表CM。CS表：属性：ID字符型最大10个字符——顾客编号NAME字符型最大16个字符——顾客姓名SEX字符型最大2个字符——性别TEL字符型最大11个字符——移动电话ADDR字符型最大50个字——家庭住址约束：ID——主码，NAME——非空，SEX——取值“男”或“女”；MOBILE——唯一性，ADDR——默认值为“UNKOWN”；Book表：属性：CSID字符型最大10个字符——顾客编号CMID字符型最大10个字符——商品编号BCOUNT整型——订购数量BOOKDATE日期型——订货日期TAKEDATE日期型——交货日期约束：CSID,CMID——主码；外码CSID,参照CS表的ID；外码CMID，参照CM表的ID，订购数量要大于等于0；CM表：属性：ID字符型最大10个字符——商品编号NAME字符型最大20个字符——商品名称MANUF字符型最大20个字符——生产厂商PRICE小数型最大不超过4位数，保留2位小数——商品单价约束：ID——主码；NAME——非空；针对上面的三个基本表写出如下命令要求的SQL命令：（30分）（1）按要求创建顾客表CSCREATETABLECS（IDchar（10）primarykey,NAMEvarchar（16）notnull,SEXchar(2)check(sex=’男’orsex=’女’)，MOBILKEchar（11）unique,ADDRchar（50）default‘UNKOWN’)（2）修改“WANYANG”顾客订购记录的交货期为2005—12—25UpdateBooksettakedate=‘2005—12—25’whereCSIDin（selectIDfromCSwherename=‘WANGYAN’）（3）往基本表CS中插入顾客元组（“0421F901”，“WU”,“女”，13980011001）InsertintoCS（ID，NAME,Sex,Tel）values（‘0421F901’，‘WU’，‘女’，‘13980011001’）（4）删除所有订购数量小于10的订购记录DeletefromBookwherebcount10（5）查询顾客名为“NANA”顾客的手机号和住址SelectMOBILE，ADDRfromCSwherename=‘NANA’（6）查询商品的平均价格高于75元钱的厂商名称SelectMANUFfromCMgroupbyMANUFhavingavg（price）75（7）查询订购数量超过100的顾客姓名，电话和地址Selectname，tel，addr，fromCSjoinbookonCS.ID=Book.CSIDwherebcount100（8）查询所有订购过商品的顾客编号SelectdistinctCSIDfromBook（9）建立订购商品号为“M900532481”的顾客的编号，住址，订购数量，交货日期的视图View001。CreateviewView001（顾客编号，住址，订货数量，交货日期）asselectid，addr，bcount，takedatefromCMjoinBookonCM.ID=Book.cmidwhereCMID=“M900532481”（10）创建触发器TCheekBook，当向Book表插入记录时，判断订货日期是否小于交货日期，如果“否”，则不能插入CreateTriggerTCheckBookonBookforInsertASBeginDeclare@bookdatedatetime，@takedatedatetimeSelect@bookdate=bookdate，@takedate=takedatefrominsertedIf@bookdate@takedaterollbackEnd4．现在一个公司的部门及雇员的数据库，由下面三张表组成：雇员（雇员号，姓名，年龄，地址，薪水）部门（部门号，部门名，部门经理）工作（雇员号，部门号，工作年限）其中，雇员表的主码是雇员号；部门表的主码是部门号，部门经理是外码，参照雇员表的雇员号；工作表的主码是雇员号和部门号，且工作表中雇员号和部门号是外码，分别参照雇员表的主码雇员号和部门表的主码部门号。试用SQL或TSQL语言完成下列功能：(1)创建雇员表，数据类型要求如下：雇员号为10位字符串，是主码；姓名定义为8位字符串，要求非空；年龄定义为整型，介于18—55之间；地址定义为50位字符串，薪水定义为0-10000之间的2位小数。createtable雇员(雇员号char(10)primarykey,姓名varchar(8)notnull,年龄intcheck(年龄=18and年龄=55)，地址varchar(50)，薪水decimal(6，2)(2)往雇员表中插入数据（E01,tom,35,海淀,8000）insertinto雇员values(‘E01’,’tom’,35,’海淀’,8000)(3)更新名为“tom”的雇员其工资为9000.update雇员set工资=9000where姓名=’tom’(4)删除tom在“国企合作部”的工作记录。deletefrom工作where雇员号in（select雇员号from雇员where姓名=’tom’）and部门号in(select部门号from部门where部门号=’国企合作部’)(5)查询工资超过5000的雇员号和姓名selec