データベース45問(mysql)

リンクは以下の通りです:学生成績表転送ゲート

        ：
select * from [  ] order by id desc limit 1;
1、   Student        Sname、Ssex Class 。
select sname,ssex,class from students;
2、               Depart 。
select * from teachers group by depart;
3、   Student      。
select * from students;
4、   Score     60 80       。
select  *  from scores  where  degree between 60 and 80;
5、   Score     85，86 88   。
select  *  from scores  where  degree in (85,86,88);
6、   Student  “95031”     “ ”     。
select  *  from students where class='95031' or ssex=' ';
7、  Class    Student      。
select  *  from students order by class desc;
8、  Cno  、Degree    Score      。
select  *  from scores order by cno asc,degree desc;
9、   “95031”      。
select  count(sno) al  from students where class='95031';
10、  Score               。
select sno,cno  from scores where degree=(select max(degree) from scores);
11、  ‘3-105’       。
select avg(degree) pinjun from scores where cno='3-105';
12、  Score     5        3          。
SELECT AVG(degree) FROM scores WHERE cno IN (SELECT cno FROM scores 
GROUP BY cno HAVING COUNT(cno)> 5 ) AND cno LIKE '3%';
13、       70，     90 Sno 。
select sno from scores  group by sno having min(degree)>70 and 
max(degree)<90;
14、       Sname、Cno Degree 。
select cno,sname,degree from scores sc,students st  where sc.sno=st.sno;
15、       Sno、Cname Degree 。
select sc.sno,sname,degree from scores sc,students st  where sc.sno=st.sno;
16、       Sname、Cname Degree 。
select sc.sno,cname,degree from scores sc,students st,courses co  
where sc.sno=st.sno and sc.cno=co.cno;
17、  “95033”         。
select avg(degree) from scores sc,students st where sc.sno=st.sno and 
st.class='95033';
18、             grade ：
        Sno、Cno rank 。
select sno,cno,degree,gr.rank from scores sc,grade gr where degree 
between low and upp;
19、    “3-105”       “109”             。
select * from scores where cno='3-105' and 
degree>(select degree from scores where sno='109' and cno='3-105');
20、  score                         。
select * from Scores a where Degree <(select MAX(degree) from scores 
b where a.Cno=b.Cno) and Sno in(select Sno from Scores group by 
Sno having count(*)>1)
21、         “109”、    “3-105”        。
select * from students,scores where students.sno=scores.sno
and scores.degree>(select degree from scores where
cno='3-105' and sno='109')
22、      108             Sno、Sname Sbirthday 。
SELECT sno,sname,YEAR(sbirthday) FROM students WHERE YEAR(sbirthday) 
 = (SELECT YEAR(sbirthday) FROM students WHERE sno = '108';
23、  “  “         。
select degree from scores where cno=(select cno from courses where 
tno=(select tno from teachers where tname='  '));
24、              5      。
select tname from teachers where tno=(select tno 
from courses where cno=(select cno from 
scores group by cno having count(*)>5));
25、  95033  95031        。
select * from students where class='95033' or class='95031';
26、     85        Cno.
select  distinct cno from scores where degree >85;
27、   “    “          。
select sc.degree from scores sc,courses co,teachers te where 
sc.cno=co.cno and co.tno=te.tno and te.depart like 
'  %';
28、  “    ” “     “        Tname Prof。
select Tname,Prof from teachers a where Prof not in(select Prof 
from teachers b where a.Depart!=b.Depart)
29、       “3-105“            “3-245”         Cno、Sno Degree,  Degree        。
select cno,sno,degree from scores where degree>=(select 
max(degree) from scores where cno='3-245') and cno='3-105'
order by degree;
30、       “3-105”          “3-245”        Cno、Sno Degree.
select Cno,Sno,Degree from Scores a where (select Degree from Scores b 
where Cno='3-105' and b.Sno=a.Sno)>(select Degree from Scores c where 
Cno='3-245' and c.Sno=a.Sno)
31、          name、sex birthday.
select distinct Sname as name,Ssex as sex,Sbirthday as birthday from 
students union
select distinct Tname as name,Tsex as sex,Tbirthady as birthday from teachers;
32、    “ ”   “ ”   name、sex birthday.
select distinct Sname as name,Ssex as sex,Sbirthday as birthday from 
students where Ssex=' ' union
select distinct Tname as name,Tsex as sex,Tbirthady as birthday from 
teachers where Tsex=' '
33、                    。
select Sno,Cno,Degree from Scores a where a.Degree<(select AVG(Degree) 
from Scores b where a.Cno=b.Cno);
34、         Tname Depart.
select Tname,Depart from teachers where Tname in (select distinct 
Tname from teachers,courses,scores where Teachers.Tno=Courses.Tno 
and Courses.Cno=Scores.Cno);
35             Tname Depart. 
select Tname,Depart from teachers where Tname not in (select distinct 
Tname from Teachers,courses,Scores where teachers.Tno=course.tno
and courses.cno=scores.Cno);
36、     2      。
select class from students where ssex=' ' group by 
class having count(*)>1;
37、  Student    “ ”     。
select sname from students where sname not like ' %';
38、  Student            。
select now(),sbirthday,timestampdiff(YEAR,sbirthday,now()) AS '  ' 
from students;
39、  Student        Sbirthday   。
select max(Sbirthday) as   ,min(Sbirthday) as    from students;
40、               Student       。
select  * from students order by class desc,sbirthday asc;
41、  “ ”         。
select tname,cname from teachers te,courses co 
where te.tsex=' ' and te.tno=co.tno;
42、        Sno、Cno Degree 。
select sno,cno,Degree from scores where 
degree=(select max(degree)from scores)
43、   “  ”         Sname.
select sname from students where ssex=(select ssex from students
where sname='  ') and sname not in ('  ');
44、   “  ”         Sname.
select sname from students where ssex=(select ssex from students where  
sname='  ') 
and sname not in ('  ') and class=(select class from students where  
sname='  ')
45、      “     ”   “ ”      
select sno,degree from scores where sno in (select sno from students
where Ssex=' ') and cno in (select cno from ourses where 
cname='     ')