....
第一篇
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 借阅 )