Sql Server Interview questions part 2

Sunday 27 March 2011

12 .what is Constraint? How many types of constraints in SQL ?


Constraints are the way by Databse managing the data inegrity.
 
Types of constraint are:-
1)NOT Null
2)Default
3)Check
4)Rule
5)Primary
6)Unique
7)Foreign
 
 
 
13. CLR Integration ? what is Notification services 
 
Common Language Runtime (CLR ) in .NET applications using SQL Server 2005.
   Notification Services was designed to ease the pain of developing and 
deploying notification applications that generate personalized, timely information to
subscriber.
 
14.  differance between sql server 2000 and 2005?
1.sql server 2005 include interprise manger and 
queryanalyser together in same window we can open
many windowtabs in same place but in 2000 it is in different.
2. sql server 2005 support more new datatype like xml
3. we can make more database 2(paw(20))-1 in 2005 in
compareto 2000 where not possible so much database
4. in storeprocedure we can write try catch statemenet in2005 not in 2000 
 
 
15. 14.   SQL QUERY :::::  
2 Tables will be there Namely Employee having columns like 
Empid,Empname,Salary,Mgrid. Phone  Table having Empid and Phone number.
 Based on these some questions like this
 1) select all the Employees who 
does not have phone? 
2)Dispaly all managers from 
table(Manager id is same as Empid)
 3)How to know How many tables contain Empno as a column in database? 
4)Find duplicate rows in a table or if we have table with one column
which has many records which are not distinct. 
How to find out the distinct values from that column and number 
  of times it's repeated?
 5) How to delete the rows which are duplicate?
(Don't remove both duplicate records.)
 6)How to find the 6th highest salary?
   ANS:   
1) select all the Employees who does not have phone? 
select * from employees where empid not in (select empid from phone)
 2)Dispaly all managers from table(Manager id is same as Empid) 
select * from employees emp where emp.employeeid = emp.mgrid 
3)How to know How many tables contain Empno as a column in database? 
 select count(c.name) as Tables,c.name as Empno fromtest.sys.tables 
as t inner join test.sys.columns as c  onc.object_id=t.object_id 
and c.name='Empno' group by c.namehaving count (c.name) > 0 4)
Find duplicate rows in a table or if we have table with one column which 
has many records which are not distinct. How to find out the distinct 
values from that column and number  of times it's repeated? select 
salary,count(salary) as Repeat from employees group by salary 
having salary > 1 5) How to delete the rows which are duplicate?
 
