Индексы, ограничения и язык запросов в PostgreSQL, страница 4

shop=# INSERT INTO products (name,kolvo,priceProd,idMaster) VALUES ('Bread white',500,21,1);

INSERT 0 1

shop=# select * from products;

 id |         name         | kolvo | priceprod | idmaster

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

  1 | Bread                |   500 |      25.5 |        1

  2 | Boots                |   100 |      1000 |        2

  3 | Nektar               |    50 |       100 |        3

  4 | Cake                 |    25 |       400 |        4

  5 | Ice-Cream            |   500 |        40 |        4

  7 | Bread white          |   500 |        21 |        1

(6 rows)

shop=# INSERT INTO products (name,kolvo,priceProd,idMaster) VALUES ('Bread white',500,2,1);

ERROR:  new row for relation "products" violates check constraint "mega_pr_lower"

shop=# INSERT INTO products (name,kolvo,priceProd,idMaster) VALUES ('Bread white',500,2000,1);

ERROR:  new row for relation "products" violates check constraint "mega_pr_up"

·  Найти все товары, прибыль от продажи которых более 10%.

shop=# select * from temp where (price-coast)>price*0.1;

name  | name_master | coast | kolvozakup |  qwe1  | price | kolvoprod | qwe2

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

 Boots  | Nike      |  1000 |        100 | 100000 |  1300 |         5 | 6500

 Nektar | BB        |   100 |         50 |   5000 |   120 |       500 | 60000

 Cake   | 5-rochka  |   400 |         25 |  10000 |   500 |        30 | 15000

(3 rows)

·  Вывести полную информацию о проданных товарах заданного наименования.

shop=# select t1.name,t2.name_master,t1.priceProd as coast,t1.kolvo as kolvoZakup,(t1.priceProd*t1.kolvo) as coastZakup,t3.priceSale,t3.kolvo as kolvoProd, (t3.priceSale*t3.kolvo) as coastProdaj FROM products AS t1, masters AS t2, sale AS t3 where t1.idMaster = t2.id and t1.id = t3.idProd;

      name         |     name_master      | coast | kolvozakup | coastzakup |

 pricesale | kolvoprod | coastprodaj

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

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

 Bread                | Voshod               |  25.5 |        500 |   12750 |

        28 |        10 |         280

 Boots                | Nike                 |  1000 |        100 |  100000 |

      1300 |         5 |        6500

 Nektar               | BB                   |   100 |         50 |    5000 |

       120 |       500 |       60000

 Cake                 | 5-rochka             |   400 |         25 |   10000 |

       500 |        30 |       15000

 Ice-Cream            | 5-rochka             |    40 |        500 |   20000 |

        41 |       450 |       18450

(5 rows)

·  Найти все товары заданного поставщика, количество которых на складе более 25 кг.

shop=# select t1.name,t2.name_master,t1.priceProd as coast,t1.kolvo as kolvoZakup,(t1.priceProd*t1.kolvo) as coastZakup,t3.priceSale,t3.kolvo as kolvoProd, (t3.priceSale*t3.kolvo) as coastProdaj FROM products AS t1, masters AS t2, sale AS t3 where t1.idMaster = t2.id and t1.id = t3.idProd and t2.name_master='Voshod' and (t1.kolvo-t3.kolvo)>25;

      name         |     name_master      | coast | kolvozakup | coastzakup |

 pricesale | kolvoprod | coastprodaj

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

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

 Bread                | Voshod               |  25.5 |     500 |      12750 |

        28 |        10 |         280

(1 row)

Выводы

В ходе лабораторной работы были рассмотрены различные типы индексов в PostgreSQL и их влияния на скорость поиска информации в базе данных. Была освоена команда EXPLAIN. Научились использовать ограничения.