SQL課上作業(yè)與答案 (1)

1、精選文檔1、 查詢xs表中計(jì)算機(jī)專業(yè)同學(xué)的學(xué)號(hào)、姓名和總學(xué)分，結(jié)果中各列的標(biāo)題分別指定為number、name和mark。 Select 學(xué)號(hào) as number,姓名 as name,總學(xué)分 as mark from xs where 專業(yè)名="計(jì)算機(jī)"2、查詢xs表中計(jì)算機(jī)專業(yè)各同學(xué)的學(xué)號(hào)、姓名和總學(xué)分，對(duì)總學(xué)分按如下規(guī)章進(jìn)行替換：若總學(xué)分為空值，替換為“尚未選課”；若總學(xué)分小于50，替換為“不及格”；若總學(xué)分在5052之間，替換為“合格”；若總學(xué)分大于52，替換為“優(yōu)秀”。總學(xué)分列的標(biāo)題更改為“等級(jí)”。 select 學(xué)號(hào),姓名,case when 總學(xué)分 is nu

2、ll then "尚未選課" when 總學(xué)分 < 50 then "不及格" when 總學(xué)分 >=50 and 總學(xué)分 <=52 then "合格" else "優(yōu)秀" end as 等級(jí) from xs where 專業(yè)名="計(jì)算機(jī)"3、 按120分制重新計(jì)算成果，顯示xs_kc表中學(xué)號(hào)為81101的同學(xué)成果信息。 select 學(xué)號(hào),課程號(hào),成果*1.2 as 成果120 from xs_kc where 學(xué)號(hào)="81101"3 rows in se

3、t4、 對(duì)xscj數(shù)據(jù)庫(kù)的xs表只選擇專業(yè)名和總學(xué)分，消退結(jié)果集中的重復(fù)行。 select distinct 專業(yè)名,總學(xué)分 from xs;8 rows in set5、 統(tǒng)計(jì)備注不為空的同學(xué)數(shù)目。 select count(備注) as 人數(shù) from xs where 備注 is not null;6、 統(tǒng)計(jì)總學(xué)分在50分以上的人數(shù)。 select count(總學(xué)分) as 人數(shù) from xs where 總學(xué)分 > 50;1 row in set7、 求選修101課程的同學(xué)的最高分和最低分。 select max(成果) as 最高分 ,min(成果) as 最低分 from

4、 xs_kc where 課程號(hào)="101" 1 row in set8、 求學(xué)號(hào)081101的同學(xué)所學(xué)課程的總成果。 select sum(成果) as 總成果 from xs_kc where 學(xué)號(hào)="81101"9、 求選修101課程的同學(xué)的平均成果。 select 學(xué)號(hào),avg(成果) from xs_kc where 課程號(hào)="101"10、 求選修101課程的成果的方差。 select variance(成果) from xs_kc where 課程號(hào)="101"11、 求選修101課程的成果的標(biāo)準(zhǔn)差。

5、 select stddev(成果) from xs_kc where 課程號(hào)="101"12、 求選修了206課程的同學(xué)的學(xué)號(hào)。 select group_concat(學(xué)號(hào)) from xs_kc where 課程號(hào)="206"13、 從xs表中檢索出全部同學(xué)的信息，并使用表別名student。 select * from xs as student;14、 查找xscj數(shù)據(jù)庫(kù)中全部同學(xué)選過的課程名和課程號(hào) select distinct kc.課程名,xs_kc.課程號(hào) from kc,xs_kc where kc.課程號(hào)=xs_kc.課程號(hào);3

6、rows in set15、 用FROM子句的JOIN關(guān)鍵字表達(dá)下列查詢：查找選修了206課程且成果在80分以上的同學(xué)姓名及成果。 select 姓名,成果 from xs inner join xs_kc on xs.學(xué)號(hào)=xs_kc.學(xué)號(hào) where 課程號(hào)="206" and 成果 > 80;3 rows in set16、 用FROM的JOIN關(guān)鍵字表達(dá)下列查詢：查找選修了“計(jì)算機(jī)基礎(chǔ)”課程且成果在80分以上的同學(xué)學(xué)號(hào)、姓名、課程名及成果。 select xs.學(xué)號(hào),姓名,課程名,成果 from xs join xs_kc on xs.學(xué)號(hào)=xs_kc.學(xué)號(hào)

