SQL的基础查询案例

表名        表的字段

Drivers      DriversID, DriversName, UnitPrice from Drivers

Expense     ExpenseID, ExpenseDate, DrinksID, Num from Expense

Singer       SingerID, SingerName, SingerType, Birthday,

             Area, PhotoPath from Singer

Song         SongID, Title, PingTitle, TypeID, SingerID, Hit, Url from Song

Type         TypeID, TypeName from Type
 

 表名与表字段都给了 ，数据你们自己加进去。

 

--1.查找歌手表中歌手类型为女歌手的信息,并按出生年月排序

select SingerID, SingerName, SingerType, Birthday,

 Area, PhotoPath from Singer where SingerType='女'

 order by Birthday

 

--2.查找歌曲表中点歌次数超过50次的歌曲,并从高至低排序

select  SongID, Title, PingTitle, TypeID, SingerID, Hit, Url from Song

where Hit>50 order by hit desc

 

--3.查找酒水小食消费表中日期在2013年5月20日至 5月28日的消费信息

select  ExpenseID, ExpenseDate, DrinksID, Num from Expense

where ExpenseDate between '2013-05-20' and '2013-05-28'

 

--4.将歌手地区'台湾'修改成'中国台湾'

select SingerID, SingerName, SingerType, Birthday,

 Area, PhotoPath from Singer

 update  Singer set Area='中国' where Area='大陆'

 

--5.查询歌曲名中包含'曾经'的歌曲信息.

select  SongID, Title, PingTitle, TypeID, SingerID, Hit, Url from Song

where Title like '%曾经%'

 

--6.找到歌手表中姓名长度大于2的歌手

select SingerID, SingerName, SingerType, Birthday,

 Area, PhotoPath from Singer

 where len(SingerName)>2

 

--7.查看歌手表中有哪些歌手地区.(去重复显示单条信息)

select distinct  Area from  Singer

 

--8.查询出歌手'周杰伦'唱的所有歌曲.

select  SongID, Title, PingTitle, TypeID, SingerID, Hit, Url from Song

where SingerID=

(select SingerID from Singer where SingerName='周杰伦')

select  SongID, Title, PingTitle, TypeID,  Hit, Url from Song,Singer

where Song.SingerID=Singer.SingerID and Singer.SingerName='周杰伦'

 

--9.查找出2013-5-30消费的酒水小食信息(要求:显示酒水小食名称和价格)

select DriversID, DriversName, UnitPrice from Drivers

select  ExpenseID, ExpenseDate, DrinksID, Num from Expense

where  ExpenseDate='2013-05-30'

select  DriversName,UnitPrice*num from Drivers,Expense

where Drivers.DriversID=Expense.DrinksID and ExpenseDate='2013-05-30'

 

--10.查找出所有‘经典老歌’（要求：where条件必须是歌曲类型表的typename = ‘经典老歌’）

select  SongID, Title, PingTitle, TypeID, SingerID, Hit, Url from Song

where TypeID =

( select TypeID from Type where TypeName='经典老歌')

select  SongID, Title, PingTitle,  SingerID, Hit, Url from Song,Type

where Song.TypeID=Type.TypeID and Type.TypeName='经典老歌'

 

--11.删除所有属于'SHE'的歌曲.(要求:where条件后是Singer表的SingerName = 'SHE')

delete from Song where SingerID=

(select SingerID from Singer where SingerName='SHE') 

--select  SongID, Title, PingTitle, TypeID, SingerID, Hit, Url from Song

--where SingerID=

--(select SingerID from Singer where SingerName='SHE')

 

--12.将2013-5-30日消费记录中的'开心果'删除.(要求:where条件后是Drinks表的DrinksName = '开心果')

delete from Expense where DrinksID=

(select DriversID from Drivers where DriversName='开心果')

and ExpenseDate='2013-5-30'

 

--13.算出2013-5-30日的消费总额.

select  sum(UnitPrice*num) from Drivers,Expense

where Drivers.DriversID=Expense.DrinksID and ExpenseDate='2013-05-30'