DB - Sql2

SQL 2

Ahmad Yoosofan

Database Course

https://yoosofan.github.io/slide/db.ql2.presentation.html

https://yoosofan.github.io/course/db.html

1

Find the names of suppliers who are located in a city where at least one part is stored

select distinct sname
from s natural join p;

select sname
from s
where exists (
    select *
    from p
    where p.city = s.city
  );

sname
Smith
Jones
Blake
Clark

2

Find the names of parts that weigh more than at least one other part

select distinct T.pname
from p as T join p on
  T.pn <> p.pn and
  T.weight > p.weight;

select distinct T.pname
from p as T join p on
T.weight > p.weight;

select distinct pname
from p as T
where exists (
    select *
    from p
    where T.weight > p.weight
  );

select distinct T.pname
from p as T join p on
  T.pn < p.pn and
  T.weight > p.weight
  ; -- incorrect

pname
Bolt
Screw

3

Find the names of parts that weigh more than at least one part in Paris

select distinct pname
from p as T
where exists (
    select *
    from p
    where city = 'Paris' and
      T.weight > p.weight
  );

select distinct pname
from p as T
where exists (
    select *
    from p
    where city = 'Paris' and
      T.weight > weight
  );

pname
Bolt
Screw
Cog

4

Find the names of the lightest parts

Find the names of the parts such that no other part weighs less than them

pname
Nut
Cam

select pname
from p as T
where not exists (
    select *
    from p
    where T.weight > p.weight
  );

pname
Nut
Cam
Nut
Bolt

select pname
from p as T
where not exists (
    select *
    from p
    where T.weight > p.weight
  ) and T.weight is not null;

pname	weight
Nut	12
Cam	12

5

Find the cities of suppliers that contain no parts

1 select distinct city
2 from   s
3 where not exists(
4     select *
5     from p
6     where p.city = s.city
7   );

1 select city
2 from s
3 except
4 select city
5 from p;

1 select city
2 from   s
3 where not exists(
4     select *
5     from p
6     where p.city = s.city
7   );

1 select city
2 from s
3 except all
4 select city
5 from p;

city
Athens
کاشان

6

Find the names of parts that are supplied exclusively by suppliers located in the same city, or are not supplied at all

Find the names of parts that have not been supplied by any supplier located in a different city

1 select pname
2 from   p
3 where not exists(
4     select *
5     from s natural join sp
6     where sp.pn = p.pn and
7       p.city <> s.city
8   );

 1 select pname
 2 from   p
 3 where not exists(
 4     select *
 5     from s
 6     where s.city <> p.city and
 7       exists(
 8         select *
 9         from sp
10         where  sp.pn = p.pn and
11           sp.sn = s.sn
12       )
13   );

pname
Screw
Cog
Nut
Bolt

7

Find the names of supplied parts that are provided only by suppliers located in the same city

1 select pname
2 from   p natural join sp as T
3 where not exists(
4     select *
5     from s natural join sp
6     where sp.pn = p.pn and
7       p.city <> s.city
8   );

pname
Screw
Cog
Screw

 1 select pname
 2 from   p natural join sp
 3 where not exists(
 4     select *
 5     from s
 6     where s.city <> p.city and
 7       exists(
 8         select *
 9         from sp
10         where  sp.pn = p.pn and
11           sp.sn = s.sn
12       )
13   );

 1 select pname
 2 from   p
 3 where exists(select * from sp where sp.pn=p.pn)
 4     and not exists( -- Run to see the differnce
 5     select *  -- Add distinct to previous SQLs
 6     from s
 7     where s.city <> p.city and
 8       exists(
 9         select *
10         from sp
11         where  sp.pn = p.pn and
12           sp.sn = s.sn
13       )
14   );

8

List the distinct names of parts that are supplied, such that all of their suppliers reside in the part's city

 1 select distinct pname
 2 from p natural join sp
 3 where not exists(
 4     select *
 5     from sp,s
 6     where sp.sn = s.sn and
 7       sp.pn = p.pn and
 8       p.city <> s.city
 9   );

 1   select pname -- ریحانه زمانیان
 2   from p natural join sp
 3 except
 4   select pname
 5   from p
 6   where exists(
 7     select *
 8     from s natural join sp
 9     where sp.pn=p.pn and
10         p.city <> s.city
11   ) ;

 1 SELECT DISTINCT p1.pname
 2 FROM p p1   --- Gemini AI
 3 WHERE EXISTS (
 4     SELECT 1
 5     FROM sp sp_test
 6     WHERE sp_test.pn = p1.pn
 7 ) AND NOT EXISTS (
 8     SELECT 1
 9     FROM sp sp1
10     JOIN s s1 ON sp1.sn = s1.sn
11     WHERE sp1.pn = p1.pn
12     AND s1.city <> p1.city
13 );

pname
Screw
Cog

9

