QUERY ON SQL

QUERY ON PHONE NUMER:-

1. SELECT * FROM [dbo].[Detail]

ORIGINAL TABLE

2. SELECT [Id],[Name],

[Phone] = STUFF([Phone],1,10,’XXXXXXX333'),[Gender]

FROM [dbo].[Detail]

3. SELECT [Id],[Name],

[Phone] = STUFF([Phone],1,7,’XXXXXXX’),[Gender]

FROM [dbo].[Detail]

4. SELECT [Id],[Name],

[Phone] =

STUFF(STUFF([Phone],1,3,’XXX’),8,10,’XXX’) ,[Gender]

FROM [dbo].[Detail]

QUERY ON NAME:-

1. SELECT * FROM [dbo].[Info]

ORIGINAL TABLE

2. Select * From [dbo].[Info]

Where charindex(‘R’, Name)=1

STARTS FROM ‘R’

3. SELECT * FROM [dbo].[Info]

WHERE [Name] like ‘RA%’

STARTS FROM ‘RA’

4. SELECT * FROM [dbo].[Info]

WHERE [Name] like ‘A%A%’

START AND END WITH ‘A’

5. SELECT * FROM [dbo].[Info]

WHERE [Name] like '[RKA]%'

START FROM “R, K AND A”

6. SELECT * FROM [dbo].[Info]

WHERE [Name] like ‘[A_K]%’

STARTS FROM A TO K

QUERY ON GENDER:-

1. update [dbo].[Detail]
   set
   [Gender]= CASE WHEN [Gender]=’Female’ then ‘Male’ ELSE ‘Female’ END

UPDATED TABLE

QUERY ON UNION, UNION ALL:-

[dbo].[Info]

[dbo].[Info1]

UNION ALL:-

SELECT * FROM [dbo].[Info]

UNION ALL

SELECT * FROM [dbo].[Info1]

UNION ALL

UNION:-

SELECT * FROM [dbo].[Info]

UNION

SELECT * FROM [dbo].[Info1]

UNION

QUERY ON JOIN:-

Table_1

Table_2

INNER JOIN:-

SELECT * FROM [dbo].[Table_1]

INNER JOIN [dbo].[Table_2]

ON

[dbo].[Table_1].[NAME] = [dbo].[Table_2].[NAME]

INNER JOIN

LEFT JOIN:-

SELECT * FROM [dbo].[Table_1]

LEFT JOIN [dbo].[Table_2]

ON

[dbo].[Table_1].[NAME] = [dbo].[Table_2].[NAME]

LEFT JOIN

RIGHT JOIN:-

SELECT * FROM [dbo].[Table_1]

RIGHT JOIN [dbo].[Table_2]

ON

[dbo].[Table_1].[NAME] = [dbo].[Table_2].[NAME]

RIGHT JOIN

FULL OUTER JOIN:-

SELECT * FROM [dbo].[Table_1]

FULL OUTER JOIN [dbo].[Table_2]

ON

[dbo].[Table_1].[NAME] = [dbo].[Table_2].[NAME]

FULL OUTER JOIN

DUPLICATE DATA AVOID IN SQL:-

ORIGINAL TABLE

SELECT DISTINCT [NAME] FROM [dbo].[INFO]

AVOID DUPLICATION

If you are newer to database learning -SQL Server recommended is the following must-watch video: -