Kada sam rekao da nije poslat DDL, mislio sam na SQL skriptu pomocu koje svako lako sebi moze da kreira bazu o kojoj je rec. Dakle nesto poput
Code:
CREATE TABLE S (
s_id CHAR(2) NOT NULL,
sname VARCHAR(10) NOT NULL,
CONSTRAINT pk_s PRIMARY KEY (s_id)
);
CREATE TABLE P (
p_id CHAR(2) NOT NULL,
pname VARCHAR(10) NOT NULL,
CONSTRAINT pk_p PRIMARY KEY (p_id)
);
CREATE TABLE J (
j_id CHAR(2) NOT NULL,
jname VARCHAR(10) NOT NULL,
CONSTRAINT pk_j PRIMARY KEY (j_id)
);
CREATE TABLE SPJ (
s_id CHAR(2) NOT NULL,
p_id CHAR(2) NOT NULL,
j_id CHAR(2) NOT NULL,
qty INTEGER NOT NULL,
CONSTRAINT pk_spj PRIMARY KEY (s_id, p_id, j_id)
);
ALTER TABLE SPJ
ADD CONSTRAINT fk_spj_s FOREIGN KEY (s_id) REFERENCES S(s_id)
ON DELETE RESTRICT ON UPDATE RESTRICT;
ALTER TABLE SPJ
ADD CONSTRAINT fk_spj_p FOREIGN KEY (p_id) REFERENCES P(p_id)
ON DELETE RESTRICT ON UPDATE RESTRICT;
ALTER TABLE SPJ
ADD CONSTRAINT fk_spj_j FOREIGN KEY (j_id) REFERENCES J(j_id)
ON DELETE RESTRICT ON UPDATE RESTRICT;
Onda jos dodas test podatke DML skriptom, poput:
Code:
INSERT INTO S (s_id, sname) VALUES ('S1', 'Smith');
INSERT INTO S (s_id, sname) VALUES ('S2', 'Jones');
INSERT INTO S (s_id, sname) VALUES ('S3', 'Blake');
INSERT INTO S (s_id, sname) VALUES ('S4', 'Clark');
INSERT INTO S (s_id, sname) VALUES ('S5', 'Adams');
INSERT INTO P (p_id, pname) VALUES ('P1', 'Nut');
INSERT INTO P (p_id, pname) VALUES ('P2', 'Bolt');
INSERT INTO P (p_id, pname) VALUES ('P3', 'Screw');
INSERT INTO P (p_id, pname) VALUES ('P4', 'Screw');
INSERT INTO P (p_id, pname) VALUES ('P5', 'Cam');
INSERT INTO P (p_id, pname) VALUES ('P6', 'Cog');
INSERT INTO J (j_id, jname) VALUES ('J1', 'Sorter');
INSERT INTO J (j_id, jname) VALUES ('J2', 'Punch');
INSERT INTO J (j_id, jname) VALUES ('J3', 'Reader');
INSERT INTO J (j_id, jname) VALUES ('J4', 'Console');
INSERT INTO J (j_id, jname) VALUES ('J5', 'Collator');
INSERT INTO J (j_id, jname) VALUES ('J6', 'Terminal');
INSERT INTO J (j_id, jname) VALUES ('J7', 'Tape');
INSERT INTO SPJ (s_id, p_id, j_id, qty) VALUES ('S1', 'P1', 'J1', 200);
INSERT INTO SPJ (s_id, p_id, j_id, qty) VALUES ('S1', 'P1', 'J4', 700);
INSERT INTO SPJ (s_id, p_id, j_id, qty) VALUES ('S2', 'P3', 'J1', 400);
INSERT INTO SPJ (s_id, p_id, j_id, qty) VALUES ('S2', 'P3', 'J2', 200);
INSERT INTO SPJ (s_id, p_id, j_id, qty) VALUES ('S2', 'P3', 'J3', 200);
INSERT INTO SPJ (s_id, p_id, j_id, qty) VALUES ('S2', 'P3', 'J4', 500);
INSERT INTO SPJ (s_id, p_id, j_id, qty) VALUES ('S2', 'P3', 'J5', 600);
INSERT INTO SPJ (s_id, p_id, j_id, qty) VALUES ('S2', 'P3', 'J6', 400);
INSERT INTO SPJ (s_id, p_id, j_id, qty) VALUES ('S2', 'P3', 'J7', 800);
INSERT INTO SPJ (s_id, p_id, j_id, qty) VALUES ('S2', 'P5', 'J2', 100);
INSERT INTO SPJ (s_id, p_id, j_id, qty) VALUES ('S3', 'P3', 'J1', 200);
INSERT INTO SPJ (s_id, p_id, j_id, qty) VALUES ('S3', 'P4', 'J2', 500);
INSERT INTO SPJ (s_id, p_id, j_id, qty) VALUES ('S4', 'P6', 'J3', 300);
INSERT INTO SPJ (s_id, p_id, j_id, qty) VALUES ('S4', 'P6', 'J7', 300);
INSERT INTO SPJ (s_id, p_id, j_id, qty) VALUES ('S5', 'P2', 'J2', 200);
INSERT INTO SPJ (s_id, p_id, j_id, qty) VALUES ('S5', 'P2', 'J4', 100);
INSERT INTO SPJ (s_id, p_id, j_id, qty) VALUES ('S5', 'P5', 'J5', 500);
INSERT INTO SPJ (s_id, p_id, j_id, qty) VALUES ('S5', 'P5', 'J7', 100);
INSERT INTO SPJ (s_id, p_id, j_id, qty) VALUES ('S5', 'P6', 'J2', 200);
INSERT INTO SPJ (s_id, p_id, j_id, qty) VALUES ('S5', 'P1', 'J4', 1000);
INSERT INTO SPJ (s_id, p_id, j_id, qty) VALUES ('S5', 'P3', 'J4', 1200);
INSERT INTO SPJ (s_id, p_id, j_id, qty) VALUES ('S5', 'P4', 'J4', 800);
INSERT INTO SPJ (s_id, p_id, j_id, qty) VALUES ('S5', 'P5', 'J4', 400);
INSERT INTO SPJ (s_id, p_id, j_id, qty) VALUES ('S5', 'P6', 'J4', 500);
COMMIT;
Ovakvim skriptama dajes strukturu i testne podatke, a ljudi koji ti pokusavaju pomoci se mogu usredsrediti na resavanje problema.
Evo ti odgovor na 15. pitanje upotrebom tehnike na koju sam ti ukazao (za supplier-a S1), a ti probaj sam da uradis 16-to pitanje.
Code:
SELECT spj1.j_id, spj1.s_id
FROM spj AS spj1
WHERE spj1.s_id = 'S1'
GROUP BY spj1.j_id, spj1.s_id
HAVING COUNT(spj1.p_id) = (SELECT COUNT(DISTINCT(spj2.p_id))
FROM spj AS spj2
WHERE spj1.s_id = spj2.s_id)
Inace, proces kojim si od pocetnih tabela dosao do ovih koje si postovao nije normalizacija, nego KODIRANJE.
Kodirao si boju 'Red' sa kodom 'C1#', a isto tako si tu boju 'Red' mogao da kodiras sa 'C1+', ili 'piros' (na Madjarskom) ili u hexa oblik '0000FF'.
Isto tako si grad 'Rome' kodirao sa 'C2', a mogao si i sa 'RI' ili '15' ili 'Rim'.
I jos jedanput: Izvrseno je kodiranje, a ne normalizacija.
[Ovu poruku je menjao chachka dana 05.09.2006. u 23:04 GMT+1]
"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
Kako ostvariti INTERSECT u bazi koja ga nema sql-2000
Re: Kako ostvariti INTERSECT u bazi koja ga nema sql-2000
Re: Kako ostvariti INTERSECT u bazi koja ga nema sql-2000