-- 1、查询所有教师和同学的name、sex和birthday.
select tname ,tsex ,tbirthday from teachers
union
select sname ,ssex ,sbirthday from students ;
-- 2、查询所有“女”教师和“女”同学的name、sex和birthday.
select tname as name,tsex sex,tbirthday birthday from teachers where tsex = '女'
UNION
select sname as name,ssex sex,sbirthday birthday from students where ssex ='女';
-- 3、查询成绩比该课程平均成绩低的同学的成绩表。
select a.*,c.sname 姓名 from scores a
INNER JOIN students c on a.sno=c.sno
inner join (select b.cno,avg(degree) 每科平均分 from scores b GROUP BY b.cno) b on a.cno=b.cno where a.degree<b.`每科平均分`;
-- 4、查询所有任课教师的Tname和Depart.
select tname,depart from teachers;
-- 5 查询所有未讲课的教师的Tname和Depart.
select tname,depart from teachers
where tno not in (select tno from courses);
-- 6、查询至少有2名男生的班号。
select class,count(*) 男生人数 from students
where ssex = '男'
group by class
having count(*)>=2;
-- 7、查询Student表中不姓“王”的同学记录。
select * from students where sname not like '王%';
-- 8、查询Student表中每个学生的姓名和年龄。
select sname, year(CURRENT_DATE)-year(sbirthday)as 年龄 from students;
-- 9、查询Student表中最大和最小的Sbirthday日期值。
select max(sbirthday),min(sbirthday) from students;
-- 10、以班号和年龄从大到小的顺序查询Student表中的全部记录。
select * from students order by class desc, sbirthday desc;
-- 11、查询“男”教师及其所上的课程。
select b.cname from teachers a inner join courses b on a.tno=b.tno where a.tsex='男';
-- 12、查询最高分同学的Sno、Cno和Degree列。
select a.sno,c.cno,b.degree from students a inner join scores b on a.sno=b.sno inner join courses c on b.cno=c.cno where b.degree = (select max(degree) from scores );
-- 13、查询和“李军”同性别的所有同学的Sname.
select sname from students where ssex=(select ssex from students where sname='李军') and sname !='李军';
-- 14、查询和“李军”同性别并同班的同学Sname.
select a.sname from students a inner join (select ssex,class from students where sname ='李军' ) s on a.ssex=s.ssex where a.class=s.class and a.sname != '李军';
-- 15、查询所有选修“计算机导论”课程的“男”同学的成绩表
select a.*,b.sname ,c.cname from scores a
left join students b on a.sno=b.sno
left join courses c on c.cno=a.cno
where c.cname='计算机导论' and b.ssex='男';
