前言
首先在SQL Server 2008 中新建一个数据库chaxun.在库中建三个表,结构如下表所示,并且录入数据.
1.
CREATE DATABASE chaxun
ON PRIMARY
(NAME=chaxun_data,
FILENAME='D:\software\chaxun.mdf',
SIZE=10MB,
MAXSIZE=UNLIMITED,
FILEGROWTH=10%
)
LOG ON
(NAME=chaxun_log,
FILENAME='D:\software\chaxun.ldf',
SIZE=1MB,
MAXSIZE=5MB,
FILEGROWTH=1MB
)
use chaxun
create table KC
(
c_ID char(3) primary key,
c_Name char(10) not null,
c_Credit int
)
create table XS
(
s_ID char(4) primary key,
s_Name char(6) not null,
s_department char(20) not null,
s_telephoe char(11)
)
create table XS_KC
(
s_ID char(4) foreign key references XS(s_ID),
c_ID char(3) foreign key references KC(c_ID),
score int,
primary key(s_ID,c_ID)
)
KC表数据:
insert into KC values('101','语文',4)
insert into KC values('102','数学',2)
insert into KC values('103','英语',3)
insert into KC values('104','体育',1)
XS表数据:
insert into XS values('1001','杨颖','信息技术系','2346666')
insert into XS values('1002','王丽','信息技术系','2346666')
insert into XS values('1003','张亮','经管系','2315555')
insert into XS values('1004','刘强','会计系','2361111')
XS_KC表数据:
insert into XS_KC values('1001','101',89)
insert into XS_KC values('1001','102',97)
insert into XS_KC values('1001','103', 86)
insert into XS_KC values('1002', '101',56)
insert into XS_KC values('1003', '102',55)
2.
(1)在KC表中查询学分低于三分的课程信息,并按课程号升序排列
select * from KC
where C_Credit<3 order by c_ID ASC
(2)在XS_KC表中按学号分组汇总学生的平均分,并按平均分的降序排列
select s_ID,AVG(score) 平均分 from XS_KC
group by s_ID
order by AVG(score) DESC
(3)在XS_KC表中查询至少选修了2门课程的学生学号和姓名
select s_Name,XS.s_ID from XS_KC,XS where XS.s_ID=XS_KC.s_ID
group by XS.s_ID,s_Name having COUNT(XS_KC.c_ID)>=2
(4)查询成绩不及格的学生的基本信息
方法1
select XS.* from XS,XS_KC
where XS.s_ID=XS_KC.s_ID and score<60
方法2(子查询)
select * from XS where s_ID in (select s_ID from XS_KC where score<60)
(5)分别用子查询和连接查询,查询101号课程不及格的基本信息
子查询
select * from XS where s_ID in(select distinct s_ID from XS_KC
where XS_KC.c_ID='101' and score <60)
连接查询
select XS.* from XS join XS_KC on XS.s_ID=XS_KC.s_ID
where XS_KC.c_ID='101' and score <60
(6)在XS表中查询住在同一宿舍的学生信息,即其所住宿舍电话相同
select * from XS where s_telephoe in
(select s_telephoe from XS group by s_telephoe having COUNT(s_telephoe)>=2)
(7)查询XS表中的所有系名
select distinct s_department from XS
(8)查询有多少名同学选修了课程
select COUNT(distinct s_ID ) 已选课人数 from XS_KC
(9)查询与杨颖同在一个系的同学姓名
方法1
select x.s_Name from XS as x,XS as y
where x.s_department=y.s_department and y.s_Name='杨颖' and x.s_Name<>'杨颖'
方法2
select s_Name from XS where s_department=
(select s_department from XS where s_Name='杨颖') and s_Name <>'杨颖'
(10)查询选修了课程的学生的姓名、课程名与成绩
方法1
select s_Name,c_Name,score from XS,KC,XS_KC
where XS.s_ID=XS_KC.s_ID and KC.c_ID=XS_KC.c_ID
方法2
select s_Name,c_Name,score from XS join XS_KC on XS.s_ID=XS_KC.s_ID
join KC on KC.c_ID=XS_KC.c_ID
(11)修改经管系电话号码为3615555
update XS set s_telephoe='3615555' where s_department='经管系'
(12)统计每门课程的选课人数和最高分
select c_ID,COUNT(s_ID) 选课人数,MAX(score) 最高分 from XS_KC group by c_ID
(13)统计每个学生的选课门数和考试总成绩,并按选课门数的降序排列
select s_ID,COUNT(c_ID) 选课门数,SUM(score) 总成绩 from XS_KC
group by s_ID order by COUNT(c_ID) DESC
(14)查询有多少名学生没有选课
select COUNT(s_ID) 未选课人数 from XS where s_ID not in
(select s_ID from XS_KC)
文章来源:https://www.toymoban.com/news/detail-418337.html
总结
今天的数据库学习内容就到这里啦,如果对友友们有帮助的话,记得点赞收藏博客,关注后续的数据库学习内容哦~👻👻👻
文章来源地址https://www.toymoban.com/news/detail-418337.html
到了这里,关于SQL Server 数据查询的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!