نام قطعه‌های عرضه شده‌ای را بیابید که شماره‌های متفاوتی داشته باشند هر چند شاید نام یکسانی داشته باشند که فقط عرضه کنندگان درون آن شهرها، آنها را عرضه کرده باشند

 1   select pname
 2   from p natural join(
 3         select distinct pn
 4         from p natural join sp
 5         where not exists(
 6             select *
 7             from sp,s
 8             where sp.sn = s.sn and
 9               sp.pn = p.pn and
10               p.city <> s.city
11           )
12     );

 1 select pname
 2 from p
 3 where exists(
 4     select *
 5     from sp
 6     where sp.pn = p.pn and
 7       not exists (
 8             select *
 9             from sp natural join s
10             where sp.pn = p.pn and
11             p.city <> s.city
12        )
13  );

10

نام قطعاتی را بیابید که همهٔ عرضه کنندگان آنها را عرضه کرده باشند

نام قطعاتی را بیابید که عرضه‌کننده‌ای وجود نداشته باشد که این قطعات را عرضه نکرده باشد.

نام قطعاتی را می‌خواهیم که وجود نداشته باشد عرضه‌کننده‌ای که برایش وجود نداشته باشد عرضه‌ای که آن عرضه از آن عرضه کننده و آن قطعه باشد.

select pname
from p
where not exists(
    select *
    from s
    where not exists(
        select *
        from sp
        where s.sn = sp.sn
          and p.pn = sp.pn
      )
  );

pname

11

نام قطعات متفاوتی را بیابید که همهٔ عرضه کنندگان با وضعیت بالای ۱۰۰ آنها را عرضه کرده باشند

select distinct pname
from p join sp
where not exists(
    select *
    from s
    where status > 100 and
      not exists(
        select *
        from sp
        where s.sn = sp.sn and
          p.pn = sp.pn
      )
  ) and exists (
     select *
     from s
     where s.status > 100 and
      s.sn = sp. sn
  );

pname
Nut
Bolt
Screw
Cam
Cog

12

Aggregation Functions

جمع همهٔ عرضه‌ها(qty) را بیابید.

select sum(qty) as sqt
from   sp;

sqt
1600

sn	pn	qty
S1	P1	300
S1	P5	100
S2	P1	300
S2	P2	400
S4	P2	200
S4	P4	300

13

جمع عرضه‌های عرضه کنندهٔ s2 را بیابید.

select sum(qty) as sqt
from   sp
where sp.sn = 'S2';

sqt
700

sn	pn	qty
S1	P1	300
S1	P5	100
S2	P1	300
S2	P2	400
S4	P2	200
S4	P4	300

14

جمع وزن قطعات متفاوتی را بیابید که عرضه‌کننده‌ای در شهر پاریس آنها را عرضه کرده باشد(۱).

1 select sum(weight) as swg
2 from p
3 where exists (
4     select *
5     from sp natural join s
6     where p.pn = sp.pn and s.city = 'Paris'
7   );

swg
29

1 select sum(weight) as swg
2 from (p natural join sp)
3   join s using(sn)
4 where s.city = 'Paris';

swg
46

15

جمع وزن قطعات متفاوتی را بیابید که عرضه‌کننده‌ای در شهر پاریس آنها را عرضه کرده باشد(۲).

select sum(weight) as swg
from p
where exists(
  select *
  from s
  where s.city = 'Paris' and exists(
    select *
    from sp
    where sp.sn = s.sn and sp.pn = p.pn
  )
);

swg
29

16

جمع وزن قطعات متفاوتی را بیابید که عرضه‌کننده‌ای در شهر پاریس آنها را عرضه کرده باشد(۳).

select sum(weight) as swg
from p
where exists (
    select *
    from sp natural join s
    where p.pn = sp.pn and s.city = 'Paris'
  );

swg
29

select sum(weight) as swg
from (p natural join sp)
  join s using(sn)
where s.city = 'Paris';

swg
46

select pn, weight, sn, s.city
from (p natural join sp)
  join s using(sn)
where s.city = 'Paris';

pn	weight	sn	city
P1	12	S2	Paris
P2	17	S2	Paris
P2	17	S3	Paris

17

جمع وزن قطعات متفاوتی را بیابید که عرضه‌کننده‌ای در شهر پاریس آنها را عرضه کرده باشد(۴).

select sum(weight) as swg
from p
where exists (
    select *
    from sp natural join s
    where p.pn = sp.pn and s.city = 'Paris'
  );

swg
29

select sum(distinct weight) as swg
from (p natural join sp)
  join s using(sn)
where s.city = 'Paris';

swg
29

آیا این راه حل آخری با distinct درست است؟

راه حل دیگر

18

جمع وزن قطعات متفاوتی را بیابید که عرضه‌کننده‌ای در شهر پاریس آنها را عرضه کرده باشد(۵).

select swg
from (
  select distinct pn, sum(weight) as swg
  from (p natural join sp)
    join s using(sn)
  where s.city = 'Paris'
);

swg
46

select sum(weight) as swg
from (
  select distinct pn, weight
  from (p natural join sp)
      join s using(sn)
  where s.city = 'Paris'
);

swg
29

19

جمع وزنی قطعات عرضه‌شده به تعداد qty را بیابید که عرضه‌کننده‌ای در شهر پاریس آنها را عرضه کرده باشد.

select sum(qty * weight) as swg
from (p natural join sp)
  join s using(sn)
where s.city = 'Paris';

swg
13800

