|
TableA
|
TableB
|
|||||
|
EmpId
|
DeptId
|
Sal
|
EmpId
|
Name
|
Age
|
|
|
1
|
A
|
10000
|
1
|
A
|
10000
|
|
|
2
|
B
|
19000
|
3
|
A
|
12000
|
|
|
3
|
A
|
12000
|
5
|
A
|
18000
|
|
|
4
|
B
|
30000
|
6
|
B
|
70000
|
|
|
5
|
C
|
18000
|
||||
|
6
|
B
|
17000
|
||||
|
TableC
|
||||||
|
AgreementID
|
Dr/Cr
|
Amount
|
||||
|
1
|
D
|
10000
|
||||
|
2
|
C
|
19000
|
||||
|
1
|
C
|
12000
|
||||
|
2
|
D
|
30000
|
||||
|
2
|
C
|
18000
|
||||
|
1
|
D
|
70000
|
||||
|
3
|
D
|
29000
|
||||
|
TableD
|
||
|
EMPPLOYEEID
|
NAME
|
MANAGERID
|
|
1
|
RAM
|
5
|
|
2
|
SHYAM
|
5
|
|
3
|
GHANSHYAM
|
5
|
|
4
|
SHANKAR
|
NULL
|
|
5
|
DEEPAK
|
4
|
|
6
|
SANDEEP
|
4
|
1.
From the above tableA, in single update statement the existing DeptId value
should be change as below
A
=> B
B
=> A
C
=> B
Answer
CREATE TABLE #TableA
(
EmpId INT,
DeptId CHAR(1),
Sal MONEY
)
INSERT INTO #TableA
VALUES (1,'A',10000),
(2,'B',19000),
(3,'A',12000),
(4,'B',30000),
(5,'C',18000),
(6,'B',70000)
SELECT * FROM #TableA
|
EmpId
|
DeptId
|
Sal
|
|
1
|
A
|
10000
|
|
2
|
B
|
19000
|
|
3
|
A
|
12000
|
|
4
|
B
|
30000
|
|
5
|
C
|
18000
|
|
6
|
B
|
70000
|
UPDATE #TableA SET DeptId = CASE WHEN DeptId = 'A' THEN 'B'
WHEN DeptId = 'B' THEN 'A'
WHEN DeptId = 'C' THEN 'B' END
SELECT * FROM #TableA
|
EmpId
|
DeptId
|
Sal
|
|
1
|
B
|
10000
|
|
2
|
A
|
19000
|
|
3
|
B
|
12000
|
|
4
|
A
|
30000
|
|
5
|
B
|
18000
|
|
6
|
A
|
70000
|
2.
Get second highest salary from the above TableA in single query statement.
Answer
SELECT TOP 1 SAL FROM #TABLEA WHERE SAL < (SELECT MAX(SAL) FROM #TABLEA) ORDER BY SAL DESC
3.
Get the EmpId from TableA which are not available in TableB.
Answer
CREATE TABLE #TableA
(
EmpId INT,
DeptId CHAR(1),
Sal MONEY
)
INSERT INTO #TableA
VALUES (1,'A',10000),
(2,'B',19000),
(3,'A',12000),
(4,'B',30000),
(5,'C',18000),
(6,'B',70000)
CREATE TABLE #TableB
(
EmpId INT,
Name CHAR(1),
Age MONEY
)
INSERT INTO #TableB
VALUES (1,'A',10000),
(3,'A',12000),
(5,'C',18000),
(6,'B',70000)
SELECT A.EMPID,A.DEPTID,A.SAL
FROM #TABLEA A
LEFT JOIN #TABLEB B ON A.EMPID = B.EMPID
WHERE B.EMPID IS NULL
|
EMPID
|
DEPTID
|
SAL
|
|
2
|
B
|
19000
|
|
4
|
B
|
30000
|
4. In DML statement can we have function?
Answer
Yes
5.
Get all Credit & Debit Transaction separately agreementID wise from the
above TableC.
Answer
CREATE TABLE #TABLEC
(
AGREEMENTID INT,
DRCR CHAR(1),
AMOUNT MONEY
)
INSERT INTO #TABLEC
VALUES (1,'D',10000),
(2,'C',19000),
(1,'C',12000),
(2,'D',30000),
(2,'C',18000),
(1,'D',70000),
(3,'D',29000)
SELECT AGREEMENTID,SUM(DEBITAMOUNT) 'DEBITAMOUNT',SUM(CREDITAMOUNT) 'CREDITAMOUNT'
FROM
(
SELECT
AGREEMENTID,
CASE WHEN DRCR = 'D' THEN SUM(AMOUNT) ELSE '0' END AS 'DEBITAMOUNT',
CASE WHEN DRCR = 'C' THEN SUM(AMOUNT) ELSE '0' END AS 'CREDITAMOUNT'
FROM #TABLEC
GROUP BY AGREEMENTID,DRCR
) A
GROUP BY AGREEMENTID
|
AGREEMENTID
|
DEBITAMOUNT
|
CREDITAMOUNT
|
|
1
|
80000
|
12000
|
|
2
|
30000
|
37000
|
|
3
|
29000
|
0
|
6. Get the Balance AgreementID wise.
Answer
CREATE TABLE #TABLEC
(
AGREEMENTID INT,
DRCR CHAR(1),
AMOUNT MONEY
)
INSERT INTO #TABLEC
VALUES (1,'D',10000),
(2,'C',19000),
(1,'C',12000),
(2,'D',30000),
(2,'C',18000),
(1,'D',70000),
(3,'D',29000)
SELECT * FROM #TABLEC ORDER BY AGREEMENTID
SELECT AGREEMENTID,SUM(DEBITAMOUNT) 'DEBITAMOUNT',SUM(CREDITAMOUNT) 'CREDITAMOUNT',(SUM(DEBITAMOUNT)-SUM(CREDITAMOUNT))'BALANCE'
FROM
(
SELECT
AGREEMENTID,
CASE WHEN DRCR = 'D' THEN SUM(AMOUNT) ELSE '0' END AS 'DEBITAMOUNT',
CASE WHEN DRCR = 'C' THEN SUM(AMOUNT) ELSE '0' END AS 'CREDITAMOUNT'
FROM #TABLEC
GROUP BY AGREEMENTID,DRCR
) A
GROUP BY AGREEMENTID
|
AGREEMENTID
|
DEBITAMOUNT
|
CREDITAMOUNT
|
BALANCE
|
|
1
|
80000
|
12000
|
68000
|
|
2
|
30000
|
37000
|
-7000
|
|
3
|
29000
|
0
|
29000
|
Or
you can Use PIVOT
SELECT AGREEMENTID,D'DEBITAMOUNT',C'CREDITAMOUNT',(ISNULL(D,0)-ISNULL(C,0))'BALANCE'
FROM (
SELECT AGREEMENTID, DRCR, SUM(AMOUNT)AMOUNT
FROM #TABLEC
GROUP BY AGREEMENTID, DRCR
) AMT
PIVOT (
MAX(AMOUNT) FOR DRCR IN (D, C)
) RESULT;
|
AGREEMENTID
|
DEBITAMOUNT
|
CREDITAMOUNT
|
BALANCE
|
|
1
|
80000
|
12000
|
68000
|
|
2
|
30000
|
37000
|
-7000
|
|
3
|
29000
|
NULL
|
29000
|
7a.
Refer
TableD and Write a query to get the following output.
|
EMPLOYEE
|
MANAGER
|
|
RAM
|
DEEPAK
|
|
SHYAM
|
DEEPAK
|
|
GHANSHYAM
|
DEEPAK
|
|
SHANKAR
|
NULL
|
|
DEEPAK
|
SHANKAR
|
|
SANDEEP
|
SHANKAR
|
Answer
CREATE TABLE #TABLED
(
EMPPLOYEEID INT,
NAME VARCHAR(10),
MANAGERID INT
)
INSERT INTO #TABLED VALUES(1,'RAM',5)
INSERT INTO #TABLED VALUES(2,'SHYAM',5)
INSERT INTO #TABLED VALUES(3,'GHANSHYAM',5)
INSERT INTO #TABLED VALUES(4,'SHANKAR',NULL)
INSERT INTO #TABLED VALUES(5,'DEEPAK',4)
INSERT INTO #TABLED VALUES(6,'SANDEEP',4)
SELECT * FROM #TABLED
|
EMPPLOYEEID
|
NAME
|
MANAGERID
|
|
1
|
RAM
|
5
|
|
2
|
SHYAM
|
5
|
|
3
|
GHANSHYAM
|
5
|
|
4
|
SHANKAR
|
NULL
|
|
5
|
DEEPAK
|
4
|
|
6
|
SANDEEP
|
4
|
Using
self join you can do it.
SELECT A.NAME'EMPLOYEE', B.NAME'MANAGER'
FROM #TABLED A
LEFT JOIN #TABLED B ON A.MANAGERID = B.EMPPLOYEEID
|
EMPLOYEE
|
MANAGER
|
|
RAM
|
DEEPAK
|
|
SHYAM
|
DEEPAK
|
|
GHANSHYAM
|
DEEPAK
|
|
SHANKAR
|
NULL
|
|
DEEPAK
|
SHANKAR
|
|
SANDEEP
|
SHANKAR
|
7b.
int nxt = 0;
int a = 0;
int b = 1;
for (int c = 0; c < n; c++)
{
if (c
<= 1)
{
nxt = c;
}
else
{
nxt = a + b;
a = b;
b = nxt;
}
Console.Write(nxt);
}
Output:
If n=15, which one is correct from following one
a.
0, 1, 2, 3, 5, 8, 9
b.
0, 2, 2, 4, 6, 10
c.
0, 1, 1, 2, 3, 5, 8, 13
d.
0, 1, 3, 5, 8, 13
Answer
c. 0, 1, 1, 2, 3, 5, 8, 13
8.
int A = 10;
try
{
A = A + 12;
}
catch (Exception ex)
{
A = 0;
}
finally
{
A = 5;
}
Console.Write(A);
8.
What will be the output of A, in case of no error?
a.
10
b.
0
c.
22
d.
5
Answer
d. 5
9.
What will be the output of A, in case of error?
a.
10
b.
0
c.
22
d.
5
Answer
d. 5
10.
class BaseClass
{
protected int i =
13;
}
class Derived : BaseClass
{
int i =
9;
public void Fun()
{
// Add
Statement here
}
}
Which
of the following statements should be added to the subroutine Fun()
If
the C#.Net code snippet given below is to output 9 13?
a.
Console.WriteLine(base.i+" "+i);
b.
Console.WriteLine(i+" "+base.i);
c.
Console.WriteLine(mybase.i+" "+i);
d.
Console.WriteLine(i+" "+mybase.i);
e.
Console.WriteLine(i+" "+this.i);
Answer
b. Console.WriteLine(i+" "+base.i);
11.
class A
{
public void Fun()
{
Console.Write("Welcome");
}
}
class B:A
{
public void Fun()
{
// Add
statement here
Console.Write("
to learning world.");
}
}
class Program
{
static void Main(string[] args)
{
B b=new B();
b.Fun();
}
}
Which
statement will you add in the function Fun() of class B, if it is to produce
the output "Welcome to learning world."
a.
base.Fun();
b.
A::Fun();
c.
Fun();
d.
mybase.Fun();
e.
A.Fun();
Answer
a. base.Fun();
12.
How can you prevent inheritance from class in C#.Net?
a.
Declare the class as shadows.
b.
Declare the class as overloads.
c.
Declare the class as sealed.
d.
Declare the class as suppress.
e.
Declare the class as override.
Answer
c. Declare the class as sealed.
13.
In Asp.Net where we can apply the common Exception handling.
Answer
Global.asax
14.
class BaseClass
{
public void Fun()
{
Console.WriteLine("Hi"+"
");
}
public void Fun(int i)
{
Console.Write("Hello"+"
");
}
}
class Derived:BaseClass
{
public void Fun()
{
Console.Write("Bye"+"
");
}
}
class Program
{
static void Main(string[] args)
{
Derived d;
d=new Derived();
d.Fun();
d.Fun(77);
}
}
What
will be the output of above code snippet?
a.
The program gives the output as: Hi Hello Bye
b.
The program gives the output as: Bye Hello
c.
The program gives the output as: Hi Bye Hello
d.
Error in the program
Answer
b. The program gives the output as: Bye Hello
No comments:
Post a Comment