CREATE DATABASE Practice
USE Practice
CREATE TABLE tblProducts(tID                INT PRIMARY KEY IDENTITY,
                         ProductName        VARCHAR(20),
                         RecommendedPrice    MONEY,
                         Category            VARCHAR(10))
                         
CREATE TABLE tblCustomers(CustomerID        INT PRIMARY KEY IDENTITY,
                          FirstName            VARCHAR(50),
                          LastName            VARCHAR(50),
                          City                VARCHAR(50),
                          State                CHAR(2),
                          Zip                VARCHAR(10))
                         
CREATE TABLE tblSales(SalesID                INT PRIMARY KEY IDENTITY,
                      ProductID                INT REFERENCES tblProducts(tID),
                      CustomerID            INT REFERENCES tblCustomers(CustomerID),
                      SalesPrice            MONEY,
                      SalesDate                SMALLDATETIME)
                     
INSERT INTO tblProducts(ProductName,RecommendedPrice,Category) VALUES('DVD',105.00 ,'LivingRoom' )
INSERT INTO tblProducts(ProductName,RecommendedPrice,Category) VALUES('MICROWAVE',98.00 ,'Kitchen' )
INSERT INTO tblProducts(ProductName,RecommendedPrice,Category) VALUES('MONITOR',200.00 ,'Office' )
INSERT INTO tblProducts(ProductName,RecommendedPrice,Category) VALUES('Speakers',85.00 ,'Office' )
INSERT INTO tblProducts(ProductName,RecommendedPrice,Category) VALUES('Refrigerator',900.00 ,'Kitchen' )                 
INSERT INTO tblProducts(ProductName,RecommendedPrice,Category) VALUES('VCR',165.00 ,'LivingRoom' )
INSERT INTO tblProducts(ProductName,RecommendedPrice,Category) VALUES('CoffeePot',35.00 ,'Kitchen' )           

INSERT INTO tblCustomers(FirstName,LastName,City,State,Zip) VALUES('Chintan', 'Patel','Anand','GJ' ,'388001')
INSERT INTO tblCustomers(FirstName,LastName,City,State,Zip) VALUES('Paresh', 'Prajapati','Nadiad','GJ','387001' )
INSERT INTO tblCustomers(FirstName,LastName,City,State,Zip) VALUES('Pragnesh', 'Patel','Surat','GJ','395008' )
INSERT INTO tblCustomers(FirstName,LastName,City,State,Zip) VALUES('Nilesh', 'Dharsandia','Mumbai','MH','400002' )
INSERT INTO tblCustomers(FirstName,LastName,City,State,Zip) VALUES('Sonal', 'Patel','Mumbai','MH','400002' )
INSERT INTO tblCustomers(FirstName,LastName,City,State,Zip) VALUES('Harshal', 'Patel','Mogri','GJ','388345' )
INSERT INTO tblCustomers(FirstName,LastName,City,State,Zip) VALUES('Prakash', 'Rathod','Mogri','GJ','388345' )
INSERT INTO tblCustomers(FirstName,LastName,City,State,Zip) VALUES('Aarzoo', 'Dodhiya','Rajkot','GJ','360003' )
INSERT INTO tblCustomers(FirstName,LastName,City,State,Zip) VALUES('Heta', 'Dave','Varanasi','UP','221002' )
INSERT INTO tblCustomers(FirstName,LastName,City,State,Zip) VALUES('Nikita', 'Dave','Varanasi','UP','221002' )
INSERT INTO tblCustomers(FirstName,LastName,City,State,Zip) VALUES('Vaibhav', 'Dave','Varanasi','UP','221002' )
INSERT INTO tblCustomers(FirstName,LastName,City,State,Zip) VALUES('Paresh', 'Patel','Pune','MH' ,'411001')
INSERT INTO tblCustomers(FirstName,LastName,City,State,Zip) VALUES('Prakash', 'Patel','Pune','MH','411001' )
INSERT INTO tblCustomers(FirstName,LastName,City,State,Zip) VALUES('Sandhya', 'Patel','Hyedrabad','AP','500031' )
INSERT INTO tblCustomers(FirstName,LastName,City,State,Zip) VALUES('Divesh', 'Patel','Hyedrabad','KA','560002' )
INSERT INTO tblCustomers(FirstName,LastName,City,State,Zip) VALUES('Payal', 'Shah','Banglore','KA','560002' )
INSERT INTO tblCustomers(FirstName,LastName,City,State,Zip) VALUES('Priyanka', 'Rana','Banglore','GJ','388121' )
INSERT INTO tblCustomers(FirstName,LastName,City,State,Zip) VALUES('Sanket', 'Dhebar','Anand','GJ','388121' )
INSERT INTO tblCustomers(FirstName,LastName,City,State,Zip) VALUES('Puja', 'Shah','V.V.Nagar','UP','388122' )
INSERT INTO tblCustomers(FirstName,LastName,City,State,Zip) VALUES('Priya', 'Shah','Varanasi','UP','221002' )

