lyps.net
当前位置:首页 >> 经典sql语句面试题 >>

经典sql语句面试题

select a.user_job as "职业",b.man_num as "男性人数",c.woman_num as "女性人数",count(id) as "总人数" from user a left join (select user_job,count(id) as man_num from user where user_sex=1 group by user_job) b on a.user_job=b.use...

-- 查询所有同学的学号、姓名、选课数、总成绩;select S.Sid,Sname,选课数=COUNT(Cid),总成绩=SUM(Score) from Student S join Sc on S.Sid=sc.Sid group by S.Sid,Sname-- 查询学过“叶萍”老师所教的所有课程的同学学号、姓名;select S.Sid,Sn...

创建表插入数据: create table test(col1 int,col2 int)insert into test values (2001,1000)insert into test values (2002,2000)insert into test values (2003,3000)insert into test values (2004,4000)执行: select a.col1,sum(isnull(b....

a)selectpnameas'商品名',avg(qty)as平均销售量froms,p,mwherem.city='上海'ands.mno=m.mnoandp.pno=s.pno,selectp.Pno,p.pname,sum(s.qty)fromsleftjoinpons.pno=p.pnoleftjoinmonp.Mno=m.Mnowherem.city='上海市'groupbyp.Pno,p.pname,p.city...

1,select * from( select a.price*c.number,a.name from goods a,trade c where a.id=c.goodid order by a.price*c.number) where rownum

(1)统计有学生选修的课程门数 select count(distinct c#) from SC 2)求选修C4课程的女学生的平均年龄 select avg(s.age) --最好都带上前缀,养成好习惯 from s,c,sc where s.s#=sc.s# and c.c#=sc.c# and c.cname='C4' and s.sex='女'--字符类型...

1. select * from student where score>80 and s_id in (select sid from student_class where c_id=(select c_id from class where c_name='一班')) 2. select c.c_name,女生人数=sum(s.s_id),平均分=avg(s.score) from classes c inner join s...

1、select Sno,Sage from student where Sage

1,select e_name from employee where e_hiredate>to_date('2001-01-01','YYYY-MM-dd') and e_hiredate

比如学生表,有学号,姓名,年龄,性别这四个字段 select a.学号,a.姓名,a.年龄,a.性别,sum(c.成绩) as 总成绩from 学生表 a,成绩表 b,选课表 c where a.学号=b.学号and b.课程号=c.课程号group by a.学号,a.姓名,a.年龄,a.性别order by a.姓名 ...

网站首页 | 网站地图
All rights reserved Powered by www.lyps.net
copyright ©right 2010-2021。
内容来自网络,如有侵犯请联系客服。zhit325@qq.com