sql sever 例题两篇

....

第一篇

create database spj

use spj
  create table s
  (sno char(2)primary key,--供应商id
  sname char(6),--供应商姓名
  status smallint, --供应商状态(没什么用不用管)
  city char(4) --城市
  --供应商表
  );
  
  create table p
  (pno char(2)primary key, --零件id
  pname char(10), --零件名
  color char(2),--颜色
  weight smallint --重量
  --零件
  );
  
  create table j
  (jno char(2)primary key,--工程id
  jname char(10),--工程名
  city  char(4) --工程所在的城市
  --工程项目
  );
  
  create table spj
  (sno char(2), --供应商id
  pno char(2),  --零件id
  jno char(2),  --工程id
  qty smallint  --供应数量(没用) 
  --供应商
  )

   insert into s
  values('s1','精益',20,'天津');
  insert into s
  values('s2','盛锡',10,'北京');
  insert into s
  values('s3','东方红',30,'北京');
  insert into s
  values('s4','丰盛泰',20,'天津');
  insert into s
  values('s5','为民',30,'上海');

   insert into p
  values('p1','螺母','红',12);
  insert into p
  values('p2','螺栓','绿',17);
  insert into p
  values('p3','螺丝刀','蓝',14);
  insert into p 
  values('p4','螺丝刀','红',14);
  insert into p
  values('p5','凸轮','蓝',40);
  insert into p
  values('p6','齿轮','红',30);

   insert into j
  values('j1','三建','北京');
  insert into j
  values('j2','一汽','长春');
  insert into j
  values('j3','弹簧厂','天津');
  insert into j
  values('j4','造船厂','天津');
  insert into j
  values('j5','机车厂','唐山');
  insert into j
  values('j6','无线电厂','常州');
  insert into j
  values('j7','半导体厂','南京');

  insert into spj 
  values('s1','p1','j1',200);
  insert into spj 
  values('s1','p1','j3',100);
  insert into spj 
  values('s1','p1','j4',700);
  insert into spj 
  values('s1','p2','j2',100);
  insert into spj 
  values('s2','p3','j1',400);
  insert into spj 
  values('s2','p3','j2',200);
  insert into spj 
  values('s2','p3','j4',500);
  insert into spj 
  values('s2','p3','j5',400);
  insert into spj 
  values('s2','p5','j1',400);
  insert into spj 
  values('s2','p5','j2',100);
  insert into spj 
  values('s3','p1','j1',200);
  insert into spj 
  values('s3','p3','j1',200);
  insert into spj
  values('s4','p5','j1',100);
  insert into spj
  values('s4','p6','j3',300);
  insert into spj
  values('s4','p6','j4',200);
  insert into spj
  values('s5','p2','j4',100);
  insert into spj
  values('s5','p3','j1',200);
  insert into spj
  values('s5','p6','j2',200);
  insert into spj
  values('s5','p6','j4',500);

  select * from spj

  （1）把全部红色零件的颜色改成白色；
  update p set color='白' where color='红'
  （2）由S5供给J4的零件P6改为由S3供应；
  update spj set sno='s3' where jno='j4' and pno='p6' and sno='s5'
  （3）将(S2,J6,P4,200)插入供应关系；
  insert into spj values ('s2','p4','j6',200)
  （4）从供应商关系中删除S2的记录，并从供应关系中删除相应的记录；(只写命令，不用操作)
  delete from spj where sno='s2'
  （5）查询北京和天津的供应商的信息。
  select * from j where city in('北京','天津')
  （6）查询重量在10～20的零件名称。
  select pname from p where weight between 10 and 20
  （7）查询名称中有“轮”的零件信息。
  select * from p where pname like'%轮%'
  （8）查询所有已开工的工程号码。
  select  distinct j.jno from j,spj where spj.jno=j.jno;
  （9）查询给工程J1供应零件的供应商号码SNO。
  select distinct sno from spj where jno='j1'
  （10）查询给工程J1供应零件P1的供应商号码SNO。
    select distinct sno from spj where jno='j1' and pno='p1'
  （11）查询所有供应商的名称和所在城市
  select sname,city from s
  （12）查收所有零件的名称、颜色、重量。
  select pname,color,weight from p
  （13）查询使用了供应商S1供应的零件的工程号码。
  select jno from spj where sno='s1';

第二篇

create database lx
use lx
create table 读者
(
    账号 char(4) not null primary key,
    姓名 varchar(6) not null,
    性别 char(2) check (性别 in ('男','女')), 
    等级 varchar(8) check (等级 in ('黄金','青铜','白银','最强王者')),
    出生日期 datetime not null,
    余额 money not null
)
insert into 读者(账号,姓名,性别,等级,出生日期,余额) values ('D001','陈诚瑞','男','黄金',1988-7-11,200)
insert into 读者(账号,姓名,性别,等级,出生日期,余额) values ('D002','郭彩霞','女','白银',1987-1-4,500)
insert into 读者(账号,姓名,性别,等级,出生日期,余额) values ('D003','王新英','女','青铜',1978-9-21,20)
insert into 读者(账号,姓名,性别,等级,出生日期,余额) values ('D004','范鼎毅','男','最强王者',1995-4-1,1000)
insert into 读者(账号,姓名,性别,等级,出生日期,余额) values ('D005','钟鸣','女','青铜',2000-4-21,100)
insert into 读者(账号,姓名,性别,等级,出生日期,余额) values ('D006','王协瑞','男','白银',1990-7-14,800)

