SQL Server

Basic SQL Query 

USE Britain

SELECT * FROM DBO.Britain

***********************************************************************************

SQL Query with filter condition (Where)

(Two filters)

SELECT * FROM DBO.Britain WHERE City = 'LONDON' AND State = 'ENGLAND'

(Between Condition)

SELECT * FROM DBO.Britain WHERE Salary between 12000 and 30000

SELECT COUNT(Salary) FROM DBO.Britain WHERE Salary between 12000 and 30000

***********************************************************************************

SQL Query with count

***********************************************************************************

SELECT COUNT(City) FROM dbo.Britain  

SELECT COUNT(City) FROM dbo.Britain  WHERE City = 'LONDON' AND State = 'ENGLAND'

SQL Query Backup table

***********************************************************************************

SELECT * INTO TEMPBRITAIN FROM DBO.Britain

Update table with Case...end based on another table

***********************************************************************************

UPDATE Flowslake

SET LOAN_SUBTYPE = 

    CASE

        WHEN LOANTYPES = 'Secured Loan' THEN 'SL'

        WHEN LOANTYPES = 'Unsecured Loan' THEN 'UL'

        ELSE LOAN_SUBTYPE  -- If not 'Secured Loan' or 'Unsecured Loan, leave it as is

    END;

--------------------------------------------------------------------------------------

-- Declare and initialize the variable

DECLARE @Loantypes NVARCHAR(100)

-- Initialize the variable with 'Unsecured Loan'

SET @Loantypes = 'LOANTYPES'

-- Retrieve the value from the table based on the condition

SELECT @Loantypes = LOANTYPES FROM Flowslake

-- Check the value of the variable and update the table accordingly

IF @Loantypes = 'Secured Loan'

BEGIN

    UPDATE Flowslake SET LOAN_SUBTYPE = 'SL' WHERE LOANTYPES = 'Secured Loan'

END

ELSE

BEGIN

    UPDATE Flowslake SET LOAN_SUBTYPE = 'UL' WHERE LOANTYPES = 'Unsecured Loan'

END

Update table with Joints

***********************************************************************************

UPDATE TBLN

SET TBLN.[01-Nov] = TBLB.Status

FROM tblNovember_2023 AS TBLN

INNER JOIN Book7 AS TBLB ON TBLN.AHA_ID = TBLB.AHA_ID

Insert the data in new table 

***********************************************************************************

  --Insert data in new table

  select * into BackUp_tblStudent from tblStudent

Insert the data in already existing table which has data

***********************************************************************************

  --Insert in already existing table which already have data

  insert into tblStudent select * from Dataset_P

Vlookup Like connection in SQL

***********************************************************************************

SELECT Sht3.*, Sht1.[Name of Student]

FROM Sheet3 AS Sht3

LEFT JOIN Sheet1 AS Sht1 ON Sht3.[Name of Student] = Sht1.[Name of Student]

WHERE Sht1.[Name of Student] IS NULL

ORDER BY Sht1.[Name of Student];

SELECT 

    Sht3.*, 

    Sht1.[Name of Student] AS [Sheet1_Name_of_Student] -- Rename this column uniquely

INTO #JointInserted

FROM Sheet3 AS Sht3

LEFT JOIN Sheet1 AS Sht1 ON Sht3.[Name of Student] = Sht1.[Name of Student]

WHERE Sht1.[Name of Student] IS NULL

ORDER BY Sht1.[Name of Student];

select * from #JointInserted

Delete the duplicate Data with Group by Max

***********************************************************************************

Select SHT2.*, SHT1.BRANCH into #TempAdded2

from Sheet2 as SHT2

INNER JOIN Sheet1 as SHT1

ON SHT2.STATE = SHT1.STATE

order by STATE

select * from #TempAdded2 order by ID

select distinct State,branch,ID from #TempAdded2 order by ID

select State, branch, count(*) from #TempAdded2 

group by State, branch 

having count (*)>1

Delete from #TempAdded2 where ID not in ( select Max(ID) from #TempAdded2 group by State, branch)

Delete the duplicate Data with CTE and ROW Function

***********************************************************************************

With TempAdded_CTE As

(

select *, ROW_NUMBER() over (partition by Branch order by Branch) as RowNumber

from #TempAdded

)

select * from TempAdded_CTE

Delete from TempAdded_CTE where RowNumber >1

Find all the store procedures associated to particular table

**************************************************************************************************

SELECT 

    OBJECT_NAME(object_id) AS ProcedureName

FROM 

    sys.sql_modules

WHERE 

    definition LIKE '%STUDENT_MASTER_TABLE%'

AND 

    OBJECTPROPERTY(object_id, 'IsProcedure') = 1;

Select/ Find the version of sql server 

**************************************************************************************************

SELECT @@VERSION;

Check SQL Server Instance Name 

**************************************************************************************************

SELECT @@SERVERNAME AS ServerName;

Check Current Database Info 

**************************************************************************************************

SELECT 

    DB_NAME() AS CurrentDatabase,

    DATABASEPROPERTYEX(DB_NAME(), 'Status') AS Status,

    DATABASEPROPERTYEX(DB_NAME(), 'Recovery') AS RecoveryModel;

List All Databases on the Server

**************************************************************************************************

SELECT 

    name AS DatabaseName,

    state_desc AS Status,

    recovery_model_desc AS RecoveryModel

FROM 

    sys.databases;

List All Tables in a Specific Database

**************************************************************************************************

SELECT 

    TABLE_SCHEMA, 

    TABLE_NAME 

FROM 

    INFORMATION_SCHEMA.TABLES

WHERE 

    TABLE_TYPE = 'BASE TABLE';

Find SQL Server Login Info

**************************************************************************************************

SELECT 

    name AS LoginName,

    type_desc AS LoginType,

    create_date,

    modify_date,

    is_disabled

FROM 

    sys.sql_logins;