Question

	Given the scenario of tracking store gift cards that includes these tables: GIFT CARD CardNo StoreName Value ActivationDate PURCHASE TransactionID Tdate Amt Balance CardNo GIFT_CARD(CardNo, StoreName, Value, ActivationDate) PURCHASE(TransactionID, tDate, Amt, Balance,CardNo) 1. Construct the SQL statement to answer this query: What stores [list name only] have gift cards (no duplicates)? 2. Construct the SQL statement to answer this query: What stores that offer gift cards have names with ‘mart’ in them? 3. Construct the SQL statement to answer this query: Which stores have gift cards with values greater than $500 that were activated on Valentine’s Day (02/14/2022)? 4. Construct the SQL statement to answer this query: What was the amount of the largest purchase made? 5. -Construct the SQL statement to answer this query: List all information about all gift cards. 6. Construct the SQL statement to answer this query: List purchases by date in chronological order. Construct the SQL statement to answer this query: How many purchases [i.e. purchase transactions] were made on the last day of the year [12/31/2022]? What stores have total purchase transaction amounts greater than $5000?

-Construct the SQL statement to answer this query:

List the gift card information and remaining balance for all gift cards that have been used to make purchases as well as the gift card information for cards that have not been used (have no purchases).

-Construct the SQL statement to answer this query:

List the name of the store, the original value of the card and the balance of all gift cards that have been used (those with purchase transactions).

-Construct the SQL statement to answer this query:

What was the total amount of purchases made every day (for each date)?

Answer 1

If you have any doubts, please give me comment...

-- 1) What stores [list name only] have gift cards (no duplicates)?

SELECT DISTINCT(StoreName)

FROM GIFT_CARD;

-- 2) What stores that offer gift cards have names with ‘mart’ in them?

SELECT StoreName

FROM GIFT_CARD

WHERE StoreName LIKE '%mart%';

-- 3) Which stores have gift cards with values greater than $500 that were activated on Valentine’s Day (02/14/2022)?

SELECT StoreName

FROM GIFT_CARD

WHERE Value>=500 AND ActivationDate = '02/14/2022';

--4) What was the amount of the largest purchase made?

SELECT MAX(Amt)

FROM PURACHASE;

--5) List all information about all gift cards.

SELECT *

FROM GIFT_CARD;

--6) List purchases by date in chronological order.

SELECT *

FROM PURCHASE

ORDER BY Tdate;

-- 7) How many purchases [i.e. purchase transactions] were made on the last day of the year [12/31/2022]?

SELECT COUNT(*)

FROM PURCHASE

WHERE Tdate = '12/31/2022';

--8) What stores have total purchase transaction amounts greater than $5000?

SELECT StoreName

FROM GIFT_CARD G, PURCHASE P

WHERE G.CardNo = P.CardNo

GROUP BY G.CardNo, StoreName

HAVING SUM(Amt)>5000;

--9) List the gift card information and remaining balance for all gift cards that have been used to make purchases as well as the gift card information for cards that have not been used (have no purchases).

SELECT G.*, balance

FROM GIFT_CARD G, PURCHASE P

WHERE G.CardNo = P.CardNo;

--10) List the name of the store, the original value of the card and the balance of all gift cards that have been used (those with purchase transactions).

SELECT G.StoreName, G.Value, P.balance

FROM GIFT_CARD G, PURCHASE P

WHERE G.CardNo = P.CardNo;

--11) What was the total amount of purchases made every day (for each date)?

SELECT Tdate, SUM(Amt)

FROM PURCHASE

GROUP BY Tdate;