SQL语句相关概念及练习之基础篇(1)(6)

--题4)输出本月拨打电话次数最多的前三个呼叫员的编号. 
 
--@按呼叫员编号进行分组； 
 
--@计算个呼叫员拨打电话的次数； 
 
--@按呼叫员拨打电话的次数进行降序排序； 
 
--@查询前3条记录中呼叫员的编号。 
 
selecttop3CallerNumber,count(*) 
 
fromT_CallRecords 
 
wheredatediff(month,StartDateTime,getdate())=12--一年前的 
 
groupbyCallerNumber 
 
orderbycount(*)DESC 
 
 
 
--题5)输出所有数据的拨号流水，并且在最后一行添加总呼叫时长： 
 
 
 
--记录呼叫员编号、对方号码、通话时长 
 
--...... 
 
--汇总[市内号码总时长][长途号码总时长] 
 
 
 
--@计算每条记录中通话时长； 
 
--@查询包含不加0号码，即市内号码的记录； 
 
--@计算市内号码通话总时长； 
 
--@查询包含加0号码，即长途号码的记录； 
 
--@计算长途号码通话总时长； 
 
--@联合查询。 
 
select'汇总'as汇总, 
 
convert(varchar(20), 
 
sum(( 
 
case 
 
whenTellNumbernotlike'0%'thendatediff(second,StartDateTime,EndDateTime) 
 
else0 
 
end 
 
)))as市内通话, 
 
sum(( 
 
case 
 
whenTellNumberlike'0%'thendatediff(second,StartDateTime,EndDateTime) 
 
else0 
 
end 
 
))as长途通话 
 
fromT_CallRecords 
 
unionall 
 
selectCallerNumber,TellNumber,datediff(second,StartDateTime,EndDateTime)as通话时长 
 
fromT_CallRecords 
 
 
 
 
 
--客户和订单表的练习 
 
--建立一个客户表 
 
createtableT_Customers( 
 
idintnotnull, 
 
namenvarchar(50)collatechinese_prc_ci_asnull, 
 
ageintnull 
 
); 
 
insertT_Customers(id,name,age)values(1,N'tom',10); 
 
insertT_Customers(id,name,age)values(2,N'jerry',15); 
 
insertT_Customers(id,name,age)values(3,N'john',22); 
 
insertT_Customers(id,name,age)values(4,N'lily',18); 
 
insertT_Customers(id,name,age)values(5,N'lucy',18); 
 
 
 
select*fromT_Customers 
 
 
 
--建立一个销售单表 
 
createtableT_Orders( 
 
idintnotnull, 
 
billnonvarchar(50)collatechinese_prc_ci_asnull, 
 
customeridintnull); 
 
 
 
insertT_Orders(id,billno,customerid)values(1,N'001',1) 
 
insertT_Orders(id,billno,customerid)values(2,N'002',1) 
 
insertT_Orders(id,billno,customerid)values(3,N'003',3) 
 
insertT_Orders(id,billno,customerid)values(4,N'004',2) 
 
insertT_Orders(id,billno,customerid)values(5,N'005',2) 
 
insertT_Orders(id,billno,customerid)values(6,N'006',5) 
 
insertT_Orders(id,billno,customerid)values(7,N'007',4) 
 
insertT_Orders(id,billno,customerid)values(8,N'008',5) 
 
 
 
select*fromT_Orders 
 
 
 
selecto.billno,c.name,c.age 
 
fromT_OrdersasojoinT_Customersascono.customerid=c.id 
 
--查询订单号,顾客名字,顾客年龄 
 
 
 
selecto.billno,c.name,c.age 
 
fromT_OrdersasojoinT_Customersascono.customerid=c.id 
 
wherec.age>15 
 
--显示年龄大于15岁的顾客姓名、年龄和订单号 
 
 
 
selecto.billno,c.name,c.age 
 
fromT_OrdersasojoinT_Customersascono.customerid=c.id 
 
wherec.age>(selectavg(age)fromT_Customers) 
 
--显示年龄大于平均年龄的顾客姓名、年龄和订单号 
 
 
 
--子查询练习 
 
--新建一个数据库，名为BookShop 
 
CreatedatabaseBookShop 
 
 
 
