SQL语句存储过程实例详解(面试宝典)「建议收藏」

SQL语句存储过程实例详解(面试宝典)「建议收藏」本文用3个题目,从建立数据库到创建存储过程,详细讲解数据库的功能。这个问题面试的时候也是经常会用到的,比如写sql语句。题目1学校图书馆借书信息管理系统建立三个表:学生信息表:student字段名称数据类型说明stuIDchar(10)学生编号,主键stuName

大家好,又见面了,我是你们的朋友全栈君。如果您正在找激活码,请点击查看最新教程,关注关注公众号 “全栈程序员社区” 获取激活教程,可能之前旧版本教程已经失效.最新Idea2022.1教程亲测有效,一键激活。

Jetbrains全系列IDE稳定放心使用

本文用3个题目,从建立数据库到创建存储过程,详细讲解数据库的功能。这个问题面试的时候也是经常会用到的,比如写sql语句。


题目1

学校
图书馆借书信息管理
系统建立三个表:

学生信息表:student

字段名称

数据类型

说明

stuID

char(10)

学生编号,主键

stuName

Varchar(10)

学生名称

major

Varchar(50)

专业

图书表:book

字段名称

数据类型

说明

stuID

char(10)

学生编号,主键

stuName

Varchar(10)

学生名称

major

Varchar(50)

专业

借书信息表:borrow

字段名称

数据类型

说明

borrowID

char(10)

借书编号,主键

stuID

char(10)

学生编号,外键

BID

char(10)

图书编号,外键

T_time

datetime

借书日期

B_time

datetime

还书日期

请编写SQL语句完成以下的功能:

1) 查询“计算机”专业学生在“2007-12-15”至“2008-1-8”时间段内借书的学生编号、学生名称、图书编号、图书名称、借出日期;参考查询结果如下图所示:
\

2) 查询所有借过图书的学生编号、学生名称、专业;参考查询结果如下图所示:


\

3) 查询借过作者为“安意如”的图书的学生姓名、图书名称、借出日期、归还日期;参考查询结果如下图所示:


\

4) 查询目前借书但未归还图书的学生名称及未还图书数量;参考查询结果如下图所示:


\

附加:建表语句:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
USE master
GO
/*$$$$$$$$$$$$$建库$$$$$$$$$$$$$$$$$$$$$$$$*/
--检验数据库是否存在,如果为真,删除此数据库--
IF exists(
SELECT
*
FROM
sysdatabases
WHERE
name
=
'BOOK'
)
  
DROP
DATABASE
BOOK
GO
CREATE
DATABASE
BOOK
GO
 
--建数据表--
USE BOOK
GO
CREATE
TABLE
student 
--学生信息表
(
  
stuID
CHAR
(10)
primary
key

--学生编号
  
stuName 
CHAR
(10)
NOT
NULL
,    
--学生名称
  
major 
CHAR
(50)
NOT
NULL   
--专业
)
GO
CREATE
TABLE
book 
--图书表
(
  
BID 
CHAR
(10)
primary
key
,   
--图书编号
  
title 
CHAR
(50)
NOT
NULL

--书名
  
author 
CHAR
(20)
NOT
NULL

--作者
)
GO
CREATE
TABLE
borrow 
--借书表
(
 
borrowID 
CHAR
(10)
primary
key
,   
--借书编号
stuID
CHAR
(10)
foreign
key
(stuID)
references
student(stuID),
--学生编号
BID 
CHAR
(10)
foreign
key
(BID)
references
book(BID),
--图书编号
 
T_time  datetime
NOT
NULL
,  
--借出日期
 
B_time  datetime   
--归还日期
)
GO
 