(Don't remove both duplicate records.)WITH [T ORDERED BY ROWID] AS (SELECT ROW_NUMBER() OVER (ORDER BY product_name ASC) AS ROWID, * FROM product where product_name ='Scale') DELETE FROM [T ORDERED BY ROWID] WHERE ROWID <> 1 6)How to find the 6th highest salary? SELECT TOP 1 salary FROM ( SELECT DISTINCT TOP 6 salary FROM employee ORDER BY salary DESC) a ORDER BY salary   13.
What is the differnce between view and materialized view
Answer
# 1
 
A view will takes the output of the query. But a materialized 
view stores the output of the query which is not possible in 
view

  14. 
What is the difference between UNIQUE KEY and UNIQUE INDEX?
Answer
# 1
 
Unique Index and Unique Constraint are the same. They 
achieve same goal. SQL Performance is same for both.
 
Add Unique Constraint
ALTER TABLE dbo.<tablename> ADD CONSTRAINT
<namingconventionconstraint> UNIQUE NONCLUSTERED
(
<columnname>
) ON [PRIMARY]
 
Add Unique Index
CREATE UNIQUE NONCLUSTERED INDEX
<namingconventionconstraint> ON dbo.<tablename>
(
<columnname>
) ON [PRIMARY]
 
There is no difference between Unique Index and Unique 
Constraint. Even though syntax are different the effect is 
the same. Unique Constraint creates Unique Index to 
maintain the constraint to prevent duplicate keys. Unique 
Index or Primary Key Index are physical structure that 
maintain uniqueness over some combination of columns across 
all rows of a table. It is a convenient way to enforce a 
Unique Constraint for SQL Server.

 15.
Suppose take my name "Reddyvaraprasad" From source it is coming Reddy vara prasad Need to get As Reddyvara prasad/
Answer
# 1
 
select Replace('Reddyvaraprasad','vara','vara ')

 16. 
How to count the no of records of a table without using COUNT function?
Answer
# 3
 
select max(rownum) from table_name;

 17. QUESTION       write a query to remove  null following table areid    name101   dineshnull  jyothinull  bharathi102   sureshnull  shilpha103   prakeshnull   suma i want the output format like id     name101    dinesh102    suresh103    prakesh   ANS: select * from table where id is not null;   18.QUESTION:  wirte a query  to remove null? following table are col1   col2   col3dinesh null   nullnull   suresh  nullnull   null   rakesh   want the output like  col1    col2    col3dinesh  suresh   prkaesh ANS: 1
wirte a query to remove null? following table are col1 col2 col3 dinesh null null null suresh null null null prakesh i want the output like col1 col2 col3 dinesh suresh prkaesh
Answer
# 1
 
select max(col1) col1,max(col2) col2,max(col3) col3
from table;
 
 
col1    col2    col3
dinesh  suresh   prkaesh

  ANS 2
wirte a query to remove null? following table are col1 col2 col3 dinesh null null null suresh null null null prakesh i want the output like col1 col2 col3 dinesh suresh prkaesh
Answer
# 2
 
SELECT DISTINCT((SELECT COL1 FROM COLL WHERE COL1<>'NULL')),(SELECT COL2 FROM COLL WHERE COL2<>'NULL'),(SELECT COL3 FROM COLL WHERE COL3<>'NULL')
FROM COLL

  19. Question       write a query   filter the null value data following source?name  agejohn  30smith nullnull  24sharp 35i want output name agejohn 30sharp 35 ANS:  
Re: write a query filter the null value data following source? name age john 30 smith null null 24 sharp 35 i want output name age john 30 sharp 35
Answer
# 1
 
select * from test_1 where name is not null and age is not 
null;

  20.  
What are Global Temporary tables
Answer
# 3
 
Global temporary tables belongs to that session only
 
create global temporary table test_gbl
( l_number_no number,
  l_char_vc   varchar2(100)
) [on commit delete rows]
 
ON COMMIT DELETE ROWS:- It's a default one
If any commit will issued that total data of a table will
losses. But table is exit
 
To overcome this we have option
ON COMMIT PRESERVE ROWS:-
means, If commit will issue the data of a table willn't loss
up to end of the session. Is session ends the data will losses.
 

    21. 
What is the Query to print out the individual total number of duplicate row in sql.
Answer
# 1
 
ANS:
 
SELECT department_id,COUNT(department_id) AS "Occurrences"
FROM departments
GROUP BY department_id
HAVING ( COUNT(department_id) > 1 )
 
DEPARTMENT_ID Occurrences
-------------   -----------
80         2
60         3
 
--In my 'departments' table , i have not assigned department_id column as a "primary key / unique key"

   22.
What is the diff between Truncate table / delete <table name> purge
Answer
# 1
 
TRUNCATE:-IT WILL DELETE ALL THE EXISTING RECORDS IN A TABLE
 
SYNTAX:-TRUNCATE TABLE <TABLE NAME>
 
DELETE:-IT WILL DELETE THE RECORDS IN A TABLE DEPENDINFG ON 
THE GIVEN CONDITION.
 
SYNTAX:-DELETE FROM <TABLE NAME> WHERE <CONDITON>
 
 
PURGE:-AFTER PERFOMING DROP OPERATION THE TABLES WILL STORE 
AT UNDO LOGS SO TO DELETE THE DATA COMPLETELY FROM TAHE 
DATA BASE WE WILL PURGE THE RECYCLEBIN.
 
SYNTAX:-PURGE RECYCLEBIN.

 23. 
What is the result, when NULL is compared with NULL?
Answer
# 4
 
The answer is NULL vs NULL = FALSE

 
Re: What is the result, when NULL is compared with NULL?
Answer
# 3
 
if we check a Null value with another Null value as equal, 
the result will be false.

24.
CAN U CREATE A PRIMARY KEY WITH OUT UNIQUE INDEX.
Answer
# 1
 
YES WE CAN CREATE. FIRST CREATE NON UNIQUE INDEX THEN 
CREATE PRIMARY KEY.

25.
Types of joins?
Answer
# 3
 
1. Self Join
Self join is a query in which a table is Joined to itself.
 
Self joins are used to compare the values in a column to 
the other values in the same column in the same table.
 
2.Equi Join or Inner join
Equi join is a join in which the rows are retrieved the 
equality of the relation ship between columns in the tables.
 
Select only those rows that have values in common in the 
columns specified in the ON clause.
 
3.Non Equi Join or Outer Join
 
Left Outer Join:
 
Retrieves the matching records in both the table and non 
matching records in the first or left table.
Right Outer Join
 
retrieves the matching records in both the table and non 
matching records in the second or the right table.
 
Full Outer Join
 
Retrieves all the records (means matching and non matching 
records in both the tables).
  

      



  


26.   
  


      IF i write before / after insert / update trigger and i do rollback what   will happen?

  


      
Answer
# 1
   
A trigger May Not issue A TCL statement like COMMIT,ROLLBACK,SAVEPOINT.
So U Cant Write A TCL Command Inside A trigger.
      



  


27.   
  


      What is different between union and minus?

  


      
Answer
# 1
   
Let's consider the difference between Minus and Union using 
following examples.
 
1.create TABLE A AND B With similar structure
2.insert records in Table A and B.
3.Keep some records identical(here 2 rows).
4.find out the difference betwn 2 looking into the output.
 
 
CREATE TABLE A(NAME VARCHAR2(30));
  INSERT INTO A VALUES('A');
   INSERT INTO A VALUES('B');
   INSERT INTO A VALUES('C');
   INSERT INTO A VALUES('D');
COMMIT;
CREATE TABLE B(NAME VARCHAR2(30));
INSERT INTO b VALUES('A')
INSERT INTO b VALUES('B')
INSERT INTO b VALUES('Y')
INSERT INTO b VALUES('X')
COMMIT;
 
 
 
1) SELECT * FROM A
MINUS
SELECT * FROM B
 