7、join kc on xs_kc.課程號(hào)=kc.課程號(hào) where 課程名="計(jì)算機(jī)基礎(chǔ)" and 成果 > 80;8 rows in set17、 查找xscj數(shù)據(jù)庫(kù)中課程不同、成果相同的同學(xué)的學(xué)號(hào)、課程號(hào)和成果。 select a.學(xué)號(hào),a.課程號(hào),b.課程號(hào),a.成果 from xs_kc as a join xs_kc as b on a.成果=b.成果 and a.學(xué)號(hào)=b.學(xué)號(hào) and a.課程號(hào) != b.課程號(hào);2 rows in set18、 查找全部同學(xué)狀況及他們選修的課程號(hào)，若同學(xué)未選修任何課，也要包括其狀況。 select xs_kc.*,課程

8、號(hào) from xs left outer join xs_kc on xs.學(xué)號(hào)=xs_kc.學(xué)號(hào);19、 查找被選修了的課程的選修狀況和全部開設(shè)的課程名。 select xs_kc.*,課程名 from xs_kc right join kc on xs_kc.課程號(hào)=kc.課程號(hào);20、 列出同學(xué)全部可能的選課狀況。 mysql> select 學(xué)號(hào),姓名,課程號(hào),課程名 -> from xs cross join kc;21、 查詢xscj數(shù)據(jù)庫(kù)xs表中學(xué)號(hào)為81101的同學(xué)的狀況。 select 學(xué)號(hào),姓名,總學(xué)分 from xs where 學(xué)號(hào)="81101&

9、quot;1 row in set22、 查詢xs表中總學(xué)分大于50分的同學(xué)的狀況。 select * from xs where 總學(xué)分 > 50;23、 查詢xs表中備注為空的同學(xué)的狀況。 select * from xs where 備注 is null;mysql> select * from xs where 備注 <=> null;24、 查詢xs表中專業(yè)為計(jì)算機(jī)，性別為女（0）的同學(xué)的狀況。 select * from xs where 專業(yè)名="計(jì)算機(jī)" and 性別=0;25、 查詢xscj數(shù)據(jù)庫(kù)xs表中姓“王”的同學(xué)學(xué)號(hào)、姓名及性

10、別。 select 學(xué)號(hào),姓名,性別 from xs where 姓名 like "王%"26、 查詢xscj數(shù)據(jù)庫(kù)xs表中，學(xué)號(hào)倒數(shù)其次個(gè)數(shù)字為0的同學(xué)的學(xué)號(hào)、姓名及專業(yè)名。 select 學(xué)號(hào),姓名,專業(yè)名 from xs where 學(xué)號(hào) like "%0_"|查詢xs表中名字包含下畫線的同學(xué)學(xué)號(hào)和姓名。 select 學(xué)號(hào),姓名 from xs where 學(xué)號(hào) like "%#_%" escape "#"28、查詢xscj數(shù)據(jù)庫(kù)xs表中不在1993年誕生的同學(xué)狀況。select * from xs whe

