查询至少选修了学生201215122选修的全部课程的学生号码
SELECT DISTINCT Sno
FROM SC SCX
WHERE NOT EXISTS
    (SELECT *
     FROM SC SCY
     WHERE SCY.Sno='201215122' AND
        NOT EXISTS
        (SELECT *
         FROM SC SCZ
         WHERE SCZ.Sno=SCX.Sno AND
         SCZ.Cno=SCY.Cno));
  
# 2.查询年龄不低于所属系平均年龄的学员信息
SELECT * FROM stu_info AS s1
WHERE age>= ( SELECT avg(age) FROM stu_info AS s2
     WHERE s1.department = s2.department);

查询shopname表中每个店铺在本周的销售总额，销售数据在表salesdata，统计店铺表shopname的每个店铺在本周的销售数据之和
select a.shopname,(select sum(salesmoney) from salesdata where shopname=a.shopname and datediff(week,[salesday],getdate())=0) as tw from shopname as a
 --这里把外层表别名为a，是因为内层表的查询条件为外层表字段

select aa.sname aa.lw from
 (select a.shopname sname,
 (select sum(salesmoney) from salesdata where shopname=a.shopname and datediff(week,[salesday],getdate())=0) tw,
 (select sum(salesmoney) from salesdata where shopname=a.shopname and datediff(week,[salesday],getdate())=1) lw
 from shopname as a) aa

select bb.shopname,bb.lw,bb.tw,round(cast(bb.tw as float)/bb.lw,2) bf from  --round是取多少位小数，cast用来转换字段为浮点型，否则运行的数据不会保留小数
 (select a.shopname,
  (select sum(salesmoney) from salesdata where shopname=a.shopname and datediff(week,[salesday],getdate())=1) as lw,
  (select sum(salesmoney) from salesdata where shopname=a.shopname and datediff(week,[salesday],getdate())=0) as tw
 from shopname as a) as bb --先查询店铺名称，各店铺上周销量之和，各店铺本周销量之和作为子表bb，再在这个bb表中查询出其字段并运行