[es] - Mozgalica za kraj leta - studenti i kursevi

Zidar
Canada

Član broj: 15387
Poruke: 3085
*.100.46-69.q9.net.

+79 Profil

Mozgalica za kraj leta - "studenti i kursevi"

^{30.08.2007. u 15:38 - pre 202 meseci}

Eto, zavrsilo se leto, prosao Beer Fest, Exit i Gucha i sad nazad u skolu/posao, sta ko vec ima :-(

Elem, otvorena nova skola. Skola nudi odredjen broj kurseva za studente. Naravno, napravljena je baza podataka i u bazi tri tabele - z_Studen, z_Course StudentCourse (ko je uzeo koji kurs). Kursevi nisu obavezni, pa svaki student uzima sta hoce. Skola je naravno privatna i kursevi se placaju. Direktor skole zeli da svima koji su upisali SVE raspolozive kurseve ucini popust.

Pitanje glasi: izlistati studente koji su uzeli sve raspolozive kurseve. Evo pocetnih podatka (sa MS SQL Servera)

Code:

CREATE TABLE z_Student (Student varchar(10) NOT NULL PRIMARY KEY)

CREATE TABLE z_Course (Course varchar(10) NOT NULL PRIMARY KEY)

CREATE TABLE z_StudentCourse
(Student varchar(10) NOT NULL
, Course varchar(10) NOT NULL)

ALTER TABLE z_StudentCourse
ADD CONSTRAINT PK_z_StudentCourse PRIMARY KEY (Student, Course)

Popunjavanje tabela:

Code:

INSERT INTO z_Student (Student) VALUES ('Pera')
INSERT INTO z_Student (Student) VALUES ('Laza')
INSERT INTO z_Student (Student) VALUES ('Mirko')
INSERT INTO z_Student (Student) VALUES ('Slavko')
INSERT INTO z_Student (Student) VALUES ('Ana')
INSERT INTO z_Student (Student) VALUES ('Mira')
INSERT INTO z_Student (Student) VALUES ('Zora')
INSERT INTO z_Student (Student) VALUES ('Goca')
INSERT INTO z_Student (Student) VALUES ('Joca')

INSERT INTO z_Course (Course) VALUES ('Matematika')
INSERT INTO z_Course (Course) VALUES ('Fizika')
INSERT INTO z_Course (Course) VALUES ('Hemija')

INSERT INTO z_StudentCourse VALUES ('Pera','Matematika')
INSERT INTO z_StudentCourse VALUES ('Pera','Fizika')
INSERT INTO z_StudentCourse VALUES ('Pera','Hemija')
INSERT INTO z_StudentCourse VALUES ('Laza','Fizika')
INSERT INTO z_StudentCourse VALUES ('Laza','Hemija')
INSERT INTO z_StudentCourse VALUES ('Mirko','Matematika')
INSERT INTO z_StudentCourse VALUES ('Mirko','Fizika')
INSERT INTO z_StudentCourse VALUES ('Slavko','Hemija')
INSERT INTO z_StudentCourse VALUES ('Ana','Fizika')
INSERT INTO z_StudentCourse VALUES ('Ana','Hemija')
INSERT INTO z_StudentCourse VALUES ('Mira','Matematika')
INSERT INTO z_StudentCourse VALUES ('Mira','Fizika')
INSERT INTO z_StudentCourse VALUES ('Mira','Hemija')
INSERT INTO z_StudentCourse VALUES ('Zora','Matematika')
INSERT INTO z_StudentCourse VALUES ('Zora','Fizika')
INSERT INTO z_StudentCourse VALUES ('Zora','Hemija')
INSERT INTO z_StudentCourse VALUES ('Goca','Hemija')
INSERT INTO z_StudentCourse VALUES ('Goca','Matematika')

Vidimo da postoje tri kursa i da su Pera, Mira i Zora upisali sva tri kursa. Ostali manje, a Joca ni jedan, on se tek upisao i jos razmislja.

Posto postavljac mozgalice treba da ponudi resenje, evo ovako. Zamislicu da je svaki student zaista upisao sve kurseve. Taj zamisljeni skup uporedicu sa tabelom StudentCourse i videcu kome koji kurs nedostaje. Kome ne nedostaje ni jedan kurs - to je resenje zadatka.

Situacija "svaki student upisao svaki kurs" moze se simulirati dekartovim proizvodom tabela z_Student i z_Course, ovako:

Code:

SELECT Student, Course
FROM z_Student , z_Course
ORDER BY Student, Course -- ORDER je samo zbog jasnoce prikaza u ovom koraku, inace se nece koristiti u medjukoracima

Dobijeni dekartov proizvod dve tabele treba uporediti sa tabelom z_StudentCourse:

Code:

SELECT A.Student, A.Course AS MoguciKurs, B.Course AS UpisaniKurs
FROM
(
SELECT Student, Course
FROM z_Student , z_Course
) AS A
LEFT JOIN z_StudentCourse AS B ON A.Student = B.student AND A.Course = B.Course
ORDER BY 1,2 -- ORDER je samo zbog jasnoce prikaza u ovom koraku, inace se nece koristiti u medjukoracima

Ako izvrsite ovaj upit, dobicete 27 redova koji u trecoj koloni imaju ponegde NULL. To su kursevi koje doticni student niej upisao.

Student koji je upisao sve kurseve, nema ni jednu NULL vrednost u trecoj koloni polednjeg upita. Kako izdvojiti studente koji nemaju ni jednu NULL vrednost u poseldnjem upitu? Ima vise nacina, trenutno mi pada na pamaet nesto ovako:

Code:

SELECT Student, COUNT(*) AS NumCourses
FROM
(
SELECT A.Student, A.Course AS CourseDesired, B.Course AS CourseActual
FROM
(
SELECT Student, Course
FROM z_Student , z_Course
) AS A
LEFT JOIN z_StudentCourse AS B ON A.Student = B.student AND A.Course = B.Course
) AS X
WHERE X.CourseActual IS NOT NULL
GROUP BY Student

Prebrojali smo koliko je ko uzeo realnih kurseva. Vidmo da su neki studeni uzeli 3 a neki manje.

Student NumCourses
---------- -----------
Ana 2
Goca 2
Laza 2
Mira 3
Mirko 2
Pera 3
Slavko 1
Zora 3

Trebju nam oni sa 3, pa moze ovako:

Ocigledno je da dobijamo sta smo trazili:
Student BrojKurseva
---------- -----------
Mira 3
Pera 3
Zora 3

Lepo, ali nama treba neko genericko resenje. Sta ako skola ponudi 5 kurseva?

Mozemo da umesto konstante (trojka) jednostavno prebrojimo koliko ima ponudjenih kurseva, ovako:

Code:

SELECT Student, COUNT(*) AS BrojKurseva
FROM
(
SELECT A.Student, A.Course AS CourseDesired, B.Course AS CourseActual
FROM
(
SELECT Student, Course
FROM z_Student , z_Course
) AS A
LEFT JOIN z_StudentCourse AS B ON A.Student = B.student AND A.Course = B.Course
) AS X
WHERE X.CourseActual IS NOT NULL
GROUP BY Student
HAVING COUNT(*) = (SELECT COUNT(*) FROM z_Course)

Time dobijamo konacan rezultat:

Student BrojKurseva
---------- -----------
Mira 3
Pera 3
Zora 3

Ponudjeno resenje se svodi na prebrojavanje kurseva koje je uzeo student i uporedjivanje tog broja sa brojem ponudjenih kurseva. Ovo sigurno moze da se napise krace i/ili efikasnije nego sto sam ja uradio. Kako?

A mozda ima i neki potpuno drugaciji pristup?

Izvolite

Odgovor na temu

sasas
Saša Slavnić
radim za neke švabe

Član broj: 35478
Poruke: 617
*.dynamic.sbb.co.yu.

Profil

Re: Mozgalica za kraj leta - "studenti i kursevi"

^{30.08.2007. u 19:59 - pre 202 meseci}

Je li može ovako, malo kraće:

Code:

SELECT student, count(*) AS BrojKurseva
FROM z_StudentCourse
GROUP BY student
HAVING count(*) = (SELECT count(*) FROM z_Course)

poz.

When something is hard to do, then it's not worth doing.

Odgovor na temu

Zidar
Canada

Član broj: 15387
Poruke: 3085
*.100.46-69.q9.net.

+79 Profil

Re: Mozgalica za kraj leta - "studenti i kursevi"

^{30.08.2007. u 20:11 - pre 202 meseci}

naravno da moze :-)

