fbpx

SQL Server Basic TSQL Examples

 

----------------------------------------
-- 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

UpScale Analytics is one of the largest platforms in the world for learning SQL by doing, consisting over 300 SQL exercises at different levels (including solutions), by topics, across over 100 different datasets. More…