澳门金沙vip学生表 课程表 成绩表 教师表 50个常用sql语句

—————-1、查询所有列 –查询 学生 表所有记录(行) select *from
学生

Student(S#,Sname,Sage,Ssex) 学生表

Course(C#,Cname,T#) 课程表

SC(S#,C#,score) 成绩表

Teacher(T#,Tname) 教师表

 

create table Student(S# varchar(20),Sname varchar(10),Sage int,Ssex varchar(2)) 

前面加一列序号:

if

exists(select table_name from information_schema.tables

  where table_name='Temp_Table')

drop table Temp_Table

go

select 排名=identity(int,1,1),* INTO   Temp_Table    from Student 

go

select * from Temp_Table

go 

 

drop database [ ]  --删除空的没有名字的数据库

问题:

1、查询“”课程比“”课程成绩高的所有学生的学号;

  select a.S# from (select s#,score from SC where C#='001') a,(select s#,score 

  from SC where C#='002') b 

  where a.score>b.score and a.s#=b.s#; 

 

2、查询平均成绩大于分的同学的学号和平均成绩;

    select S#,avg(score) 

    from sc 

    group by S# having avg(score) >60; 

 

3、查询所有同学的学号、姓名、选课数、总成绩;

  select Student.S#,Student.Sname,count(SC.C#),sum(score) 

  from Student left Outer join SC on Student.S#=SC.S# 

  group by Student.S#,Sname 

 

4、查询姓“李”的老师的个数;

  select count(distinct(Tname)) 

  from Teacher 

  where Tname like '李%'; 

 

5、查询没学过“叶平”老师课的同学的学号、姓名;

    select Student.S#,Student.Sname 

    from Student  

    where S# not in (select distinct( SC.S#) from SC,Course,Teacher where  SC.C#=Course.C# and Teacher.T#=Course.T# and Teacher.Tname='叶平'); 

 

6、查询学过“”并且也学过编号“”课程的同学的学号、姓名;

  select Student.S#,Student.Sname from Student,SC where Student.S#=SC.S# and SC.C#='001'and exists( Select * from SC as SC_2 where SC_2.S#=SC.S# and SC_2.C#='002'); 

 

7、查询学过“叶平”老师所教的所有课的同学的学号、姓名;

  select S#,Sname 

  from Student 

  where S# in (select S# from SC ,Course ,Teacher where SC.C#=Course.C# and Teacher.T#=Course.T# and Teacher.Tname='叶平' group by S# having count(SC.C#)=(select count(C#) from Course,Teacher  where Teacher.T#=Course.T# and Tname='叶平')); 

 

8、查询课程编号“”的成绩比课程编号“”课程低的所有同学的学号、姓名;

  Select S#,Sname from (select Student.S#,Student.Sname,score ,(select score from SC SC_2 where SC_2.S#=Student.S# and SC_2.C#='002') score2 

  from Student,SC where Student.S#=SC.S# and C#='001') S_2 where score2 <score; 

 

9、查询所有课程成绩小于分的同学的学号、姓名;

  select S#,Sname 

  from Student 

  where S# not in (select Student.S# from Student,SC where S.S#=SC.S# and score>60); 

 

10、查询没有学全所有课的同学的学号、姓名;

    select Student.S#,Student.Sname 

    from Student,SC 

    where Student.S#=SC.S# group by  Student.S#,Student.Sname having count(C#) <(select count(C#) from Course); 

 

11、查询至少有一门课与学号为“”的同学所学相同的同学的学号和姓名;

    select S#,Sname from Student,SC where Student.S#=SC.S# and C# in select C# from SC where S#='1001'; 

 

12、查询至少学过学号为“”同学所有一门课的其他同学学号和姓名;

    select distinct SC.S#,Sname 

    from Student,SC 

    where Student.S#=SC.S# and C# in (select C# from SC where S#='001'); 

 

13、把“SC”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩;

    update SC set score=(select avg(SC_2.score) 

    from SC SC_2 

    where SC_2.C#=SC.C# ) from Course,Teacher where Course.C#=SC.C# and Course.T#=Teacher.T# and Teacher.Tname='叶平'); 

 

14、查询和“”号的同学学习的课程完全相同的其他同学学号和姓名;

    select S# from SC where C# in (select C# from SC where S#='1002') 

    group by S# having count(*)=(select count(*) from SC where S#='1002'); 

 

15、删除学习“叶平”老师课的SC表记录;

    Delect SC 

    from course ,Teacher  

    where Course.C#=SC.C# and Course.T#= Teacher.T# and Tname='叶平'; 

 

16、向SC表中插入一些记录,这些记录要求符合以下条件:没有上过编号“”课程的同学学号、、

    号课的平均成绩;

    Insert SC select S#,'002',(Select avg(score) 

    from SC where C#='002') from Student where S# not in (Select S# from SC where C#='002'); 

 

17、按平均成绩从高到低显示所有学生的“数据库”、“企业管理”、“英语”三门的课程成绩,按如下形式显示:学生ID,,数据库,企业管理,英语,有效课程数,有效平均分

    SELECT S# as 学生ID 

        ,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#='004') AS 数据库

        ,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#='001') AS 企业管理

        ,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#='006') AS 英语

        ,COUNT(*) AS 有效课程数, AVG(t.score) AS 平均成绩

    FROM SC AS t 

    GROUP BY S# 

    ORDER BY avg(t.score)  

 

18、查询各科成绩最高和最低的分:以如下形式显示:课程ID,最高分,最低分

    SELECT L.C# As 课程ID,L.score AS 最高分,R.score AS 最低分

    FROM SC L ,SC AS R 

    WHERE L.C# = R.C# and 

        L.score = (SELECT MAX(IL.score) 

                      FROM SC AS IL,Student AS IM 

                      WHERE L.C# = IL.C# and IM.S#=IL.S# 

                      GROUP BY IL.C#) 

        AND 

        R.Score = (SELECT MIN(IR.score) 

                      FROM SC AS IR 

                      WHERE R.C# = IR.C# 

                  GROUP BY IR.C# 

                    ); 

 

19、按各科平均成绩从低到高和及格率的百分数从高到低顺序

    SELECT t.C# AS 课程号,max(course.Cname)AS 课程名,isnull(AVG(score),0) AS 平均成绩

        ,100 * SUM(CASE WHEN  isnull(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) AS 及格百分数

    FROM SC T,Course 

    where t.C#=course.C# 

    GROUP BY t.C# 

    ORDER BY 100 * SUM(CASE WHEN  isnull(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) DESC 

 

20、查询如下课程平均成绩和及格率的百分数(用"1行"显示): 企业管理(),马克思(),OO&UML (),数据库()

    SELECT SUM(CASE WHEN C# ='001' THEN score ELSE 0 END)/SUM(CASE C# WHEN '001' THEN 1 ELSE 0 END) AS 企业管理平均分

        ,100 * SUM(CASE WHEN C# = '001' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '001' THEN 1 ELSE 0 END) AS 企业管理及格百分数

        ,SUM(CASE WHEN C# = '002' THEN score ELSE 0 END)/SUM(CASE C# WHEN '002' THEN 1 ELSE 0 END) AS 马克思平均分

        ,100 * SUM(CASE WHEN C# = '002' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '002' THEN 1 ELSE 0 END) AS 马克思及格百分数

        ,SUM(CASE WHEN C# = '003' THEN score ELSE 0 END)/SUM(CASE C# WHEN '003' THEN 1 ELSE 0 END) AS UML平均分

        ,100 * SUM(CASE WHEN C# = '003' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '003' THEN 1 ELSE 0 END) AS UML及格百分数

        ,SUM(CASE WHEN C# = '004' THEN score ELSE 0 END)/SUM(CASE C# WHEN '004' THEN 1 ELSE 0 END) AS 数据库平均分

        ,100 * SUM(CASE WHEN C# = '004' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '004' THEN 1 ELSE 0 END) AS 数据库及格百分数

  FROM SC 

 

21、查询不同老师所教不同课程平均分从高到低显示

  SELECT max(Z.T#) AS 教师ID,MAX(Z.Tname) AS 教师姓名,C.C# AS 课程ID,MAX(C.Cname) AS 课程名称,AVG(Score) AS 平均成绩

    FROM SC AS T,Course AS C ,Teacher AS Z 

    where T.C#=C.C# and C.T#=Z.T# 

  GROUP BY C.C# 

  ORDER BY AVG(Score) DESC 

 

22、查询如下课程成绩第名到第名的学生成绩单:企业管理(),马克思(),UML (),数据库()

    [学生ID],[学生姓名],企业管理,马克思,UML,数据库,平均成绩

    SELECT  DISTINCT top 3 

      SC.S# As 学生学号, 

        Student.Sname AS 学生姓名, 

      T1.score AS 企业管理, 

      T2.score AS 马克思, 

      T3.score AS UML, 

      T4.score AS 数据库, 

      ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0) as 总分

      FROM Student,SC  LEFT JOIN SC AS T1 

                      ON SC.S# = T1.S# AND T1.C# = '001' 

            LEFT JOIN SC AS T2 

                      ON SC.S# = T2.S# AND T2.C# = '002' 

            LEFT JOIN SC AS T3 

                      ON SC.S# = T3.S# AND T3.C# = '003' 

            LEFT JOIN SC AS T4 

                      ON SC.S# = T4.S# AND T4.C# = '004' 

      WHERE student.S#=SC.S# and 

      ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0) 

      NOT IN 

      (SELECT 

            DISTINCT 

            TOP 15 WITH TIES 

            ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0) 

      FROM sc 

            LEFT JOIN sc AS T1 

                      ON sc.S# = T1.S# AND T1.C# = 'k1' 

            LEFT JOIN sc AS T2 

                      ON sc.S# = T2.S# AND T2.C# = 'k2' 

            LEFT JOIN sc AS T3 

                      ON sc.S# = T3.S# AND T3.C# = 'k3' 

            LEFT JOIN sc AS T4 

                      ON sc.S# = T4.S# AND T4.C# = 'k4' 

      ORDER BY ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0) DESC); 

 

23、统计列印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60] 

    SELECT SC.C# as 课程ID, Cname as 课程名称

        ,SUM(CASE WHEN score BETWEEN 85 AND 100 THEN 1 ELSE 0 END) AS [100 - 85] 

        ,SUM(CASE WHEN score BETWEEN 70 AND 85 THEN 1 ELSE 0 END) AS [85 - 70] 

        ,SUM(CASE WHEN score BETWEEN 60 AND 70 THEN 1 ELSE 0 END) AS [70 - 60] 

        ,SUM(CASE WHEN score < 60 THEN 1 ELSE 0 END) AS [60 -] 

    FROM SC,Course 

    where SC.C#=Course.C# 

    GROUP BY SC.C#,Cname; 

 

24、查询学生平均成绩及其名次

      SELECT 1+(SELECT COUNT( distinct 平均成绩) 

              FROM (SELECT S#,AVG(score) AS 平均成绩

                      FROM SC 

                  GROUP BY S# 

                  ) AS T1 

            WHERE 平均成绩> T2.平均成绩) as 名次, 

      S# as 学生学号,平均成绩

    FROM (SELECT S#,AVG(score) 平均成绩

            FROM SC 

        GROUP BY S# 

        ) AS T2 

    ORDER BY 平均成绩desc; 

 

25、查询各科成绩前三名的记录:(不考虑成绩并列情况) 

      SELECT t1.S# as 学生ID,t1.C# as 课程ID,Score as 分数

      FROM SC t1 

      WHERE score IN (SELECT TOP 3 score 

              FROM SC 

              WHERE t1.C#= C# 

            ORDER BY score DESC 

              ) 

      ORDER BY t1.C#; 

 

26、查询每门课程被选修的学生数

  select c#,count(S#) from sc group by C#; 

 

27、查询出只选修了一门课程的全部学生的学号和姓名

  select SC.S#,Student.Sname,count(C#) AS 选课数

  from SC ,Student 

  where SC.S#=Student.S# group by SC.S# ,Student.Sname having count(C#)=1; 

 

28、查询男生、女生人数

    Select count(Ssex) as 男生人数from Student group by Ssex having Ssex='男'; 

    Select count(Ssex) as 女生人数from Student group by Ssex having Ssex='女'; 

 

29、查询姓“张”的学生名单

    SELECT Sname FROM Student WHERE Sname like '张%'; 

 

30、查询同名同性学生名单,并统计同名人数

  select Sname,count(*) from Student group by Sname having  count(*)>1;; 

 

31、年出生的学生名单(注:Student表中Sage列的类型是datetime) 

    select Sname,  CONVERT(char (11),DATEPART(year,Sage)) as age 

    from student 

    where  CONVERT(char(11),DATEPART(year,Sage))='1981'; 

 

32、查询每门课程的平均成绩,结果按平均成绩升序排列,平均成绩相同时,按课程号降序排列

    Select C#,Avg(score) from SC group by C# order by Avg(score),C# DESC ; 

 

33、查询平均成绩大于的所有学生的学号、姓名和平均成绩

    select Sname,SC.S# ,avg(score) 

    from Student,SC 

    where Student.S#=SC.S# group by SC.S#,Sname having    avg(score)>85; 

 

34、查询课程名称为“数据库”,且分数低于的学生姓名和分数

    Select Sname,isnull(score,0) 

    from Student,SC,Course 

    where SC.S#=Student.S# and SC.C#=Course.C# and  Course.Cname='数据库'and score <60; 

 

35、查询所有学生的选课情况;

    SELECT SC.S#,SC.C#,Sname,Cname 

    FROM SC,Student,Course 

    where SC.S#=Student.S# and SC.C#=Course.C# ; 

 

36、查询任何一门课程成绩在分以上的姓名、课程名称和分数;

    SELECT  distinct student.S#,student.Sname,SC.C#,SC.score 

    FROM student,Sc 

    WHERE SC.score>=70 AND SC.S#=student.S#; 

 

37、查询不及格的课程,并按课程号从大到小排列

    select c# from sc where scor e <60 order by C# ; 

 

38、查询课程编号为且课程成绩在分以上的学生的学号和姓名;

    select SC.S#,Student.Sname from SC,Student where SC.S#=Student.S# and Score>80 and C#='003'; 

 

39、求选了课程的学生人数

    select count(*) from sc; 

 

40、查询选修“叶平”老师所授课程的学生中,成绩最高的学生姓名及其成绩

    select Student.Sname,score 

    from Student,SC,Course C,Teacher 

    where Student.S#=SC.S# and SC.C#=C.C# and C.T#=Teacher.T# and Teacher.Tname='叶平' and SC.score=(select max(score)from SC where C#=C.C# ); 

 

41、查询各个课程及相应的选修人数

    select count(*) from sc group by C#; 

 

42、查询不同课程成绩相同的学生的学号、课程号、学生成绩

  select distinct  A.S#,B.score from SC A  ,SC B where A.Score=B.Score and A.C# <>B.C# ; 

 

43、查询每门功成绩最好的前两名

    SELECT t1.S# as 学生ID,t1.C# as 课程ID,Score as 分数

      FROM SC t1 

      WHERE score IN (SELECT TOP 2 score 

              FROM SC 

              WHERE t1.C#= C# 

            ORDER BY score DESC 

              ) 

      ORDER BY t1.C#; 

 

44、统计每门课程的学生选修人数(超过人的课程才统计)。要求输出课程号和选修人数,查询结果按人数降序排列,查询结果按人数降序排列,若人数相同,按课程号升序排列

    select  C# as 课程号,count(*) as 人数

    from  sc  

    group  by  C# 

    order  by  count(*) desc,c#  

 

45、检索至少选修两门课程的学生学号

    select  S#  

    from  sc  

    group  by  s# 

    having  count(*)  >  =  2 

 

46、查询全部学生都选修的课程的课程号和课程名

    select  C#,Cname  

    from  Course  

    where  C#  in  (select  c#  from  sc group  by  c#)  

 

47、查询没学过“叶平”老师讲授的任一门课程的学生姓名

    select Sname from Student where S# not in (select S# from Course,Teacher,SC where Course.T#=Teacher.T# and SC.C#=course.C# and Tname='叶平'); 

 

48、查询两门以上不及格课程的同学的学号及其平均成绩

    select S#,avg(isnull(score,0)) from SC where S# in (select S# from SC where score <60 group by S# having count(*)>2)group by S#; 

 

49、检索“”课程分数小于,按分数降序排列的同学学号

    select S# from SC where C#='004'and score <60 order by score desc; 

 

50、删除“”同学的“”课程的成绩

delete from Sc where S#='001'and C#='001'; 

1,通过命令行方式打开sqlplus
2,执行登录命令
sqlplus scott/scott@192.168.248.129/orcl
3进行sqlplus命令测试

–带条件的查询 select *from 学生 where 年龄>19

 

Set time on
Set pagesize 数字
Set linesizes 数字
Set pause on/off

——————-2、查询指定的列 –查询 所有人的姓名和性别 select
姓名,性别 from 学生

 

Describe命令  查看表或视图结构 也可以desc

–查询 所有 年龄>19 的学生的 姓名 select 姓名,地址 from 学生 where
年龄>19

学生表(学号、姓名、性别、年龄、所在系) 
课程表(课程号、课程名、先修课号、学分) 
学生选课表(学号、课程号、成绩) 

Show 查看相应参数 如:show parameter,show user,show error等

/*比较运算符 = > < >= <= != <>不等于 !>不大于
!<不小于 */ ———————–3、给列取别名 –方式一 select
姓名,地址=家乡 from 学生

–1:查询全体学生的学号和姓名 

Get命令 把一个sql脚本文件内容放入缓冲区

–方式二 select 姓名,地址as家乡 from 学生

–2:查询全体学生的姓名、学号和所在系 

用系统编辑程序编辑命令
在SQL*PLUS中运行操作系统缺省的文本编辑程序(EDIT)
命令为:edit或者ed

———————–4、消除重复 –查询该表有哪些家乡 select distinct
地址 from 学生

–3:  查询全体学生的详细记录 

将缓存中的sql语句保存到文件中:SAVE 文件名【具体路径】
查看缓存sql:list

———————-5、top n(查询前N条) select top 3 * from 学生
–查询前3条记录

–4: 查询全体学生的姓名及其出生年份 

执行文件中的sql语句:
START my.sql
@ my.sql

———————–6、排序 select *from 学生 order by 年龄 asc    
–按年龄进行升序排列                        –desc降序         –asc升序

–5:查询全体学生姓名、出生年份和所在系,要求用小写字母表示所有系名 

清空缓冲区:clear buffer
格式化命令:column
column sal format $999,999.00
如:
SQL> select ename, sal from emp
  2
SQL> /

select * from  学生 order by 年龄 desc ,编号 asc —按年龄降序
–先按年龄进行降序,在出现相同年龄的时候,把这些相同的学生 再按照 学号
升序排列

–6:查询选修了课程的学生学号 

ENAME               SAL

–例:查询 学生 表中,年龄最大的三个学生的 年龄、姓名、编号 select top
3  年龄,姓名,编号 from 学生 order by 年龄 desc

–7:查询计算机系(IS)所有学生的名单 


——————————-7、 and(并且)、or(或者) select *from
学生 where 年龄=20 and 姓名=’张三’

–8:查询所有年龄在20以下学生的姓名和年龄 

SMITH           $800.00
ALLEN         $1,600.00
WARD          $1,250.00
JONES         $2,975.00
MARTIN        $1,250.00
BLAKE         $2,850.00
CLARK         $2,450.00

–例如:查询 性别为男的 或 专 地址为武汉 select *from 学生 where
性别=’男’  or 地址=’武汉’

–9:  查询考试成绩有不及格的学生的学号 

重置为默认值:clear columns;
将屏幕上的内容写入到文件中
spool fileName
结束写入
spool off

———————-8、between … and(介于…之间)
–例:查询年龄为20-30之间的所有人 select *from 学生 where 年龄 between
20 and 30

–10: 查询年龄在20-23 (包括20和23)之间的学生的姓名、系别和年龄 

3.Oracle查询
1) 查询EMP表,显示部门号为10的所有雇员的NAME 、JOB、SALARY和
DEPTNO,并以岗位降序、工资升序进行排序。
select ename,job,sal,deptno from emp where deptno=10 order by job desc ,
sal asc

———————–9、in 的用法 select * from 学生 where 年龄
in(20,19,18)

–11: 查询信息系(IS)、数学系(MA)和计算机科学系(CS)学生的姓名和性别 

2) 从EMP表中查询所有雇员的NAME和补助(SALARY+COMM),并处理null行。
select ename,(sal+nvl(comm,0)) from emp

—————————10、top N  与 order by 同时使用

–12: 查询学号为95001的学生的详细情况 

3) 统计30号部门的总人数、人均工资、最高工资和最低工资。
select count(*) totle,avg(sal),max(sal),min(sal) from emp where
deptno=30

–例:查询年龄最大的一人 select top 1 with ties * from  学生 
–加了with ties 后 如有并列第一的就全都显示 order by 年龄 desc

–13: 查询所有姓林的学生的姓名、学号和性别 

4)
查询各个部门各个工种的平均工资、每个部门的平均工资和所有员工的平均工资。
select deptno,job,avg(sal) from emp group by rollup(deptno,job);

———————————11、case替换查询结果
–查询所有人信息,如果年龄>=40岁,就显示”中年人”, —     如果年龄
介于30-39  ,就显示“青年” —     如果年龄  介于20-29 ,就显示“青少年”
—     如果年龄   小于20  , 就显示“少年”

–14: 查询姓“欧阳”且全名为三个汉字的学生的姓名 

5)
查询各个部门中各个工种的平均工资、每个部门的平均工资、每个工种的平均工资和所有员工的平均工资。
select deptno,job,avg(sal) from emp group by cube(deptno,job);

select 学号,姓名,性别, 年龄=case   when 年龄>=40 then ‘中年人’   when
年龄 between 30 and 39 then ‘青年’   when 年龄 between 20 and 29  then
‘青少年’   else ‘少年’ –else表示不满足以上条件时,就全部  end ,住址
from 学生

–15:查询名字中第二个字为“燕”字的学生姓名和学号 

6)
查询EMP,显示1999年之后参加工作人员的NAME、JOB和HIREDATE,并以工作日期的升序进行排序。
select * from emp where hiredate>’1-JAN-99′;

———————————–12、模糊查找 使用like子句进行模糊查询
like子句与通配符配合使.Sql server提供4种通配符 1.%:表示任意字符 2.
_:表示单个任意字符 3.[ ]:表示方括号里列出的任意一个字符.
4.[^]:表示任意一个没有在方括号里列出的字符.

–16:查询所有不姓“刘”的学生的姓名 

7) 查询名字以“S”开始的所有职工:
select *from emp where ename like ‘S%’;

–例:查找姓周的所有人信息 select * from 学生 where 姓名 like ‘周%’
–%可以代替任意几个字符

–17:查询课程名为“DB_DESIGN”的课程号的学分 

8) 让员工姓名右对齐显示
col ename jus left

select * from 学生 where 姓名 like ‘周_’ –_表示可以代替一个字符

–18:查询缺少成绩的学生的学号和相应的课程号(成绩字段值为Null) 

9) 显示姓名只有5个字母组成的员工信息
select *from emp where ename like ‘_____’
或者
select *from emp where length(ename)=5

–例:查找姓名的第二个字包含 ’心‘ 或者 ’三‘ 的人 select * from 学生
where 姓名 like ‘_[星,三]_’

–19: 查询所有有成绩的学生的学号和课程号 

10) 查询在当月倒数第三天入职的员工信息
select *from emp where hiredate = last_day(hiredate)-2

 

–20: 查询所有计算机系年龄在20以下的学生姓名 

2) 对各表中的数据进行不同条件的查询;
a)查询全体学生的学号和姓名
select sno,sname from student;

–嵌套查询(一般嵌套请不要超过3层,即不要出现超过3个select) select *
from 学生  where 年龄< (  select 年龄 from 学生 where 姓名=’张三’ )

–21: 查询选修了3号课程的学生的学号和成绩,查询结果按分数降序排列 

b)查询全体学生的详细记录
select *from student;

  –例如:查询所有比 中文系所有学生年龄 大的学生 select * from xs where
