数据库问题
1\select sname from s where not exists (select 1 from c, sc where sc.sno = s.sno and sc.cno = c.cno and c.cteacher = ‘liming’)2\select s.sname from sc,s wh…
数据库中有如下三个表:学生表(Sno、SName……),选课表(Sno、Cno、Score、……),课程表(Cno、
select s.sno, s.sname, avg(sc.score) avg_score from 学生表 s, 选课表 sc where s.sno=sc.sno group by s.sno,s.sname having count(1)>2 and avg(sc.score)>70 order by avg_score desc;
用sql语句将这个表中数据的行列转换变成 sno sname 语文 数学 英语 就是这个意思
select sno,sname sum(语文) as 语文,sum(数学) as 数学, sum(英语) as 英语 from (select sno ,sname ,case when course=’语文’ then grade else 0 end as 语文,case when course=’数学’ then grade else 0 end as 数学,case when course=’英语’ then grade else 0 end as 英语 from table ) aa group by aa.sno,aa.sname 注意把table 换成你的表名
2、已知关系模式:学生表S(Sno,Sname,Sage,Sdept),课程表C(Cno,Cname,Ccredit),学生选课表SC(Sno,Cno,sco
1.select sno from sc where cno=’003′ 2.select a.sno,b.cno,b.score from s a,sc b where a.sno=b.sno and a.sname=’李四’
S: sno, sname, age,sex,dept SC: sno, cno,grade C: cno,cname 用SQL实现以下功能:
(1)查询“张三”同学所学课程的成绩,列出sno,cno,grade.select sno,cno,grade from s,sc where sc.sno=sc.sno and sname=’张三’ (2)检索计算机系年龄20岁以上的同的学号和姓名 select sno,sname where dept=’计算机系’ and age>20 (3)统计…
sql解答有三个关系 S(sno,sname,age,sex,sdept) C(cno,cname,cdept,tname) tname
查询至少选修了2号课程和8号课程的学生姓名 select s.sname from s,sc where s.sno=sc.sno and sc.cno in (2,8) group by s.sname having count(*)>=2 查询张红的年龄 select age from s where sname=’张红’ 查询李明同学不及格的课程名称 select …
数据库SQL语言
1)select sno, snmae, sex, age,addr from s(2)select sname,sex,age from s where sex = ‘男’ and age between 19 and 21(3)insert into s (sno, snmae, sex, age,addr) values (‘N10′,’姓名’,’男’,21,’籍贯’)(4)delete from s where age >= 30(5)delete…
基于S表,查询1983 – 01 – 01以前出生的学生名单,要求输出SNO、SNAME字段,查询保存为“Q1”;
q1是什么,表?文件名?还是视图?如果是表那么就create table q1 select sno,sname from s where 出生时间 select s.name,avg(sc.grade) from s, sc where s.sno=sc.sno group by s.sno按照学号分组,算出每组的平均值,然后和学生姓名对应.(如果有两个人名字相同,因为按照学号分组所以也不会算在一起,而是分为两条数据 那就用union:select * from student where sname = ‘张三’ union all select * from student where sname = ‘李四’ union all select * from student where sname = ‘王庆’求SQL(要求用循环嵌套) S(SNO,SNAME) SC(SNO,GRADE) 找出各个学生的平均成绩,输出学生姓名和平均成绩
表student中字段sno,sname 的值有’01’,’张三’;’02’,’李四’;’03’,’刘枫’;’04’,’王午’;’05’,’赵子龙