Please write the following SQL quaries: 2a. Display the names (BevName) and cate
ID: 3891090 • Letter: P
Question
Please write the following SQL quaries:
2a.
Display the names (BevName) and category names (CategoryName) of all beverages from IL and NY. Sort results by state and within the state by SupplierID.
Write this query without the JOIN keyword.
2b.
Display all beverages with the word “WINE” in their names (BevName) that are from NY and cost over $12.
NOTE:
Include BevName and Cost in your results
make sure that the cost figure for each beverage is preceded by a dollar sign ($)
sort results by cost in descending order
2c.
Show how many different *types* of beverages that CRAFT BEER INC. can provide.
Write this query with the JOIN keyword.
2d.
List the number of suppliers from each state. Only include states with 2 or more suppliers.
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
…
…
…
…
…
Maximum number of characters (including HTML tags added by text editor): 32,000Explanation / Answer
If you have any doubts, please give me comment...
-- 2a)
SELECT BevName, CategoryName
FROM BEVERAGE B, CATEGORY C, SUPPLIER S
WHERE B.CategoryID = C.CategoryID AND B.SupplierID = S.SupplierID AND S.SupplierState = 'IL' AND BevID IN(
SELECT BevID
FROM BEVERAGE B1, SUPPLIER S1
WHERE B1.SupplierID = S1.SupplierID AND S1.SupplierState = 'NY')
ORDER BY SupplierState, SupplierID;
--2b)
SELECT B.BevName, CONCAT('$',Cost) AS Cost
FROM BEVERAGE B, Supplier S
WHERE B.SupplierID = S.SupplierID AND B.BevName LIKE '%WINE%' AND S.SupplierState = 'NY' AND Cost>12
ORDER BY Cost DESC;
--2c)
SELECT COUNT(*)
FROM SUPPLIER S, BEVERAGE B
WHERE S.SupplierID = B.SupplierID AND S.SupplierName='CRAFT BEER INC.';
--2d)
SELECT SupplierState, COUNT(*) AS no_of_suppliers
FROM SUPPLIER
GROUP BY SupplierState
HAVING COUNT(*)>1;