建表语句就不写了,这个是基操吧
- 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩。
select student.*,t1.avgscore
from student inner JOIN(
select sc.SId ,AVG(sc.score)as avgscore
from sc
GROUP BY sc.SId
HAVING AVG(sc.score)>=60)as t1 on student.SId=t1.SId
- 查询在 SC 表存在成绩的学生信息。
select DISTINCT student.*
from student ,sc
where student.SId=sc.SId
- 查询「李」姓老师的数量。
select count(*)
from teacher
where teacher.Tname like '李%
- 查询学过「张三」老师授课的同学的信息。
select student.*
from teacher ,course ,student,sc
where teacher.Tname='张三'
and teacher.TId=course.TId
and course.CId=sc.CId
and sc.SId=student.SId
- 查询没有学全所有课程的同学的信息。
- 解法1
select student.*
from sc ,student
where sc.SId=student.SId
GROUP BY sc.SId
Having count(*)<(select count(*) from course)
但这种解法得出来的结果不包括什么课都没选的同学。
- 解法2
select DISTINCT student.*
from
(select student.SId,course.CId
from student,course ) as t1 LEFT JOIN (SELECT sc.SId,sc.CId from sc)as t2 on t1.SId=t2.SId and t1.CId=t2.CId,student
where t2.SId is null
and t1.SId=student.SId
利用笛卡尔积可以把什么课都没选的同学查询出来
- 查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息。
select DISTINCT student.* from sc ,student where sc.CId in (select CId from sc where sc.SId='01') and sc.SId=student.SId
- 查询和" 01 "号的同学学习的课程 完全相同的其他同学的信息。
select DISTINCT student.*
from (
select student.SId,t.CId
from student ,(select sc.CId from sc where sc.SId='01') as t) as t1 LEFT JOIN sc on t1.SId=sc.SId and t1.CId=sc.CId,student
where sc.SId is null
and t1.SId=student.SId
- 查询没学过"张三"老师讲授的任一门课程的学生姓名。
select *
from student
where student.SId not in
(
select student.SId
from student left join sc on student.SId=sc.SId
where EXISTS
(select *
from teacher ,course
where teacher.Tname='张三'
and teacher.TId=course.TId
and course.CId=sc.CId))
- 查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩。
select student.SId,student.Sname,avg(sc.score) from student ,sc where student.SId=sc.SId and sc.score<60 GROUP BY sc.SId HAVING count(*)>=2
- 统计各科成绩各分数段人数:课程编号,课程名称,[100-85],[85-70],[70-60],[60-0] 及所占百分比
-
这个是网上找的一些,自己还不能编写。
select t1.*,round(t1.num/t2.all_num*100,2) || '%' 百分比
from
(select m.cid , m.Cname ,
(case
when n.score >= 85 then '85-100'
when n.score >= 70 and n.score < 85 then '70-85'
when n.score >= 60 and n.score < 70 then '60-70'
else '0-60'
end) as px,
count(1) num
from Course m , sc n
where m.cid = n.cid
group by m.cid , m.Cname , (
case when n.score >= 85 then '85-100'
when n.score >= 70 and n.score < 85 then '70-85'
when n.score >= 60 and n.score < 70 then '60-70'
else '0-60'
end)
order by m.cid , m.Cname , px) t1,
(select m.cid , m.Cname ,
count(1) all_num
from Course m , sc n
where m.cid = n.cid
group by m.cid , m.Cname
order by m.cid , m.Cname) t2
where t1.cid=t2.cid
- 求每门课程的学生人数。
select sc.CId,count(*) as 学生人数 from sc GROUP BY sc.CId
- 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩。
select *
from sc as t1
where exists(select * from sc as t2 where t1.SId=t2.SId and t1.CId!=t2.CId and t1.score =t2.score )
- 查询每门功成绩最好的前两名。
select *
from sc as t1
where (select count(*) from sc as t2 where t1.CId=t2.CId and t2.score >t1.score)<2
ORDER BY t1.CId
- 检索至少选修两门课程的学生学号。
select DISTINCT t1.SId from sc as t1 where(select count(*) from sc where t1.SId=sc.SId)>=3
- 查询选修了全部课程的学生信息。
select student.*
from sc ,student
where sc.SId=student.SId
GROUP BY sc.SId
HAVING count(*) = (select DISTINCT count(*) from course )
- 存储过程及函数练习
- 创建一个存储过程,以学生编号为参数,输出该学生的年龄。
create or replace procedure p_sxt1(v_empno in emp.empno%type, v_sal out emp.sal%type) is
begin
select sal into v_sal from emp where empno = v_empno;
end;
--(1)执行
declare
v_empno emp.empno%type := 7369;
v_sal emp.sal%type;
begin
p_sxt1(v_empno,v_sal);
dbms_output.put_line(v_empno || ' 员工的工资为:' || v_sal);
end;
创建一个存储过程,以员工号为参数,输出该员工的工资
--Mysql方法: DELIMITER // CREATE PROCEDURE myproc(IN id varchar(10),OUT Sage datetime) BEGIN SELECT Student .Sage INTO Sage FROM Student where Sid = id; END // DELIMITER ;
调用: SET @p_id = ‘01’; SET @p_datetime = now(); CALL myproc(@p_id, @p_datetime); SELECT @p_datetime; |
- 创建一个存储过程,以课程编号为参数,输出不及格的学生信息。
create or replace procedure test60(cnumber in integer)
is
cursor student_cursor is select distinct S.* from Student S,sc where S.sid = sc.sid and sc.score < 60 and sc.cid = cnumber;
T_Student student_cursor%ROWTYPE;
//定义的是结果集中的每一行的数据类型,在后续的结果提取当中,这个会成提取出来的对象。
begin
open student_cursor;
loop
fetch student_cursor into T_Student;
exit when student_cursor%notfound;
dbms_output.put(T_Student.Sid);
dbms_output.put(T_Student.Sname);
dbms_output.put(T_Student.Sage);
dbms_output.put_line(T_Student.Ssex);
end loop;
close student_cursor;
end test60;
创建一个存储过程,以员工号为参数,返回该员工的工作年限(以参数形式返回)。
- 创建一个函数,以课程编号为参数,返回该课程的平均成绩。
create or replace function f_sxt6(v_deptno emp.deptno%type) return emp.sal%type is
vr_sal emp.sal%type;
begin
select avg(sal) into vr_sal from emp where deptno = v_deptno;
return vr_sal;
end;
--(6)执行
select f_sxt6(20) 部门平均工资 from dual;
创建一个函数,以部门号为参数,返回该部门的平均工资。
--Mysql方法:
- DELIMITER //
- CREATE FUNCTION GetAvgScoreByID(cid VARCHAR(10))
- RETURNS decimal
(
18,
1)
- BEGIN
- RETURN(SELECT avg(score) FROM course WHERE CId=cid);
- END//
- DELIMITER ;
调用:
SET @cid =’01’;
SELECT GetAvgScoreByID(@cid);
- 触发器练习
-
创建一个行级触发器CASCADE_DEL_UPD,当删除课程表中某门课程时,成绩表中该门课程的成绩也一并删除。create or replace trigger CASCADE_DEL_UPD
before update of deptno or delete
on dept
for each row
declare
begin
if deleting then
delete from emp where deptno=:old.deptno;
end if;
end;
-
--Mysql方法:
DELIMITER ||
CREATE TRIGGER demo BEFORE DELETE
ON Course FOR EACH ROW
BEGIN
If deleting then
delete from SC where CId = OLD.CId
END
||
DELIMITER ;