数据库组合查询练习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);