Get Even More Visitors To Your Blog, Upgrade To A Business Listing >>

mySQL fundamentals part 2

Read about mySQL fundamentals part 1

List out productName, price, categoryName for each product

Select  b.categoryName ,a.productName, a.price  from product as a 
		join category as b on a.categoryId=b.categoryId;
	(or)
	Select categoryName, productName,price from product,category 
		where product.categoryId=category.categoryId;

List out number of products for each category

select b.categoryName,count(a.productId) as noOfProducts from product as a  
		join category  as b on a.categoryId= b.categoryId   group by b.categoryId;
	(or)
	select categoryName,count(productId)as noOfProducts  from product, category 
		where product.categoryId = category.categoryId group by category.categoryId

change margo product’s market price as 60

update product set price=60 where productName ='margo'

List out productName, offerName, discount percentage in amount for each product;

select  a.productName, b.offerName,a.price, b.discount, 
		a.price*(b.discount/100) as discount_amount from product as a join offerdetail as b 
		on a.productId= b.productId;

List out categoryName,productName,offerName,discount for each product

select  categoryName,productName,offerName,discount from category, product, offerdetail 
		where category.categoryId = product.categoryId and product.productId = offerdetail.productId

List out product’s price,discount percentage, prodcut’s price after discount, offerName,productName for each product.

select  a.productName, b.offerName, a.price, b.discount, a.price-(a.price*(b.discount/100)) 
		as discount_amount from product as a join  offerdetail as b on a.productId= b.productId;

List out offer detail which is named as ‘rose’;

select * from offerdetail where offerName like "rose";

List out expired offer details

select * from offerdetail where startDate

Update pears product discount as 15 and extend that offer expire date upto “2017-06-31 00:00:00”;

update offerdetail set discount=15 ,endDate="2017-06-31 00:00:00" where 
		productId in (select productId from product where productName like 'pears');

List out highest discount along with productName

select offerdetail.discount , product.productName from product, offerdetail 
		where offerdetail.productId = product.productId order by offerdetail.discount desc limit 0,1;

List out first three highest discount along with productName

select product.productName, offerdetail.discount from offerdetail,product 
		where product.productId = offerdetail.productId  order by offerdetail.discount desc limit 3;

List out productName along with discount, where the offer is started between “2015-06-1” and “2016-08-29”

select product.productName, offerdetail.discount from  offerdetail, product 
		where offerdetail.productId = product.productId and offerdetail.startDate 
		between "2015-06-1" and "2016-08-29";

To get third highest discount along with productName

select offerdetail.discount, product.productName from product, offerdetail 
		where offerdetail.productId = product.productId order by offerdetail.discount 
		desc limit 2,1

To get second highest discount along with productName

select offerdetail.discount , product.productName from product, offerdetail 
		where offerdetail.productId = product.productId order by offerdetail.discount 
		desc limit 1,1

To get second least discount along with productName

select offerdetail.discount , product.productName from product, offerdetail 
		where offerdetail.productId = product.productId order by offerdetail.discount asc 
		limit 1,1;

The post mySQL fundamentals part 2 appeared first on Drtuts.



This post first appeared on Drtuts, please read the originial post: here

Share the post

mySQL fundamentals part 2

×

Subscribe to Drtuts

Get updates delivered right to your inbox!

Thank you for your subscription

×