operation.sql

该代码是一个脚本

SELECT FOOD#
FROM INGREDIENTS
WHERE INGREDIENT#='BUTTER' AND QUANTITY>16;

SELECT DISTINCT CUISINE
FROM DISH
WHERE FOOD# IN(SELECT FOOD#
               FROM INGREDIENTS
               WHERE INGREDIENT#='CUMIN');

UPDATE INGREDIENTS
SET QUANTITY=16
WHERE INGREDIENT#='BUTTER' AND QUANTITY>16;

DELETE FROM INGREDIENTS
WHERE INGREDIENT#='BEEF'
AND FOOD# IN(SELECT FOOD#
             FROM DISH
             WHERE CUISINE='INDIAN');

CREATE TABLE PB06210445.SHOPPINGLIST
(CUISINE VARCHAR2(20) NOT NULL,
INGREDIENT# VARCHAR2(20) NOT NULL,
QUANTITY NUMBER(8),
CONSTRAINT FK_CALORIE FOREIGN KEY(INGREDIENT#)
REFERENCES PB06210445.CALORIES(INGREDIENT#));

INSERT INTO SHOPPINGLIST
SELECT CUISINE,INGREDIENT#,SUM(QUANTITY)
FROM DISH,INGREDIENTS
WHERE DISH.FOOD#=INGREDIENTS.FOOD#
GROUP BY CUISINE,INGREDIENT#;
SELECT * FROM SHOPPINGLIST ORDER BY CUISINE;

SELECT FOOD#,SUM(INGREDIENTS.QUANTITY*CALORIES.CALORIES/CALORIES.QUANTITY) AS CALORIES
FROM INGREDIENTS,CALORIES
WHERE INGREDIENTS.INGREDIENT#=CALORIES.INGREDIENT#
GROUP BY FOOD#;

COMMIT;