SQL 上机作业3(2010).doc

SQL Server 上机作业31按下列要求完成查询（注意：以下所出现的099均代表自己学号后3位。）（要求：先将Pubs数据库中的 authors 复制成 auth099、publishers 复制成 publish099、titles 复制成 titl099、sales 复制成 sale099、titleauthor 复制成 titleauth099；将 Northwind数据库的customers 复制成 custom099、employees复制成employ099；然后根据复制的数据表完成下列任务。）1）从auth099表中返回前10%的数据。select top 10 percent * from auth0992）从publish099表中查询出版商的国籍。select distinct country from publish0993）从auth099表中查询作者的姓名以及作者所居住的州和城市。（作者的姓和名之间用空格分隔，州和城市之间用“，”分隔）select au_lname + .+ au_fname as 姓名,city+ ,+state as 州和城市 from auth0994）查询titl099表中，价格打了8折后仍大于12美元的书号、种类、原价以、打折后价格。select title_id as 书号,type as 种类, price as 原价, price * 0.8 as 8折价格 from titl099 where price * 0.8125）从Publish099表中，查询居住城市第3个字符与“自己姓氏的汉语拼音”任意字符匹配的出版商信息。Select * from Publish099 Where substring(city,3,1) like liu6）从auth099表中查询作者名字的最后一个字符与“自己名字的汉语拼音”任意字符匹配的作者信息。Select * from auth099 Where right(rtrim(au_fname),1) like guang7）用两种方法从titl099查询价格在15和20美元之间的书的书号、种类和价格。select title_id as 书号,type as 种类 ,price as 原价from titl099 where price between 15 and 20或select title_id as 书号,type as 种类 ,price as 原价from titl099 where price =15 and price= 208）用两种方法从titl099查询书价大于15和书价小于10的书的书号、种类和价格。9select title_id as 书号,type as 种类, price as 原价 from titl099 where price 20或select title_id as 书号,type as 种类 ,price as 原价from titl099 where price not between 15 and 209）从auth099查询所有居住在KS、CA、MI或IN州的作家。select au_id,au_lname,au_fnamefrom auth099 where state IN (CA,KS,MI,IN)10）从auth099查询出所有au_id满足前2个字符为“72”，第3个字符为3、4、5中的一个，第4个字符为“-”的作家的姓名和电话号码。select au_lname,au_lname, phone, au_idfrom auth099 where au_id like 72345-%11）从auth099查询所有au_id的第一个字符为5-9、第二个字符与“自己学号后两位”任意一个相同的作家的姓名和电话号码。select au_lname,au_lname, phone, au_idfrom auth099 where au_id like 5-901%12）从sale099查询仓储的货物种类。select count(distinct stor_id) as 货物种类 from sale09913）查询titl099中各类书的书号、价格、年销售量和销售金额，并用年销售量和书价进行升序排列。select title_id,price,ytd_sales,销售金额=price*ytd_sales from titl099 order by ytd_sales,price14）在titl099按书的种类分类，任意统计3种类型书籍的价格总和、平均价格以及各类书籍的数量。select type,sum(price) 价格总和 ,avg(price) 平均价格from titl099 where type in(business,mod_cook,trad_cook) group by type15）在titl099按书的种类和出版商代号分类，返回平均价格、最低价格、最高价格。select type,pub_id,avg(price)平均价格,min(price)最低价格，max(price)最高价格from titl099group by type,pub_id16）在titl099所有价格超过10美元的书中，查询所有平均价格超过18美元的书的种类和平均价格。select type , avg(price) 平均价格from titl099 where price10group by type having avg(price)1817）从titl099和titlauth099表中查询书的书号、书名、作者号、类型和价格。select titl099.title_id,title,au_id,type,pricefrom titl099 join titleauth099 on titl099.title_id=titleauth099.title_id18）从titl099、auth099和titlauth099表中查询书的书号、书名、作者号和作者名。select titl099.title_id,title,auth099.au_id,au_lnamefrom titl099 join titleauthor on titl099.title_id=titleauthor.title_idjoin auth099 on auth099.au_id=titleauth099.au_id19）从titl099查询所有价格高于平均价格的书。select title from titl099where price (select averageprice=avg(price) from titl099)20）从auth099、titlauth099查询书号为pc1035的作者的作者号、作者姓名。select au_id,au_lname,au_fnamefrom auth099 where au_id=(select au_id from titleauth099 where title_id=pc1035 )20）从auth099、titlauth099查询所有出版了书的作者的信息。select au_id,au_lname,au_fname from auth099 where au_id in (select au_id from titleauth099)22）从sale099查找销售量大于平均销售量的书的书号、书名。select title_id,titlefrom sale099 where qty (select avg(qty) from sale099)23）从auth099、titl099、titlauth099查询商业类书的名称、价格、作者姓名，并将查询结果存入一个新表B_TITLAUTH099。select title,price,au_fname,au_lnameinto b_titleauth099 from titl099 t join titleauth099 ta on (t. title_id=ta. title_id ) join auth099 a on (a.au_id=ta.au_id)where type=business24）将Custom099表中顾客ID、姓名及Order099表中销售人员的ID、姓名组合在一个结果集中。Use NorthwindSelect CustomerID,contactName from Custom099UnionSelect CustomerID,shipName from Order09925）查询在Employ099表中以字母A-M作为Firstname第一个字母的雇员，按生日BirthDate从小到大进行排列。并将查询结果存入一个新表AM_Employ099Select * from Employ099 into AM_Employ099Where Firstname like A-M%Order by BirthDate2查询及综合应用（先将提供的SQL脚本文件中的jbqk099、cjb099表的名称修改成自己学号后3位再运行）1）查询本专业、与自己同省份的学生信息，按省份升序、性别降序、姓名升序进行排序，显示学生的学号、姓名、性别、省份及各门课程成绩。Select jbqk099.学号,姓名,性别,省份,高等数学,大学英语,数据库,管理学from jbqk099 inner join cjb099 on jbqk099.学号= cjb099.学号where 所学专业=电子商务 and 省份 =(select 省份 from jbqk099 where jbqk099.学号=0802110199)Order by 省份, 性别 desc, 姓名2）统计管理学院各专业男女生高等数学的平均分、最高分、最低分及人数，按专业降序排列。Select 所学专业,性别,avg(高等数学) as 平均分, max(高等数学) as 最高分, min(高等数学) as 最低分from jbqk099 inner join cjb099 on jbqk099.学号= cjb099.学号where 所属院系=管理学院group by所学专业,性别3）统计管理学院各专业大学英语的平均分、学习人数，按专业降序排列，只显示学习人数不低于3人的专业。Select 所学专业,avg(大学英语) as 平均分, count(*) as 人数from jbqk099 inner join cjb099 on jbqk099.学号= cjb099.学号where 所属院系=管理学院group by所学专业 having count(*)=3order by 所学专业 desc4）统计总成绩前10名的学生，显示学号、姓名、专业、各门课程成绩及总成绩，按总成绩、专业、学号排序。Select top 10 jbqk099.学号,姓名,性别,所学专业,高等数学,大学英语,数据库,管理学, 总成绩=(高等数学+大学英语+数据库+管理学) from jbqk099 inner join cjb099 on jbqk099.学号= cjb099.学号order by总成绩, 所学专业, jbqk099.学号5）查询高等数学成绩高于平均成绩的学生信息，显示学号、姓名、专业、高等数学等；Select jbqk099.学号,姓名,所学专业,高等数学 from jbqk099 inner join cjb099 on jbqk099.学号= cjb099.学号where高等数学(select avg(高等数学) from cjb099)6）查询信息管理专业男生的高等数学成绩、电子商务专业女生的大学英语成绩、能源学院所有学生的数据库成绩，显示显示学号、姓名、性别、院系、专业、学习成绩等信息，按院系、专业排序；Select jbqk099.学号,姓名,性别,所属院系,所学专业,高等数学 as 学习成绩 from jbqk099 inner join cjb099 on jbqk099.学号= cjb099.学号where所学专业=信息管理 and 性别=男unionSelect jbqk099.学号,姓名,性别,所属院系,所学专业,大学英语 from jbqk099 inner join cjb099 on jbqk099.学号= cjb099.学号where所学专业=电子商务 and 性别=女unionSelect jbqk099.学号,姓名,性别,所属院系,所学专业,数据库 from jbqk099 inner join cjb099 on jbqk099.学号= cjb099.学号where院系名称=能源学院order by 院系名称, 所学专业7）将本专业不及格学生的信息保存在数据表bjg_xs ，保存学号、姓名、专业、各门课程成绩。Select jbqk099.学号,姓名,所学专业,高等数学,大学英语,数据库,管理学from jbqk099 inner join cjb099 on jbqk099.学号= cjb099.学号where所学专业=电子商务 and (高等数学60 or 大学英语60 or 数据库60 or管理学60)8）对本专业高等数学不及格的学生，将数学成绩提高15%；Update cjb099Set 高等数学=高等数学*1.15 Where cjb099.学号 in (select jbqk099.学号 from jbqk099 inner join cjb099 on jbqk099.学号= cjb099.学号wher所学专业=电子商务 and 高等数学60)9）按学院、专业列出高等数学的明细，并统计各专业的平均、最高、最低成绩；Select 学号，姓名，所属院系，所学专业，高等数学from jbqk099 inner join cjb099 on jbqk099.学号= cjb099.学号order by所学专业compute avg(高等数学), max(高等数学), min(高等数学) by 所学专业10）列出管理学院各个专业的大学英语明细，并统计各专业的平均、最高、最低成绩；然后计算全院的平均、最高、最低成绩。Select 学号，姓名，所属院系，所学专业，大学英语from jbqk099 inner join cjb099 on jbqk099.学号= cjb099.学号order by 所学专业compute avg(大学英语), max(大学英语), min(大学英语) by 所学专业compute avg(大学英语), max(大学英语), min(大学英语)3数据表的综合练习（根据jbqk099、cjb099表快速生成数据表jb_cj099的结构，字段包括学号、姓名、性别、出生日期、院系名称、所学专业、高等数学、大学英语、数据库、管理学；并完成下列操作）1）录入“管理学院”学生信息，只包括学号,姓名,性别,出生日期,院系名称,所学专业,高等数学,数据库的相关信息；Insert into jb_cj099(学号,姓名,性别,出生日期,院系名称,所学专业,高等数学,数据库) Select 学号,姓名,性别,出生日期,院系名称,所学专业,高等数学,数据库from jbqk099 inner join cjb099 on jbqk099.学号= cjb099.学号where院系名称=管理学院2）录入“机械学院”学生信息，只包括学号,姓名,性别,出生日期,院系名称,所学专业, 大学英语,管理学的相关信息；Insert into jb_cj099(学号,姓名,性别,出生日期,院系名称,所学专业,大学英语,管理学) Select 学号,姓名,性别,出生日期,院系名称,所学专业, 大学英语,管理学from jbqk099 inner join cjb099 on jbqk099.学号= cjb099.学号where院系名称=机械学院3）录入“能源学院”、“人文学院”学生信息；Insert into jb_cj099Select 学号,姓名,性别,出生日期,院系名称,所学专业, 高等数学，大学英语,数据库，管理学from jbqk099 inner join cjb099 on jbqk099.学号= cjb099.学号where院系名称=能源学院 or 院系名称=人文学院4）修改“学号”的宽度为10，并将本专业的学号前7位改为专业的编号、第8位为1或2（随机产生）、后两位不变；Alter table jb_cj099 alter column 学号 char(10)Update jb_cj099Set 学号=0902110+str(floor(1+rand()*2),1)+right(rtrim(学号),2)5）添加“年龄”字段，并计算每个学生的年龄；Alter table jb_cj099 add 年