mysql练习题（1~10）

练习题（1~10）"/>

mysql练习题（1~10）

文章目录

• • 材料
  • 1.查询“001”课程比“002”课程成绩高的所有学生的学号;
  • 2、查询平均成绩大于60分的同学的学号和平均成绩;
  • 3、查询所有同学的学号、姓名、选课数、总成绩;
  • 4、查询姓“李”的老师的个数;
  • 5、查询没学过“叶平”老师课的同学的学号、姓名;
  • 6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名;
  • 7、查询学过“叶平”老师所教的所有课的同学的学号、姓名;
  • 8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名;
  • 9、查询所有课程成绩小于60分的同学的学号、姓名;
  • 10、查询没有学全所有课的同学的学号、姓名;

材料

ORACLE(表+数据)
CREATE TABLE student (
sid varchar2(10) NOT NULL,
sName varchar2(20) DEFAULT NULL,
sAge date ,
sSex varchar2(10) DEFAULT NULL,
PRIMARY KEY (sid)
)

CREATE TABLE course (
cid varchar2(10) NOT NULL,
cName varchar2(10) DEFAULT NULL,
tid number(20) DEFAULT NULL,
PRIMARY KEY (cid)
)

CREATE TABLE sc (
sid varchar2(10) DEFAULT NULL,
cid varchar2(10) DEFAULT NULL,
score number(10) DEFAULT NULL
)

CREATE TABLE teacher (
tid number(10) DEFAULT NULL,
tName varchar2(10) DEFAULT NULL
)

insert into course(cid,cName,tid) values (‘001’,‘企业管理’,3);
insert into course(cid,cName,tid) values (‘002’,‘马克思’,3);
insert into course(cid,cName,tid) values (‘004’,‘数据库’,1);
insert into course(cid,cName,tid) values (‘005’,‘英语’,1);

insert into sc(sid,cid,score) values (‘1001’,‘001’,80);
insert into sc(sid,cid,score) values (‘1001’,‘002’,60);
insert into sc(sid,cid,score) values (‘1001’,‘003’,70);
insert into sc(sid,cid,score) values (‘1002’,‘001’,85);
insert into sc(sid,cid,score) values (‘1002’,‘002’,70);
insert into sc(sid,cid,score) values (‘1003’,‘004’,90);
insert into sc(sid,cid,score) values (‘1003’,‘001’,90);
insert into sc(sid,cid,score) values (‘1003’,‘002’,99);
insert into sc(sid,cid,score) values (‘1004’,‘002’,65);
insert into sc(sid,cid,score) values (‘1004’,‘003’,50);
insert into sc(sid,cid,score) values (‘1005’,‘005’,80);
insert into sc(sid,cid,score) values (‘1005’,‘004’,70);
insert into sc(sid,cid,score) values (‘1003’,‘003’,10);
insert into sc(sid,cid,score) values (‘1003’,‘005’,10);

insert into student(sid,sName,sAge,sSex) values (‘1001’,‘张三丰’,to_date(‘1980-10-12 23:12:36’,‘YYYY-MM-DD HH24:MI:SS’),‘男’);
insert into student(sid,sName,sAge,sSex) values (‘1002’,‘张无极’,to_date(‘1995-10-12 23:12:36’,‘YYYY-MM-DD HH24:MI:SS’),‘男’);
insert into student(sid,sName,sAge,sSex) values (‘1003’,‘李奎’,to_date(‘1992-10-12 23:12:36’,‘YYYY-MM-DD HH24:MI:SS’),‘女’);
insert into student(sid,sName,sAge,sSex) values (‘1004’,‘李元宝’,to_date(‘1980-10-12 23:12:36’,‘YYYY-MM-DD HH24:MI:SS’),‘女’);
insert into student(sid,sName,sAge,sSex) values (‘1005’,‘李世明’,to_date(‘1981-10-12 23:12:36’,‘YYYY-MM-DD HH24:MI:SS’),‘男’);
insert into student(sid,sName,sAge,sSex) values (‘1006’,‘赵六’,to_date(‘1986-10-12 23:12:36’,‘YYYY-MM-DD HH24:MI:SS’),‘男’);
insert into student(sid,sName,sAge,sSex) values (‘1007’,‘田七’,to_date(‘1981-10-12 23:12:36’,‘YYYY-MM-DD HH24:MI:SS’),‘女’);

insert into teacher(tid,tName) values (1,‘李老师’);
insert into teacher(tid,tName) values (2,‘何以琛’);
insert into teacher(tid,tName) values (3,‘叶平’);

teacher: tname, tid
course：tid , cname , cid
sc : cid , score , sid
student: sid , sname ,ssex

问题：

1.查询“001”课程比“002”课程成绩高的所有学生的学号;

SELECT a.sid  FROM (SELECT sid ,score FROM sc where cid = 001) AS a ,
(SELECT sid ,score FROM sc where cid = 002) AS b 
where a.sid = b.sid  and a.score > b.score ;

2、查询平均成绩大于60分的同学的学号和平均成绩;

SELECT sid ,avg(score) FROM sc GROUP BY sid HAVING avg(score) >60;

3、查询所有同学的学号、姓名、选课数、总成绩;

SELECT st.sid ,st.sname ,COUNT(sc.cid),SUM(sc.score) FROM student AS stLEFT JOIN sc on st.sid= sc.sid 
GROUP BY sid;

4、查询姓“李”的老师的个数;

SELECT count(*) FROM teacher WHERE tname LIKE "李%"; 

5、查询没学过“叶平”老师课的同学的学号、姓名;

select Student.sid,Student.Sname 
from Student 
where sid not in (select distinct( SC.sid) from SC INNER JOIN course on SC.cid = course.cid 
INNER JOIN teacher on course.tid = teacher.tid where Teacher.Tname='叶平');

6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名;

select Student.sid,Student.Sname from Student,SC where Student.sid=SC.sid 
and SC.cid='001'and exists( Select * from SC where sc.cid='002');

7、查询学过“叶平”老师所教的所有课的同学的学号、姓名;

SELECT distinct st.sName , st.sid from student as st inner join sc on sc.sid = st.sid 
inner join course on sc.cid = course.cid inner join teacher on course.tid = teacher.tid 
where teacher.tname = '叶平';

8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名;

select st.sid ,st.sname from student as st inner join  (select sid ,score from sc where sc.cid = 001) as a on st.sid = a.sid inner join (select sid ,score from sc where sc.cid = 002) as b on st.sid = b.sid 
where a.score > b.score ;

9、查询所有课程成绩小于60分的同学的学号、姓名;

SELECT distinct st.sid ,st.sname from student as st inner join sc on st.sid = sc.sid 
where sc.score <60;

10、查询没有学全所有课的同学的学号、姓名;

SELECT st.sname ,st.sid from student as st inner join sc on st.sid = sc.sid 
group by st.sid 
having (count(sc.cid)<5);