--创建4张表 
 
createtableT_Reader(FIdINTNOTNULL,FNamevarchar(50),FYearOfBirthINT,FCityvarchar(50),FProvincevarchar(50),FYearOfJoinINT); 
 
createtableT_Book(FIdintnotnull,FNamevarchar(50),FYearPublishedint,FCategoryIdint); 
 
createtableT_Category(FIdintnotnull,FNamevarchar(50)); 
 
createtableT_ReaderFavorite(FCategoryIdint,FReaderIdint); 
 
 
 
--分别为4张表插入数据 
 
insertintoT_Category(FId,FName)values(1,'Story'); 
 
insertintoT_Category(FId,FName)values(2,'History'); 
 
insertintoT_Category(FId,FName)values(3,'Theory'); 
 
insertintoT_Category(FId,FName)values(4,'Technology'); 
 
insertintoT_Category(FId,FName)values(5,'Art'); 
 
insertintoT_Category(FId,FName)values(6,'Philosophy'); 
 
 
 
insertintoT_Reader(FId,FName,FYearOfBirth,FCity,FProvince,FyearOfJoin)values(1,'Tom',1979,'TangShan','Hebei',2003); 
 
insertintoT_Reader(FId,FName,FYearOfBirth,FCity,FProvince,FyearOfJoin)values(2,'Sam',1981,'LangFang','Hebei',2001); 
 
insertintoT_Reader(FId,FName,FYearOfBirth,FCity,FProvince,FyearOfJoin)values(3,'Jerry',1966,'DongGuan','GuangDong',1995); 
 
insertintoT_Reader(FId,FName,FYearOfBirth,FCity,FProvince,FyearOfJoin)values(4,'Lily',1972,'JiaXing','ZheJiang',2005); 
 
insertintoT_Reader(FId,FName,FYearOfBirth,FCity,FProvince,FyearOfJoin)values(5,'Marry',1985,'BeiJing','BeiJing',1999); 
 
insertintoT_Reader(FId,FName,FYearOfBirth,FCity,FProvince,FyearOfJoin)values(6,'Kelly',1977,'ZhuZhou','HuNan',1995); 
 
insertintoT_Reader(FId,FName,FYearOfBirth,FCity,FProvince,FyearOfJoin)values(7,'Tim',1982,'YongZhou','HuNan',2001); 
 
insertintoT_Reader(FId,FName,FYearOfBirth,FCity,FProvince,FyearOfJoin)values(8,'King',1979,'JiNan','ShanDong',1997); 
 
insertintoT_Reader(FId,FName,FYearOfBirth,FCity,FProvince,FyearOfJoin)values(11,'John',1979,'QingDao','ShanDong',2003); 
 
insertintoT_Reader(FId,FName,FYearOfBirth,FCity,FProvince,FyearOfJoin)values(9,'Lucy',1978,'LuoYang','HeNan',1996); 
 
insertintoT_Reader(FId,FName,FYearOfBirth,FCity,FProvince,FyearOfJoin)values(10,'July',1983,'ZhuMaDian','HeNan',1999); 
 
insertintoT_Reader(FId,FName,FYearOfBirth,FCity,fProvince,FyearOfJoin)values(12,'Fige',1981,'JinCheng','ShanXi',2003); 
 
 
 
insertintoT_Book(FId,FName,FYearPublished,FCategoryId)values(1,'AboutJ2EE',2005,4); 
 
insertintoT_Book(FId,FName,FYearPublished,FCategoryId)values(2,'LearningHibernate',2003,4); 
 
insertintoT_Book(FId,FName,FYearPublished,FCategoryId)values(3,'TowCites',1999,1); 
 
insertintoT_Book(FId,FName,FYearPublished,FCategoryId)values(4,'JaneEyre',2001,1); 
 
insertintoT_Book(FId,FName,FYearPublished,FCategoryId)values(5,'OliverTwist',2002,1); 
 
insertintoT_Book(FId,FName,FYearPublished,FCategoryId)values(6,'HistoryofChina',1982,2); 
 
insertintoT_Book(FId,FName,FYearPublished,FCategoryId)values(7,'HistoryofEngland',1860,2); 
 