--学生信息表中插入数据--
INSERT
INTO
student(stuID,stuName,major)
VALUES
(
'1001'
,
'林林'
,
'计算机'
)
INSERT
INTO
student(stuID,stuName,major)
VALUES
(
'1002'
,
'白杨'
,
'计算机'
)
INSERT
INTO
student(stuID,stuName,major)
VALUES
(
'1003'
,
'虎子'
,
'英语'
)
INSERT
INTO
student(stuID,stuName,major)
VALUES
(
'1004'
,
'北漂的雪'
,
'工商管理'
)
INSERT
INTO
student(stuID,stuName,major)
VALUES
(
'1005'
,
'五月'
,
'数学'
)
--图书信息表中插入数据--
INSERT
INTO
book(BID,title,author)
VALUES
(
'B001'
,
'人生若只如初见'
,
'安意如'
)
INSERT
INTO
book(BID,title,author)
VALUES
(
'B002'
,
'入学那天遇见你'
,
'晴空'
)
INSERT
INTO
book(BID,title,author)
VALUES
(
'B003'
,
'感谢折磨你的人'
,
'如娜'
)
INSERT
INTO
book(BID,title,author)
VALUES
(
'B004'
,
'我不是教你诈'
,
'刘庸'
)
INSERT
INTO
book(BID,title,author)
VALUES
(
'B005'
,
'英语四级'
,
'白雪'
)
--借书信息表中插入数据--
INSERT
INTO
borrow(borrowID,stuID,BID,T_time,B_time)
VALUES
(
'T001'
,
'1001'
,
'B001'
,
'2007-12-26'
,
null
)
INSERT
INTO
borrow(borrowID,stuID,BID,T_time,B_time)
VALUES
(
'T002'
,
'1004'
,
'B003'
,
'2008-1-5'
,
null
)
INSERT
INTO
borrow(borrowID,stuID,BID,T_time,B_time)
VALUES
(
'T003'
,
'1005'
,
'B001'
,
'2007-10-8'
,
'2007-12-25'
)
INSERT
INTO
borrow(borrowID,stuID,BID,T_time,B_time)
VALUES
(
'T004'
,
'1005'
,
'B002'
,
'2007-12-16'
,
'2008-1-7'
)
INSERT
INTO
borrow(borrowID,stuID,BID,T_time,B_time)
VALUES
(
'T005'
,
'1002'
,
'B004'
,
'2007-12-22'
,
null
)
INSERT
INTO
borrow(borrowID,stuID,BID,T_time,B_time)
VALUES
(
'T006'
,
'1005'
,
'B005'
,
'2008-1-6'
,
null
)
INSERT
INTO
borrow(borrowID,stuID,BID,T_time,B_time)
VALUES
(
'T007'
,
'1002'
,
'B001'
,
'2007-9-11'
,
null
)
INSERT
INTO
borrow(borrowID,stuID,BID,T_time,B_time)
VALUES
(
'T008'
,
'1005'
,
'B004'
,
'2007-12-10'
,
null
)
INSERT
INTO
borrow(borrowID,stuID,BID,T_time,B_time)
VALUES
(
'T009'
,
'1004'
,
'B005'
,
'2007-10-16'
,
'2007-12-18'
)
INSERT
INTO
borrow(borrowID,stuID,BID,T_time,B_time)
VALUES
(
'T010'
,
'1002'
,
'B002'
,
'2007-9-15'
,
'2008-1-5'
)
INSERT
INTO
borrow(borrowID,stuID,BID,T_time,B_time)
VALUES
(
'T011'
,
'1004'
,
'B003'
,
'2007-12-28'
,
null
)
INSERT
INTO
borrow(borrowID,stuID,BID,T_time,B_time)
VALUES
(
'T012'
,
'1002'
,
'B003'
,
'2007-12-30'
,
null
)

标准答案: — 1)查询“计算机”专业学生在“2007-12-15”至“2008-1-8”时间段内借书的学生编号、学生名称、图书编号、图书名称、借出日期— 

select
学生编号=stuID,
学生名称=(
select
stuName
from
student
where
stuID=borrow.stuID),
图书编号=BID,
图书名称=(
select
title
from
book
where
BID=borrow.BID),
借出日期=T_time 
from
borrow
where
stuID
in
(
select
stuID
from
student
where
major=
'计算机'
and
T_time>
'2007-12-15'
and
T_time<
'2008-1-8'
2)查询所有借过图书的学生编号、学生名称、专业--

select
学生编号=stuID,
学生名称=stuName,
专业=major
from
student
where
stuID
in
(
select
stuID
from
borrow)
3)查询借过作者为“安意如”的图书的学生姓名、图书名称、借出日期--

select
学生名称=(
select
stuName
from
student
where
stuID=borrow.stuID),
图书名称=(
select
title
from
book
where
BID=borrow.BID),
借出日期=T_time,
归还日期=B_time
from
borrow
where
BID
in
(
select
BID
from
book
where
author=
'安意如'
)
4)查询目前借书但未归还图书的学生名称及未还图书数量--