11、re 誕生時(shí)間<=>1993;29、查詢xs表中專業(yè)名為“計(jì)算機(jī)”、“通信工程”或“無線電”的同學(xué)的狀況。select * from xs where 專業(yè)名="計(jì)算機(jī)" or 專業(yè)名="通信工程" or 專業(yè)名="無線電"30、查詢xscj數(shù)據(jù)庫(kù)中總學(xué)分尚不定的同學(xué)狀況。select * from xs where 總學(xué)分 is null;31、查找在xscj數(shù)據(jù)庫(kù)中選修了課程號(hào)為206的課程的同學(xué)的姓名、學(xué)號(hào)。select 姓名,學(xué)號(hào) from xs where 學(xué)號(hào) in(select 學(xué)號(hào) from xs_kc w

12、here 課程號(hào)="206");32、查找未選修離散數(shù)學(xué)的同學(xué)的姓名、學(xué)號(hào)、專業(yè)名。select 姓名,學(xué)號(hào),專業(yè)名 from xs where 學(xué)號(hào) not in (select 學(xué)號(hào) from xs_kc where 課程號(hào) in (select 課程號(hào) from xs where 課程號(hào)="離散數(shù)學(xué)");33、查找選修了離散數(shù)學(xué)的同學(xué)學(xué)號(hào)。select 學(xué)號(hào) from xs_kc where 課程號(hào) = (select 課程號(hào) from kc where 課程名="離散數(shù)學(xué)");34、查找xs表中比全部計(jì)算機(jī)系的同學(xué)年齡都大的同

13、學(xué)學(xué)號(hào)、姓名、專業(yè)名、誕生日期。select 學(xué)號(hào),姓名,專業(yè)名,誕生時(shí)間 from xs where 誕生時(shí)間<all(select 誕生時(shí)間 from xs where 專業(yè)名="計(jì)算機(jī)");35、查找xs_kc表中課程號(hào)206的成果不低于課程號(hào)101的最低成果的同學(xué)的學(xué)號(hào)。select 學(xué)號(hào) from xs_kc where 課程號(hào)="206" and 成果>any(select 成果 from xs_kc where 課程號(hào)="101");36、查找選修206號(hào)課程的同學(xué)姓名。select 姓名 from xs w

14、here exists (select * from xs_kc where 課程號(hào)="206" and 學(xué)號(hào)=xs.學(xué)號(hào));37、查找選修了全部課程的同學(xué)的姓名。select 姓名 from xs where not exists (select * from kc where not exists (select * from xs_kc where 學(xué)號(hào)=xs.學(xué)號(hào) and 課程號(hào)=kc.課程號(hào));38、從xs表中查找總學(xué)分大于50分的男同學(xué)的姓名和學(xué)號(hào)。select 姓名,學(xué)號(hào) from xs where 總學(xué)分>50 and 性別=1;select 姓名,學(xué)

15、號(hào) from xs where 學(xué)號(hào) in (select 學(xué)號(hào) from xs where 總學(xué)分>50 and 性別=1);select 姓名,學(xué)號(hào),總學(xué)分 from (select 姓名,學(xué)號(hào),性別,總學(xué)分 from xs where 總學(xué)分 > 50) as student where 性別=1;39、從xs表中查找全部女同學(xué)的姓名、學(xué)號(hào)，以及與81101號(hào)同學(xué)的年齡差距。select 姓名,學(xué)號(hào),year(誕生時(shí)間)-year(select 誕生時(shí)間 from xs where 學(xué)號(hào)="81101" ) ) as 年齡差距 from xs where

16、性別=0;40、查找與81101號(hào)同學(xué)性別相同、總學(xué)分相同的同學(xué)學(xué)號(hào)和姓名。select 學(xué)號(hào),姓名 from xs where (性別,總學(xué)分)=(select 性別,總學(xué)分 from xs where 學(xué)號(hào)="81101");41、將xscj數(shù)據(jù)庫(kù)中各專業(yè)名輸出。select 專業(yè)名 from xs group by 專業(yè)名;42、求xscj數(shù)據(jù)庫(kù)中各專業(yè)的同學(xué)數(shù)。select 專業(yè)名,count(*) as "同學(xué)數(shù)" from xs group by 專業(yè)名;43、求被選修的各門課程的平均成果和選修該課程的人數(shù)。select 課程號(hào),avg(成果

17、) as "平均成果",count(*) as "總?cè)藬?shù)" from xs_kc group by 課程號(hào);44、在xscj數(shù)據(jù)庫(kù)上產(chǎn)生一個(gè)結(jié)果集，包括每個(gè)專業(yè)的男生人數(shù)、女生人數(shù)、總?cè)藬?shù)，以及同學(xué)總?cè)藬?shù)。select 專業(yè)名,性別,count(*) as 總?cè)藬?shù) from xs group by 專業(yè)名,性別 with rollup;45、在xscj數(shù)據(jù)庫(kù)上產(chǎn)生一個(gè)結(jié)果集，包括每門課程各專業(yè)的平均成果、每門課程的總平均成果和全部課程的總平均成果。select 課程名,專業(yè)名,avg(成果) as 平均成果 from xs_kc,kc,xs where

18、xs_kc.課程號(hào) =kc.課程號(hào) and xs_kc.學(xué)號(hào)=xs.學(xué)號(hào) group by 課程名,專業(yè)名 with rollup;46、查找xscj數(shù)據(jù)庫(kù)中平均成果在85分以上的同學(xué)的學(xué)號(hào)和平均成果。select 學(xué)號(hào),avg(成果) as "平均成果" from xs_kc group by 學(xué)號(hào) having avg(成果)>=85;47、查找選修課程超過2門且成果都在80分以上的同學(xué)的學(xué)號(hào)。select 學(xué)號(hào) from xs_kc group by 學(xué)號(hào) having count(*) >2;48、查找通信工程專業(yè)平均成果在85分以上的同學(xué)的學(xué)號(hào)和平均成果。select 學(xué)號(hào),avg(成果) as "平均成果" from xs_kc where 學(xué)號(hào) in (select