1. 查询单价在10到20之间、印刷数量大于5000的“外语”类图书的书名、单价和印刷数量。
select sm,dj,yssl
from tsb
where yssl>5000 and lb ='外语'and dj between 10 and 20
题型:常规
2. 查询店名为“王府井书店”的进书情况,列出图书的书名、进书数量及进书日期。
select sm,jssl,jsrq
from dbo.tsb a join dbo.jsb b on a.isbn =b.isbn join dbo.sdb c on b.sdbh=c.sdbh
where sddm ='王府井书店'
题型:常规,根据题意,多表连接,1个条件
3.查询地址在“海淀区”的各书店2001年1月1日以后的详细进书情况,列出书店的名称、每次进书的书名、进书日期和进书数量,
要求查询结果按每次进每本书的数量从多到少的顺序排列。
select sddm,sm,jsrq,jssl
from dbo.tsb a join dbo.jsb b on a.isbn =b.isbn join dbo.sdb c on b.sdbh=c.sdbh
where dz like '%海淀区%' and jsrq >'2001-01-01'
order by jssl desc
题型:常规,多表连接,两个条件,排序
4. 查询哪些类别的图书在“王府井书店”从没有进过,列出图书的类别。
select distinct lb --
from tsb
where lb not in(select lb
from dbo.tsb a join dbo.jsb b on a.isbn =b.isbn join dbo.sdb c on b.sdbh=c.sdbh
where sddm ='王府井书店'
)
题型:没有型。条件A满足B条件下没有做的事情,三步走
第一步根据题意,要列出的字段,即select语句中需出现的字段,在哪个表中
第二步,满足B条件的select 语句
第三步,A not in (满足B条件的select 语句)
5. 新筹建一个书店,编号为“S111”,书店名为“当代书城”,地址和电话还没有确定,请将此书店信息插入到书店表中。
insert into sdb (sdbh ,sddm )
values('S111','当代书城')
题型:常规,注意对应顺序
6. 将“计算机”类图书的单价高于“计算机”类图书的平均单价超过50元的图书的单价减10元。
update tsb set dj=dj-10
where dj >((select AVG(dj)+50
from tsb
where lb ='计算机'))
题型:常规,往往会加个子查询,update 表名 set 字段=表达式
From 表格 ------------------------可以省略
Where 条件------------------------可以省略
--1. 查询姓“张”和姓“王”的客户的详细信息。
select * from khb where khm like '[张王]%'
题型:常规,可以用字符串left和right函数来做,但还是上面的方法简单。
--2. 查询2008年每个月每个商品的销售总数量,列出月份、商品号和销售总数量,结果按月份和商品号升序排序
select month(xsrq), sph, sum(xssl) from xsb
where year(xsrq) = 2008
group by month(xsrq),sph
order by month(xsrq),sph
题型:常规
--3. 查询被卖出次数最少的三种商品(包括没有被卖出过的商品,包括并列的情况),列出商品名、类别和进价。
select spm, lb, jj from spb
where sph in (
select top 3 with ties a.sph from spb a left join xsb b on a.sph = b.sph
group by a.sph
order by count(b.sph) asc)
题型:排序型。子查询,蓝色标记的字段语句,最少词用top,并列用with ties,分组排序,然后列出显示的字段和来自的表,用商品号做和子查询连接字段。
--4. 查询在2008年3月到8月期间没有被卖出过的“电脑”类商品的名称和进价。
select spm, jj from spb where sph not in (
select sph from xsb
where xsrq between '2008/3/1' and '2008/8/31')
and lb = '电脑'
题型:没有型
1、显示的字段和来自的表格
2、满足B的条件是:期间 所以 从销售表中提取商品号,条件是满足题中要求的日期
3、用商品号做连接
--5. 查询至少购买了“电视”和“冰箱”两类商品的客户名、购买的商品名和类别。
select distinct khm,spm,lb
from khb a join xsb b on a.khh = b.khh
join spb c on c.sph = b.sph
where a.khh in (
select khh from xsb join spb on xsb.sph = spb.sph
where lb = '电视')
and a.khh in (
select khh from xsb join spb on xsb.sph = spb.sph
where lb = '冰箱')
题型:至少型。
步骤:第一步,列出要显示的字段,判断各自表格,进行连接
第二步,由于列别(lb)不能既是电视,又是冰箱,必须找一个字段来表示,商品号也不行,于是客户号
第三步,两个子句间用and运算,说明既在和又在。
--6. 在客户表中插入一新记录,客户号为“K100”,客户名为“新客户”,积分用默认值。
insert into khb(khh,khm) values('K100','新客户')
题型:常型
--7. 将销售总数量超过1000的商品的进价降低10%。
update spb set jj = jj * 0.9
where sph in (
select sph from xsb group by sph having sum(xssl) > 1000)
题型:常规:修改。往往会加个子查询
--1、查询2006年以后(包括2006年)的投稿情况,显示作者名、期刊名、文章名称和投稿日期。
select zzm,qkm,wzmc,tgrq
from zzb join tgb on zzb.zzh=tgb.zzh
join qkb on tgb.qkh=qkb.qkh
where tgrq>='2006/1/1'
常规:多表,条件1
--2、查询姓哪个姓的作者最多,列出这个姓氏和姓这个姓氏的作者人数,包含并列的情况。
select top 1 with ties left(zzm,1),count(*)
from zzb
group by left(zzm,1)
order by count(*) desc
题型:排序型。用top 来做,数量一般用count(*)来做。
--3、查询作者“杨伍华”没有投过稿的核心期刊的名称
select qkm
from qkb
where lb='是' and qkh not in
(select qkh
from zzb join tgb on zzb.zzh=tgb.zzh
where zzm='杨伍华' )
题型:没有型。A在满足B条件没有,子查询。
步骤:第一步:分析A和B,换个句子,哪些核心期刊作者“杨伍华”没有投过稿,列出名称,题中A为核心期刊,qkh和lb是, B为杨伍华。
第二步,题意名称字段和所属表格
第三步,select qkh 满足作者为杨伍华的期刊,类别不在以上的就求得题解
--4、查询“杨伍华”投过稿的期刊的投稿情况,显示期刊号,审稿通过总次数,
版面费总金额(说明:只有审稿结果为"通过"时,作者才需向期刊缴纳相应版面费),
--要求只列出投稿次数超过5次(不包括5次)且版面费总金额大于7000的情况。
select tgb.qkh,count(*),sum(bmf)
from tgb join qkb on tgb.qkh=qkb.qkh
where sgjg='通过' and tgb.qkh in
(select qkh
from tgb join zzb on tgb.zzh=zzb.zzh
where zzm='杨伍华')
group by tgb.qkh
having count(*)>5 and sum(bmf)>7000
题型:多条件综合类型
步骤:第一步:明确要显示的字段以及来自的表格。
第二步:考虑说明的条件
第三步:以期刊号作分组,后面的条件在分组的基础上再设条件
第四步:杨伍华投过稿的期刊号,做一个子查询。
--5、查询全体作者(包括还没有投过稿的作者)的人均投稿次数,列出总投稿次数、总人数和人均投稿次数.
--要求平均投稿次数保存到小数点后2位
select count(tgb.zzh),count(distinct zzb.zzh),
cast(count(tgb.zzh)*1.0/count(distinct zzb.zzh) as numeric(4,2))
from zzb left join tgb on zzb.zzh=tgb.zzh
题型:平均次数型
步骤:第一步:显示字段
第二步:count总投稿表中的次数,count作者表中的人数,有重复,必须用distinct
cast(count(tgb.zzh)*1.0/count(distinct zzb.zzh) as numeric(4,2)),类似的条件必须记住。
第三步:作者表左连接投稿表,是因为还有作者没有投过表,也要统计进去。
--6、向投稿表中插入一条记录,作者号为“zz01”,期刊号为“qk02”,文章名称为“地铁限界系统的研究”,
--投稿日期和审稿结果为默认值。
insert into tgb(zzh,qkh,wzmc)
values('zz01','qk02','地铁限界系统的研究')
题型:常规
--7、删除投稿表中2000年以前(包括2000年),期刊名为"哲学研究"中审稿未通过的的投稿记录。
delete from tgb
from tgb join qkb on tgb.qkh=qkb.qkh
where sgjg='未通过' and year(tgrq)<=2000 and qkm='哲学研究'
题型:常规
--1. 查询姓“张”和姓“王”的客户的详细信息。
select * from khb where khm like '[张王]%'
题型:常规
--2. 查询2008年每个月每个商品的销售总数量,列出月份、商品号和销售总数量,结果按月份和商品号升序排序
select month(xsrq), sph, sum(xssl) from xsb
where year(xsrq) = 2008
group by month(xsrq),sph
order by month(xsrq),sph
题型:常规
--3. 查询被卖出次数最少的三种商品(包括没有被卖出过的商品,包括并列的情况),列出商品名、类别和进价。
select spm, lb, jj from spb
where sph in (
select top 3 with ties a.sph from spb a left join xsb b on a.sph = b.sph
group by a.sph
order by count(b.sph) asc)
题型:排序型.top
步骤:第一步:考虑显示的字段
第二步: top 3, order by count(sph) asc,spb和xsb 左连接,左边是全部的商品,右边是销售情况。
第三步:用sph做连接。
--4. 查询在2008年3月到8月期间没有被卖出过的“电脑”类商品的名称和进价。
select spm, jj from spb where sph not in (
select sph from xsb
where xsrq between '2008/3/1' and '2008/8/31')
and lb = '电脑'
题型:没有型。
步骤:第一步,分析AB。与哪些核心期刊作者“杨伍华”没有投过稿,列出名称相同,哪些“电脑”类商品的名称和进价没有卖出过,列出名称和进价。B为电脑类的商品的名称和进价,A为商品号。
第二步:要显示的字段
第三步:找字段连接。
--5. 查询至少购买了“电视”和“冰箱”两类商品的客户名、购买的商品名和类别。
select distinct khm,spm,lb from khb a join xsb b on a.khh = b.khh join spb c on c.sph = b.sph
where a.khh in (
select khh from xsb join spb on xsb.sph = spb.sph
where lb = '电视')
and a.khh in (
select khh from xsb join spb on xsb.sph = spb.sph
where lb = '冰箱')
题型:至少型。
步骤:1、要显示的字段,
2、类别不能同时为电视和冰箱,所以必须找一个字段来连接,商品号不行,针对题意商品号可以。中间用and运算。
--6. 在客户表中插入一新记录,客户号为“K100”,客户名为“新客户”,积分用默认值。
insert into khb(khh,khm) values('K100','新客户')
题型:常规
--7. 将销售总数量超过1000的商品的进价降低10%。
update spb set jj = jj * 0.9
where sph in (
select sph from xsb group by sph having sum(xssl) > 1000)
题型:常规:往往会有一个子查询
--1、查询2010年以前单笔销售数量最少的销售记录
--子查询
select *
from xsqkb
where year(xsrq)<2010 and xssl=(
select min(xssl)
from xsqkb
where year(xsrq)<2010
)
--使用top
select top 1 with ties *
from xsqkb
where year(xsrq)<2010
order by xssl
题型:排序型
--2、查询由多个产地生产的相同类别的产品,列出产品名称,类别及产地
--子查询
select cpmc,lb,cd
from cpb
where lb in(
select lb
from cpb
group by lb
having count(distinct cd)>1
)
--自连接
select distinct a.cpmc,a.lb,a.cd
from cpb a join cpb b on a.lb=b.lb and a.cd<>b.cd
题型:多产地同类型
步骤:1、要显示的内容,是否涉及不同的表
2、如果只是一张表,那最好用自连接,连接条件是类别相同,产地不同,
3、显示的时候需去重
或者也可以用 having count(distinct cd)>1的子查询来做。
--3、查询只销售过"电冰箱"类产品的销售人员信息。
select *
from xsryb
where zgh in (
select zgh
from xsqkb a join cpb b on a.cph=b.cph
where b.lb='电冰箱'
) and zgh not in (
select zgh
from xsqkb a join cpb b on a.cph=b.cph
where b.lb<>'电冰箱'
)
题型:只有型
步骤:1、要显示的字段
2、分析有交集
--4、查询销售过"创新牌音响"的销售人员的销售记录次数及平均销售数量,要求只列出销售记录次数大于2次且平均销售数量大于2件的情况
select zgh,count(*),avg(xssl)
from xsqkb
where zgh in (
select a.zgh
from xsqkb a join cpb b on a.cph=b.cph
where b.cpmc='创新牌音响'
)
group by zgh
having count(*)>2 and avg(xssl)>2
题型:多条件型。Having设条件
--5、查询2001年每个月每个产品的销售总数量,列出月份、产品号和销售总数量,结果先按月份升序排序,再按销售数量降序排序
select month(xsrq),cph,sum(xssl)
from xsqkb
where year(xsrq)=2001
group by month(xsrq),cph
order by month(xsrq) asc,sum(xssl) desc
题型:常规
--6、查询销售次数大于平均销售次数的人员信息。
select *
from xsryb
where zgh in (
select zgh
from xsqkb
group by zgh
having count(*)>(
select count(*)/count( distinct zgh)
from xsryb
)
)
题型:平均型,以职工号作为连接的子查询
--7、列出"张晓峰"没有销售过的产品名称及价格信息(不能多表连接查询)
select cpmc,jg
from cpb
where cph not in(
select b.cph
from xsryb a join xsqkb b on a.zgh=b.zgh
where a.xm='张晓峰'
)
题型:没有型。分A和B,
--8、查询销售过所有产品的销售人员的职工号
select zgh
from xsqkb
group by zgh
having count(distinct cph)=(
select count(cph)
from cpb
)
题型:所有型。待分析
--9、查询被卖出次数最少的前10%的产品(包括没有被买出过的产品,包括并列的情况),列出产品名称
select cpmc
from cpb
where cph in (
select top 10 percent with ties a.cph
from cpb a left join xsqkb b on a.cph=b.cph
group by a.cph
order by count(b.cph)
)
--讨论
select top 10 percent with ties a.CPMC
from CPB a left join XSQKB b on a.CPH=b.CPH
group by a.CPH,a.CPMC
order by COUNT(a.cph)
题型:排序型。左连接或子查询
--10、查询产品表按价格降序排序后排在第6~10位的产品信息
select top 5 *
from cpb
where cph not in (
select top 5 cph
from cpb
order by jg desc
)
order by jg desc
题型:排序型。取中间段显示
--11、将销售人员表中邮政编码列的类型修改为统一编码定长字符型,6位长,不能为空
alter table xsryb
alter column yzbm nchar(6) not null
题型:常型:记住列类型改变用 column
--12、根据产品销售数量对产品价格进行调整。产品销量为0,不调整;产品销量小于5,产品价格下调5%;产品销量在5到10之间,产品价格下调10%;产品销量大于10,产品价格下调15%。
--子查询
update cpb
set jg=jg*(
select case
when sum(xssl) is null then 1
when sum(xssl)<5 then 0.95
when sum(xssl) between 5 and 10 then 0.9
when sum(xssl)>10 then 0.85
end
from cpb a left join xsqkb b on a.cph=b.cph
where a.cph=cpb.cph
group by a.cph
)
--多表连接
create view v4
as
select a.cph,tf=
case
when sum(xssl) is null then 1
when sum(xssl)<5 then 0.95
when sum(xssl) between 5 and 10 then 0.9
when sum(xssl)>10 then 0.85
end
from cpb a left join xsqkb b on a.cph=b.cph
group by a.cph
update cpb
set jg=jg*b.tf
from cpb a join v4 b on a.cph=b.cph
1. 查询单价在10到20之间、印刷数量大于5000的“外语”类图书的书名、单价和印刷数量。
select SM,dj,YSSL from TSB
where dj between 10 and 20 and YSSL > 5000
and lb = '外语'
题型:常型:1表3条件
2. 查询店名为“王府井书店”的进书情况,列出图书的书名、进书数量及进书日期
select SM, jYSSL, JSRQ from TSB a join JSB b on a.isbn = b.isbn
join SDB c on b.SDBh = c.SDBh
where SDDM = '王府井书店'
题型:常型:多表1条件
3.查询地址在“海淀区”的各书店2001年1月1日以后的详细进书情况,列出书店的名称、
每次进书的书名、进书日期和进书数量,要求查询结果按每次进书的数量从多到少的顺序排列。
select SDDM, SM, JSRQ, jYSSL
from TSB a join JSB b on a.isbn = b.isbn
join SDB c on b.SDBh = c.SDBh
where DZ like '%海淀区%' and JSRQ>='2001/1/1'
order by jYSSL desc
题型:常型:多表2条件排序
4. 查询哪些类别的图书在“王府井书店”从没有进过,列出图书的类别。
select distinct lb from TSB
where lb not in(
select lb from JSB a join SDB b
on a.SDBh = b.SDBh
join TSB on TSB.isbn = JSB.isbn
where SDDM ='王府井书店')
题型:没有型
5. 新筹建一个书店,编号为“S111”,书店名为“当代书城”,地址和电话还没有确定,
请将此书店信息插入到书店表中。
insert into SDB(SDBh, SDDM) values('s111','当代书城')
题型:常型
6. 将“计算机”类图书的单价高于“计算机”类图书的平均单价超过50元的图书的单价减10元。
update TSB set dj = dj - 10
where lb = '计算机'
and dj >(
select avg(dj) +50 from TSB where lb = '计算机')
题型:常型。往往加个子查询。
1. 查询2001年12月31日之后的销售情况,要求列出销售人员姓名、销售的产品名以及销售日期。
SELECT XM,CPM,XSRQ FROM XSRYB a join XSQKB b on a.ZGH = b.ZGH
join CPB c on b.CPH = c.CPH
WHERE XSRQ > '2001/12/31'
题型:常型。多表1条件
2. 查询销售电冰箱的销售人员的最大年龄。
SELECT max(NL ) from XSRYB a join XSQKB b on a.ZGH = b.ZGH
join CPB c on b.CPH = c.CPH
WHERE CPM = '电冰箱'
题型:排序型。多表1条件
3. 统计每个产品的销售总数量,要求只列出销售数量前3名的产品号和销售数量。
SELECT TOP 3 WITH TIES CPH,SUM(XSSL) FROM XSQKB
GROUP BY CPH
ORDER BY SUM(XSSL) DESC
题型:常型
4. 查询销售人员的销售情况,包括有销售记录的销售人员和没有销售记录的销售人员,
要求列出销售人员姓名、销售的产品号、销售数量和销售日期。
select XM,CPH,XSSL,XSRQ FROM XSRYB a
left join XSQKB b on a.ZGH = b.ZGH
题型:常型。左连接
5.列出2000年1月1日以后销售总量第一的产品的名称和生产厂家。
SELECT CPM,SCCJ from CPB a
WHERE a.CPH IN (SELECT TOP 1 CPH FROM XSQKB
WHERE XSRQ > '2000/1/1'
GROUP BY CPH
ORDER BY SUM(XSSL) DESC)
题型:排序型。
步骤:3步。
6.将生产厂家为“天津”的产品的价格降低200。
UPDATE CPB SET JG = JG - 200
WHERE SCCJ = '天津'
题型:常型
7.删除销售生产厂家为“青岛”的产品的销售记录。
DELETE FROM XSQKB WHERE CPH IN(
SELECT CPH FROM CPB WHERE SCCJ = '青岛')
题型:常型
8. 将职工号为'G09',姓名为'RY9',年龄为30的销售人员插入到销售人员表中。
INSERT INTO XSRYB(ZGH,XM,NL) VALUES('G09','RY9',30)
题型:常型
1.查询至少销售过“电冰箱”和“音响”的销售人员的姓名、销售数量、价格,按销售人员姓名降序排列。
SELECT XM,XSSL,JG
FROM XSRYB AS XR INNER JOIN XSQKB AS XQ ON XR.ZGH=XQ.ZGH
INNER JOIN CPB AS C ON C.CPH=XQ.CPH
WHERE XQ.ZGH IN
(
SELECT XQ.ZGH
FROM CPB AS C INNER JOIN XSQKB XQ ON C.CPH=XQ.CPH
WHERE CPM IN ('电冰箱','音响')
GROUP BY XQ.ZGH
HAVING COUNT (XQ.CPH)>1
)
ORDER BY XM DESC
/*如果在最外层查询加上条件CPM IN ('电冰箱','音响'),可以更好对照*/
SELECT XM,XSSL,JG
FROM XSRYB AS XR INNER JOIN XSQKB AS XQ ON XR.ZGH=XQ.ZGH
INNER JOIN CPB AS C ON C.CPH=XQ.CPH
WHERE CPM IN ('电冰箱','音响') AND XQ.ZGH IN
(
SELECT XQ.ZGH
FROM CPB AS C INNER JOIN XSQKB XQ ON C.CPH=XQ.CPH
WHERE CPM IN ('电冰箱','音响')
GROUP BY XQ.ZGH
HAVING COUNT (XQ.CPH)>1
)
ORDER BY XM DESC
2.查询只销售过“电冰箱”的销售人员信息。
SELECT *
FROM XSRYB
WHERE ZGH IN
(
SELECT ZGH
FROM CPB INNER JOIN XSQKB ON CPB.CPH=XSQKB.CPH
WHERE CPM='电冰箱'
AND ZGH IN
(
SELECT ZGH
FROM XSQKB
GROUP BY ZGH
HAVING COUNT(CPH)=1
)
)
3.查询被卖出次数最少的前50%的产品(包括没有被买出过的产品,包括并列的情况),列出产品名称
SELECT CPM
FROM CPB
WHERE CPH IN
(
SELECT TOP 50 PERCENT WITH TIES C.CPH
FROM CPB AS C LEFT OUTER JOIN XSQKB AS XQ ON C.CPH=XQ.CPH
GROUP BY C.CPH
ORDER BY SUM(XSSL) ASC
)
4.查询与“电冰箱”价格相同的其它产品的信息。
SELECT C2.CPH,C2.CPM,C2.CD,C2.JG,C2.SCRQ
FROM CPB AS C1 INNER JOIN CPB AS C2 ON C1.JG=C2.JG
WHERE C1.CPM='电冰箱' AND C1.CPM!=C2.CPM
5.查询由多个产地生产的产品,列出产品名称及产地。
SELECT CPM,CD
FROM CPB
WHERE CPM IN
(
SELECT CPM
FROM CPB
GROUP BY CPM
HAVING COUNT(CD)>1
)
6.查询销售次数大于平均销售次数的人员信息。
SELECT *
FROM XSRYB
WHERE ZGH IN
(
SELECT ZGH
FROM XSQKB
GROUP BY ZGH
HAVING SUM(XSSL)>
(
SELECT AVG(XSSL)
FROM XSQKB
)
)
7.统计每个销售人员的销售情况,列出人员姓名、销售总金额和业绩。如果销售总金额大于1万元,则业绩为“较好”;如果总销售金额在5000元至1万元之间,则业绩为“一般”;如果销售总金额小于5000元,则业绩为“较差”。如果没有销售记录,则业绩为“无”。(提示:总销售金额=销售数量*价格)
SELECT XS.ZGH,XM AS 人员姓名,销售金额=SUM(JG*XSSL),
业绩=
CASE
WHEN SUM(JG*XSSL)>10000 THEN '较好'
WHEN (SUM(JG*XSSL) BETWEEN 5000 AND 10000) THEN '一般'
WHEN SUM(JG*XSSL)<5000 THEN '较差'
ELSE '无'
END
FROM XSRYB XS FULL OUTER JOIN XSQKB AS XQ ON XS.ZGH=XQ.ZGH
FULL OUTER JOIN CPB C ON C.CPH=XQ.CPH
WHERE XS.ZGH IS NOT NULL
GROUP BY XS.ZGH,XM
/*若用内连接,则只显示销售情况表中的每个销售人员的销售情况,可与上述实现方法
做比较*/
SELECT XS.ZGH,XM AS 人员姓名,销售金额=SUM(JG*XSSL),
业绩=
CASE
WHEN SUM(JG*XSSL)>10000 THEN '较好'
WHEN (SUM(JG*XSSL) BETWEEN 5000 AND 10000) THEN '一般'
ELSE '较差'
END
FROM XSRYB XS INNER JOIN XSQKB AS XQ ON XS.ZGH=XQ.ZGH
INNER JOIN CPB C ON C.CPH=XQ.CPH
GROUP BY XS.ZGH,XM
8.假设销售表中有一列x,请删除此列
ALTER TABLE BANKT
DROP COLUMN X
9.删除销售表中2001年以前(包括2001年)销售次数最少的人员的销售记录
DELETE FROM XSQKB
WHERE ZGH IN
(
SELECT TOP 1 WITH TIES ZGH
FROM XSQKB
WHERE XSRQ<='2001/12/31'
GROUP BY ZGH
ORDER BY SUM(XSSL)
)