select 学生名称=(select stuName from student where stuID=borrow.stuID),
借书数量=count(*) from borrow where B_time is null group by stuID

题目2

程序员工资表:ProWage

字段名称

数据类型

说明

ID

int

自动编号,主键

PName

Char(10)

程序员姓名

Wage

int

工资

创建一个存储过程,对程序员的工资进行分析,月薪1500到10000不等,如果有百分之五十的人薪水不到2000元,给所有人加薪,每次加100,再进行分析,直到有一半以上的人大于2000元为止,存储过程执行完后,最终加了多少钱?

例如:如果有百分之五十的人薪水不到2000,给所有人加薪,每次加100元,直到有一半以上的人工资大于2000元,调用存储过程后的结果如图:


\

请编写T-SQL来实现如下功能:

1) 创建存储过程,查询是否有一半程序员的工资在2200、3000、3500、4000、5000或6000元之上,如果不到分别每次给每个程序员加薪100元,至之一半程序员的工资达到2200,3000,3500,4000,5000或6000元。

2) 创建存储过程,查询程序员平均工资在4500元,如果不到则每个程序员每次加200元,至到所有程序员平均工资达到4500元。

建表语句:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
USE master
GO
/*$$$$$$$$$$$$$建库$$$$$$$$$$$$$$$$$$$$$$$$*/
--检验数据库是否存在,如果为真,删除此数据库--
IF exists(
SELECT
*
FROM
sysdatabases
WHERE
name
=
'Wage'
)
  
DROP
DATABASE
Wage
GO
CREATE
DATABASE
Wage
GO
 
--建数据表--
USE Wage
GO
CREATE
TABLE
ProWage 
--程序员工资表
(
  
ID
int
identity(1,1)
primary
key

--工资编号
  
PName 
CHAR
(10)
NOT
NULL
,    
--程序员姓名
  
Wage 
int
NOT
NULL   
--工资
)
GO
--插入数据--
INSERT
INTO
ProWage(PName,Wage)
VALUES
(
'青鸟'
,1900)
INSERT
INTO
ProWage(PName,Wage)
VALUES
(
'张三'
,1200)
INSERT
INTO
ProWage(PName,Wage)
VALUES
(
'李四'
,1800)
INSERT
INTO
ProWage(PName,Wage)
VALUES
(
'二月'
,3500)
INSERT
INTO
ProWage(PName,Wage)
VALUES
(
'蓝天'
,2780)

标准答案:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
--1、创建存储过程--
if exists (
select
*
from
sysobjects
where
name
=
'Sum_wage'
)
drop
procedure
Sum_wage
GO
create
procedure
Sum_wage
@PWage
int
,
@AWage
int
,
@total
int
as
while (1=1)
begin
if (
select
count
(*)
from
ProWage)>2*(
select
count
(*)
from
ProWage
where
Wage>=@PWage)
update
ProWage
set
@total=@total+@AWage,Wage=Wage+@AWage
else
break
end
print
'一共加薪:'
+
convert
(
varchar
,@total)+
'元'
print
'加薪后的程序员工资列表:'
select
*
from
ProWage
--调用存储过程1--
exec
Sum_wage @PWage=2000,@AWage=100,@total=0
exec
Sum_wage @PWage=2200,@AWage=100,@total=0
exec
Sum_wage @PWage=3000,@AWage=100,@total=0
exec
Sum_wage @PWage=4000,@AWage=100,@total=0
exec
Sum_wage @PWage=5000,@AWage=100,@total=0
exec
Sum_wage @PWage=6000,@AWage=100,@total=0
 
--2、创建存储过程2--
if exists (
select
*
from
sysobjects
where
name
=
'Avg_wage'
)
drop
procedure
Avg_wage
GO
create
procedure
Avg_wage
@PWage
int
,
@AWage
int
,
@total
int
as
while (1=1)
begin
if ((
select
Avg
(Wage)
from
ProWage)<=@PWage)
update
ProWage
set
@total=@total+@AWage,Wage=Wage+@AWage
else
break
end
print
'一共加薪:'
+
convert
(
varchar
,@total)+
'元'
print
'加薪后的程序员工资列表:'
select
*
from
ProWage
--调用存储过程--
exec
Avg_wage @PWage=3000,@AWage=200,@total=0
exec
Avg_wage @PWage=4500,@AWage=200,@total=0

题目3

