baza dabych SQL- trudniejsze zapytania SELECT Opcje

[kamillo1290]

Stworzyłem bazę danych, miała się składać tylko z kilku tabel i mieć nieskomplikowaną formę, wygląda tak:

-- Definicje tabel

CREATE TABLE kraj(
id_kraj INT PRIMARY KEY,
nazwa_kraju VARCHAR(30) NOT NULL,
ranking INT NOT NULL
);

CREATE TABLE skoczek(
id_skoczek INT PRIMARY KEY,
imie VARCHAR(20) NOT NULL,
nazwisko VARCHAR(20) NOT NULL,
data_ur DATETIME NOT NULL,
waga INT NOT NULL,
wzrost INT NOT NULL,
id_kraj INT NOT NULL,
liczba_punktow INT NOT NULL
);

CREATE TABLE skocznia(
id_skocznia INT PRIMARY KEY,
id_klasyfikacja_skoczni INT NOT NULL,
nazwa VARCHAR(50) NOT NULL,
rekord_skoczni DEC(5,2) NOT NULL,
id_skoczek INT,
id_kraj INT
);

CREATE TABLE klasyfikacja_skoczni(
id_klasyfikacja_skoczni INT PRIMARY KEY,
nazwa VARCHAR(40) NOT NULL
);

-- Wprowadzenie przykładowych rekordów
INSERT INTO kraj
VALUES ('1','Polska','6');
INSERT INTO kraj
VALUES ('2','Finlandia','4');
INSERT INTO kraj
VALUES ('3','Austria','1');
INSERT INTO kraj
VALUES ('4','Niemcy','3');
INSERT INTO kraj
VALUES ('5','Norwegia','2');
INSERT INTO kraj
VALUES ('6','Szwajcaria','5');
INSERT INTO kraj
VALUES ('7','Slowenia','7');

INSERT INTO skoczek
VALUES ('1','Simon','Ammann','1981-06-25','58','174','6','1649');
INSERT INTO skoczek
VALUES ('2','Gregor','Schlierenzauer','1990-01-07','66','180','3','1368');
INSERT INTO skoczek
VALUES ('3','Thomas','Morgenstern','1986-10-30','65','180','3','944');
INSERT INTO skoczek
VALUES ('4','Andreas','Kofler','1984-05-17','71','176','3','893');
INSERT INTO skoczek
VALUES ('5','Adam','Malysz','1977-12-03','54','170','1','842');
INSERT INTO skoczek
VALUES ('6','Wolfgang','Loitzl','1980-01-13','63','180','3','760');
INSERT INTO skoczek
VALUES ('7','Anders','Jacobsen','1985-02-17','74','178','5','557');
INSERT INTO skoczek
VALUES ('8','Martin','Koch','1982-01-22','61','169','3','545');
INSERT INTO skoczek
VALUES ('9','Bjoern Einar','Romoeren','1981-04-01','63','182','5','517');
INSERT INTO skoczek
VALUES ('10','Michael','Uhrmann','1978-09-16','71','174','4','424');
INSERT INTO skoczek
VALUES ('11','Janne','Ahonen','1977-05-11','67','184','2','494');
INSERT INTO skoczek
VALUES ('12','David','Zauner','1985-04-09','58','170','3','403');
INSERT INTO skoczek
VALUES ('13','Harri ','Olli','1985-01-15','60','178','2','367');
INSERT INTO skoczek
VALUES ('14','Michael','Neumayer','1970-01-15','67','181','4','285');
INSERT INTO skoczek
VALUES ('15','Kamil','Stoch','1987-05-25','52','172','1','203');


INSERT INTO klasyfikacja_skoczni
VALUES ('1','mala');
INSERT INTO klasyfikacja_skoczni
VALUES ('2','srednia');
INSERT INTO klasyfikacja_skoczni
VALUES ('3','normalna');
INSERT INTO klasyfikacja_skoczni
VALUES ('4','duza');
INSERT INTO klasyfikacja_skoczni
VALUES ('5','mamucia');


