[go: up one dir, main page]
Scribd
Upload
100 views4 pages

Mysql 7-10

This query selects the product ID, product name, category name, original price, and new price calculated based on category from the products and categories tables. If the category is "Motors", the new price decreases the original price by 3000. If the category is "Electronics", the new price increases the original price by 50. If the category is "Fashion", the new price increases the original price by 150. For other categories, the new price is the same as the original price.

Uploaded by

sanisani1020
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
100 views4 pages

Mysql 7-10

This query selects the product ID, product name, category name, original price, and new price calculated based on category from the products and categories tables. If the category is "Motors", the new price decreases the original price by 3000. If the category is "Electronics", the new price increases the original price by 50. If the category is "Fashion", the new price increases the original price by 150. For other categories, the new price is the same as the original price.

Uploaded by

sanisani1020
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 4

7.

Write a query to display ProductId, ProductName, CategoryName, Old_Price(price) and New_Price as


per the following criteria a. If the category is “Motors”, decrease the price by 3000 b. If the category is
“Electronics”, increase the price by 50 c. If the category is “Fashion”, increase the price by 150 For the rest
of the categories price remains same. Hint: Use case statement, there should be no permanent change
done in table/DB. (57 Rows) [Note: products, categories]

SELECT `productId` , `ProductName` , `CategoryName` , `Price` ,

CASE Price

WHEN 'Motors' THEN 'Price - 3000'

WHEN 'Electronics' THEN 'Price + 50'

WHEN 'Fashion' THEN 'Price + 150'

ELSE 'Old_Price'

END AS New_Price

from Products P INNER JOIN Categories C

ON P.CategoryId = C.CategoryId;
8. Display the percentage of females present among all Users. (Round up to 2 decimal places) Add “%”
sign while displaying the percentage. (1 Row) [Note: users]

select Concat(round(((select count(*)

from Users

where gender='F' )/count(*)*100),2) ,"%") as percentage_female

from Users;
9. Display the average balance for both card types for those records only where CVVNumber > 333 and
NameOnCard ends with the alphabet “e”.

SELECT `NameOnCard` , `CardType` , `CVVNumber` , avg(`Balance`)

FROM carddetails

WHERE CVVNumber >333

AND NameOnCard LIKE '%e'

GROUP BY CardType;

10. What is the 2nd most valuable item available which does not belong to the “Motor” category.
Value of an item = Price * QuantityAvailable. Display ProductName, CategoryName, value.

SELECT ProductName,

CategoryName,

QuantityAvailable,

MAX("Value of item"= (Price* QuantityAvailable))

WHERE "Value of item" NOT IN (SELECT Max ("Value of item"),


AND (C.CategoryName != 'Motors'),

AND P.CategoryId = C.CategoryId,

FROM products P,

categories C

ORDER BY price DESC

LIMIT 1

OFFSET 2;

You might also like