Database Course

Ahmad Yoosofan

SQL 2

University of Kashan

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

جمع عرضه‌های عرضه کنندهٔ 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

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

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   )
8 ;

swg

29

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

swg

46

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

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

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

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

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

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 درست است؟

راه حل دیگر

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

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

جمع وزنی قطعات عرضه‌شده به تعداد 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

Average

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

select avg(weight) as awg
from p
;

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

select avg(qty) as sqt
from   sp
;

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

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

awg

14.5

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

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

Count

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

select count(pn) as awg
from p
;

awg

8

select count(weight) as awg
from p
;

awg

6

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

count(distinct)

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

select count(distinct city) as ccy
from p
;

ccy

3

select city
from p
;

city

Oslo

London

Paris

London

London

Paris

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

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

sqt

4

select distinct sn
from sp;

sn

S1

S2

S3

S4

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

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

scc

3

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

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'
  )
;

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

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

Min

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

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

max

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

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

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

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

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

pn

sqt

p1

600

p2

1350

p3

400

p4

500

p5

500

p6

100

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

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

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

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
6 ;

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

pn

sqt

p2

1350

گام به گام

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

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

 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;

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

1 select city
2 from p
3 ;
1 select p.city as pcity
2 from p join sp using(pn)
3   join s using(sn)
4 where status > 10
5 ;
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
7 ;
 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 )
 9 ;

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

 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
11 ;

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

 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
10 ;

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 
 9 select city -- نادرست
10 from sp natural join p
11 group by pn
12 having status >10 and sum(qty) > 20
13 ;
 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
10 ;

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

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 ;

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
10 ;

نام پروژه‌هایی را بیابید که عرضه‌کننده‌ای با وضعیت بیشتر از ۲۰ نیز آنها را عرضه کرده باشند و جمع وزنی عرضه‌های هر کدام از آن پروژه‌ها بیشتر از ۱۰۰ باشد(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)
12 ;
 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 )
15 ;

نام پروژه‌هایی را بیابید که عرضه‌کننده‌ای با وضعیت بیشتر از ۲۰ نیز آنها را عرضه کرده باشند و جمع وزنی عرضه‌های هر کدام از آن پروژه‌ها بیشتر از ۱۰۰ باشد(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 );

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

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
6 ;
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
6 ;
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
6 ;
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
6 ;

نام شهرهای قطعاتی را بیابید که عرضه‌کننده‌ای با وضعیت بیشتر از ده ، دست کم یکی از قطعات درون آن شهرها را عرضه کرده باشد و مجموع عرضه‌های قطعه‌های آن شهرها بیشتر از ۲۰ باشد به شرطی که تعداد قطعات در آن شهر قطعه بیشتر از دو باشد(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
8 ;
 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
10 ;
 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
12 ;

نام شهرهای قطعاتی را بیابید که عرضه‌کننده‌ای با وضعیت بیشتر از ده ، دست کم یکی از قطعات درون آن شهرها را عرضه کرده باشد و مجموع عرضه‌های قطعه‌های آن شهرها بیشتر از ۲۰ باشد به شرطی که تعداد قطعات در آن شهر قطعه بیشتر از دو باشد(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   )
 9 group by p.city
10 having sum(qty) > 20 and
11   count(distinct pn) > 2
12 ;
 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
11     where p.city = p2.city and
12       p.pn <> p2.pn
13   )
14 group by p.city
15 having sum(qty) > 20
16 ;

نام شهرهای قطعاتی را بیابید که عرضه‌کننده‌ای با وضعیت بیشتر از ده ، دست کم یکی از قطعات درون آن شهرها را عرضه کرده باشد و مجموع عرضه‌های قطعه‌های آن شهرها بیشتر از ۲۰ باشد به شرطی که تعداد قطعات در آن شهر قطعه بیشتر از دو باشد(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
12 ;
 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
17 ;

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

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

 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)
13 ;

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

 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)
