Printing Number Pattern using SQL

Link for video answer:

SQL:

--*********************

--PRINT Pattern

--*********************

-- 1

-- 11

-- 111

-- 1111

-- 11111

-- 22222

-- 2222

-- 222

-- 22

-- 2

-- Solution:

DECLARE @counter int  = 1  -- Declare and Initialize counter

DECLARE @number int = 1 -- Declare and Initialize number to be printed

WHILE @counter <= 5                   -- Condition

BEGIN                             -- Begin

IF(@number = 1)

BEGIN

PRINT replicate(@number , @counter)   -- Print

SET @counter = @counter + 1 

IF(@number =1 AND @counter=6) -- once @var value becomes 6 on 5th iteration we increment the number just once

BEGIN

SET @number=@number+1

PRINT replicate(@number , @counter-1)

SET @counter = @counter - 2 

END

END

ELSE 

BEGIN

PRINT replicate(@number , @counter)

SET @counter = @counter - 1  -- decreasing the counter and using the incremented number from above

IF(@counter=0)

BREAK;

END

END

2nd Way:

DECLARE @counter int  = 1  -- Declare and Initialize counter

DECLARE @number int = 1 -- Declare and Initialize number to be printed

WHILE @counter <= 5                   -- Condition

BEGIN                             -- Begin

IF(@number = 1)

BEGIN

PRINT replicate(CAST(@number AS VARCHAR(2)) + SPACE(5), @counter)

PRINT('')

SET @counter = @counter + 1 

IF(@number =1 AND @counter=6) -- once @var value becomes 6 on 5th iteration we increment the number just once

BEGIN

SET @number=@number+1

PRINT replicate(CAST(@number AS VARCHAR(2)) + SPACE(5), @counter-1) 

PRINT('')

SET @counter = @counter - 2 

END

END

ELSE 

BEGIN

PRINT replicate(CAST(@number AS VARCHAR(2)) + SPACE(5), @counter)

PRINT('')

SET @counter = @counter - 1  -- decreasing the counter and using the incremented number from above

IF(@counter=0)

BREAK;

END

END

3rd Way:

DECLARE @counter1 int  = 1  -- Declare and Initialize counter

DECLARE @number1 int = 1 -- Declare and Initialize number to be printed

WHILE @counter1 <= 5                   -- Condition

BEGIN                             -- Begin

IF(@number1 = 1)

BEGIN

PRINT replicate(@number1, @counter1)       -- Print

SET @counter1 = @counter1 + 1 

END

END

SET @number1 = 2

SET @counter1 = 5

WHILE @counter1 <= 5 

BEGIN

PRINT replicate(@number1 , @counter1)      -- Print

SET @counter1 = @counter1 - 1 

IF(@counter1=0)

BREAK;

END

 

SQL for Beginners Series - Session 12

JOINS in SQL

Please click here for you tube video Answer

Exercise:

For this exercise, we are considering 2 tables 

    tblEmployee

    tblDept

Deptid is the joining column between these 2 tables. 

In Relational databases, all the data is not loaded in a single table, but it will be loaded in multiple tables and those tables will have relations. This process of reducing the redundancy is called Normalization.

These tables can be joined using JOINS in SQL.

There are 4 types of join in SQL

INNER JOIN or JOIN

LEFT JOIN or LEFT OUTER JOIN 

RIGHT JOIN or RIGHT OUTER JOIN

FULL JOIN or FULL OUTER JOIN

Scripts Used:

CREATE TABLE tblEmployee (Empid INT NULL

,EmpName Varchar(50) NULL

,Deptid INT NULL

)

CREATE TABLE tblDept (Deptid INT NULL

                     ,DeptName Varchar(50) NULL

                     )

INSERT tblEmployee Values (1,'Emp5',NULL), (2,'Emp2',2),(3,'Emp3',3)

INSERT tblDept Values  (1, 'HR'),(2, 'Finance'),(3,'IT'),(NULL,'Sales')

--INNER JOIN – Returns the common records between two tables

--LEFT JOIN -  Returns all the rows from Left table and matching rows between two tables

--LEFT JOIN = INNER JOIN + Records from Left table