年龄> (  select top 1 年龄 from xs  where 所在系=’中文’  order by
年龄 desc )

–22: 查询全体学生情况,查询结果按所在系的系号升序排列,同一系中的学生按年龄降序排列 

c)查询所有选修过课程的学生学号
select distinct sno from sc;
或者
select sno from student where sno in (select sno from sc);

/*运算符  all some   any  */ 

–23: 查询学生总人数 

d)查询考试有不及格的学生学号
select sno from sc where nvl(grade,0) <60;

/*
all:指定表达式要与子查询结果集中的每个值都进行比较,当表达式与每个值都满足比较的关系时,才返回true,否则返回false;

–24: 查询选修了课程的学生人数 

e)查询不是信息系(IS)、计算机系(CS)的学生性别、年龄、系别
select ssex,sage,sdept from student where sdept not in(‘is’,’cs’);

Some和any:表示表达式只要与子查询结果集中的某个值满足比较的关系时,
就返回true,否则返回false.

–25: 计算1号课程的学生的平均成绩 

f)查询选修了4号课的学生学号和成绩,结果按成绩降序排列
select sc1.sno,sc1.grade from sc sc1,sc sc2 where sc2.cno=4 and
sc1.sno=sc2.sno;

*/

–26: 计算1号课程的学生的最高成绩分数 

