Zad. 1 z lab6x.pdf
a)
select * from P where city ='London'

b)
select P# from P where city ='London'

c) wypisalo 21 pozycji
select distinct J#,P# from SPJ

d) Wypisalo 7 pozycji, nic same cyferki, wtf
select J#, (select COUNT(P#) from P where city = 'London'),
(select count(distinct P.P#) from P join SPJ on P.P#=SPJ.P# where city = 'london'
and J.J#=SPJ.J#) from J

e) wypisalo tylko 1 pozyje, bez distinct nic nie wypisalo
select * from J
where
(select COUNT(P#) from P where city = 'London')
= (select count(distinct P.P#) from P join SPJ on P.P#=SPJ.P# where city = 'london'
and j.j#=SPJ.J#)

f) wypisalo tylko J4
select j# from j
except
select j# from
(select j#, p# from j, p where p.city = 'london'
except
select j#, p# from spj
)
t


Zad. 2 nie wiem o co chodzilo ale ktos to ma

a)
select j# from j where
not exists
( -- lista czesci z londynu
select

except
-- lista czesci z londynu dostarczona przez j
)


b) to tez wypisalo tylko 1 pozycje, j4
select j# from j where
not exists
( -- lista czesci z londynu
select * from
P left outer join SPJ on P.P#=SPJ.P# and SPJ.J#=J.J#
where city = 'london' and spj.p# is null
)


c) nic nie wypisalo lol xd
select * from
P left outer join SPJ on P.P#=SPJ.P# and SPJ.J#='J4'
where city = 'london' and SPJ.P# is null



zad. 3

to chyba juz jest zadanie 3 wypisalo 10 pozycji, wypisalo pary s, j tak jak w zad 3 wowowowo

select s#, j# from s, j
where
-- s dostarcza all co j odbierze
not exists
(
select P# from SPJ where SPJ.J#=J.J#
except
select P# from SPJ where SPJ.S#=S.S#
)


Zad. ktore nie wiem, ale wypisalo tabele 2x2
a)

select S#,J# from S,J
except
select S#,J# from
(
select S.S#,SPJ.J#,SPJ.P# from SPJ,S
except
select S#,J#,P# from SPJ
) t

b) wypisalo 10 pozycji s# i J#
select S#,J# from S,J
except
select S#,J# from
(
select S.S#,SPJ.J#,SPJ.P# from SPJ,S
except
select SPJ.S#,J.J#,SPJ.P# from SPJ,J
) t


teraz lomatkoboska nordwind loghohoho


Zad. 1 nie wyszukalo pracownika ani klienta ktory by wszystko kupowal, albo bledy w kodzie

select EmployeeID from Employees
where

not exists
(
select ProductID from Products
except
select ProductID from Orders join [Order Details] on Orders.OrderID=[Order Details].OrderID
and Orders.EmployeeID=Employees.EmployeeID
)



WTFFFFF znowu nie pokazalo zadnego wyniku *--538*7;

with ep as
(
select productID,EmployeeID from Orders join [Order Details] on Orders.OrderID=[Order Details].OrderID
)

select EmployeeID from ep
except
select EmployeeID from
(
select EmployeeID, ProductID from Employees,Products
except
select EmployeeID, ProductID from ep
)
t