sql练习题2

31、查询所有教师和同学的name、sex和birthday.

select students.sname,students.ssex,students.sbirthday,teachers.TNAME,teachers.TSEX,teachers.TBIRTHDAY from students inner join teachers
SELECT Sname,Ssex,Sbirthday
FROM Students
UNION
SELECT Tname,Tsex,Tbirthday

FROM Teachers;
32、查询所有“女”教师和“女”同学的name、sex和birthday.

SELECT Sname,Ssex,Sbirthday
FROM Students where ssex = '女'
UNION
SELECT Tname,Tsex,Tbirthday
FROM Teachers where tsex = '女';

33、查询成绩比该课程平均成绩低的同学的成绩表。
select * from scores where degree < avg(degree)
34、查询所有任课教师的Tname和Depart.
select tname,depart from teachers
SELECT Tname,Depart
FROM Teachers
WHERE Tno IN(
SELECT Tno
FROM Courses
);
35 查询所有未讲课的教师的Tname和Depart.
SELECT Tname,Depart
FROM Teachers
WHERE Tno not IN(
SELECT Tno
FROM Courses
);
36、查询至少有2名男生的班号。
select class from students where ssex = ‘男 ‘group by ssex having count(ssex ) >= 2
SELECT Class,COUNT(1) AS boyCount
FROM Students
WHERE Ssex=’男’
GROUP BY Class
HAVING boyCount>=2;
37、查询Student表中不姓“王”的同学记录。
select * from students where sname not like ’ 王%’
38、查询Student表中每个学生的姓名和年龄。
select sname,year(now())-year(sbirthday) from students
39、查询Student表中最大和最小的Sbirthday日期值。
select max(sbirthday),min(sbirthday) from students
40、以班号和年龄从大到小的顺序查询Student表中的全部记录。
select * from students class desc ,sbirthday asc