select pn, qty, weight
from (p natural join sp)
  join s using(sn)
where s.city = 'Paris';

pn	qty	weight
p1	300	12
p2	400	17
p2	200	17

20

Average

میانگین وزن قطعه‌ها را بیابید

select avg(weight) as awg
from p;

میانگین مقدار عرضه‌ها(qty) را بیابید

select avg(qty) as sqt
from   sp;

21

میانگین وزن قطعات را در شهر پاریس بیابید

select avg(weight) as awg
from p
where city='Paris';

awg
14.5

22

میانگین مقدار عرضه‌های(qty) عرضه‌کنندگان شهر پاریس را بیابید

select avg(qty) as paqt
from s natural join sp
where s.city = 'Paris';

23

Count

تعداد قطعات را بیابید

select count(pn) as awg
from p;

awg
8

select count(weight) as awg
from p;

awg
6

24

Count(*)

تعداد قطعات را بیابید

select count(*) as awg
from p;

awg
8

select count(city) as ccy
from p;

ccy
8

select count(*) as sqt
from   sp;

  -- همهٔ رکوردها را می‌شمارد به فیلد خاصی مربوط نیست.

sqt
12

25

count(distinct)

تعداد شهرهای قطعات را بیابید

select count(distinct city) as ccy
from p;

ccy
3

select city
from p;

city
Oslo
London
Paris
London
London
Paris

26

تعداد عرضه‌کنندگانی را بیابید که قطعه‌ای عرضه کرده باشند

select count(distinct sn) as sqt
from sp;
-- شماره‌های تکراری را نمی‌شمارد

sqt
4

select distinct sn
from sp;

sn
S1
S2
S3
S4

27

تعداد عرضه‌کنندگانی را بیابید که قطعهٔ قرمزی را به تعداد عرضه(qty) بیشتر از ۵ عرضه کرده باشند.

select count(distinct sn) as scc
from sp natural join p
where qty > 5 and
  p.color = 'Red';

scc
3

28

تعداد عرضه‌کنندگانی را بیابید که دست کم مقدار یکی از عرضه‌های آنها بیشتر از ۵ باشد و عرضه‌ای از قطعه‌ای به رنگ قرمز نیز داشته باشند(۱).

select count(*) as csn
from (
    select sn
    from sp
    where qty > 5
  intersect
    select sn
    from sp join p using (pn)
    where p.color = 'Red'
);

select count(*) as csn -- May have error
from (
      select sname
      from s natural join sp
      where qty > 5
    intersect
      select sname
      from s natural join sp
        join p using (pn)
      where p.color = 'Red'
  );

29

تعداد عرضه‌کنندگانی را بیابید که دست کم مقدار یکی از عرضه‌های آنها بیشتر از ۵ باشد و عرضه‌ای از قطعه‌ای به رنگ قرمز نیز داشته باشند(۲).

select count(sn) as csn
from s
where exists(
    select *
    from sp
    where sp.sn = s.sn
          and sp.qty > 5
  ) and exists(
    select *
    from sp natural join p
    where s.sn = sp.sn and
      p.color = 'Red'
  );

csn
3

30

Min

کمترین وزن قطعه را بیابید

select min(weight) as wgt
from   p;
--- کمترین

31

max

بیشترین وزن قطعه را بیابید

select max(weight) as wgt
from   p;
--- بیشترین
--- به اینها تابع تجمعی گفته می‌شود. aggregation function

32

group by

شماره و مجموع عرضه‌های عرضه‌کنندگانی را بیابید که قطعه‌ای عرضه کرده‌اند.

select sn, sum(qty) as sqt
from   sp
group by sn;

بر پایهٔ شمارهٔ عرضه کننده دسته‌بندی می‌کند
سپس برای هر دسته
شمارهٔ آن عرضه کننده (که با آن گروه‌بندی انجام شده است)

sn	sqt
s1	1300
s2	700
s3	200
s4	900
s6	350

33

شماره و مجموع عرضه‌های قطعاتی را بیابید که عرضه شده باشند.

1 select pn, sum(qty) as sqt
2 from   sp
3 group by pn;

pn	sqt
p1	600
p2	1350
p3	400
p4	500
p5	500
p6	100

34

شمارهٔ قطعات با وزن بیشتر از ۱۲ را همراه با جمع عرضه‌های هر کدام بیابید

select pn, sum(qty) as sqt
from sp join p using(pn)
where weight > 12
group by pn;

نخست شرط where اعمال می‌شود سپس بر روی رکوردهای باقیمانده دسته‌بندی انجام می‌شود.

pn	sqt
p2	1350
p3	400
p4	500
p6	100

35

شمارهٔ قطعات با وزن بیشتر از ۱۲ را همراه با جمع عرضه‌های هر کدام بیابید به شرطی که بیشتر از دو عرضه کننده آنها را عرضه کرده باشند

1 select pn, sum(qty) as sqt
2 from sp join p using(pn)
3 where weight>12
4 group by pn
5 having count(sn)>2;

مانند پیشین با این تفاوت که گروه‌هایی برگردانده می‌شوند که شرط having را نیز داشته باشند.

pn	sqt
p2	1350

36

