SQL Server Interview Questions and Answers Part 9

1.) What is Trigger ?                                                                                                                        
A trigger is an event that is executed when any condition (statement) is satisfied. 

2.) How to prevent' table creation' in database using concept of trigger  ?             
Create trigger ddl trigger on database 
for Create table
as 
begin
rollback
Print 'table can not be created'
end

3.) How to view all triggers in your database ?                                                                    
Select*from sys.triggers

4.) What are the types of triggers in sql server ?                                                                 

1. DML (Data Manipulation Language) Triggers
2. DDL(Data Definition Language ) Triggers
3. Logon Triggers
4. CLR Triggers

3

Read More...

SQL Server Interview Questions and Answers Part 8

1.) What is view in SQL Server ?                                                                                               
A View is a logical or virtual table,which does not exists physically in the database.View is an object which contains 'select' statement.View is consider like as Virtual table.

2.) What are the main purpose to create a view ?                                                               
We create views for two main purpose.

1. To make complex query to simple query
2. Database Security .

3.) Why we create view in sql server or other explain?                                                     

There is following reason to create a view in sql server or other database.

•  If we don't want to show all the column's data of the table to an user then we generally create a view.

3

Read More...

Sql Server Interview Questions and Answers Part 7

1.) What are the Joins Concepts in SQL Server ?                                                    

Joins are the concepts which are used to get the related data from more than one table in SQL Server.That is basically used to get the more than one related tables data by a single sql command.

2.) What are the types of Joins used in SQL Server ?                                              

1. Inner Join
2. Outer Join
3. Self Join
4. Cross Join

3.) What is Inner Join and why we use it in SQL Server ?                                      

In Inner Join , we get values from those table which have at least one column same.If no column value is same then we can't use inner join concepts on those tables.

If we want to get only those tables records whose values are same in all the joined tables. 

Ex.
select sid,sname,cname from  studentdetails  join scourse
on id=sid

4.) What is  Outer Join in SQL Server ?                                                                   

If we want to get matched as well as unmatched records from two or more joined tables then we can use outer join concepts. 

5.) What are the types of  Outer Join in SQL Server?                                              

1. Left Outer Join
2. Right outer join
3. Full outer join

6.) What is Left Outer Join in SQL Server?                                                             
If we want to show matched and unmatched records from left side table and matched record only right side table,then we can use left outer join concepts.
Ex.
select sid,sname,course_name,sage from scourse c Left join studentdetails s on c.id=s.sid  

7.) What is right Outer Join in SQL Server?                                                           
if we want to show matched ,unmatched records from right side table and matched records only left side table ,then we can use Right outer join concepts.
Ex.
select sid,sname,course_name from scourse c Right join studentdetails s 
on c.id=s.sid

8.) What is Full Outer Join in SQL Server?                                                             
If we want to display all matched and unmatched records from left and right side table then ,we can use full outer join concepts.
Ex. 
select sid,sname,course_name from scourse c  full outer join studentdetails s on c.id=s.sid

9.) What is Self Join concepts in SQL Server?                                                         
When the related data exist within the same table then we can implement self join concepts in SQL Server.
Ex.
Select s.sid,s.sname,T.sid ,T.sname from students s join students T on s.sid =T.sid

10.) What is Cross Join concepts in SQL Server?                                                    

If one table is join with another table without any column basis,so the total number of output will be one table row x second table row.
Ex.
select sid,sname,course_name from scourse c cross join students s

11.) What are the two sql commands that are used to get the same records form two or more tables? 

1. Select*from students,scourse
2. select sid,sname,course_name from scourse c cross join students s

3

Read More...

SQL Server Interview Questions and Answers Part 6

1.) What is Transaction ?                                                                                                         
 A Transaction insure that changes will be made or none of changes will be done.

2.) What are the four Properties of Transaction ?                                                             

• Atomicity
• Consistency
• Isolation
• Durability

3.) What are the types of Transaction in SQL Server?                                                     

• Implicit Transaction
• Explicit Transaction

3

Read More...

Sql Server Interview Questions and Answers Part 5

1.) What is Stored procedure in Sql server ?                                                                         
Stored Procedure is a set of SQL Statement or PL/SQL Programming which perform a specific task.There is no need to compile stored procedure again and again.

2.) How to create stored procedure in sql server ?                                                             
Syntax:-
Create Procedure Procedure_name
as
sql command....

Ex.

create procedure prcselect

     as

select *from student

3

Read More...

Sql Server Interview Questions and Answers Part 2

1.) How to delete a specific record(a Row) from a table in sql server?                  
delete from table_name where column_name =value
Ex.
delete from student where sid=103
2.) How to add a New Column in your Existing table using sql command ?          
alter table table_name add column_name datatype
Ex.
alter table student add saddress varchar(40)

3.) How to change the size of Existing column in a table ?                                            
alter table  table_name  alter column column_name datatype
Ex.
alter table student alter column saddress varchar(80)

3

Read More...

Sql Server Interview Questions and Answers Part 4

1.) Where we generally use Identity column Constraints on a table ?                       
If any user wants,one column value should  incremented automatically without any  intervention   of users then we generally use this constraints on the column.

 2.) How to create a table and add an Identity column on that  table?                    
Syntax:- 
Identity (<seed> < Incremented >)
Example:-

create table student(eid int identity(0,1),sname varchar(30), sage int)




3.) How to add Identity  column in Existing table ?                                                         

alter table student add sid int identity(0,1)

                OR

ALTER TABLE student ADD ToBeIdentity INT IDENTITY(0,1)

3

Read More...

Sql Server Interview Questions and Answers Part 1

1.) What is the use of SQL Server Management Studio ?                                                  
It is a software which is basically used for store and retrieve the data from the database.

2.) How to create a Database in sql server using command ?                                         
Create Database database_name
Ex.
Create Database School

3.) How to change existing database using command in sql server ?                        
Alter Database olddatabase_name modify Name=NewDatabase
Ex.
Alter Database school modify Name=college

3

Read More...