--一、单表查询练习
--1、查询<学生信息表>，查询学生"张三"的全部基本信息
SELECT * FROM a_studentinfo WHERE sname = '张三';
--2、查询<学生信息表>，查询学生"张三"和”李四”的基本信息
SELECT *
FROM a_studentinfo
WHERE sname = '张三'
OR sname = '李四';
--3、查询<学生信息表>，查询姓"张"学生的基本信息
SELECT * FROM a_studentinfo WHERE sname LIKE '张%';
--4、查询<学生信息表>，查询姓名中含有"四"字的学生的基本信息
SELECT * FROM a_studentinfo WHERE sname LIKE '%四%';
--5、查询<学生信息表>，查询姓名长度为三个字，姓“李”，且最后一个字是“强”的全部学生信息。
SELECT * FROM a_studentinfo WHERE sname LIKE '%李_强%';
--6、查询<学生信息表>，查询姓"张"或者姓”李”的学生的基本信息。
SELECT *
FROM a_studentinfo
WHERE sname LIKE '张%'
OR sname LIKE '李%';
--7、查询<学生信息表>，查询姓"张"并且"所属省份"是"北京"的学生信息
SELECT *
FROM a_studentinfo
WHERE sname LIKE '张%'
AND province = '北京';
--8、查询<学生信息表>，查询"所属省份"是"北京"、”新疆”、”山东”或者"上海"的学生的信息
SELECT *
FROM a_studentinfo
WHERE province = '北京'
OR province = '上海'
OR province = '新疆'
OR province = '山东';
--9、查询<学生信息表>，查询姓"张"，但是"所属省份"不是"北京"的学生信息
SELECT *
FROM a_studentinfo
WHERE province <> '北京'
AND sname LIKE '张%';
--10、查询<学生信息表>，查询全部学生信息，并按照“性别”排序，性别相同的情况下按照“所属省份”排序，所属省份相同的情况下再按照“班级”排序
SELECT *
FROM a_studentinfo
ORDER BY sex, province, CLASS
--11、查询<学生信息表>，查询现有学生都来自于哪些不同的省份
SELECT province
FROM a_studentinfo
GROUP BY province
--12、查询<学生选修信息表>，查询没有填写成绩的学生的学号、课程号和成绩
SELECT sno, cno, score
FROM a_studentcourse
WHERE score IS NULL
--13、查询<学生选修信息表>，查询全部填写了成绩的学生的选修信息，并按照“成绩”从高到低进行排序
SELECT sno, cno, score
FROM a_studentcourse
WHERE score IS NOT NULL
ORDER BY score DESC
--二、聚合函数练习
--1、统计<学生信息表>，统计共有多少个学生
SELECT COUNT(sno)
FROM a_studentinfo;
--2、统计<学生信息表>，统计年龄大于20岁的学生有多少个
SELECT COUNT(sno) FROM a_studentinfo WHERE 2016 - yearofbirth > 30;
--3、统计<学生信息表>，统计入学时间在1980年至1982年的学生人数
SELECT COUNT(sno)
FROM a_studentinfo
WHERE enrollment BETWEEN to_date('2000', 'yyyy') AND
to_date('2008', 'yyyy');
--4、统计<学生选修信息表>，统计学号为"S001"的学生的平均成绩
SELECT sno, AVG(score)
FROM a_studentcourse
GROUP BY sno
HAVING sno = 'S001';
--5、统计<学生选修信息表>，统计学号为"S001"的学生的总成绩
SELECT sno, SUM(score)
FROM a_studentcourse
GROUP BY sno
HAVING sno = 'S001';
--6、统计<学生选修信息表>，查询课程号为”C001”的课程的最高成绩
SELECT cno, MAX(score)
FROM a_studentcourse
GROUP BY cno
HAVING cno = 'C001';
--7、统计<学生信息表>，查询所有学生中的最大年龄是多少
SELECT 2016 - MIN(yearofbirth)
FROM a_studentinfo
--三、分组查询练习
--1、统计<学生选修信息表>，统计每个课程的选修人数
SELECT COUNT(sno), cno
FROM a_studentcourse
GROUP BY cno
ORDER BY cno ASC;
--2、统计<学生选修信息表>，统计每个同学的总成绩
SELECT SUM(score), cno FROM a_studentcourse GROUP BY cno ORDER BY cno ASC;
--3、统计<学生信息表>，统计每个班级中每种性别的学生人数，并按照班级排序
SELECT COUNT(sno), sex, CLASS
FROM a_studentinfo
GROUP BY CLASS, sex
ORDER BY CLASS ASC;
--4、统计<学生选修信息表>，统计每门课程的平均成绩,并按照成绩降序排序
SELECT AVG(score) s, cno FROM a_studentcourse GROUP BY cno ORDER BY s DESC;
--5、统计<学生选修信息表>，显示有两门以上课程不及格的学生的学号
SELECT sno
FROM a_studentcourse
WHERE score < 60
GROUP BY sno
HAVING COUNT(sno) > 2
--6、统计<学生信息表>，统计每个班级中的最大年龄是多少
SELECT MAX(2016 - yearofbirth)
FROM a_studentinfo
GROUP BY CLASS
--四、嵌套查询练习
--1、用子查询实现，查询选修“高等数学”课的全部学生的总成绩
SELECT SUM(score)
FROM a_studentcourse
WHERE sno IN
(SELECT sno FROM a_courseinfo WHERE subject = '高等数学');
--2、用子查询实现，统计<学生选修信息表>，显示学号为"S001"的学生在其各科成绩中，最高分成绩所对应的课程号和成绩
--思考：如果该学号学生有两个课程分数都为最高的100分，查询会有什么结果
SELECT cno, score
FROM a_studentcourse
WHERE score = (SELECT MAX(score) FROM a_studentcourse WHERE sno = 'S001')
AND sno = 'S001';
--3、用子查询实现，查询2班选修"数据库技术"课的所有学生的成绩之和
SELECT SUM(score)
FROM a_studentcourse
WHERE sno IN (SELECT sno FROM a_studentinfo WHERE CLASS = 2)
AND cno = (SELECT cno FROM a_courseinfo WHERE subject = '数据库技术');
--4、用子查询实现，查询3班"张三"同学的"测试管理"成绩
SELECT score
FROM a_studentcourse
WHERE cno = (SELECT cno FROM a_courseinfo WHERE subject = '测试管理')
AND sno = (SELECT sno
FROM a_studentinfo
WHERE CLASS = 3
AND sname = '张三');
--五、联接查询练习
--1、查询"张三"的各科考试成绩，要求显示姓名、课程号和成绩
SELECT s.sname, sc.cno, sc.score
FROM a_studentinfo s
JOIN a_studentcourse sc
ON s.sno = sc.sno
WHERE s.sname = '张三';
--2、查询"张三"的各科考试成绩中，哪科没有记录考试成绩，要求显示姓名、课程号和成绩
SELECT s.sname, sc.cno, sc.score
FROM a_studentinfo s
JOIN a_studentcourse sc
ON s.sno = sc.sno
WHERE s.sname = '张三'
AND sc.score IS NULL;
--3、查询"张三"的各门课程成绩，要求显示姓名、课程名称和成绩
SELECT s.sname, c.subject, sc.score
FROM a_studentinfo s
JOIN a_studentcourse sc
ON s.sno = sc.sno
JOIN a_courseinfo c
ON sc.cno = c.cno
WHERE s.sname = '张三';
--4、查询3班"张三"的"测试管理"成绩，要求显示姓名、成绩
SELECT s.sname, sc.score
FROM a_studentinfo s
JOIN a_studentcourse sc
ON sc.sno = s.sno
JOIN a_courseinfo c
ON sc.cno = c.cno
WHERE c.subject = '测试管理'
AND s.sname = '张三';
--5、查询所有2000年以前入学的，各班男生的各科考试平均成绩
SELECT s.class, sc.cno, AVG(sc.score)
FROM a_studentinfo s
JOIN a_studentcourse sc
ON sc.sno = s.sno
WHERE s.sex = '男'
AND s.enrollment < to_date('2000', 'yyyy')
GROUP BY sc.cno, s.class;
--六、外联接查询
--查询”李坚强”所有课程的成绩，并显示学号、姓名、课程号和成绩，没有成绩记录的学号包括：('S009','S010','S011')
--1、使用左联接
SELECT s.sno, s.sname, sc.score
FROM a_studentinfo s
LEFT JOIN a_studentcourse sc
ON sc.sno = s.sno
WHERE s.sname = '李坚强'
--2、使用右联接
SELECT s.sno, s.sname, sc.score
FROM a_studentcourse sc
RIGHT JOIN a_studentinfo s
ON sc.sno = s.sno
WHERE s.sname = '李坚强'
SELECT * FROM a_courseinfo;
--七、补充提高
--1、查询“张三”比“王三”入学早几年
SELECT ((SELECT s.enrollment FROM a_studentinfo s WHERE sname = '张三') -
enrollment) / 365
FROM a_studentinfo s
WHERE sname = '王三';
--2、查询所在班级和该班内学生的年龄之和，其中每个人的年龄都大于20岁，每个班的年龄之和大于60岁
SELECT CLASS, SUM(2016 - yearofbirth)
FROM a_studentinfo
WHERE (2016 - yearofbirth) > 20
GROUP BY CLASS
HAVING SUM(2016 - yearofbirth) > 60
/*3、计算每种产品的剩余库存量
表1，为产品进货表，产品名称name[char(10)]，产品数量amount[int]
表2，为产品出货表，产品名称name[char(10)]，产品数量amount[int]
业务逻辑：表1存储产品的总进货量，表2存储每种产品每次的出货量，如产品A进货为100，出货了3次，每次分别为10、20、30，那么A产品的库存就为40
表A1 | 表A2
-------------------|---------------------------
name amount | name amount
A 100 | A 10
B 100 | A 20
| A 30
| B 10
| B 40*/
CREATE TABLE aa1(NAME CHAR(10), amout NUMBER) CREATE TABLE aa2(NAME
CHAR(10),
amout NUMBER)
INSERT
INTO aa1 VALUES('a', 100);
INSERT INTO aa2 VALUES ('a', 10);
INSERT INTO aa2 VALUES ('a', 20);
INSERT INTO aa2 VALUES ('a', 30);
SELECT DISTINCT a.name, (a.amout - (SELECT SUM(amout) FROM aa2)) 剩余
FROM aa1 a
JOIN aa2 b
ON a.name = b.name;
