欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

SQL复杂查询

程序员文章站 2022-03-15 11:44:59
...

1.学生关系是S(S#,SN.,SEX,AGE),课程关系是C(C#,CNAME,TEACHER),学生选课关系是SC(S#,C#,GRADE),

查询选修课程C#为"C2”的学生中成绩最高的学生的学号

答:select S# from SC where C#='C2' and GRADE = (select max(GRADE) from SC where C#='C2');

OR

  select S# from SC where C#='C2' and GRADE>= all(select GRADE from SC where C#='C2');

 

 

2.给两张表t1和t2,t1有列id,name,t2有列id,name,log,t1表中id=10的name更新了,怎么把t1中id为10的那行name值更新到t2表中id为10的name中?(t1和t2的id有关联)

答:update t1,t2 set t2.name=t1.name where t1.id=t2.id and t1.id=10; 

 

 

3.两张表,店铺表shop(s_id,sname,loc)和订单表ordertable(o_id,oname,money,s_id),店铺和订单是一对多关系,查询订单数大于等于20的店铺.

答:select s.s_id,s.sname from shop s inner join ordertable o on s.s_id = o.s_id group by s.s_id,s.sname having count(o.o_id)>=20;

 

4.店铺表shop(s_id,sname,loc),查询店铺名字段重复的记录.

答:select sname,count(s_id) as 重复次数 from shop group by sname having count(sname)>1

 

5.查询没有订单的店铺.

答:select * from shop where s_id <> all(select s_id from ordertable ); 

  OR 

 SELECT s.* FROM shop s left join ordertable o on s.s_id=o.s_id where o.o_id is null

91 fx:/明倩艺4馨业燕5静尢麸嬅支付3宝领