• SQL语句(十七)综合练习_分组查询_内嵌查询_视图使用


    Select * from Student
    Select * From Course
    Select * from SC
    
    --子查询  低于总平均成绩的女同学成绩
    Select Grade
    from Student, SC
    Where Student.Sno = SC.Sno and Student.Sex = '' and 
          Grade < (Select AVG(Grade) from SC)
    
    --(1).把低于总平均成绩的女同学成绩提高5%
    Update SC
    SET Grade = Grade * (1+0.05)
    From SC, Student
    Where Student.Sno = SC.Sno and Student.Sex = '' and 
          SC.Grade < (Select AVG(Grade) from SC) 
    
    Select * from SC
    
    --(2).把SC复制为SC1
    --先创建一个表,这里已经复制了,下面那个复制全部数据语句可以省略
    Select * 
    Into SC1
    From SC
    --复制全部数据
    Insert Into SC1 Select * From SC1 --(3)从SC1中删除刘晨所有选课记录 DELETE FROM SC1 FROM Student WHERE SC1.Sno = Student.Sno and Student.Sname = '刘晨' Select * From SC1 --(4).检索每一门课程成绩都大于等于80分的学生学号、姓名和性别,并把检索到的值送往
    --另一个已存在的基本表s1(Sno,SNAME,SEX),如果s1不存在,自己创建
    --先创建s1 Select * Into s1 FROM Student --插入数据 INSERT INTO s1(Sno, SNAME, SEX) Select distinct SC.Sno, Sname, Sex From SC, Student Where SC.Sno = Student.Sno and SC.Sno NOT IN (Select Sno From SC Where SC.Grade < 80) Select * from SC --(5)创建选课数少于3门的学生的视图SC_3(sno,sname) Create View SC_3(sno, sname) AS Select SC.Sno, Sname From SC, Student Group by SC.Sno, Student.Sname, Student.Sno Having Count(*) < 3 and SC.Sno = Student.Sno --子查询 选课数少于3门的学生 Select SC.Sno, Count(*) as 选课数 From SC Group by SC.Sno Having Count(*) < 3 select * From SC_3

    --作业二

    select * from Student
    select * from Course
    select * from SC
    
    --(1)取出没有选修‘操作系统’课程的学生姓名和年龄
    Select distinct SC.Sno, Sname, Sage
    From Student, Course, SC
    Where Student.Sno = SC.Sno and Course.Cno = SC.Cno
    and SC.Sno not in(
    select distinct SC.Sno
    From Course, SC
    where Course.Cno = SC.Cno and Course.Cname = '操作系统')
    
    
    
     --(2)检索至少选修课程“数据结构”和“C语言”的学生学号。
     Select distinct SC.Sno
     From Course, SC
     where SC.Cno = Course.Cno and Course.Cname in ('数据结构', 'C语言')
    
     --(3)检索和“刘晨”同性别并同系的同学的姓名。
    select Sname
    from Student
    Where Sex = 
                ( Select Sex
                  From Student
                  Where Sname = '刘晨')
         and Sdept = 
                ( Select Sdept
                  From Student
                  Where Sname = '刘晨')
                                        
    
    --(4)求选修课程名为"数据结构"课程的学生的平均年龄;
    Select AVG(Sage)
    from Student, Course, SC
    Where Student.Sno = SC.Sno and Course.Cno = SC.Cno and Course.Cname = '数据结构'
    
    
     --(5)查询没有选课的学生的学号和姓名
     Select Sno, Sname
     From Student
     Where Student.Sno not in 
                            ( select distinct SC.Sno
                              From Student, SC
                              where Student.Sno = SC.Sno )
    
        
  • 相关阅读:
    Wijmo 更优美的jQuery UI部件集:从wijwizard和wijpager开始
    设计规范基础
    libgdx的tmx地图处理工具gdxtiledpreprocessor.jar(TiledMapPacker)修正版
    Zookeeper简介
    翻译:Contoso 大学 6 – 更新关联数据
    jquery 插件ztree的应用简单的树(tree)
    使用EF构建企业级应用
    可复用的WPF或者Silverlight应用程序和组件设计(1)——应用程序级别
    Castor简单介绍
    Eclipse各种书籍资料整理包括书籍介绍和下载
  • 原文地址:https://www.cnblogs.com/douzujun/p/6656385.html
Copyright © 2020-2023  润新知