学生成绩信息三个表,结构如下:

学生表:Member

字段名称

数据类型

说明

MID

Char(10)

学生号,主键

MName

Char(50)

姓名

课程表:F

字段名称

数据类型

说明

FID

Char(10)

课程,主键

FName

Char(50)

课程名

成绩表:Score

字段名称

数据类型

说明

SID

int

自动编号,主键,成绩记录号

FID

Char(10)

课程号,外键

MID

Char(10)

学生号,外键

Score

int

成绩

请编写T-SQL语句来实现如下功能:

1) 查询各个学生语文、数学、英语、历史课程成绩,例如下表:

姓名

语文

数学

英语

历史

张萨

78

67

89

76

王强

89

67

84

96

李三

70

87

92

56

李四

80

78

97

66

2) 查询四门课中成绩低于70分的学生及相对应课程名和成绩。

3) 统计各个学生参加考试课程的平均分,且按平均分数由高到底排序。

4) 创建存储过程,分别查询参加1、2、3、4门考试及没有参加考试的学生名单,要求显示姓名、学号。

建表语句:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
USE master
GO
/*$$$$$$$$$$$$$建库$$$$$$$$$$$$$$$$$$$$$$$$*/
--检验数据库是否存在,如果为真,删除此数据库--
IF exists(
SELECT
*
FROM
sysdatabases
WHERE
name
=
'Student'
)
  
DROP
DATABASE
Student
GO
CREATE
DATABASE
Student
GO
 
--建数据表--
USE Student
GO
CREATE
TABLE
Member 
--学生表
(
  
MID 
char
(10)
primary
key

--学生号
  
MName 
CHAR
(50)
NOT
NULL 
--姓名
)
GO
CREATE
TABLE

--课程表
(
 
FID  
char
(10)
primary
key
,   
--课程号
FName 
CHAR
(50)
NOT
NULL
--课程名
)
GO
CREATE
TABLE
score 
--学生成绩表
(
  
SID
int
identity(1,1)
primary
key

--成绩记录号
  
FID
char
(10) 
foreign
key
(FID)
references
F(FID) ,    
--课程号
  
MID
char
(10) 
foreign
key
(MID)
references
Member(MID) ,    
--学生号
  
Score 
int
NOT
NULL   
--成绩
)
GO
--课程表中插入数据--
INSERT
INTO
F(FID,FName)
VALUES
(
'F001'
,
'语文'
)
INSERT
INTO
F(FID,FName)
VALUES
(
'F002'
,
'数学'
)
INSERT
INTO
F(FID,FName)
VALUES
(
'F003'
,
'英语'
)
INSERT
INTO
F(FID,FName)
VALUES
(
'F004'
,
'历史'
)
--学生表中插入数据--
INSERT
INTO
Member(MID,MName)
VALUES
(
'M001'
,
'张萨'
)
INSERT
INTO
Member(MID,MName)
VALUES
(
'M002'
,
'王强'
)
INSERT
INTO
Member(MID,MName)
VALUES
(
'M003'
,
'李三'
)
INSERT
INTO
Member(MID,MName)
VALUES
(
'M004'
,
'李四'
)
INSERT
INTO
Member(MID,MName)
VALUES
(
'M005'
,
'阳阳'
)
INSERT
INTO
Member(MID,MName)
VALUES
(
'M006'
,
'虎子'
)
INSERT
INTO
Member(MID,MName)
VALUES
(
'M007'
,
'夏雪'
)
INSERT
INTO
Member(MID,MName)
VALUES
(
'M008'
,
'璐璐'
)
INSERT
INTO
Member(MID,MName)
VALUES
(
'M009'
,
'珊珊'
)
INSERT
INTO
Member(MID,MName)
VALUES
(
'M010'
,
'香奈儿'
)
--成绩表中插入数据--
INSERT
INTO
Score(FID,MID,Score)
VALUES
(
'F001'
,
'M001'
,78)
INSERT
INTO
Score(FID,MID,Score)
VALUES
(
'F002'
,
'M001'
,67)
INSERT
INTO
Score(FID,MID,Score)
VALUES
(
'F003'
,
'M001'
,89)
INSERT
INTO
Score(FID,MID,Score)
VALUES
(
'F004'
,
'M001'
,76)
INSERT
INTO
Score(FID,MID,Score)
VALUES
(
'F001'
,
'M002'
,89)
INSERT
INTO
Score(FID,MID,Score)
VALUES
(
'F002'
,
'M002'
,67)
INSERT
INTO
Score(FID,MID,Score)
VALUES
(
'F003'
,
'M002'
,84)
INSERT
INTO
Score(FID,MID,Score)
VALUES
(
'F004'
,
'M002'
,96)
INSERT
INTO
Score(FID,MID,Score)
VALUES
(
'F001'
,
'M003'
,70)
INSERT
INTO
Score(FID,MID,Score)
VALUES
(
'F002'
,
'M003'
,87)
INSERT
INTO
Score(FID,MID,Score)
VALUES
(
'F003'
,
'M003'
,92)
INSERT
INTO
Score(FID,MID,Score)
VALUES
(
'F004'
,
'M003'
,56)
INSERT
INTO
Score(FID,MID,Score)
VALUES
(
'F001'
,
'M004'
,80)
INSERT
INTO
Score(FID,MID,Score)
VALUES
(
'F002'
,
'M004'
,78)
INSERT
INTO
Score(FID,MID,Score)
VALUES
(
'F003'
,
'M004'
,97)
INSERT
INTO
Score(FID,MID,Score)
VALUES
(
'F004'
,
'M004'
,66)
INSERT
INTO
Score(FID,MID,Score)
VALUES
(
'F001'
,
'M006'
,88)
INSERT
INTO
Score(FID,MID,Score)
VALUES
(
'F002'
,
'M006'
,55)
INSERT
INTO
Score(FID,MID,Score)
VALUES
(
'F003'
,
'M006'
,86)
INSERT
INTO
Score(FID,MID,Score)
VALUES
(
'F004'
,
'M006'
,79)
INSERT
INTO
Score(FID,MID,Score)
VALUES
(
'F002'
,
'M007'
,77)
INSERT
INTO
Score(FID,MID,Score)
VALUES
(
'F003'
,
'M008'
,65)
INSERT
INTO
Score(FID,MID,Score)
VALUES
(
'F004'
,
'M007'
,48)
INSERT
INTO
Score(FID,MID,Score)
VALUES
(
'F004'
,
'M009'
,75)
INSERT
INTO
Score(FID,MID,Score)
VALUES
(
'F002'
,
'M009'
,88)