INSERT INTO tblSales(ProductID,CustomerID,SalesPrice,SalesDate) VALUES(1,1,130.00,'2005-06-14')
INSERT INTO tblSales(ProductID,CustomerID,SalesPrice,SalesDate) VALUES(2,2,97.00,'2005-06-19')
INSERT INTO tblSales(ProductID,CustomerID,SalesPrice,SalesDate) VALUES(3,3,200.00,'2005-09-20')
INSERT INTO tblSales(ProductID,CustomerID,SalesPrice,SalesDate) VALUES(4,4,80.00,'2005-03-22')
INSERT INTO tblSales(ProductID,CustomerID,SalesPrice,SalesDate) VALUES(5,5,899.00,'2005-01-23')
INSERT INTO tblSales(ProductID,CustomerID,SalesPrice,SalesDate) VALUES(6,6,150.00,'2005-03-24')
INSERT INTO tblSales(ProductID,CustomerID,SalesPrice,SalesDate) VALUES(3,7,209.00,'2005-03-10')
INSERT INTO tblSales(ProductID,CustomerID,SalesPrice,SalesDate) VALUES(4,8,90.00, '2005-08-11')
INSERT INTO tblSales(ProductID,CustomerID,SalesPrice,SalesDate) VALUES(6,9,130.00, '2005-08-12')
INSERT INTO tblSales(ProductID,CustomerID,SalesPrice,SalesDate) VALUES(2,14,85.00, '2005-12-13')
INSERT INTO tblSales(ProductID,CustomerID,SalesPrice,SalesDate) VALUES(3,15,240.00, '2005-05-14')
INSERT INTO tblSales(ProductID,CustomerID,SalesPrice,SalesDate) VALUES(1,17,87.00, '2005-07-19')
INSERT INTO tblSales(ProductID,CustomerID,SalesPrice,SalesDate) VALUES(2,18,99.00, '2005-09-20')
INSERT INTO tblSales(ProductID,CustomerID,SalesPrice,SalesDate) VALUES(6,19,150.00, '2005-07-22')
INSERT INTO tblSales(ProductID,CustomerID,SalesPrice,SalesDate) VALUES(5,5,900.00, '2005-03-06')
INSERT INTO tblSales(ProductID,CustomerID,SalesPrice,SalesDate) VALUES(4,6,86.00, '2005-04-07')
INSERT INTO tblSales(ProductID,CustomerID,SalesPrice,SalesDate) VALUES(2,7,88.00, '2005-11-08')
INSERT INTO tblSales(ProductID,CustomerID,SalesPrice,SalesDate) VALUES(3,8,198.00, '2005-05-09')
INSERT INTO tblSales(ProductID,CustomerID,SalesPrice,SalesDate) VALUES(1,9,150.00, '2005-10-10')
INSERT INTO tblSales(ProductID,CustomerID,SalesPrice,SalesDate) VALUES(6,14,99.00, '2005-09-09')
INSERT INTO tblSales(ProductID,CustomerID,SalesPrice,SalesDate) VALUES(6,15,104.00, '2005-09-20')
INSERT INTO tblSales(ProductID,CustomerID,SalesPrice,SalesDate) VALUES(4,14,90.00, '2005-07-22')
INSERT INTO tblSales(ProductID,CustomerID,SalesPrice,SalesDate) VALUES(1,1,130.00,'2005-03-06')
INSERT INTO tblSales(ProductID,CustomerID,SalesPrice,SalesDate) VALUES(2,2,102.00, '2005-04-07')
INSERT INTO tblSales(ProductID,CustomerID,SalesPrice,SalesDate) VALUES(1,3,114.00, '2005-11-08')
INSERT INTO tblSales(ProductID,CustomerID,SalesPrice,SalesDate) VALUES(5,4,1000.00, '2005-05-09')
INSERT INTO tblSales(ProductID,CustomerID,SalesPrice,SalesDate) VALUES(5,5,1100.00, '2005-10-10')
INSERT INTO tblSales(ProductID,CustomerID,SalesPrice,SalesDate) VALUES(3,6,285.00, '2005-06-11')
INSERT INTO tblSales(ProductID,CustomerID,SalesPrice,SalesDate) VALUES(2,7,87.00, '2005-10-12')
INSERT INTO tblSales(ProductID,CustomerID,SalesPrice,SalesDate) VALUES(3,8,300.00, '2005-07-13')
INSERT INTO tblSales(ProductID,CustomerID,SalesPrice,SalesDate) VALUES(3,20,205.00, '2005-12-31')

