《《數(shù)據(jù)查詢》練習(xí)的答案》由會員分享,可在線閱讀,更多相關(guān)《《數(shù)據(jù)查詢》練習(xí)的答案(3頁珍藏版)》請在裝配圖網(wǎng)上搜索。
1、在教務(wù)管理數(shù)據(jù)庫“ EDUC ”中進(jìn)行操作:1、學(xué)生表“Student”中查詢出男生的學(xué)號、姓名、性別的數(shù)據(jù)信息。select SID,Sname,Sexfrom Studentwhere Sex=男2、學(xué)生表“Student”中查詢出前20%的數(shù)據(jù)行select top 20 percent SID,Sname,Sex,Birthdayfrom Student3、學(xué)生表“Student”中查詢出學(xué)校各專業(yè)的名稱(不允許出現(xiàn)重復(fù)字段)select distinct specialtyfrom Student4、學(xué)生表“Student”中查詢出學(xué)生趙成剛的信息。select *from Stud
2、entwhere Sname= 趙成剛5、學(xué)生表“Student”中查詢出男生的信息。select *from Studentwhere Sex= 男6、學(xué)生表“Student”中查詢出在1985-12-1 之前出生的學(xué)生的信息select *from Studentwhere Birthday 1985-12-1 7、學(xué)生表“Student”中查詢出在1985-12-1 之前出生的女學(xué)生的信息select *from Studentwhere Birthday 4的專業(yè)名稱 和人數(shù)select specialty,count(*) as 人數(shù)from Studentgroup by spec
3、ialtyhaving count(*)4在圖書管理數(shù)據(jù)庫“ Library ”中進(jìn)行操作: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 co
4、unt(*) 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 (pri
5、ce ) 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