Task 1:
Task 3:
CREATE TABLE WTL_Stock (
WTL_Product_Id VARCHAR(6) NOT NULL,
WTL_Depot_Id VARCHAR(6) NOT NULL,
WTL_Quantity Number(4) DEFAULT 0,
Aisle VARCHAR(3) NOT NULL,
Bay NUMBER(3) NOT NULL,
CONSTRAINT fk_stock_product
FOREIGN KEY (WTL_Product_Id) REFERENCES WTL_Product(WTL_Product_Id)
);
INSERT INTO WTL_STOCK VALUES ('AB2101', 'DEP001', 10, 'A1', 3);
INSERT INTO WTL_STOCK VALUES ('AB2102', 'DEP002', 15, 'A2', 3);
INSERT INTO WTL_STOCK VALUES ('AB2103', 'DEP003', 20, 'A3', 3);
INSERT INTO WTL_STOCK VALUES ('AB2104', 'DEP004', 11, 'A4', 3);
INSERT INTO WTL_STOCK VALUES ('AB2105', 'DEP005', 22, 'A5', 3);
INSERT INTO WTL_STOCK VALUES ('AB2106', 'DEP006', 19, 'A6', 3);
INSERT INTO WTL_STOCK VALUES ('AB2107', 'DEP007', 100, 'A7', 3);
INSERT INTO WTL_STOCK (WTL_PRODUCT_ID, WTL_DEPOT_ID, AISLE, BAY) VALUES ('AB2107', 'DEP001', 'B1', 1);
INSERT INTO WTL_STOCK (WTL_PRODUCT_ID, WTL_DEPOT_ID, AISLE, BAY) VALUES ('AB2106', 'DEP002', 'B2', 2);
INSERT INTO WTL_STOCK (WTL_PRODUCT_ID, WTL_DEPOT_ID, AISLE, BAY) VALUES ('AB2105', 'DEP003', 'B3', 3);
INSERT INTO WTL_STOCK (WTL_PRODUCT_ID, WTL_DEPOT_ID, AISLE, BAY) VALUES ('AB2104', 'DEP007', 'B4', 4);
INSERT INTO WTL_STOCK (WTL_PRODUCT_ID, WTL_DEPOT_ID, AISLE, BAY) VALUES ('AB2103', 'DEP005', 'B5', 5);
DESCRIBE WTL_STOCK;
SELECT * FROM WTL_STOCK;
Task 4:
/*1*/ SELECT COUNT(*), TRUNC(AVG(WTL_PRICE),2), MAX(WTL_PRICE), MIN(WTL_PRICE)
FROM WTL_PRODUCT;
/*2*/ SELECT WTL_SUPPLIER_ID, WTL_ADDRESS, WTL_NAME
FROM WTL_SUPPLIER
WHERE WTL_NAME LIKE '%M'
ORDER BY WTL_ADDRESS ASC;
/*3*/ SELECT WP.WTL_PRODUCT_ID, WD.WTL_LOCATION
FROM WTL_PRODUCT WP
JOIN WTL_STOCK WS
ON WP.WTL_PRODUCT_ID = WS.WTL_PRODUCT_ID
JOIN WTL_DEPOT WD
ON WS.WTL_DEPOT_ID = WD.WTL_DEPOT_ID
WHERE WP.WTL_PRODUCT_ID IN ('AB2101', 'AB2102', 'AB2103');
/*4*/ SELECT WTL_SUPPLIER_ID, WTL_NAME
FROM WTL_SUPPLIER
WHERE WTL_EMAIL IS NULL
AND WTL_ADDRESS IN ('Cambridge', 'Glasgow', 'London');
/*5*/ SELECT WP.WTL_PRODUCT_ID, WP.WTL_DESCRIPTION
FROM WTL_PRODUCT WP
JOIN WTL_STOCK WS
ON WP.WTL_PRODUCT_ID = WS.WTL_PRODUCT_ID
WHERE WP.WTL_PRODUCT_ID LIKE 'B%'
AND WS.WTL_QUANTITY BETWEEN 300 AND 500;
/*6*/ SELECT WS.WTL_SUPPLIER_ID, WS.WTL_NAME
FROM WTL_SUPPLIER WS
LEFT JOIN WTL_PRODUCT WP
ON WS.WTL_SUPPLIER_ID = WP.WTL_SUPPLIER_ID
WHERE WP.WTL_SUPPLIER_ID IS NULL;
/*7*/ SELECT WP.WTL_PRODUCT_ID, WP.WTL_PRICE, SUM(WST.WTL_QUANTITY), WS.WTL_NAME
FROM WTL_PRODUCT WP
JOIN WTL_STOCK WST
ON WP.WTL_PRODUCT_ID = WST.WTL_PRODUCT_ID
JOIN WTL_SUPPLIER WS
ON WP.WTL_SUPPLIER_ID = WS.WTL_SUPPLIER_ID
GROUP BY WP.WTL_PRODUCT_ID, WP.WTL_PRICE, WS.WTL_NAME;
/*8*/ SELECT WS.WTL_SUPPLIER_ID, WS.WTL_NAME, WS.WTL_ADDRESS, WS.WTL_EMAIL,
SUM(WST.WTL_QUANTITY) AS ProductCount
FROM WTL_SUPPLIER WS
JOIN WTL_PRODUCT WP
ON WS.WTL_SUPPLIER_ID = WP.WTL_SUPPLIER_ID
JOIN WTL_STOCK WST
ON WP.WTL_PRODUCT_ID = WST.WTL_PRODUCT_ID
GROUP BY WS.WTL_SUPPLIER_ID, WS.WTL_NAME, WS.WTL_ADDRESS, WS.WTL_EMAIL
ORDER BY ProductCount DESC;
/*9*/ SELECT WP.WTL_PRODUCT_ID, WP.WTL_DESCRIPTION, WS.WTL_SUPPLIER_ID, WS.WTL_NAME
FROM WTL_PRODUCT WP
JOIN WTL_SUPPLIER WS
ON WP.WTL_SUPPLIER_ID = WS.WTL_SUPPLIER_ID
LEFT JOIN WTL_STOCK WST
ON WP.WTL_PRODUCT_ID = WST.WTL_PRODUCT_ID
WHERE WST.WTL_QUANTITY IS NULL;
/*10*/ SELECT TRUNC(AVG(TS.STACKS),5) AS AVERAGE_STACK
FROM (
SELECT COUNT(*) AS STACKS
FROM WTL_STOCK
GROUP BY WTL_DEPOT_ID, WTL_AISLE, WTL_BAY) TS;