Q1. Query to insert random data in database table
IF Object_id('dbo.RandomData') IS NOT NULL
BEGIN
DROP TABLE dbo.RandomData
END
CREATE TABLE dbo.RandomData
(
RowId INT IDENTITY(1, 1) NOT NULL,
SomeInt INT,
SomeBit BIT,
SomeVarchar VARCHAR(10),
SomeDateTime DATETIME,
SomeNumeric NUMERIC(16, 2)
)
GO
DECLARE @count INT
SET @count = 1
WHILE @count <= 100000
BEGIN
INSERT INTO dbo.RandomData
SELECT @count,
CASE
WHEN Datepart(MILLISECOND, Getdate()) >= 500 THEN 0
ELSE 1
END AS [SomeBit],
Char((Abs(Checksum(Newid())) % 26) + 97)
+ Char((Abs(Checksum(Newid())) % 26) + 97) AS [SomeVarchar],
Dateadd
(
MILLISECOND,
(Abs(Checksum(Newid())) % 6000 ) * -1,
Dateadd(MINUTE, ( Abs(Checksum(Newid())) % 1000000 ) * -1, Getdate())
) AS [SomeDateTime],
(
Abs(Checksum(Newid())) % 100001 )
+ ( ( Abs(Checksum(Newid())) % 100001 )
* 0.00001
) AS [SomeNumeric]
SET @count += 1
END
SELECT *
FROM dbo.RandomData
Q2. Query to do batch insert with delay of 1 second
-- Creating Sample Data
IF Object_id('dbo.SourceTable') IS NOT NULL
BEGIN
DROP TABLE dbo.A
END
CREATE TABLE SourceTable
(
id INT
)
-- Creating Sample Data
IF Object_id('dbo.DestinationTable') IS NOT NULL
BEGIN
DROP TABLE dbo.A
END
CREATE TABLE DestinationTable
(
id INT
)
DECLARE @count INT
SET @count = 1
WHILE @count <= 100000
BEGIN
INSERT INTO dbo.SourceTable
SELECT @count
SET @count += 1
END
SELECT Count(*)
FROM dbo.SourceTable
-- Solution 1: Batch Insert Query
DECLARE @UK_ID INT
DECLARE @batchSize INT
DECLARE @results INT
SET @results = 1 -- stores the row count after each successful batch
SET @batchSize = 50000 -- How many rows you want to operate on each batch
SELECT @UK_ID = Min(ID)
FROM dbo.A
-- when 0 rows are returned, exit the loop
WHILE ( @results > 0 )
BEGIN
INSERT INTO dbo.DestinationTable
(ID)
SELECT *
FROM dbo.SourceTable
WHERE id >= @UK_ID
AND id < @UK_ID + @batchSize
SET @results = @@ROWCOUNT
-- next batch
SET @UK_ID = @UK_ID + @batchSize
WAITFOR DELAY '00:00:01'
END
Q3. Query to Swap Columns Values in SQL Server
CREATE TABLE SampleData ( Col1 INT, Col2 INT )
INSERT INTO SampleData VALUES (1, 2), (3, 4)
SELECT * FROM SampleData
UPDATE SampleData
SET Col1 = Col2,
Col2 = Col1
SELECT * FROM SampleData
Q4. What would be output of below Query
SELECT 23A
SELECT 'A' = 23
SELECT A = 23
SELECT *
FROM (
VALUES(1),(2),(3)
) t(a)
OR
CREATE TABLE my_table(a INT)
INSERT INTO my_table(a)
VALUES(1),(2),(3);
SELECT * FROM my_table
CREATE TABLE my_table(a INT, b INT)
INSERT INTO my_table(a, b)
VALUES(1,1),(2,2),(3,3);
SELECT * FROM my_table
Q5. Query to find Second Highest Salary of Employee
CREATE TABLE Employee (EmpID INT, EmpName NVARCHAR(255), Salary MONEY, MgrID INT)
INSERT INTO Employee
VALUES
(1, 'Siva', 10000, NULL),
(2, 'Chaitanya', 9000, 1),
(3, 'Ashutosh', 8000, 1),
(4, 'Sameer', 7000, 1)
-- DELETE FROM Employee
SELECT * FROM Employee
Select *
from Employee e1
where 2 = (Select count(distinct Salary)
from Employee e2
where e1.salary<=e2.salary);
Q6. Query to find duplicate rows from a table
-- DROP TABLE dbo.DuplicateData
CREATE TABLE dbo.DuplicateData
(
ID INT,
Name1 NVARCHAR(255)
)
INSERT INTO dbo.DuplicateData
VALUES
(1, 'Sam'),
(1, 'Sam'),
(2, 'Ron'),
(3, 'Andrew'),
(3, 'Andrew'),
(4, 'Michel'),
(4, 'Michel')
SELECT * FROM dbo.DuplicateData
-- JUST Display Unique Values
SELECT ID, Name1
FROM dbo.DuplicateData
GROUP BY ID, Name1
;
WITH CTE(RowID, ID, Name1)
AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID, Name1) AS RowID, ID, Name1
FROM dbo.DuplicateData
)
select * from CTE
WHERE RowID = 1
-- Deleting Duplicate Records
ALTER TABLE dbo.DuplicateData ADD PKID INT NOT NULL identity(1,1)
ALTER TABLE dbo.DuplicateData ADD Rnk INT
UPDATE dd1
SET dd1.Rnk = dd2.Rnk
FROM dbo.DuplicateData dd1
INNER JOIN
(
SELECT ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID, Name1) AS Rnk, PKID
FROM dbo.DuplicateData
) dd2 ON dd1.PKID = dd2.PKID
DELETE FROM dbo.DuplicateData
where Rnk <> 1
SELECT * FROM dbo.DuplicateData
-- Deleting Duplicate Records using CTE
;
WITH CTE(RowID, ID, Name1)
AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID, Name1) AS RowID, ID, Name1
FROM dbo.DuplicateData
)
DELETE FROM CTE
WHERE RowID <> 1
SELECT * FROM dbo.DuplicateData
Q7. Query to find duplicate values in a table with a unique index
CREATE TABLE dbo.DuplicateData1
(
ID INT NOT NULL IDENTITY(1,1),
Value INT,
CONSTRAINT PK_ID PRIMARY KEY(ID)
)
INSERT INTO dbo.DuplicateData1(Value)
VALUES(1),(2),(3),(4),(5),(5),(3),(5)
SELECT *
FROM dbo.DuplicateData1
-- Retaining MaxID
SELECT a.*
FROM dbo.DuplicateData1 a
INNER JOIN (SELECT Max(ID) AS ID,
Value
FROM dbo.DuplicateData1
GROUP BY Value
HAVING Count(Value) > 1) b
ON a.ID < b.ID
AND a.Value = b.Value
SELECT a.*
FROM DuplicateData1 a
WHERE ID < (SELECT Max(ID)
FROM DuplicateData1 b
WHERE a.Value = b.Value
GROUP BY Value
HAVING Count(Value) > 1)
SELECT a.*
FROM DuplicateData1 a
INNER JOIN (SELECT ID,
Rank()
OVER(
PARTITION BY Value
ORDER BY ID DESC) AS rnk
FROM DuplicateData1) b
ON a.ID = b.ID
WHERE b.rnk > 1
SELECT *
FROM DuplicateData1
WHERE ID NOT IN (SELECT MAX(ID)
FROM DuplicateData1
GROUP BY Value)
Q8. Query to split comma separated values to column values
IF Object_id('dbo.Employee') IS NOT NULL
BEGIN
DROP TABLE dbo.Employee
END
CREATE TABLE dbo.Employee
(
ID INT NOT NULL PRIMARY KEY IDENTITY(1, 1),
EmployeeName NVARCHAR(255),
Roles NVARCHAR(255)
)
INSERT INTO dbo.Employee
(EmployeeName,
Roles)
VALUES ('John Miller',
'Therapist,Manager'),
('Smith Johnson',
'Manager,Admin,Owner')
SELECT *
FROM dbo.Employee
SELECT *
FROM dbo.Employee e
CROSS APPLY String_split(Roles, ',')
Alternatively, We can write our own function for previous versions of sql server
CREATE FUNCTION [dbo].[Fnsplitstring] (@string NVARCHAR(MAX),
@delimiter CHAR(1))
RETURNS @output TABLE(
splitdata NVARCHAR(MAX))
BEGIN
DECLARE @start INT,
@end INT
SELECT @start = 1,
@end = Charindex(@delimiter, @string)
WHILE @start < Len(@string) + 1
BEGIN
IF @end = 0
SET @end = Len(@string) + 1
INSERT INTO @output
(splitdata)
VALUES (Substring(@string, @start, @end - @start))
SET @start = @end + 1
SET @end = Charindex(@delimiter, @string, @start)
END
RETURN
END
SELECT *
FROM dbo.Employee e
CROSS APPLY dbo.[Fnsplitstring](Roles, ',')
Another one
CREATE FUNCTION [dbo].Stringsplitxml (@String VARCHAR(MAX),
@Separator CHAR(1))
RETURNS @RESULT TABLE(
Value VARCHAR(MAX))
AS
BEGIN
DECLARE @XML XML
SET @XML = Cast(( '<i>'
+ Replace(@String, @Separator, '</i><i>')
+ '</i>' ) AS XML)
INSERT INTO @RESULT
SELECT t.i.value('.', 'VARCHAR(MAX)')
FROM @XML.nodes('i') AS t(i)
WHERE t.i.value('.', 'VARCHAR(MAX)') <> ''
RETURN
END
SELECT *
FROM dbo.Employee e
CROSS APPLY dbo.StringSplitXML(Roles, ',')
Q9. Query to get aggregated sales data at Month, Quarter, Year, Catagory & Subcategory levels
IF Object_id('dbo.Sales') IS NOT NULL
BEGIN
DROP TABLE dbo.Sales
END
CREATE TABLE dbo.Sales
(
ID INT NOT NULL PRIMARY KEY IDENTITY(1, 1),
SaleDate DateTime,
ItemName NVARCHAR(50),
Catergory NVARCHAR(50),
Subcatergory NVARCHAR(50),
Quantity INT,
FinalSalePrice MONEY
)
INSERT INTO dbo.Sales
(
SaleDate,
ItemName,
Catergory,
Subcatergory,
Quantity,
FinalSalePrice
)
VALUES
('2019-12-19 12:59:10.217', '60 Minute Body Massage', 'Services', 'Massage', 1, 125.00),
('2019-12-19 12:59:10.217', '60 Minute Body Massage', 'Services', 'Massage', 1, 125.00),
('2019-12-19 12:59:10.217', '60 Minute Body Massage', 'Services', 'Massage', 1, 125.00),
('2019-12-19 12:59:10.217', '90 Minute Body Massage', 'Services', 'Massage', 1, 150.00),
('2019-12-20 12:59:10.217', '60 Minute Body Massage', 'Services', 'Massage', 1, 125.00),
('2019-12-20 12:59:10.217', 'Hair Color', 'Services', 'Hair', 1, 125.00),
('2019-12-20 12:59:10.217', '45 Minute Body Massage', 'Services', 'Massage', 1, 90.00),
('2019-12-21 12:59:10.217', 'Hair Cut', 'Services', 'Hair', 1, 125.00),
('2019-12-21 12:59:10.217', '60 Minute Body Massage', 'Services', 'Massage', 1, 125.00),
('2019-12-21 12:59:10.217', '120 Minute Body Massage', 'Services', 'Massage', 1, 200.00)
SELECT GROUPING_ID(Year(SaleDate), DateName(MONTH, SaleDate), Catergory, Subcatergory, ItemName) AS GroupingID,
Year(SaleDate) AS Year,
DateName(MONTH, SaleDate) AS Month,
Catergory,
Subcatergory,
ItemName,
Sum(FinalSalePrice) AS TotalSales
FROM dbo.Sales
GROUP BY GROUPING SETS
(
( Year(SaleDate)),
( Year(SaleDate), DateName(MONTH, SaleDate) ),
( Year(SaleDate), DateName(MONTH, SaleDate), Catergory ),
( Year(SaleDate), DateName(MONTH, SaleDate), Catergory, Subcatergory ),
( Year(SaleDate), DateName(MONTH, SaleDate), Catergory, Subcatergory, ItemName ) )
Q10. Write An SQL Query To Show Only Even Rows From A Table
IF Object_id('dbo.Sales') IS NOT NULL
BEGIN
DROP TABLE dbo.SampleData
END
CREATE TABLE dbo.SampleData
(
ID INT IDENTITY NOT NULL,
Details nvarchar(50)
)
INSERT INTO dbo.SampleData
(
Details
)
VALUES
('John Smith'),
('Millinda Mechel'),
('Marsh'),
('Mikel'),
('Rosana'),
('Joseph'),
('Alina')
SELECT * FROM dbo.SampleData
SELECT * FROM dbo.SampleData WHERE ID % 2 = 0;
Q11. Write An SQL Query To Fetch The List Of Employees With The Same Salary
IF Object_id('dbo.Employee') IS NOT NULL
BEGIN
DROP TABLE dbo.Employee
END
CREATE TABLE dbo.Employee
(
ID INT NOT NULL PRIMARY KEY IDENTITY(1, 1),
EmployeeName NVARCHAR(255),
Salary MONEY
)
INSERT INTO dbo.Employee
(EmployeeName,
Salary)
VALUES ('John Miller',
11000),
('Smith Johnson',
15000),
('John Wick',
11000),
('Alan Jon',
13000)
SELECT *
FROM dbo.Employee
SELECT *
FROM dbo.Employee e1, dbo.Employee e2
WHERE e1.Salary = e2.Salary
AND e1.ID != e2.ID
Q12 Write Query to display employee hierarchy
IF Object_id('dbo.Employee') IS NOT NULL
BEGIN
DROP TABLE dbo.Employee
END
CREATE TABLE dbo.Employee
(
EmployeeID INT NOT NULL PRIMARY KEY,
EmployeeName NVARCHAR(255),
Salary MONEY,
ManagerID INT
)
INSERT INTO dbo.Employee
(EmployeeID,
EmployeeName,
Salary,
ManagerID)
VALUES (1000,
'John Miller',
11000,
NULL),
(1001,
'Smith Johnson',
15000,
1000
),
(1002,
'John Wick',
11000,
1000),
(1003,
'Alan Jon',
13000,
1001)
SELECT *
FROM dbo.Employee
SELECT e.EmployeeID,
e.EmployeeName,
m.EmployeeID AS ManagerID,
m.EmployeeName AS ManagerName
FROM dbo.Employee e
LEFT JOIN dbo.Employee m
ON e.ManagerID = m.EmployeeID
;
WITH EmpHierarchy(EmployeeID, EmployeeName, EmployeeLevel, ManagerID, ManagerName)
AS
(
SELECT
e.EmployeeID,
e.EmployeeName,
0 AS EmployeeLevel,
NULL AS ManagerID,
CAST('' AS NVARCHAR(255)) AS ManagerName
FROM dbo.Employee e
WHERE e.ManagerID IS NULL
UNION ALL
SELECT e.EmployeeID,
e.EmployeeName,
m.EmployeeLevel + 1,
m.EmployeeID AS ManagerID,
m.EmployeeName AS ManagerName
FROM dbo.Employee e
INNER JOIN EmpHierarchy m ON m.EmployeeID = e.ManagerID
)
SELECT * FROM EmpHierarchy
Q13. Write SQL Query To Show One Row Twice In Results From a Table
IF Object_id('dbo.Employee') IS NOT NULL
BEGIN
DROP TABLE dbo.Employee
END
CREATE TABLE dbo.Employee
(
ID INT NOT NULL PRIMARY KEY IDENTITY(1, 1),
EmployeeName NVARCHAR(255),
Salary MONEY
)
INSERT INTO dbo.Employee
(EmployeeName,
Salary)
VALUES ('John Miller',
11000),
('Smith Johnson',
15000),
('John Wick',
11000),
('Alan Jon',
13000)
SELECT *
FROM dbo.Employee
SELECT *
FROM dbo.Employee
UNION ALL
SELECT *
FROM dbo.Employee
Q14. Write An SQL Query To Fetch The First 50% Records From A Table
IF Object_id('dbo.Employee') IS NOT NULL
BEGIN
DROP TABLE dbo.Employee
END
CREATE TABLE dbo.Employee
(
ID INT NOT NULL PRIMARY KEY IDENTITY(1, 1),
EmployeeName NVARCHAR(255),
Salary MONEY
)
INSERT INTO dbo.Employee
(EmployeeName,
Salary)
VALUES ('John Miller',
11000),
('Smith Johnson',
15000),
('John Wick',
11000),
('Alan Jon',
13000)
SELECT *
FROM dbo.Employee
SELECT *
FROM dbo.Employee
WHERE ID <= (SELECT count(ID)/2 from dbo.Employee);
Q15. Write a SQL Query to Check if first alphabet of column is Capitalized
IF Object_id('dbo.Customers') IS NOT NULL
BEGIN
DROP TABLE dbo.Customers
END
CREATE TABLE dbo.Customers
(
ID INT NOT NULL PRIMARY KEY IDENTITY(1, 1),
CustomerName NVARCHAR(255)
)
INSERT INTO dbo.Customers
(CustomerName)
VALUES ('John Miller'),
('smith Johnson'),
('John Wick'),
('alan Jon')
SELECT *
FROM dbo.Customers
SELECT *
FROM dbo.Customers
WHERE SUBSTRING(CustomerName, 1, 1) <> UPPER(SUBSTRING(CustomerName, 1, 1)) COLLATE LATIN1_GENERAL_CS_AS
Q16. Write SQL Query to Schedule Matches between teams such that each team play a match with another.
IF OBJECT_ID('dbo.Team') IS NOT NULL BEGIN DROP TABLE dbo.Team END
CREATE TABLE [Team]
(
[TeamID] INT
,[TeamName] VARCHAR(10)
)
GO
INSERT INTO [Team] VALUES
(1,'Team1'),
(2,'Team2'),
(3,'Team3'),
(4,'Team4')
GO
SELECT * FROM [Team]
SELECT ROW_NUMBER() OVER(ORDER BY e1.[TeamID]) AS MatchID,
CONCAT(e1.[TeamName] , ' Vs ', e2.[TeamName])
FROM [Team] e1
CROSS APPLY
(
SELECT *
FROM [Team] e2 where e1.[TeamID] < e2.[TeamID]
) e2
order by e1.[TeamID]
Q17. Write SQL Query to Schedule Matches between teams such that each team in Group A play a match with team in Group B.
IF OBJECT_ID('dbo.Team') IS NOT NULL BEGIN DROP TABLE dbo.Team END
CREATE TABLE [Team]
(
[TeamID] INT,
[TeamName] NVARCHAR(50),
[Group] NVARCHAR(50)
)
GO
INSERT INTO [Team] VALUES
(1,'Team1', 'A'),
(2,'Team2', 'A'),
(3,'Team3', 'B'),
(4,'Team4', 'B')
GO
SELECT * FROM [Team]
SELECT ROW_NUMBER() OVER(ORDER BY e1.[TeamID]) AS MatchID,
CONCAT(e1.[TeamName] , ' Vs ', e2.[TeamName])
FROM [Team] e1
CROSS APPLY
(
SELECT *
FROM [Team] e2
WHERE e2.[Group] = 'B'
) e2
WHERE e1.[Group] = 'A'
order by e1.[TeamID]
Q18. List Down Different ways to add date and time
DECLARE @Dt DATE = '2018-08-10',
@Tm TIME = '07:48:46.4566667'
SELECT Cast(@Dt AS DATETIME)
+ Cast(@Tm AS DATETIME) [DateTime]
SELECT Dateadd(MILLISECOND, Datediff(MILLISECOND, '00:00:00', @Tm), CONVERT(DATETIME, @Dt)) [DateTime]
SELECT Dateadd(DAY, Datediff(DAY, 0, @Dt), Cast(@Tm AS DATETIME)) [DateTime]
SELECT Cast(Concat(@Dt, ' ', Cast(@Tm AS VARCHAR(12))) AS DATETIME) [DateTime]
Q20. Write SQL Query's to list down all stored procedures in a database
SELECT DISTINCT o.name,
o.xtype
FROM sysobjects o
WHERE o.xtype = 'P'
SELECT *
FROM SYS.PROCEDURES
Q21. Write SQL Query to fetch all stored procedures related to table
SELECT DISTINCT o.name,
o.xtype
FROM syscomments c
INNER JOIN sysobjects o
ON c.id = o.id
WHERE o.xtype = 'P'
AND c.text LIKE '%Invoice%' -- Table Name
Q22. T-SQL Query to get all dependency of stored procedure
;WITH stored_procedures
AS (SELECT oo.name AS table_name,
Row_number()
OVER(
partition BY o.name, oo.name
ORDER BY o.name, oo.name) AS row
FROM sysdepends d
INNER JOIN sysobjects o
ON o.id = d.id
INNER JOIN sysobjects oo
ON oo.id = d.depid
WHERE o.xtype = 'P'
AND o.name LIKE '%GetPOInvoiceForVendorEmail%')
SELECT Table_name
FROM stored_procedures
WHERE row = 1
Q23. SQL Query to rebuild all indexes
EXEC Sp_msforeachtable
@command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"
GO
EXEC Sp_updatestats
GO
Q24. Find Data Size of all tables in database
SELECT sob.name AS Table_Name,
Sum(sys.length) AS [Size_Table(Bytes)]
FROM sysobjects sob,
syscolumns sys
WHERE sob.xtype = 'u'
AND sys.id = sob.id
GROUP BY sob.name
Q25. Get List of tables without Identity Column
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE Table_NAME NOT IN (SELECT DISTINCT c.TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS c
INNER JOIN sys.identity_columns ic
ON ( c.COLUMN_NAME = ic.NAME ))
AND TABLE_TYPE = 'BASE TABLE'
No comments:
Post a Comment