گام به گام

select pn, qty as sqt
from sp join p using(pn)
where weight>12;

pn	sn	qty
p1	s1	300
p1	s2	300

pn	sn	qty
p2	s1	200
p2	s2	400
p2	s3	200
p2	s4	200
p2	s6	350

pn	sn	qty
p3	s1	400

.

pn	sn	qty
p6	s1	100

pn	sn	qty
p4	s1	200
p4	s4	300

pn	sn	qty
p5	s1	100
p5	s4	400

37

نام قطعاتی را بیابید که بیشتر از دو عرضه کننده آنها را عرضه کرده باشند

 1 select distinct pname
 2 from sp join p using(pn)
 3 where exists(
 4   select *
 5   from sp as T
 6   where T.sn <> sp.sn and
 7     T.pn = sp.pn and exists(
 8       select *
 9       from sp as T2
10       where T.sn <> sp.sn and
11         T2.sn <> sp.sn and
12         T2.sn <> T.sn and
13         T2.pn = sp.pn
14     )
15 )

 1 select distinct pname
 2 from p natural join (
 3   select pn
 4   from sp join p using(pn)
 5   group by pn
 6   having count(sn) > 2
 7 );
 8 
 9 -- Second solution
10 select distinct pname
11 from p join sp using(pn) join
12   sp as T1 using(pn) join
13   sp as T2 using(pn)
14 where T1.sn <> sp.sn and
15   T2.sn <> sp.sn and
16   T2.sn <> T1.sn;

38

نام شهرهای قطعاتی را بیابید که عرضه‌کننده‌ای با وضعیت بیشتر از ۱۰ نیز آنها را عرضه کرده باشد و جمع عرضه‌های هر کدام از آن شهرهای قطعه‌ها بیشتر از ۲۰ باشد(۱)

1 select city
2 from p;

1 select p.city as pcity
2 from p join sp using(pn)
3   join s using(sn)
4 where status > 10;

1 select p.city as pcity --wrong
2 from p join sp using(pn)
3   join s using(sn)
4 where status > 10
5 group by p.city
6 having sum(qty) > 20;

1 select p.city as pcity
2 from p
3 where exists(
4   select *
5   from sp join s using(sn)
6   where p.pn = sp.pn and
7     status > 10
8 );

39

نام شهرهای قطعاتی را بیابید که عرضه‌کننده‌ای با وضعیت بیشتر از ۱۰ نیز آنها را عرضه کرده باشد و جمع عرضه‌های هر کدام از آن شهرهای قطعه‌ها بیشتر از ۲۰ باشد(۲)

 1 select p.city as pcity
 2 from p natural join sp
 3 where exists(
 4   select *
 5   from s
 6   where s.sn = sp.sn and
 7     status > 10
 8 )
 9 group by p.city
10 having sum(qty) > 20;

40

نام شهرهای قطعاتی را بیابید که عرضه‌کننده‌ای با وضعیت بیشتر از ۱۰ نیز آنها را عرضه کرده باشد و جمع عرضه‌های هر کدام از آن شهرهای قطعه‌ها بیشتر از ۲۰ باشد

 1 select p.city
 2 from p join sp using(pn)
 3 where exists (
 4     select *
 5     from s
 6     where s.sn = sp.sn and status > 10
 7   )
 8 group by p.city
 9 having sum(qty) > 20;

city
London
Oslo
Paris

 1 select p.city  --- نادرست
 2 from s natural join sp
 3   join p using(pn)
 4 where status > 10
 5 group by p.city
 6 having sum(qty) > 20;
 7 
 8 select city -- نادرست
 9 from sp natural join p
10 group by pn
11 having status >10 and sum(qty) > 20;

 1 select p.city
 2 from p join sp using(pn)
 3 where exists (
 4     select *
 5     from (s natural join sp) as T
 6     where T.pn = p.pn and status > 10
 7   )
 8 group by p.city
 9 having sum(qty) > 20;

41

نام شهرهای قطعاتی را بیابید که فقط عرضه‌کنندگان با وضعیت بیشتر از ۱۰ آنها را عرضه کرده باشند و جمع عرضه‌های هر کدام از آن شهرهای قطعه‌ها بیشتر از ۲۰ باشد

1 select p.city
2 from s natural join sp
3   join p using(pn)
4 where status > 10
5 group by p.city
6 having sum(qty) > 20;

city
London
Oslo
Paris

 1 select p.city
 2 from sp join p using(pn) natural join
 3   (
 4     select sn
 5     from s
 6     where status > 10
 7   )
 8 group by p.city
 9 having sum(qty) > 20;

42

نام پروژه‌هایی را بیابید که عرضه‌کننده‌ای با وضعیت بیشتر از ۲۰ نیز آنها را عرضه کرده باشند و جمع وزنی عرضه‌های هر کدام از آن پروژه‌ها بیشتر از ۱۰۰ باشد(I)

 1 select jname
 2 from spj join j using(jn)
 3   join p using(pn)
 4 where exists (
 5     select *
 6     from s
 7     where s.sn = spj.sn and
 8       s.status > 20
 9   )