Odgovor na temu

Zidar
Canada

Član broj: 15387
Poruke: 3085
*.100.46-69.q9.net.

+79 Profil

Re: Mozgalica za kraj leta - "studenti i kursevi"

^{07.09.2007. u 16:24 - pre 202 meseci}

Izgleda da je zadatak bio mnogo lak. Da ga malo otezamo. Pretpostavimo da studenti mogu da uzimaju i druge kurseve, ne samo ona tri koje smo izlistali u tabeli.
Evo ponovo struktura tabela i novih podataka. stuktura tabela je identicna kao i u prvobitnoj mozgalici. Ko je sacuvao skripte i tabele, ne mora da menja. Dodao sam tri INSERT INTO linije sa novim predmetima (Istorija, geografija) sto cete videti na kraju koda.

Code:

-- posmatrani studenti
CREATE TABLE z_Student (Student varchar(10) NOT NULL PRIMARY KEY)

-- kursevi koji nas interesuju
CREATE TABLE z_Course (Course varchar(10) NOT NULL PRIMARY KEY)

-- kursevi koje su uzeli studenti (Ima vise kurseva nego sto je u tabeli z_Course )
CREATE TABLE z_StudentCourse
(Student varchar(10) NOT NULL
, Course varchar(10) NOT NULL)

