面试准备之SQL 4—— 数据查询

代码

use  master
go
-- ---------创建数据库------------

if   exists  ( select   *   from  sysdatabases  where  name = ' stuDB ' )
drop   database  stuDB
create   database  stuDB
on   primary
(
name = ' stuDB_data ' ,
filename = ' D:\stuDB_data.mdf ' ,
size = 3mb,
maxsize = 10mb,
filegrowth = 1mb
)
log   on
(
name = ' stuDB_log ' ,
filename = ' D:\stuDB_data.ldf ' ,
size = 1mb,
filegrowth = 1mb
)

-- ---------创建数据库表------------
use  stuDB
go
if   exists  ( select   *   from  sysobjects  where  name = ' stuInfo ' )
drop   table  stuInfo
create   table  stuInfo
(
    stuId  int   identity ( 1 , 1 )  primary   key   not   null ,
    stuName  varchar ( 20 )  not   null ,
    stuNo  varchar ( 20 )  not   null ,
    stuSex  char ( 6 )  not   null ,
    stuAge  int   not   null ,
    stuAddress  text   null
)
go

if   exists  ( select   *   from  sysobjects  where  name = ' stuMarks ' )
drop   table  stuMarks
create   table  stuMarks
(
    marksId  int   identity ( 1 , 1 )  primary   key   not   null ,
    ExamNo  varchar ( 50 )  not   null ,  -- 考号
    stuNo  char ( 6 )  not   null , -- 学号
    writtenExam  int    null , -- 笔试成绩
    LabExam  int   null -- 机试成绩
)
go
-- 向学员信息表stuInfo插入数据--
INSERT   INTO  stuInfo(stuName,stuNo,stuSex,stuAge,stuAddress) VALUES ( ' 张秋丽 ' , ' s25301 ' , ' 男 ' , 18 , ' 北京海淀 ' )
INSERT   INTO  stuInfo(stuName,stuNo,stuSex,stuAge,stuAddress)  VALUES ( ' 李斯文 ' , ' s25303 ' , ' 女 ' , 22 , ' 河南洛阳 ' )
INSERT   INTO  stuInfo(stuName,stuNo,stuSex,stuAge)  VALUES ( ' 李文才 ' , ' s25302 ' , ' 男 ' , 31 )
INSERT   INTO  stuInfo(stuName,stuNo,stuSex,stuAge,stuAddress)  VALUES ( ' 欧阳俊雄 ' , ' s25304 ' , ' 男 ' , 28 , ' 威武哈 ' )

-- 向学员成绩表stuMarks插入数据--
INSERT   INTO  stuMarks(ExamNo,stuNo,writtenExam,LabExam)  VALUES ( ' E2005070001 ' , ' s25301 ' , 80 , 58 )
INSERT   INTO  stuMarks(ExamNo,stuNo,writtenExam)  VALUES ( ' E2005070002 ' , ' s25302 ' , 50 )
INSERT   INTO  stuMarks(ExamNo,stuNo,writtenExam,LabExam)  VALUES ( ' E2005070003 ' , ' s25303 ' , 97 , 82 )

-- 查看数据--
select   *   from  stuInfo
select   *   from  stuMarks

/* =======查询数据练习========= */  
-- 1.查询两表的数据--
select   *   from  stuInfo
select   *   from  stuMarks
  -- 2.查询男学员名单-- 
select   *   from  stuInfo  where  stuSex = ' 男 '
  -- 3.查询笔试成绩优秀的学员情况（成绩在75~100之间)-- 
select   *   from  stumarks  where  writtenexam  between   75   and   100
  -- 4.查询参加本次考试的学员成绩，包括学员姓名，笔试成绩，机试成绩-- 
select  i.stuName,m.writtenExam,m.LabExam  from  stuInfo  as  i  inner   join  stuMarks  as  m  on  m.stuNo  =  i.stuNo
  -- 5.统计笔试考试平均分和机试考试平均分-- 
select   avg (writtenExam)  as  笔试平均成绩, avg (LabExam)  as  机试平均成绩  from  stuMarks
select   avg (writtenExam) 笔试平均成绩, avg (LabExam) 机试平均成绩  from  stuMarks
  -- 6.统计参加本次考试的学员人数 
select   count (stuno)  from  stumarks 
  -- 7.查询没有通过考试的人数（笔试或机试小于60分）-- 
select   count (stuno)  from  stumarks  where  writtenExam  <=   60   or  labexam <= 60
select   *   from  stumarks  where  writtenExam  is   null   or  labexam  is   null   -- 查询为全部参加考试的信息
  -- 8.查询学员成绩，显示学号，笔试成绩，机试成绩，平均分-- 
select  stuno  as  学号,writtenExam 笔试,labexam 机试,(writtenExam + labexam) / 2  平均成绩  from  stumarks
  -- 9.排名次（按平均分从高到低排序），显示学号、平均分--  
select  stuno  as  学号,(writtenExam + labexam) / 2  平均成绩  from  stumarks  order   by  (writtenExam + labexam) / 2   desc

select  stuno  as  学号,(writtenExam + labexam) / 2  平均成绩  from  stumarks  order   by  平均成绩  desc
  -- 10.排名次（按平均分从高到低排序），显示姓名，笔试成绩，机试成绩，平均分--
select  i.stuno  as  学号,writtenExam 笔试,labexam 机试,(writtenExam + labexam) / 2  平均成绩  
from  stumarks  as  m  inner   join  stuinfo  as  i  on  m.stuno  =  i.stuno  order   by  平均成绩  desc
-- 根据以上SQL语句总结：凡是两个表中有同名的列名就需要用别名却分开来，如果没用别名可以直接查询列明

  -- 11.根据平均分，显示前两名信息，包括姓名、笔试成绩、机试成绩、平均分-- 
select   top   2  i.stuno  as  学号,writtenExam 笔试,labexam 机试,(writtenExam + labexam) / 2  平均成绩  
from  stumarks  as  m  inner   join  stuinfo  as  i  on  m.stuno  =  i.stuno  order   by  平均成绩  desc
/* =======修改数据练习========= */  
-- 都提5分--
-- 100分封顶（加分后超过100分的，按100分计算）--

update  stumarks  set  writtenExam  =  writtenExam  +   5
update  stumarks  set  writtenExam  =   100   where  writtenExam > 100
 

 

转载于:https://www.cnblogs.com/Simcoder/archive/2010/04/15/1712911.html