数据库SQL语句--单表查询 上机练习（1）

--警情数据系统的表结构
CREATE DATABASE 警情数据;
CREATE TABLE dbo.人员表(
	身份证号 nvarchar(255)  NOT NULL,
	出生日期 datetime NULL,
	文化程度 nvarchar(255)  NULL,
	案件编号 nvarchar(255)  NOT NULL,
	警情类别 nvarchar(255)  NULL,
	户籍地 nvarchar(255)  NULL,
    PRIMARY KEY CLUSTERED (身份证号,案件编号)
)

--1、运行给定代码，生成人员表及数据。共4999条
select * from 人员表;

--2、给人员表增加年龄字段
alter table 人员表 add 年龄 int;

--3、计算并更新年龄字段的值，计算方法为截止至2021-12-31的年龄。
update 人员表
set 年龄 = 2021 - year(出生日期);

--4、计算每个年龄的人数，按人数从高到低排序
select count(*) as 人数 from 人员表
group by 年龄
order by 人数 desc;
--5、求涉毒品类案件（指警情类别中包含‘毒品’，后题同）各年龄及人数，显示前5个，按人数从高到低排序
select top 5 年龄,count(*) as 人数 from 人员表
where 警情类别 like '%毒品%'
group by 年龄
order by 人数 desc
 
--6、求涉盗窃类案件的最大年龄、最小年龄、平均年龄
select max(年龄) as 最大年龄,min(年龄) as 最小年龄,avg(年龄) as 平均年龄
from 人员表
where 警情类别 like '%盗窃%';

--7、统计各户籍地每种警情类别的数量，按照户籍地从高到低排序，户籍地相同的，按照警情数量从高到低排序。
select 户籍地,警情类别,count(*) as 警情数量 from 人员表
group by 户籍地,警情类别
order by 户籍地 desc,警情数量 desc;

--8、只显示上一题中数量大于等于50的那些数据。
select 户籍地,警情类别,count(*) 警情数量 from 人员表
group by 户籍地,警情类别
having count(*)>=50
order by 户籍地 desc,警情数量 desc;

--9、按户籍地统计每个地区年龄小于30岁，参与毒品案件的人数。
--列出其中人数大于等于5人的地区及人数，按人数降序排列
select top 5 户籍地,count(*) as 小于30参与毒品人数
from 人员表
where 年龄<30 and 警情类别 like '%毒品%'
group by 户籍地
order by 小于30参与毒品人数 desc;