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;
Find SQL Randomize
**************************************************************************************************
CREATE TABLE salesTable
(
SalesPersonName VARCHAR(100),
[Month] VARCHAR(20),
ProductName VARCHAR(50),
Qty INT,
City VARCHAR(50),
[Year] INT,
Rate INT
);
WHILE 1 = 1
BEGIN
DECLARE @RowCount INT;
SET @RowCount = (ABS(CHECKSUM(NEWID())) % 7) + 1; -- 1 to 7 rows
;WITH Numbers AS
(
SELECT TOP (@RowCount) ROW_NUMBER() OVER (ORDER BY NEWID()) AS n
FROM sys.objects
)
INSERT INTO salesTable
(
SalesPersonName,
[Month],
ProductName,
Qty,
City,
[Year],
Rate
)
SELECT
-- Random Sales Person
(SELECT TOP 1 Name FROM
(VALUES
('Asfiya Pathan'),
('Priyanshu Somani'),
('Rajni.Waghmare'),
('Akshay Vibhute'),
('Abhishek Jadhav'),
('Krushna Khedkar'),
('Ujvala Shamrav Shete'),
('Rohini Deshmukh')
) AS Names(Name)
ORDER BY NEWID()),
-- Random Month
DATENAME(MONTH, DATEADD(MONTH, ABS(CHECKSUM(NEWID())) % 12, 0)),
-- Random Product
(SELECT TOP 1 Product FROM
(VALUES
('Laptop'),
('Refriderator'),
('Washing Machine'),
('Television'),
('Microwave Owen'),
('Steam Iron')
) AS Products(Product)
ORDER BY NEWID()),
-- Random Qty (1–10)
(ABS(CHECKSUM(NEWID())) % 10) + 1,
-- Random City
(SELECT TOP 1 City FROM
(VALUES
('Mumbai'),
('Delhi'),
('Bengalore'),
('Pune'),
('Chennai'),
('Hyderabad')
) AS Cities(City)
ORDER BY NEWID()),
-- Random Year (2018–2024)
2018 + (ABS(CHECKSUM(NEWID())) % 7),
-- Random Rate
CASE
WHEN (ABS(CHECKSUM(NEWID())) % 6) = 0 THEN 55000
WHEN (ABS(CHECKSUM(NEWID())) % 6) = 1 THEN 25000
WHEN (ABS(CHECKSUM(NEWID())) % 6) = 2 THEN 30000
WHEN (ABS(CHECKSUM(NEWID())) % 6) = 3 THEN 40000
WHEN (ABS(CHECKSUM(NEWID())) % 6) = 4 THEN 15000
ELSE 2000
END
FROM Numbers;
-- Wait for 2 seconds
WAITFOR DELAY '00:00:02';
END
---------------------------------------
CREATE OR ALTER PROCEDURE GenerateSalesData
AS
BEGIN
SET NOCOUNT ON;
DECLARE @RowCount INT;
SET @RowCount = (ABS(CHECKSUM(NEWID())) % 7) + 1;
;WITH Numbers AS
(
SELECT TOP (@RowCount) ROW_NUMBER() OVER (ORDER BY NEWID()) AS n
FROM sys.objects
)
INSERT INTO salesTable
(
SalesPersonName, [Month], ProductName, Qty, City, [Year], Rate
)
SELECT
(SELECT TOP 1 Name FROM (VALUES
('Asfiya Pathan'), ('Priyanshu Somani'), ('Rajni.Waghmare'),
('Akshay Vibhute'), ('Abhishek Jadhav'), ('Krushna Khedkar'),
('Ujvala Shamrav Shete'), ('Rohini Deshmukh')
) AS N(Name) ORDER BY NEWID()),
DATENAME(MONTH, DATEADD(MONTH, ABS(CHECKSUM(NEWID())) % 12, 0)),
(SELECT TOP 1 Product FROM (VALUES
('Laptop'), ('Refriderator'), ('Washing Machine'),
('Television'), ('Microwave Owen'), ('Steam Iron')
) AS P(Product) ORDER BY NEWID()),
(ABS(CHECKSUM(NEWID())) % 10) + 1,
(SELECT TOP 1 City FROM (VALUES
('Mumbai'), ('Delhi'), ('Bengalore'),
('Pune'), ('Chennai'), ('Hyderabad')
) AS C(City) ORDER BY NEWID()),
2018 + (ABS(CHECKSUM(NEWID())) % 7),
CASE ABS(CHECKSUM(NEWID())) % 6
WHEN 0 THEN 55000
WHEN 1 THEN 25000
WHEN 2 THEN 30000
WHEN 3 THEN 40000
WHEN 4 THEN 15000
ELSE 2000
END
FROM Numbers;
END
SET NOCOUNT ON;
SET QUERY_GOVERNOR_COST_LIMIT 0;
WHILE 1 = 1
BEGIN
EXEC GenerateSalesData;
WAITFOR DELAY '00:00:02';
END
No comments:
Post a Comment