I need them in MS SQL please Table: table: Product PID CataID CataType ProdName

Question

I need them in MS SQL please

Table:

table:

Product PID CataID CataType ProdName ProdFinish Price Qty_on_Hand Description VID 1100 CT50 Outdoors Picnic Table Wood 67.99 5 Rectangular 36x48 8999 1110 CT40 Living Room Coffee Table Cherry 175 11 Oval 24 inch 3444 1111 CT10 Family Room Lazy Chair Leather 199.99 12 36 inch Tall 7676 3330 CT60 Office Office Desk Natural Oak 375 10 100 x 48 x 36 4555 4440 CT10 Family Room Table Lamp Metallic Silver 30 23 16 inch Tall 2333 4444 CT50 Outdoors Picnic Table Metallic Silver 39.99 23 16 inch Tall 2333 4448 CT50 Outdoors Porch Table Metallic Silver 29.99 23 16 inch Tall 2333 5550 CT20 Bedroom Dresser Coffee Bean 299 5 37 x 64 x 17 5666 7777 CT30 Porch Rick-n-Roll Chair Cherry 99.99 120 36 inch Tall 7676 8888 CT10 Family Room Dinning Table Natural Oak 14.95 50 24 inch Tall 5666 5. List PID, ProdName, and Price for all products with a product name containing the word Table" and whose price is above S30.00. Name your SQL statement under the name Query5, then copy your query and its results into the Word document under the subheading Query5.

Explanation / Answer

5. Select PID, ProdName, Price from Product where ProdName LIKE '%Table%' AND PRICE >30

The LIKE statement checks if the contents of the column contain the world Table. The % denotes that there can be any number of characters before or after Table and hence with the price constraint added , we can select the required data from the table.

6. Select OrderNo, OrderDate, PromiseDate from custOrder inner join (select OrderNo, count(Distinct PID) from Orderline group by OrderNo having count(distinct PID) >=3) on Orderline.OrderNo= CustOrder.OrderNo;

Here we take two selections and join them on order no.

we take order no, order date and promisedate from custorder and join it to add the extra count element. Now since we have to count for atleast different product items, we use the count(distinct pid). We used distinct pid instead of * because we also have multiple orders for the same product which we need to ignore. hence the output table will contain the 4 columns required in the output based on the constraints provided.

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

---

---

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