----------------------------------------
-- Basic TSQL
----------------------------------------

-- Ex1
DECLARE
		@var1 int
BEGIN
		SET @var1 = 1
		PRINT @var1
END 

-- Ex2

DECLARE
	@v_firstName varchar(25) ,
	@v_lastName varchar(25)
BEGIN
	SET @v_firstName = 'Ram'
	SET @v_lastName = 'Kedem'
	PRINT @v_firstName + ' ' + @v_lastName
END 

GO
-- Ex3 

DECLARE
	@v_firstName varchar(25) ,
	@v_lastName varchar(25) ,
	@v_age int
BEGIN
	SET @v_firstName = 'Ram'
	SET @v_lastName = 'Kedem'
	SET @v_age = 30
	PRINT @v_firstName + ' ' + @v_lastName +  ' ' + CAST(@v_age AS varchar)
END 

-- Ex4
GO 

DECLARE
	@v_firstName varchar(25) ,
	@v_lastName varchar(25) ,
	@v_age int
BEGIN
	SELECT @v_firstName = 'Ram'
	SELECT @v_lastName = 'Kedem'
	SELECT @v_age = 30
	PRINT @v_firstName + ' ' + @v_lastName +  ' ' + CAST(@v_age AS varchar)
END 

-- Ex5 

USE Northwind
GO

DECLARE
		@v_productName varchar(25),
		@v_unitPrice int
BEGIN
		SELECT @v_productName = productName , @v_unitPrice = unitPrice
		FROM products
		WHERE productID = 2 

		PRINT @v_productName + ' ' + CAST(@v_unitPrice as VARCHAR)
END 

-- Ex6

DECLARE
		@v_catid int ,
		@v_avgprice numeric(6,2)
BEGIN
		SET @v_catid = 3 

		SELECT @v_avgprice = AVG(unitPrice)
		FROM products
		WHERE categoryID = @v_catid

		--PRINT CAST(@v_avgprice AS VARCHAR) + ' - ' + CAST(@v_catid AS VARCHAR)
		-- (error) PRINT CAST(@v_avgprice AS VARCHAR) , CAST(@v_catid AS VARCHAR)
		SELECT CAST(@v_avgprice AS VARCHAR) + ' - ' + CAST(@v_catid AS VARCHAR) AS 'Column'
		SELECT CAST(@v_avgprice AS VARCHAR) , CAST(@v_catid AS VARCHAR) AS 'Column'
END 

-- P3
GO

DECLARE
		@v_supplierID int ,
		@v_maxup int ,
		@v_productName varchar(25)
BEGIN
		SET @v_supplierID = 2 

		SELECT @v_productName = productName , @v_maxup = unitPrice
		FROM products
		WHERE unitPrice = (SELECT MAX(unitPrice)
							FROM products
							WHERE supplierID = @v_supplierID)
		AND supplierID = @v_supplierID

		PRINT  @v_maxup
		PRINT @v_productName
END 

-- Ex7 

SELECT * FROM customers 

DECLARE
		@v_city varchar(25) ,
		@v_count_customers int
BEGIN
		SET @v_city = 'Berlin' 

		SELECT @v_count_customers = COUNT(*)
		FROM customers
		WHERE city = @v_city

		PRINT @v_count_customers

		IF @v_count_customers < 5
		PRINT 'Less than 5 customers in this city ! '
		ELSE IF @v_count_customers > 5
		PRINT 'This city has more than 5 customers '
		ELSE
		PRINT 'This cityu has exactly 5 customers '

END 

-- Ex8 

DECLARE
	@v_num int
BEGIN
	SET @v_num = 5
	IF @v_num > 5
		PRINT 'Greater than 5 !'
	ELSE IF @v_num < 5
		PRINT 'Less than 5 !'
	ELSE
		PRINT 'Equal to 5 !'
END 

-- P4 

DECLARE
		@v_productID int ,
		@v_unitPrice money
BEGIN
		SET @v_productID = 3 

		SELECT @v_unitPrice = unitPrice
		FROM products
		WHERE productID = @v_productID

		PRINT @v_unitPrice

		IF @v_unitPrice > 50
		PRINT ' > 50 '
		ELSE IF @v_unitPrice < 50
			BEGIN
				PRINT ' < 50 '
				UPDATE products SET unitPrice = unitPrice * 1.1 WHERE productID = @v_productID
				PRINT ' Price has been updated !'
			END
		ELSE
		PRINT ' = 50 ' 

END 

-- CASE CONDITION
GO

DECLARE
		@v_productID int ,
		@v_unitPrice money ,
		@case_price varchar(25)
BEGIN
		SET @v_productID = 3 

		SELECT @v_unitPrice = unitPrice
		FROM products
		WHERE productID = @v_productID

		PRINT @v_unitPrice

		SET @case_price = (CASE WHEN @v_unitPrice > 50 THEN 'CASE_A'
								WHEN @v_unitPrice < 50 THEN 'CASE_B'
								ELSE 'CASE_C'
							END )

		PRINT @case_price

END 

SELECT * FROM products 

-- P5
GO

DECLARE
		@v_productID int ,
		@v_unitPrice int ,
		@v_avgprice int
BEGIN
		-- Step 0 - Setting @v_productID
		SET @v_productID = 3
		-- Step 1 - setting @v_avgprice
		SELECT @v_avgprice = AVG(unitPrice)
		FROM products
		PRINT 'Avg price is : ' + CAST (@v_avgprice AS VARCHAR)
		-- Step 2 - setting @v_unitPrice
		SELECT @v_unitPrice = unitPrice
		FROM products
		WHERE productID = @v_productID
		PRINT 'Product price is : ' + CAST (@v_unitPrice AS VARCHAR)
		-- Step 3 - IF
		IF  @v_unitPrice < @v_avgprice
		BEGIN
				PRINT 'Unitprice is less than avg price, updating product..'
				UPDATE products SET unitPrice = unitPrice * 1.1 WHERE productID = @v_productID
				PRINT 'Price has been updated successfully !'
		END
END