g)查询每个课程号和相应的选课人数
select cno,count(sno) from sc group by cno;

select * from xs where 年龄>all (  select 年龄 from xs where
所在系=’中文’ )

–27:求各个课程号及相应的选课人数 

h)查询计算机系(CS)的学生姓名、年龄、系别
select sname,sage,sdept from student where sdept=’cs’

———————————-表的复制
/*把所有计算机系的学生拉出来单独创建一个表*/

–28:  查询选修了三门以上课程的学生学号 

i)查询年龄18-20岁的学生学号、姓名、系别、年龄;
select sno,sname,sage,sdept from student where sage between 18 and 20

create  table  xs_jisuanji    –创建一个新表 (  学号 int,  姓名
varchar(50),  性别 char(10),  年龄 int )

–29:查询每个学生及其选修课情况 

j)查询姓刘的学生情况
select *from student where sname like ‘刘%’

insert into xs_jisuanji       –查询内容 并复制内容到新建的表 select
学号 ,姓名, 性别, 年龄 from xs where  所在系=’计算机’H

–30:查询每一门课的间接先行课 

k)查询既选修1号课程,又选修2号课程的学生学号和成绩
select sno,grade from sc where cno = 1 and cno in(select sno from sc
where cno=2);

 

–31:选修2号课程且成绩在90以上的学生的学号和姓名 