create table 图书
(
    条形码 varchar(7) not null primary key,
    书名 varchar(20) not null,
    作者 varchar(6) not null,
    出版社 varchar(20),
    出版日期 datetime,
    售价 money not null
)
go
insert into 图书(条形码,书名,作者,出版社,出版日期,售价) values ('TP303.5','大学计算机基础','李凯','科学出版社',2018-10-7,50)
insert into 图书(条形码,书名,作者,出版社,出版日期,售价) values ('TP304.1','数据库技术及应用','郭栋阳','高教出版社',2017-6-16,42.6)
insert into 图书(条形码,书名,作者,出版社,出版日期,售价) values ('TP213.5','七彩课堂','朱五书','河北教育出版社',2019-11-8,31.8)
insert into 图书(条形码,书名,作者,出版社,出版日期,售价) values ('TP204.2','钩针编织基础','寿玉梅','科学出版社',2015-9-18,56)
insert into 图书(条形码,书名,作者,出版社,出版日期,售价) values ('TP305.5','C语言程序设计','谭森','高教出版社',2014-1-12,48.5)
insert into 图书(条形码,书名,作者,出版社,出版日期,售价) values ('TP305.6','VB程序设计','钟鸣','人民邮电出版社',2014-7-11,44.6)

create table 借阅
(
    账号 char(4) not null,
    条形码 varchar(7) not null,
    借书日期 datetime not null,
    还书日期 datetime not null
)
drop table 图书
drop table 借阅

insert into 借阅(账号,条形码,借书日期,还书日期) values ('D001','TP303.5',2018-8-2,2018-10-7)
insert into 借阅(账号,条形码,借书日期,还书日期) values ('D001','TP304.1',2017-3-18,2017-6-16)
insert into 借阅(账号,条形码,借书日期,还书日期) values ('D005','TP303.5',2019-10-3,2019-11-8)
insert into 借阅(账号,条形码,借书日期,还书日期) values ('D002','TP204.2',2015-6-6,2015-9-18)
insert into 借阅(账号,条形码,借书日期,还书日期) values ('D002','TP305.5',2013-12-4,2014-1-12)
insert into 借阅(账号,条形码,借书日期,还书日期) values ('D003','TP305.6',2014-5-1,2014-7-11)
insert into 借阅(账号,条形码,借书日期,还书日期) values ('D003','TP304.1',2020-1-2,2020-3-11)

select * from 借阅

1.查询图书表中售价介于50元到70元之间的图书的全部信息
select * from 图书 where 售价 between 50 and 70
2.查询图书表中全部图书的最高售价、最低售价和平均售价。
select max(售价),min(售价),avg(售价) from 图书
3.查询图书的条形码，书名，出版社和出版日期，要求结果按出版社升序排列，出版社相同的数据按出版日期降序排列
select 借阅.条形码,书名,出版社,出版日期 from 图书,借阅 where 图书.条形码=借阅.条形码
4.在图书表中插入一条记录，内容是：条形码“TP211.3”，书名“狼图腾”，作者“姜戎”，售价 44.5元
insert into 图书(条形码,书名,作者,售价) values ('TP211.3','狼图腾','姜戎',44.5)
5.通过图书表，读者表和借阅表，查询读者的借阅情况，要求结果中包含以下字段：账号，姓名，条形码，书名，借书日期和还书日期。
select 借阅.账号,姓名,借阅.条形码,书名,借书日期,还书日期 from 读者,图书,借阅 where 读者.账号=借阅.账号 and 图书.条形码=借阅.条形码
6.在读者表中查询所有等级为“白银”的读者的余额之和。
select sum(余额) from 读者 where 等级='白银'
7.通过读者和借阅表，查询读者信息，包括以下字段：账号，姓名，性别，条形码，借书日期和还书日期
select 读者.账号,姓名,性别,条形码,借书日期,还书日期
 from 读者,借阅 where 读者.账号=借阅.账号
8.查询图书表中有哪些出版社，要求结果没有重复值
select distinct 出版社 from 图书 
9.将图书表中条形码为TP204.2的图书信息删除
delete from 图书 where 条形码='tp204.2'
10.在读者表中，查询男女性读者各多少人。
select 性别,count(性别) from 读者 group by 性别
11.在读者表中查询不同等级各自的人数，结果按人数降序排列。(降序的关键字是desc)
select 等级,count(等级) from 读者 group by 等级 order by count(等级) desc
12.查询图书表中条形码左边开始三个字符是“TP3”的图书的全部信息
select * from 图书 where 条形码 like'tp3%'
13.将图书表中科学出版社出版的图书价格上涨5%。
update 图书  set 售价=售价+售价*0.05 where 出版社='科学出版社'
14.查询图书表中科学出版社出版的价格在50元以上的图书的信息，要求结果中包括书名，作者，出版社和售价4列。(不包括50元）
select 书名,作者,出版社,售价 from 图书 where  出版社='科学出版社' and 售价>50
15.使用IN子句查询所有‘科学出版社’和‘高教出版社’的书名，作者，售价。
select 书名,作者,售价 from 图书 where 出版社 in('科学出版社','高教出版社')
16.在读者表中查询余额最高的读者的全部信息。提示:请使用嵌套查询作答，不能用top

select * from 读者 where 余额=(select max(余额) from 读者 )
17.根据读者表和借阅表，查询没有借书记录的读者的账号和姓名。提示:使用“带有NOT IN的嵌套查询”

select 账号,姓名 from 读者 where 账号 not in (select distinct 账号 from 借阅)
18.通过图书表和借阅表，查询哪本书没有被借阅过，显示它的条形码，书名和作者信息 

select 条形码,书名,作者 from 图书 where 条形码 not in (select distinct 条形码 from 借阅 )