13 ;
 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   )
16 ;

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

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
6 ;
1 select pname, count(sn)
2 from p natural join sp
3 group by pn
4 -- having count(distinct pn) > 1
5 ;

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

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

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'
5 ;

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   )
8 ;

swg

29

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

 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   )
 9 ;

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   )
8 ;

swg

29

بحث اصلی

LIMIT

select distinct city
from p
order by weight, city
;

city

London

Oslo

Paris

select distinct city
from p
order by weight, city
limit 2
;

city

London

Oslo

Scalar value(I)

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

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

pn

weight

p2

17

p3

17

p6

19

Scalar value(II)

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

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

pn

weight

p1

12

p5

12

select pn, weight
from p  -- Wrong
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
);

Scalar value(V)

1 select pn, 1 as qt
2 from p
3 where city = 'Paris'
4 ;

pn

qt

P2

1

P5

1

P8

1

شمارهٔ همهٔ قطعات را همراه با جمع تعداد عرضه‌های (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

شمارهٔ همهٔ قطعات را همراه با جمع تعداد عرضه‌های (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

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

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

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

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

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

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;

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

 1 select p.city
 2 from p left outer 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
12 ;

Full Outer Join(I)

select pn, sum(qty) as sqty
from p natural full outer join sp
group by pn;
select pn, sum(qty) as sqty
from p full outer join sp using(pn)
group by pn;
select p.pn, sum(qty) as sqty
from p full outer join sp on p.pn = sp.pn
group by p.pn;
select distinct p.city, s.city
from p natural left outecd inr join s;

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

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

select s.city, p.city
from s left outer join p using(city);
select  p.city, s.city --wrong
from p full outer join s on p.city = s.city;
select  p.city, s.city --wrong
from p full outer join s using(city);
select  p.city, s.city --wrong
from p natural full outer join s;
select  p.city, s.city
from p natural right outer join s;
select  p.city, s.city --wrong
from p natural left outer join s;

نام همهٔ شهرهای عرضه کنندگان را در کنار نام شهر قطعاتی که همشهری آنها هستند بنویسید و اگر قطعه‌ای همشهری آن عرضه کننده نبود نام شهر عرضه کننده همراه با 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

کاشان

is null / is not null

select pname
from p
where weight is null;
select sname
from s
where status is not null;

Scalar value(III)

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

1 select pn, weight -- wrong
2 from p
3 order by weight asc
4 limit 1
5 ;

pn

weight

p7

1 select pn, weight -- wrong
2 from p
3 where weight is not null
4 order by weight asc
5 limit 1
6 ;

pn

weight

p1

12

Scalar value(IV)

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

1 select pn, weight -- wrong
2 from p
3 where weight is not null
4 order by weight asc
5 limit 1
6 ;

pn

weight

p1

12

 1 select pn, weight
 2 from p
 3 where weight = (
 4     select weight
 5     from p
 6     where weight is not null
 7     order by weight asc
 8     limit 1
 9   )
10 ;

pn

weight

p1

12

p5

12

Scalar value(IV)

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

 1 select pn, weight
 2 from p
 3 where weight = (
 4     select weight
 5     from p
 6     where weight is not null
 7     order by weight asc
 8     limit 1
 9   )
10 ;

pn

weight

p1

12

p5

12

1 select pn, weight
2 from p
3 where weight = (
4     select min(weight)
5     from p
6   )
7 ;

pn

weight

p1

12

p5

12

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

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

نام پروژه‌هایی را بیابید که عرضه‌کننده‌ای با وضعیت بیشتر از ۲۰ برای آن پروژه‌ها عرضه کرده باشد و مجموع وزن قطعات عرضه شده برای آن پروژه بیشتر از ۱۰۰ باشد. دقت کنید مجموع وزن قطعات باید 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 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 p.city
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
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 pn, sum(qty)
from sp natural join p
where p.weight > 12
group by pn
having count(sn)>2;

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

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

END

1