《數(shù)據(jù)查詢》練習的答案
在教務管理數(shù)據(jù)庫“ EDUC ”中進行操作:1、學生表“Student”中查詢出男生的學號、姓名、性別的數(shù)據(jù)信息。select SID,Sname,Sexfrom Studentwhere Sex='男'2、學生表“Student”中查詢出前20%的數(shù)據(jù)行select top 20 percent SID,Sname,Sex,Birthdayfrom Student3、學生表“Student”中查詢出學校各專業(yè)的名稱(不允許出現(xiàn)重復字段)select distinct specialtyfrom Student4、學生表“Student”中查詢出學生趙成剛的信息。select *from Studentwhere Sname=' 趙成剛'5、學生表“Student”中查詢出男生的信息。select *from Studentwhere Sex=' 男'6、學生表“Student”中查詢出在'1985-12-1 '之前出生的學生的信息select *from Studentwhere Birthday < ' 1985-12-1 '7、學生表“Student”中查詢出在'1985-12-1 '之前出生的女學生的信息select *from Studentwhere Birthday < ' 1985-12-1 ' and Sex=' 女'8、學生表“Student”中查詢姓“李”的學生信息select *from Studentwhere Sname like ' 李%'9、學生表“Student”中查詢學號為2005216007和2006216578的學生信息select *from Studentwhere S ID = 2005216007 or S ID = 20062165710、學生表“Student”中查詢出各專業(yè)的學生總數(shù),要求查詢結(jié)果顯示專業(yè)名稱和人數(shù) 兩個列select specialty,count(*) as 人數(shù)from Studentgroup by specialty11、學生表“Student”中查詢出各專業(yè)的學生總數(shù),要求顯示專業(yè)人數(shù)>4的專業(yè)名稱 和人數(shù)select specialty,count(*) as 人數(shù)from Studentgroup by specialtyhaving count(*)>4在圖書管理數(shù)據(jù)庫“ Library ”中進行操作:1、圖書表“book”中查詢出前5行數(shù)據(jù)。select top 5 bid,bname,authorfrom book2、讀者類型表“ ReaderType”中查詢出所有數(shù)據(jù)。select *from ReaderType3、圖書表“book”中查詢出所有圖書折價90%后的價格select bid,bname,author,pubcomp,price,price*0.9 as 折價后的價格 from book4、圖書表“book”中統(tǒng)計出高等教育出版社出版的圖書數(shù)量(使用count()select count(*) as 冊數(shù) from bookwhere pubcomp='高等教育出版社'5、圖書表“ book ”中統(tǒng)計各出版社的個數(shù)(每個出版社只計數(shù)一次)select count(distinct (pubcomp) as 出版社個數(shù)from book6、圖書表“ book ”中查詢出圖書的總冊數(shù)、最高價、最低價、總價值、折扣后的總價值和 評價價select count (price ) as 冊數(shù),max (price ) as 最高價,min (price ) as 最低價, sum (price ) as 總價值,sum (price *0.9) as 折價后的總價值,avg (price ) as 平均價 from book7、 圖書表“book”中查詢定價在10元到15元之間的圖書信息。select bid,bname,price from bookwhere price between 10 and 158、圖書表“book”中查詢有關(guān)ERP方面的圖書。select *from bookwhere bname like '%ERP%'9、從表“Borrow”中查詢還沒有歸還的圖書的信息。select book.*from book,borrowwhere book.bid=borrow.bid and borrow.returndate is null10、圖書表“ book ”中查詢各出版社圖書的總價。select pubcomp,sum(price) as 總價 from bookgroup by pubcomp11、圖書表“ book ”中查詢圖書信息,并按照出版社名稱升序和價格降序排序select *from bookorder by pubcomp asc,price desc