Функции в PostgreSQL. Проектирование структуры БД гарантийного ремонта, страница 3

 2008-06-22 |     3 |  2

 2008-06-15 |     1 |  3

 2008-10-31 |     2 |  4

 2008-11-01 |     3 |  5

(10 rows)

Возраст мастеров не должен быть менее 18 лет.

remont=> alter table zakazchik add constraint birthdate check (extract(year from now())-extract(year from birthdate)>=18);

ALTER TABLE

remont=> insert into zakazchik values('Ivancev', 'ilya', '1996-02-03', 'gorod ylica fonar', 4);

ERROR:  new row for relation "zakazchik" violates check constraint "birthdate" STATEMENT:  insert into zakazchik values('Ivancev', 'ilya', '1996-02-03', 'gorod ylica fonar', 4);

ERROR:  new row for relation "zakazchik" violates check constraint "birthdate"

remont=> select * from zakazchik;

   fam    | name | birthdate  |       adress       | id

----------+------+------------+--------------------+----

 Ivanov   | Ivan | 1976-04-12 | Marksa 1, 15       |  1

 Abalov   | Nick | 1986-06-27 | Bogatkova 51, 1    |  2

 Gluxova  | Anna | 1989-05-28 | Visotskogo 111, 45 |  3

 Pac      | Nick | 1970-07-29 | Lenina 41, 5       |  4

 Belousov | Mixa | 1990-05-27 | Truda 61, 123      |  5

 (5 rows)

Вывести информацию обо всех мастерах в возрасте старше 50 лет, заработавших за последний день более 700 р.

select  Z.name,Z.fam,Z.adress,age(Z.birthdate),sumT.Ssum

from

      (select  sum(T.stoimost) as Ssum, T.id

      from (

            select t3.id, t3.stoimost

            from zakaz as t1, zakazchik as t2, predmet as t3

            where (extract (year from age(t2.birthdate))>50)

            and (t1.data_z = (select max(data_z) from zakaz))

            and(t1.id=t2.id)and(t1.id=t3.id)) as T

      group by T.id) as sumT,

      zakazchik as Z

where (sumT.id=Z.id)and(sumT.Ssum>700);

name  |    fam    | adress |       age        | ssum

------+-----------+--------+------------------+-------

 Igor | Ivanovich | perm   | 53 years 21 days | 24502

(1 row)

 Найти всех мастеров в возрасте до 30 лет, которые занимаются ремонтом чайников.

select distinct t1.name,t1.fam,t1.adress,age(t1.birthdate)

from zakazchik as t1, predmet as t2

where (extract (year from age(t1.birthdate))<30)

and (t2.vid = 'chainik')and(t1.id=t2.id);

name  |   fam   |       adress       |           age

------+---------+--------------------+-------------------------

 Anna | Gluxova | Visotskogo 111, 45 | 20 years 6 mons 27 days

(1 row)

Найти всех мастеров, чья работа стоит более 500 р.

select distinct t1.name,t1.fam,t1.adress,age(t1.birthdate)

from zakazchik as t1, predmet as t2

where (extract (year from age(t1.birthdate))<30)

and (t2.stoimost > 500)and(t1.id=t2.id);

name  |   fam    |       adress       |           age

------+----------+--------------------+-------------------------

 Anna | Gluxova  | Visotskogo 111, 45 | 20 years 6 mons 27 days

 Mixa | Belousov | Truda 61, 123      | 19 years 6 mons 28 days

 Nick | Abalov   | Bogatkova 51, 1    | 23 years 5 mons 27 days

(3 rows)

Выводы