How to do the questions below Thank you so much Please write

How to do the questions below? Thank you so much

Please write down the following queries in standard relational algebra. Note that some of them may not be expressible in standard relational algebra, and for these queries you need to explain the reason that they cannot be expressed. (Also, products with the same name always have the same product id, and vice versa.)

Consider the following relations for e-commercial system:

Users (uid: integer, uname: string, age: integer, accountBalance: double)

Stores (sid: integer, sname: string, rank: integer)

Products (pid: integer, sid: integer, pname: string, type: string, price: double)

PurchaseRecords (uid: integer, sid: integer, pid: integer, date:string)

1. (10 marks) Find the name(s) of the store(s) that sell product(s) named ‘Minecraft’.

2. (10 marks) Find the name(s) of the product(s) with type ‘game’ that Kelvin has purchased.

3. (10 marks) Find the name(s) and id(s) of the user(s) that can afford to purchase “Minecraft”.

4. (10 marks) Find the id(s) of the user(s) that has spent more than $10000.

5. (10 marks) Find name(s) and id(s) of the user(s) who cannot afford to purchase any product with type ‘game’.

6. (10 marks) Find the name(s) of the user(s) who have purchased every product available.

7. (10 marks) Find the name(s) of the store(s) that have sold 10 products.

8. (10 marks) Find the name(s) of the store(s) that has not sold any product.

9. (20 marks) Find the name(s) of the product(s) that is the second most expensive product with the type ‘game’. (Assumption: The price of each product is unique)

Solution

1)select sname from stores,products where pname=\'Minecraft\' and product.sid=store.sid;

2)select pname from products,users where type=\'games\'and uname=\'Kelvin\';

4)select uid,uname from users,products where price>10000

5)select uid,uname from users,products where type!=\'game\';

6)select uname from users,products where count(pid)=(select distinct count(pid) group by pid)