10 group by jn
11 having(sum(weight*qty)>100);

 1 select jname
 2 from j natural join (
 3   select jn
 4   from spj join j using(jn)
 5     join p using(pn)
 6   where exists (
 7       select *
 8       from s
 9       where s.sn = spj.sn and
10         s.status > 20
11     )
12   group by jn
13   having(sum(weight*qty)>100)
14 );

 1 select jname
 2 from j natural join (
 3   select jn
 4   from spj join j using(jn)
 5     join p using(pn)
 6   where exists (
 7       select *
 8       from s
 9       where s.sn = spj.sn and
10         s.status > 20
11     )
12   group by jn
13   having(sum(weight*qty)>100)
14 );

43

نام پروژه‌هایی را بیابید که عرضه‌کننده‌ای با وضعیت بیشتر از ۲۰ نیز آنها را عرضه کرده باشند و جمع وزنی عرضه‌های هر کدام از آن پروژه‌ها بیشتر از ۱۰۰ باشد(II)

 1 select jname
 2 from j natural join (
 3   select jn
 4   from spj join j using(jn)
 5     join p using(pn)
 6   where exists (
 7       select *
 8       from s
 9       where s.sn = spj.sn and
10         s.status > 20
11     )
12   group by jn
13   having(sum(weight*qty)>100)
14 );

44

شمارهٔ قطعات با وزن بیشتر از ۱۲ را همراه با جمع عرضه‌های هر کدام بیابید که بیشتر از دو عرضه کننده آنها را عرضه کرده باشند

1 select pn, sum(qty)
2 from sp natural join p
3 where p.weight > 12
4 group by pn
5 having count(sn)>2;

1 select pn, sum(qty) -- same result
2 from sp natural join p
3 where p.weight > 12
4 group by pn
5 having count(distinct sn)>2;

1 select pn, sum(qty) -- wrong
2 from spj natural join p
3 where p.weight > 12
4 group by pn
5 having count(sn)>2;

1 select pn, sum(qty)
2 from spj natural join p
3 where p.weight > 12
4 group by pn
5 having count(distinct sn)>2;

45

نام شهرهای قطعاتی را بیابید که عرضه‌کننده‌ای با وضعیت بیشتر از ده ، دست کم یکی از قطعات درون آن شهرها را عرضه کرده باشد و مجموع عرضه‌های قطعه‌های آن شهرها بیشتر از ۲۰ باشد به شرطی که تعداد قطعات در آن شهر قطعه بیشتر از دو باشد(I).

1 select p.city -- wrong answer
2 from p join sp using(pn)
3   join s using(sn)
4 where s.status > 10
5 group by p.city
6 having sum(qty) > 20 and
7   count(distinct pn) > 2;

 1 SELECT p.city  -- wrong answer
 2 FROM p NATURAL JOIN sp
 3 WHERE EXISTS(
 4   SELECT * FROM s NATURAL JOIN sp
 5   WHERE s.sn=sp.sn AND p.city=s.city
 6     AND s.status > 10
 7   )
 8 GROUP by pn
 9 HAVING count(pn)>2 and sum(qty)>20;

 1 select p.city -- wrong answer
 2 from p join sp using(pn)
 3 where exists(
 4     select *
 5     from s -- Mohammad Javad Akbari
 6     where status > 10 and
 7       s.sn = sp.sn
 8   )
 9 group by p.city
10 having sum(qty) > 20 and
11   count(pn) > 2;

46

نام شهرهای قطعاتی را بیابید که عرضه‌کننده‌ای با وضعیت بیشتر از ده یکی از قطعات درون آن شهرها را عرضه کرده باشد و مجموع عرضه‌های قطعه‌های آن شهرها بیشتر از ۲۰ باشد به شرطی که تعداد قطعات در آن شهر قطعه بیشتر از دو باشد.

نام شهرهای قطعاتی را بیابید که عرضه‌کننده‌ای با وضعیت بیشتر از ده ، دست کم یکی از قطعات درون آن شهرها را عرضه کرده باشد و مجموع عرضه‌های قطعه‌های آن شهرها بیشتر از ۲۰ باشد به شرطی که تعداد قطعات در آن شهر قطعه بیشتر از دو باشد(II).

 1 select p.city
 2 from p join sp using(pn)
 3 where exists(
 4     select *
 5     from s
 6     where status > 10 and
 7       s.sn = sp.sn
 8   ) group by p.city
 9 having sum(qty) > 20 and
10   count(distinct pn) > 2;

 1 select p.city
 2 from p join sp using(pn)
 3 where exists(
 4     select *  from s
 5     where status > 10 and s.sn = sp.sn
 6   ) and exists(
 7     select * from p as p2
 8     where p.city = p2.city and p.pn <> p2.pn
 9   ) group by p.city
10 having sum(qty) > 20;

47

نام شهرهای قطعاتی را بیابید که عرضه‌کننده‌ای با وضعیت بیشتر از ده ، دست کم یکی از قطعات درون آن شهرها را عرضه کرده باشد و مجموع عرضه‌های قطعه‌های آن شهرها بیشتر از ۲۰ باشد به شرطی که تعداد قطعات در آن شهر قطعه بیشتر از دو باشد(III).

 1 select p.city
 2 from p join sp using(pn)
 3 where exists(
 4     select *
 5     from s
 6     where status > 10 and
 7       s.sn = sp.sn
 8   )
 9 group by p.city
