SQL上机2解答合集.doc

SQL 上机作业2一、数据表结构、数据操作1. 在默认的Students数据库中创建下列数据表：(将每个表的后3位改成自己学号后3位)学生情况表099学号姓名身份证号性别出生日期省份所属单位专业班级联系方式40304001李龙342425760824271男03/09/1983陕西省管理学院信管1班8385801140304060张06/18/1985河南省测量系地质3班8385901201403001王亚08/17/1986湖南省测量系地质1班83858021Create table 学生情况表099(学号 char(10) not null,姓名 char(8),身份证号char(18),性别char(2),出生日期 datetime,省份char(20),所属单位char(20),专业 char(16),班级char(4),联系方式char(12)课程情况表099课程号课程名学时学分是否必修任课教师课程简介028英语12011是张千034高数15013是吴立Create table课程情况表099(课程号char(4) not null,课程名char(20),学时 smallint，学分 tinyint,是否必修char(2),任课教师char(8),课程简介 text)学生选课表099学号课程号选课学期成绩备注40304060028185.54030406103419240304060034196Create table 学生选课表099(学号char(10) not null,课程号char(4) not null,选课学期 char(2),成绩 real,备注 text)2. 按下列要求修改数据表1） 修改“学生情况表099”的结构，为其添加“寝室号”、“照片”字段；Alter table 学生情况表099 add寝室号 char(16),照片 image2） 将“性别”移到“身份证号”前面；将“省份”移到“班级”后面；3） 修改“课程情况表099”，为其添加“职称”、“上课地点”字段；Alter table课程情况表099 add职称 char(20),上课地点 char(30)4） 修改“学生选课表099”，添加“成绩等级”字段；Alter table 学生选课表099 add成绩等级 char(6)5） 将“专业”的类型修改为varchar，宽度修改为30，不允许为空；Alter table 学生情况表099 alter column 专业 varchar(30) not null6） 将“学生情况表099” 的学号修改为不为空、且为主键；Alter table 学生情况表099 Add constraint Pk_xh Primary key(学号)7） 将“课程情况表099”中的课程号修改为不为空、且为主键；Alter table 课程情况表099 Add constraint Pk_kc Primary key(课程号)8） 将“姓名、身份证号、课程名” 修改为不为空。Alter table 学生情况表099 alter column 姓名 char(8) not nullAlter table 学生情况表099 alter column 身份证号 char(18) not nullAlter table 课程情况表099 alter column 课程名 char(20) not null9） 生成3个数据表的脚本文件。3. 用INSERT命令语句，为上面三个数据表录入至少10条记录（输入的学号是从自己学号开始的10个，姓名为自己和后面同学的真实姓名；课程号是自己学号的后三位开始，依次增加；专业、班级为真实信息；课程为自己学过的10门课程；其他数据自定）；并将插入数据的命令保存。Insert into 学生情况表099 Values (0902110188,张三1,男1985-01-01,管理学院,1班,陕西省,83858021,null,null)goInsert into 学生情况表099 Values (0902110189,张三2,女1986-02-02,管理学院,1班,四川省,83858031,null,null)goInsert into 学生情况表099 Values (0902110190,张三3,女1987-02-01,管理学院,1班,山东省,83858041,null,null)GoInsert into 课程情况表099 Values (088,英语,120,8,是,李四1,学好英语非常重要)goInsert into 课程情况表099 Values (089,高数,120,8,是,李四2,学好数学非常重要)goInsert into 课程情况表099 Values (090,管理学,64,4,是,李四3,学好管理知识非常重要)GoInsert into 学生选课表099 Values (0902110188,088,1,95,非常好)goInsert into 学生选课表099 Values (0902110189,089,2,85,很好)goInsert into 学生选课表099 Values (0902110190,090,3,98,非常好)goInsert into 学生选课表099 Values (0902110191,091,4,92,非常好)goInsert into 学生选课表099 Values (0902110192,092,5,75,较好)go4.按下列要求进行查询1）查询自己第1学期高等数学的成绩，只显示学生的学号、姓名、班级、课程名、选课学期、成绩等字段；SELECT 学生情况表099.学号, 姓名, 专业, 班级,课程名, 选课学期,成绩FROM 学生情况表099 INNER JOIN 学生选课表099 ON 学生情况表099.学号 = 学生选课表099.学号 INNER JOIN 课程情况表099 ON 学生选课表099.课程号 = 课程情况表099.课程号WHERE (学生情况表099.学号 = 0802110188) AND (选课学期 = 1) AND (课程情况表099.课程名 = 高数)2）查询与自己同班同学的第1学期大学英语的成绩，只显示学生的学号、姓名、班级、课程名、选课学期、成绩等字段；SELECT 学生情况表099.学号, 姓名, 专业, 班级,课程名, 选课学期, 成绩FROM 学生情况表099 INNER JOIN学生选课表099 ON 学生情况表099.学号 = 学生选课表099.学号 INNER JOIN课程情况表099 ON 学生选课表099.课程号 = 课程情况表099.课程号WHERE (选课学期 = 1) AND (课程情况表099.课程名 = 英语) AND (班级 LIKE 1%) AND (专业 = 电子商务)3）查询第2学期各个院系英语成绩的平均分、最高分、最低分；SELECT 所属单位, AVG(成绩) AS 英语平均, MAX(成绩) AS 英语最高, MIN(成绩) AS 英语最低FROM 学生情况表099 INNER JOIN学生选课表099 ON 学生情况表099.学号 = 学生选课表099.学号 INNER JOIN课程情况表099 ON 学生选课表099.课程号 = 课程情况表099.课程号WHERE (课程情况表099.课程名 = 英语) AND (选课学期 = 2)GROUP BY 所属单位4）查询自己各个学期的选课情况，按“选课学期”升序、“成绩”降序显示学号、姓名、课程名、学时、学分、选课学期、成绩等信息。SELECT 学生情况表099.学号, 姓名,课程名,学时,学分, 选课学期, 成绩FROM 学生情况表099 INNER JOIN学生选课表099 ON 学生情况表099.学号 = 学生选课表099.学号 INNER JOIN课程情况表099 ON 学生选课表099.课程号 = 课程情况表099.课程号WHERE (学生情况表099.学号 = 0902110188)ORDER BY 选课学期, 成绩 DESC5）查询高等数学成绩高于自己的其他同学的相关信息。（显示字段自己确定）SELECT 学生情况表099.学号, 姓名, 专业, 班级,课程名, 选课学期, 成绩FROM 学生情况表099 INNER JOIN学生选课表099 ON 学生情况表099.学号 = 学生选课表099.学号 INNER JOIN课程情况表099 ON 学生选课表099.课程号 = 课程情况表099.课程号WHERE (成绩 (SELECT AVG(成绩) FROM 学生选课表099 a JOIN 课程情况表099 b ON a.课程号 = b.课程号 WHERE 学号 = 0902110188 AND 课程名 = 高数) AND (学生情况表099.学号 0902110188 AND 课程名 = 高数 )6）查询与自己同一个部门且不姓“张”的同学的选课信息情况。SELECT 学生情况表099.学号, 姓名, 专业, 班级,课程名, 选课学期,学时,学分FROM 学生情况表099 INNER JOIN学生选课表099 ON 学生情况表099.学号 = 学生选课表099.学号 INNER JOIN课程情况表099 ON 学生选课表099.课程号 = 课程情况表099.课程号WHERE (所属单位 IN (SELECT 所属单位 FROM 学生情况表099 WHERE 学号 = 0902110188) AND (NOT (姓名 LIKE 张%)7）计算自己所学课程的学时、学分。SELECT SUM(课程情况表099.学时) AS 总学时, SUM(课程情况表099.学分) AS 总学分FROM 学生选课表099 INNER JOIN课程情况表099 ON 学生选课表099.课程号 = 课程情况表099.课程号WHERE (学生选课表099.学号 = 0902110188)8）计算自己每学期的总成绩、平均成绩、最高成绩、最低成绩。SELECT 选课学期, SUM(成绩) AS 总成绩, AVG(成绩) AS 平均成绩, MAX(成绩) AS 最高成绩, MIN(成绩) AS 最低成绩FROM 学生选课表099 INNER JOIN课程情况表099 ON 学生选课表099.课程号 = 课程情况表099.课程号WHERE (学生选课表099.学号 = 0902110188)GROUP BY 选课学期9）计算与自己同一个院系学生各个学期的平均成绩、最高成绩、最低成绩。SELECT 学生选课表099.学号, 选课学期, AVG(成绩) AS 平均成绩, MAX(成绩) AS 最高成绩, MIN(成绩) AS 最低成绩FROM 学生选课表099 INNER JOIN课程情况表099 ON 学生选课表099.课程号 = 课程情况表099.课程号 INNER JOIN学生情况表099 ON 学生选课表099.学号 = 学生情况表099.学号WHERE (所属单位 = 管理学院)GROUP BY 学生选课表099.学号, 选课学期10）统计每门课程的学生人数、最高成绩、最低成绩、平均成绩。SELECT课程名, COUNT(*) AS 人数, AVG(成绩) AS 平均成绩, MAX(成绩) AS 最高成绩, MIN(成绩) AS 最低成绩FROM 学生选课表099 INNER JOIN课程情况表099 ON 学生选课表099.课程号 = 课程情况表099.课程号GROUP BY课程名二、SELECT基本命令在Pubs 数据库上进行简单查询，要求显示的标题均为汉字。1在authors表中检索OaklAND的作者姓名,并显示他们所在的城市名。Select au_lname+au_fname as 姓名,city as 城市名 from authors Where city= OaklAND2在authors表中找出住在CA州的姓White的作者名,并显示他们的州名。Select au_lname+au_fname as 姓名,state as州名 from authors Where state=CA and au_lname=White3在authors表中找出编号第五六位是80的作者的姓名,并显示他们的作者编号Select au_lname+au_fname as 姓名,au_id as编名 from authors Where au_id like _80%4在titles表中检索出书价大于15元的书名及其价格。Select title as 书名,price as 价格 from titles where price155在titles表中显示中包含computer字样的书名。Select title as 书名from titles where title like % computer%6在titles表中找出1994年后出版的书,并显示其价格、出版日期和书名。Select title as 书名,price as 价格 pubdate as 出版日期from titles where pubdate1994-12-317统计authors表中的作者总数,并以authors表的记录总数为标题显示出来。Select count(au_id) as authors表的记录总数 from authors8统计authors表中加州的作者总数,并以作者总数（加州）为标题显示出来。Select count(au_id) as 作者总数（加州） from authors where state=CA9在titles表中求1991年后出版的书的平均价格,以平均数为标题显示出来。Select avg(price) as平均数 from titles where pubdate1991-12-3110 在titles表中显示价格大于20元的书名及其价格，其中价格以降序方式显示，书名以升序方式显示。Select title as 书名,price as 价格 from titles where price20order by price desc, title 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）录入“机械学院”