Matjaž Horvat pravi:
Helou!
Mi kdo lahko poslje resitev 7. naloge v 3. vajah, ki smo jih resevali
na vajah za PB v 1. semestru?
To je naloga:
"Pre�itaj ťifro trgovca (X); poiť�i ťifre projektov, ki kupujejo
vse potrebne dele tudi pri trgovcu X!"
LP,
Matjaz
__________ NOD32 1.1407 (20060213) Information __________
This message was checked by NOD32 antivirus system.
http://www.eset.com
VAJA3
1.
SELECT S.CITY,SPJ.P#,J.CITY
FROM S , SPJ , J
WHERE S.CITY <> J.CITY AND SPJ.S#=S.S# AND SPJ.J#=J.J#;
2.
SELECT DISTINCT T1.S#
FROM SPJ T1
WHERE NOT EXISTS ((SELECT J#
FROM J)
MINUS
(SELECT T2.J#
FROM SPJ T2
WHERE T2.P#=T1.P# AND T2.S#=T1.S#));
3.
SELECT T.J#
FROM SPJ T
WHERE T.S#='&X' AND T.J# NOT IN (SELECT K.J#
FROM SPJ K
WHERE
K.S#<>'&X');
4.
SELECT DISTINCT P#
FROM P
WHERE NOT EXISTS ((SELECT J.J#
FROM J
WHERE J.CITY='&X')
MINUS
(SELECT J#
FROM SPJ
WHERE SPJ.P#=P.P#));
5.
SELECT DISTINCT K.J#
FROM SPJ K
WHERE NOT EXISTS ((SELECT K1.P#
FROM SPJ K1
WHERE K1.S#='&X')
MINUS
(SELECT K2.P#
FROM SPJ K2
WHERE K2.J#=K.J#));
6.
SELECT DISTINCT K.J#
FROM SPJ K
WHERE NOT EXISTS ((SELECT K1.P#
FROM SPJ K1
WHERE K1.J#=K.J#)
MINUS
(SELECT K2.P#
FROM SPJ K2
WHERE K2.S#='&X'));
7.
SELECT DISTINCT K.J#
FROM SPJ K
WHERE NOT EXISTS ((SELECT K1.P#
FROM SPJ K1
WHERE K1.S#='&X')
MINUS
(SELECT K2.P#
FROM SPJ K2
WHERE K2.J#=K.J# AND K2.S#='&X'));
8.
SELECT DISTINCT K.J#
FROM SPJ K
WHERE NOT EXISTS ((SELECT K1.P#
FROM SPJ K1
WHERE K1.J#=K.J#)
MINUS
(SELECT K2.P#
FROM SPJ K2
WHERE K2.S#='&X' AND K2.J#=K.J#));
9.
SELECT DISTINCT K.J#
FROM SPJ K
WHERE NOT EXISTS ((SELECT S#
FROM SPJ K1
WHERE K1.J#=K.J#)
MINUS
(SELECT S#
FROM SPJ NATURAL JOIN P
WHERE COLOR='Red'));
10.
SELECT COUNT(DISTINCT J#) AS STEVILO
FROM SPJ
WHERE S#='&X';
11.
SELECT SUM(QTY) AS KOLICINA
FROM SPJ
WHERE P#='&X' AND S#='&Y';
12.
SELECT P#,J#,SUM(QTY) AS KOLICINA
FROM SPJ
GROUP BY P#, J#;
