Interview questions and answers of database in sql server

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 change existing database using command in sql server ?

Alter Database olddatabase_name modify Name=NewDatabase
Ex.
Alter Database school modify Name=college

3.) How to update an existing record in a table ?

Update table_name set column1=value,column2=value where column3=value
Ex.
Update student set sname='mithilesh4.) How to Drop the Existing column from a table ?
alter table table_name drop column column_name
Ex.
alter table student drop column saddress',sage=25 where sid=103

4.) How to delete a row from a table in sql server?

Delete from table_name where column_name =value
Ex.
Delete from student where sid =105

5.) 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)

6.) 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)

7.) How to Drop the Existing column from a table ?

alter table table_name drop column column_name
Ex.
alter table student drop column saddress

8.) Is "INTO" Keyword is optional in insert statement ?

Yes,We can insert the data in table without Into Keyword.

9.) Which keyword is always used for any changes in Database or table ?

Alter

10.) How to change the column name in a existing table ?

sp_rename 'table_name.oldcolumn_name','NewColumnName','column'
Ex.
sp_rename 'student.sage','s_age','column'

11.) How to change a existing table in sql server using command ?

sp_rename ' oldtable_name ',' Newtable_name ','object'
Ex.
sp_rename 'student','student_details','object'

12.) How to check the structure schema of any table in sql server ?

sp_help table_name
Ex.
sp_help student

13.) How to remove a column name from a existing table. ?

Alter table table_name drop column column_name
Ex.
Alter table student drop column sage

14.) Is Sql command is case sensitive?

No, We can use small latter or capital latter for any operation.

15.) What is Data Integrity in sql server?

The Consistency of data is known as data Integrity.If Data integrity concepts is applied on table then data will become in consistence form.Means we can easily access a specific value from table easily.

16.) What types of data integrity concepts are used in sql sever?

Entity Integrity
Referential Integrity
Domain Integrity

17.) How can create primary key constraints on a table's column in sql server ?

create table student(sid int constraint pk_sid primary key,sname varchar(40),sage int)

18.) How can add primary key constraints on a Existing table in sql server ?

Before Creating Primary key on a table.First make table not null able as given below:
alter table student alter column sid int not null
Now create constraints on existing table's column:-
alter table student add primary key (sid).

19.) How to check whether constraints are present in table or not ?

sp_help constraint student

20.) What is Referential Integrity in Sql server ?

If a table column refer to another table column, then both column(parent, child) have same value as specify within parent column.we can't delete parent table if child table is exist in database.
here my two tables are:-
Student
Scourse
Example:-
alter table scource add constraint fk_sid foreign key(sid) references student(sid)

21.) What is Domain Integrity in Sql server ?

Domain Intigrity insures that values within the column should be according to the "Business logic" (within specify range).To implement this integrity we can specify the 'Check constraint' and 'Default constraint'.

22.) How to create constraints at column level on a table ?

create table studentdetails(sid int primary key,sname varchar(40),sage int)

23.) How to create constraints at table level in sql server?

Create table student (sid int, sname varchar(40),sage int,constraint pksid primary key( sid,sname,sage))

24.) 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.

25.) How to create a table and add an Identity column on that table?

Syntax:-
Identity ( < Incremented >)
Example:-
create table student(eid int identity(0,1),sname varchar(30), sage int)

26.) How to ON/OFF Identity column constraints in sql server using command ?

set identity_insert student on
set identity_insert student off

27.)Can we copy data from one table to another table using sql command ?

Yes.If Number of column in both table is same.

28.) How to create a new table at run time and copy the data from a existing table using sql command ?

select *into student2 from student1
Note:-Here student2 is a new table and student1 is a existing table.

29.) How to copy data in two existing table in sql server using command ?

Syntax:-
insert [first table_name] select *from[second table_name]
Example:-
insert students select *from studentdetails

30.) How to copy the data in two existing table if sequence of column name of both table are same ?

Syntax:-
insert into [first table_name](column2,column3,column1)select(column2,column3,column1)from [Second table_name]
Example:-
insert into student2(sid,sname,sage)select sid, sname,sage from student1