INSERT INTO skocznia
VALUES ('1','5','Planica',239,'9','7');
INSERT INTO skocznia
VALUES ('2','4','Oberstdorf',143.5,NULL,'4');
INSERT INTO skocznia
VALUES ('3','5','Bad Mitterndorf',215.5,'2','4');
INSERT INTO skocznia
VALUES ('4','5','Harrachov',214.5,'3',NULL);
INSERT INTO skocznia
VALUES ('5','4','Kuusamo',147,'2','2');
INSERT INTO skocznia
VALUES ('6','4','Lillehammer',146,'1','5');
INSERT INTO skocznia
VALUES ('7','4','Innsbruck',134.5,NULL,'4');
INSERT INTO skocznia
VALUES ('8','4','Garmisch-Partenkirchen',143.5,'1','4');
INSERT INTO skocznia
VALUES ('9','4','Sapporo',140,NULL,'5');
INSERT INTO skocznia
VALUES ('10','4','Willingen',152,'11','4');
INSERT INTO skocznia
VALUES ('11','3','Lahti',98.5,'5','2');
INSERT INTO skocznia
VALUES ('12','4','Oslo',136,NULL,'5');
INSERT INTO skocznia
VALUES ('13','4','Zakopane',140.5,'1','1');


Mile widziane wszelkie uwagi. Problem mam taki, że nie muszę ułożyć złożone zapytania SELECT do niej (3-5), same treści zadań nie są większym problemem, lecz mam problemy z odpowiedziami.

Przykładowe zadania wymyślone:

1. Wyświetl skocznie mamucie, na których rekordzistami są Finowie i rekord skoczni jest większy niż 140m.
2. Wyświetlić imię, nazwisko, wagę najcięższego skoczka oraz nazwę kraju, którym trenuje.
3. Wypisać trzech zawodników o najmniejszych wagach. Jeżeli kilku ma taką samą wagę, wypisać tych o nazwiskach wcześniejszych alfabetycznie.
4. Dla każdego kraju wypisać jego nazwę oraz nazwisko i wagę najlżejszego i najcięższego zawodnika.
5. Posegregować skoczków według tego jakie miejsce w rankingu ma ich kraj. Jeżeli kilku skoczków ma taki sam kraj, wypisać według kolejności alfabetycznej.
6. Wypisać nazwy tych skoczni, dla których nie ma nazwiska skoczka, który ustanowił rekord skoczni.

Mile widziane propozycje podobnych lub trudniejszych zadań odnośnie tej bazy.

Ten post edytował kamillo1290 10.04.2010, 20:34:46

[kamillo1290]

Właśnie zaczynam, cały dzień dziś siedzę przed kompem już, więc odpadam powoli jeśli chodzi o myślenie, ale postaram się jeszcze dziś przedstawić choć zarysy rozwiązań do tych wymyślonych zadań.

Odnośnie pierwszego:

Ta część wybiera mamucie skocznie:

SELECT nazwa FROM skocznia s
JOIN klasyfikacja_skoczni c ON s.id_klasyfikacja_skoczni=c.id_klasyfikacja_skoczni
WHERE c.nazwa = 'mamucia';

UNION - klauzula UNION łączy polecenia SELECT

Tutaj mam problem z tym co wymyśliłem, ponieważ w tabeli skocznia id_kraj odnosi się do kraju w jakim jest skocznia a nie do kraju pochodzenia rekordzisty, więc trzeba id_kraj ze skoczka połączyć z id_kraj z tabeli kraj i wtedy odpowiadające temu id_skoczek ze skoczka do id_skoczek ze skocznie.

rekord skoczni większy niż 140m

WHERE rekord_skoczni > 140

Mam też spore problemy jak pojawia mi się właśnie taka komplikacja jak tu gdy mam kilka tabel połączyć by to polecenie działało poprawnie.
Zajmę się resztą jutro raczej i wkleję wyniki



2.
SELECT s.imie, s.nazwisko, s.waga, k.nazwa_kraju
FROM skoczek s JOIN kraj k ON s.id_kraj=k.id_kraj
WHERE s.waga = (SELECT MAX(waga) FROM skoczek);

3.
SELECT DISTINCT TOP 3 s.imie, s.nazwisko, s.waga
FROM skoczek s
ORDER BY s.waga ASC, s.nazwisko ASC;

Dałby ktoś radę do tej bazy stworzyć trudniejsze zapytanie( przy użyciu LIKE, ORDER BY, JOIN, GROUP BY, HAVING BY, UNION )(IMG:style_emoticons/default/questionmark.gif) ?


4.

SELECT nazwa_kraju, MAX(waga) AS "MAX", MIN(waga) AS "MIN"
,(SELECT TOP 1 nazwisko FROM skoczek WHERE id_kraj=k.id_kraj ORDER BY waga DESC) AS "najciezszy"
,(SELECT TOP 1 nazwisko FROM skoczek WHERE id_kraj=k.id_kraj ORDER BY waga ASC) AS "najlzejszy"
FROM kraj k JOIN skoczek s ON k.id_kraj=s.id_kraj
GROUP BY nazwa_kraju,k.id_kraj
ORDER BY nazwa_kraju;

Ten post edytował kamillo1290 11.04.2010, 12:19:05