SELECT * FROM tblProducts
SELECT * FROM tblCustomers
SELECT * FROM tblSales

--------------------------------------------------------------------------------------------------
/*
1.Return the First Name, Last Name, Product Name, and
Sale Price for all products sold in the
month of October 2005.
*/
SELECT  FirstName,
        LastName,
        ProductName,
        SalesPrice
FROM tblCustomers INNER JOIN tblProducts ON tblProducts.tID=tblCustomers.CustomerID INNER JOIN tblSales  ON tID=ProductID
WHERE SalesDate BETWEEN '2005-10-01' AND '2005-10-31'
--------------------------------------------------------------------------------------------------
/*
2.Return the CustomerID, First Name, and Last Name of
those individuals in the Customer table
who have made no Sales purchases.
*/
SELECT CustomerID,FirstName,LastName 
FROM tblCustomers
WHERE CustomerID not in (select distinct CustomerID from tblSales)
--------------------------------------------------------------------------------------------------
/*
3.Return the First Name, Last Name, Sale Price, Recom
mended Sale Price, and the difference
between the Sale Price and Recommended Sale Price f
or all Sales. The difference must be
returned as a positive number.
*/
SELECT FirstName,LastName,SalesPrice,abs(SalesPrice-RecommendedPrice) as diff
FROM tblCustomers
INNER JOIN tblProducts ON tblProducts.tID= tblCustomers.CustomerID
INNER JOIN tblSales  ON tID=ProductID
--------------------------------------------------------------------------------------------------
/*
4.Return the average Sale Price by Product Category
*/
SELECT category,AVG(salesprice) as total
FROM tblProducts inner join tblSales on ProductID=tID
GROUP BY category

--------------------------------------------------------------------------------------------------
/*
5.Add the following Customer and Sale information to
the database. (using store procedure)
FirstName:     Priyanka
LastName:     Chopra
City: Mumbai
State: MH
Zip: 400001
ProductID: 3
SalePrice: 205
SaleDate: 12/31/2005
*/

CREATE PROCEDURE spInsertInToSalesDetails()

DECLARE @firstName varchar(50);
 @lastName  varchar(50)
 @productName  varchar(50);
 @cat varchar(50);
 @sPrice float;
 @custID int;
 @pid int;