10 having sum(qty) > 20 and
11   count(distinct pn) > 2;

 1 select p.city
 2 from p join sp using(pn)
 3 where exists(
 4     select *
 5     from s
 6     where status > 10 and
 7       s.sn = sp.sn
 8   ) and exists(
 9     select *
10     from p as p2, p as p3
11     where p.city = p2.city and
12       p.pn <> p2.pn and p.city = p3.city
13       and p.pn <> p3.pn and p2.pn <> p3.pn
14   )
15 group by p.city
16 having sum(qty) > 20;

48

نام پروژه‌هایی را بیابید که عرضه‌کننده‌ای با وضعیت بیشتر از ۲۰ برای آن پروژه‌ها عرضه کرده باشد و مجموع وزن قطعات عرضه شده برای آن نام پروژه (یا پروژه‌ها) بیشتر از ۱۰۰ باشد

دقت کنید مجموع وزن قطعات باید تعداد در وزن ضرب شود

 1 select jname
 2 from spj join j on
 3   spj.jn = j.jn join
 4   p using(pn)
 5 where exists(
 6     select *
 7     from s
 8     where s.sn = spj.sn
 9       and s.status > 20
10   )
11 group by jname
12 having(sum(weight*qty)>100);

49

نام پروژه‌هایی را بیابید که عرضه‌کننده‌ای با وضعیت بیشتر از ۲۰ برای آن پروژه‌ها عرضه کرده باشد و مجموع وزن قطعات عرضه شده برای آن پروژه (و نه همراه با هم‌نام‌هایش) بیشتر از ۱۰۰ باشد

 1 select jname
 2 from spj join j on
 3   spj.jn = j.jn join
 4   p using(pn)
 5 where exists(
 6     select *
 7     from s
 8     where s.sn = spj.sn
 9       and s.status > 20
10   )
11 group by jn
12 having(sum(weight*qty)>100);

 1 select jname
 2 from j natural join (
 3   select jn
 4     from spj join j on
 5       spj.jn = j.jn join
 6       p using(pn)
 7     where exists(
 8         select *
 9         from s
10         where s.sn = spj.sn
11           and s.status > 20
12       )
13     group by jn
14     having(sum(weight*qty)>100)
15   );

50

دسته‌بندی در یک گروه

1 -- Totally wrong
2 select pn, count(distinct pn)
3 from p natural join sp
4 group by pname;
5 -- having count(distinct pn) > 1;

1 select pname, count(sn)
2 from p natural join sp
3 group by pn;
4 -- having count(distinct pn) > 1

51

جمع وزن قطعه‌هایی را بیابید که دستِ‌کم عرضه کننده‌ای از پاریس نیز آنها را عرضه کرده باشد.

1 select sum(weight) as swg
2 from (p natural join sp)
3   join s using(sn)
4 where s.city = 'Paris';

swg
46

1 select pn, weight, sn, s.city
2 from (p natural join sp)
3   join s using(sn)
4 where s.city = 'Paris';

pn	weight	sn	city
P1	12	S2	Paris
P2	17	S2	Paris
P2	17	S3	Paris

1 select sum(weight) as swg
2 from (
3     select distinct pn, weight
4     from (p natural join sp)
5       join s using(sn)
6     where s.city = 'Paris'
7   );

swg
29

52

جمع وزن قطعه‌هایی را بیابید که دستِ‌کم عرضه کننده‌ای از پاریس نیز آنها را عرضه کرده باشد.

1 select sum(weight) as swg
2 from p natural join sp
3 where exists(
4     select *
5     from s
6     where s.sn = sp.sn and
7       s.city = 'Paris'
8   );

swg
46

1 select sum(weight) as swg
2 from (
3     select distinct pn, weight
4     from (p natural join sp)
5       join s using(sn)
6     where s.city = 'Paris'
7   );

swg
29

بحث اصلی

53

Scalar value(II)

شماره و وزن قطعاتی را بیابید که وزن آنها از میانگین وزن همهٔ قطعات بیشتر است.

select pn, weight
from p
where weight > (
    select avg(weight)
    from p
  );

pn	weight
p2	17
p3	17
p6	19

54

Scalar value(III)

شماره و وزن قطعاتی را بیابید که کمترین وزن را داشته باشند.

select pn, weight
from p
where weight=(select min(weight) from p);

pn	weight
p1	12
p5	12

select pn, weight
from p  --incorrect
where weight = (
    select weight from p
    order by weight asc limit 1
);

select pn, weight from p
where weight = (
    select weight from p
    where weight is not null
    order by weight asc limit 1
);

55

شمارهٔ همهٔ قطعات را همراه با جمع تعداد عرضه‌های (qty) آن قطعات بیابید(۱).

select pn, sum(qty) as sqty
from sp
group by pn;

-- wrong

pn	sqty
p1	600
p2	1350
p3	400
p4	500
p5	500
p6	100

56

شمارهٔ همهٔ قطعات را همراه با جمع تعداد عرضه‌های (qty) آن قطعات بیابید(۲).

