你的位置:首页 > 数据库

[数据库]数据库查询4


 1 --查询所有任课教师的Tname和Depart. 2 select t.tname 教师,c.cname 课程名 from teacher t,course c where t.tno = c.tno; 3 --查询Student表中不姓“王”的同学记录。 4 select * from student t where t.sname not like '王%'; 5 --查询Student表中每个学生的姓名和年龄。 6 select st.sname,extract(year from sysdate)-extract(year from st.sbirthday) as 年龄 from student st; 7 --查询Student表中最大和最小的Sbirthday日期值。 8 select max(extract(day from st.sbirthday)) 最大, min(extract(day from st.sbirthday)) 最小 from student st; 9 --查询“男”教师及其所上的课程。10 select t.tname 姓名,c.cname 课程 from teacher t,course c where t.tno = c.tno;11 --查询最高分同学的Sno、Cno和Degree列。12 select * from score sc where sc.degree = (select max(degree) from score);13 --查询和“李军”同性别的所有同学的Sname.14 select st.sname from student st where st.ssex = (select s.ssex from student s where s.sname='李军'); 15 --查询和“李军”同性别并同班的同学Sname.16 select st.sname from student st where st.ssex = (select st1.ssex from student st1 where st1.sname ='李军')17 and st.class = (select st1.class from student st1 where st1.sname = '李军');18 --查询所有选修“计算机导论”课程的“男”同学的成绩表。19 select * from score sc where sc.cno = (select c.cno from course c where c.cname='计算机导论')20 and sc.sno in (select st.sno from student st where st.ssex = '男'); 21 --以班号和年龄从大到小的顺序查询Student表中的全部记录。22 select * from student st order by st.class desc,extract(year from sysdate)-extract(year from st.sbirthday) desc;23 -- 查询所有未讲课的教师的Tname和Depart. 24 select t.tname,t.depart from teacher t where t.tno =(25 select c.tno from course c where c.cno not in(select sc.cno from score sc group by sc.cno));26 --查询至少有2名男生的班号。27 select st.class from student st group by st.class having count(st.ssex)>=2;28 --查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列。29 select st.sno,st.sname,st.sbirthday from student st where st.sno<>'108'and extract(year from st.sbirthday)=30 (select extract(year from st2.sbirthday) from student st2 where st2.sno='108') ;