as begin
SET @custID = (SELECT CustomerID FROM tblCustomers WHERE FirstName = @firstName AND LastName = @lastName)
SET @pid = (SELECT tID FROM tblProducts WHERE ProductName = @productName
INSERT INTO tblSales(ProductID,CustomerID,SalesPrice,SalesDate) VALUES(@pid,@custID,@sPrice,'')
END



--------------------------------------------------------------------------------------------------
/*
6.Return the Product Category and the average Sale Price
for those customers who have purchased two or more products.
*/
SELECT distinct FirstName,LastName,Category, COUNT(*) AS 'Total Product',AVG(salesprice) as 'Average Sale Price'
FROM tblProducts INNER JOIN tblSales ON tblProducts.tID=tblSales.ProductID INNER JOIN tblCustomers ON tblSales.CustomerID=tblCustomers.CustomerID
GROUP BY category,FirstName,LastName
HAVING COUNT(*)>=2
--------------------------------------------------------------------------------------------------
/*
7.Update
the Sale Price to the Recommended Sale Price of those
Sales occurring between 6/10/2005 and 6/20/2005.
*/
UPDATE tblSales SET SalesPrice= RecommendedPrice
FROM tblSales
INNER JOIN tblProducts ON tblSales.ProductID = tblProducts.tID
WHERE SalesDate BETWEEN '2005-06-10' AND '2005-06-20'
--------------------------------------------------------------------------------------------------
/*
8.Number of Sales by Product Category where the averafIRS
ge Recommended Price is 10 or more
dollars greater than the average Sale Price.
*/
SELECT Category,COUNT(SalesID)
FROM tblProducts INNER JOIN tblSales ON tID=ProductID
GROUP BY category
HAVING AVG(RecommendedPrice) >= 10 AND AVG(RecommendedPrice)>AVG(SalesPrice)
--------------------------------------------------------------------------------------------------
/*
9.Without using a
declared iterative construct, return Sale Date and
the running total for all sales,
ordered by the Sale Date in Ascending Order.
*/

--------------------------------------------------------------------------------------------------
/*
EXTRA ...
Count product for each cust
*/
SELECT distinct FirstName,LastName,ProductName, COUNT(*) as 'Total'
FROM tblCustomers
INNER JOIN tblSales ON tblCustomers.CustomerID=tblSales.ProductID
INNER JOIN tblProducts ON tblProducts.tID = tblCustomers.CustomerID
GROUP BY FirstName,LastName,ProductName
--------------------------------------------------------------------------------------------------
/*
=========================================================================================================
1.Create a trigger to maintain a log  table when user insert or updates in customer table log table should
store the date and time of updation or insertion.
*/

CREATE TABLE tblLog(srNo int identity,
                    Operation VARCHAR(50),
                    ModifyDate DATE)
                   
CREATE TRIGGER logTriggerinsert on tblCustomers AFTER INSERT OR UPDATE
as begin
insert into tblLog(Operation,ModifyDate) values('Insert Operation',CURRENT_TIMESTAMP);
end

CREATE TRIGGER logTriggerUpdate on tblCustomers AFTER UPDATE
as begin
insert into tblLog(Operation,ModifyDate) values('Update Operation',CURRENT_TIMESTAMP);
end

CREATE TRIGGER logTriggerDelete on tblCustomers AFTER DELETE
as begin
insert into tblLog(Operation,ModifyDate) values('Delete Operation',CURRENT_TIMESTAMP);
end

SELECT * FROM tblLog
--------------------------------------------------------------------------------------------------
/*
Create a index on product name and retrive sales and  customer description by it
*/
CREATE INDEX indexProductName
ON tblProducts (ProductName)

SELECT * FROM tblProducts WHERE ProductName='DVD'
--------------------------------------------------------------------------------------------------
/*
Create view to display all the records of customer and corresponding products which they have
purchased
*/
CREATE VIEW MyView AS
SELECT FirstName,LastName,ProductName
FROM tblCustomers
INNER JOIN tblSales ON tblCustomers.CustomerID=tblSales.ProductID
INNER JOIN tblProducts ON tblProducts.tID = tblCustomers.CustomerID

select * from MyView
--------------------------------------------------------------------------------------------------
/*
custmer who had purchased specific item(using stored pro)
*/
CREATE PROCEDURE SP_productByCust(@productName VARCHAR(50))
AS BEGIN

DECLARE @totalNoItem as varchar(255)
SET @totalNoItem = 'total' + @productName

select FirstName,LastName,ProductName,COUNT(*) AS 'total'
FROM tblProducts INNER JOIN tblSales ON tblProducts.tID = tblSales.ProductID
                 INNER JOIN tblCustomers ON tblCustomers.CustomerID = tblSales.CustomerID
WHERE tblProducts.ProductName = @productName
GROUP BY FirstName,LastName,ProductName

END

exec SP_productByCust 'dvd'
exec SP_productByCust 'Speakers'
exec SP_productByCust 'keyboard'
--------------------------------------------------------------------------------------------------
/*
SIMPLE CURSOR EXAMPLE
*/

DECLARE @fname AS VARCHAR(50)
DECLARE @lname AS VARCHAR(50)
DECLARE @c AS VARCHAR(50)

DECLARE curSalary CURSOR STATIC FOR

SELECT FirstName,LastName,City FROM tblCustomers

OPEN curSalary
    FETCH NEXT FROM curSalary INTO @fname,@lname,@c;
        WHILE (@@FETCH_STATUS =0)
            BEGIN
                PRINT @fname + ' ' + @lname + ' ' + @c

            FETCH NEXT FROM curSalary INTO @fname,@lname,@c;
            END
    CLOSE curSalary;
DEALLOCATE curSalary
--------------------------------------------------------------------------------------------------
/*
Q Create a query to update product table increase salary by 10 % whose salary is above 20,000  and
count number of rows affected.(Hint using cursor).
*/