select pn, (
    select sum(qty)
    from sp
    where p.pn = sp.pn
  ) as sqty
from p ;

pn	sqty
p1	600
p2	1350
p3	400
p4	500
p5	500
p6	100
p7
p8

57

شمارهٔ همهٔ قطعات را همراه با جمع وضعیت عرضه‌کنندگان درون شهر آن قطعات به همراه شهر قطعه بیابید که به ترتیب نزولی وزن قطعه نشان داده شده باشند.

1 select pn,
2    (select sum(status)
3     from s
4     where s.city = p.city
5    ) as sum_status,
6    city
7 from p
8 order by weight desc ;

1 select pn, sum(status) , p.city
2 from p natural join sp natural join s
3 order by weight desc; -- wrong

1 select pn, sum(status) , p.city
2 from p join s using(city)
3 order by weight desc; -- wrong

pn	sum_status	city
p6	40	London
p2	40	Paris
p3		Oslo
p4	40	London
p1	40	London
p5	40	Paris
p7	40	London
p8	40	Paris

58

شمارهٔ همهٔ قطعات را همراه جمع تعداد عرضه‌های آنها بیابید.

1 select pn, sum(qty) as sqty
2 from p natural join sp
3 group by pn; -- wrong

select pn, (
    select sum(qty)
    from sp
    where sp.pn = p.pn
  ) as sqty
from p;

pn	sqty
p1	600
p2	1350
p3	400
p4	500
p5	500
p6	100

pn	sqty
p1	600
p2	1350
p3	400
p4	500
p5	500
p6	100
p7
p8

59

Left Outer Join(I)

select pn, sum(qty) as sqty
from p natural left outer join sp
group by pn;

select pn, sum(qty) as sqty
from p left outer join sp using(pn)
group by pn;

pn	sqty
p1	600
p2	1350
p3	400
p4	500
p5	500
p6	100
p7
p8

60

Left Outer Join(II)

select p.pn, sum(qty) as sqty
from p left outer join sp on p.pn = sp.pn
group by p.pn;

1 select pn, (
2     select sum(qty)
3     from sp
4     where sp.pn = p.pn
5   ) as sqty
6 from p;

61

نام شهرهای همهٔ قطعاتی را بیابید که عرضه‌کننده‌ای با وضعیت بیشتر از ده ، دست کم یکی از قطعات درون آن شهرها را عرضه کرده باشد و مجموع عرضه‌های قطعه‌های آن شهرها بیشتر از ۲۰ باشد به شرطی که تعداد قطعات در آن شهر قطعه بیشتر از دو باشد.

1 select p.city
2 from p left outer join sp using(pn)
3 where exists(
4     select *  from s
5     where status > 10 and s.sn = sp.sn
6   )
7 group by p.city
8 having sum(qty) > 20 and count(distinct pn) > 2;

1  select p.city -- incorrect
2  from p left outer join sp using(pn)
3  group by p.city
4  having sum(qty) > 20 and
5    count(distinct pn) > 2 and
6    max(status > 10);

1  select p.city
2  from p left outer join sp using(pn)
3    join s using(sn)
4  group by p.city
5  having sum(qty) > 20 and
6    count(distinct pn) > 2 and
7    max(status > 10);

62

نام شهرهای همهٔ قطعاتی را بیابید که عرضه‌کننده‌ای با وضعیت بیشتر از ده درون آن شهرها باشد و مجموع عرضه‌های قطعه‌های آن شهرها بیشتر از ۲۰ باشد به شرطی که تعداد قطعات در آن شهر قطعه بیشتر از دو باشد.

1 select p.city
2 from p left outer join sp using(pn)
3 where exists(
4     select *  from s
5     where status > 10 and s.city = p.city
6   )
7 group by p.city
8 having sum(qty) > 20 and count(distinct pn) > 2;

1  select p.city
2  from s join p using(city)
3    left outer join sp using(pn)
4  group by p.city
5  having sum(qty) > 20 and
6    count(distinct pn) > 2 and
7    max(status > 10);

63

Full Outer Join(I)

  select pn, sum(weight) as sqty
  from p natural full outer join s
  group by pn;

select pn, sum(weight) as sqty
from p full outer join s using(city)
group by pn;

select p.pn, sum(weight) as sqty
from p full outer join s on p.city = s.city
group by p.pn;

╭──────┬──────╮
│  pn  │  pw  │
╞══════╪══════╡
│ NULL │ NULL │
│ p1   │   24 │
│ p2   │   34 │
│ p3   │   17 │
│ p4   │   28 │
│ p5   │   24 │
│ p6   │   38 │
│ p7   │ NULL │
│ p8   │ NULL │
╰──────┴──────╯

select distinct p.city, s.city
from p natural left outer join s;

    ╭────────┬────────╮
    │  city  │  city  │
    ╞════════╪════════╡
    │ London │ London │
    │ Paris  │ Paris  │
    │ Oslo   │ NULL   │
    │ NULL   │ Athens │
    │ NULL   │ کاشان  │
    ╰────────┴────────╯

64

Full Outer Join(II)

select distinct p.city, s.city
from p natural full outer join s;

