SQL练习

-- 1. 使用本机MySQL-School数据库，测试多表查询
select * from classes c, student s WHERE c.cid = s.cno
select * from classes c LEFT JOIN student s ON c.cid = s.cno
-- 查询学生生日在17年10月之后的班级的信息
SELECT * FROM classes WHERE cid in ( SELECT cno FROM student WHERE birthday > '2017-10-01')
-- 查询学生生日大于17年10月1日，如果记录存在，前面的SQL语句就会执行
SELECT * FROM classes WHERE EXISTS ( SELECT cno FROM student WHERE birthday > '2017-10-01')
-- 学生所在班级编号大于任意一个班级编号就符合
SELECT * FROM classes WHERE cid > ANY(SELECT cno FROM student)
-- 学生所在班级编号大于所有班级编号才符合
SELECT * FROM classes WHERE cid > ALL(SELECT cno FROM student)
-- 查询班级名称，和班级总人数
SELECT c.cname , count(*) FROM classes c,student s WHERE c.cid = s.cno GROUP BY cname
-- 查询学生的姓名和学生所选的总课程平均成绩。
SELECT s.sname,AVG(sc.score) FROM student s,stu_cour sc WHERE s.sid = sc.sno GROUP BY sname
-- 查询学生的姓名和学生的选课总数，显示选课超过2门学生姓名。
SELECT s.sname,count(sc.cno) FROM student s,stu_cour sc WHERE s.sid = sc.sno GROUP BY sname HAVING count(sc.cno) > 2
-- 查询平均成绩大于80分的学生的总数
SELECT count(c.sname) from (SELECT s.sname,AVG(sc.score) FROM student s,stu_cour sc WHERE s.sid = sc.sno GROUP BY sname HAVING AVG(sc.score > 80)) c
-- 查询学生的平均成绩，但是平均成绩大于01班的任何一个学生的平均成绩。
SELECT s.sname,AVG(sc.score) FROM student s,stu_cour sc WHERE s.sid = sc.sno GROUP BY s.sname HAVING AVG(sc.score) > ANY(SELECT AVG(sc.score) FROM student s,stu_cour sc WHERE s.sid = sc.sno AND s.cno = 1 GROUP BY s.sname)
-- 2. 使用本机Oracle，wateruser/itcast
-- 按区域分组统计水费合计数
SELECT a.AREAID,SUM(a.money) FROM T_ACCOUNT a GROUP BY a.AREAID
-- 查询水费合计大于 16900 的区域及水费合计
SELECT sum(a.MONEY),a.AREAID FROM T_ACCOUNT a GROUP BY a.AREAID HAVING SUM(a.MONEY) > 169000
-- 查询显示业主编号，业主名称，业主类型名称
SELECT ow.id 业主编号,ow.name 业主名称,ot.name 业主类型 FROM T_OWNERS ow,T_OWNERTYPE ot WHERE ow.ownertypeid=ot.id
-- 查询显示业主编号，业主名称、地址和业主类型
SELECT ow.id 业主编号,ow.name 业主名称,ot.name 业主类型,ad.name 地址 FROM T_OWNERS ow,T_OWNERTYPE ot,T_ADDRESS ad WHERE ow.ownertypeid=ot.id AND ow.addressid=ad.id
-- 查询显示业主编号、业主名称、地址、所属区域、业主分类
SELECT ow.id 业主编号,ow.name 业主名称,ot.name 业主类型,ad.name 地址,ar.name 所属区域 FROM T_OWNERS ow,T_OWNERTYPE ot,T_ADDRESS ad,T_AREA ar WHERE ow.ownertypeid=ot.id AND ow.addressid=ad.id AND ad.areaid=ar.id
-- 查询显示业主编号、业主名称、地址、所属区域、收费员、业主分类
SELECT ow.id 业主编号,ow.name 业主名称,ot.name 业主类型,ad.name 地址,ar.name 所属区域,op.name 收费员 FROM T_OWNERS ow,T_OWNERTYPE ot,T_ADDRESS ad,T_AREA ar,T_OPERATOR op WHERE ow.ownertypeid=ot.id AND ow.addressid=ad.id AND ad.areaid=ar.id AND ad.operatorid=op.id
-- 查询业主的账务记录，显示业主编号、名称、年、月、金额。如果此业主 没有账务记录也要列出姓名。
SELECT ow.id,ow.name,ac.year,ac.month,ac.money FROM T_OWNERS ow LEFT JOIN T_ACCOUNT ac ON ow.id = ac.owneruuid
SELECT ow.id,ow.name,ac.year,ac.month,ac.money FROM T_ACCOUNT ac RIGHT JOIN T_OWNERS ow ON ow.id = ac.owneruuid
SELECT ow.id,ow.name,ac.year ,ac.month,ac.money FROM T_OWNERS ow,T_ACCOUNT ac WHERE ow.id=ac.owneruuid(+)
-- 查询 2012 年 1 月用水量大于平均值的台账记录
SELECT * FROM T_ACCOUNT ac WHERE ac.year = 2012 and ac.month = 01 and ac.usenum > (SELECT AVG(usenum) FROM T_ACCOUNT WHERE year = 2012 AND month = 01)
-- 查询地址编号为 1 、3、4 的业主记录
SELECT * FROM T_OWNERS ow WHERE ow.addressid in (1,3,4)
-- 查询地址含有“花园”的业主的信息
SELECT * FROM T_OWNERS ow,T_ADDRESS ad WHERE ow.addressid=ad.id and ad.name like '%花园%'
select * from T_OWNERS where addressid in ( select id from t_address where name like '%花园%' )
-- 查询地址不含有“花园”的业主的信息
SELECT * FROM T_OWNERS ow WHERE ow.addressid in (SELECT id FROM T_ADDRESS ad WHERE ad.name not like '%花园%')
-- 查询显示业主编号，业主名称，业主类型名称，条件为业主类型为”居民”， 使用子查询实现。
SELECT * FROM (SELECT ow.id,ow.name,ot.name 业主类型 FROM T_OWNERS ow,T_OWNERTYPE ot WHERE ow.ownertypeid=ot.id) WHERE 业主类型 = '居民'
-- 列出业主信息，包括 ID，名称，所属地址。
SELECT ow.id,ow.name,(SELECT name FROM T_ADDRESS ad WHERE ad.id=ow.addressid) FROM T_OWNERS ow
-- 列出业主信息，包括 ID，名称，所属地址，所属区域
SELECT
    ow.id 业主ID,
    ow.name 名称,
    ( SELECT ad.name 所属地址 FROM T_ADDRESS ad WHERE ow.addressid = ad.id ),
    (SELECT (SELECT name FROM T_AREA ar WHERE ad.areaid = ar.id) FROM T_ADDRESS ad WHERE ad.id = ow.addressid)
FROM
    T_OWNERS ow
--