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’