标准答案: — 1)查询各个学生语文、数学、英语、历史课程成绩– 

SELECT
Member.MName
AS
姓名,
英语 =
SUM
(
CASE
F.FName
WHEN
'语文'
THEN
Score.Score
END
),
数学 =
SUM
(
CASE
F.FName
WHEN
'数学'
THEN
Score.Score
END
),
语文 =
SUM
(
CASE
F.FName
WHEN
'英语'
THEN
Score.Score
END
),
历史 =
SUM
(
CASE
F.FName
WHEN
'历史'
THEN
Score.Score
END
)
FROM
Score, Member,F
WHERE
F.FID = Score.FID
AND
Member.MID =Score.MID
GROUP
BY
Member.MName 

2)查询四门课中成绩低于70分的学生及相对应课程名和成绩–

select 姓名=(select MName from Member where MID=Score.MID),
课程名=(select FName from F where FID=Score.FID),
成绩=Score from Score where Score<70
3)统计各个学生四课程的平均分,且按平均分数由高到底排序--
select 姓名=(select MName from Member where MID=Score.MID),
平均分=Avg(Score) from Score group by MID order by 平均分 desc
4)创建存储过程--

if exists (
select
*
from
sysobjects
where
name
=
'P_stu'
)
drop
procedure
P_stu
GO
create
procedure
P_stu
@num
int
As
print
'参加'
+
convert
(
varchar
(5),@num)+
'门课考试的学生姓名及学号:'
select
姓名=(
select
MName
from
Member
where
MID=Score.MID),
学号=MID
from
Score
group
by
MID
having
count
(*)=@num
 
--调用存储过程--
exec
P_stu @num=2

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。

发布者:全栈程序员-用户IM,转载请注明出处:https://javaforall.cn/185618.html原文链接:https://javaforall.cn

【正版授权,激活自己账号】: Jetbrains全家桶Ide使用,1年售后保障,每天仅需1毛

【官方授权 正版激活】: 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...

(0)
blank

相关推荐

发表回复

您的电子邮箱地址不会被公开。

关注全栈程序员社区公众号