Question

Below are several table schemas representing online store delivery system, with attributes and relationships: You will use these to answer the SQL questions 1-7. (2 marks each).

Shopper (shopperNo(pk), shopperfName, shopperlName, shopperContact, shopperCity, shopperCcard);

Store (storeNo(pk), storeLocation, storeSize)

Review (revCode(pk), revDate, revRating, shopperNo, storeNo(fk))

Purchase (purId(pk), purDate, purTime, purQty, purValue, shopperNo(fk), storeNo(fk)))

Use SQL statements to answer the following questions:

1. Use SQL statements to create the 2 tables: Purchase and Store with the correct relationship constraints.
2. Add 2 rows:
   1. One row in STORE with one store having number ‘99’.
   2. Another row in SHOPPER with your student full name and student ID.
3. List the shoppers (shopperNo, shopperfName, shopperlName ) who have not given any reviews; sort the results by fName and lName in ascending order.
4. Show the number of times that each shopper made a purchase. Sort the results by the shopperNo.
5. List the storeNo and storeLocation for all stores that had a purchase order on 10/5/2020.
6. Show the total amount of the purchases made in store ‘99’ to display, if the total purchase value is more than $400.
7. For the purchase table, we need to change the purchase quantity to ‘30’ for the shopper ‘2556’: write an SQL statement to make this change.

Below are several table schemas representing online store delivery system, with attributes and relationships: You will use these to answer the SQL questions 1-7. (2 marks each).

Shopper (shopperNo(pk), shopperfName, shopperlName, shopperContact, shopperCity, shopperCcard);

Store (storeNo(pk), storeLocation, storeSize)

Review (revCode(pk), revDate, revRating, shopperNo, storeNo(fk))

Purchase (purId(pk), purDate, purTime, purQty, purValue, shopperNo(fk), storeNo(fk)))

Use SQL statements to answer the following questions:

1. Use SQL statements to create the 2 tables: Purchase and Store with the correct relationship constraints.
2. Add 2 rows:
   1. One row in STORE with one store having number ‘99’.
   2. Another row in SHOPPER with your student full name and student ID.
3. List the shoppers (shopperNo, shopperfName, shopperlName ) who have not given any reviews; sort the results by fName and lName in ascending order.
4. Show the number of times that each shopper made a purchase. Sort the results by the shopperNo.
5. List the storeNo and storeLocation for all stores that had a purchase order on 10/5/2020.
6. Show the total amount of the purchases made in store ‘99’ to display, if the total purchase value is more than $400.
7. For the purchase table, we need to change the purchase quantity to ‘30’ for the shopper ‘2556’: write an SQL statement to make this change.

Answer 1

To create table, use the syntax, CREATE TABLE table_name (column datatype);

CREATE TABLE Purchase (
purId int NOT NULL PRIMARY KEY,
purDate DATE NOT NULL,
purTime TIME (0) NOT NULL,
purQty int,
purValue int,
FOREIGN KEY (shopperNo) REFERENCES Shopper(shopperNo),
FOREIGN KEY (storeNo) REFERENCES Store(storeNo)
);

Similarly create Store table,

CREATE TABLE Store (
storeNo int NOT NULL PRIMARY KEY,
storeLocation char(255),
storeSize int,
);

To insert values in the table, use the syntax INSERT INTO table_name (column)VALUES (value);
INSERT INTO Store VALUES (99, 'mount road,XYZ', 100);
INSERT INTO Shopper(shopperNo, shopperfName, shopperlName) VALUES (123, 'Abc', 'Def');

To get result from combining two or more tables Join or where can be used.

Here, we used where and in to combine results.

select shopperNo, shopperfName, shopperlName from Shopper A where
A.shopperNo in (
select B.shopperNo from Review B where B.revRating IS NULL);

To calculate the number of times that each shopper made a purchase, COUNT() is used.

Group by groups shopperNo and order by sort them.
  
SELECT shopperNo,COUNT(purId)
FROM Review
GROUP BY shopperNo
ORDER BY COUNT(purId));

where A.storeNo in is used to link Store table to Purchase.
select storeNo, storeLocation from Store A where
A.storeNo in (
select B.storeNo from Purchase B where B.purDate = '10/5/2020');

Here, where A.storeNo in is used to link Purchase and Store table.
  
select purId, purQty from Purchase A where purValue > 400 AND
A.storeNo in (select B.storeNo from Store B where B. storeNo = 99);

To update a existing row in a table,UPDATE table_name
SET column1 = value1 WHERE condition;
  
UPDATE Purchase SET purQty = 30 WHERE shopperNo='2556';