SQL Interview Questions and answers Part 1 | SQL Scenario based Interview Question and Answer  

SQL Interview Questions and answers Part 1 | SQL Scenario based Interview Question and Answer #sqlMS
SQL Hindi Playlist : • SQL Tutorials-Hindi
MS SQL English Playlist : • SQL Tutorial-English
#mssqlserver , #sqlserver , #mssql , #database , #technology , #nosql , #informationtechnology , #sqldeveloper , #dba , #mysqldeveloper , #sqlservergeeks , #sqlservers, #sqlserveres , #mysql , #databaseadministrator , #mysqlserver , #trends ,
#sql #sqlserver #education #sqlinterviewquestions #mssqlserver #sqlforbeginners #sqltutorial #sqltraining #sqlcourse
CREATE TABLE [dbo].[Transactions](
[CustID] [int] ,
[TranID] [int] ,
[TranAmt] [float] ,
[TranDate] [date]
)
INSERT [dbo].[Transactions] ([CustID], [TranID], [TranAmt], [TranDate]) VALUES (1001, 20001, 10000, CAST('2020-04-25' AS Date))
INSERT [dbo].[Transactions] ([CustID], [TranID], [TranAmt], [TranDate]) VALUES (1001, 20002, 15000, CAST('2020-04-25' AS Date))
INSERT [dbo].[Transactions] ([CustID], [TranID], [TranAmt], [TranDate]) VALUES (1001, 20003, 80000, CAST('2020-04-25' AS Date))
INSERT [dbo].[Transactions] ([CustID], [TranID], [TranAmt], [TranDate]) VALUES (1001, 20004, 20000, CAST('2020-04-25' AS Date))
INSERT [dbo].[Transactions] ([CustID], [TranID], [TranAmt], [TranDate]) VALUES (1002, 30001, 7000, CAST('2020-04-25' AS Date))
INSERT [dbo].[Transactions] ([CustID], [TranID], [TranAmt], [TranDate]) VALUES (1002, 30002, 15000, CAST('2020-04-25' AS Date))
INSERT [dbo].[Transactions] ([CustID], [TranID], [TranAmt], [TranDate]) VALUES (1002, 30003, 22000, CAST('2020-04-25' AS Date))
--Using Inner Join Suquery
select a.custID, a.TranID, a.TranAmt,(a.TranAmt/MaxAmt) As Ratio,a.TranDate FRom Transactions a
Inner Join
(select custID, Max(TranAmt) as MaxAmt FRom Transactions
Group by custID) b
ON A.CustID=b.CustID
--Using CTE
with cte (custID,TranID,TRanAmt) AS
(
select custID,TranID,TranAmt FRom Transactions
),
CTE2(CustID,MaxAmt) AS
(
select custID, Max(TranAmt) as MaxAmt FRom Transactions
Group by custID
)
select a.custID,a.TranID,a.TranAmt,(a.TranAmt/MaxAmt) As Ratio FRom cte a
Inner Join
cte2 b
ON A.CustID=b.CustID