1、创建如下表
歌手(歌手ID,歌手名,地区,年龄)
create table 歌手
(歌手ID char(8) primary key,
歌手名 char(12),
地区 char(4) check (地区 in('大陆','香港','台湾')),
年龄 int check(年龄<50))
insert into 歌手
values('g001','刘的华','香港',40)
insert into 歌手
values('g002','李于春','大陆',20)
insert into 歌手
values('g003','张于','香港',30)
insert into 歌手
values('g004','张会妹','台湾',30)
insert into 歌手
values('g005','齐情','台湾',32)
insert into 歌手
values('g006','罗大右','台湾',45)
演唱(歌手ID,歌曲ID,演唱时间)
insert into 演唱
values('g001','m001','2005-5-5')
insert into 演唱
values('g001','m002','2005-6-5')
insert into 演唱
values('g002','m003','2005-8-4')
insert into 演唱
values('g003','m004','2005-2-5')
create table 演唱
(歌手ID char(8) references 歌手(歌手id),
歌曲ID char(8)references 歌曲(歌曲id),
演唱时间 datetime,
primary key(歌手ID,歌曲ID))
歌曲(歌曲ID,歌曲名,歌曲风格)
create table 歌曲
(歌曲ID char(8) primary key ,
歌曲名 char(20),
歌曲风格 char(4)
check (歌曲风格 in('民族','通俗','美声'))
)
insert into 歌曲
values('m001','冰雨','通俗')
insert into 歌曲
values('m002','天意','通俗')
insert into 歌曲
values('m003','请你恰恰','通俗')
insert into 歌曲
values('m004','月亮惹的祸','美声')
其中歌手ID,歌曲ID分别为歌手和歌曲主键
演唱表中的主键为歌手ID和歌曲ID,
地区取值范围为(大陆,香港,台湾)
歌曲风格取值范围为(民族,通俗,美声)
歌手年龄小于50岁
歌手(歌手ID,歌手名,地区,年龄)
演唱(歌手ID,歌曲ID,演唱时间)
歌曲(歌曲ID,歌曲名,歌曲风格
2、查询大陆地区的歌手记录
select * from 歌手 where 地区='大陆'
3、查询香港地区年龄〈50岁的歌手记录
select * from 歌手
where 地区='香港' and 年龄<50
4、按年龄对台湾地区的歌手从大到小显示
select * from 歌手
where 地区='台湾'
order by 年龄 desc
5、查询姓名中有’华‘这个字的歌手记录
select * from 歌手
where 歌手名 like '%华%'
select * from 歌手
where 歌手名 like '__华'
6、统计大陆地区歌手平均年龄和歌手的人数
select '平均年龄'=avg(年龄),'人数'=count(*)
from 歌手
where 地区='大陆'
7、查询地区歌手的平均年龄〉30岁的地区
select 地区, '平均年龄'=avg(年龄)
from 歌手
group by 地区
having avg(年龄)>30
8、查询刘德华演唱的歌曲名
select 歌手名,歌曲名 from 歌手 a join 演唱 b
on a.歌手id=b.歌手id join 歌曲 c
on b.歌曲id=c.歌曲id
and 歌手名='刘的华'
9、查询年龄比刘德华小的歌手演唱的歌曲名
select 歌曲名 from 歌曲
where 歌曲id in
(select 歌曲id from 演唱
where 歌手id in
( select 歌手id from 歌手
where 年龄<
( select 年龄 from 歌手
where 歌手名='刘的华'
) ) )
10、查询大陆地区歌手演唱的歌曲名
select 歌手名,歌曲名 from 歌手 a join 演唱 b
on a.歌手id=b.歌手id join 歌曲 c
on b.歌曲id=c.歌曲id
and 地区='大陆'
11 、查询演唱歌曲'童话‘的歌手记录
select 歌手名,歌曲名 from 歌手 a join 演唱 b
on a.歌手id=b.歌手id join 歌曲 c
on b.歌曲id=c.歌曲id
and 歌曲名='天意'
12、修改香港地区的歌手演唱歌曲风格为通俗
update 歌曲
set 歌曲风格='民族'
where 歌曲id in
( select 歌曲id from 演唱
where 歌手id in
( select 歌手id from 歌手
where 地区='香港') )
select * from 歌曲