sql小测

create table student(
    sno char(6) primary key,
	sname varchar(50) not null,
	sex varchar(2) check(sex in('男','女')),
	dept varchar(20) check(dept in('信息系','计算机科学系','数学系','管理系','中文系','外语系','法学系')),
	birth datetime,
	age int check(age between 0 and 100)
)

create table cs(
   sno char(6),
   cno int,
   cj int check(cj between 0 and 100),
   primary key(sno,cno),
   foreign key(sno) references student(sno),
   foreign key(cno) references course(cno)
)

create table course(
    cno int primary key,
	cname varchar(20) not null ,
	credit int
)

--(1)查询全体学生的姓名、学号、所在系，并用别名显示出结果
select  sname as 姓名, sno as 学号, dept as 所在系
from   student
--(2)查询信息系、数学系和计算机科学系学生的姓名和性别。
select sname, sex
from  student
where  (dept in ('信息系', '数学系', '计算机科学系'))
--(3)查询所有姓刘学生的姓名、学号和性别。
select  sname, sno, sex
from    student
where   (sname like '刘%');
--(4)查询以"DB_"开头，且倒数第3个字符为 i的课程的详细情况。
select  cno, cname, credit
from   course
where   (cname LIKE 'DB\_%i__' ESCAPE ''\'); 
--(5)查询每个学生选修课程的总学分。
select * from student,cs ,course where student.sno=cs.sno and cs.cno=course.cno
select student.sno,sum(credit) 
from student,cs ,course 
where student.sno=cs.sno and cs.cno=course.cno
group by student.sno
--(6)查询每个学生的学号、姓名、选修的课程名及成绩。
select student.sno,student.sname,course.cname,cj 
from student,cs,course 
where student.sno=cs.sno and cs.cno=course.cno

--(7) 查询所有选修了1号课程的学生姓名。（分别用嵌套查询和连查询）
--嵌套查询
select sname 
from student 
where sno in(
     select sno 
	 from cs 
	 where cno=1)
--连接查询
select sname 
from student,cs
where student.sno=cs.sno and cs.cno=1
--(8)创建一个视图View_s，通过这个视图找到既选修了课程1又选修了课程2的学生的信息。
drop view View_s
create view View_s
as
select student.* 
from student,cs
where student.sno in(
     select cs.sno 
	 from cs
     where cs.cno=1 and sno in (
           select sno 
		   from cs 
		   where cs.cno=2)
       )

--(9)查询平均成绩大于85的学生学号及平均成绩。
select  sno, AVG(cj) as Expr1
from   cs
group by sno
having  (AVG(cj) > 85)

--(10)要求查寻学生的所有信息，并且查询的信息按照年龄由高到低排序，如果年龄相等，则按照学号从低到高排序
select * 
from student 
order by age desc,sno asc
--(11)向student(学生表)中插入一条数据：姓名华晨宇，学号955008，性别为男，系别为管理系，出生日期为1990/5/5，年龄为30。
insert into student values('华晨宇','955088','男','管理系','1990/5/5',30)
--(12)删除cs表中成绩为空的信息。
delete  
from cs 
where cj is null
--(13)新建一个登录名dll，并创建一个用户dll，并将student表的查询、插入和删除的权限授予dll。
create login dll
with password ='abc'
create user dll for login dll
grant select, insert, delete
on  student 
to dll;
--(14)回收dll的student的删除权限。
revoke delete 
on student 
from dll
--(15)在course表中的credit字段增加一个constraint约束，约束名字为C_credit,要求credit字段的取值为1-5之间的整数。
alter table course 
add constraint C_credit check(credit >=1 and credit<=5)