SQL Query * Query 5. For products that meet both (a) and (b) conditions of Query

Question

SQL Query

* Query 5.

For products that meet both (a) and (b) conditions of Query 4, if we rank

them in Z -> A

order of ProductID, i.e., from largest to smallest and the one with the

largest ProductID

will be ranked as #1. Based on this rank, find only four of them which are

ranked #8, #9,

#10 and #11. The output should contains same four columns as in Query 4.

Hint: extend ORDER BY with OFFSET.

HERE IS QUERY #4 FOR REFERENCE:

/* Query 4.

Find products that satisfy BOTH conditions below:

(a) Its unit price must NOT below $22.

(b) Its category ID is 4 or 7 or 8. If its category ID is 1 or 3, then it

must

have at least 40 units in stock.

Display their ProductID, UnitPrice, CategoryID, and UnitsInStock. The

output should

be sorted by CategoryID in A -> Z order (i.e., from smallest to largest).

For those

with the same CategoryID, the one with the highest UnitPrice should be

displayed first.

Hint 1: a compound condition with AND, OR, NOT.

Hint 2: correct output has 16 products.

*/

Explanation / Answer

here is your sql query : --------------------------->>>>>>>>>>>>>>>>>>>>>>>

SELECT * FROM ( SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT ProductId,UnitPrice,CategoryID,UnitslnStock FROM (SELECT * FROM products WHERE UnitPrice >= 22 AND (CategoryID = 4 OR CatagoryID = 7 OR CategoryID = 8) AND ((CategoryID = 1 OR CategoryID = 3) AND UnitPrice >= 40) ) ORDERBY CategoryID) ORDERBY ProductID desc) WHERE ROWNUM < 12 ) ORDERBY ProductID) WHERE ROWNUM < 5;

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

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