SQL语句练习.doc

SQL语句练习一、以下是一个学生选课系统的数据库，包括三张表，分别为：学生表、课程表、选课表，表结构如下所示：学生字段名称数据类型长度备注学号char10主键姓名varchar50性别char2出生日期datetime课程字段名称数据类型长度备注课号char4主键课程名varchar100学分int选课字段名称数据类型长度备注学号char10主键课号char4成绩int针对以上两表，用SQL语句完成以下操作。1、 用SQL语句创建以上数据表，要求考虑主键和外键。2、 用insert语句将下列信息插入到学生表中。学号姓名性别出生日期2012001001王五男1982-1-233、 将课号为“1001”的课程学分改为4。4、 删除学号为“2012001001”的学生信息。5、 查询出生日期为“1985-1-1”以后出生的学生信息。6、 查询选修了“数据库”课程的学生信息，显示学号，姓名。(使用子查询)7、 查询“数据库”课程的选课情况，显示学号、姓名、课程名、学分、成绩。8、 查询选修了“数据库”课程的学生人数。9、 查询学号为“2012001001”学生的选课成绩，显示结果按成绩降序排列，显示学号、姓名、课程名、成绩。10、 查询姓“李”的学生的学号和姓名。11、 查询每门课的选课人数，显示课号、课程名、人数。（注意没有人选的课程也要显示）12、 查询选课人数在3人以上课程的平均成绩。13、 查询年龄小于21周岁的学生信息。参考答案：1、create table 学生 ( 学号 char(10) primary key, 姓名 varchar(50), 性别 char(2) 出生日期 datetime )create table 课程 (课号 char(4) primary key, 课程名 varchar(100), 学分 int)create table 选课(学号 char(10) references 学生(学号), 课号 char(4) references 课程(课号), 成绩 int, primary key（学号,课号)2、insert into 学生 values(2012001001,王五,男,1982-1-23)3、update 课程 set 学分=4 where 课号=10014、delete from 学生 where 学号=20120010015、select * from 学生 where 出生日期=1985-1-16、select 学号,姓名 from 学生 where 学号 in (select 学号 from 选课 where 课号 in (select 课号 from 课程 where 课程名=数据库)7、select 学生.学号,姓名，课程名，学分，成绩 from 学生 inner join 选课 on 学生.学号=选课.学号 inner join 课程 on 选课.课号=课程.课号8、select count(*) from 选课 where 课号 in (select 课号 from 课程 where 课程名=数据库)9、select 学生.学号,姓名,课程名，成绩 from 学生 inner join 选课 on 学生.学号=选课.学号 inner join 课程 on 选课.课号=课程.课号 where 学号=2012001001 order by 成绩 desc10、select 学号 ，姓名 from 学生 where 姓名 like 李%11、select 课程.课号,课程名,count(学号) as 人数 from 课程 left outer join 选课 on 选课.课号=课程.课号 group by课程.课号,课程名12、select 课号，avg(成绩) from 课程 group by 课号 having count(*)=313、select * from 学生 where datediff(yy,出生日期,getdate()21二、已知商品表、订单表、订单细节表、顾客表的结构如下所示：商品字段名称数据类型长度备注商品编号char5主键商品名称varchar50单价money库存数量int顾客字段名称数据类型长度备注顾客编号char5主键顾客姓名varchar50订单字段名称数据类型长度备注订单编号char10主键下单日期date顾客编号Char5订单细节字段名称数据类型长度备注订单编号char10主键商品编号char5数量int针对以上两表，用SQL语句完成以下操作。1、 用SQL语句创建以上数据表，要求考虑主键和外键。2、 用insert语句将下列信息插入到学生表中。 商品编号商品名称定价库存数量10001电视机2000203、 将编号为“10001”的商品库存数量减3。4、 删除编号为“10001”的商品信息。5、 查询2012-12-1日的订单信息，显示：订单编号、下单日期、顾客姓名。6、 查询商品名称中包括“电”的商品信息。7、 查询订购了“10001”号商品的所有订单，显示：订单编号、下单日期。按下单日期降序排列8、 查询每个订单的商品总金额，显示：订单编号，下单日期，总金额。9、 查询没有任何订单的商品信息。10、 查询每种商品的订单数量，没有订单的也要显示。显示：商品编号、商品名称，订单数量参考答案：1、create table 商品 (商品编号 char(5) primary key,商品名称 varchar(50),单价 money,库存数量 int)create table 顾客 (顾客编号 char(5) primary key, 顾客姓名 varchar(50)create table 订单 (订单编号 char(10) primary key, 下单日期 date, 顾客编号 char(5) references 顾客(顾客编号)create table 订单细节 (订单编号 char(10) references 订单(订单编号), 商品编号 char(5) references 商品(商品编号), 数量 int)2、insert into 学生 values(10001,电视机,2000,20)3、update 商品 set 库存数量=库存数量-3 where 商品编号=100014、delete from 商品 where 商品编号=100015、select 订单编号，下单日期，顾客姓名 from 订单 inner join 顾客 on 顾客.顾客编号=订单.顾客编号 where 下单日期=2012-12-16、select * from 商品 where 商品名称 like %电%7、select 订单编号，下单日期 from 订单 where 订单编号=10001 order by 下单日期 desc8、select 订单编号，下单日期,sum(单价*数量) as 总金额 from 订单 inner join 商品 on 订单.商品编号=商品.商品编号 group by 订单编号,下单日期9、select * from 商品 where 商品编号not in (select 商品编号 from 订单)10、select 商品.商品编号，商品名称，count(distinct 订单编号) as 订单数量 from 商品 left outer join 订单细节 on 订单细节.商品编号=商品.商品编号 group by 商品.商品编号，商品名称三、已知一个图书借阅馆理系统的E-R图如下：根据以上E-R图完成以下操作：(各字段数据类型自行定义)1、 查询每本图书的馆藏数量，显示的字段有：图书编号、书名、出版社，馆藏数量。馆藏数量从图书财产中计算得出。2、 查询超期的图书，显示的字段有：序号、条码号、书名、作者、出版社、借阅日期、应还日期。(使用getdate()函数获取当前系统日期)3、 查询书名中包含“数据库”三字的图书，显示的字段有：图书编号、书名、作者、出版社。4、 查询卡号为“87910”的借阅情况，显示的字段有：卡号、条码号、书名、作者、出版社、借阅日期、应还日期。并按借阅日期降序排列。5、 查询日期范围从2008年1月1日至今没有借阅记录的图书。显示的信息包括图书编号、书名、出版社。(注：借阅记录在“借阅”表和“历史借阅”表中) 6、 建立一个视图，显示有超期图书的借阅卡。显示的信息有：卡号、姓名、卡类型名称、超期册数。7、 编写一个存储过程实现借阅操作。存储过程名称：borrow存储过程输入参数：ReaderID，CodeID (注：ReaderID为卡号，CodeID为条码号)存储过程返回值：借阅成功返回0，未成功返回-1。存储过程实现的功能：（1） 检查卡号和条码号的合法性（数据库中存不存在），不存在返回-1。（2） 计算可借阅天数（数据在借阅期限表的可借天数字段中）。将借阅信息存入借阅表。借阅日期=系统当前日期；应还日期=借阅日期+可借阅天数（获取系统日期函数getdate()，在指定日期上增加天数函数DATEADD ( day , 可借阅天数,借阅日期 ) )8、 当读者还书时，将删除借阅表中的数据，但要将借阅信息存入“历史借阅”表中。请编写一个触发器，当在借阅表中执行删除操作时，自动将删除的数据存入“历史借阅”表。（历史借阅表的序号字段是自动增长的，存入数据时不用考虑）9、 注销借阅卡时将删除借阅卡信息和历史借阅信息，请编写一个存储过程，完成注销借阅卡功能。存储过程名称：remove存储过程参数：ReaderID (注：readerid为卡号)存储过程返回值：执行成功返回0，否则返回-1实现功能：（1） 查询借阅表中是否有记录，有则返回-1。（2） 使用事务将借阅卡的历史借阅信息（历史借阅表中）和借阅卡信息删除。参考答案：1、 Select 图书.图书编号, 书名,出版社,count(条码号) from 图书 left outer join 图书财产 on 图书.图书编号=图书财产.图书编号 group by图书.图书编号, 书名,出版社2、 Select 序号，图书财产.条码号,书名,出版社,借阅日期,应还日期 from 借阅 inner join 图书财产 on 借阅.条码号=图书财产.条码号 inner join 图书 on 图书财产.图书编号=图书.图书编号 where 应还日期=2008-1-1and 借阅日期=2008-1-1 and 借阅日期=getdate() )6、 create view myviewasselect 借阅卡.卡号, 姓名,卡类型名称,count(条码号) as 超期册数 from 借阅 inner join 借阅卡 on 借阅卡.卡号=借阅.卡号 inner join 借阅卡类型 on 借阅卡类型.卡类型编号=借阅卡.卡类型编号 where 应还日期getdate() group by 借阅卡.卡号, 姓名,卡类型名称7、create procedure borrow readerid char(4) codeid(4)asbegindeclare days intif not exists(select * from 借阅卡 where 卡号=readerid) return -1if not exists(select * from 图书财产 where 条码号=codeid) return -1select days=可借天数 from 借阅期限 where 卡类型编号 in (select 卡类型编号 from 借阅卡 where 卡号=readerid) and 图书类型编号 in (select 图书类型编号 from 图书 where 图书编号 in (select 图书编号 from 图书财产 where 条码号=codeid) insert into 借阅(借阅日期,应还日期,卡号,条码号) values(getdate(),dateadd(day,days,getdate(),readerid,codeid)end8、create trigger tri_mytri on 借阅for delete asbegininsert into 借阅历史(借阅日期,