--############################################
--工作和面试中常见Sql示例
--############################################
/*题目1:
销售员表A(sellid,sellname)
产品表B(pid,pname,pprice)
交易表C(cid,pid,sellid,buynum,buydate)
--------------------------------------------
求解: 销售员小王卖给客户小夏的各类产品在2004-5-8日到2005-3-6日销售的产品名称和每个产品一共销售的金额.
---.要求按照时间倒序排列(这是原题附加的条件, 题目纰漏, 不可能按时间排序. 原因: 假设有2天都购买了同一商品, 因为是总金额所以无法判断哪天买的)。
*/
--解答: 思路: 销售员小王(使用表A), 产品名称(使用表B), 总金额(使用表C), 同时使用到所有的表, 适合拼成大表(为高效使用B和C大数据量表作主表)
select B.pname as ProduceName, sum(buynum * pprice) as TotalPrice
from (B inner join C on B.pid = C.pid and C.cid = '小夏') inner join A on C.sellid = A.sellid and A.sellname = '小王' and C.buydate between '2004-5-8' and '2005-3-6' --拼表
group by B.pname --分组用于计算每个产品的总金额
--############################################
/*题目2: 现有数据库结构及数据如下：
学生表(students) S
st_id st_name(姓名) gender(性别)
st001 张杰 男
st002 公孙燕飞 男
st003 王楠 女
st004 王伟 男
st005 李燕纹 女
st006 孙武 男
老师表(teachers) T
t_id t_name(姓名) t_lesson(课程)
t001 张老师 数学
t002 李老师 英语
成绩表(results) R
r_id r_fenshu(int) r_stid r_tid
r001 90 st001 t002
r002 68 st005 t001
r003 92 st003 t001
r004 82 st006 t002
r005 70 st002 t002
r006 86 st002 t001
r007 57 st003 t002
r008 76 st006 t001
r009 55 st001 t001
r010 77 st004 t002
r011 58 st005 t002
以上数据库结构中字段未标明具体类型的，皆为varchar类型。
*/
--建表
--创建学生表
if exists(select [name] from sysobjects where [name] = 'Students' and [type] = 'U')
drop table Students
go
create table Students(
st_id nvarchar(10) not null primary key,
st_name nvarchar(30) not null,
gender nchar(1) check(gender = '男' or gender='女') default('男')
)
go
insert into Students(st_id,st_name,gender) values('st001','张杰','男')
insert into Students(st_id,st_name,gender) values('st002','公孙燕飞','男')
insert into Students(st_id,st_name,gender) values('st003','王楠','女')
insert into Students(st_id,st_name,gender) values('st004','王伟','男')
insert into Students(st_id,st_name,gender) values('st005','李燕纹','女')
insert into Students(st_id,st_name,gender) values('st006','孙武','男')
insert into Students(st_id,st_name,gender) values('st007','公孙策','男')
insert into Students(st_id,st_name,gender) values('st008','李霜燕','女')
go
--创建教师表
if exists(select [name] from sysobjects where [name]='Teachers' and [type]='U')
drop table Teachers
go
create table Teachers(
t_id nvarchar(10) not null primary key,
t_name nvarchar(30) not null,
t_lesson nvarchar(30)
)
go
insert into Teachers(t_id,t_name,t_lesson) values('t001','张老师','数学')
insert into Teachers(t_id,t_name,t_lesson) values('t002','李老师','英语')
go
--创建成绩表
if exists(select [name] from sysobjects where [name]='Results' and [type]='U')
drop table Results
go
create table Results(
r_id nvarchar(10) not null primary key,
r_score int default(0),
r_stid nvarchar(10) foreign key references Students(st_id),
r_tid nvarchar(10) foreign key references Teachers(t_id)
)
go
insert into Results(r_id,r_score,r_stid,r_tid) values('r001',90,'st001','t002')
insert into Results(r_id,r_score,r_stid,r_tid) values('r002',68,'st005','t001')
insert into Results(r_id,r_score,r_stid,r_tid) values('r003',92,'st003','t001')
insert into Results(r_id,r_score,r_stid,r_tid) values('r004',82,'st006','t002')
insert into Results(r_id,r_score,r_stid,r_tid) values('r005',70,'st002','t002')
insert into Results(r_id,r_score,r_stid,r_tid) values('r006',86,'st002','t001')
insert into Results(r_id,r_score,r_stid,r_tid) values('r007',57,'st003','t002')
insert into Results(r_id,r_score,r_stid,r_tid) values('r008',76,'st006','t001')
insert into Results(r_id,r_score,r_stid,r_tid) values('r009',55,'st001','t001')
insert into Results(r_id,r_score,r_stid,r_tid) values('r010',77,'st004','t002')
insert into Results(r_id,r_score,r_stid,r_tid) values('r011',58,'st005','t002')
insert into Results(r_id,r_score,r_stid,r_tid) values('r012',66,'st007','t002')
insert into Results(r_id,r_score,r_stid,r_tid) values('r013',78,'st008','t001')
go
------------------基础题目:-------------------------
--1）查询出王伟同学的学生编号。
select st_id from students where st_name = '王伟'
--2）查询出名字第三个字是“燕”或"策"字的学生的编号和姓名。
select st_id, st_name from students where st_name like '__[燕策]%'
--3）查询显示出所有男学生的姓名及其名子的长度。
select st_name, len(st_name) as NameLength from students where gender = '男'
--4）查出数学考试成绩的最低分。
--解1：要取出两张表中的字段, 故采用表连接
select t.t_lesson as CourseName, min(isnull(r_score,0)) as MinScore --若有聚合函数, 即使不写group by 子句, 也会默认将表分为一个大组
from results r inner join teachers t on r.r_tid = t.t_id and t.t_lesson = '数学'
group by t.t_lesson
--解2：只取个ID即可
select top 1 '数学' as CourseName, r_score --top 1是用来筛选记录的, 所以得紧跟select, 不用考虑字段顺序
from results
where r_tid in ( --为保险起见, 这里可用in替代了=; 还有个好处: 在项目中可很容易改成模糊查询;
select t_id from teachers t where t_lesson = '数学'
)
order by r_score
--5）查出英语考试成绩的平均分。
select '英语' as CourseName, avg(isnull(r_score,0)) as AvgSCore
from results r inner join teachers t on r.r_tid = t.t_id and t.t_lesson = '英语'
--6）查出所有女学生的各科成绩。
--思路: results结果中只是参加了考试的人, 还有没参加考试的. 因此, 直接连接results结果错误.
--解1：
--先找出所有女生参应该参加那些考试(笛卡尔积); 然后再左连接显示成绩.
select s.st_name, t.t_lesson, isnull(r.r_score,0)
from (students s cross join teachers t) left join results r on s.st_id = r.r_stid and t.t_id = r.r_tid --and s.gender ='女', 晒不出来
where s.gender = '女'
--由于笛卡尔积操作效率低, 稍作改进
select s.st_name, t.t_lesson, isnull(r.r_score,0)
from ( (select * from students where gender='女') s
cross join teachers t) left join results r on s.st_id = r.r_stid and t.t_id = r.r_tid
--7) 查询缺考的学生及缺考的科目。
--思路: 先找出所有学生应该参加那些考试(cross join); 然后再左连接并选择null的项
select s.st_name,t.t_lesson
from (students s cross join teachers t) left join results r on s.st_id = r.r_stid and t.t_id = r.r_tid --and r.r_score is null
where r.r_score is null
--8）在全部男学生中查询出学生编号最后两名的所有信息，并以学生编号降序显示。
--思路: 查询的是所有信息, 那么肯定包含考试成绩, 授课教师名称等等信息, 因此还是需要连接3表
--先交叉连接得到所有学生的所有课程信息; 然后左连接得到成绩信息. 注意: 最后的子查询必不可少且别忘了加where='男'的条件
select s.st_name,s.gender,t.t_lesson,t.t_name, isnull(r.r_score,0) --不能直接order by, 因为一个人可以考多门课
from ( (select * from students s where gender = '男') s
cross join teachers t) left join results r on s.st_id = r.r_stid and t.t_id = r.r_tid
where s.st_id in (select top 2 st_id from students where gender='男' order by st_id desc ) --不排除女生, 这里会缺失结果
--9）统计出王楠同学在这次考试中的所有课程成绩的合计分。
--思路: 只要总分, 所以取个st_id就行. 最后, 多拼个字段即可
select '王楠' as StudentName, sum(r_score) as TotalScore
from results
where r_stid in (select st_id from students where st_name = '王楠')
--10）查询所有课程考试中成绩及格，但未达到90分的学生的姓名。（不显示重复姓名）。
--思路: 因为是所有课程(有一门不及格就得排除), 故cross join. 又因为要取得姓名和学号, 因此适合联合查询
--查询到所有信息后(1个人有多门课成绩), 因此可以按学号分组, 用have子句选最小的>=60， 最大的<90即可
select distinct s.st_name
from (students s cross join teachers t) left join results r on s.st_id = r.r_stid and t.t_id = r.r_tid
group by s.st_name having min(isnull(r.r_score,0)) >= 60 and max(isnull(r.r_score,0)) < 90
--11）给所有参加考试的女学生的考试成绩每科加10分。 (最高成绩不超过100)。
--思路: case when(搜索case when, 简单case when只能根据值判断)
--搜索case when
update results set r_score = case when (r_score + 10) >100 then 100 else r_score + 10 end
where r_stid in (select st_id from students where gender = '女')
--测试语句
select * from students s inner join results r on s.st_id = r.r_stid and s.gender = '女'
------------------进阶题目:-------------------------
--1）统计出数学考试的及格人数，并显示出授课老师的姓名。
--思路: 两表取数据, 联合查询
select '及格人数' = count(r_id), t.t_name --若使用统计函数, 则自动分为1个大组
from results r inner join teachers t on r.r_tid = t.t_id and t.t_lesson = '数学'
group by t.t_name
--2）按课程统计查询出总分合计最多的课程，显示出该课程的名称、总分、授课老师的编号及姓名。
--思路: 字段涉及3张表, 3表联合; 需要统计所有学生的所有课程成绩, cross join
select top 1 t.t_lesson, sum(isnull(r.r_score,0)) Total , t.t_id, t.t_name
from (students s cross join teachers t) left join results r on s.st_id = r.r_stid and t.t_id = r.r_tid
group by t.t_lesson, t.t_id,t.t_name
order by sum(isnull(r.r_score,0)) desc
--3）在这次的考试中有一个学生缺考了一门课程，根据学校规定，缺考任何一门课程，该学生的其它课程考试成绩也视同无效，故请用一条SQL语句删除掉此次缺---考学生的其它课程考试成绩。
--思路: 找出缺考的学生的学号, 同样是先找所有课程成绩cross join; 然后找成绩为null的学号; 删除成绩
delete from results
where r_stid in (
select st_id
from (students s cross join teachers t) left join results r on r.r_stid = s.st_id and r.r_tid = t.t_id
where r.r_score is null)
------------------选作题目:-------------------------
--1）列出数据库里所有的用户表名称。
--思路: 系统表sysobjects和objectproperty(id,N'IsUserTable') = 1;
select * from sysobjects where type= 'U'
select * from sysobjects where objectproperty(id,N'IsUserTable') = 1
--2）使用一条SQL语句随机从学生表中取出5个学生的信息。
select top 5 * from students order by newid() --newid()是系统函数, 它将会为记录临时添加一列, 且内容是随机的
--############################################
/*题目3:
为管理岗位业务培训信息，建立3个表:
S (S#,SN,SD,SA) S#,SN,SD,SA 分别代表学号、学员姓名、所属单位、学员年龄
C (C#,CN ) C#,CN 分别代表课程编号、课程名称
SC ( S#,C#,G ) S#,C#,G 分别代表学号、所选修的课程编号、学习成绩
*/
--表S
if exists( select [name] from sysobjects where [name]='S' and [type]='U')
drop table S
go
create table S(
S# int,
SN nvarchar(30),
SD nvarchar(30),
SA int
)
go
insert into S(S#,SN,SD,SA)
select 1,'张三','财务部',43 union
select 2,'李四','项目部', 32union
select 3,'王五' ,'保卫处',30union
select 4,'赵六','客服部',26 union
select 5,'田七','前台',22 union
select 6,'候八','测试部',28 union
select 7,'周九','财务部',67
go
--表C
if exists( select [name] from sysobjects where [name]='C' and [type]='U')
drop table C
go
create table C(
C# nvarchar(30),
CN nvarchar(30)
)
go
insert into C(C#,CN)
select 'C1','税法概论'union
select 'C2','财务理论'union
select 'C3','税收基础'union
select 'C4','思想政治'union
select 'C5','税收基础'
go
--表TC
if exists( select [name] from sysobjects where [name]='SC' and [type]='U')
drop table SC
go
create table SC(
S# int,
C# nvarchar(30),
G int
)
go
insert into SC(S#,C#,G)
select 1,'C2',68 union
select 2,'C5',78 union
select 3,'C1',96 union
select 4,'C4',38 union
select 4,'C2',28 union
select 4,'C5',33 union
select 5,'C1',68 union
select 5,'C3',78 union
select 6,'C1',98 union
select 6,'C5',76 union
select 7,'C1',76 union
select 7,'C2',30 union
select 7,'C3',93 union
select 7,'C4',37 union
select 7,'C5',68
go
------------------嵌套语句题目:-------------------------
--1. 使用标准SQL嵌套语句查询选修课程名称为’税收基础’的学员学号和姓名
select s#,sn from s where s# in(
select s# from SC where c# in(
select c# from c where cn='税收基础')
)
--2. 使用标准SQL嵌套语句查询选修课程编号为’C2’的学员姓名和所属单位
select sn,sd from s where s# in (
select s# from sc where c# = 'C2')
--3. 使用标准SQL嵌套语句查询不选修课程编号为’C2’的学员姓名和所属单位
select sn,sd from s where s# not in (
select s# from sc where c# = 'C2')
--4. 使用标准SQL嵌套语句查询选修全部课程的学员姓名和所属单位
--思路: 先确定全部课程的数量, 在sc表中用分组函数和count()统计选课数
select sn,sd from s where s# in (
select s# from sc group by s# having count(*) = (
select count(*) from c)
)
--5. 查询选修了课程的学员人数
select count(distinct s#) from sc
--6. 查询选修课程超过5门的学员学号和所属单位
select sn,sd from s where s# in (
select s# from sc group by s# having count(*) > 5
)
--############################################
/*题目4:
已知关系模式:
S (SNO,SNAME) 学生关系。SNO 为学号，SNAME 为姓名
C (CNO,CNAME,CTEACHER) 课程关系。CNO 为课程号，CNAME 为课程名，CTEACHER 为任课教师
SC(SNO,CNO,SCGRADE) 选课关系。SCGRADE 为成绩
*/
--表s
if exists( select [name] from sysobjects where [name]='S' and [type]='U')
drop table S
go
create table S(
Sno int,
Sname nvarchar(30)
)
go
insert into S(Sno,Sname)
select 1,'张三' union
select 2,'李四' union
select 3,'王五' union
select 4,'赵六' union
select 5,'田七' union
select 6,'候八'
go
--表c
if exists( select [name] from sysobjects where [name]='C' and [type]='U')
drop table C
go
create table C(
Cno int,
Cname nvarchar(30),
Cteacher nvarchar(30)
)
go
insert into C(Cno,Cname,Cteacher)
select 1,'语文','韩耕' union
select 2,'数学','庄知县' union
select 3,'外语','钱某某' union
select 4,'政治','李明'
go
--表sc
if exists( select [name] from sysobjects where [name]='SC' and [type]='U')
drop table SC
go
create table SC(
Sno int,
Cno int,
SCgrade int
)
go
insert into SC(Sno,Cno,SCgrade)
select 1,2,68 union
select 2,1,66 union
select 2,2,35 union
select 2,3,55 union
select 3,1,96 union
select 4,4,87 union
select 4,2,80 union
select 4,1,98 union
select 4,3,33 union
select 5,1,68 union
select 5,3,78 union
select 6,1,98 union
select 6,4,76
go
--1. 找出没有选修过“李明”老师讲授课程的所有学生姓名
--解1：
select distinct sname
from sc inner join s on s.sno = sc.sno
where cno not in(
select cno from c where cteacher = '李明')
--解2：
select sname from s where sno in (
select sno from sc where cno not in(
select cno from c where cteacher = '李明'
)
)
--2. 列出有二门以上(含两门)不及格课程的学生姓名及其平均成绩
--思路: 从sc表中选择分数低于60的记录, 按sno分组后找count(*)>=2的记录(只能按sno分组, sname有可能重名);
-- 又需要姓名及avg成绩, 因此s和sc联合且按sname分组; 最后对分组加上学号的限制
select s.sname,avg(sc.scgrade) --含统计函数, 默认使用group by子句
from s inner join sc on s.sno = sc.sno
group by s.sname, s.sno having s.sno in (
select sno from sc where scgrade < 60 group by sno having count(*) >= 2)
--3. 列出既学过“1”号课程，又学过“2”号课程的所有学生姓名
--思路: 从sc1中选择cno=1且从sc2中选择cno=2的记录通过自连接拼成一张大表
-- 例如: sc中有两条sno记录, 按sno自连接后将会出现4条记录,
-- 若加上sc1.cno = 1 and sc2.cno =2即可筛出选择c1和c2的记录
select sname from s where sno in (
select sc1.sno from sc sc1 inner join sc sc2 on sc1.sno = sc2.sno and sc1.cno = 1 and sc2.cno = 2)
--4. 列出“1”号课成绩比“2”号同学该门课成绩高的所有学生的学号
--思路: 题目要求选出所有学生的学号, 这些学生的1号课成绩比学号为2的同学的1号课成绩高
--先找出2号同学的1号课成绩, 再找出所有1号课的记录并且成绩要大于2号同学的成绩
select sno from sc where cno = 1 and scgrade > (
select scgrade from sc where sno = 2 and cno = 1)
--5. 列出“1”号课成绩比“2”号课成绩高的所有学生的学号及其“1”号课和“2”号课的成绩
--思路: 1,2号课成绩相比, 肯定是考过1号课和2号课, 通过自连接选出同时选修1号和2号课的记录, 再附加个条件即可
select sc1.sno,sc1.scgrade '1号课',sc2.scgrade '2号课'
from sc sc1 inner join sc sc2 on sc1.sno = sc2.sno and sc1.cno = 1 and sc2.cno = 2 and sc1.scgrade > sc2.scgrade
--############################################
/*题目5:
数据关系
工资表S(姓名name,月份mon,工资salary)
*/
--1. 找出所有2月份工资比1月份工资高的员工姓名
select s1.name
from s s1 inner join s s2 on s1.sname = s2.sname and s1.mon = 1 and s2.mon = 2 and s2.salary > s1.salary
--2. 找出该年份所有涨工资的员工姓名
select s1.name
from s s1 inner join s s2 on s1.sname = s2.sname and s1.mon = 1 and s2.mon = 12 and s2.salary > s1.salary
--############################################
--动态Sql语句: 对于那些开发过程中无法确定的Sql语句(如: 列名数量无法确定等), 解决问题的思想就是: 拼接字符串
--题目1: 见题目2的表, 输出"孙武"同学参加了哪些考试; 该种情况下适合在存储过程中通过输出参数返回字符串
declare @coursestr nvarchar(4000)
set @coursestr = '';
select @coursestr =@coursestr + t.t_lesson + ',' from (students s inner join results r on s.st_id = r.r_stid and s.st_name='孙武') inner join teachers t on t.t_id= r.r_tid
print @coursestr
--题目2: 见题目2的表, 输出所有同学的各门课的成绩
--思路: 课程的数量不确定, 所以列数也是不确定的, 因此适合采用动态拼接sql语句
--1. 先选出所有学员的所有课程的成绩, (3表连接, 可以考虑做成视图)
create view ScoreView
as
select s.st_name [Name], t.t_lesson Lesson, isnull(r.r_score,0) Score from ( students s cross join teachers t) left join results r on s.st_id = r.r_stid and r.r_tid = t.t_id
go
--2. 利用简单case when 写出示例数据
select [Name], sum(case Lesson when '数学' then Score else 0 end ) '数学', --case when选出数学成绩, 其他成绩均为0, 用sum显示数学成绩
sum(case Lesson when '英语' then Score else 0 end) '英语'
from ScoreView
group by [name]
go
--3. 动态拼接Sql语句: 关于分号: 'xxx''xx''' --> xxx'xx'; '''' ---> '
declare @sql nvarchar(4000)
set @sql = 'select [name]' --拼头
select @sql = @sql + ', sum(case Lesson when ''' +lesson + ''' then Score else 0 end ) '''+lesson + ''''
from (select distinct lesson from ScoreView ) X --Sql中必须有from子句, 因此将from后边的子查询起个别名X
set @sql = @sql + 'from ScoreView group by [name]' --拼尾
--print @sql
exec(@sql)
--############################################
--题目: 一张表有一万多条记录，表的第一个字段 RecID 是自增长字段， 写一个SQL语句， 找出表的第2001-2020记录。
--思路: 先找出20条记录, 这20条记录不在前2000条里面
-- 可以考虑做成存储过程, 用两个变量控制起始和终止的位置
--注意: 在Sql2005中, top函数后边可以跟变量, 但是sql2000不可以. 因此, 为增加可移植性, 采用SQL92的拼串的方法
select top 20 recid, field1,field2
from TestTable
where recid not in (
select top 2000 recid from TestTable where recid > -1) --子查询加recid > -1将使子查询也使用索引
--存储过程1:
create proc GetPage(
@start int,
@end int
)
as
declare @sql nvarchar(2000)
begin
set @sql='select top '+ str(@end-@start+1) + ' recid, f1,f2,f3 from T where recid not in (
select top '+str(@start-1) +' recid from T where recid>-1)'
exec(@sql)
end
--存储过程2:
drop procedure GetPage
go
create procedure GetPage(
@start int,
@end int,
@table nvarchar(50)
)
as
declare @sql nvarchar(2000)
declare @id nvarchar(50)
begin
set @sql = 'select top ' --拼头
set @id = (select top 1 col_name(sc.object_id,sc.column_id)
from sys.columns sc
where sc.object_id = object_id(@table))
--print @id
set @sql = @sql + str(@end - @start + 1) + ' * from ' + @table + ' where ' +@id + ' not in ( select top ' + str(@start-1) +@id+' from ' + @table + ' where ' + @id
set @sql = @sql + ' > str(-1) )' --拼尾
--print @sql
exec(@sql) --exec sp_executesql @sql, 也可以执行@sql
end
go
--测试
exec GetPage 5,10,SC --该表第一列有重复, 所以结果有偏差
exec GetPage 3,6,students
--############################################
--用户函数的递归调用 --查找某id的子id(下线)数量
create function GetSons(
@id int
)
return int
as
declare @num int
begin
set @num = 0
select @num = count(*) from T where pid = @id
if(@num > 0)
begin
select @num = @num + dbo.GetSons(id) from T where pid = @id --id是T表的主键
end
return @num
end
--############################################
--返回TableName表的主键列名, --网上找的
SELECT i.name AS IndexName, OBJECT_NAME(ic.OBJECT_ID) AS TableName, COL_NAME(ic.OBJECT_ID,ic.column_id) AS ColumnName,
isnull(e.[value], '') as evalue
FROM sys.indexes AS i
INNER JOIN sys.index_columns AS ic ON i.OBJECT_ID = ic.OBJECT_ID AND i.index_id = ic.index_id
left join sys.extended_properties e on e.major_id=ic.object_id and e.minor_id=ic.Column_id and e.class=1
WHERE i.is_primary_key = 1
and ic.OBJECT_ID=object_id('Results')
order by ic.column_id
代码
