【SQL Server】常用Code及练习题

发布时间：2024-09-18 19:01

数据库练习

利用空闲的时间每天练习两道,持续更新中...

0.常用CHECK CODE

(1). 查看不同库表间的依赖关系

select distinct name 
from sysobjects(nolock) o, syscomments(nolock) s 
where o.id = s.id 
and text like \'%COFIS_STG_GCAP_PC_APPLICATION_DELTA%\' 
and o.xtype = \'P\'
order by name

select distinct object_name(id) 
from syscomments 
where id in (select id from sysobjects where type  in(\'V\',\'P\')) --V表示视图，P表示存储过程
and text like \'%ODS_BUSINESS_PORTFOLIO_REPORT_DM_ETL%\'--tablename为需要查询被引用的表名称

(2). 查找某个字段在哪张表里

select [name] 
from [库名].[dbo].sysobjects 
where id in(
	select id 
	from [库名].[dbo].syscolumns 
	Where name=\'字段名\'
)

--多个字段
select [name] 
from [ODS].[dbo].sysobjects 
where id in(
	--select id 
	--from [ODS].[dbo].syscolumns 
	--Where name=\'Contract_No\'

	--InterSect--交集
	--Except--差集
	--Union--并集

	select id 
	from [ODS].[dbo].syscolumns 
	Where name=\'Applicant_Name\' 
)

(3). 查看pro在不在用

SELECT * FROM msdb.dbo.sysjobs JOB WITH( NOLOCK) 
INNER JOIN msdb. dbo.sysjobsteps STP WITH(NOLOCK ) 
ON STP .job_id = JOB .job_id WHERE 
STP .command LIKE \'%P_ETL_CMS_ODS_CONTRACT%\'

(4). 模糊查表名

select name 
from sys.objects 
where type=\'U\' and name like \'%date%\';

(5). 查看存储过程在哪些作业中被调用

SELECT *
FROM msdb.dbo.sysjobs JOB WITH( NOLOCK)
INNER JOIN msdb. dbo.sysjobsteps STP WITH(NOLOCK )
ON STP .job_id = JOB .job_id
WHERE STP .command LIKE N\'%PROC_COLLECTIONS_DATA%\'

(6). 查询存储过程和视图是否用到表

select distinct object_name(id) from syscomments 
where id in (
	select id 
	from sysobjects 
	where type  in(\'V\',\'P\',\'U\')
	) --V表示视图，P表示存储过程，\'U\'用户表
and text like \'%BP_MAIN%\'--tablename为需要查询被引用的表名称

(7). 查询存储过程和视图是否用到表字段

--查询存储过程视图是否用到表和字段
select name from sysobjects as s
inner  join syscomments as o
on s.id=o.id 
where text like N\'%表名%字段名%\'

(8). 快速比较两个张，相同和不同的数据！

--不同数据
 select  * from b
 except
 select  * from a

--相同数据
 select  * from a
 intersect
 select  * from b

(9). 查看某个proc的执行时间记录

SELECT TOP 100 db_name(d.database_id) as DBName,
    s.name as 存储名称,
    s.type_desc as 存储类型,
    d.cached_time as SP添加到缓存的时间,
    d.last_execution_time as 上次执行SP的时间,
    d.last_elapsed_time as [上次执行SP所用的时间（微妙）],
    d.total_elapsed_time as [完成此SP的执行所用的总时间（微妙）],
    d.total_elapsed_time/d.execution_count as [平均执行时间（微妙）],
    d.execution_count as 自上次编译以来所执行的次数
FROM SYS.procedures S JOIN SYS.dm_exec_procedure_stats D
    ON S.object_id=D.object_id
WHERE S.NAME=\'P_COFIS_STG_PBOC_CORP_CAPTOWN_FULL\'
ORDER BY D.total_elapsed_time/D.execution_count DESC

(10). 查看存储过程在哪些作业中被调用

SELECT description,last_run_date,last_run_time,*
FROM msdb.dbo.sysjobs JOB WITH( NOLOCK)
INNER JOIN msdb. dbo.sysjobsteps STP WITH(NOLOCK )
ON STP .job_id = JOB .job_id
WHERE STP .command LIKE N\'%SP_Business_Portfolio_Report_DM%\'

(11). 查看表的主键

1)利用SQL自带的存储过程找到SqlServer主键
EXEC sp_pkeys @table_name=\'表名\'

2)利用系统表INFORMATION_SCHEMA.KEY_COLUMN_USAGE 找到SqlServer主键
SELECT TABLE_NAME,COLUMN_NAME 
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE   
WHERE TABLE_NAME=\'CONTRACT_ASSET_COLLATERAL_R\'

(12).保留小数

1)cast 字段 as float  --(保留两位小数)
2)ROUND(字段,2)

