前言

首先在SQL Server 2008 中新建一個(gè)數(shù)據(jù)庫(kù)chaxun.在庫(kù)中建三個(gè)表,結(jié)構(gòu)如下表所示,并且錄入數(shù)據(jù).

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表數(shù)據(jù)：

insert into KC values('101','語(yǔ)文',4)
insert into KC values('102','數(shù)學(xué)',2)
insert into KC values('103','英語(yǔ)',3)
insert into KC values('104','體育',1)

XS表數(shù)據(jù)：

insert into XS values('1001','楊穎','信息技術(shù)系','2346666')
insert into XS values('1002','王麗','信息技術(shù)系','2346666')
insert into XS values('1003','張亮','經(jīng)管系','2315555')
insert into XS values('1004','劉強(qiáng)','會(huì)計(jì)系','2361111')

XS_KC表數(shù)據(jù)：

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表中查詢學(xué)分低于三分的課程信息，并按課程號(hào)升序排列

select * from KC
where C_Credit<3 order by c_ID ASC

(2)在XS_KC表中按學(xué)號(hào)分組匯總學(xué)生的平均分，并按平均分的降序排列

select s_ID,AVG(score) 平均分 from XS_KC 
group by s_ID
order by AVG(score) DESC

(3)在XS_KC表中查詢至少選修了2門(mén)課程的學(xué)生學(xué)號(hào)和姓名

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)查詢成績(jī)不及格的學(xué)生的基本信息

方法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號(hào)課程不及格的基本信息

子查詢

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表中查詢住在同一宿舍的學(xué)生信息，即其所住宿舍電話相同

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)查詢有多少名同學(xué)選修了課程

select COUNT(distinct s_ID ) 已選課人數(shù) from XS_KC

(9)查詢與楊穎同在一個(gè)系的同學(xué)姓名

方法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)查詢選修了課程的學(xué)生的姓名、課程名與成績(jī)

方法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)修改經(jīng)管系電話號(hào)碼為3615555

update XS set s_telephoe='3615555' where s_department='經(jīng)管系'

(12)統(tǒng)計(jì)每門(mén)課程的選課人數(shù)和最高分

select c_ID,COUNT(s_ID) 選課人數(shù),MAX(score) 最高分 from XS_KC group by c_ID

(13)統(tǒng)計(jì)每個(gè)學(xué)生的選課門(mén)數(shù)和考試總成績(jī)，并按選課門(mén)數(shù)的降序排列

select s_ID,COUNT(c_ID) 選課門(mén)數(shù),SUM(score) 總成績(jī) from XS_KC
group by s_ID order by COUNT(c_ID) DESC

(14)查詢有多少名學(xué)生沒(méi)有選課

select COUNT(s_ID) 未選課人數(shù) from XS where s_ID not in 
(select s_ID from XS_KC)

總結(jié)

今天的數(shù)據(jù)庫(kù)學(xué)習(xí)內(nèi)容就到這里啦,如果對(duì)友友們有幫助的話,記得點(diǎn)贊收藏博客,關(guān)注后續(xù)的數(shù)據(jù)庫(kù)學(xué)習(xí)內(nèi)容哦~??????
文章來(lái)源地址http://www.zghlxwxcb.cn/news/detail-418337.html

到了這里，關(guān)于SQL Server 數(shù)據(jù)查詢的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！