l)查询学生的姓名和出生年份(今年2003年)
select sname,2017-sage born from student;

/*复制方式二*/   –创建中文系的表 select 学号,姓名,性别 ,年龄 into
xs_zhongwen from xs where 所在系=’中文’

–32:查询每个学生的学号、姓名、选修的课程名及成绩 

m)查询没有成绩的学生学号和课程号
select s.sno,c.cno from student s,course c where (s.sno,c.cno) not
in(select sno,cno from sc);

—跨数据库表的复制(需要在 表名前加数据库名) select  * into
test.dbo.xs  from   n2d09003

–33:查询与’林燕芳’在同一个系学习的学生姓名 

n)查询总成绩大于200分的学生学号
select sno from sc group by sno having sum(grade)>200;

 

–34: 查询其他系中比信息系某一学生小的学生姓名和年龄 

o)查询每门课程不及格学生人数
select cno ,count(sno) from sc where nvl(grade,0)<60 group by
cno【不及格分组】

 

–35:查询所有选修了1号课程的学生的学生姓名 

p)查询不及格课程超过3门的学生学号
select sno from (select sno,cno from sc where nvl(grade,0)<60) r
group by sno having count(r.cno)>3

——————————————– –聚合函数

–36:查询选修了全部课程的学生姓名 

