spv.sql

sql的数据库系统的实例,是关于体育协会的详细分类,是用德语写的.

set echo on
set termout on
set tab off
set pause '=============> Weiter mit <ET>'

DROP   TABLE mitglied;
DROP   TABLE wettkampf;
DROP   TABLE teilnahme;
DROP   TABLE schieds;
DROP   VIEW  judo;

create table mitglied(
	mnr NUMBER(4) NOT NULL,
	name VARCHAR(15),
	vorname VARCHAR(15),
	gebdat DATE,
	addresse VARCHAR(18),
	telnr NUMBER(11));

create table wettkampf(
	wbez VARCHAR(10) NOT NULL,
	datum DATE,
	ort VARCHAR(16),
	mnr NUMBER(4));

create table teilnahme(
	mnr NUMBER(4) NOT NULL,
	wbez VARCHAR(10) NOT NULL,
	datum DATE NOT NULL,
	ort VARCHAR(16) NOT NULL,
	platz NUMBER(2));

create table schieds(
	mnr NUMBER(4) NOT NULL,
	wbez VARCHAR(10) NOT NULL);

insert into mitglied values
(1123,'Koenig','Peter','21.6.57','Petersstr.12',01795492874);
insert into mitglied values
(1786,'Jonas','Alex','64.6.54','Petersstr.12',01795492874);
insert into mitglied values
(5642,'Blasche','Heine','5.5.87','Petersstr.12',01795492874);
insert into mitglied values
(8753,'Clauss','Sichone','15.5.83','Petersstr.12',01795492874);
insert into mitglied values
(4365,'Schott','Johanes','24.8.77','Petersstr.12',01795492874);
insert into mitglied values
(1674,'Adam','Rene','29.12.67','Petersstr.12',01795492874);
insert into mitglied values
(1675,'Forner','Thomas','12.3.59','Petersstr.12',01795492874);
insert into mitglied values
(9684,'Schneider','Bernd','29.12.67','Petersstr.12',01795492874);
insert into mitglied values
(2034,'Deisler','Sebastian','12.3.59','Petersstr.12',01795492874);
insert into mitglied values
(1979,'Froitzheim','Manfred','9.5.67','Petersstr.12',01795492874);

insert into wettkampf values
('Judo','23.5.04','Niederbobritzsch',1123);
insert into wettkampf values
('Judo','31.1.05','Berlin',1123);
insert into wettkampf values
('Judo','2.5.05','Niederbobritzsch',1786);
insert into wettkampf values
('Judo','11.2.03','Berlin',1786);
insert into wettkampf values
('Boxen','3.2.05','Bonn',1123);
insert into wettkampf values
('Judo','17.1.04','Hamburg',1786);
insert into wettkampf values
('Laufen','15.5.03','Freiberg',1675);
insert into wettkampf values
('Boxen','14.12.04','Stuttgart',1674);
insert into wettkampf values
('Judo','7.12.04','Bonn',1786);
insert into wettkampf values
('Judo','6.2.04','Siebenlehn',1123);
insert into wettkampf values
('Flechten','24.12.04','Mannheim',1979);

insert into teilnahme values
(5642,'Judo','23.5.04','Niederbobritzsch',1);
insert into teilnahme values
(8753,'Judo','31.1.05','Berlin',2);
insert into teilnahme values
(4365,'Judo','2.5.05','Niederbobritzsch',2);
insert into teilnahme values
(9684,'Boxen','3.2.05','Bonn',2);
insert into teilnahme values
(5642,'Laufen','15.5.03','Freiberg',5);
insert into teilnahme values
(2034,'Judo','11.2.03','Berlin',3);
insert into teilnahme values
(4365,'Judo','6.2.04','Siebenlehn',2);
insert into teilnahme values
(5642,'Judo','7.12.04','Bonn',1);
insert into teilnahme values
(8753,'Judo','6.2.04','Siebenlehn',1);
insert into teilnahme values
(5642,'Judo','17.1.04','Hamburg',1);

insert into schieds values
(1123,'Judo');
insert into schieds values
(1123,'Boxen');
insert into schieds values
(1786,'Judo');
insert into schieds values
(1674,'Boxen');
insert into schieds values
(1123,'Laufen');
insert into schieds values
(1786,'Boxen');
insert into schieds values
(1675,'Laufen');
insert into schieds values
(1979,'Fechten');

COMMIT;

SELECT * FROM mitglied;
SELECT * FROM wettkampf;
SELECT * FROM teilnahme;
SELECT * FROM schieds;

--2.1
select name,vorname,mnr
from mitglied
order by gebdat desc;

--2.1_2
select name,vorname,mnr
from mitglied
order by name,vorname;

--2.2
create view judo as
select mnr,ort,platz from teilnahme
where wbez='Judo'
and ort in
('Berlin','Niederbobritzsch',
'Bonn','Siebenlehn')
and platz in(1,2,3);
select * from judo;

--2.3(!!!mnr nur einmal anzeigen!!!)
select distinct s1.mnr 
from schieds s1,schieds s2
where s1.mnr=s2.mnr
and s1.wbez!=s2.wbez;

--2.4
select name,vorname
from mitglied m,teilnahme t
where m.mnr=t.mnr
and platz=1;

--2.5
select name,vorname from mitglied
where mnr in
(select mnr from teilnahme
where platz=1);

--2.6
alter table schieds
add(spr_ken varchar(26));

--2.6_2
update schieds
set spr_ken='Englisch,Chinesisch'
where mnr=1123;
select * from schieds;

--2.7
update wettkampf
set wbez='Fechten'
where wbez='Flechten';
select * from wettkampf;