以文本方式查看主题

-  课外天地 李树青  (http://www.njcie.com/bbs/index.asp)
--  数据库系统原理课件  (http://www.njcie.com/bbs/list.asp?boardid=19)
----  关于第三次作业的说明（SQL语句）  (http://www.njcie.com/bbs/dispbbs.asp?boardid=19&id=609)
--  作者：admin
--  发布时间：2008/11/22 7:14:12
--  关于第三次作业的说明（SQL语句）

1、查询学生姓名和年龄

select name,extract(year from sysdate)-extract(year from birthday) as age from student

2、查询平均分大于85分的学生姓名

select min(name),round(avg(grade),1) from student inner join grade on student."NUMBER"=grade."NUMBER" group by student."NUMBER" having avg(grade)>85

3、利用自动生成的序列数字来给学号主键赋值

create sequence count start with 10 increment by 2 maxvalue 2000 nocycle cache 20;

create table stu ( sid integer primary key, name varchar(8) not null, sex char(1) check(sex in(\'M\',\'F\')) );

insert into stu values(count.nextval,\'Tom\',\'M\');

select * from stu;

[此贴子已经被作者于2010-12-11 20:04:26编辑过]