NAME                          
------------------------------
C                             
D                             
2 rows selected
 
 
 
2)SELECT * FROM A
UNION
SELECT * FROM B
 
 
NAME                          
------------------------------
A                             
B                             
C                             
D                             
Y                             
x                             
6 rows selected
      



  





  


      Re: What is different between union and minus?

  


      
Answer
# 2
   
 
unoin:- This operator returns from all the queries(combined
through union) but not duplicate record will be display.
ex- A={1,2,3,4}
    B={2,3,4,5}
AUB={1,2,3,4,5}............
 
Minus:- This operator displays records which belongs to only
the first query.
ex:- A={1,2,3,4}
     B= {2,3,5}
A-B={1,4}...................
      



  


28.   
  


      What is a transaction?

  


      
Answer
# 2
   
It starts with a first executable statement and ends when 
commit or rollback statement occurs.
      



  


29.   
  


      What is difference between CHAR and VARCHAR2?What is the maximum SIZE   allowed for each type?

  


      
Answer
# 1
   
VARCHAR2(size):variable lenght character data maximum size 
is 4000 and minimum is 1
char2(size):fixed lenght character data of lenght size bytes
minimum and default is 1 and maximum is 2000.
  

      



  


30.    
  


      how u can find the n row from a table?

  


      
Answer
# 6
   
select *
from employees
where  rowid=(select max(rowid) a 
from employees)
      



  


 










Posted by
.net tips


at
00:12










































0
comments:
        
















Post a Comment



























Subscribe to:
Post Comments (Atom)