SQL语句汇总

一、  创建以下四个表

 

1）department 表（院系编号、院系名称），deptNo是主键。

create tabledepartment

(

deptNo smallintPRIMARY KEY not null,

deptName varchar(50)

);

2）student表（学号、姓名、年龄、性别、院系），sno是主键，deptNo是外键。

create tablestudent(

 deptsno char(8) PRIMARY KEY not null,

                       deptsname varchar(8),

                       deptsage smallint,

                       ssex char(2),

 deptNo smallint，

FOREIGN KEY(Cno)REFERENCES department(deptNo)

);

   3）course表（课程号、课程名、先修课、学分、院系编号），cno是主键。

 

Create table course(

cno char(4)PRIMARY KEY not null,

                       cname varchar(50,

                       pno char(4),

                       credit int,

deptNo smallint);

 

4）选课表（学号、课程号、成绩），学号和课程号联合做主键。

 

                     create table s

(

                  sno char(8) primary key,

                  cno char(4) primary key,

                  grade int not null

              );

 

二、  创建索引

1.创建Student的姓名字段的索引

create uniqueindex StuSname on Student(Sname);

2.创建SC表的课程号升序、成绩降序索引

create uniqueindex CouCno ON SC(Cno);

create uniqueindex Scno ON SC(Cno DESC);

三、  SQL查询

单表

1.  检索计算机系的学生。

 

select *

from d

where dname='计算机';

2.  检索年龄大于20岁的女学生的学号和姓名。

select sno,sname

from s

where age>20;

 

3.  检索姓名以王打头的所有学生的姓名和年龄。

 

select sname,age

from s

where snamelike'王%'

 

4.  查询姓名为‘X国X’同学。

 

select sname,age

from s

where snamelike'_国_';

 

5.  在SC中检索成绩为空值的学生学号和课程号。

 

select sno,cno

from sc

where gradeis null;

 

6.  输出c01课程的成绩单，要求成绩按从高到低排序。

 

select sno,grade

from sc

where cno='c01'

order by grade desc;

7.        求选修C04课程的学生的平均分。

 

select 'c04'cno,AVG(grade)avg

from sc

where cno='c04'

8.        每个学生选修的课程门数。

 

select sno,COUNT(cno)sumcno

from sc

group by sno;

 

9.  检索至少选修两门课程的学生学号。

 

select sno,COUNT(cno)sumcno

from sc

group by sno

having COUNT(cno)>2;

 

10.      求每个学生平均成绩。

 

select sno,AVG(grade)avg

from sc

group by sno;

 

11.统计每门课程的学生选修人数(超过3人的课程才统计)。查询结果按人数降序排列，若人数相同，按课程号升序排列。

 

select cno,COUNT(sno)sumcno

from sc

group by cno

having COUNT(sno)>3

order by COUNT(sno) desc,cno asc;

 

多表联合查询

12.检索每个学生的总学分，输出学号、姓名、院系和总学分，按院系排序。

 

select x.sno,x.sname,sum(x.credit)sumcredit,x.dname

from

(

    select s.sno,sname,s.dept,sc.cno,c.credit,d.dname

    from s,sc,c,d

    where s.sno=sc.sno and sc.cno=c.Cno and s.dept=d.dept

     ) x

group by x.sname,x.sno,x.dname

order by x.dname desc;

 

13.检索计算机系所开课程的课程号和课程名。

 

select cno,cname

from c,d

where c.dept=d.dept

     and  d.dname='计算机';

 

14.检索缺考2门以上的学生姓名。

 

select x.sno,x.sname,COUNT(x.sno)缺考

from

(

           select s.*,sc.grade

           from s,sc

           where s.sno=sc.sno

              and sc.grade is null

     ) x

group by x.sno,x.sname

having COUNT(x.sno)>2;

15.检索选修C语言且成绩不及格的计算机系的学生的姓名与学号。

 

select s.sno,s.sname

from s,d,sc,c

where s.dept=d.dept

    and s.sno=sc.sno

    and d.dept=s.dept

    and sc.cno=c.Cno

    and d.dname='计算机'

    and c.Cname='C语言'

    and(sc.grade<60 or sc.grade is null);

     

16.求计算机系所开课程的每门课程的学生平均成绩，按平均成绩从高到低排序输出。

 

select d.dname,c.Cname,avg(sc.grade)平均

from d,c,sc

group by c.Cno,d.dept,d.dname,sc.cno,c.dept,c.Cname

having d.dept=c.dept

    and sc.cno=c.Cno

    andd.dname='计算机';

        

17.检索所有学生及其所学课程的信息，即使没有选课，也要列出学生的信息。

 

select s.*,sd.*,sc.*,c.*,cd.*

from c,d sd,d cd,s left outer join sc on (s.sno=sc.sno)

where s.dept=sd.dept

    and sd.dept=s.dept

    and sc.cno=c.Cno

    andc.dept=cd.dept;

     

18.查询选修3门以上课程且平均成绩大于80的学生的学号

 

select sno,COUNT(sc.sno)课程数,AVG(sc.grade)平均分

from sc

group by sc.sno

having count(sc.sno)>3

     andavg(sc.grade)>80;

 

19.查询选修3门以上课程且平均成绩大于80的学生的学号、姓名、所在系名。

 

select s.sname,d.dname,x.*

from s,d,

(

         select sc.sno,COUNT(sc.sno)课程数,AVG(sc.grade)平均分

         from sc

         group by sc.sno

         having count(sc.sno)>3

             and avg(sc.grade)>80

         )x

where s.dept=d.dept

  ands.sno=x.sno;

        

20.检索王丽同学所学课程的课程号和课程名。

 

select s.sname,sc.cno,c.Cname

from s,sc,c

where s.sno=sc.sno

   and sc.cno=c.Cno

    ands.sname='王丽';

        

 

嵌套查询

21.检索选修‘c01’课程的女学生的姓名

 

select sname

from s

where sex='女'

    and sno in

    (

       select sno

       from sc

       where cno='c01'

    );

     

22.检索学号比王丽同学小，而年龄比她大的学生姓名

 

select sname

from s

where age >

    (

       select age

       from s

       where sname='王丽'

    )

    and sno<

    (   select sno

       from s

       where sname='王丽'

    );

     

23.检索s01同学不学的课程的课程号。

 

select c.Cno

from c

except

select sc.cno

from sc

     

24.检索王丽同学不学的课程的课程号。（in和exists两种方法）

 

select c.Cno

from c

where not exists

(

       select *

       from sc,s

       where sc.sno=s.sno

           and s.sname='王丽'

           and c.Cno=sc.cno

);

     

select c.Cno

from c

where c.Cno not in

(

       select sc.Cno

       from sc,s

       where sc.sno=s.sno

           and s.sname='王丽'

);

25.检索至少选修计算机系所开课程2门以上的女学生姓名

 

select s.sname

from s,d,sc

group by s.sname,s.dept,d.dept,s.sno,sc.sno,d.dname,s.sex

having s.dept=d.dept

  and s.sno=sc.sno

  and d.dname='计算机'

  and s.sex='女'

  andCOUNT(sc.cno)>2

      

 

26.查询没有选修c02，c03，c04的同学的学号。

 

select sno

from s

where not exists(

  select sno

  from sc

  where s.sno=sc.sno and sc.cno in('c02','c03','c04')

  );

     

27.查询既选修了‘数据库’又选修了‘操作系统’的学生的姓名及学号。

 

select s.sno,s.sname

from s,sc,c

where s.sno=sc.sno

    and sc.cno=c.Cno

    and c.Cname='数据库'

    and s.sno in

    (

       select s.sno

       from s,sc,c

       where c.Cname='操作系统'

           and sc.sno=s.sno

           and sc.cno=c.Cno

    );

     

 

 

select s.sno,s.sname

    from s,sc,c

    where s.sno=sc.sno

       and sc.cno=c.Cno

       and c.Cname='数据库'

intersect

    select s.sno,s.sname

    from s,sc,c

    where s.sno=sc.sno

       and sc.cno=c.Cno

       andc.Cname='操作系统';

28.查询即没有选修‘数据库’又没有选修‘操作系统’的学生的姓名及学号。（使用子查询和集合运算两种方法实现）

 

select s.sno,s.sname

from s,sc,c

where s.sno=sc.sno

    andsc.cno=c.Cno

    ands.sno not in

    (

       selects.sno

       froms,sc,c

       wherec.Cname='操作系统'

           ands.sno=sc.sno

           andsc.cno=c.Cno

           ors.sno in

           (

              select s.sno

              from sc,c,s

              where c.Cname='数据库'

                  and s.sno=sc.sno

                  and sc.cno=c.Cno

           )

    );

     

 

select s.sno,s.sname

from s,sc,c

where s.sno=sc.sno

  and sc.cno=c.Cno

except

( select s.sno,s.sname

  from s,sc,c

  where s.sno=sc.sno

     and sc.cno=c.Cno

     and c.Cname='数据库'

union

  select s.sno,s.sname

  from s,sc,c

  where s.sno=sc.sno

     and sc.cno=c.Cno

     and c.Cname='操作系统'

)

29.检索选修了全部课程的学生的学号与姓名。

 

select s.sno,s.sname

from s

where s.sno in

(

    select sno

    from sc

    group by sc.sno

    having COUNT(*)=

    (

       select COUNT(*)

       from c

    )

);

     

30.检索选修计算机系所开全部课程的学生的学号与姓名。

 

select s.sno,s.sname

from s

where not exists

(

  select c.Cno

  from c

  where c.dept=

  (

     select d.dept

     from d

     where d.dname='计算机'

  )

     and not exists

     (

         select *

         from sc

         where sc.cno=c.Cno

            and sc.sno=s.sno

     )

);

  

 

 

 

集合运算

31.检索计算机系同学与男同学的交集（即检索计算机系男同学，交运算）

 

select *

from s

where s.sex='男'

intersect

select *

from s

where s.dept in

    (

       select d.dept

       from d

       where d.dname='计算机'

    );

  

32.检索没有同学选修的课程号（差运算）

 

select c.Cno

from c

except

select sc.cno

from sc

     

 

33.检索选修了计算机系或者信息系所开课程的同学的学号，按学号降序排列（并运算）

 

select s.sno

from s

where s.dept in

    (

       select d.dept

       from d

       where d.dname='计算机'

    )

union

select s.sno

from s

where s.dept in

    (

       select d.dept

       from d

       where d.dname='信息系'

    )

order by sno desc;

 

34.检索缺考或不及格门数3门以上的学生学号和姓名。（并运算）

 

select s.sno,s.sname

from s

where s.sno in

    (

       select sc.sno

       from sc

       where sc.grade is null

       union

       select sc.sno

       from sc

       group by sc.sno,sc.grade

       having sc.grade<60

           and COUNT(sc.sno)>3

    );

     

35.查询s02同学没有选修的课程的课程号。(差运算)

 

select c.cno

from c

except

select sc.cno

from sc

where sc.sno='s02';

        

36.查询即没有选修‘数据库’又没有选修‘操作系统’的学生的姓名及学号。

 

select s.sno,s.sname

from s,sc,c

where s.sno=sc.sno

    andsc.cno=c.Cno

    ands.sno not in

    (

       selects.sno

       froms,sc,c

       wherec.Cname='操作系统'

           ands.sno=sc.sno

           andsc.cno=c.Cno

           ors.sno in

           (

              select s.sno

              from sc,c,s

              where c.Cname='数据库'

                  and s.sno=sc.sno

                  and sc.cno=c.Cno

           )

    );

     

 

 

37.查询没有选修c02，c03，c04的同学的学号。

 

select sno

from s

where not exists(

  select sno

  from sc

  where s.sno=sc.sno and sc.cno in('c02','c03','c04')

  );

     

 

38.查询即没有选修‘数据库’又没有选修‘操作系统’的学生的姓名及学号。

 

 

select s.sno,s.sname

from s,sc,c

where s.sno=sc.sno

  and sc.cno=c.Cno

except

( select s.sno,s.sname

  from s,sc,c

  where s.sno=sc.sno

     and sc.cno=c.Cno

     and c.Cname='数据库'

union

  select s.sno,s.sname

  from s,sc,c

  where s.sno=sc.sno

     and sc.cno=c.Cno

     and c.Cname='操作系统'

)

 

五、增删改

39.创建一个计算机系的学生表studentCS，并将student表中计算机系的同学记录插入新表中。

 

create table studentCS

    (

       sno char(9) primary key,

       sname char(20) unique,

       age int,

       sex char(2),

       dept char(20)

    )

 

insert intostudentCS

    select *

    from s

    where s.dept in

    (

       select d.dept

       from d

       where d.dname='计算机'

    )

 

 

 

     

 

40.更新选课表中数据库课程的成绩，根据公式：成绩*0.8 20。

 

update sc

set grade=grade*0.8 20;

 

41.删除成绩为空的记录。

 

delete

from sc

where grade is null;

 

六、创建视图：

42.建立计算机系的学生信息视图CS_Student。

 

create viewCS_student

as

select *

from s

where s.dept in

    (

       select d.dept

       from d

       where d.dname='计算机'

    );

 

 

43.建立计算机系平均成绩在85分以上的学生视图CS_Aver85,包含sno,dept,avergrade。

 

create viewCS_Aver85(sno,dept,avergrade)

as

select sc.sno,s.dept,AVG(grade)avergrade

from sc,s

group by sc.sno,s.dept

having AVG(grade)>85

    and sc.sno in

    (

       select s.sno

       from s

       where s.dept in

       (

           select d.dept

           from d

           where d.dname='计算机'

       )

    )

    and s.dept in

    (

       select s.dept

       from s

       where s.sname='计算机'

    )

 

 

44.在视图CS_Student中查询20岁以上的学生信息。

 

select *

from CS_student

where age>20;

 

 

 

45.将视图CS_Student中的朱岩同学的性别改为’女’

 

update CS_student

set CS_student.sex='女'

where CS_student.sname='朱岩';

     

46.向视图CS_Student中插入一名新生，学号为s20，姓名为李红，年龄为20岁，计算机系的女生。

 

insert

into CS_student(sno,sname,age,sex,dept)

values('s20','李红',20,'女',1);

     

47.删除视图CS_Student中李红同学的信息。

 

delete

from CS_student

where CS_student.sname='李红';

     

七、授权与回收

48.创建一个新用户User1（通过界面操作即可），授予他对student表的查询和修改的权限。

 

grant select,update

on s

to user1;

 

49.收回上题中授予User1的权限。

 

revoke select,update

on s

from user1;

 

八、CHECK约束

50.Department表中的deptNo字段的取值范围在1~20之间。

 

alter tabledepartment

    drop constraint c1;

alter tabledepartment

    addconstraint c1 check(deptNo between 1 and 20);

 

51.student表中的sex字段时可以是‘男’或者‘女’

 

alter tables

    drop constraint c4;

alter tabledepartment

    addconstraint c4 check(sex in('男','女'));

 

九、创建触发器

52.在department表中创建一个触发器，当插入新的记录时，检查院系名称是否存在，如果存在的话再进行插入。

 

create triggerchu_yuan

on department

for insert

as

begin

  declare yuanName char(20)

  set yuanName =

     (

         select deptName

         from inserted

     )

  if(yuanName is null)

     begin

         print '院系名不可为空'

         rollback transaction

     end

end;

 

53.在student表中增加一个字段avgGrade。(手动增加即可)。当sc表中进行增删改操作时都要重新计算平均成绩，写入student表的avgGrade字段。

 

 

create triggersc_grade

after insertor update on sc

referencing

  new table as avGrade

  for each statement

insert intoavgGrade(grade)

  select AVG(grade)

  fromsc;