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

sql语句 面试题

比如学生表,有学号,姓名,年龄,性别这四个字段 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.姓名 ...

我也不知道 哈哈哈,因为这是我没上过

SELECT *,( SELECT COUNT(*) FROM (SELECT COUNT(*),b.sid FROM sc b LEFT JOIN course c ON b.Cid=c.Cid GROUP BY b.sid,c.tid ) ow WHERE ow.sid = a.sid ) as '选课数量',(SELECT sum(Score) FROM Sc d WHERE a.sid = d.sid )as '总成绩' FRO...

A.创建表格CODE省略 注明:学生表PK stu_id 课程表pk cos_id 分数表PK enrollment_id FK stu_id,cos_id B.插入数据code省略 C.Query select s.stu_id,stu_name,count(cos_id) from student s,enrollments e where s.stu_id = e.stu_id and e.gra...

5.1 select a.username,b.deptname from users a,dept b where a.dept_id=b.id; 5.2 update users set dept_id='9' where dept_id='2'; 5.3 select a.deptname,b.count_id from dept a,(select dept_id,count(id) as count_id from users group ...

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 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.姓名 ...

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 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='女'--字符类型...

对于附表,即使不存在对应于主表的数据,也不影响主表的检索结果。 比如说(简单举例):学生表(主表),选修课表(附表) 因为选修课学生可以选择,也可以不选择。没选择选修课的学生在选修课表中没有记录。当检索条件是外连接时,检索结果会...

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