q)查询年龄在10到19岁之间的学生信息
select *from student where sage>=10 and sage<=19;
select *from student where sage between 10 and 19;【between and
包含两头】

–求学生总分 select sum(成绩) as 总分数 from xs

–37:至少选修了学生95002选修全部课程的学生号码 

r)查询全体学生情况,按所在系升序排列,同一个系的学生按年龄降序排列
select *from student order by sdept asc,sage desc;

–求分数最高分 select max(成绩) as 最高分 from xs

1:select Sno,Sname from Student 

s)查询选了1号课程的学生平均成绩
select cno, avg(grade) avgGrade from sc where cno=1 group by cno;

— 求最低分 select min(成绩) as 最低分 from xs

2:select Sno,Sname,Sdept from Student 

t)查询选了3号课程的学生的最高分
select max(grade) from sc where cno=3;

–求平均分 select avg(成绩) as 平均分 from xs

3:select * from Student /*也可以逐一列出列名并用逗号分隔*/ 

u)查询每个同学的总成绩
select sno, count(grade) from sc group by sno;

–统计有多少名学生 select count(成绩) as 人数 from xs

–其中getdate是获取当前系统时间。这是一个获取到的结果 :2008-12-11 16:02:17.967 

复杂查询
内容和主要步骤:
1)实验一中的数据为基础

