SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE IF NOT EXISTS ITEM ( ID INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY, IDorders INTEGER, IDstock INTEGER, Quantity INTEGER, Total INTEGER ); INSERT INTO ITEM (IDorders, IDstock, Quantity, Total) VALUES (4, 2, 150, 3324), (2, 3, 165, 138992), (6, 5, 270, 87287), (3, 1, 58, 44653), (2, 4, 76, 1234), (2, 6, 151, 435346), (6, 4, 242, 246546); -- CUSTOMER(ID, CompanyName, LastName, FirstName, Address, City, Phone, Fax); -- ORDERS(ID, IDcustomer, OrdersDate, ShipDate, PaidDate, Status); -- ITEM(ID, IDorders, IDstock, Quantity, Total); -- STOCK(ID, UnitPrice, OnHand, Reoder, Description). -- Создать представление ORDERS_ITEM со схемой ORDERS_ITEM -- (IDorders, ID.item), выполнив группировку по -- номеру заказа и сортировку по номеру пункта заказа. DROP VIEW IF EXISTS ORDERS_ITEM; CREATE VIEW ORDERS_ITEM AS SELECT IDorders, ID, AVG(Quantity) OVER ( PARTITION BY IDorders ) FROM ITEM ORDER BY (IDorders, ID); SELECT * FROM ORDERS_ITEM;