-- 1、查詢所有教師和同學(xué)的name、sex和birthday.

select tname ,tsex ,tbirthday? from teachers

union

select sname ,ssex ,sbirthday? from students ;

-- 2、查詢所有“女”教師和“女”同學(xué)的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、查詢成績比該課程平均成績低的同學(xué)的成績表。

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表中不姓“王”的同學(xué)記錄。

select * from students where sname not like '王%';

-- 8、查詢Student表中每個學(xué)生的姓名和年齡。

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、查詢最高分同學(xué)的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、查詢和“李軍”同性別的所有同學(xué)的Sname.

select sname from students where ssex=(select ssex from students where sname='李軍') and sname !='李軍';

-- 14、查詢和“李軍”同性別并同班的同學(xué)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、查詢所有選修“計算機(jī)導(dǎo)論”課程的“男”同學(xué)的成績表

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='計算機(jī)導(dǎo)論' and b.ssex='男';