ALTER TABLE z_StudentCourse
ADD CONSTRAINT PK_z_StudentCourse PRIMARY KEY (Student, Course)

Ulazni podaci:

Pitanje glasi:
1) izlistati imena studenata koji su uzeli sve kurseve iz tabele z_Course (mogu da imaju i neke dodatne kurseve)
2) izlistati imena studenata koji su uzeli kurseve iz table z_Cours, njih i samo njih, ni jedn drugi kurs vise ili manje

Kod koji je dao Sasa za prvobitnu mozgalicu ne radi korektno za nove uslove. Moj radi, ali samo za 1) ne i za 2)

Trazi se jos neka varijanta koda za 1) i varijanta za 2)

Znam da ce Sasa da resi oba problema

Ima li jos neko neku drugu ideju? Moze li ovo u Accesu?

Odgovor na temu

chachka
Srđan Mijatov
Programer
BUS Computers
Kikinda

Član broj: 53780
Poruke: 576
*.ADSL.neobee.net.

Sajt: www.baze-podataka.net

+4 Profil

Re: Mozgalica za kraj leta - "studenti i kursevi"

^{09.09.2007. u 07:41 - pre 202 meseci}

Rešenje pod 1)

Kada bismo uspeli da izbacimo novounete redove iz razmatranja, rešenje bi nam se svelo na primenu sasas-ove relacione divizije.

Kako izbaciti 3 dodatna nova reda? Lako uz upotrebu LEFT OUTER JOIN-a. Ako izvršimo sledeći upit:

Code:

SELECT sc.student, sc.course, c.course
  FROM z_studentcourse AS sc
       LEFT OUTER JOIN
       z_course AS c
         ON c.course = sc.course;

jasno nam se ističu tri nova reda.

To su redovi za koje je c.course IS NULL. Dakle njih ćemo izbaciti, a na ostatak primeniti relacionu diviziju:

Code:

SELECT sc.student
  FROM z_studentcourse AS sc
       LEFT OUTER JOIN
       z_course AS c
         ON c.course = sc.course
WHERE c.course IS NOT NULL
GROUP BY sc.student
HAVING COUNT(sc.*) = (SELECT COUNT(*) FROM z_course);

Rešenje pod 2)

Pri rešavanju ovog problema se koristi ista ideja kao i za rešavanje predhodnog problema.

Trebaju nam studenti čiji je broj uzetih kurseva jednak broju uzetih kurseva iz tabele z_Course.

Code:

SELECT sc.student
  FROM z_studentcourse AS sc
       LEFT OUTER JOIN
       z_course AS c
         ON c.course = sc.course
GROUP BY sc.student
HAVING COUNT(sc.*) = COUNT(c.*)

-----
Napomena: Rešenja su testirana na PostgreSQL 8.2.4 SUBP.

"The best code is no code at all."
- Zidar (ES član)
"Biggest obstacle to learning
SQL is unlearning procedural
programming." - Joe
Celko
"Minimize code, maximize data."
- A. Neil Pappalardo

Odgovor na temu

Zidar
Canada

Član broj: 15387
Poruke: 3085
*.100.46-69.q9.net.

+79 Profil

Re: Mozgalica za kraj leta - "studenti i kursevi"

^{10.09.2007. u 17:07 - pre 202 meseci}

Hvala!

Pre nego sto nastavimo, ubacio sam jos dva reda u tabelu z_StudentCourse

INSERT INTO z_StudentCourse VALUES ('Ana','Istorija')
INSERT INTO z_StudentCourse VALUES ('Mirko','Geografija')

Sada Ana i Mirko imaju po tri kursa, ali to nije kompletan skup trazenih kurseva. Cisto da smo sigurni da nasa resenja nisu osetljiva na ovakve stvari.

