Please write the following SQL quaries: 1a. Suppose that the CATEGORY and SUPPLI
ID: 3891089 • Letter: P
Question
Please write the following SQL quaries:
1a. Suppose that the CATEGORY and SUPPLIER tables have already been created. Write a single command to create the BEVERAGE table.
NOTE:
Include all necessary PK and FK constraints
make sure that null values are NOT allowed in the SupplierID column.
Whenever possible, all constraints should be named and defined at the table level.
1b. Insert the first record (B01) into the BEVERAGE table.
1c. Add a new column, SupplierZip, to the SUPPLIER table. All zip codes will have 5 digits (e.g., 99999).
1d. Update the cost of 'WHITE WINE' to $9.99.
CATEGORY
CategoryID
CategoryName
C01
BEER
C02
WINE
C03
MIXED DRINKS
…
SUPPLIER
SupplierID
SupplierName
SupplierAddress
SupplierCity
SupplierState
S01
BIG BEER CO.
123 MAIN STREET
ST. LOUIS
MO
S02
CRAFT BEER INC.
456 BROADWAY
ROCHESTER
NY
S03
UNITED BEVERAGES
789 BEAUFORT ST
NORMAL
IL
…
BEVERAGE
BevID
BevName
Cost
Qty
CategoryID
SupplierID
B01
KILLIAN’S
0.87
12
C01
S01
B02
COORS
0.59
40
C01
S01
B03
OLD STYLE
0.43
35
C01
S02
B04
ANCHOR STEAM
0.89
27
C01
S02
B05
MILLER LIGHT
0.45
70
C01
S03
B06
DRY WINE
12.35
10
C02
S03
B07
WHITE WINE
8.87
8
C02
S04
B08
RED WINE
7.15
3
C02
S04
B09
WINE COOLER
10.02
16
C03
S05
B10
…
…
…
…
…
CategoryID
CategoryName
C01
BEER
C02
WINE
C03
MIXED DRINKS
…
Explanation / Answer
If you have any doubts, please give me comment...
-- 1a)
CREATE TABLE BEVERAGE(
BevID CHAR(3) NOT NULL PRIMARY KEY,
BevName VARCHAR(100),
Cost REAL(10,2),
Qty INTEGER,
CategoryID CHAR(3),
SupplierID CHAR(3) NOT NULL,
FOREIGN KEY(CategoryID) REFERENCES CATEGORY(CategoryID),
FOREIGN KEY(SupplierID) REFERENCES SUPPLIER(SupplierID)
);
-- 1b)
INSERT INTO BEVARAGE VALUES('B01', 'KILLIANS', 0.87, 12, 'C01', 'S01');
--1c)
ALTER TABLE SUPPLIER ADD SupplierZip INT(5);
--1d)
UPDATE BEVERAGE SET Cost=9.99 WHERE BevName='WHITE WINE';