mysql中的degree升序,sql 练习(四)

环境是mysql

SELECT sname,ssex,sbirthday

FROM student

WHERE ssex = '女'

UNION

SELECT tname,tsex,tbirthday

FROM teacher

WHERE tsex = '女'

32、查询成绩比该课程平均成绩低的同学的成绩表。

SELECT a.sno,a.cno,a.degree

FROM score a

LEFT JOIN (

SELECT AVG(degree) degree,cno

FROM score

GROUP BY cno)b

ON a.cno=b.cno

WHERE a.degree< b.degree

SELECT Sno,Cno,degree

FROM Score a

WHERE degree

33、查询所有任课教师的Tname和Depart.

SELECT tname,depart

FROM teacher

WHERE tno IN(SELECT tno FROM course )

SELECT tname,depart

FROM teacher a

WHERE EXISTS (SELECT * FROM course b WHERE a.tno=b.tno)

34、查询所有未讲课的教师的Tname和Depart.

SELECT tname,depart

FROM Teacher

WHERE tno NOT IN(SELECT tno FROM course)

35、查询至少有2名男生的班号。

SELECT class

FROM Student

WHERE Ssex='男'

GROUP BY class

HAVING COUNT(SSex)>=2

36、查询不姓王的同学信息

SELECT *

FROM student

WHERE sname NOT LIKE '王%'

37、查询Student表中每个学生的姓名和年龄。

SELECT Sname,(YEAR(NOW())-YEAR(Sbirthday)) age

FROM student

38、查询Student表中最大和最小的Sbirthday日期值。

SELECT MAX(sbirthday),MIN(sbirthday)

FROM student

39、以班号和年龄从大到小的顺序查询Student表中的全部记录。

SELECT *

FROM student

ORDER BY sbirthday ASC,class DESC

40、查询“男”教师及其所上的课程。

SELECT a.tname,b.cname

FROM teacher a

JOIN course b

ON a.tno = b.tno

WHERE a.tsex ='男'

41、查询最高分同学的Sno、Cno和Degree列。

SELECT *

FROM score

WHERE degree=(SELECT MAX(degree)FROM score)

42、查询所有选修“计算机导论”课程的“男”同学的成绩表。

SELECT sno,degree

FROM score

WHERE sno IN (SELECT sno FROM student WHERE ssex='男')

AND cno IN (SELECT cno FROM course WHERE cname='计算机导论')

43、查询和“李军”同性别的所有同学的Sname.

SELECT sname

FROM student

WHERE ssex=(SELECT ssex FROM student WHERE sname='李军')

44、查询日期

SELECT *

FROM teacher

WHERE tbirthday=STR_TO_DATE('1972-05-05 00:00:00','%Y-%m-%d')