I need 5 seperate SQL queries for each of these: /* Query 1. (10 points) Which p

Question

I need 5 seperate SQL queries for each of these:

/* Query 1. (10 points)

Which products whose ProductName is ended with 't' and has a UnitPrice

higher than $30?

Display their ProductID, ProductName, and UnitPrice in the order of

UnitPrice from highest

to lowest. Solve this query by writing a SELECT statement with the LIKE

operator.

Hint: correct output has 5 products.

*/

/* Query 2. (10 points)

Solve Query 1 again by writing another SELECT statement that uses LEFT()

or RIGHT()

functions without the LIKE operator. For the output of Query 2, an extra

column of

CategoryID is wanted and only products with the smallest CategoryID value

are desired.

Hint 1: different sorting from Query 1

Hint 2: TOP x WITH TIES

Hint 3: correct output has three products

*/

/* Query 3.

Display SupplierID and Phone of suppliers whose phone number, including

area code,

contains two and only two '0's. For example, supplier 11 should not appear

in the

output because its phone number (010) 9984510 contains three '0's.

Hint 1: correct output has three suppliers.

Hint 2: considering two LIKE operators.

*/

/* 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.

*/

/* 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.

*/

Explanation / Answer

Query 1

Answer:

select ProductID, ProductName, UnitPrice from Products
where ProductName like '%t' and UnitPrice > '$30'
order by UnitPrice desc;

Query 2: Answer

select ProductID, right('ProductName',3), UnitPrice, min(CategoryID) from Products, Categories
where UnitPrice > '$30'
order by UnitPrice desc;

Query 3: Answer

SELECT SupplierID, Phone from Suppliers where Phone like '0%' and phone like '_0%';

Query 4: Answer

select ProductID, Unit, Products.CategoryID from Products
inner join Categories on Categories.CategoryID = Products.CategoryID
where Unit > 22 and Products.CategoryID = 4 or Products.CategoryID = 4 or Products.CategoryID = 8 and Products.CategoryID = 1 or Products.CategoryID = 3 and Unit == 40
group by ProductID
order by Products.CategoryID asc

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

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