数据库组合查询练习1

drop table stu;
drop sequence seq_stu;
create sequence seq_stu;
create table stu(
 sid number(5) primary key,
 sname varchar2(10),
 ssex varchar2(2) default '男',
 sage number(3),
 sdept number(2) 
);
insert into stu values(seq_stu.nextval,'tom','男',21,1);
insert into stu values(seq_stu.nextval,'lily','女',22,1);
insert into stu values(seq_stu.nextval,'tony','男',19,1);
insert into stu values(seq_stu.nextval,'sandy','女',20,2);
insert into stu values(seq_stu.nextval,'tom','男',21,2);
insert into stu values(seq_stu.nextval,'lily','女',23,2);
insert into stu values(seq_stu.nextval,'小明','男',22,3);
insert into stu values(seq_stu.nextval,'小丽','女',24,3);
commit;
select * from stu;

--select distinct sname from stu;
--重复数据只显示一条
select * from stu where sid in (select min(sid) from stu group by sname);
--删除重复的数据
--delete from stu where sid not in (select min(sid) from stu group by sname);

drop table temptbs;
drop sequence seq_tbs;
create sequence seq_tbs;
create table temptbs(
 id number(5) primary key,
 name varchar2(10),
 value varchar2(10)
);
insert into temptbs values(seq_tbs.nextval,'a','pp');
insert into temptbs values(seq_tbs.nextval,'a','pp');
insert into temptbs values(seq_tbs.nextval,'b','ii');
insert into temptbs values(seq_tbs.nextval,'b','pp');
insert into temptbs values(seq_tbs.nextval,'b','pp');
insert into temptbs values(seq_tbs.nextval,'c','pp');
insert into temptbs values(seq_tbs.nextval,'c','pp');
insert into temptbs values(seq_tbs.nextval,'c','ii');
insert into temptbs values(seq_tbs.nextval,'d','ii');
commit;
select * from temptbs;
--删除重复数据
delete from temptbs where id in (select min(id) from temptbs group by name);
--只显示重复数据
select * from temptbs where name in (select name from temptbs group by name,value having count(*)>1);
--只显示不重复数据
select * from temptbs where name in (select name from temptbs group by name having count(*)=1);