t-sql 第十三章 答案

INSERT QQUser(QQID,PassWord,LastLogTime,Online,Level)
select 54789625,'add5123&','2013-02-16 17:01:35',2,1 UNION
select 88662753,'admin0219','2013-02-19 21:08:50',0,5 UNION
select 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'UNION
SELECT 88662753,'秋芙蓉',0,20,'河南省','南阳','方城博望','88715783657725'UNION
SELECT 8855678,'双眼皮の潴',1,38,'北京市','北京','双榆树东里','65794968876143'
-----------------------------------------------------------
INSERT Relation(QQID,RelationQQID,RelationStatus)
SELECT 54789625,88662753,0 UNION
SELECT 88662753,8855678,1 UNION
SELECT 54789625,8855678,0 
-------用例6: 查询数据
select q.QQID as QQ号,b.NickName as 昵称,b.Age as 年龄
from QQUser q,BaseInfo b
where q.QQID=b.QQID  and q.QQID=88662753
----------------------------------------------
select q.QQID,b.NickName,b.Sex,b.Age,b.City,b.Address
from BaseInfo b,QQUser q
where b.QQID=q.QQID and q.Online=0
--------------------------------------------
select QQID,NickName as 昵称,Sex as 性别,Age as 年龄,Province as 省份,City as 城市
from BaseInfo
where Age>18 and Age <45 and Province='北京市'
----------------------------
select *
from BaseInfo 
where NickName='双眼皮の潴'
-----------------------------------
select COUNT(*) AS 人数,B.Province
from BaseInfo b,Relation r
where r.QQID=54789625 and r.RelationStatus=0 and r.RelationQQID=b.QQID
group by Province
ORDER BY COUNT(*) DESC
------------------------------------
SELECT B.QQID,Q.LastLogTime,Q.Level,B.NickName,B.Age
FROM BaseInfo B,QQUser Q
WHERE B.QQID=Q.QQID AND DATEDIFF(DD,Q.LastLogTime, GETDATE())>150
ORDER BY   Q.LastLogTime  DESC
-----------------------------------------------------
SELECT R.QQID,B.NickName,B.Age,Q.Level
FROM QQUser Q,  BaseInfo B,    Relation R
WHERE 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.Age
FROM Relation R,BaseInfo B ,QQUser Q
WHERE R.RelationQQID=B.QQID AND B.QQID=54789625 AND Q.Online=2
-----------------------------------
SELECT R.QQID,COUNT(*) AS 人数
FROM Relation R,BaseInfo B
WHERE R.QQID=B.QQID
GROUP BY R.QQID
HAVING COUNT(*)>1
------------------------------------
select top 20 r.RelationQQID,COUNT(*)AS 被拉黑人数次数
from Relation r, BaseInfo b
where r.RelationQQID=b.QQID  and r.RelationStatus=1
group by r.RelationQQID
order 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 
 ----------------------