资源描述：
《ORCLE数据库查询语句》由会员上传分享，免费在线阅读，更多相关内容在教育资源-天天文库。
1、/*1:查询书城所有图书信息*/SPOOLD:AOUTPUT1.SQL;SELECT*FROMBOOKS;SPOOLOFF;/*2:查询书城所有图书的类别*/SPOOLD:AOUTPUT2.SQL;SELECTDISTINCTCATEGORYFROMBOOKS;SPOOLOFF;/*3:查询类别为textbook的图书信息*/SPOOLD:AOUTPUT3.SQL;SELECT*FROMBOOKSWHERECATEGORY='textbook';SPOOLOFF;/*4:查询类别为romantic、cooking、pol
2、itics的书名和作者*/SPOOLD:AOUTPUT4.SQL;SELECTtitle,authorFROMBOOKSWHEREcategoryIN('romantic','cooking','politics');SPOOLOFF;/*5:查询当前价格最高的图书的定价*/SPOOLD:AOUTPUT5.SQL;SELECTMAX(price)FROMpricing;SPOOLOFF;/*6:查询既买了id是101的图书，且购买时书价是20元的顾客id及购买日期*/SPOOLD:AOUTPUT6.SQL;SELECTc
3、id,pdateFROMpurchasesWHEREBOOKID=101ANDPPRICE=20;SPOOLOFF;/*7:查询每一位顾客的消费总额，设置消费总额别名为“COST”,并按总额降序排列*/SPOOLD:AOUTPUT7.SQL;SELECTCID,SUM(PPRICE)COSTFROMpurchasesGROUPBYcidORDERBYCOSTDESC;SPOOLOFF;/*8:查询消费总额>100元的顾客id及消费总额*/SPOOLD:AOUTPUT8.SQL;SELECTCID,SUM(PPRICE)COS
4、TFROMpurchasesGROUPBYcidHAVINGSUM(PPRICE)>'100';SPOOLOFF;/*9:查询购买书名中含“Database”的图书的顾客id*/SPOOLD:AOUTPUT9.SQL;SELECTDISTINCTCIDFROMBOOKS,PURCHASESWHEREBOOKS.bookid=PURCHASES.bookidANDTITLELIKE'%Database%';SPOOLOFF;/*10:查询购买书名中含“Database”的图书的顾客id、年龄*/SPOOLD:AOUTPUT10
5、.SQL;SELECTDISTINCTCUSTOMERS.cid,CUSTOMERS.ageFROMBOOKS,CUSTOMERS,PURCHASESWHEREBOOKS.BOOKID=PURCHASES.BOOKIDANDPURCHASES.CID=CUSTOMERS.CIDANDTITLELIKE'%Database%';SPOOLOFF;/*11:查找图书价格高于30元的图书id、书名、书价及出版时间*/SPOOLD:AOUTPUT11.SQL;SELECTPRICING.BOOKID,BOOKS.TITLE,PRICIN
6、G.PRICE,BOOKS.YEARFROMBOOKS,PRICINGWHEREBOOKS.BOOKID=PRICING.BOOKIDANDPRICE>30;SPOOLOFF;/*12:查询购买精装本（hardcover）图书的顾客id、姓名及年龄*/SPOOLD:AOUTPUT12.SQL;SELECTDISTINCTCUSTOMERS.CID,CUSTOMERS.CNAME,CUSTOMERS.AGEFROMCUSTOMERS,PRICING,PURCHASESWHERECUSTOMERS.CID=PURCHASES.CID
7、ANDPURCHASES.BOOKID=PRICING.BOOKIDANDFORMATLIKE'hardcover';SPOOLOFF;/*13:查询出版年限早于2005年的图书id、书名和定价*/SPOOLD:AOUTPUT13.SQL;SELECTBOOKS.BOOKID,BOOKS.TITLE,PRICING.PRICEFROMBOOKS,PRICINGWHEREBOOKS.BOOKID=PRICING.BOOKIDANDYEAR<2005;SPOOLOFF;/*14:查询既买了id是101的图书，也买了id是102的图书
8、的顾客id（提示：用自身连接）*/SPOOLD:AOUTPUT14.SQL;SELECTFIRST.CIDFROMPURCHASESFIRST,PURCHASESSECONDWHEREFIRST.CID=SECOND.CIDAND
