实验二 SQL查询一
【实验目的】
1、 掌握单表查询
2、 初步掌握多表查询 【实验性质】 验证性 【实验内容】
使用实验一建立的银行贷款数据库和表，完成以下查询。
注意：实验一的数据集仅作为部分测试数据，不能以 SQL命令“在实验一所使用数据集上能返回正确结 果”作为检验 SQL命令是否正确的标准！
(1) 查询所有法人的法人代码、法人名称、经济性质和注册资金。
(2) 查询“102100028292”银行的的银行名称和电话。
(3) 查询单笔贷款金额在 30 至 80 万元之间的法人代码、银行代码、贷款日期和贷款金额。
(4) 查询 2009 年 1 月 1 日以后贷款且贷款期限是 10 年的法人代码。
(5) 查询经济性质为“私营企业”的所有法人的最高注册资金、最低注册资金和平均注册资金。
(6) 查询每种经济性质的法人的经济性质、最高注册资金、最低注册资金和平均注册资金。
(7) 统计每个法人的法人代码和贷款总次数，要求查询结果按贷款总次数的升序排列。
(8) 查询贷款次数超过 3 次的法人的平均贷款金额和贷款次数。
(9) 统计每种经济性质贷款的法人的总数和其平均贷款金额，列出平均贷款金额前三名的经济性质、 贷款总数和平均贷款金额。
(10) 查询贷款期限为 5 年、10 年或 15 年的法人名称、银行名称、贷款日期、贷款金额和贷款期限。
(11) 查询经济性质为“集体企业”的法人在“北京”的银行贷款（通过名称中是否包含“北京”判断银行所在 地）的信息，列出法人名称、银行名称和贷款日期。
(12) 查询哪些法人没有贷过款，列出法人名称。分别用多表连接和子查询两种方式实现。
(13) 查询有多少个法人没有贷款记录。
使用实验一建立的学生数据库和表，完成以下查询。
(14) 查询 CS 系没有选课的学生的姓名和性别。
(15) 统计‘PASCAL 程序设计’课程的考试最高分、最低分和平均分和选课人数。
(16) 统计每个系学生‘数据库原理’课程考试的最高分、最低分、平均分和选课人数。
(17) 查询每个学生的修课总学分，并进行如下处理：如果总学分高于 10 分，则显示‘优秀’；如果总学 分在 6 到 10 分间，则显示‘正常’；如果总学分低于 6 分，则显示‘应努力’。
(18) 统计每个系每个学生的修课门数和平均成绩，如果修课门数大于 3 并且平均成绩高于 90 的显示‘优 秀’；如果修课门数大于 3 并且平均成绩在 80～90，则显示‘较好’；对于修课门数小于等于 3 的并 且平均成绩高于 90 的显示‘较好’；如果修课门数小于等于 3 并且平均成绩在 80～90，则显示‘一 般’；其他情况显示为‘应努力’。列出系名、学号、原修课门数和平均成绩以及处理后的显示结 果。
(19) 查询 SE 系学生的‘数据库原理’课程考试的最低分所对应的学生的姓名、课程名和成绩。
(20) 查询没有选修直接先修课就选修了后续课程的学生选课信息：姓名、学号、已选课程名、未选先 修课名称。

答案仅供参考，数据库是一门实践性很强的课程，各位同学还是要自主完成哦。
答案如有错误，欢迎指出。

SELECT Eno,Ename,Enature,Ecapital
FROM LET

SELECT Bname,Tel
FROM bankt
WHERE Bno=‘102100028292’

SELECT Eno,Bno,LSdate,Lamount
FROM Loant
WHERE Lamount>30 AND Lamount< 80

SELECT Eno
FROM Loant
WHERE LEdate>'2009-1-1’AND Lterm=10

SELECT MAX(Ecapital) 最高注册资金,MIN(Ecapital) 最低注册资金,AVG(Ecapital) 平均注册资金
FROM LET
WHERE Enature=‘私营企业’