select distinct p.city, s.city
from p, s; -- very different result

select distinct p.city, s.city
from p, s; -- very different result
where p.city = s.city;

select distinct p.city, s.city
from p natural join s;

65

نام همهٔ شهرهای عرضه کنندگان را در کنار نام شهر قطعاتی که همشهری آنها هستند بنویسید و اگر قطعه‌ای همشهری آن عرضه کننده نبود نام شهر عرضه کننده همراه با null به جای نام شهر قطعه بیاید(I)

select s.city, p.city
from s left outer join p using(city);

select  p.city, s.city -- incorrect
from p full outer join s on p.city = s.city;

select  p.city, s.city -- incorrect
from p full outer join s using(city);

select  p.city, s.city -- incorrect
from p natural full outer join s;

select  p.city, s.city
from p natural right outer join s;

select  p.city, s.city -- incorrect
from p natural left outer join s;

66

نام همهٔ شهرهای عرضه کنندگان را در کنار نام شهر قطعاتی که همشهری آنها هستند بنویسید و اگر قطعه‌ای همشهری آن عرضه کننده نبود نام شهر عرضه کننده همراه با null بیاید(II)

select s.city as scity, p.city as pcity
from s left outer join p using(city);

select s.city as scity, p.city as pcity
from s, p --  wrong

scity	pcity
London	London
London	London
London	London
London	London
Paris	Paris
Paris	Paris
Paris	Paris
Paris	Paris
Paris	Paris
Paris	Paris
London	London
London	London
London	London
London	London
Athens
کاشان

67

نام قطعات و شمارهٔ عرضه‌کنندگان همشهری آن قطعات را بیابید. نیازی نیست آن عرضه کننده آن قطعه را عرضه کرده باشد.

select pname, sn
from p natural join (select city, sn from s);

select pname, sn
from p natural join s;

pname	sn
Nut	s1
Nut	s4
Bolt	s2
Bolt	s3
Screw	s1
Screw	s4
Cam	s2
Cam	s3
Cog	s1
Cog	s4
Nut	s1
Nut	s4
Bolt	s2
Bolt	s3

68

نام پروژه‌هایی را بیابید که عرضه‌کننده‌ای با وضعیت بیشتر از ۲۰ برای آن پروژه‌ها عرضه کرده باشد و مجموع وزن قطعات عرضه شده برای آن پروژه بیشتر از ۱۰۰ باشد. دقت کنید مجموع وزن قطعات باید qty در weight ضرب شود.

select jname -- نادرست
from spj join j using(jn) join s
  using(sn) join p using(pn)
where s.status > 20
group by jn
having(sum(weight * qty) > 100)

select jname  -- نادرست
from j join spj using(jn) join s
  using(sn) join P using(pn)
where status > 20
group by pn
having sum(qty * weight) > 100;

select jname -- درست
from p join spj using(pn) join
  j using(jn)
where exits(
  select *
  from s natural join (spj as T)
  where T.jn=j.jn and status > 20
)
group by jn
having (weight*qty)>100;

select jname -- درست
from j natural join (
  select jn
  from p join spj using(pn) join
    j using(jn)
  where exists(
    select *
    from s natural join spj as T
    where T.jn=j.jn and status > 20
  )
  group by jn
  having (weight*qty)>100)
);

69

نام شهرهای قطعاتی را بیابید که عرضه‌کننده‌ای با وضعیت بیشتر از ده یکی از قطعات درون آن شهرها را عرضه کرده باشد و مجموع عرضه‌های قطعه‌های آن شهرها بیشتر از ۲۰ باشد به شرطی که تعداد قطعات در آن شهر قطعه بیشتر از دو باشد.

select p.city -- incorrect
from p join sp using(pn) join s using(sn)
where s.status > 10
group by p.city
having sum(qty) > 20 and count(distinct pn) > 2;

select p.city -- incorrect
from p join sp using(pn)
where exists(
  select *
  from s
  where s.status > 10 and s.sn = sp.sn
)
group by p.city
having sum(qty) > 20 and count(distinct pn) > 2;

select p.city -- correct
from p left outer join sp using(pn)
where exists(
  select *
  from s
  where s.status > 10 and s.sn = sp.sn
)
group by p.city
having sum(qty) > 20 and count(distinct pn) > 2;

select p.city -- correct ?
from p left outer join sp using(pn)
  join s using(city)
group by p.city
having sum(qty) > 20 and
  count(distinct pn) > 2 and
  max(status > 10);

70

شمارهٔ قطعات با وزن بیشتر از ۱۲ را همراه با جمع عرضه‌های هر کدام بیابید که بیشتر از دو عرضه کننده آنها را عرضه کرده باشند.

select pn, sum(qty)
from sp natural join p
where p.weight > 12
group by pn
having count(sn)>2;

select pn, sum(qty)
from spj natural join p
where p.weight > 12
group by pn
having count(distinct sn)>2;

71

شمارهٔ قطعات با وزن بیشتر از ۱۲ را همراه با جمع عرضه‌های هر کدام بیابید.

select pn, sum(qty)
from sp natural join p
where p.weight > 12
group by pn

72

END

73