日历

2019年九月
« 5月    
 1
2345678
9101112131415
16171819202122
23242526272829
30  

最近评论

    linux/unix基础知识

    SQL练习题—转载

    原文地址:http://gundumw100.javaeye.com/blog/618011
    题目1

    问题描述:
    为管理岗位业务培训信息,建立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 RDATE<GETDATE()
    5. 查询书名包括”网络”关键词的图书,输出书号、书名、作者
    SELECT BNO,BNAME,AUTHOR FROM BOOKS WHERE BNAME LIKE N’%网络%’
    6. 查询现有图书中价格最高的图书,输出书名及作者
    SELECT BNO,BNAME,AUTHOR FROM BOOKS WHERE PRICE=(SELECT MAX(PRICE) FROM BOOKS)
    7. 查询当前借了”计算方法”但没有借”计算方法习题集”的读者,输出其借书卡号,并按卡号降序排序输出
    SELECT a.CNO FROM BORROW a,BOOKS b
    WHERE a.BNO=b.BNO AND b.BNAME=N’计算方法’ AND NOT EXISTS(
         SELECT * FROM BORROW aa,BOOKS bb WHERE aa.BNO=bb.BNO AND bb.BNAME=N’计算方法习题集’ AND aa.CNO=a.CNO)
    ORDER BY a.CNO DESC
    8. 将”C01″班同学所借图书的还期都延长一周
    UPDATE b SET RDATE=DATEADD(Day,7,b.RDATE) FROM CARD a,BORROW b WHERE a.CNO=b.CNO AND a.CLASS=N’C01′
    9. 从BOOKS表中删除当前无人借阅的图书记录
    DELETE A FROM BOOKS a WHERE NOT EXISTS(
        SELECT * FROM BORROW WHERE BNO=a.BNO)
    10. 如果经常按书名查询图书信息,请建立合适的索引
    CREATE CLUSTERED INDEX IDX_BOOKS_BNAME ON BOOKS(BNAME)
    11. 在BORROW表上建立一个触发器,完成如下功能:如果读者借阅的书名是”数据库技术及应用”,就将该读者的借阅记录保存在BORROW_SAVE表中(注ORROW_SAVE表结构同BORROW表)
    CREATE TRIGGER TR_SAVE ON BORROW FOR INSERT,UPDATE AS
    IF @@ROWCOUNT>0
    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
    #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;

    评论已关闭。