SELECT Enature 经济性质,MAX(Ecapital) 最高注册资金,MIN(Ecapital) 最低注册资金,AVG(Ecapital) 平均注册资金
FROM LET
GROUP BY Enature

SELECT Eno,count()
FROM Loant
GROUP BY Eno
ORDER BY count() ASC

SELECT AVG(Lamount) 平均贷款金额,count()
FROM Loant
GROUP BY Eno
HAVING count()>3

SELECT Enature 经济性质,count(*) 贷款总数,AVG(Lamount) 平均贷款金额
FROM Loant,LET
WHERE Loant.Eno=LET.Eno
GROUP BY Enature
ORDER BY AVG(Lamount) DESC LIMIT 3

SELECT Ename,Bname,LSdate,Lamount,Lterm
FROM Loant,LET,bankt
WHERE Loant.Eno=LET.Eno AND Bankt.Bno=LoanT.Bno AND (Lterm=5 OR Lterm=10 OR Lterm=15)

SELECT Lamount,Ename,Bname,LSdate
FROM Bankt,LET,Loant
WHERE Bankt.Bno=Loant.Bno AND LET.Eno=LoanT.Eno AND Enature=‘集体企业’ AND Bname LIKE ‘%北京%’

多表连接
SELECT Ename
FROM LET LEFT JOIN LoanT ON LET.Eno=LoanT.Eno
WHERE Lamount=null
子查询
SELECT Ename
FROM LET
WHERE Ename NOT IN (
select Ename
FROM Loant
);

SELECT Eno
FROM Loant
GROUP BY Eno
HAVING count(*)!=0

SELECT sname,ssex
FROM student,sc
where sdept=“CS” AND student.sno!=sc.sno

SELECT MAX(grade),MIN(grade),AVG(grade),count(*)
FROM sc,course
where sc.cno=course.cno AND cname=“PASCAL程序设计”

SELECT cname,sdept,MAX(grade),MIN(grade),AVG(grade),count(*)
FROM sc,course,student
where sc.cno=course.cno AND student.sno=sc.sno AND cname=“数据库原理”
group by sdept

select sno,
(
case
when sum(credit)>10 then ‘优秀’
when sum(credit) between 6 AND 10 then ‘正常’
else ‘应努力’
end
) 评价,sum(credit) 总学分
FROM sc,course
where sc.Cno=course.Cno
group by sno

select sc.sno,
(
case
when count(sc.cno)>3 AND AVG(grade)>90 then ‘优秀’
when count(sc.cno)>3 AND AVG(grade) between 80 AND 90 then ‘较好’
when count(sc.cno)<=3 AND AVG(grade)>90 then ‘较好’
when count(sc.cno)<=3 AND AVG(grade) between 80 AND 90 then ‘一般’
else ‘应努力’
end
) 评价,sdept 系名,count(sc.sno) 原修课门数,AVG(grade) 平均成绩
FROM sc,course,student
where sc.Cno=course.Cno AND student.sno=sc.sno
group by sc.sno

select sname,cname,grade
FROM student,course,sc
where student.sno=sc.sno AND course.cno=sc.cno AND sdept=“SE” AND cname=“数据库原理” AND grade=(
select min(grade)
from sc,student,course
where student.sno=sc.sno AND course.cno=sc.cno AND sdept=“SE” AND cname=“数据库原理”
)

select sname,student.sno,course.cname,pre_table.pre
FROM student,sc,course,
(
select c1.cno cno,c2.cname pre
from course c1
left outer join course c2 on c1.cpno=c2.cno
)pre_table
where sc.cno=student.sno AND sc.cno=course.cno AND
pre_table.cno=course.cno AND cpno is not null AND cpno not in
(
select c.cno
from student stu,sc,course c
where sc.sno=student.sno AND sc.cno=c.cno AND
c.cno=course.cpno AND stu.sno=student.sno
)