31.) What are the two sql statement used to remove the records from database ?

Syntax:
delete from Table_name where Clause.
Example:
delete from student where sid=101;
Syntax:
truncate table Table_name;
Example:
truncate table student;

32.) What is difference between delete and Truncate statement in sql server ?

In case of delete each row is deleted one by one and deleted entry is made within log file but In case of truncate table structure is drop and recreated rows are not deleted physically.
Truncate is faster than delete.
Within delete statement we can specify 'Where' clause but in truncate statement we can not specify the 'Where' clauses.
In case of Identity column the difference is,if we run delete statement then insert a new record then identity column value will be the next value of the last inserted values.But if we run truncate statement and insert a new record the next value seed value.
In case of Foreign key constraint the difference is, if child table does not have any record then we can run delete statement against parents table but we can not run truncate statement against parent table.
Delete is DML Statement and truncate is DDL Statement.
If record is deleted that can be recover but if record is truncated then can not recover.

33.) 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.

34.) How to create stored procedure in sql server ?

Syntax:-
Create Procedure Procedure_name
as
sql command....
Ex.
create procedure prcselect
as
select *from student

35.) What are the different ways to execute stored procedure in sql server ?

There are three different ways to execute stored procedure as given below:-
1.) execute prcselect
2.) exec prcselect
3.) prcselect

36.) Can we use out parameter in stored procedure statement ?

yes.

37.) What are the benefits of stored procedure?

No need to recompile the code again and again.
It is also more helpful for security purpose.
It Reduce the Sql injection problem is sql statement.

38.) What is the default format of date in sql server?

'MDY' (Month-date-Year)

39.) Can we change 'MDY' Format in another format in sql server?

Yes.

40.) What are the number of combination of date format in sql server?

3! = 3*2*1=6

41.) What is the use of cast function in sql server?

This Function is used to convert Particular type value to another type value.
Syntax:-
Cast ( as )
select sid as 'students id',sname as 'name',cast(regdate as varchar(30))as 'joining date' from student

42.) What is the use of convert function in sql server?

This function is also used to convert one type value to another type value.
Syntax:-
Convert ()
select sid as 'student_id',sname as 'Name',convert(varchar(30),regdate,104)as 'date' from student

43.) What is difference between Cast and Convert Function in sql server?

Cast is ANSI SQL-92 but Convert is a specific to SQL Server.
Except 'date' data type ,if we convert any type value into any other type ,there is no difference between Cast and Convert function.
If we want to convert date data type value to varchar' data type the convert function will be used.Because date format is exist in Convert function not Cast function.
It is faster than function.

44.) What is Transaction ?

A Transaction insure that changes will be made or none of changes will be done.

45.) What are the four Properties of Transaction ?

Atomicity
Consistency
Isolation
Durability

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

Implicit Transaction
Explicit Transaction

47.) What is the implicit transaction in sql server ?

In Implicit transaction ,if we want, we can not Rollback statement back.Implicit Transaction is by default Automatic in SQL Server.

48.) How to implement implicit transaction in sql server ?

set implicit_transactions on
insert into employee values(101,'sachin','store',14000)
Rollback

49.) What is the Explicit transaction in sql server ?

An explicit transaction is one in which we explicitly define the begin (start) and end of the transaction.
There are some controls of transactions which are given below:-
1.) Begin Transaction:- It is used to set starting point of any transaction.
2.) Commit:- It is to save the changes in Database.

50.) What are the four main controls of transaction in sql server ?

BeginTransaction
Commit
RollBack
SavePoint
More Details...

51.) What is the Savepoint Explain ?

We can provide a 'save point' to divide a transaction into sub logical transaction.It means, if within a transaction ,there is a set of statement which must be executed and another set of statement will be executed or Rollback ,which dependent on some condition.Then we can provide a Save Point to those statement which must be committed.

52.) What is RollBack Explain ?

It is used to Rollback the changes in Database.When error is encountered in between the transaction,with the help of Rollback ,we can easily Rollback the state of the transaction.

53.) What is the meaning of Implicit Transaction ON in sql server Explain ?

