Problems: Create ad-hoc queries to address specific user requests stated below.

Question

Problems: Create ad-hoc queries to address specific user requests stated below. You need to produce the results that look like the screenshot shown under each problem. You may need to use necessary query out formatting commands for some questions to make the outcome look the same as the screenshot. 1. Instructions of query output: a. List those books that are NOT in the Fitness, Cooking, Family Life, or Computer b. Only list those chosen books with a cost greater than or equal to $20. C. Sort the output in ascending order of category and descending order of cost. d. Group the query results by category and insert one blank line in between categorie e. Add a $ symbol before the cost figure. ATEGORY TITLE COST USINESS SUPPLY CHAIN MANAGEMENT BUSINESS TALK FROM GOOD TO BEST SMART INVESTORS $85 $45.4 $26.45 $24.4 HILDREN BUILDING A CAR WITH TOOTHPICKS $37.8 BIG FOOT AND ME $25.32 LITERATURE SHORTEST POEMS $21.85 $20.85 GONE WITH THE CAT $27.85 $27.85 $24.85 ELF HELP MY BEST LIFE THE POWER OF I AM BE A BETTER YOU 1 rows selected

Explanation / Answer

Query 1:

SELECT CATEGORY, TITLE, '$' + CONVERT(VARCHAR(100),SUM(COST)) FROM BOOKS
WHERE CATEGORY NOT IN ('Fitness', 'Cooking','Family Life', 'Computer')
AND SUM(COST) >= 20
GROUP BY CATEGORY, TITLE
ORDER BY CATEGORY ASC, COST DESC

Query 2:

SELECT [Customer#], Customer, [Address],[Referred By]
FROM Customer
WHERE [Referred By] IS NOT NULL
AND Address LIKE 'P.O. BOX%'
AND StreetAddress IS NULL
ORDER BY [Customer#] ASC

Query 3:

SELECT REGION, [CUSTOMERS WITH NO REFERRAL], STATE
FROM CUSTOMER
WHERE [Referred By] IS NULL
GROUP BY REGION
ORDER BY REGION, STATE, [Last Name]

Query 4:

SELECT CATEGORY, TITLE, PUBDATE, (RetailPrice - Cost) AS PROFIT
FROM BOOKS
WHERE YEAR(PUBDATE) IN (2004, 2005)
AND CATEGORY IN ('Children', 'COMPUTER', 'Family Life')
AND (RetailPrice - Cost) > 10.00
ORDER BY Profit DESC

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

---

---

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