提供了编程的基础技术教程

zl程序教程

您现在的位置是：首页 >  数据库

当前栏目

SQL语句练习04

2023-02-18 16:30:42 时间

目录

一、见表并插入数据

二、查询语句的练习

一、见表并插入数据

  • 一、建立如下故事表(命名格式“姓名拼音_三位学号 _story”，如LBJ_023_story），并插入数据 、
create table LYL_116_story(sid varchar(7),sname varchar(12),
snation varchar(5),primary key(sid))

insert into LYL_116_story values('S001','倚天屠龙记','中国')
insert into LYL_116_story values('S002','浪漫满屋','韩国')
insert into LYL_116_story values('S003','美妙人生','韩国')
  • 二、建立如下人物表(命名格式“姓名拼音_三位学号 _person”，如LBJ_023_person），并插入数据 
create table LYL_116_person(pid varchar(7),pname varchar(7),
psex varchar(2),pbeauty int primary key(pid))

insert into LYL_116_person values('P001','张无忌','男',90)
insert into LYL_116_person values('P002','赵敏','女',98)
insert into LYL_116_person values('P003','周芷若','女',98)
insert into LYL_116_person values('P004','小昭','女',93)
insert into LYL_116_person values('P005','殷离','女',85)
insert into LYL_116_person values('P006','宋青书','男',96)
insert into LYL_116_person values('P007','Rain','男',98)
insert into LYL_116_person values('P008','宋慧乔','女',97)
insert into LYL_116_person values('P009','韩恩珍','女',93)
insert into LYL_116_person values('P010','金承洙','男',94)
insert into LYL_116_person values('P011','金载沅','男',93)
insert into LYL_116_person values('P012','柳真','女',95)
insert into LYL_116_person values('P013','李志勋','男',96)
insert into LYL_116_person values('P014','李彩英','女',91)
  • 三、建立如下主演表(命名格式“姓名拼音_三位学号 _personinstory”，如LBJ_023_personinstory），并插 入数据 
create table LYL_116_personstory(pid varchar(7),sid varchar(7),
foreign key(pid) references LYL_116_person(pid),
foreign key(sid) references LYL_116_story(sid))

insert into LYL_116_personstory values('P001','S001')
insert into LYL_116_personstory values('P002','S001')
insert into LYL_116_personstory values('P003','S001')
insert into LYL_116_personstory values('P004','S001')
insert into LYL_116_personstory values('P005','S001')
insert into LYL_116_personstory values('P006','S001')
insert into LYL_116_personstory values('P007','S002')
insert into LYL_116_personstory values('P008','S002')
insert into LYL_116_personstory values('P009','S002')
insert into LYL_116_personstory values('P010','S002')
insert into LYL_116_personstory values('P011','S003')
insert into LYL_116_personstory values('P012','S003')
insert into LYL_116_personstory values('P013','S003')
insert into LYL_116_personstory values('P014','S003')
  • 四、建立如下爱情表(命名格式“姓名拼音_三位学号 _love”，如LBJ_023_love），并插入数据 
create table LYL_116_love(pid varchar(7),lovepid varchar(7),love_index int,
injury_index int,foreign key(pid) references LYL_116_person(pid),
foreign key(lovepid) references LYL_116_person(pid))

insert into LYL_116_love values('P001','P002',70,30);
insert into LYL_116_love values('P002','P001',100,80);
insert into LYL_116_love values('P003','P001',90,80);
insert into LYL_116_love values('P004','P001',80,40);
insert into LYL_116_love values('P005','P001',50,20);
insert into LYL_116_love values('P006','P003',85,35);
insert into LYL_116_love values('P007','P009',80,40);
insert into LYL_116_love values('P007','P008',90,60);
insert into LYL_116_love values('P008','P007',98,80);
insert into LYL_116_love values('P009','P010',80,40);
insert into LYL_116_love values('P009','P007',70,30);
insert into LYL_116_love values('P010','P008',90,80);
insert into LYL_116_love values('P011','P012',95,90);
insert into LYL_116_love values('P011','P014',90,70);
insert into LYL_116_love values('P012','P011',98,90);
insert into LYL_116_love values('P013','P012',90,80);
insert into LYL_116_love values('P014','P011',60,60);

二、查询语句的练习

  • 1）查询所有的故事书的书名和国家 
select sname,snation from LYL_116_story
  • 2）先按照性别，然后按照美丽程度由高往低的顺序，列出所有人物的名字，性别和美丽程度 
select pname,psex,pbeauty from 
LYL_116_person order by psex,pbeauty desc
  • 3）先按照人名，然后按照被爱的人名的顺序，列出所有爱情关系的施爱人，被爱人，爱情指数，受伤指数 
select p.pname'施爱人',p1.pname '被爱人',l.love_index'爱情指数',
l.injury_index'受伤指数' from LYL_116_love l,LYL_116_person p1,
LYL_116_person p where l.pid=p.pid and l.lovepid=p1.pid order by p.pname,p1.pname
  • 4）哪些男人没有得到任何一个女人的爱？ 
select p.pname'没女人爱的男人' from LYL_116_person p where p.pid not in 
(select l.lovepid from LYL_116_love l,LYL_116_person p 
where l.pid=p.pid and p.psex='女') and p.psex='男'
  • 5）哪些男人至少爱着2个女人？ 
select p1.pname from LYL_116_person p1 where p1.psex = '男' and p1.pid in(
  select l.pid from LYL_116_love l,LYL_116_person p2
  where l.lovepid = p2.pid and p2.psex = '女'
  group by l.pid having count(*) >=2)
  • 6）哪个人得到了最多人的爱情？ 
select p.pname from LYL_116_person p,
(select l.lovepid,count(l.lovepid)'loved_num'from LYL_116_love l group by l.lovepid) s 
where p.pid=s.lovepid and s.loved_num = (select max(s.loved_num) from 
(select count(l.lovepid)'loved_num'from LYL_116_love l group by l.lovepid) s)
  • 7）列出所有人的姓名，以及爱他的人的数量 
select p1.pname,(select count(*) from LYL_116_love 
where lovepid = p1.pid)'爱他的人的数量'from LYL_116_person p1
  • 8）列出男人的追求者的平均美貌程度 
select p1.pname 被追求者姓名,avg(p2.pbeauty)'平均美貌程度'
from LYL_116_love love,LYL_116_person p1,LYL_116_person p2
where p1.pid=love.lovepid and p1.psex='男' and p2.pid=love.pid
group by p1.pname
  • 9）列出追求者的平均爱情指数最低的男人的姓名，及其追求者的姓名和爱情指数
select p2.pname'被追求者',p1.pname'追求者',l.love_index 爱情指数 from 
 LYL_116_person p1,LYL_116_person p2,LYL_116_love l
 where l.pid=p1.pid and l.lovepid=p2.pid
 and p2.pid in(
 select top(1) lovepid from LYL_116_love l1,LYL_116_person p0 
 where psex='男' and l1.lovepid = p0.pid 
 group by l1.lovepid order by avg(l1.love_index)asc)

相关文章