We can set implicit_transactions on.But there are some differences as given below:-.
When we set implicit_transactions on ,Then we will not see Begin Transaction in the Log file.
In implicit mode ,Transaction automatically started after each commit.

54.) 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.

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

Inner Join
Outer Join
Self Join
Cross Join

56.) 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

57.) 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.

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

Left Outer Join
Right outer join
Full outer join

59.) 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

60.) 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

61.) 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

62.) 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

63.) 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

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

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

65.) 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.

66.) What are the main purpose to create a view ?

We create views for two main purpose.
To make complex query to simple query
Database Security .

67.) 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.

68.) How to create a view in sql server explain?

Suppose we have two table studentdetails and scourse then we create view as given below:-
create view vdata(student_id,student_name,student_course)
as
select sid,sname,cname from scourse c join studentdetails s
on c.id=s.sid

69.) How to create a view with left outer join concepts?

create view vdata1(student_id,student_name,student_course)
as
select sid,sname,cname from scourse c Left join studentdetails s
on c.id=s.sid

70.) How to create a view with Right outer join concepts ?

create view vdata2(student_id,student_name,student_course)
as
select sid,sname,cname from scourse c Right join studentdetails s
on c.id=s.sid

71.) How to create a view with full outer join concepts ?

create view vdata3(student_id,student_name,student_course)
as
select sid,sname,cname from scourse c full outer join
studentdetails s on c.id=s.sid

72.) How to get data from a view?

Syntax:-
select *from view_name
Ex.
select*from vdata

73.) How to get the view details in database?

sp_help vdata
sp_helptext vdata

74.) How to delete view from database?

Drop view vdata

75.) What are the main conditions to insert,delete and update the records within table with view ?

There are two main conditions as given below:-
View must contain only one table within select query(no join tables).
Select query in view must contain all not nullable columns.

76.) What is Trigger ?

A trigger is an event that is executed when any condition (statement) is satisfied.

77.) 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

78.) How to view all triggers in your database ?

Select*from sys.triggers

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

DML (Data Manipulation Language) Triggers
DDL(Data Definition Language ) Triggers
Logon Triggers
CLR Triggers

80.) What operations can be performed in sql server ?

Insert statement
Update Statement
Delete statement
Stored Procedures

81.) What are the types of DML Triggers used in sql server ?

After Triggers (For/After clause)
Instead of Triggers(Instead of Clause)

82.) What is After triggers in sql server ?

This Triggers basically runs after an insert,delete and update operations on a table. We can perform After Triggers on a table not a View table. We can use three types of After Triggers in sql server which are given below:-
After Insert Trigger
After Delete Trigger
After Update Trigger

83.) What is instead of triggers in sql server ?

Instead of Triggers are used as an Interceptor.Suppose we define an Instead of Triggers on table or Views for the delete operation. If any conditions\rules written in statements are not satisfied or any error occurs in sql database then this trigger jump to another statements by escaping some statements and displayed some message to the client.
we can further classified this trigger in three types as given below:-
Instead of Insert Trigger
Instead of Delete Trigger
Instead of Update Trigger

84.) What is DDL triggers in sql server ?

DDL Trigger can be used for Observe ,control action and audit operations on the sql server. DDL Trigger are basically used for administration operations such as creating auditing and controlling the database operations. We can create DDL Triggers on the Tables by following statements as given below:-
Create Statement
Alter Statement
Drop Statement
Stored procedures can perform on DDL Trigger

85.) What is Logon triggers in sql server ?

Logon Triggers are DDL Triggers that is created at server label. Logon triggers are a spacial type of triggers which are fired when a logon event of sql server occurs.

86.) What are the syntax of Logon triggers in sql server ?

create trigger trigger_name
on {table/view}
[with encryption/Execute as]
[{for/after/instead of}/{insert/delete/update/create/alter/drop}]
[Not for replication]
as
SQL statement(1,2,3......n)

87.) What is CLR triggers in sql server ?

CLR Trigger are special type of triggers that are based on the CLR (Common Language Run time ) in .NET Framework.we can create DDL and DML Triggers using CLR Language (Visual basic ,C#,F#). CLR Trigger Concepts introduced in SQL Server 2008.