—————————————分类汇总 group by –例1

–datepart从获取到的系统时间中分离出需要的部分,这里是分离出年份,更多信息请查看SQL Server联机帮助 

2) 对各表中的数据进行不同条件的连接查询和嵌套查询;

–查询学生表中有哪些专业 select distinct 所在系 from xs

–下面的 出生年 指定了别名来替代原来结果页出现的文字 

(1)?查询每个学生及其选课情况;
select * from sc;

–group by 实现 select 家乡 from N2D09003 group by 家乡

4:select Sname , (datepart( year,getdate())- Sage) ‘出生年’ from Student 

(2)?查询每门课的间接先修课
select a.cno,b.cpno from course a,course b where a.cpno=b.cno;

–例2 求每个地方的学生数 select 家乡,count (*) as 人数 from N2D09003
group by 家乡  –按照家乡  进行分类汇总

–该实例利用了小写转换函数lower() 提示:通过查询分析器的 公用对象 的 字符串函数中你可以找到这个函数 

(3)?将STUDENT,SC进行右连接
select *from student right join sc on student.sno=sc.sno;

–[例3]求每个地方 男生和女生的人数 select 家乡,性别,count(*) as 人数
from N2D09003 group by 家乡,性别  –按照家乡 和  性别 进行分类汇总

5:select Sname ‘姓名’ , (datepart( year,getdate())- Sage) ‘出生年’,lower(Sdept) ‘所在系’ from Student 

(4)?查询有不及格的学生姓名和所在系
select sname,sdept from student where sno in (select distinct sno from
sc where nvl(grade,0)<60);

