AskSia
Plus
1. ALTER TABLE PRODUCT
ADD STOCK_QUANTITY NUMBER
2. SELECT P.PRODUCT_NAME
F...
Jun 5, 2024
1. ALTER TABLE PRODUCT
ADD STOCK_QUANTITY NUMBER
2. SELECT P.PRODUCT_NAME
FROM PRODUCT P
JOIN SUPPLY S ON P.PRODUCT_ID=S.PRODUCT_ID
WHERE S.SUPPLIER_ID='S_04';
3. SELECT PRODUCT_NAME
FROM PRODUCT
WHERE PRODUCT_ID IN
(SELECT PRODUCT_ID
FROM SUPPLY
WHERE SUPPLIER_ID = 'S_04');
4. SELECT SUPPLIER_NAME AS "SUPPLIER NAME"
FROM SUPPLIER
WHERE SUPPLIER_NAME LIKE 'S%';
5. SELECT COUNT(SUPPLIER_ID) AS "NUMBER OF SUPPLIERS FROM SELANGOR"
FROM SUPPLIER
WHERE SUPPLIER_LOCATION='SELANGOR';
6. SELECT COUNT(PRODUCT_ID) AS "NUMBER OF PRODUCT SUPPLY IN JAN"
FROM SUPPLY
WHERE SUPPLY_DATE BETWEEN '1/1/2024' AND '1/31/2024';
7. SELECT SUPPLIER_NAME, COUNT(SUPPLIER_ID) AS "TOTAL SUPPLIED"
FROM SUPPLIER
JOIN SUPPLY
USING (SUPPLIER_ID)
HAVING COUNT(SUPPLIER_ID)>2
GROUP BY SUPPLIER_NAME;
8. SELECT S.SUPPLY_DATE AS "DATE",
SP.SUPPLIER_LOCATION AS "LOCATION"
FROM PRODUCT P
NATURAL JOIN SUPPLIER SP
NATURAL JOIN SUPPLY S
WHERE P.PRODUCT_NAME='DURIAN';
9. SELECT PRODUCT_TYPE AS "PRODUCT",
SUM(PRICE) AS "TOTAL PRICE"
FROM PRODUCT
GROUP BY PRODUCT_TYPE;
10. SELECT PRODUCT_TYPE AS "PRODUCT",
MIN(STOCK_QUANTITY) AS "MINIMUM QUANTITY"
FROM PRODUCT
GROUP BY PRODUCT_TYPE;
11. SELECT P.PRODUCT_NAME AS "PRODUCT NAME",
P.PRICE
FROM PRODUCT P
JOIN SUPPLY S ON P.PRODUCT_ID=S.PRODUCT_ID
JOIN SUPPLIER SP ON S.SUPPLIER_ID=SP.SUPPLIER_ID
WHERE SP.SUPPLIER_LOCATION='SELANGOR';
12. SELECT AVG(STOCK_QUANTITY) AS "AVERAGE QUANTITY"
FROM PRODUCT;
13. SELECT P.PRODUCT_ID AS "PRODUCT ID",
P.PRODUCT_NAME AS "PRODUCT NAME"
FROM PRODUCT P
WHERE P.PRODUCT_ID
NOT IN (SELECT S.PRODUCT_ID FROM SUPPLY S);
14. SELECT SP.SUPPLIER_NAME AS "NAME",
COUNT(S.SUPPLIER_ID) AS "NUMBER OF TIMES SUPPLIED"
FROM SUPPLIER SP
JOIN SUPPLY S ON SP.SUPPLIER_ID=S.SUPPLIER_ID
WHERE SP.SUPPLIER_LOCATION='JOHOR' AND
S.SUPPLY_DATE BETWEEN '1/1/2023' AND '12/31/2023'
GROUP BY SP.SUPPLIER_NAME;
15. SELECT PRODUCT_ID, PRODUCT_NAME, PRODUCT_TYPE, PRICE, STOCK_QUANTITY,
STOCK_QUANTITY -
(SELECT STOCK_QUANTITY FROM PRODUCT WHERE PRODUCT_NAME = 'ORANGE')
AS "STOCK DIFFERENCE"
FROM PRODUCT
WHERE STOCK_QUANTITY >
(SELECT STOCK_QUANTITY FROM PRODUCT WHERE PRODUCT_NAME = 'ORANGE');
16. SELECT SP.SUPPLIER_NAME,
SUM(P.STOCK_QUANTITY) AS "QUANTITY",
COUNT(SP.SUPPLIER_ID) AS "NUMBER OF PRODUCT"
FROM SUPPLIER SP
JOIN SUPPLY S ON SP.SUPPLIER_ID=S.SUPPLIER_ID
JOIN PRODUCT P ON S.PRODUCT_ID=P.PRODUCT_ID
WHERE P.STOCK_QUANTITY >20
GROUP BY SP.SUPPLIER_NAME
HAVING COUNT(SP.SUPPLIER_ID) > 1;
List the Relational Algebra (RA) for all listed SQL statement.
Here are the Relational Algebra (RA) expressions for each of the SQL statements you provided:
ALTER TABLE PRODUCT ADD STOCK_QUANTITY NUMBER
This is a DDL (Data Definition Language) statement and does not have a direct equivalent in Relational Algebra.
πP.PRODUCT_NAME(σS.SUPPLIER_ID=′S_04′(PRODUCT⋈SUPPLY))
πPRODUCT_NAME(σPRODUCT_ID∈(πPRODUCT_ID(σSUPPLIER_ID=′S_04′(SUPPLY)))(PRODUCT))
πSUPPLIER_NAME(σSUPPLIER_NAME LIKE ′S%′(SUPPLIER))
\gamma_{COUNT(SUPPLIER\_ID) \rightarrow "NUMBER OF SUPPLIERS FROM SELANGOR"} (\sigma_{SUPPLIER\_LOCATION='SELANGOR'} (SUPPLIER))
\gamma_{COUNT(PRODUCT\_ID) \rightarrow "NUMBER OF PRODUCT SUPPLY IN JAN"} (\sigma_{SUPPLY\_DATE \geq '1/1/2024' \land SUPPLY\_DATE \leq '1/31/2024'} (SUPPLY))
\gamma_{SUPPLIER\_NAME, COUNT(SUPPLIER\_ID) \rightarrow "TOTAL SUPPLIED"} (\sigma_{COUNT(SUPPLIER\_ID) > 2} (SUPPLIER \bowtie SUPPLY))
\pi_{S.SUPPLY\_DATE \rightarrow "DATE", SP.SUPPLIER\_LOCATION \rightarrow "LOCATION"} (\sigma_{P.PRODUCT\_NAME='DURIAN'} (PRODUCT \bowtie SUPPLIER \bowtie SUPPLY))
\gamma_{PRODUCT\_TYPE \rightarrow "PRODUCT", SUM(PRICE) \rightarrow "TOTAL PRICE"} (PRODUCT)
\gamma_{PRODUCT\_TYPE \rightarrow "PRODUCT", MIN(STOCK\_QUANTITY) \rightarrow "MINIMUM QUANTITY"} (PRODUCT)
\pi_{P.PRODUCT\_NAME \rightarrow "PRODUCT NAME", P.PRICE} (\sigma_{SP.SUPPLIER\_LOCATION='SELANGOR'} (PRODUCT \bowtie SUPPLY \bowtie SUPPLIER))
\gamma_{AVG(STOCK\_QUANTITY) \rightarrow "AVERAGE QUANTITY"} (PRODUCT)
\pi_{P.PRODUCT\_ID \rightarrow "PRODUCT ID", P.PRODUCT\_NAME \rightarrow "PRODUCT NAME"} (\sigma_{P.PRODUCT\_ID \notin (\pi_{S.PRODUCT\_ID} (SUPPLY))} (PRODUCT))
\gamma_{SP.SUPPLIER\_NAME \rightarrow "NAME", COUNT(S.SUPPLIER\_ID) \rightarrow "NUMBER OF TIMES SUPPLIED"} (\sigma_{SP.SUPPLIER\_LOCATION='JOHOR' \land S.SUPPLY\_DATE \geq '1/1/2023' \land S.SUPPLY\_DATE \leq '12/31/2023'} (SUPPLIER \bowtie SUPPLY))
\pi_{PRODUCT\_ID, PRODUCT\_NAME, PRODUCT\_TYPE, PRICE, STOCK\_QUANTITY, (STOCK\_QUANTITY - (\pi_{STOCK\_QUANTITY} (\sigma_{PRODUCT\_NAME='ORANGE'} (PRODUCT)))) \rightarrow "STOCK DIFFERENCE"} (\sigma_{STOCK\_QUANTITY > (\pi_{STOCK\_QUANTITY} (\sigma_{PRODUCT\_NAME='ORANGE'} (PRODUCT)))} (PRODUCT))
\gamma_{SP.SUPPLIER\_NAME, SUM(P.STOCK\_QUANTITY) \rightarrow "QUANTITY", COUNT(SP.SUPPLIER\_ID) \rightarrow "NUMBER OF PRODUCT"} (\sigma_{P.STOCK\_QUANTITY > 20} (SUPPLIER \bowtie SUPPLY \bowtie PRODUCT))
© 2023 AskSia.AI all rights reserved