SQL：聚合函数，分组查询，多表查询，按照月份统计，电脑不是满屏解决方法，倒序输出
题目：
create database superKTV1
use superKTV1
create table singer(
SingerID int primary key identity(1,1),
SingerName nvarchar(20) not null,
SingerType nvarchar(10) check(singertype='男歌手' or singertype='女歌手'),
Birthday date,
Area nvarchar(50),
PhotoPath nvarchar(200),
Cunkuan decimal(18,2)
)
create table song(
SongID int primary key identity(1,1),
Title nvarchar(50) not null,
SingerID int,
Hit int default 0
Foreign key (SingerID) references singer(SingerID)
)
-------------------------------------
insert into singer values('张三','男歌手','1999-08-08','山东','E:\备课文件夹\SCCE G1课件 8\大一',80000.12)
insert into singer values('王丽丽','女歌手','1990-06-08','山西','E:\SCCE G1课件 8\大一',50000.26)
insert into singer values('李菲菲','女歌手','1995-07-08','湖北','E:\备课文件夹\SCCE G1课件 8\大一',40000.33)
insert into singer values('陈德勇','男歌手','1994-03-12','湖南','E:\SCCE G1课件 8\大一',80000.57)
insert into singer values('王兴','男歌手','1990-06-08','北京','E:\备课文件夹\SCCE G1课件 8\大一',40000.44)
insert into singer values('李晴','女歌手','1993-06-08',null,'E:\备课文件夹\SCCE G1课件 8\大一',60000.44)
insert into singer values('崔雨','女歌手','1994-06-08',null,'E:\备课文件夹\SCCE G1课件 8\大一',30000.44)
insert into singer values('靠山王','男歌手','1993-03-08',null,'E:\备课文件夹\SCCE G1课件 8\大一',30000.44)
insert into singer values('小王八','男歌手','1992-04-08',null,'E:\备课文件夹\SCCE G1课件 8\大一',30000.44)
insert into singer values('大王小王','男歌手','1992-04-08',null,'E:\备课文件夹\SCCE G1课件 8\大一',30000.44)
insert into singer values('Mary','女歌手','1992-04-08',null,'E:\备课文件夹\SCCE G1课件 8\大一',10000.44)
insert into singer values('Raven','女歌手','1992-04-08',null,'E:\备课文件夹\SCCE G1课件 8\大一',20000.44)
insert into singer values('Asia','男歌手','1992-04-08',null,'E:\备课文件夹\SCCE G1课件 8\大一',40000.44)
insert into singer values('八神','女歌手','1992-04-08',null,'E:\备课文件夹\SCCE G1课件 8\大一',-20000.44)
insert into singer values('大蛇','男歌手','1992-04-08',null,'E:\备课文件夹\SCCE G1课件 8\大一',-40000.44)
--1.查询男歌手的平均存款
--2.查询女歌手的数量
--3.查询存款最多和最低的存款
--4.查询所有男歌手的总存款
--5.按照地区统计每个地区的存款
--6.按照月份统计每月的生日人数
--7.按照歌手类别分类,查找每个类别存款大于40000的人的姓名及存款
--8.统计每个歌手有几首歌曲
--9.按照歌手类别查询每个歌手的id和名称倒序输出
--10.按照歌手名称长度统计歌手的人数及总存款
我自己写的答案：
create database superKTV1
use superKTV1
drop table singer
create table singer(
SingerID int primary key identity(1,1),
SingerName nvarchar(20) not null,
SingerType nvarchar(10) check(singertype='男歌手' or singertype='女歌手'),
Birthday date,
Area nvarchar(50),
PhotoPath nvarchar(200),
Cunkuan decimal(18,2)
)
drop table song
create table song(
SongID int primary key identity(1,1),
Title nvarchar(50) not null,
SingerID int,
Hit int default 0
Foreign key (SingerID) references singer(SingerID)
)
insert into singer values('张三','男歌手','1999-08-08','山东','E:\备课文件夹\SCCE G1课件 8\大一',80000.12)
insert into singer values('王丽丽','女歌手','1990-06-08','山西','E:\SCCE G1课件 8\大一',50000.26)
insert into singer values('李菲菲','女歌手','1995-07-08','湖北','E:\备课文件夹\SCCE G1课件 8\大一',40000.33)
insert into singer values('陈德勇','男歌手','1994-03-12','湖南','E:\SCCE G1课件 8\大一',80000.57)
insert into singer values('王兴','男歌手','1990-06-08','北京','E:\备课文件夹\SCCE G1课件 8\大一',40000.44)
insert into singer values('李晴','女歌手','1993-06-08',null,'E:\备课文件夹\SCCE G1课件 8\大一',60000.44)
insert into singer values('崔雨','女歌手','1994-06-08',null,'E:\备课文件夹\SCCE G1课件 8\大一',30000.44)
insert into singer values('靠山王','男歌手','1993-03-08',null,'E:\备课文件夹\SCCE G1课件 8\大一',30000.44)
insert into singer values('小王八','男歌手','1992-04-08',null,'E:\备课文件夹\SCCE G1课件 8\大一',30000.44)
insert into singer values('大王小王','男歌手','1992-04-08',null,'E:\备课文件夹\SCCE G1课件 8\大一',30000.44)
insert into singer values('Mary','女歌手','1992-04-08',null,'E:\备课文件夹\SCCE G1课件 8\大一',10000.44)
insert into singer values('Raven','女歌手','1992-04-08',null,'E:\备课文件夹\SCCE G1课件 8\大一',20000.44)
insert into singer values('Asia','男歌手','1992-04-08',null,'E:\备课文件夹\SCCE G1课件 8\大一',40000.44)
insert into singer values('八神','女歌手','1992-04-08',null,'E:\备课文件夹\SCCE G1课件 8\大一',-20000.44)
insert into singer values('大蛇','男歌手','1992-04-08',null,'E:\备课文件夹\SCCE G1课件 8\大一',-40000.44)
select * from singer
insert into song(Title ,SingerID)values('男孩你快来',1)
insert into song( Title ,SingerID)values('女孩你快来',2)
insert into song( Title ,SingerID )values('男孩你别来',3)
insert into song( Title ,SingerID )values('男孩你得来',4)
insert into song(Title ,SingerID)values('女孩你得来',5)
insert into song(Title ,SingerID)values('女孩你得',6)
insert into song(Title ,SingerID)values('女孩你',7)
insert into song(Title ,SingerID)values('男孩你快',8)
insert into song( Title ,SingerID)values('女孩你快',9)
insert into song( Title ,SingerID )values('男孩你别',10)
insert into song( Title ,SingerID )values('男孩你得',11)
insert into song(Title ,SingerID)values('女孩你',12)
insert into song(Title ,SingerID)values('女孩来',13)
insert into song(Title ,SingerID)values('女孩别',14)
insert into song(Title ,SingerID)values('女孩快',15)
insert into song(Title ,SingerID)values('女孩,come on!',15)
--1.查询男歌手的平均存款
select avg(Cunkuan) from singer where SingerType in('男歌手')
--2.查询女歌手的数量
select count(SingerName) from singer where SingerType in('女歌手')
--3.查询存款最多和最低的存款
select max(Cunkuan) as '存款最多',min(Cunkuan) as '最低的存款' from singer
--4.查询所有男歌手的总存款
select sum(Cunkuan) as '所有男歌手的总存款' from singer where SingerType in('男歌手')
--5.按照地区统计每个地区的存款
select Area as '地区' ,sum(Cunkuan)as '每个地区的存款' from singer group by Area
--6.按照月份统计每月的生日人数
select month(Birthday) as '月份' ,count(SingerName)as '每月的生日人数' from singer group by month(Birthday)
--7.按照歌手类别分类,查找每个类别存款大于40000的人的姓名及存款
select SingerType as '歌手类别' ,SingerName as '每个类别存款大于40000的人的姓名'
,Cunkuan as '每个类别存款大于40000的人的存款'
from singer where Cunkuan >40000 group by SingerType ,SingerName,Cunkuan
--8.统计每个歌手有几首歌曲
select SingerName as '歌手姓名' ,count(Title) as '歌手歌曲数' from singer as si ,song as so where si.SingerID=so.SingerID
group by SingerName
--9.按照歌手类别查询每个歌手的id和姓名倒序输出
select SingerType as '歌手类别',SingerID as '歌手的id' ,SingerName as '歌手的的姓名'
from singer group by SingerType,SingerID,SingerName order By SingerID desc
--10.按照歌手名称长度统计歌手的人数及总存款
select len(SingerName) as '歌手名称长度' ,count(SingerName) as '歌手的人数' ,sum(Cunkuan) as '歌手的总存款'
from singer group by len(SingerName)
