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

INSERT INTO worker VALUES(1,'w1');

INSERT INTO worker VALUES(2,'w2');

INSERT INTO worker VALUES(3,'w3');

INSERT INTO worker VALUES(4,'w4');

INSERT INTO worker VALUES(5,'w5');

INSERT INTO worker VALUES(6,'w6');

INSERT INTO worker VALUES(7,'w7');

INSERT INTO client VALUES(1,'Bob1','Sumy',0661283733);

INSERT INTO client VALUES(2,'Bob2','Sumy',0668373312);

INSERT INTO client VALUES(3,'Bob3','Sumy',0661283153);

INSERT INTO client VALUES(4,'Bob4','Sumy',0951283733);

INSERT INTO client VALUES(5,'Bob5','Sumy',0661123733);

INSERT INTO client VALUES(6,'Bob6','Sumy',0631283733);

INSERT INTO client VALUES(7,'Bob7','Sumy',0981283733);

INSERT INTO client VALUES(8,'Bob8','Sumy',0641283733);

INSERT INTO client VALUES(9,'Bob9','Sumy',0681283733);

 --sell(ID, productID,   workerID, clientID ,  sellDate) 

INSERT INTO sell VALUES( 1,1,1,1,1,TO_DATE('11-02-2011','DD-MM-YYYY'));

INSERT INTO sell VALUES( 2,2,4,6,2,TO_DATE('12-02-2011','DD-MM-YYYY'));

INSERT INTO sell VALUES( 3,4,4,7,3,TO_DATE('18-02-2011','DD-MM-YYYY'));

INSERT INTO sell VALUES( 4,6,4,9,4,TO_DATE('24-02-2011','DD-MM-YYYY'));

INSERT INTO sell VALUES( 5,5,2,8,5,TO_DATE('04-03-2011','DD-MM-YYYY'));

INSERT INTO sell VALUES( 6,5,2,5,6,TO_DATE('11-03-2011','DD-MM-YYYY'));

INSERT INTO sell VALUES( 7,7,7,1,7,TO_DATE('11-03-2011','DD-MM-YYYY'));

INSERT INTO sell VALUES( 8,3,3,8,8,TO_DATE('19-03-2011','DD-MM-YYYY'));

INSERT INTO sell VALUES( 9,9,3,8,7,TO_DATE('21-03-2011','DD-MM-YYYY'));

INSERT INTO sell VALUES(10,8,5,8,6,TO_DATE('26-03-2011','DD-MM-YYYY'));

INSERT INTO sell VALUES(11,6,5,6,5,TO_DATE('01-04-2011','DD-MM-YYYY'));

INSERT INTO sell VALUES(12,8,6,3,4,TO_DATE('15-04-2011','DD-MM-YYYY'));

INSERT INTO sell VALUES(13,1,5,2,3,TO_DATE('20-04-2011','DD-MM-YYYY'));

INSERT INTO sell VALUES(14,1,1,4,2,TO_DATE('25-04-2011','DD-MM-YYYY'));

INSERT INTO sell VALUES(15,3,7,8,1,TO_DATE('27-04-2011','DD-MM-YYYY'));

commit;

6 Создание выборок

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

SELECT clientName, productName

FROM Client, Product, Sell

WHERE Sell.productID = Product.ID AND

      Sell.clientID  = Client.ID  AND 

      MONTHS_BETWEEN(SYSDATE, sellDate) < 2;

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

SELECT workerName Name, COUNT(*) Sells

FROM Sell LEFT JOIN Worker

ON   Sell.workerID = Worker.ID

WHERE MONTHS_BETWEEN(SYSDATE, sellDate) < 2 AND

                MONTHS_BETWEEN(SYSDATE, sellDate) > 0

GROUP BY workerName

HAVING COUNT(*) IN (  SELECT MAX(COUNT(*))

                                             FROM Sell

                                             WHERE MONTHS_BETWEEN(SYSDATE, sellDate) < 2 AND

                                                            MONTHS_BETWEEN(SYSDATE, sellDate) > 0

                                             GROUP BY workerID

 );

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

SELECT SUM(sellQuantity * price) Total_Salle

FROM Sell LEFT JOIN Product

ON   Sell.productID = Product.ID

WHERE MONTHS_BETWEEN(SYSDATE, sellDate) > 1;

7 МетаМОДЕЛЬ

Мета-модель (мета- обозначает находящийся вне, за пределами, сверх) — это модель, которая описывает структуру, принципы действия другой модели.

7.1 Скрипты метамодели

drop table params;

drop table attributes;

drop table objects;

drop table object_types;

CREATE TABLE object_types (

     object_type_id NUMBER(20,0) NOT NULL ,

     name VARCHAR2(100),

     description VARCHAR2(1000),

     CONSTRAINT object_types_pk PRIMARY KEY (object_type_id)

);

CREATE TABLE objects (

     object_id NUMBER(20,0) NOT NULL ,

     parent_id NUMBER(20,0),

     object_type_id NUMBER(20,0) NOT NULL ,

     name VARCHAR2(100),

     CONSTRAINT objects_pk PRIMARY KEY (object_id),

     CONSTRAINT objects_object_type_fk FOREIGN KEY (object_type_id)

     REFERENCES object_types (object_type_id)  ON DELETE CASCADE,

     CONSTRAINT objects_objects_fk FOREIGN KEY (parent_id)