SQL Server Interview Questions

Editorial / Best Answer

Answered by: Parished.D Chennai India

create table ee (eno int, ename varchar(200), sal int)

insert into ee values(1, 'a', 2000)
insert into ee values(2, 'b', 6000)
insert into ee values(3, 'c', 8000)

select ENO, ENAME, min(sal) AS SAL from ee group by eno,ename having min(sal) > 5000

Editorial / Best Answer

Answered by: Hanif

we have to use the group by with having command to get the duplicate values. this query shall show the result of only the users have duplicate values in the employee table.

Syntex:
Select columnName From Table_name
Group By columnName
Having count (*) > 1

Example:
SELECT  UserID FROM employee
GROUP BY userid
HAVING count( * ) > 1

Editorial / Best Answer

Answered by: suji

Member Since Sep-2005 | Answered On : Nov 5th, 2011

Here are multiple ways to get the table information. The DESCRIBE command does not exist in MS SQL SERVER. This is an Oracle command used to describe the structure of objects within a given database. To achieve the same task in MSSQL Server, there are a series of stored procedures with the prefix SP_ that can be used. To view the structure of a table within the current database, use the command

Code
sp_help 'TABLE_NAME';

Code
CREATE procedure DESCRIBE 
(
@tablename varchar(256)
)
AS 
begin
SELECT DISTINCT sCols.colid AS 'order', sCols.name, sTyps.name, sCols.length
FROM [syscolumns] sCols
INNER JOIN [systypes] sTyps ON sCols.xtype = sTyps.xtype
INNER JOIN [sysobjects] sObjs ON sObjs.id = sCols.[id]
AND UPPER(sObjs.name) = UPPER(@tablename)
ORDER BY sCols.colid
end

Code
sp_columns 'TableName' (e.g. sp_columns 'Employee')
sp_columns [ @table_name = ] object [ , [ @table_owner = ] owner ] 
[ , [ @table_qualifier = ] qualifier ] 
[ , [ @column_name = ] COLUMN ] 
[ , [ @ODBCVer = ] ODBCVer ]

Code
EXEC sp_columns @table_name = 'Department', @table_owner = 'sa';
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'TableName'

Editorial / Best Answer

Answered by: suji

Member Since Sep-2005 | Answered On : Nov 7th, 2011

I would use full-text indexing as you are on the latest version. Create and store the html in varbinary(max) column and set its associated file type to ".html" in a file type column. Then enable full-text index, so indexer will parse the data and search only the text content while ignoring the HTML tags. We had been using blob to store the documents, with the new features have come out in the later versions, this seems to be best option. We are in the process of migrating one of the app to use varbinary and to take advantage of the search feature. There is another way of storing using filestrem. Here is a good read-up from MS . FILESTREAM enables SQL Server-based applications to store unstructured data, such as documents and images, on the file system. Applications can leverage the rich streaming APIs and performance of the file system and at the same time maintain transactional consistency between the unstructured data and corresponding structured data http://blogs.msdn.com/b/manisblog/archive/2007/10/21/filestream-data-type-sql-server-2008.aspx Here is another old report from MS back in 2006 explains about using oldway of BLOG http://research.microsoft.com/apps/pubs/default.aspx?id=64525 There are multiple options for you, pick the best approach that matches your needs Thanks, Suji

Editorial / Best Answer

Answered by: suji

Member Since Sep-2005 | Answered On : Nov 6th, 2011

Here are the Binary data types in sql server - bit, binary, varbinary, image. Bit variables store a single bit with a value of 0, 1 or NULL. binary(n) variables store n bytes of fixed-size binary data. They may store a maximum of 8,000 bytes. varbinary(n) variables store variable-length binary data of approximately n bytes. They may store a maximum of 8,000 bytes. image variables store up to 2 gigabytes of data and are commonly used to store any type of data file. Rest of the data types are non-binary

Editorial / Best Answer

Answered by: Steven

Normalizatiopn is the process of splitting tables up to reduce record redundancy. For instance if you have a table of addresses you may want to create a table of cities and link the addresses to the cities table in a foreign key. Since the city names would be identical on several records this would save a lot of space. In some cases if tables are not normalized the amount of space they would take up would be so large that it would not be possible to record the data. The amount of normalization that you choose would depend upon how often field data is repeated throughout the table.

Editorial / Best Answer

Answered by: Ankush Sharma

Though the HAVING clause specifies a condition that is similar to the purpose of a WHERE clause, the two clauses are not interchangeable. Listed below are some differences to help distinguish between the two:

1. The WHERE clause specifies the criteria which individual records must meet to be selcted by a query. It can be used without the GROUP BY clause. The HAVING clause cannot be used without the GROUP BY clause.
2. The WHERE clause selects rows before grouping. The HAVING clause selects rows after grouping.
3. The WHERE clause cannot contain aggregate functions. The HAVING clause can contain aggregate functions.

for Example: if for an "Select" statement we use the "where" clause then the the result based on the "where" condition results and then we can use "group by" clause to arrange in some order, Now if we want to impose the condition on that group then we use "having" clause.

The main reason for using WHERE clause is to select rows that are to be included in the query. For example, assume table Test.Suppose I want the names, account numbers, and balance due of all customers from California and Los Angles. Since STATE is one of the fields in the record format, I can use WHERE to select those customers.

Using the code

Code
SELECT cusnum, lstnam, init
FROM Test
WHERE state IN ('CA', 'LA')
 
CUSNUM LSTNAM INIT BALDUE
====== ============ ==== ========
938472 John G K 37.00
938485 Mark J A 3987.50
593029 Lily E D 25.00

Code
SELECT state,SUM(baldue)
FROM Test
GROUP BY state
ORDER BY state
 
State Sum(Baldue)
===== ===========
CA 250.00
CO 58.75
GA 3987.50
MN 510.00
NY 589.50
TX 62.00
VT 439.00
WY .00

Code
SELECT state,SUM(baldue)
FROM Test
GROUP BY state
HAVING SUM(baldue) > 250
 
 
State Sum(Baldue)
===== ===========
GA 3987.50
MN 510.00
NY 589.50
VT 439.00