t-sql 第十三章 答案

INSERT QQUser(QQID,PassWord,LastLogTime,Online,Level)select 54789625,'add5123&','2013-02-16 17:01:35',2,1 UNIONselect 88662753,'admin0219','2013-02-19 21:08:50',0,5 UNIONselect 8855678,'guest0221','2013-02-21 16:28:20',1,6------------------------------------------------------------INSERT BaseInfo(QQID,NickName,Sex,Age,Province,City,Address,phone)SELECT 54789625,'蝴蝶飞飞',1,16,'北京市','北京','亚运村','37547388157668'UNIONSELECT 88662753,'秋芙蓉',0,20,'河南省','南阳','方城博望','88715783657725'UNIONSELECT 8855678,'双眼皮の潴',1,38,'北京市','北京','双榆树东里','65794968876143'-----------------------------------------------------------INSERT Relation(QQID,RelationQQID,RelationStatus)SELECT 54789625,88662753,0 UNIONSELECT 88662753,8855678,1 UNIONSELECT 54789625,8855678,0 -------用例6: 查询数据select q.QQID as QQ号,b.NickName as 昵称,b.Age as 年龄from QQUser q,BaseInfo bwhere q.QQID=b.QQID  and q.QQID=88662753----------------------------------------------select q.QQID,b.NickName,b.Sex,b.Age,b.City,b.Addressfrom BaseInfo b,QQUser qwhere b.QQID=q.QQID and q.Online=0--------------------------------------------select QQID,NickName as 昵称,Sex as 性别,Age as 年龄,Province as 省份,City as 城市from BaseInfowhere Age>18 and Age <45 and Province='北京市'----------------------------select *from BaseInfo where NickName='双眼皮の潴'-----------------------------------select COUNT(*) AS 人数,B.Provincefrom BaseInfo b,Relation rwhere r.QQID=54789625 and r.RelationStatus=0 and r.RelationQQID=b.QQIDgroup by ProvinceORDER BY COUNT(*) DESC------------------------------------SELECT B.QQID,Q.LastLogTime,Q.Level,B.NickName,B.AgeFROM BaseInfo B,QQUser QWHERE B.QQID=Q.QQID AND DATEDIFF(DD,Q.LastLogTime, GETDATE())>150ORDER BY   Q.LastLogTime  DESC-----------------------------------------------------SELECT R.QQID,B.NickName,B.Age,Q.LevelFROM QQUser Q,  BaseInfo B,    Relation RWHERE Q.QQID=B.QQID  AND B.QQID=R.QQID AND  Q.Level>10 AND Q.QQID=54789625 and RelationStatus=0-----------------------------------------------------SELECT R.RelationQQID ,B.NickName, B.AgeFROM Relation R,BaseInfo B ,QQUser QWHERE R.RelationQQID=B.QQID AND B.QQID=54789625 AND Q.Online=2-----------------------------------SELECT R.QQID,COUNT(*) AS 人数FROM Relation R,BaseInfo BWHERE R.QQID=B.QQIDGROUP BY R.QQIDHAVING COUNT(*)>1------------------------------------select top 20 r.RelationQQID,COUNT(*)AS 被拉黑人数次数from Relation r, BaseInfo bwhere r.RelationQQID=b.QQID  and r.RelationStatus=1group by r.RelationQQIDorder by COUNT(*)-----------------------------------------------------------用例7: 修改数据  UPDATE QQUser SET  Online=2  WHERE QQID=8855678  -------------------------------------  UPDATE BaseInfo SET NickName='被淹死的鱼',Address='解放中路6号院106室' where QQID=8855678  --------------------------------------------  UPDATE Relation SET  RelationStatus=1 where RelationQQID=248624066 and QQID=8855678-------------------------------------------------  update QQUser set Level=Level+1 where Level<6   ---------------------------------------update QQUser set Level=-1 where  DATEDIFF(dd,LastLogTime,GETDATE())>365--------------------------------------------------update  QQUser set Level=Level+1 where QQID in (select QQID from Relation group by QQID   having COUNT(*)>20)  -------------------------------------------------update Relation set RelationStatus=1 where RelationQQID in (select QQID from BaseInfo  where NickName='嘟嘟鱼' )AND QQID=286314 AND RelationStatus=0 --------------用例8   删除数据 delete from Relation where QQID=54789625 and RelationStatus=1  ----------------------