How to do the questions below? Please write down the following queries in standa

Question

How to do the questions below?

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)

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)

Explanation / Answer

This is the SQL Query to fetch the second most expensive product with the type 'game':

Select pname from Products where price In(Select MAX(price) from Products where price NOT IN(Select Max(price) from Products where type="game"));

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.