Za slucaj 1), studenti imaju trazena tri kursa a moze i jos po neki, Kod koji je dao Chachka, u MS SQL dao je tacne rezultate sa minimalnom izmenom

Code:

SELECT sc.student
FROM z_studentcourse AS sc
LEFT OUTER JOIN z_course AS c
ON c.course = sc.course
WHERE c.course IS NOT NULL
GROUP BY sc.student
--HAVING COUNT(sc.*) = (SELECT COUNT(*) FROM z_course); -- original Chackin kod
HAVING COUNT(*) = (SELECT COUNT(*) FROM z_course);

Za slucaj 2), nisam mogao da prevedem direktno, pa sam morao da napisem MS SQL resenje zasnovano na istoj logici: studenti moraju da imaju tacno 3 kursa, i ta tri kursa moraju da budu bas ona tri iz tabele z_Course. Podestimo se, trazimo studente koji imaju upisen tacno one kurseve iz tabele z_Course, ni manje ni vise:

Code:

SELECT
Student, SC.Course, C.Course AS Trazeni_Kurs
FROM z_StudentCourse AS SC
LEFT JOIN z_Course AS C ON C.Course = SC.Course

Uocimo naravno NULL vrednosti u koloni TrazeniKursevi. Trebaju nam studenti koji imaju tacno tri kursa. Ako izracunamo COUNT(*) iz datog izraza, mnogi ce imati 3 i vise kurseva. Treba da eliminisemo one koji imaju vise od 3 kao i one imaju NULL u koloni Trazeni_Kurs. Verujem da se sleddeci izraz moze napisati i u kracem obliku, ali je meni lakse da pratim sta se desava ako malo vise pisem, pa evo:

Code:

SELECT
    Student
    , BrojKurseva = COUNT(*)
    , BrojNotNULLTrazenihKurseva = SUM(CASE WHEN Trazeni_Kurs IS NULL THEN 0 ELSE 1 END)
FROM
(
SELECT
    SC.Student
    , SC.Course
    , C.Course AS Trazeni_Kurs
FROM z_StudentCourse AS SC
LEFT JOIN z_Course AS C ON C.Course = SC.Course
) AS X
GROUP BY Student
HAVING COUNT(*) = (SELECT COUNT(*) FROM z_Course) --- trazimo da ima onoliko koliko je u tabeli z_Course
--- a ovo dalje nam obezbedjuje da su u pitanju ama bas ti kursevi koji se traze
    AND SUM(CASE WHEN Trazeni_Kurs IS NULL THEN 0 ELSE 1 END) = (SELECT COUNT(*) FROM z_Course)

Dobijem sledeci rezultat:
Student BrojKurseva BrojNotNULLTrazenihKurseva
---------- ----------- --------------------------
Pera 3 3
Zora 3 3

(2 row(s) affected)

Poslednji izraz se uz sitnu izmenu moze naterati da radi za slucaj 1), studenti koji su uzeli trazene kurseve, a mozda i neki drugi:

Code:

SELECT
    Student
    , BrojKurseva = COUNT(*)
    , BrojNotNULLTrazenihKurseva = SUM(CASE WHEN Trazeni_Kurs IS NULL THEN 0 ELSE 1 END)
FROM
(
SELECT
    SC.Student
    , SC.Course
    , C.Course AS Trazeni_Kurs
FROM z_StudentCourse AS SC
LEFT JOIN z_Course AS C ON C.Course = SC.Course
) AS X
GROUP BY Student
HAVING COUNT(*) >= (SELECT COUNT(*) FROM z_Course) ---- umesto '=' imamo '>='
    AND SUM(CASE WHEN Trazeni_Kurs IS NULL THEN 0 ELSE 1 END) = (SELECT COUNT(*) FROM z_Course)

Rezultat:
Student BrojKurseva BrojNotNULLTrazenihKurseva
---------- ----------- --------------------------
Mira 4 3 -- Mira ima trazena 3 kursa plus Georafiju

Pera 3 3
Zora 3 3
(3 row(s) affected)

Odgovor na temu

mm2000

Član broj: 149978
Poruke: 7
*.bredband.comhem.se.

Profil

Re: Mozgalica za kraj leta - "studenti i kursevi"

^{12.09.2007. u 00:25 - pre 202 meseci}

Code:

SELECT Student
FROM z_StudentCourse SC INNER JOIN z_Course C ON C.Course = SC.Course
GROUP BY Student
HAVING COUNT(*) = (SELECT COUNT(*) FROM z_Course)

