MySQL is Open Source Relational SQL database management system being used for developing web-based software applications. Here,sample examples are given below to give underline for mysql. Now, we are going to create database for shopping named as ‘shop’ .It includes three tables as follows ..,

create Database:

create database shop;

create Table for “category” that includes, categoryId, categoryName. categoryId should not be Null and it has to be incremented automatically and should be unique.

CREATE TABLE 'category' ('categoryId' INT(12) NOT NULL AUTO_INCREMENT, 
	'categoryName' VARCHAR(32) NOT NULL, PRIMARY KEY ('categoryId'));

Insert value into category table

INSERT INTO 'category' ('categoryId', 'categoryName') VALUES (NULL, 'soap');

To Insert Multiple Values into category table

INSERT INTO  'category' ('categoryId', 'categoryName') VALUES (NULL,'powder'),
	(NULL,'shampoo'),(NULL, 'paste'),(NULL,'cooldrinks');

create table for “product” to store product details that’s are productId,categoryId,productName,price. productId should not be null and it has to be incremented automatically and should be unique.

CREATE TABLE  'product' ('productId' INT(12) NOT NULL AUTO_INCREMENT PRIMARY KEY, 
	'categoryId' INT(12) NOT NULL, 'productName' VARCHAR(24) NOT NULL, 
	'price' INT(12) NOT NULL);

Insert value into product table

INSERT INTO  'product' ('productId', 'categoryId', 'productName', 'price') VALUES 
	(NULL, 4, 'colgatesalt', 35);

To insert multiple values into product table

INSERT INTO  'product' ('productId', 'categoryId', 'productName', 'price') VALUES 
	(NULL, 4, 'pepsodent', 40),(NULL, 1, 'hamam', 45),(NULL, 1, 'pears', 50),
	(NULL, 3, 'margo', 35);

create table for product’s offerdetail that hold the below fields, offered,productId, offerName,discount,offer start date, offer end date.offerId should not be null and it has to be incremented automatically and should be unique.

CREATE TABLE 'offerdetail' (  'offerId' int(12) NOT NULL AUTO_INCREMENT, 
	'productId' int(12) NOT NULL,  'offerName' varchar(26) NOT NULL,  
	'discount' int(12) NOT NULL, 'startDate' datetime NOT NULL,  'endDate' 
	datetime NOT NULL, PRIMARY KEY ('offerId'));

Insert value into offerdetail table

INSERT INTO  'offerdetail' ('offerId', 'productId', 'offerName', 'discount',
	 'startDate', 
	'endDate') VALUES (NULL, '2', 'golden', '10', '2016-08-22 21:53:47', 
	'2016-09-22 21:53:47');

To insert multiple values into offerdetail table

INSERT INTO  'offerdetail' ('offerId', 'productId', 'offerName', 'discount',
	 'startDate', 'endDate') 
	VALUES (NULL, 3, 'silver', 6, '2016-09-2 21:53:47', '2016-12-03 21:53:47'),
	 (NULL, 4, 'rose', 9, '2016-08-22 21:53:47', '2016-09-22 21:53:47'),
	 (NULL, 1, 'megaoffer',5, '2016-08-22 21:53:47', '2016-09-22 21:53:47'),
	 (NULL, 5, 'silver',2, '2016-08-22 21:53:47', '2016-09-22 21:53:47');

To list out all category detail

select * from category;

To list out all product detail

select * from product order by productId desc;

To list out offerdetail along product name for each product

select  a.productName ,b.offerName, a.price,b.Discount,b.startDate,b.endDate 
	from offerdetail as b join product as a on a.productId= b.productId 
	order by b.productId;

		( OR )

	select  productName,offerName, price,Discount,startDate,endDate 
	from offerdetail ,product where offerdetail.productId = product.productId 
	order by b.productId;

To list out category detail which start with ‘s’;

select * from category where categoryName like 's%';

To list out product detail which is charged as greater than 45;

select * from product where price >45;

The post mySQL fundamentals part 1 appeared first on Drtuts.