当前位置:文档之家› 数据库常用函数汇总统计

数据库常用函数汇总统计

实验二(续):利用SQL语句查询三、常用库函数及统计汇总查询1、求学号为 S1学生的总分和平均分;select sum(score) as TotalScore,avg(score)as AveScorefrom scwhere sno='S1'2、求选修 C1号课程的最高分、最低分及之间相差的分数;select max(score)as MaxScore,min(score)as MinScore, max(score)- min(score)as difffrom scwhere cno='C1'3、求选修 C1号课程的学生人数和最高分;select count(distinct sno),max(score)from scwhere cno='C 1'4、求计算机系学生的总数;select count(sno)from swhere dept=' 计算机 '5、求学校中共有多少个系;select count(distinct dept) as DeptNumfrom s6、统计有成绩同学的人数;select count(score)from sc7、利用特殊函数 COUNT(*)求计算机系学生的总数;select count(*)from swhere dept=' 计算机 '8、利用特殊函数 COUNT(*)求女学生总数和平均年龄;select count(*),avg(age)from swhere sex=' 女 '9、利用特殊函数 COUNT(*)求计算机系女教师的总数。

select count(*)from twhere dept=' 计算机 'and sex=' 女 '四、分组查询及排序1、查询各个教师的教师号及其任课门数;select tno,count(*)as c_numfrom tcgroup by tno2、按系统计女教师的人数;select dept,count(tno)from twhere sex=' 女 'group by dept3、查询选修两门以上课程的学生的学号和选课门数;select sno,count(*)as sc_numfrom scgroup by snohaving count(*)>24、查询平均成绩大于 70分的课程号和平均成绩;select cno,avg(score)from scgroup by cnohaving avg(score)>705、查询选修 C1的学生学号和成绩,并按成绩降序排列;select sno,scorefrom scwhere cno='01'order by score desc6、查询选修 C2、C3、C4或C5课程的学号、课程号和成绩,查询结果按学号升序排列,学号相同再按成绩降序排列;select sno,cno,scorefrom scwhere cno in('C2','C3','C4','C5')order by sno ,score desc7、求有三门以上选课成绩及格的学生的学号及其总成绩,查询结果按总成绩降序列出。

select sno,sum(score)as TotalScorefrom scwhere score>=60group by snohaving count(*)>=3order by sum(score) desc[注意]:1、函数 SUM和A VG只能对数值型字段进行计算;2、COUNT函数对空值不计算,但对零值计算;3、COUNT(*)用来统计元组的个数,不消除重复行,不允许使用DISTINCT关键字;4、WHERE子句作用于基本表或视图,从中选择满足条件的元组,HA VING子句作用于组,选择满足条件的组,必须用于GROUP BY子句之后.在 D 盘建立以自己的班级 _ 姓名 _ 学号为名称的文件夹,用来保存数据库以及相关的 SQL 语句。

先创建教学数据库 student 中包含六个基本表:学生情况基本表 s (sno,sname,sex,age,dept)教师情况基本表 t (tno,tname,sex,age,prof,sal,comm,dept)课程基本表 c (cno,cname,ct)选课基本表 sc (sno,cno,score)授课基本表 tc (tno,cno)系别基本表 d (dept,addr)[步骤]:用 SQL语句完成如下操作:一、创建学生表1、创建一个学生表 s1 ,对 sno 字段进行 not null 约束;create table s1(sno char(10) constraint sno_cons not null,sname varchar(10),sex char(2),age int,dept varchar(30))2、创建一个学生表 s2 ,定义 sname 为唯一键;create table s2(sno char(10),sname char(10) constraint sname_uniq unique,sex char(2),age int,dept varchar(30))3、创建一个学生表 s3 ,定义 sname+sex 为唯一键;create table s3(sno char(10),sname char(10),sex char(2),age int,dept varchar(30),constraint s3_uniq unique(sname,sex))4、创建一个学生表 s4 ,定义 sno 为主键;create table s4sname char(10),sex char(2),age int,dept varchar(30))5、创建一个学生表 s5 ,定义 age 的取值范围为 15~50 ;create table s5(sno char(10),sname char(10),sex char(2),age int constraint age_chk check(age between 15 and 50),dept varchar(30))6、创建一个学生表 s6 ,使 sex 列的缺省值为“男”;create table s6(sno char(10),sname char(10),sex char(2) default …男 ',age int,dept varchar(30))7、创建一个学生表 s7 ,定义 sno 为主键, sname 为唯一键, age 的取值范围为 15~50 , sex 列的缺省值为“男”, dept 字段进行 not null 约束;create table s7(sno char(10) constraint sno_prim primary key,sname char(10) constraint sname_uniq unique,sex char(2) default …男 ',age int constraint age_chk check(age between 15 and 50),dept varchar(30) constraint dept_cons not null)8、创建一个学生表 s ,定义 sno 为主键,对 sname 字段进行 not null 约束。

create table s(sno char(10) constraint sno_prim primary key,sex char(2),age int,dept varchar(30))二、创建教师表创建一个教师表 t ,定义 tno 为主键,对 tname 字段进行 not null 约束。

Create table t(tno char(8) constraint tno_prim primary key,tname char(10) not null,sex char(2),age int,prof char(10),sal numeric(6),comm numeric(6),dept varchar(30))三、创建课程表创建一个课程表 c ,定义 cno 为主键,对 cname 字段进行 not null 约束。

Create table c(cno char(8) constraint cno_prim primary key,cname char(20) not null,ct int)四、创建选课表创建一个选课表 sc ,定义 sno+cno 为主键,定义 sno , cno 为外部键。

Create table sc(sno char(10) not null constraint s_fore foreign keyreferences s(sno),cno char(8) not null constraint c_fore foreign keyreferences c(cno),score numeric(3),constraint sc_prim primary key(sno,cno))五、创建授课表创建一个授课表 tc ,定义 tno+cno 为主键。

Create table tc(tno char(8),cno char(8),constraint tc_prim primary key(tno,cno))六、创建系别表创建一个系别表 d ,定义 dept 为主键。

Create table d(dept varchar(30) constraint dept_prim primary key, addr varchar(40))。

相关主题