sql数据库试题

sql数据库试题 问题描述: 为管理岗位业务信息，建立3个: S (S#,SN,SD,SA) S#,SN,SD,SA 分别代表学号、学员姓名、所属单位、学员年龄 C (C#,CN ) C#,CN 分别代表课程编号、课程名称 SC ( S#,C#,G ) S#,C#,G 分别代表学号、所选修的课程编号、学习成绩 要求实现如下5个处理: 1. 使用SQL嵌套语句查询选修课程名称为’税收基础’的学员学号和姓名 2. 使用标准SQL嵌套语句查询选修课程编号为’C2’的学员姓名和所属单位 3. 使用标准SQL嵌套语句查询不选修课程编号为’C5’的学员姓名和所属单位 4. 使用标准SQL嵌套语句查询选修全部课程的学员姓名和所属单位 5. 查询选修了课程的学员人数 6. 查询选修课程超过5门的学员学号和所属单位 1. 使用标准SQL嵌套语句查询选修课程名称为’税收基础’的学员学号和姓名 SELECT SN,SD FROM S WHERE [S#] IN( SELECT [S#] FROM C,SC WHERE C.[C#]=SC.[C#] AND CN=N'税收基础') 2. 使用标准SQL嵌套语句查询选修课程编号为’C2’的学员姓名和所属单位 SELECT S.SN,S.SD FROM S,SC WHERE S.[S#]=SC.[S#] AND SC.[C#]='C2' 3. 使用标准SQL嵌套语句查询不选修课程编号为’C5’的学员姓名和所属单位 SELECT SN,SD FROM S WHERE [S#] NOT IN( SELECT [S#] FROM SC WHERE [C#]='C5') 4. 使用标准SQL嵌套语句查询选修全部课程的学员姓名和所属单位 SELECT SN,SD FROM S WHERE [S#] IN( SELECT [S#] FROM SC RIGHT JOIN C ON SC.[C#]=C.[C#] GROUP BY [S#] HAVING COUNT(*)=COUNT(DISTINCT [S#])) 5. 查询选修了课程的学员人数 SELECT 学员人数=COUNT(DISTINCT [S#]) FROM SC 6. 查询选修课程超过5门的学员学号和所属单位 SELECT SN,SD FROM S WHERE [S#] IN( SELECT [S#] FROM SC GROUP BY [S#] HAVING COUNT(DISTINCT [C#])>5) 题目2 问题描述: 已知关系模式: S (SNO,SNAME) 学生关系。SNO 为学号，SNAME 为姓名 C (CNO,CNAME,CTEACHER) 课程关系。CNO 为课程号，CNAME 为课程名，CTEACHER 为任课教师 SC(SNO,CNO,SCGRADE) 选课关系。SCGRADE 为成绩 要求实现如下5个处理: 1( 找出没有选修过“李明”老师讲授课程的所有学生姓名 2( 列出有二门以上(含两门)不及格课程的学生姓名及其平均成绩 3( 列出既学过“1”号课程，又学过“2”号课程的所有学生姓名 4( 列出“1”号课成绩比“2”号同学该门课成绩高的所有学生的学号 5(列出“1”号课成绩比“2”号课成绩高的所有学生的学号及其“1”号课和“2”号课的成绩 1. 找出没有选修过“李明”老师讲授课程的所有学生姓名 SELECT SNAME FROM S WHERE NOT EXISTS( SELECT * FROM SC,C WHERE SC.CNO=C.CNO AND C.CTEACHER='李明' AND SC.SNO=S.SNO) 2. 列出有二门以上(含两门)不及格课程的学生姓名及其平均成绩 SELECT S.SNO,S.SNAME,AVG_SCGRADE=AVG(SC.SCGRADE) FROM S,SC,( SELECT SNO FROM SC WHERE SCGRADE<60 GROUP BY SNO HAVING COUNT(DISTINCT CNO)>=2 )A WHERE S.SNO=A.SNO AND SC.SNO=A.SNO GROUP BY S.SNO,S.SNAME 3. 列出既学过“1”号课程，又学过“2”号课程的所有学生姓名 SELECT S.SNO,S.SNAME FROM S,( SELECT SC.SNO FROM SC,C WHERE SC.CNO=C.CNO AND C.CNAME IN('1','2') GROUP BY SNO HAVING COUNT(DISTINCT CNO)=2 )SC WHERE S.SNO=SC.SNO 4. 列出“1”号课成绩比“2”号同学该门课成绩高的所有学生的学号 SELECT S.SNO,S.SNAME FROM S,SC SC1,SC SC2 WHERE SC1.CNO='1' AND SC2.SNO='2' AND SC1.CNO=S.CNO AND SC1.SCGRADE>SC2.SCGRADE 5. 列出“1”号课成绩比“2”号课成绩高的所有学生的学号及其“1”号课和“2”号课的成绩 SELECT SC1.SNO,[1号课成绩]=SC1.SCGRADE,[2号课成绩]=SC2.SCGRADE FROM SC SC1,SC SC2 WHERE SC1.CNO='1' AND SC2.CNO='2' AND SC1.SNO=SC2.SNO AND SC1.SCGRADE>SC2.SCGRADE 题目3 问题描述: 本题用到下面三个关系表: CARD 借卡。 CNO 卡号，NAME 姓名，CLASS 班级 BOOKS 图书。 BNO 书号，BNAME 书名,AUTHOR 作者，PRICE 单价，QUANTITY 库存册数 BORROW 借书。 CNO 借书卡号，BNO 书号，RDATE 还书日期 备注:限定每人每种书只能借一本;库存册数随借书、还书而改变。 要求实现如下15个处理: 1( 写出建立BORROW表的SQL语句，要求定义主码完整性约束和引用完整性约束。 2( 找出借书超过5本的读者,输出借书卡号及所借图书册数。 3( 查询借阅了"水浒"一书的读者，输出姓名及班级。 4( 查询过期未还图书，输出借阅者(卡号)、书号及还书日期。 5( 查询书名包括"网络"关键词的图书，输出书号、书名、作者。 6( 查询现有图书中价格最高的图书，输出书名及作者。 7(查询当前借了"计算方法"但没有借"计算方法习题集"的读者，输出其借书卡号，并按卡号降序排序输出。 8( 将"C01"班同学所借图书的还期都延长一周。 9( 从BOOKS表中删除当前无人借阅的图书记录。 10(如果经常按书名查询图书信息，请建立合适的索引。 11(在BORROW表上建立一个触发器，完成如下功能:如果读者借阅的书名是"数据库技术及应用"，就将该读者的借阅记录保存在BORROW_SAVE 表中(注ORROW_SAVE表结构同BORROW表)。 12(建立一个视图，显示"力01"班学生的借书信息(只要求显示姓名和书名)。 13(查询当前同时借有"计算方法"和"组合数学"两本书的读者，输出其借书卡号，并按卡号升序排序 输出。 14(假定在建BOOKS表时没有定义主码，写出为BOOKS表追加定义主码的语句。 15(对CARD表做如下修改: a. 将NAME最大列宽增加到10个字符(假定原为6个字符)。 b. 为该表增加1列NAME(系名)，可变长，最大20个字符。 1. 写出建立BORROW表的SQL语句，要求定义主码完整性约束和引用完整性约束 CREATE TABLE BORROW( CNO int FOREIGN KEY REFERENCES CARD(CNO), BNO int FOREIGN KEY REFERENCES BOOKS(BNO), RDATE datetime, PRIMARY KEY(CNO,BNO)) 2. 找出借书超过5本的读者,输出借书卡号及所借图书册数 SELECT CNO,借图书册数=COUNT(*) FROM BORROW GROUP BY CNO HAVING COUNT(*)>5 3. 查询借阅了"水浒"一书的读者，输出姓名及班级 SELECT * FROM CARD c WHERE EXISTS( SELECT * FROM BORROW a,BOOKS b WHERE a.BNO=b.BNO AND b.BNAME=N'水浒' AND a.CNO=c.CNO) 4. 查询过期未还图书，输出借阅者(卡号)、书号及还书日期 SELECT * FROM BORROW WHERE RDATE0 INSERT BORROW_SAVE SELECT i.* FROM INSERTED i,BOOKS b WHERE i.BNO=b.BNO AND b.BNAME=N'数据库技术及应用' 12. 建立一个视图，显示"力01"班学生的借书信息(只要求显示姓名和书名) CREATE VIEW V_VIEW AS SELECT a.NAME,b.BNAME FROM BORROW ab,CARD a,BOOKS b WHERE ab.CNO=a.CNO AND ab.BNO=b.BNO AND a.CLASS=N'力01' 13. 查询当前同时借有"计算方法"和"组合数学"两本书的读者，输出其借书卡号，并按卡号升序排序输出 SELECT a.CNO FROM BORROW a,BOOKS b WHERE a.BNO=b.BNO AND b.BNAME IN(N'计算方法',N'组合数学') GROUP BY a.CNO HAVING COUNT(*)=2 ORDER BY a.CNO DESC 14. 假定在建BOOKS表时没有定义主码，写出为BOOKS表追加定义主码的语句 ALTER TABLE BOOKS ADD PRIMARY KEY(BNO) 15.1 将NAME最大列宽增加到10个字符(假定原为6个字符) ALTER TABLE CARD ALTER COLUMN NAME varchar(10) 15.2 为该表增加1列NAME(系名)，可变长，最大20个字符 ALTER TABLE CARD ADD 系名 varchar(20) 其他sql: #1 有学生和学生扩展两张表，在学生这张表中有学生密码这个字段，我想设置密码，是将学生扩展这张表的生日字段作为他的密码(例20060102)这两张表通过学号关联，我想实现两个查询，一是使所有学生初始化，二是是选中的学生初始化 。 1) update student s set s.password=(select t.brithday from student_ext t where s.id=t.id) 2) update student s set s.password=(select t.brithday from student_ext t where s.id=t.id) where s.id= ? #2 id sname smoney sprovince 1 zhangsan 2098 A 2 lisi 3000 B 3 wangwu 6789 C 4 liumazi 4587 C 5 dongjiu 3298 B 6 shiga 4567 A id:合同id sname:姓名 smoney :业绩 sprovince:地区 第一道:显示出 业绩 大于同一地区平均值的 合同id 姓名 地区 业绩 第二道:把同一地区的 平均业绩 地区 插入到新表中 (新表只包含两个字段即:平均业绩 地区) select T1.* from test T1,( select avg(smoney) s ,sprovince from test group by sprovince)T2 where T1.smoney > T2.s and T1.sprovince = T2.sprovince; create table table_name_2 from (select sprovince, avg(smoney) avgmoney from table_name_1 group by sprovince); #3 表内容 user(x varchar(50),y varchar(50)) 火箭 胜 火箭 胜 火箭 负 火箭 负 湖人 胜 湖人 负 结果: 胜 负 火箭 2 2 湖人 1 1 这样的sql语句怎么写? select x user,sum(case y when '胜' then 1 else 0 end) 胜,sum(case y when '负' then 1 else 0 end) 负 from user group by x #4 表结构 city id name money 1 北京 200 2 上海 100 3 北京 399 4 广州 300 5 北京 300 6 上海 100 7 广州 200 8 上海 500 求 money的值小于 money 的平均值 城市id select a.id from test a where a.money <(select avg(money) from test) #5 数据库中有这样的字段: id str 1 a 1 b 2 c 2 d 但是现在想通过sql语句查询出来，显示成为下面这样: 1 ab 2 cd 有什么方法吗, select id, GROUP_CONCAT(str SEPARATOR'' ) from tablename group by id; #6 表stuinfo id subject score 1 语文 78 1 数学 60 1 英语 90 2 语文 50 2 数学 44 2 英语 55 3 语文 45 3 数学 44 3 英语 90 现在要求 如下 查出每个编号的总成绩 并且要大于200分 id score?(总成绩要大于200分) 1 228 请写出sql语句, select id, s score from (select id,sum(score) s from stuinfo group by id) t where t.s > 200 select id ,sum(score) from stuinfo group by id having sum(score)>=220; #7 a表(人) b表(水果) aid name bid name 1 a 1 苹果 2 b 2 梨 3 c 3 橙 4 d 4 香蕉 c表 cid aid bid 1 1 1 2 1 3 3 1 4 4 2 3 5 2 4 6 3 2 7 4 1 8 4 2 9 4 3 10 4 4 问题:根据bid求出aid(如:bid=3 and bid=4 那么可以得到aid=1,aid=2,aid=4)， 就是说根据条 件给出的水果类别.来求出买了这些水果的人.(求出买了 橙子和香蕉的人的名字: ) select distinct(aid) from C where aid in (select aid from C where bid=3) and aid in (select aid from C where bid=4) #8 数据库表A 有个字段a是int类型 a中有数据有1到9任意(有重复的) 想取得a中，当 a=1时 a=2时 a=3…… 各一条记录的列表 select top 1 * from A where a=1 union select top 1 * from A where a=2 ... union select top 1 * from A where a=9 #9 一个表User，有字段id，name，message，现在要查出这个表中发消息(message)最多的 人的名字，怎么表达啊, select n from (select name n count(id) c from user group by id order by c desc limit 1) as a; #10 field1 field2 A 1 A 2 A 3 用一条sql语句查询成: field1 all A 1,2,3, select field1,group_concat(field2) as all from table group by field1