--RIGHT JOIN – Returns all the rows from Left table and matching rows 

--between two tables

--RIGHT JOIN = INNER JOIN + Records from Right table

--FULL JOIN -  Returns all the rows from two tables

 

Azure DP-900 Certification Introduction

                                Azure Data Fundamentals

You tube Answer:

Syllabus Links: https://query.prod.cms.rt.microsoft.c...

Learning Path Link: https://docs.microsoft.com/en-us/lear...

Links to Chapters: Chapter 1 (5 Units): https://docs.microsoft.com/en-us/lear...

Chapter 2 (3 Units): https://docs.microsoft.com/en-us/lear...

Chapter 3 (3 Units): https://docs.microsoft.com/en-us/lear...

Chapter 4 (5 Units): https://docs.microsoft.com/en-us/lear...

 

SSMS Tips and Tricks - Part 5

You tube Answer:

-- We may run SQL queries / scripts in different Environment 

-- 1. When we are connected to multiple server, we do not know which env we are connected

    --Always the bottom right corner of SSMS to check the server name

-- 2. The same happens when we open a sql script from an email, or a folder

-- This is because SSMS allows us to change connection with in the query window

-- Steps to Avoid such incidents:

-- Customize Status bar with color for each environment

Select Options --> Choose Custom Color --> Select the color --> Ok --> restart SSMS

You are done. Next time when you open SSMS you will get the Status color with the color you have chosen for that server.

If you have learnt anything new, please do subscribe to my channel. Stay Tuned :)

Installation of python

   Part - 1

Below are steps to Install Python in your pc 

➔ Launch to browser and search for python or visit https://python.org

➔ once you click on https://python.org click on downloads

➔ Latest version will be visible on the top e.g. Python 3.9.4

➔ In case if you need a lower version, just scroll down the page and you can find all the lower versions 

➔ Click on the version you wish to install, then .exe file will be downloaded

➔ Double click on the downloaded .exe file, Python set up window opens, tick the check box 'Add Python 3.9 to PATH' and click on 'Customize Installation'

➔ You can also choose py launcher 

➔ Next ➔ Advanced Options

➔ Choose the location where you want to install ➔ tick check box 'Install for all users'

➔ Click on Install 

➔ 'Set up was Successful' on successful Installation

➔ Verify Python Installation

➔ You can verify python installation by launching command prompt 

➔ Run ➔ type cmd ➔ then type and then press enter

or     you can also verity by typing

This would display the version which has been installed

Thank you. If you like my video, please subscribe to my channel :)

 

Case Sensitive Search using SQL

   Part - 29

Link for video answer:

--********************************************************************************

-- Q29 -- Case Sensitive Search using SQL

--********************************************************************************

This can be achieved by changing the collation settings. If you set this at database level, this would be impacted by all the tables under that table. However you can use this with in Select statement with COLLATE so that it can to a Case sensitive Search.

If you want to see the current collation, use below query:

-- SQL_Latin1_General_CP1_CI_AS -- Case Insensitivity

--Latin1_General_CS_AS -- Case Sensitivity

Lets Illustrate with an example:

CREATE TABLE EmployeeName (EmpName Nvarchar(50))

INSERT EmployeeName VALUES ('Microsoft'), ('MicroSoft'), ('MICROSOFT'),('microsoft')

SELECT * FROM EmployeeName

This query returns all rows irrespective of the case sensitivity. We can use COLLATE to force use the Case Sensitivity or we can set at database level as well based on the requirement.

 

Using Collate, It retrieves only one record.

Thank you!. If you like this video, pls subscribe to my channel and like the video

 

SSMS Tips and Tricks - Part 4

You tube Answer:

Q1. How to get column list with comma separation (Replace Select * with column list)

Go to Object Explorer --> Expand the database --> expand tables --> right click on the table --> Select "Script Table as" --> Select to --> New Query Editor window --> now you can copy the columns with comma separation

Q2. How to add Line numbers in SSMS

Press Ctrl + G

Or

Tool --> Options --> Text Editor --> All Languages --> check the check box --> Line Numbers

Q3. How to Wrap text in SSMS

Tool --> Options --> Text Editor --> All Languages --> check the check box -->  Word wrap