-- 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(*) 男生人數(shù) 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='男';
