SQL：聚合函數(shù)，分組查詢，多表查詢，按照月份統(tǒng)計(jì)，電腦不是滿屏解決方法，倒序輸出
題目：
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.查詢女歌手的數(shù)量
--3.查詢存款最多和最低的存款
--4.查詢所有男歌手的總存款
--5.按照地區(qū)統(tǒng)計(jì)每個(gè)地區(qū)的存款
--6.按照月份統(tǒng)計(jì)每月的生日人數(shù)
--7.按照歌手類別分類,查找每個(gè)類別存款大于40000的人的姓名及存款
--8.統(tǒng)計(jì)每個(gè)歌手有幾首歌曲
--9.按照歌手類別查詢每個(gè)歌手的id和名稱倒序輸出
--10.按照歌手名稱長度統(tǒng)計(jì)歌手的人數(shù)及總存款
我自己寫的答案：
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.查詢女歌手的數(shù)量
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.按照地區(qū)統(tǒng)計(jì)每個(gè)地區(qū)的存款
select Area as '地區(qū)' ,sum(Cunkuan)as '每個(gè)地區(qū)的存款' from singer group by Area
--6.按照月份統(tǒng)計(jì)每月的生日人數(shù)
select month(Birthday) as '月份' ,count(SingerName)as '每月的生日人數(shù)' from singer group by month(Birthday)
--7.按照歌手類別分類,查找每個(gè)類別存款大于40000的人的姓名及存款
select SingerType as '歌手類別' ,SingerName as '每個(gè)類別存款大于40000的人的姓名'
,Cunkuan as '每個(gè)類別存款大于40000的人的存款'
?from singer where Cunkuan >40000 group by SingerType ,SingerName,Cunkuan?
--8.統(tǒng)計(jì)每個(gè)歌手有幾首歌曲
select SingerName as '歌手姓名' ,count(Title) as '歌手歌曲數(shù)' from singer as si ,song as so where si.SingerID=so.SingerID
group by SingerName
--9.按照歌手類別查詢每個(gè)歌手的id和姓名倒序輸出
select SingerType as '歌手類別',SingerID as '歌手的id' ,SingerName as '歌手的的姓名'
from singer? ?group by SingerType,SingerID,SingerName order By SingerID desc
--10.按照歌手名稱長度統(tǒng)計(jì)歌手的人數(shù)及總存款
select len(SingerName) as '歌手名稱長度' ,count(SingerName) as '歌手的人數(shù)' ,sum(Cunkuan) as '歌手的總存款'
from singer? ?group by len(SingerName)
