SQL Server - Q's - Only

   SQL Server



170.  ?????
169. How can we store a transaction result(either pass/fail) in a particular table?(Ans: Logging)
168. What is the use of CheckPoints?
167. How to see IDENTITY for the last inserted records ?
166. How can we insert the no of records to a table with out locking tables(at the same time someone doing
SELECT Operation on your table)?
165. Max  no of params for SP and User defined Function is : 2,100
163. F.K references for 1-table? (253)
164. Instances for Computer?
50 instances on a stand-alone server for all SQL Server editions.
SQL Server supports 25 instances on a failover cluster.

162. How many tables can be allowable in a single SQL Query?
256 according to Sql-2005 Version

161.w.a.q to get 4th Highest Salary?
Select TOP(1) SAL
From
    ( Select distinct top 4 SAL FROM EMPLOYEE
       Order by SAL Desc)  S
Order by SAL

160. after adding new Column Total in a table , how to update that value by adding related fields?
Update Employee
set DA=Basic * 40/100,
HRA=Basic * 30/100,
Dedc=Basic * 10/100
Total=(Basic+DA+HRA)-Dedc

159. Among Duplicate records , I wanna change 1st duplicate record to another values?
update TOP(1) Employee
set eno=999, ename='ramesh'
where eno=1200
---Here eno=1200 record will be replaced with eno=999 and other values also renamed if you mentioned in the query like ename.
158, To change column name of a table?
SP_Rename '<T.Name>.<Old_ColName>'  '<New Col Name>'
Note: But with Alter Command we can't change column name of a column.
157. w.a.q to display duplicate records from Employee table?
select eno,ename,sal,count(*) as Duplicates
from Employee
Group By eno, ename, sal
Having Duplicates>1
156.Highest Sal form each dept & employee count should be more than 3? and display sals in Descending order?
select deptno,max(sal) as SAL , count(*) as Empcount
from Employee
group by deptno
having count(*)>3
order by  SAL
155.Highest Sal form each dept & employee count should be more than 3? and
select deptno,max(sal) , count(*) as Empcount
from Employee
group by deptno
having count(*)>3
154. Highest Sal From each dept?
153. Total Sal Query: (Sal+ISNULL(Commision,0))
152.How can we Structure/Definition of a table?(Ans: SP_HELP <T.Name>)
151. Max No of DBs allowed per Sql Server instance ?(32,767)
150.Max no of connections allowed in sql server ,
& its Query to find no of connections.
====12thAug13
149.How to update a table based on another table (on common column basis)
1. How can we retrieve Alternative Rows from a table?
2. How can we do custom order on particular column?
3. Query process order: Select , order by, group by, where, from , top, having?
4. How can we select single max sal from no same max salaries?
5. How can we insert data into a table without create statement?
6. How to eliminate the Duplicate rows?
7. How to find top1 salaries in each department , the sal have the minimum of avgsal?
Select top 1 sal from EMP
Where sal>( Select avg(sal) from EMP
 Where deptno=all (or) IN(10,20,30)
8. Wt is Index ? wt is Default index in a Table?
9. Give me a Scenario for the L.O.j?
10. PERFORMANCE TUNNING IN SQLSERVER
11. Diff bw SP and Function?
12. What is a text qualifier?
13. DIFFERENCE B/W SQLSERVER 2005 AND 2008
14. MERGE DATATYPE
15. WHAT IS THE REPORT SERVER DATABASE
16. HOW YOU PROVIDE A SECURITY FOR THE REPORT
17. Which joins you used in Real Time?
18. Which one gives better performance in SSIS or in T-SQL?
SSIS
19. SQL  injection query service
Trigger
20. WRITE A QUEARY OF THE FIRST DAY OF THE MONTH
21. WHAT IS THE OUTPUT OF QUEARY SELECT * FROM EMP WHERE 1=0
22. WHAT IS THE OUTPUT OF QUEARY SELECT * FROM EMP WHERE 1=1
23. WHAT IS THE OUTPUT OF QUEARY SELECT * FROM EMP WHERE 1=NULL
24. WHAT IS THE OUTPUT OF QUEARY SELECT * FROM EMP WHERE NULL=NULL
25. WHICH ONE(TEMP TABLE AND TEMPTABLE VARIABLE) IS FASTEST
26. WRITE A QUEARY USING CTE TO GENERATE "HAI" TO THE BASE WORD
HAI
HAIA
HAIAA
HAIAAA
27. WRITE A UPDATE QUEARY UPDATE COLUMN AGE IN TABLE T1 FROM AGE COLUM IN T2(SINGLE QUEARY)
T1
NAME  AGE
A     27
B     34
C     29
T2
NAME  AGE
A      31
B       34
C       33
28.
29. Replication?
30. OPENROWSET?
31. Architecture changes in 2012?
32. .Can we use where clause in groupby clause?
33. define indexes, what is filtered index
34. can u create index on temp tables
35. difference between Rank & Dense Rank
36. tab1, tab2
Eno,  sal   eno, sal
111. 10000 111 3000
112. 45000 111 2000
123. 11000 111 1000
124. 12000 123 4000
Write query for tab 1 is emp sal, tab 2 is eno takes advance from his salary after deduction each emp sal to find current emp salaries?
37. difference between join and union?
38. table
eno,  ename
101. xxxx
102. xxy
103. ccc
104. vvv
null          ffff
null          bbb
select count(eno) from table
select count(1) from table
select count(*)   from table
select count(15) from table
39. what is query execution plan
40. LIST THE TYPES OF USER DEFINE FUNCTION IS SQLSERVER 2005
41. difference between stored procedure and triggers
42. ID   FIRSTNAME  MIDDLENAME     LASTNAME
  1     A           NULL            NULL
  2     NULL         B             NULL
  3     C            NULL             NULL
  4     NULL        NULL             D
OUTPUT
ID  NAME
1    A
2    B
3   C
4   D
WITHOUT USING NULLFUNCTION AND CASESTATEMENT
43. difference between cast and convert functions
44. difference between replace and stuff function, varchar and nvarchar
45. Diff bw Inner join and Left outer join?
46. Diff bw PK and UK?
47. Diff bw SUB Query and Corelated SubQuery?
48. Diff bw SQL Server 05/08?
49. Diff bw SQL Server 08/12?
50. Wt is Merge data type?
51. Diff bw Union and Join?
52. DIFFERENCR B/W STORED PROCEDURE AND FUNCTION AND PERFOMANCE WHICH IS BEST
53. What is the difference between global and temporary variable
54.
55. WRITE A SQLQUEARY TO CONCATENATE STRING VALUES SEPARATED BY COMMA FOR THE TABLE XYZ
NAME      AGE
ROBET     45
GARY      54
ERIC      34
BOB       23
OUTPUT
ROBET,GARY,ERIC,BOB
56.
57. Diff bw Temporary Table & Temp Variable?
58. Types of joins?
59. Which are the Types of SQL Table Objects?
60. Wt are the different Index configurations a table can have?
61. Sub Queries 3-Types?
62. Wt is COALSCES()
63. what is RANK(),ROWNUMBER(),DENSERANK()
64.
65. Diff bw Inner & Outer Joins?
66. .column1    column2
       1                     1
       1                     1
       1                     1
       1                     1
join the above two tables as Inner and left join
    column1    column2
       1                     1
       2                     2
       3                     3
       4                     4
    null                 null
join the above two tables
67. what is covering index?
Creating a non-clustered index that contains all the columns used in a SQL query, a technique called index covering
I can only suppose that a covered query is a query that has an index that covers all the columns in its returned recordset. One caveat - the index and query would have to be built as to allow the SQL server to actually infer from the query that the index is useful.
For example, a join of a table on itself might not benefit from such an index (depending on the intelligence of the SQL query execution planner):
PersonID ParentID Name
1        NULL     Abe
2        NULL     Bob
3        1        Carl
4        2        Dave
Let's assume there's an index on PersonID,ParentID,Name - this would be a covering index for a query like:
SELECT PersonID, ParentID, Name FROM MyTable
But a query like this:
SELECT PersonID, Name FROM MyTable LEFT JOIN MyTable T ON T.PersonID=MyTable.ParentID
Probably wouldn't benifit so much, even though all of the columns are in the index. Why? Because you're not really telling it that you want to use the triple index of PersonID,ParentID,Name.
Instead, you're building a condition based on two columns - PersonID and ParentID (which leaves out Name) and then you're asking for all the records, with the columns PersonID, Name. Actually, depending on implementation, the index might help the latter part. But for the first part, you're better off having other indexes.
68. Wt is CTE? how it can be used in a table?
69. Diff bw TempTable and CTE?
70. Diff bw Union & UnionAll?
71. Wt is Index ? types of indexes , why we use only 1-clustered index in table. Default clustered index?
72. Wt is Synonym, index.
73. DIFFERENCE B/W STUFF AND REPLACE
74. WHAT IS PIVOTTABLE
75. HOW TO RESET THE IDENTITY COLUMN
76. WHAT ARE THE USRE DEFINED DATATYPES AND WHEN YOU SHOULD GO FOR THEM
77. WHAT IS BIT DATATYPE AND WHAT'S IS INFORMATION THAT CAN BE STORED INSIDE THE BIT COLUMN
78. LIMATION OF SELECT STATEMENT
79. WHAT TYPES OF LOCKING
80. WHAT IS THE INDXSEEK AND INDEX SCAN
81. Diff B/W LOGINS AND USER
82. Scenario: (CTS)
Eid  ename  Deptid
101 a  20
Salary
Eid  sal
101 3000
    Dept
Deptid dname  location
20  Hr  bang
21  it  bang
22  sls  hyd
Output:
Location  total Salary total emps
-----------------------------------------------
Output:
Deptname
To find dname whose salaries are maximum of each dept wise
83. Wt is Temporary Table, how we use in the DataBase?
84. How to find duplicates in a Table?
85. Wt types of Functions available in SSMS?
86. Wt is Query Hint?
87. can we write delete on 3 tables
88. How to query performance increase using Stored Procedures?
89. Diff bw Truncate & Drop?
90. How many types of indexes in SSMS?
91. Wt is Self Referencing table in SQL Server?
92. Wt are the Dynamic Queries?
93. Wt is CTE?
94. What is the difference between SCRIPT task and script component
95. How can you rectify errors while executing a stored procedures?
96. What are the different index configurations a table can have?
97. Difference b/w Local Temporary and Global Temporary tables in SQL?
98. If we delete a table can we RollBack / not?
Ans: By Transactions we can do.
99. I just created one table without any key . wt type of Index created there?
100. Write a SP to insert value bw 10000 & 50000 only.
If we insert value bw the above the range print message like “Value not allow” otherwise print the value if it is in bw the Range?
101. Wt is constraint and its Types?
102. How can we get the List of User Tables?
103. Wt is Composite key?
104. Wt is IDENTITY?
105. Scenario:
eno  ename  mgrno
1  a  3
2  b  5
3  c  6
Query for Emp details whose eno & mgrno is Same?
106. Create table Syntax?
107. Update Command Syntax?
108. In a Data Base we have Five tables in that DB wt are the table(s) which have “Salary” column?
Ans:  Select Sysob+”.”+TableName
From Information_schema.Column
 where column=’Salary’
109. How to find which SP is executed on particular Table?
110. Syntax for Group By Operator?
111. column1     column2
        ab"cd               20
        "xyz"              30
  How can you eliminate the symbol( ") in the above table and load in to the destination?
112. Which type of join will be formed, when you just specify two tables in from clause without writing any join condition between two tables?
113. Can a table have more than 1-PK/not?
Ans:  A table has only 1-PK but a table columns have more than 1-PK.
114. Can a SP call another SP?
Ans: A SP can call 1/34 SPs or 1/34 functions But a Function can’t call SP.
Lly A function can call 34 Functions/1 function.
115. Write a Query to find the empname where salary is Max in that table?
116. Scenario:
Insert no from 1 to 1000 in a table?
Declare @i int
SET @i=1
While (@>=1000)
Begin
 @i=@i+1
Insert into Table Values(@i)
End
117. Change Balu to Kalu by STUFF() Function?
Ans: Select STUFF(‘Balu’,1,B,’K’)
118. Diff bw WHERE & HAVING?
119. Types of Data Types?
120. Cursor types? And syntax?
121. Triggers , usage?
122. Stored Procedures, Usage?
123. Functions, Replication, Built in Functions?
124. Normalization & BOYCE Code Normalization?
125. Diff bw SUB Query & View?
126. Diff bw Corelated & Nested Sub Query?
127. Scenario:
Eno  ename  salary
101  krishna  3000
101  venkat  4000
103  kishore  2000
104  kiran  7000
Print the Avg of salary from the same eno.
128. Syntax for CONVERT() Function?
129. Index, which index is Faster?
130. Union & UnionAll diff?
Which is Faster, why?
131. Merge, MergeJoin, which is Faster, & Why?
132. Delete & Truncate commands which is Faster and Why?
133. Joins, Triggers,
134. Steps to Create Cursor?
135. Wt is Candidate Key, Magic Table?
136. Row Data?
137. 18th Highest Salary?
138. Index Syntax?
139. Diff bw SP & Function?
140. BOB Tech Q’s
ID NAME AGE SALARY
1 ANDY 61 140000
2 RACHEL 34 44000
5 CHRIS 34 40000
7 CHANDLER 41 52000
8 ROSS 57 115000
11 JOE 38 38000
THE ABOVE IS THE “SalesPerson” Table
Oreders Table is Below given:
Name Order_date Clientid Salespersionid Amount
10 8-2-96 4 2 540
20 1-30-99 4 8 1800
30 7-14-95 9 1 460
40 1-29-98 7 2 2400
50 2-3-98 6 7 600
60 3-2-98 6 7 720
70 5-6-98 9 7 150
60 3-2-98 6 7 720
Client Table:
Id Name City Industrytype
4 Pija Hut Banglore
6 Dominos Hyderabd
7 Papa Coat Chennai
9 popajohns Mumbai
Q’s:
141. Names of all salespersons who have an order with Dominos
142. Names of all SPs who donot have any order with dominos & popajohns
143. Names of SPs that have 2 or more orders worth $100
144. Write a SQL Trigger to insert rows into a table called HighAchiever(Name,Age) when a SP sales amount >5000
145. W.a.q to delete any duplicate rows from orders table
146. Name of the person who have not made any sales or sales<50 in each year
147. Every sale amount , date of sale with the total amount of sales for that day.
148.