insertintoT_Book(FId,FName,FYearPublished,FCategoryId)values(8,'HistoryofAmerica',1700,2); 
 
insertintoT_Book(FId,FName,FYearPublished,FCategoryId)values(9,'HistoryofTheVorld',2008,2); 
 
insertintoT_Book(FId,FName,FYearPublished,FCategoryId)values(10,'Atom',1930,3); 
 
insertintoT_Book(FId,FName,FYearPublished,FCategoryId)values(11,'RELATIVITY',1945,3); 
 
insertintoT_Book(FId,FName,FYearPublished,FCategoryId)values(12,'Computer',1970,3); 
 
insertintoT_Book(FId,FName,FYearPublished,FCategoryId)values(13,'Astronomy',1971,3); 
 
insertintoT_Book(FId,FName,FYearPublished,FCategoryId)values(14,'HowTosinging',1771,5); 
 
insertintoT_Book(FId,FName,FYearPublished,FCategoryId)values(15,'DaoDeJing',2001,6); 
 
insertintoT_Book(FId,FName,FYearPublished,FCategoryId)values(16,'ObediencetoAu',1995,6); 
 
 
 
 
 
insertintoT_ReaderFavorite(FCategoryId,FReaderId)values(1,1); 
 
insertintoT_ReaderFavorite(FCategoryId,FReaderId)values(5,2); 
 
insertintoT_ReaderFavorite(FCategoryId,FReaderId)values(2,3); 
 
insertintoT_ReaderFavorite(FCategoryId,FReaderId)values(3,4); 
 
insertintoT_ReaderFavorite(FCategoryId,FReaderId)values(5,5); 
 
insertintoT_ReaderFavorite(FCategoryId,FReaderId)values(1,6); 
 
insertintoT_ReaderFavorite(FCategoryId,FReaderId)values(1,7); 
 
insertintoT_ReaderFavorite(FCategoryId,FReaderId)values(4,8); 
 
insertintoT_ReaderFavorite(FCategoryId,FReaderId)values(6,9); 
 
insertintoT_ReaderFavorite(FCategoryId,FReaderId)values(5,10); 
 
insertintoT_ReaderFavorite(FCategoryId,FReaderId)values(2,11); 
 
insertintoT_ReaderFavorite(FCategoryId,FReaderId)values(2,12); 
 
insertintoT_ReaderFavorite(FCategoryId,FReaderId)values(1,12); 
 
insertintoT_ReaderFavorite(FCategoryId,FReaderId)values(3,1); 
 
insertintoT_ReaderFavorite(FCategoryId,FReaderId)values(1,3); 
 
insertintoT_ReaderFavorite(FCategoryId,FReaderId)values(4,4); 
 
 
 
select*fromT_Book 
 
 
 
select*fromT_Category 
 
 
 
select*fromT_Reader 
 
 
 
select*fromT_ReaderFavorite 
 
 
 
--并列查询 
 
select1asf1,2,(selectMIN(FYearPublished)fromT_Book), 
 
(selectMAX(FYearPublished)fromT_Book)asf4 
 
 
 
--查询入会日期在2001或者2003年的读者信息 
 
select*fromT_Reader 
 
whereFYearOfJoinin(2001,2003) 
 
 
 
--与between...and不同 
 
select*fromT_Reader 
 
whereFYearOfJoinbetween2001and2003 
 
 
 
--查询有书出版的年份入会的读者信息 
 
select*fromT_Reader 
 
whereFYearOfJoinin 
 
( 
 
selectFYearPublishedfromT_Book 
 
) 
 
 
 
--SQLServer2005之后的版本内置函数：ROW_NUMBER()，称为开窗函数，可以进行分页等操作。 
 
selectROW_NUMBER()over(orderbyFSalaryDESC)asRow_Num, 
 
FNumber,FName,FSalary,FAgefromT_Employee 
--特别注意，开窗函数row_number()只能用于select或orderby子句中，不能用于where子句中 
 
--查询第3行到第5行的数据 
select*from 
( 
selectROW_NUMBER()over(orderbyFSalaryDESC)asRow_Num, 
FNumber,FName,FSalary,FAgefromT_Employee 
)ase1 
wheree1.Row_Num>=3ande1.Row_Num<=5