Разработка базы данных из предметной области магазин, страница 7

INSERT INTO params (object_id, attr_id, date_value) VALUES (1000014, 8, TO_DATE('25-04-2011','DD-MM-YYYY'));

INSERT INTO params (object_id, attr_id, date_value) VALUES (1000015, 8, TO_DATE('27-04-2011','DD-MM-YYYY'));

commit;

7.3 Sql запросы для метамодели.

1). Какие товары  и какими клиентами были куплены за 2 месяца.

SELECT Client.name, product.Name

  FROM (

             (

                      objects Product JOIN objects Sell

                      ON Sell.parent_id = Product.object_id

                      AND Product.object_type_id = 3

                      AND Sell.object_type_id = 4

                 )

                 JOIN

                 (

                      objects Client JOIN params ClientID

                      ON ClientID.number_value = Client.object_id

                      AND Client.object_type_id = 2

                      AND ClientID.attr_id = 6

                 )

                 ON ClientID.object_id = Sell.object_id

           )

           JOIN

           (

                 params SellDate

           )

           ON SellDate.object_id = Sell.object_id

           AND SellDate.attr_id = 8

      WHERE MONTHS_BETWEEN(SYSDATE, sellDate.date_value) < 2;  

2). Какой работник совершил наибольшее количество сделок продажи за прошлый месяц.

SELECT Worker.Name, COUNT(*)

FROM

      (

           (

                 objects Client JOIN params ClientID

                 ON ClientID.number_value = Client.object_id

                 AND Client.object_type_id = 2

                 AND ClientID.attr_id = 6  

           )

           JOIN

           (

                 objects Worker JOIN params WorkerID

                 ON WorkerID.number_value = Worker.object_id

                 AND Worker.object_type_id = 1

                 AND WorkerID.attr_id = 5

           )

           ON WorkerID.object_id = ClientID.object_id

      )

      JOIN

      (

           params SellDate

      )

      ON SellDate.object_id = WorkerID.object_id

      AND SellDate.attr_id = 8

WHERE MONTHS_BETWEEN(SYSDATE, sellDate.date_value) < 2 AND

        MONTHS_BETWEEN(SYSDATE, sellDate.date_value) > 1

GROUP BY Worker.Name

HAVING COUNT(*) IN

      (

           SELECT MAX(COUNT(*))

           FROM

                 (

                      (

                            objects Client JOIN params ClientID

                            ON ClientID.number_value = Client.object_id

                            AND Client.object_type_id = 2

                            AND ClientID.attr_id = 6  

                      )

                      JOIN

                      (

                            objects Worker JOIN params WorkerID

                            ON WorkerID.number_value = Worker.object_id

                            AND Worker.object_type_id = 1

                            AND WorkerID.attr_id = 5

                      )

                      ON WorkerID.object_id = ClientID.object_id

                 )

                 JOIN

                 (

                      params SellDate

                 )

                 ON SellDate.object_id = WorkerID.object_id

                 AND SellDate.attr_id = 8

           WHERE MONTHS_BETWEEN(SYSDATE, sellDate.date_value) < 2 AND

                   MONTHS_BETWEEN(SYSDATE, sellDate.date_value) > 1

           GROUP BY Worker.Name

      );

 

3). На какую сумму было продано товара до начала этого месяца.

SELECT SUM(SellQuantity.number_value * Price.number_value)

FROM

      (

           (

                 (

                      objects Product JOIN objects Sell

                      ON Sell.parent_id = Product.object_id

                      AND Product.object_type_id = 3

                      AND Sell.object_type_id = 4

                 )

                 JOIN

                 (

                      params SellDate

                 )

                 ON SellDate.object_id = Sell.object_id