(13).限制工作日 T+1

Convert(datetime,Convert(VARCHAR,[时间字段],112)) >= Convert(datetime,Convert(VARCHAR,CASE WHEN Datepart(dw,Getdate()) = 2 THEN Getdate() - 3 ELSE Getdate() - 1 END,112))

(14).Check DB 磁盘空间

WITH DB_Disk_Space
AS (

SELECT	
	 Team			= SUBSTRING( \'NONPROD01\',0,len(\'NONPROD01\')-1)
	,Instance		= @@SERVERNAME
    ,database_name	= DB_NAME(database_id)
    , log_size_mb   = CAST(SUM(CASE WHEN type_desc = \'LOG\' 	THEN size END) * 8.0 / 1024 AS DECIMAL(25,2))
    , row_size_mb   = CAST(SUM(CASE WHEN type_desc = \'ROWS\' THEN size END) * 8.0 / 1024 AS DECIMAL(25,2))
    , total_size_GB = CAST(SUM(size) * 8.0 / (1024*1024*1024) AS DECIMAL(25,2)) 
	,Check_Date		= CONVERT(varchar(100),getdate(),23)
FROM sys.master_files WITH(NOWAIT)
GROUP BY database_id with ROLLUP

)

(15) 零时Fix 数据

IF EXISTS (SELECT 1 FROM COFIS_STAGING.sys.tables WHERE  name = \'STG_COFIS_CONTRACT_BP_Temp\')
BEGIN
	DROP TABLE COFIS_STAGING..STG_COFIS_CONTRACT_BP_Temp
END

select * into COFIS_STAGING..STG_COFIS_CONTRACT_BP_Temp from COFIS_STAGING..STG_COFIS_CONTRACT_BP

update AA
set AA.ETL_PROCESSED_IND=\'T\'
from( 
select
   CONTRACT_ID,            
   CB.BUSINESS_PARTNER_ID,     
   ISNULL(BR.ROLE_CDE, CB.ROLE_CDE) as role_cde,
   CB.ETL_PROCESSED_IND,
   ROW_NUMBER() over(partition BY CB.CONTRACT_ID,CB.BUSINESS_PARTNER_ID,  ISNULL(BR.ROLE_CDE, CB.ROLE_CDE) order by CB.CONTRACT_ID,CB.BUSINESS_PARTNER_ID, ISNULL(BR.ROLE_CDE, CB.ROLE_CDE) )  rowNums          
FROM  COFIS_STAGING..STG_COFIS_CONTRACT_BP CB            
LEFT  JOIN  ODS_BP_ROLE_CODE BR ON BR.CS_CODE = CB.ROLE_CDE            
WHERE  ETL_PROCESSED_IND = \'F\'            
          --   and CONTRACT_ID=\'CN2109020095\'    and  CB.BUSINESS_PARTNER_ID=\'1595047\'          
    AND  NOT EXISTS             
   (SELECT 1            
      FROM ODS_CONTRACT_BP A             
     WHERE A.CONTRACT_ID = CB.CONTRACT_ID            
       AND A.BUSINESS_PARTNER_ID = CB.BUSINESS_PARTNER_ID            
       AND A.ROLE_CDE = ISNULL(BR.ROLE_CDE, CB.ROLE_CDE)            
    ) 
) AA
where AA.rowNums>1

(16) Restore DB

USE [master]
ALTER DATABASE [ODS] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE [ODS] FROM  DISK = N\'\\\\Path\\ODS\\ODS_Full_210306.BAK\' WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 5
ALTER DATABASE [ODS] SET MULTI_USER
GO

查看Restore进度

SELECT 
	session_id,
	percent_complete,
	DATEADD(MILLISECOND,estimated_completion_time,CURRENT_TIMESTAMP) Estimated_finish_time,
	(total_elapsed_time/1000)/60 Total_Elapsed_Time_MINS,
	DB_NAME(Database_id) Database_Name,
	command,sql_handle
FROM sys.dm_exec_requests WHERE session_id=189

(17). BACKUP DB

非压缩
BACKUP DATABASE PBOC_Interface TO DISK =\'\\\\Path\\PBOC_Interface_Full_190909.BAK\' WITH  INIT ,  COPY_ONLY;

压缩
BACKUP DATABASE COFIS_Staging  TO DISK =\'\\\\Path\\COFIS_Staging_210924.BAK\'    WITH RETAINDAYS = 15, FORMAT, compression, maxtransfersize = 1048576,   NAME = \'COFIS_Staging\',   SKIP, NOREWIND, NOUNLOAD,  STATS = 10,COPY_ONLY;

declare @File_name_BAK varchar(200)
declare @Backup_date varchar(200)
declare @Back_Path varchar(200)

select @Backup_date = CONVERT(varchar(200), CAST(GETDATE() AS DATE), 12)
set @Back_Path = \'\\\\SFCN0195\\Path\\Dragon\\\'
set @File_name_BAK = @Back_Path + \'GCAP_Full_\'+@Backup_date + \'.BAK\'

begin

--print @File_name_BAK

BACKUP DATABASE DB_NAME TO DISK = @File_name_BAK WITH RETAINDAYS = 15, FORMAT, compression, maxtransfersize = 1048576,   NAME = \'DB_NAME\', SKIP, NOREWIND, NOUNLOAD,  STATS = 10,COPY_ONLY;

end

(18). 按照分隔符“|”,将一行数据转成多行
$\"【SQL$

--Old
select REJECTION_COMMENTS 
from APPLICATION_COMMENTS_R
where APPLICATION_ID=\'31xxxxxxxx07\'

--New
select a.*, substring(a.REJECTION_COMMENTS, b.number, charindex(\'|\', a.REJECTION_COMMENTS + \'|\', b.number) - b.number) as name
from APPLICATION_COMMENTS_R a
join master..spt_values b on b.type = \'P\'
where charindex(\'|\', \'|\' + a.REJECTION_COMMENTS, b.number) = b.number
and a.APPLICATION_ID=\'31xxxxxxxx07\'

1.建表

students–学生表

建表

create table Students(
	sid varchar(10),
	sname varchar(10),
	sage datetime,
	ssex nvarchar(10)
);

--学生表
Student(SId,Sname,Sage,Ssex)
--SId 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别

$\"【SQL$

teacher–教师表

建表

create table Teacher(
	tid varchar(10),
	tname varchar(10)
);

--教师表
Teacher(TId,Tname)
--TId 教师编号,Tname 教师姓名

$\"【SQL$

SC–成绩表

建表

create table SC(
	sid varchar(10),
	cid varchar(10),
	score decimal(18,1)
);

--成绩表
SC(SId,CId,score)
--SId 学生编号,CId 课程编号,score 分数

$\"【SQL$

course–课程表

建表

create table Course(
	cid varchar(10),
	cname varchar(10),
	tid varchar(10)
);

--课程表
Course(CId,Cname,TId)
--CId 课程编号,Cname 课程名称,TId 教师编号

$\"【SQL$

2.查询语句

题目
1、查询“01”课程比“02”课程成绩高的所有学生的学号；

方式一：

select sc01.sid
from (select * from sc where cid=\"01\") as sc01 left join (select * from sc where cid=\"02\") as sc02 on sc01.sid=sc02.sid
where sc01.score>sc02.score;

方式二：

select 
	a.sid,
	a.a01,
	a.a02,
	row_number() over(partition by a.sid order by a.sid ) aindex
from (
select
	student.sid,
	sum(case when sc.cid=\'01\' then score end) as \'a01\',
	sum(case when sc.cid=\'02\' then score end) as \'a02\'
from student,sc,course,teacher
where student.sid=sc.sid and sc.cid=course.cid and course.tid=teacher.tid
group by student.sid) a
where a.a01 > a.a02

2、查询平均成绩大于60分的同学的学号和平均成绩；

SELECT sid,round(avg(score),2) AS 平均成绩
FROM sc 
GROUP BY sid
HAVING avg(score)>60;

3、查询所有同学的学号、姓名、选课数、总成绩

SELECT students.sid,students.sname,count(*) as 选课数,sum(SC.score) as 总成绩
FROM students LEFT JOIN SC ON students.sid=SC.sid
GROUP BY students.sid;

4、查询姓“李”的老师的个数；

select count(*)  as 姓李老师的个数
from teacher 
where tname like \"李%\";

5、查询没学过“张三”老师课的同学的学号、姓名；

方式一：

select sid,sname
from students
where sid not in (
	select sid 
	from sc
	where cid =(
		select cid 
		from course 
		where tid=(
			select tid 
			from teacher 
			where tname=\"张三\"
			)
		)
);

方式二：

select *
	from(
	select 
		cs.cid,	
		cs.cname,		
		Student.sid,		
		SC.score,		
		Student.sname,	
		Student.sage,	
		Student.ssex,	
		Teacher.tid,	
		Teacher.tname
	from Course cs,SC,Student,Teacher 
	where Teacher.tid=cs.tid and cs.cid=Sc.cid and sc.sid=Student.sid
) a
where a.tname<>\'张三\'

6、查询学过编号“01”并且也学过编号“02”课程的同学的学号、姓名；

select students.sid,students.sname
from students left join sc on students.sid=sc.sid
where cid in(\"01\",\"02\")
group by students.sid
having count(*)=2;

7、查询学过“张三”老师所教的课的同学的学号、姓名；

select students.sid,students.sname from students left join (
select * from sc  where cid=(
	select cid from course  where tid=(
		select tid  from teacher where tname=\"张三\"
		)
	)
) as zhang on students.sid=zhang.sid;

8、查询课程编号“01”的成绩比课程编号“02”课程低的所有同学的学号、姓名；

select * 
from students left join (
	select sid
	,sum(if(cid=\"01\",score,0)) as \"01\"
	,sum(if(cid=\"02\",score,0)) as \"02\"
from sc
group by sid) as guan on students.sid=guan.sid
where guan.01<guan.02;

9、查询所有课程成绩小于60分的同学的学号、姓名；

select students.sid,students.sname
from students right join (
	select sid
	from sc
	group by sid
	having count(*)=sum(if(score<60,1,0))
	) as guan on students.sid=guan.sid;

10、查询没有学全所有课的同学的学号、姓名；

select students.sid,students.sname
from students right join (
	select sid
	from sc
	group by sid
	having count(*)!=3
	) as guan on students.sid=guan.sid;

11、查询至少有一门课与学号为“01”的同学所学相同的同学的学号和姓名；

select distinct students.sid,students.sname 
from students left join sc on students.sid=sc.sid
where cid in(select cid from sc where sid=\"01\");

12、查询和\"01\"号的同学学习的课程完全相同的其他同学的学号和姓名

方式一

select sid,sname
from Student
where sid in(
	select sid
	from (
			select distinct sid,
			cid = ( stuff( (select \',\' + cid from SC where sid = a.sid for xml path(\'\')), 1, 1, \'\' ) )
			from SC as a
			) b
	where cid =( 
				select cid from(
					select * from (
							select distinct sid, cid = ( stuff((select \',\' + cid from SC where sid = a.sid for xml path(\'\')), 1, 1, \'\' ) )
							from SC as a
						) b
				) c
				where sid=01)
)

方式二

select t1.sid,sname
from(
	select sc.sid,count(distinct sc.cid) as count_
	from(
		select cid
		from sc
		where sid=\'01\'
		)t1 --#选出01的同学所学的课程
	left join sc
	on t1.cid=sc.cid
	group by sc.sid
	having count(distinct sc.cid)= (select count(distinct cid) from sc where sid = \'01\')
) t1
left join student
on t1.sid=student.sid
where t1.sid!=\'01\'

13、把“SC”表中“张三”老师教的课的成绩都更改为此课程的平均成绩；

update dd
set 
	dd.score =(
		select Convert(decimal(18,2),AVG(score))  
		from Course c,Teacher t,SC s
		where t.tid=c.tid and s.cid=c.cid
		group by t.tname
		having tname=\'张三\'
)
from(
	select * 
	from(
		select c.cid,cname,t.tid,tname,sid,score
		from Course c,Teacher t,SC s
		where t.tid=c.tid and s.cid=c.cid
	) cc
	where cc.tname=\'张三\'
) dd

14、查询没学过\"张三\"老师讲授的任一门课程的学生姓名

select distinct sname
from 
(
	select 
	c.cid,cname,t.tid,tname,s.sid,score,sname,sage,ssex
	from Course c,Teacher t,SC s,Student st
	where t.tid=c.tid and s.cid=c.cid and st.sid=s.sid
) a
where a.tname <> \'张三\'

15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

select 
	sid,
	sname,
	Convert(decimal(18,2),AVG(score)) as \'scoreAvg\'
from 
(
	select 
	c.cid,cname,t.tid,tname,s.sid,score,sname,sage,ssex
	from Course c,Teacher t,SC s,Student st
	where t.tid=c.tid and s.cid=c.cid and st.sid=s.sid
) a
group by sid,sname
having sum(case when score<60 then 1 else 0 end) >=2

16、检索\"01\"课程分数小于60，按分数降序排列的学生信息

select 
	sid,sname,sage,ssex,cid,cname,score
from
(
	select 
	c.cid,cname,t.tid,tname,s.sid,score,sname,sage,ssex
	from Course c,Teacher t,SC s,Student st
	where t.tid=c.tid and s.cid=c.cid and st.sid=s.sid
) a
where cid=\'01\' and score<60
order by score desc

17、按平均成绩从高到低显示所有学生的平均成绩

select 
	sid,sname,Convert(decimal(18,2),AVG(score)) as \'scoreAvg\'
from
(
	select 
	c.cid,cname,t.tid,tname,s.sid,score,sname,sage,ssex
	from Course c,Teacher t,SC s,Student st
	where t.tid=c.tid and s.cid=c.cid and st.sid=s.sid
) a
group by sid,sname
order by AVG(score) desc

18、查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率

select 
	cid,cname,
	MAX(score) max,
	MIN(score) min,
	case when 
		SUM(case when score>=0 then 1 else 0 end)=0 then \'0.00%\' 
	else 
		convert(varchar,CONVERT(decimal(18, 2),100*(SUM(case when score>=60 then 1 else 0 end))) * 1.0 / SUM(case when score>=0 then 1 else 0 end))+\'%\' 
	end \'及格率\'
from
(
	select 
	c.cid,cname,t.tid,tname,s.sid,score,sname,sage,ssex
	from Course c,Teacher t,SC s,Student st
	where t.tid=c.tid and s.cid=c.cid and st.sid=s.sid
) a
group by cid,cname

19、按各科平均成绩从低到高和及格率的百分数从高到低顺序

select 
	cid,cname,
	AVG(score) avg_,
	case when 
		SUM(case when score>=0 then 1 else 0 end)=0 then \'0.00%\' 
	else 
		convert(varchar,CONVERT(decimal(18, 2),100*(SUM(case when score>=60 then 1 else 0 end))) * 1.0 / SUM(case when score>=0 then 1 else 0 end))+\'%\' 
	end passingRate
from
(
	select 
	c.cid,cname,t.tid,tname,s.sid,score,sname,sage,ssex
	from Course c,Teacher t,SC s,Student st
	where t.tid=c.tid and s.cid=c.cid and st.sid=s.sid
) a
group by cid,cname
order by avg_ asc,passingRate desc

20、查询学生的总成绩并进行排名

select sid,sname,SUM(score) scoreSum 
from
(
	select 
	c.cid,cname,t.tid,tname,s.sid,score,sname,sage,ssex
	from Course c,Teacher t,SC s,Student st
	where t.tid=c.tid and s.cid=c.cid and st.sid=s.sid
) a
group by sid,sname
order by scoreSum desc

21、查询不同老师所教不同课程平均分从高到低显示

select tid,tname,cname,AVG(score) scoreAvg
from
(
	select 
	c.cid,cname,t.tid,tname,s.sid,score,sname,sage,ssex
	from Course c,Teacher t,SC s,Student st
	where t.tid=c.tid and s.cid=c.cid and st.sid=s.sid
) a
group by tid,tname,cname
order by scoreAvg desc

22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩

select sid,sname,sage,ssex,cid,cname,score,aindex
from
(
	select 
	c.cid,cname,t.tid,tname,s.sid,score,sname,sage,ssex,
	row_number() over(partition by c.cid order by s.sid ) aindex
	from Course c,Teacher t,SC s,Student st
	where t.tid=c.tid and s.cid=c.cid and st.sid=s.sid
) a
where aindex between  2 and 3

23、统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比

select
	cid,cname,
    SUM(case  when score between 0 and 60 then 1 else 0 end) [0-60],
	Round(convert(float,SUM(case  when score between 0 and 60 then 1 else 0 end))/convert(float,COUNT(score)),2) 占比,
	SUM(case  when score between 60 and 70 then 1 else 0 end ) [60-70],
	Round(convert(float,SUM(case  when score between 60 and 70 then 1 else 0 end ))/convert(float,COUNT(score)),2) 占比,
	SUM(case  when score between 70 and 85 then 1 else 0 end ) [70-85],
	Round(convert(float,SUM(case  when score between 70 and 85 then 1 else 0 end ))/convert(float,COUNT(score)),2) 占比,
	SUM(case  when score between 85 and 100 then 1 else 0 end )[85-100],
	Round(convert(float,SUM(case  when score between 85 and 100 then 1 else 0 end ))/convert(float,COUNT(score)),2) 占比	
from
(
	select 
	c.cid,cname,t.tid,tname,s.sid,score,sname,sage,ssex,
	row_number() over(partition by c.cid order by s.sid ) aindex
	from Course c,Teacher t,SC s,Student st
	where t.tid=c.tid and s.cid=c.cid and st.sid=s.sid
) a
group by cid,cname

24、查询学生平均成绩及其名次

select sid,sname,AVG(score) avgScore,
row_number() over(order by AVG(score) desc) number
from
(
	select 
	c.cid,cname,t.tid,tname,s.sid,score,sname,sage,ssex
	from Course c,Teacher t,SC s,Student st
	where t.tid=c.tid and s.cid=c.cid and st.sid=s.sid
) a
group by sid,sname

25、查询各科成绩前三名的记录

select *
from
(
	select 
	c.cid,cname,t.tid,tname,s.sid,score,sname,sage,ssex,
	row_number() over(partition by c.cid order by score desc) aindex
	from Course c,Teacher t,SC s,Student st
	where t.tid=c.tid and s.cid=c.cid and st.sid=s.sid
) a
where aindex between 1 and 3

26、查询每门课程被选修的学生数

select cid,cname,COUNT(*) 人数
from
(
	select 
	c.cid,cname,t.tid,tname,s.sid,score,sname,sage,ssex
	from Course c,Teacher t,SC s,Student st
	where t.tid=c.tid and s.cid=c.cid and st.sid=s.sid
) a
group by cid,cname

27、查询出只选修了两门课程的全部学生的学号和姓名

select sid,sname
from 
(
	select sid,sname,cid,cname,row_number() over(partition by sid order by score desc) aindex
	from
	(
		select 
		c.cid,cname,t.tid,tname,s.sid,score,sname,sage,ssex
		from Course c,Teacher t,SC s,Student st
		where t.tid=c.tid and s.cid=c.cid and st.sid=s.sid
	) a
)b
group by sid,sname
having COUNT(sid)=2

28、查询男生、女生人数

select 
	sum(case  when ssex=\'男\' then 1 else 0 end) 男,
	sum(case  when ssex=\'女\' then 1 else 0 end) 女
from Student

29、查询名字中含有\"风\"字的学生信息

select *
from Student
where sname like \'%风%\'

30、查询同名同性学生名单，并统计同名人数

select 
	sname,ssex,
	row_number() over(partition by sname,ssex order by sid desc) aindex,
	case when COUNT(sname)=1 then \'无\' else sname end 同名人
from Student
group by sname,ssex,sid

31、查询1990年出生的学生名单(注：Student表中Sage列的类型是datetime)

select *
from Student
where YEAR(sage)=1990

32、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列

select cid,cname,AVG(score) 平均成绩
from
(
	select 
	c.cid,cname,t.tid,tname,s.sid,score,sname,sage,ssex,
	row_number() over(partition by c.cid order by score desc) aindex
	from Course c,Teacher t,SC s,Student st
	where t.tid=c.tid and s.cid=c.cid and st.sid=s.sid
) a
group by cid,cname
order by AVG(score),cid desc

37、查询不及格的课程，并按课程号从大到小排列

select 
	cid,cname,
	SUM(case when score between 0 and 60  then 1 else 0 end)  不及格人数,
	case when AVG(score)<60  then \'是\' else \'否\' end 不及格课程
from
(
	select 
	c.cid,cname,t.tid,tname,s.sid,score,sname,sage,ssex,
	row_number() over(partition by c.cid order by score desc) aindex
	from Course c,Teacher t,SC s,Student st
	where t.tid=c.tid and s.cid=c.cid and st.sid=s.sid
) a
group by cid,cname
having AVG(score)<60
order by cid desc

38、查询课程编号为\"01\"且课程成绩在60分以上的学生的学号和姓名；

select sid,sname
from
(
	select 
	c.cid,cname,t.tid,tname,s.sid,score,sname,sage,ssex
	from Course c,Teacher t,SC s,Student st
	where t.tid=c.tid and s.cid=c.cid and st.sid=s.sid
) a
where cid=01 and score>60

40、查询选修“张三”老师所授课程的学生中，成绩最高的学生姓名及其成绩

select *
from
(
	select 
	c.cid,cname,t.tid,tname,s.sid,score,sname,sage,ssex,
	row_number() over(partition by c.cid order by score desc) aindex
	from Course c,Teacher t,SC s,Student st
	where t.tid=c.tid and s.cid=c.cid and st.sid=s.sid
) a
where tname=\'张三\' and aindex=1

42、查询每门功课成绩最好的前两名

select *
from
(
	select 
	c.cid,cname,t.tid,tname,s.sid,score,sname,sage,ssex,
	row_number() over(partition by c.cid order by score desc) aindex
	from Course c,Teacher t,SC s,Student st
	where t.tid=c.tid and s.cid=c.cid and st.sid=s.sid
) a
where aindex between 1 and 2

43、统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

select cid,cname,COUNT(sid)
from
(
	select 
	c.cid,cname,t.tid,tname,s.sid,score,sname,sage,ssex,
	row_number() over(partition by c.cid order by score desc) aindex
	from Course c,Teacher t,SC s,Student st
	where t.tid=c.tid and s.cid=c.cid and st.sid=s.sid
) a
group by cid,cname
having COUNT(sid)>=5
order by COUNT(sid) desc,cid

44、检索至少选修两门课程的学生学号

select sid,sname,COUNT(sid) 选课数
from
(
	select 
	s.sid,score,sname,sage,ssex,c.cid,cname,t.tid,tname,
	row_number() over(partition by s.sid order by score desc) aindex
	from Course c,Teacher t,SC s,Student st
	where t.tid=c.tid and s.cid=c.cid and st.sid=s.sid
) a
group by sid,sname
having COUNT(sid)>=2

45、查询选修了全部课程的学生信息

select sid,sname,sage,ssex,COUNT(sid) 选课数
from
(
	select 
	s.sid,score,sname,sage,ssex,c.cid,cname,t.tid,tname,
	row_number() over(partition by s.sid order by score desc) aindex
	from Course c,Teacher t,SC s,Student st
	where t.tid=c.tid and s.cid=c.cid and st.sid=s.sid
) a
group by sid,sname,sage,ssex
having COUNT(sid)=3

46、查询各学生的年龄

select sage
from Student

47、查询本周过生日的学生

select *
from student
where weekofyear(sage)=weekofyear(curdate()) --mysql

48、查询下周过生日的学生

select *
from student
where weekofyear(sage) = weekofyear(date_add(curdate(),interval 1 week)) --mysql

49、查询本月过生日的学生

select *
from student
--where month(sage) = month(curdate()) --mysql
where MONTH(getdate())-MONTH(sage)=0  --sqlserver

50、查询下月过生日的学生

select *
from student
--where month(date_sub(sage,interval 1 month)) = month(curdate())  --mysql
where MONTH(getdate())-MONTH(sage)=-1  --sqlserver

【SQL Server】常用Code及练习题

数据库练习

0.常用CHECK CODE

1.建表

students–学生表

teacher–教师表

SC–成绩表

course–课程表

2.查询语句

相关推荐