数据库sql练习

图书表Book
列名 数据类型 约束 描述
ID CHAR(10) 主码 图书编号
name VARCHAR(30) 非空 图书的名称
author VARCHAR(10) 图书作者
publish VARCHAR(20) 默认值“东软出版社” 出版社
price DECIMAL(6,2) 大于0 定价
Reader
列名 数据类型 约束 描述
ID CHAR(10) 主码 读者编号
name VARCHAR(8) 非空 读者姓名
sex CHAR(2) 取值“男”或“女” 读者性别
birthdate DATETIME 读者的出生日期
Borrow
列名 数据类型 约束 描述
bookID CHAR(10) 主码；外码；/*78参照Book表中的book_ID 图书编号
readerID CHAR(10) 主码：外码；参照Reader表中reader_ID 读者编号
borrowdate DATETIME 借阅日期

(1) 写出创建Book表的SQL语句。
Create table Book (
ID char(10) primary key ,
Name varchar(30) not null,
Author varchar(10) ,
Publish varchar(20) default ‘东软出版社’,
Price decimal(6,2) check(price>0)
)
(2) 向Book表添加记录（‘A12DT90008’，‘专业外语’，‘冯刚’，‘东软电子出版社’，42）
Insert into Book values(‘A12DT90008’，‘专业外语’，‘冯刚’，‘东软电子出版社’，42)
(3) 更新Book表，将“东软电子出版社”出版的图书价格涨价10%。
Update book set price=price110% where publish=’ 东软电子出版社’
(4) 查询所有读者基本信息，按读者编号升序排列。
Select * from reader order by id asc
(5) 查找图书价格在20至50之间的图书信息。
Select * from book where price between 20 and 50
(6) 查询出版图书数量最多的出版社名称。
Select publish from book group by publish having count()>=all(
select count(*) from book group by publish)
(7) 在Book表的定价列Price上创建一个名为IdxPrice的降序索引。
Create index idxPrice on Book(price desc)
(8) 创建视图ViewAll，通过此视图可以查看每个读者的编号、读者姓名、所借图书编号、书名和对应借阅日期。
Create view ViewAll(读者编号, 读者姓名, 所借书号, 书名, 借阅日期)
As
Select reader.ID, reader.name, book.ID, book.name , borrowdate
From reader join borrow on reader.ID=borrow.readerID join Book on Book.ID=borrow.bookID
(9) 通过上面创建的视图ViewAll查询读者“胡晓丽”借阅“数据库原理”的借阅日期。
Select 借阅日期 from ViewAll
where读者姓名=‘胡晓丽’ and书名=‘数据库原理’
（10）查询读者王旭借阅的图书书号和借阅日期
SELECT Borrow.bookID,Borrow.borrowdate FROM Borrow JOIN Book ON Book.ID=Borrow.bookID JOIN Reader ON Reader.ID=Borrow.readerID
WHERE Reader.name=’王旭’

2、设有关系职工表（职工号，职工名，部门号，工资）和
部门表（部门号，部门名，主任），其中职工表的主码是职工号，部门表的主码是部门号，
职工表的部门号是外码，参照部门表的主码部门号。用SQL或TSQL完成下列要求：
（1）根据如下要求创建职工表。
职工号：3位字符串，主码；
职工名：8为字符串，非空；
部门号：2位字符串，外码，参照部门表的主码部门号；
工资：decimal（7，2），大于0.
CREATE TABLE
职工(职工号char(3) PRIMARY KEY,
职工名 CHAR(8) NOT NULL,
部门号CHAR(2) FOREIGN KEY(部门号) REFERENCES 部门(部门号),
工资 decimal(7,2) CHECK(工资>0))
（2）向职工表中插入行（’025’,’王芳’,’03’,1000）
Insert into 职工(职工号，职工名，部门号，工资) values(‘025’，‘王芳’，‘03’，1000)
（3）从职工表中删除部门名为“人力资源管理部门”的所有员工
Delete from 职工 where 部门号 =（select 部门号from部门where 部门名=‘人力资源管理部门’）
（4）将职工号为‘001’的员工工资改为700元钱
Update 职工set 工资=700 where 职工号=‘001’
（5）查询所有员工的最高工资
SELECT MAX（工资） FROM 职工
（6）查询所有员工信息，结果先按部门号升序排列，在按职工号降序排列
SELECT * FROM 职工 ORDER BY部门号ASC，职工号DESC
（7）查询员工“王芳”所在部门的部门主任
SELECT 主任 FROM 部门 WHERE 部门号=（SELECT 部门号 FROM 职工 WHERE 职工名=‘王芳’）
（8）建立公司所有部门的公共视图ViewAll，其中包含部门号、部门名、职工号、职工名和工资信息
CREATE VIEW ViewAll AS SELECE 职工.部门号,部门名,职工号,职工名,工资 FROM 职工 JOIN 部门 ON 职工.部门号=部门.部门号
（9）从部门视图ViewAll中查询财务处员工工资高于800元的员工信息
SELECT * FROM ViewAll WHERE 部门名=‘财务处’ and 工资>800
（10）授予用户“manage01”向职工表插入记录的权限
Grant insert on 职工 to manage01

3、表一：学生表STUDENT
列名 数据类型 约束 描述
Sno CHAR(10) 主码 学号
Sname VARCHAR(10) 非空 姓名
Sage Int 15-40岁之间 年龄
Cno Char(10) 外码，参照CLASS的主码Cno 班号

表二：班级表CLASS
列名 数据类型 约束 描述
Cno CHAR(10) 主码 班号
Specialty VARCHAR(30) 专业名
Cname VACHAR(30) 班级名
Dno CHAR(10) 外码，参照DEPARTMENTN表中的主码Dno 所属系别
EnrollDate DATETIME 入学年份
表三：系别表：DEPARTMENT
列名 数据类型 约束 描述
Dno CHAR(10) 主码 系别
Dname VARCHAR(30) 系名

1、按照表1创建班级表Student
CREATE TABLE Student(
Sno CHAR(10) PRIMARY KEY,
Sname VARCHAR(10) NOT NULL,
Sage Int CHECK （Sage>15 and Sage<40）
Cno Char(10),
FOREIGN KEY(Cno) REFERENCES CLASS(Cno) )
2、使用SQL语句实现学校新建立一个嵌入工程系，编号为123
INSERT INTO DEPARTMENT VALUES(‘123’,’嵌入工程系’)
3、删除“计算机系”的“通信工程”专业的所有班级
DELETE FROM CLASS FROM CLASS JOIN PEPARTMENT ON CLASS.DNO=DAPEARTMENT.DNO WHERE DNAME=’计算机系’AND SPECIALITY’通信工程’
4、请查询该学校所有的专业名称。
SELECT DISTINCT Specialty FROM CLASS
5、请查询所有名字中含“晓”字的学生，并按其年龄由大到小排序。
SELECT Sname FROM STUDENT WHERE Sname LIKE ‘%晓%’ORDER BY Sage ASC
6、列出平均年龄超过20的所有班级的编号。
SELECT Cno FROM STUDENT GROUP BY Cno HAVING AVG(Cno)>20
7、查询学生“许小莹”的所在系
SELECT Dname FROM DEPARTMENT WHERE Dno IN(SELECT Dno FROM CLASS WHERE Cno IN(SELECT Cno FROM STUDENT WHERE Sname=’许小莹’))
8、收回用户user1对学生表的查询和更新权限
REVOKE SELECT,UPDATE FROM user1
9、创建所有学生信息视图stuView，列出学生的学号、姓名、班号、班级名、专业名、系名和入学年份。
CREATE VIEW stuView(学号,姓名,班号,班级名,专业名,系名,入学年份) AS
STUDENT.Sno,STUDENT.Sname,STUDENT.Cno,CLASS.Cname,CLASS.Specialty,DEPARTMENT.Dname,CLASS.EnrollDate FROM STUDENT JOIN CLASS ON STUDENT.Cno =CLASS.Cno JOIN DEPARTMENT ON CLASS.Dno=DEPARTMENT.Dno
10、查询视图stuView,查询2017年入学的所有学生学号、姓名、班级号和系名
SELECT 学号,姓名,班级号,系名 FROM stuView

4. 请根据如下四张表做后面的题目：
   学生表Student（Sno，Name，Sex，Birthday，Class）
   教师表Teacher（Tno，Name，Sex，Birthday，Prof，Depart）
   课程表Course（Cno，CName，Tno）
   成绩表Score（Sno，Cno，Grade）
   其中：
   Student表中Sno是主码，Teacher表中Tno是主码，
   Course表中Cno是主码，Score表中Sno是Student表的外码，
   Cno是Course表的外码。
   （1）向Student表添加Depart列，其数据类型为字符串型，最大长度30位。
   Alter table Student
   Add Depart char(30)
   （2）向Teacher表插入一条记录（010125，刘冰，女，1985-06-25，助教，电子工程系）。
   Insert into Teacher(Tno，Name，Sex，Birthday，Prof，Depart)
   Values(‘010125’，’刘冰’，’女’，’1985-06-25’，’助教’，’电子工程系’)
   （3）将Student表中学号为“209”的学生班号改为“95031”。
   Update Student set Class=’95031’
   Where Sno=’209’
   （4）删除选修刘冰教师所教课程的学生选修信息。
   DELECT FROM SCORE FROM SCORE JOIN COURSE ON SCORE.CON=COURSE.CNO JOIN TEACHER ON COURSE.TNO=TEACHER.TNO WHERE TEACHER.NAME=’刘冰’
   （5）显示student表中95031班女同学的记录
   Select * from Student Where class=‘95031’
   （6）显示课程号“C01“课程的平均分
   Select avg（Grade） from Score Where Cno=’ C01’
   (7)查询平均成绩高于75分的课程号和平均成绩
   Select Cno，avg（Grade）from Score GROUP BY CNO HAVING AVG(GRADE)>75
   (8)建立一个包含所有学生sno，name，cname，grade和class字段的视图degrees
   Create view degrees（sno，name，cname，grade，class）
   As
   Select sno，name，cname，grade，class from Student join Course on Student. Sno =
   Course. Sno join Course on Course.cno=Score.cno
   (9)根据上题所建视图，查询‘95031‘班学生选课信息
   Select * from degrees Where class=’95031’
   (10)在成绩表上创建索引idxGrade，按课程号升序排列
   Create index idxGrade on Score(Cno ASC)

5 、学生表（表名 S）
字段名 数据类型 字段宽度 约束条件 含义
sno CHAR 10 主码 学生编号
sname VARCHAR 10 非空 学生姓名
sex CHAR 2 只能是“男”或“女” 性别
specialty VARCHAR 20 专业名称
schiarship INT 奖学金

课程表（表名 C）
字段名 数据类型 字段宽度 约束条件 含义
cno CHAR 10 主码 课程编号
cname VARCHAR 20 唯一 课程名称
credit INT 学分

选课表（表名 SC）
字段名 数据类型 字段宽度 约束条件 含义
sno CHAR 10 外键，参照学生表中的学生编号 (sno,cno)是联合主码
cno CHAR 10 外键，参照课程表中的课程编号
grade INT 0-100之间的整数 分数
1.写出创建S表的SQL语句
CREATE TABLE S(
Sno char(10) primary key,
Sname varchar(10) not null,
Sex char(2) check （sex=’男’or sex=’女’），
Specialty varchar(20),
Scholarship int)

2.将新生记录（0621105，张三，男，软件工程）插入到S表中
Insert into S(sno,sname,sex,specialty) Values(‘0621105’, ‘张三’，’男’，’软件工程’)
3.将“软件测试”专业所有学生的奖学金设置为500
Update S set scholarship=500 where specialty=’软件测试’
4.删除选修“数据库原理”课程学生的选课信息
Delect from SC where cname=” 数据库原理”
5.查询姓“王”学生的信息，列出学号，姓名，性别，专业
Select sno,sname,sex,specialty from S where name like “王%”
6.查看软件工程专业学生的最高奖学金
Select MAX(scholarship) from S where specialty=”软件工程”
7.在S表的scholarship列上创建一个名为SIdx的降序索引
Create index SIdx on S (scholarship DESC)
8.授予用户user1对S表的查询权限和插入权限
Grant select,insert on S to user1
9.创建视图V，通过此视图可以查看各个专业学生的平均成绩，要求列出专业与平均成绩
Create view V
As select Avg(grade),specialty from S group by specialty
10.根据上题创建的视图V，查询“电子工程专业”学生的平均成绩
Select Avg(grade) from S where specialty=”电子工程专业”