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
No comments:
Post a Comment