Sql Server Interview Questions and Answers (1758) - Page 30

Latest and authentic Interview questions. You can also post an interview question and win monthly prizes as well as gain community credit points.

1758 records found.

Post |

Interview Experiences |

Interview FAQs |

Online Interviews |

Exclusive Questions

Get 650+ Questpond's Interview videos on discount

How to restart SQL Server in single user mode? How to start SQL Server in minimal configuration mode?

SQL Server can be started from command line, using the SQLSERVR.EXE.
This EXE has important parameters with which a DBA should be familiar with.
1) -m is used for starting SQL Server in single user mode
2) -f is used to start the SQL Server in minimal configuration mode.

Explain about reporting services of MYSQL ?

This forms the report generating environment whereby a report is generated by the data generated by the client and server. This feature is administered via a web interface. Reports are created in RDL format. They can be designed by Microsoft visual studio.

What is the difference between weak entity set & strong entity set ?

Weak Entity Set:
An entity set which does not possess sufficient attributes to form a primary key is known as a weak entity set.
Example:
Specific Person,Company,Event,Plant

Strong Entity Set:
An entity set which does have a primary key is called a strong entity set.
Example:
Set of all Persons,Companies,Trees,Holidays

What is Normalization?

Normalization
Normalization is the process of simplifying the relationship between data elements in a record.

(i) 1st normal form: - 1st N.F is achieved when all repeating groups are removed, and P.K should be defined. big table is broken into many small tables, such that each table has a primary key.
(ii) 2nd normal form: - Eliminate any non-full dependence of data item on record keys. I.e. The columns in a table which is not completely dependent on the primary key are taken to a separate table.
(iii) 3rd normal form: - Eliminate any transitive dependence of data items on P.K’s. i.e. Removes Transitive dependency. Ie If A is the primary key in a table. B & C are columns in the same table. Suppose C depends only on B and B depends on A. Then C does not depend directly on primary key. So remove C from the table to a look up table.

How to get First Day date and Last day Date of the current week.

declare @start_date DATETIME

SET @start_date=dateadd(week, datediff(week, 0, getdate()), 0) ;

-- Get the last date of the last week

declare @end_date DATETIME

SET @end_date=dateadd(week, datediff(week, 0, getdate()), 6) ;



select 

		CONVERT(VARCHAR(10),@start_date,105) 'start day date of the week',

		CONVERT(VARCHAR(10),@end_date,105) 'end day date of the week'



CONVERT(VARCHAR(10),@start_date,105) : This is used for removing the time part from the DateTime data type.

Difference between where and having clause in select statment ?

The where clause specifies the criteria which individual record s must meet for the query. It can used along select,delete and update statement.

The having clause specifies the criteria which individual group or an aggregate function must meet for the query. It can only used with select statement.

What is the wildcard character in SQL ?

The wildcard character is %.

Example:

Let us say you want to query database with LIKE for all employees whose name starts with La. The wildcard character is %, the proper query with LIKE would involve La%.

What is @@rowcount ?

@@rowcount gives the number of rows given as a result of previous query ran.

Example:

Create procedure get_emp_count ( @emp_id int) 

As Select * from emp where emp_id =@emp_id 

If @@rowcount = 0 

Begin 

Select 'no rows with emp_id= ' Select @emp_id 

End

HOW MANY TYPES OF JOINS AVAILABLE IN SQL SERVER?

There are Five type of joins available in sql server

1) INNER JOIN
There are Two tables having Common column values go with inner join.
2) OUTER JOIN(LEFT OUTER JOIN,RIGHT OUTER JOIN)
Left outer join: Taking Left side of the values checking in to Right side Taking Right side values checking with left side call right outer join.
3) FULL JOIN
Full join combined with left outer join and right outer join.
4) CROSS JOIN
Cross join is an Cartesian Product.its look matrix format.
5) SELF JOIN
join within the table is called self join..

WHAT IS TRIGGER IN SQL SERVER?

TRIGGER IS AN SELF CONTAINED BLOCK OF STATEMENTS.
TRIGGER IS AN FIRED WHEN AN INSERT,UPDATE ,DELETE STATEMENTS.
TRIGGER IS APPLIED FOR DML STATEMENTS IN SQL SERVER 2000/2005
SQL SERVER 2008 DDL STATEMENTS ALSO SUPPORT TRIGGER

TYPES OF TRIGGER:

THERE ARE TWO TYPES OF TRIGGER AVAILABLE IN SQLSERVER
1) INSTEAD OF TRIRRGER
2) AFTER TRIGGER

REGARDS

WHAT IS VIEW IN SQL SERVER ?HOW MANY TYPES OF VIEW AVAILABLE?

VIEW IS AN VIRTUAL TABLE.VIEW IT WONT OCCUPY ANY MEMORY,VIEW IS AN IMAGINARY TABLE.
VIEW CANNOT INSERT,UPDATE ,DELETE STATEMENTS

THERE TWO TYPES OF VIEWS AVAIALBLE

1) SCHAMABINDING
2) ENCRYPTION

REGARDS

If we can execute multiple sql statements inside a stored procedure. What will be the result of this stored procedure ? create procedure usp_test as begin select * from order_table select * from product_table end

When the stored procedure is executed, it will fetch all the rows of the order_table and product_table collectively !

Thanks and Regards
Akiii

In SQL Server, what is the difference between Count(*) and Count(column_name) ?

Count(*) - Will count all the rows in the specified table

Count(column_name) - returns the number of rows which have a value (NULL values will not be counted)

Thanks and Regards
Akiii

What is meaning of COUNT(DISTINCT column_name) ?

COUNT(DISTINCT column_name) - returns the number of distinct values of the specified column

For example :-

select COUNT(distinct store_name) from tbl_MEOC_information;

It will fetch distinct rows from the store_name column.

Thanks and Regards
Akiii

There are two tables, customer table has 5 rows and the order table has 8 rows. How many will be generated when the following query is executed against the database :- select * from tbl_customer, tbl_order;

NOTE: This is objective type question, Please click question title for correct answer.

Are the two statements same :- select custid from tbl_order; select All custid from tbl_order;

NOTE: This is objective type question, Please click question title for correct answer.

Can we define multiple Unique Keys in a table ?

Yes, we can define multiple unique keys in a table.

Create Table Sampletable

(

UserID integer NOT NULL PRIMARY KEY,

UserName varchar(50),

FirstName varchar(50) ,

LastName varchar(50),

CONSTRAINT us_UserId UNIQUE (UserID),

CONSTRAINT us_Username UNIQUE (UserName)

)

If you have specified NOT NULL Constraint, will that column allow 'blank' as a value ?

NOTE: This is objective type question, Please click question title for correct answer.

Advantages for using a View in SQL Server ?

(1) To hide the complexity of the underlying database schema
(2) To control access to rows and columns of data
(3) Customize the data and schema for a set of users
(4) To aggregate data for performance

Can you update the data in a view?

Yes, we can update the data in a view if proper privileges to the underlying tables are given.

A view is created by joining one or more tables. When you update record(s) in a view, it updates the records in the underlying tables that make up the view.