/*` [特别注意:1:select 后面出现的列名,必须出现在group by 后面]
2:group by与order by连用,order by 子句中可包含聚合函数. 3、group
by关键字后可以使用多个字段名作为分组字段,这样,
系统将根据这些字段的先后顺序对结果集进行更加详细地分组。

6:select Sno  from sc –这里将返回全部结果,有重复的值 

(5)?查询所有成绩为优秀(大于90分)的学生姓名
select sname from student where sno in (select distinct sno from sc
where nvl(grade,0)>90);

–[例4]求每个地方的总人数,并且按照人数从多到少排序 select
家乡,count(*) as 人数 from N2D09003 group by 家乡 order by 人数 desc   
–这里的order by 后面可以是聚合函数(如果需要的话)

   select distinct Sno from sc–加入关键字distinct就可以去除重复结果,只留1个 

(6)?查询既选修了2号课程又选修了3号课程的学生姓名、学号;
 select sname,sno from student where sno  in (select sno from sc where
cno = 3 and cno in(select sno from sc where cno=2));

select * from xs order by max(年龄) dese –错误  不满足使用要求第二条

–sql 中默认对字符串大小写不敏感的,所以下面的sdept=’IS’你也可以写成sdept=’is’。如果你要启用大小写敏感,可以用下面的方法 

(7)?查询和刘晨同一年龄的学生
select *from student where sname<>’刘晨’ and sage in (select sage
from student where sname=’刘晨’);

————————————————————–09.12.04

if   敏感   
       select   *   from   table   where   field1=”AAA”   COLLATE   Chinese_PRC_CS_AS   
  else   
        select   *   from   table   where   field1=”AAA”   COLLATE   Chinese_PRC_CI_AS 
  COLLATE   的中文排序规则参数可用下列方式查到 
  SELECT   *   FROM   ::fn_helpcollations()   where   name   like   ‘Chinese%’ 

(8)?选修了课程名为“数据库”的学生姓名和年龄
select sname,sage from student  where sno in(select sno from sc where
cno in(select cno from course where cname=’数据库’))

————————————-group by …having
–作用:分类汇总后,再进行筛选 /*查询每个专业总人数,并且显示
总人数>3人的专业*/ select 所在系 , count(*) as  人数 from  xs
group  by 所在系 having count(*)>3  –筛选出人数>3人的专业

7:  select Sname from student where sdept=’IS’ 

(9)?查询其他系比IS系任一学生年龄小的学生名单
select sname from student where sdept<>’is’ and sage < (select
max(sage) from student where sdept=’is’)

——————–group by ….with rollup select 所在系, count(*) as
人数 from xs group by 所在系,性别 with rollup  –在分类汇总之后,再次汇总

8:  select Sname,Sage from student where Sage<20 

(10)?查询其他系中比IS系所有学生年龄都小的学生名单
select sname from student where sdept<>’is’ and sage < (select
min(sage) from student where sdept=’is’)

select 所在系,性别, count(*) as 人数 from xs group  by 所在系,性别 with
rollup  –在分类汇总之后,再次汇总

9:  select Sno from sc where grade<60 

(11)?查询选修了全部课程的学生姓名
 select sname from student where sno in(select sno from sc group by sno
having count(cno) = (select count(cno)from sc))

——————-group by …. with cube select 所在系,性别,count(*)
as 人数 from xs group by 所在系,性别 with cube –比rollup
汇总的更详细(按照 group by 后面的列进行再次汇总)

–如果要查询不在这个区间的记录,那只要改成 not between就可以了 

(12)?查询计算机系学生及其性别是男的学生
select *from student where sdept=’cs’ and ssex=’男’

————————————————链接查询
/*查找选修了课程号为2且成绩在80分以上的学生姓名和成绩*/ select
姓名,xx.成绩 from xs,xx where xs.学号=xx.学号  –两表链接条件 and
课程号=2 and xx.成绩>80

10:select Sname,Sdept,Sage from student where Sage between 20 and 23 

(13)?查询选修课程1的学生集合和选修2号课程学生集合的差集
select *from sc where cno=’1′ and sno not in(select sno from sc where
cno=’2′)

–加了 表名.列名
(一般无需在列名之前加表名前缀,只有当两个表有相同的列名时才加前缀)
select xs.姓名,xx.成绩 from xs,xx where xs.学号=xx.学号  –两表链接条件
and xx.课程号=2 and xx.成绩>80

–如果要查询不属于信息系、数学系和计算机科学系的,可以在in前面加上NOT 

(14)?查询李丽同学不学的课程的课程号
select cno from course where cno not in(select cno from sc where sno in
(select sno from student where sname=’李丽’))

———————–查询  刘德华的成绩 –方式一 省略前缀 select
xx.成绩 from xx,xs        where  xx.学号=xs.学号 and 姓名=’刘德华’

–也可以这样子写:select Sname,Ssex from student where Sdept=’is’ or sdept=’ma’ or sdept=’cs’ 

(15)?查询选修了3号课程的学生平均年龄
select avg(sage) from student where sno in(select sno from sc where
cno=3)

–方式二 嵌套查询 select 成绩 from xx where 学号=    ( select 学号 from
xs where 姓名=’刘德华’ )

11:select Sname,Ssex from student where Sdept in(‘IS’,’MA’,’CS’) 

