Programming Nest

SELECT CodeNo, CustName,ShopName FROM Customer;
SELECT DISTINCT CodeNo,ItemName,SalePrice,Unit FROM Stock;
SELECT CustName,Mobile,ShopName FROM Customer WHERE CodeNo=101 AND CodeNo=102;
SELECT CustName, ShopName FROM Customer WHERE CustName='Faiz' AND ShopName='Shopna Store';
SELECT ItemName, SalePrice FROM Stock WHERE ItemName='Shampo' OR SalePrice='500' OR Unit=5;
SELECT VrNO,SellDate,PaymentDate FROM BillEntry WHERE NOT VrNO='A102';
SELECT VrNO,SellDate,Paid FROM BillEntry ORDER BY Paid ASC;
SELECT VrNO,SellDate,Paid FROM BillEntry ORDER BY Paid DESC;
SELECT TOP 2 Paid FROM BillEntry;
SELECT MIN(SalePrice) AS SmallestPrice FROM Stock;
SELECT MAX(SalePrice) AS LargestPrice FROM Stock;
SELECT COUNT(CodeNo)FROM Stock;
SELECT AVG(SalePrice) FROM Stock;
SELECT SUM(10) FROM Stock;
SELECT CustName,Address,ShopName FROM Customer WHERE ShopName LIKE 'a%';
SELECT CustName,Address,ShopName FROM Customer WHERE Address LIKE '%a';
SELECT CustName,Address,Mobile,ShopName FROM Customer WHERE ShopName LIKE '%or%';
SELECT CustName,Address,Mobile,ShopName FROM Customer WHERE ShopName LIKE '_r%';
SELECT CustName,Address,Mobile,ShopName FROM Customer WHERE CustName LIKE 'a__%';
SELECT CustName,Address,Mobile,ShopName FROM Customer WHERE ShopName LIKE 'a%o';
SELECT * FROM Customer WHERE CustName IN ('Faiz', 'Jane');
SELECT * FROM Customer WHERE CustName NOT IN ('Faiz', 'Jane');
SELECT * FROM Stock WHERE SalePrice BETWEEN 500 AND 750;
SELECT CustName AS [Customer Name], Mobile AS [Contact Person] FROM Customer

USE master
Go
If DB_ID('InventoryDB') IS NOT NULL

DROP DATABASE InventoryDB
GO

CREATE DATABASE InventoryDB
ON
(
Name='InventoryDB_data',
FileName='C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\InventoryDB.mdf',
Size=10MB,
MaxSize=2GB,
FileGrowth=10%
)
LOG ON
(
Name='InventoryDB_log',
FileName='C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\InventoryDB.ldf',
Size=10MB,
MaxSize=2GB,
FileGrowth=10%
);

GO

USE InventoryDB

GO

CREATE TABLE Customer
(CodeNo INT NOT NULL PRIMARY KEY NONCLUSTERED,
CustName VARCHAR(50) NOT NULL,
Address VARCHAR (50) NOT NULL,
Mobile VARCHAR (50) NOT NULL,
ShopName VARCHAR (50) NOT NULL)

CREATE TABLE Stock
(StockID INT NOT NULL PRIMARY KEY,
CodeNo INT NOT NULL FOREIGN KEY REFERENCES Customer(CodeNo),
ItemName VARCHAR (50) NOT NULL,
PurchagePrice decimal NOT NULL,
SalePrice decimal NOT NULL,
Unit INT NOT NULL,
)

CREATE TABLE BillEntry
(BillID INT NOT NULL PRIMARY KEY,
VrNO VARCHAR (10) NOT NULL,
SellDate DATETIME NOT NULL,
PaymentDate DATETIME NOT NULL,
CodeNo INT NULL FOREIGN KEY REFERENCES Customer(CodeNo),
StockID INT NULL FOREIGN KEY REFERENCES Stock (StockID),
Paid decimal NOT NULL,
)

INSERT INTO Customer
(CodeNo,CustName,Address,Mobile,ShopName)
VALUES
(101,'Faiz','Dhaka',01713627446,'Shopna Store'),
(102,'Jane','Comilla',01521407163, 'Awliya Store'),
(103,'Frank','Chittagong',01729130873, 'Sadik Telikom'),
(104,'Saiful','Chittagong',01913301344, 'Organization for Child'),
(105,'Pritom Karmakar','Sylhet',01712735026, 'Attic Computer')

INSERT INTO Stock
(StockID,CodeNo,ItemName,PurchagePrice,SalePrice,Unit)
VALUES
(1,101,'Shampo',450,500,5),
(2,102,'Body Spry',700,750,6),
(3,103,'Baby Food',1050,1200,3)

INSERT INTO BillEntry
(BillID,VrNO,SellDate,PaymentDate,Paid)
VALUES
(1,'A101','1/1/2015','1/1/2020',2500),
(2,'A102','1/1/2016','1/2/2020',4500),
(3,'A103','1/1/2017','1/3/2020',3600)
GO

Installation Process Of SQL Server