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
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.
*/
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started