(16)?求每门课程学生的平均成绩
select cno ,avg(grade) from sc group by cno;

–方式三 内联式查询 select  xx.成绩 from xx            join xs on 
xs.学号=xx.学号     –两表连接条件 where 姓名=’刘德华’            
–其他限制条件

–或者是select * from student where sno = ‘95001’ 

(17)?统计每门课程的学生选修人数(超过3人的才统计)。
要求输出课程号和选修人数,结果按人数降序排列,若人数相同,按课程号升序排列
select cno ,count(sno) from sc group by cno having count(sno)>3 order
by count(sno) desc,cno asc;

–查询林心如的古汉语成绩 select 姓名, 课程名 ,xx.成绩

12: select * from student where sno like ‘95001’:–like用于字符串匹配 

(18)?查询学号比刘晨大,而年龄比他小的学生姓名。
select a.sname from student a,(select sno,sname,sage from student where
sname=’刘晨’) b where a.sno>all b.sno and a.sage<all b.sage;

from xs  join  xx on  xs.学号=xx.学号          join  kc on 
kc.课程号=xx.课程号 and 姓名=’林心如’ and 课程名=’古汉语’

–百分号匹配多个字符,包括0个 

(19)?求年龄大于女同学平均年龄的男同学姓名和年龄
select sname,sage from student where sage  > (select avg(sage) from
student where ssex=’女’);

select 姓名 ,课程名 ,xx.成绩 from  xs,xx,kc where xs.学号=xx.学号 and
xx.课程号=kc.课程号 and 姓名=’林心如’ and 课程名=’古汉语’

13: select Sname,Sno,Sage  from student where sname like ‘林%’ 

(20)?求年龄大于所有女同学年龄的男同学姓名和年龄
select sname,sage from student where sage  >all (select sage from
student where ssex=’女’);

select 成绩 from xx where 课程号= ( select 课程号 from kc where
课程名=’古汉语’ ) and 学号= ( select 学号 from xs where 姓名=’林心如’ )

— 一个下划线匹配单个字符 

(21)?查询至少选修了95002选修的全部课程的学生号码
思想:首先查找学号为95002的同学的课程号集合,然后查找sc中所有学号在该集合中的选课信息,【此时该选课信息均为95002选过的课程信息】记为
msg
然后从msg中以学号进行分组,然后选出cno个数和95002选课个数相同的学号,该学号就是至少选修了95002选修的全部课程的学生学号
select sno from sc where sno<>95002
and
cno in (select cno from sc where sno=95002)
group by sno
having
count(cno)=(select count(cno) from sc where sno=95002)

–查询 学生
表所有记录(行) select *from 学生 –带条件的查询 select *from 学生
where 年龄19 ——————-2、查询…

14:select sname from student where sname like ‘欧阳_’ 

(22)?查询95001和95002两个学生都选修的课程的信息
select cno from sc where sno =95001 and cno in (select cno from sc where
sno=95002);

15:select sname,sno from student where sname like ‘_燕%’ 

更新查询题目:
1)    应用INSERT,UPDATE,DELETE语句进行更新操作;
a)    插入如下学生记录(学号:95030,姓名:李莉,年龄:18)
insert into student (sno,sname,sage) values (95030,’李莉’,18)

16:select sname from student where sname not like ‘刘%’ 

b)    插入如下选课记录(95030,1)
insert into sc (sno,cno) values(95030,1)

17:select Ccredit from course where cname like ‘DB\_DESIGN’ escape’\’ 

c)    计算机系学生年龄改成20
update student set sage=20  where sdept=’cs’;

–注意:这里不用使用 = null 

d)    数学系所有学生成绩改成0
update sc set grade = 0
where sno in (select sno from student where sdept=’ma’)

18:select sno,cno  from sc where grade is null 

e)    把低于总平均成绩的女同学成绩提高5分
update sc set grade = grade+5 where sno in (select sno from student
where ssex=’女’ and grade < (select avg(grade) from sc))

19:select sno,cno from sc where grade is not null 

f)    修改2号课程的成绩,若成绩小于75分提高5%,成绩大于75时提高%1
update sc set  grade = grade+grade*0.05 where grade<75
update sc set grade=grade+grade*0.01 where grade>75

20: select sname from student where sdept=’CS’ and sage<20 

4)(两个语句实现,注意顺序)
g)    删除95030学生信息
delete student where sno=95030

21:select sno,grade from sc where cno=3 order by grade desc 

h)    删除SC表中无成绩的记录
delete sc where grade is null;

22:select * from student order by sdept,sage desc 

i)    删除张娜的选课记录
delete sc where sno in (select sno from student where sname=’张娜’)

23::select count(*) from student 

j)    删除数学系所有学生选课记录
delete sc where sno in (select sno from student where sdept=’ma’)

24:select count(distinct sno) from sc 

k)    删除不及格的学生选课记录
delete sc where grade <60

25: select avg(grade) from sc where cno=’1′ 

l)  
 查询每一门课程成绩都大于等于80分的学生学号、姓名和性别,把值送往另一个已经存在的基本表STU(SNO,SNAME,SSEX)中
create table stu as (select sno,sname,ssex from student where sno
in(select sno from sc where sno not in(select distinct sno from sc where
grade<80)))