Please have these queries in microsoft SQL language Name of suppliers offering a
ID: 3670959 • Letter: P
Question
Please have these queries in microsoft SQL language
Name of suppliers offering all products? SELECT Name FROM Tb_Supplier S WHERE NOT EXISTS(SELECT* FROM Tb Product P WHERE NOT EXISTS (SELECT* FROM Tb_Offers WHERE S.Supp ID-Tb_Offers.Supp_ID Name of consumerving at least one supplier in each supplier city? SELECT Name FROM Tb Consumer C WHERE NOT EXISTS ( SELECT FROM Tb_Supplier S WHERE NOT EXISTS ( SELECT * FROM Tb_Transactions T, Tb_Supplier WHERE T. Supp-ID = Tb-Supplier. Supp-ID AND T.Con ID=C.Con ID AND City- S.City))Explanation / Answer
1)
SELECT S.NAME, C.NAME, P.NAME, T.Quantity, T.Price
FROM SUPPLIER S, CONSUMER C, PRODUCT P, TRANSACTION T
WHERE S.SUPP_ID = T.SUPP_ID AND C.CON_ID = T.CON_ID AND P.PROD_ID = T.PROD_ID
AND C.CITY='Stevens Point' AND T.PRICE > 10000;
2)
SELECT S.NAME FROM SUPPLIER S
WHERE S.SUPP_ID IN(
SELECT O.SUPP_ID FROM OFFERS O
WHERE O.PROD_ID IN(
SELECT P.PROD_ID FROM PRODUCT
WHERE P.NAME='computers' AND P.NAME='oranges';
3)
SELECT S.NAME
FROM SUPPLIER S, PRODUCT P, OFFERS O
WHERE S.SUPP_ID = O.SUPP_ID AND P.PROD_ID = O.PROD_ID
AND S.CITY= 'WAUSAU' OR P.NAME = 'computers' OR P.NAME = 'oranges'
4)
SELECT S.NAME FROM SUPPLIER S, PRODUCT P, OFFERS O
WHERE S.SUPP_ID = O.SUPP_ID AND P.PROD_ID = O.PROD_ID
AND P.NAME = 'computers' AND P.NAME = 'auto' AND P.NAME = 'oranges';
5)
SELECT P.NAME
FROM PRODUCT P
WHERE CITY <> 'CHICAGO';
6)
SELECT C.NAME
FROM CONSUMER C, PRODUCT P, REQUESTS R
WHERE C.CON_ID = R.CON_ID AND P.PROD_ID = R.PROD_ID AND P.NAME = 'computers';
7)
SELECT P.NAME FROM SUPPLIER S, PRODUCT P, OFFERS O
WHERE S.SUPP_ID = O.SUPP_ID AND P.PROD_ID = O.PROD_ID;