mysql----select----练习题(2)

-- 14、查询所有学⽣的Sname、Cno和Degree列。
/*
select Cno,degree from SCORE where SNAME in (
select SNAME from STUDENG) */
select b.sname,a.cno,a.degree 
from SCORE a
inner join STUDENG b
on a.sno=b.sno

---

-- 15、查询所有学⽣的Sno、Cname和Degree列。
select a.Sno,b.Cname,a.Degree 
from SCORE a
inner join COURSE b
on a.cno=b.cno

---

-- 16、查询所有学⽣的Sname、Cname和Degree列。
select a.sname,b.cname,c.degree 
from STUDENG a
inner join COURSE b
inner join SCORE c
on a.sno=c.sno and b.cno=c.cno

---

-- 17、查询“95033”班所选课程的平均分。
select avg(b.degree) as'95003 avgrage'
from COURSE a
inner join SCORE b
inner join STUDENG c
on a.cno=b.cno and b.sno=c.sno
where c.class='95033'
-----------------------------------
SELECT CNO,AVG(DEGREE) FROM SCORE WHERE SNO IN (SELECT SNO FROM 
STUDENG WHERE CLASS =95033) GROUP BY CNO

---

-- 19、查询选修“3-105”课程的成绩⾼于“109”号同学成绩的所有同学的记录。 

select * from SCORE where cno='3-105' and degree > (
select degree from SCORE where sno='109' and cno='3-105')

---

-- 20、查询score中选学多⻔课程的同学中各科分数为⾮最⾼分成绩的记录。

SELECT * FROM SCORE A WHERE A.SNO IN (SELECT SNO FROM SCORE GROUP BY 
SNO HAVING COUNT(*)>1)

---

-- 21.查询1975年之后出⽣的学⽣的所学课程以及成绩。
select a.cname,b.degree,c.sname 
from COURSE a
inner join SCORE b 
inner join STUDENG c
on a.cno=b.cno and b.sno= c.sno
where c.sbirthday > 1975-01-01;

---

-- 23、查询“张旭“教师任课的学⽣成绩。
select  degree
from TEACHER a
inner join COURSE b
inner join SCORE c
on a.tno= b.tno and b.cno=c.cno
where a.tname='张旭'

---

-- 24、查询选修某课程的同学⼈数多于5⼈的教师姓名。
select a.tname 
from TEACHER a
inner join  COURSE b
inner join SCORE c
on a.tno=b.tno and b.cno=c.cno
group by c.cno having count(*)>5

---

-- 25、查询95033班和95031班全体学⽣的记录
select * from STUDENG where class in (95033,95031)

---

-- 26、查询存在有85分以上成绩的课程Cno
select cno from practice.SCORE where degree>85;

---

-- 27、查询出“计算机系“教师所教课程的成绩表。
/*
select * from SCORE where sno in (
select sno from COURSE where tno in ( 
select tno from TEACHER where DEPART ='计算机系'))*/
------------------------------------------------------------
select a.sno,a.cno,a.degree
from SCORE a
inner join COURSE b
inner join TEACHER c
on a.cno=b.cno and b.tno=c.tno
where c.depart ='计算机系'

---

-- 28、查询“计算机系”与“电⼦⼯程系“不同职称的教师的Tname和Prof。
select tname,prof from TEACHER

---

-- 29、查询选修编号为“3-105“课程且成绩⾄少⾼于选修编号为“3-245”的同学的
-- Cno、Sno和Degree,并按Degree从⾼到低次序排序
SELECT CNO,SNO,DEGREE FROM SCORE WHERE CNO='3-105'AND
DEGREE >(SELECT MIN(DEGREE)FROM SCORE WHERE CNO='3-245')ORDER BY 
DEGREE DES

---

-- 30、查询选修编号为“3-105”且成绩⾼于选修编号为“3-245”课程的同学的Cno、Sno和Degree.
select * from SCORE where cno='3-105' and degree > (
select max(degree) from SCORE where cno='3-245')

---

-- 31、查询所有教师和同学的name、sex和birthday 
select sname as name,ssex as sex,sbirthday as birthday from STUDENG union select
TNAME ,TSEX,TBIRTHDAY FROM TEACHER

---

-- 32、查询所有“⼥”教师和“⼥”同学的name、sex和birthday.
select sname as name,ssex as sex, sbirthday as birthday from STUDENG 
where ssex='⼥' union select tname,tsex,tbirthday from TEACHER where tsex='⼥'

---

--33、查询成绩⽐该课程平均成绩低的同学的成绩表。

---

-- 34、查询所有任课教师的Tname和Depart
select tname,depart from TEACHER 

---

-- 34、查询所有任课教师的Tname和Depart
SELECT TNAME,DEPART FROM TEACHER WHERE TNO NOT IN(SELECT TNO FROM 
COURSE)

---

-- 36 查询⾄少有2名男⽣的班号。
select class from STUDENG where ssex='男'group by class having count(*)>=2

---

-- 37、查询Student表中不姓“王”的同学记录。
select * from STUDENG where sname not like'王%'

---

-- 38、查询Student表中每个学⽣的姓名和年龄。
select sname,year(now())-year(sbirthday) as age from STUDENG 

---

-- 39、查询Student表中最⼤和最⼩的Sbirthday⽇期值。
select max(sbirthday),min(sbirthday) from STUDENG 

---

-- 40、以班号和年龄从⼤到⼩的顺序查询Student表中的全部记录
select * from STUDENG order by year(now())-year(sbirthday) asc,class 

---

--  41、查询“男”教师及其所上的课程
select b.cname ,a.tname,a.tsex
from TEACHER a
inner join COURSE b
on a.tno=b.tno
where tsex='男'

---

-- 42、查询最⾼分同学的Sno、Cno和Degree列
select sno,cno,degree from SCORE where degree = (
select max(degree) from SCORE )

---

-- 43、查询和“李军”同性别的所有同学的Sname
select sname From STUDENG where ssex in ( 
select ssex from STUDENG where sname='李军')

---

-- 45、查询所有选修“计算机导论”课程的“男”同学的成绩表。
select b.degree,c.ssex,c.sname
from COURSE a
inner join SCORE b
inner join STUDENG c
on a.cno= b.cno and b.sno=c.sno
where a.cname='计算机导论' and c.ssex='男'

---

-- 46、查询出选修课程号为3-245和6-166的课程的学⽣学号与姓名
select a.sno,a.sname 
from STUDENG a 
inner join  COURSE b
inner join SCORE c
on a.sno=c.sno and b.cno = c.cno
where b.cno=3-245 or b.cno='6-166'

---

-- 47、查询出没有选修课程号为3-245和6-166的课程的学⽣学号与姓名
select a.sno,a.sname, b.cno
from STUDENG a 
inner join  COURSE b
inner join SCORE c
on a.sno=c.sno and b.cno = c.cno
where b.cno!=3-245 or b.cno!='6-166'
group by sname