SQL Basic Query

I want to discuss about SQL Basic Query. Microsoft SQL and Oracle PLSQL is import for DB sql query.

SQL queries can be written in the box located under the “Execute SQL” tab. Click ‘Run SQL’ to execute the query in the box.

SELECT year
FROM surveys;

1 2	SELECT year FROM surveys;

We have capitalized the words SELECT and FROM because they are SQL keywords. SQL is case insensitive, but it helps for readability, and is good style.

Select Code in 5 Digit:

SELECT top 10 PAYROLL_CODE,
 CAST((REPLICATE('0', 5 - len(PAYROLL_CODE)) + CAST(PAYROLL_CODE AS VARCHAR)) as varchar) 
FROM [TAX_OIL].[dbo].[TAX_DETAILS_OLL]

SELECT top 10 PAYROLL_CODE,

CAST((REPLICATE('0', 5 - len(PAYROLL_CODE)) + CAST(PAYROLL_CODE AS VARCHAR)) as varchar)

FROM [TAX_OIL].[dbo].[TAX_DETAILS_OLL]

Update:

update [TAX_DETAILS_OLL]
set PAYROLL_CODE =  CAST((REPLICATE('0', 5 - len(PAYROLL_CODE)) + CAST(PAYROLL_CODE AS VARCHAR)) as varchar)

1 2	update [TAX_DETAILS_OLL] set PAYROLL_CODE = CAST((REPLICATE('0', 5 - len(PAYROLL_CODE)) + CAST(PAYROLL_CODE AS VARCHAR)) as varchar)

Search Tables For Specific Columns

SELECT COLUMN_NAME AS 'ColumnName' ,TABLE_NAME AS 'TableName' 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE COLUMN_NAME LIKE '%MyName%' ORDER BY TableName ,ColumnName;

SELECT COLUMN_NAME AS 'ColumnName' ,TABLE_NAME AS 'TableName'

FROM INFORMATION_SCHEMA.COLUMNS

WHERE COLUMN_NAME LIKE '%MyName%' ORDER BY TableName ,ColumnName;

Table List Of a Database

USE YOURDBNAME
GO 
SELECT *
FROM sys.Tables
GO

USE YOURDBNAME

SELECT *

FROM sys.Tables

Update One Table From Another Table:

UPDATE NATIONAL_SALES_OLL_Test.dbo.TRANS_TARGET_MONTHLY_DTL 
SET REGION_NAME = Q.REGION_NAME
FROM
(
	select REGION_CODE,REGION_NAME
	from NATIONAL_SALES_OLL_Test.dbo.TRANS_TARGET_MONTHLY_DTL TD
	where TRG_ID= 106
)Q
where TRG_ID= 107 and NATIONAL_SALES_OLL_Test.dbo.TRANS_TARGET_MONTHLY_DTL.REGION_CODE = Q.REGION_CODE