EDIT: Dodati su code tagovi.

_{[Ovu poruku je menjao chachka dana 12.09.2007. u 11:58 GMT+1]}

Odgovor na temu

chachka
Srđan Mijatov
Programer
BUS Computers
Kikinda

Član broj: 53780
Poruke: 576
*.ADSL.neobee.net.

Sajt: www.baze-podataka.net

+4 Profil

Re: Mozgalica za kraj leta - "studenti i kursevi"

^{12.09.2007. u 10:57 - pre 202 meseci}

Tako je :)
mm2000 nam je dao rešenje za prvi dodatni problem.

"The best code is no code at all."
- Zidar (ES član)
"Biggest obstacle to learning
SQL is unlearning procedural
programming." - Joe
Celko
"Minimize code, maximize data."
- A. Neil Pappalardo

Odgovor na temu

Zidar
Canada

Član broj: 15387
Poruke: 3085
*.100.46-69.q9.net.

+79 Profil

Re: Mozgalica za kraj leta - "studenti i kursevi"

^{12.09.2007. u 15:06 - pre 202 meseci}

Pokusao sam da resenje mm2000 upotrebim tako da dobijem i resenej za slucaj 2 (tacno 3 trazena kursa i ni jedan vise)

dakle, po mm2000, imamo za slucaj 1) (svi trazeni kursevi, dozvoljeno i vise od toga)

Code:

SELECT sc.student
FROM z_studentcourse AS sc
INNER JOIN z_course AS c -- ne trebaju nam drugi kursevi
ON c.course = sc.course
GROUP BY sc.student
HAVING COUNT(*) = (SELECT COUNT(*) FROM z_Course)

Upotreba INNER JOIN umesto LEFT JOIN eliminisala je potrebu za WHERE .. NOT NULL u Chachkinom izrazu (da ni ne spominjem stepen pojednostavljenja u odnosu na monster-kveri koji sam ja sastavio na pocetku, ja sam imao citav cross-join nepotrebno :-P

Zamenio sam INNER JOIN i stavio LEFT JOIN i dobio izraz veoma slican Chackinom (nedostaje WHERE .. NOT NULL)

Code:

SELECT sc.student
FROM z_studentcourse AS sc
LEFT JOIN z_course AS c -- ne trebaju nam drugi kursevi
ON c.course = sc.course
GROUP BY sc.student
HAVING COUNT(*) = (SELECT COUNT(*) FROM z_Course)

student
----------
Ana
Mirko
Pera
Zora

(4 row(s) affected)

Rezultat je nadskup onoga sto trazimo.
Tu su nam Pera i Zora koji imaju tacno tri kursa i to su bas kursevi koje se traze
Ali imamo Anu i Mirka, koji imaju po tri kursa od kojih nisu svi oni trazeni.
Nemamo Miru koja ima tri trazena kursa plus jos jedan, dakle 4 kursa.
Znaci, mm2000 resenje sa LEFT JOIN, daje nam koga trazimo, eliminise koga treba (Miru) ali daje i koga ne treba (Anu i Mirka)

Tu sam opet upotrebio elegantni mm2000 izraz koji daje studente koji zasigurno imaju sva tri trazena kursa. To sam upotrebio kao filter u WHERE izrazu:

Code:

SELECT sc.student
FROM z_studentcourse AS sc
LEFT JOIN z_course AS c    -- ne trebaju nam drugi kursevi
ON c.course = sc.course
WHERE sc.Student IN -- slucaj 1, svi studenti koji zasigurno imaju sva tri kursa
    (
    SELECT sc.student
    FROM z_studentcourse AS sc
    INNER JOIN z_course AS c ON c.course = sc.course
    GROUP BY sc.student
    HAVING COUNT(*) = (SELECT COUNT(*) FROM z_Course)
    )
GROUP BY sc.student
HAVING COUNT(*) = (SELECT COUNT(*) FROM z_Course)

student
----------
Pera
Zora

(2 row(s) affected)

Kod nije elegantan kao resenej za slucaj 2) koje je dao Chachka, ali je najblize sto mogu da mu se priblizim u ovom momentu. Mora da to moze i elegantnije.

Sto se mene tice, za slucaj 1) najelegantnije je resenje koje je dao mm2000, a za slucaj 2) majstor Chacka nosi pehar, na sistemu koji to moze da odradi. Za veoma specijalan slucaj, sa kojim smo poceli mozgalicu, naravno majstor SSlavnic.

A ja sam naucio stosta iz ove diskusije.

Odgovor na temu