sql questions

sql常用试题

一、试题一

现有关系数据库如下：
数据库名：我班同学数据库
同学表(学号 char(6), 姓名, 性别, 年龄, 民族, 身份证, 宿舍号)
宿舍表(宿舍号 char(6), 宿舍电话)
用SQL语言实现下列功能的sql语句代码：

1.创建数据库[我班同学数据库]代码

create databases 我班同学数据库;

2.创建数据表[宿舍表]代码

宿舍表(宿舍号 char(6), 宿舍电话)
要求使用：主键(宿舍号)、宿舍电话：以633开头的7位电话号码

create table 宿舍表(
宿舍号 char(6) primary key,
宿舍电话 char(7) check(宿舍电话 like '633[0-9][0-9][0-9][0-9]')
);

3.创建数据表[同学表]代码

同学表(学号 char(6), 姓名, 性别, 年龄, 民族, 身份证, 宿舍号)
要求使用：主键(学号)、外键(宿舍号)、默认(民族)、非空(民族、性别、年龄)、唯一(身份证号)、检查(性别)

create table 同学表(
学号 char(6) primary key,
姓名 char(4) not null,
性别 char(1) not null,
年龄 int,
民族 char(8) default '汉族' not null,
身份证 char(18) unique,
宿舍号 char(6) references 宿舍表(宿舍号)
);

4.将下列宿舍信息添加到宿舍表的代码

宿舍号 宿舍电话
101 6331157
102 6331777
修改宿舍号为101的宿舍电话：6331158
删除宿舍号为102的宿舍信息

insert into 宿舍表 values('101','6331157');
insert into 宿舍表 values('102','6331777');
update 宿舍表 set 宿舍电话='6331158' where 宿舍号='101';
delete from 宿舍表 where 宿舍号='102';

5.创建视图[同学表视图]代码

同学表视图(学号,姓名,性别,年龄,民族,身份证,宿舍号,宿舍电话)

create view 同学表视图 as
 select 学号,姓名,性别,年龄,民族,身份证,同学表.宿舍号,宿舍电话
 from 同学表,宿舍表
 where 同学表.宿舍号=宿舍表.宿舍号;

6.从同学表视图中查询性张的女同学的姓名、性别、宿舍电话

select 姓名,性别,宿舍电话 from 同学表视图 where 姓名 like '张%' and 性别='女';

7.从同学表中查询女同学的最大年龄、最小年龄、平均年龄

select max(年龄) as 最大年龄,min(年龄) as 最小年龄,avg(年龄) as 平均年龄 from 同学表 where 性别='女';

说明as可以省略

8.创建带参数的存储过程[某宿舍同学]：姓名,性别,宿舍电话

执行此过程，查询’101’宿舍的情况

create procedure 某宿舍同学 @宿舍号 varchar(6) as select 姓名,性别,宿舍电话 from 同学表视图 where 宿舍号=@宿舍号 go execute 某宿舍同学 '101';

9.设置-SQL身份验证的用户账户：

登录名：U班主任，密码：888
数据库用户名：U读者，权限：可查询本数据库中所有表、视图、内嵌表值函数的数据、执行所有的存储过程。
请写出账户、权限设置的T_SQL脚本

exec sp_addlogin 'U班主任','888','我班同学数据库';
exec sp_grantdbaccess 'U班主任','读者';
exce sp_addrolemember 'db_datareader','U读者'

二、试题2

现有关系数据库如下：
数据库名：学生成绩数据库
学生信息表(学号 char(6), 姓名, 性别, 民族, 身份证)
课程信息表(课号 char(6), 名称)
成绩信息表(ID,学号，课号,分数)
用SQL语言实现下列功能的sql语句代码：

1.创建数据库[学生成绩数据库]代码

create database 学生成绩数据库;
use 学生成绩数据库;

2.创建数据表[学生信息表]代码

学生信息表(学号 char(6), 姓名, 性别, 民族, 身份证)
要求使用：主键(学号)、默认(民族)、非空(民族、姓名)、唯一(身份证)、检查(性别)

create table 学生信息表(
学号 char(6) primary key,
姓名 char(4) not null,
性别 char(1) check(性别 in ('男','女')),
民族 char(8) default '汉族' not null,
身份证 char(18) unique);

3.创建数据表[课程信息表]代码

课程信息表(课号 char(6),名称)
要求使用：主键(课号)、非空(名称)

create table 课程信息表(
课号 char(6) primary key,
名称 char(20) not null
);

4.创建数据表[成绩信息表]代码

成绩信息表(ID,学号，课号,分数)
要求使用：外键(学号、课号)、检查(分数),自动编号(ID)

create table 成绩信息表(
ID int identity(1,1),
学号 char(6) references 学生信息表(学号),
课号 char(6) references 课程信息表(课号),
分数 int check(分数 between 0 and 100)
);

说明：
1.identity(1,1)该列自动增长，由1开始每次增加是1。
2.sql中integer的意思为“整数、整型数”，代表整数类型，包含负整数、零和正整数，允许介于“-32,768”到“32,767”之间的数字；可用于指定数据表中某一字段的数据类型，例“create table test (id integer);”。
3.CHECK 约束用于限制列中的值的范围。
如果对单个列定义 CHECK 约束，那么该列只允许特定的值。
如果对一个表定义 CHECK 约束，那么此约束会基于行中其他列的值在特定的列中对值进行限制。
4.BETWEEN 操作符选取介于两个值之间的数据范围内的值

5.蒋下列课程信息添加到课程信息表代码

课程名称
100101 西班牙语
100102 大学英语
修改课号为100102的课号名称：专业英语
删除课号为100101的课程信息

6.创建视图[成绩信息表视图]的代码

成绩信息表视图(学号,姓名,课号,课程名称,分数)

7.从学生信息表中查询性刘的女同学的情况：姓名、性别、民族

8.查询一门或一门以上课程成绩小于60分的所有学生信息，包括学号、姓名

9.创建带参数的存储过程[某门课程高低均分]、执行该过程的代码

存储过程功能、查询某门课程的最高分、最低分、平均分
执行改过程，查询所有修‘专业英语’这门学生的最高分、最低分、平均分

10.设置一SQL身份验证的用户账户

登入名：U院长，密码：888，
数据库用户名：U读者
权限：可查询本数据库中所有表、视图、内嵌表值函数的数据、执行所有的存储过程。
请写出账户、权限设置的T_SQL脚本