Read Best Answer

Editorial / Best Answer

Answered by: Parished.D Chennai India

• Apr 24th, 2007

---

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

Arshad

• Jul 30th, 2023

SELECT ENO, SUM(SALARY)
FROM EE
GROUP BY ENO
HAVING SUM(SALARY) > 5000

dinesh chandra gannavarapu

• Apr 15th, 2020

SELECT EmpId,EmpName,Salary
FROM Employee
GROUP BY EmpId,EmpName,Salary
HAVING Salary>5000

Improve Answer

Display employee name, designation, Dob who are born in the same year.

Emono ename Designation Dob
101 Raj Developer 24-06-1992
102 mahesh Engineer 15-06-1992
103 Rajesh Mechanic 02-02-1991
104 suresh Jr Engineer 01-01-1990

Afsar

• Oct 25th, 2017

TD QUERY:
SELECT EXTRACT (YEAR FROM DOB), ename, Designation, Dob
FROM TABLE TAB1
GROUP BY EXTRACT (YEAR FROM DOB)

Answer Question  Select Best Answer

How to delete duplicate records?

Naveen

• Jun 16th, 2017

Insert the distinct rows from the duplicate rows table to new temporary table. Delete data from table which has duplicate rows then insert the distinct rows from the temporary table

Raj Acharya

• Sep 30th, 2016

Using CTE ...you can remove duplicate records in one query....

Answer Question  Select Best Answer

Read Best Answer

Editorial / Best Answer

Answered by: Steven

• Oct 4th, 2011

---

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.

Jatin Girdhar, Gurgaon

• Oct 12th, 2015

SQL Normalization is a systematic approach of de-composing tables to: 1. To eliminate redundant (useless) data 2. To store the data logically Benefits: 1. Removes Data redundancy 2. Improves perform...

rayudu

• Oct 11th, 2011

Normalizatilon is the process of organizing the table to minimize the redundency

Improve Answer

Can i update views in SQL if yes in what scenarios?
if your view is joining multiple tables

Ayushi

• May 7th, 2015

We can update simple views(having data from one table) and complex views(having data from multiple tables) but columns should be taken from one base table for update.

asifeqbal

 Profile Answers by asifeqbal Questions by asifeqbal

• Mar 18th, 2015

Yes, we can update view when view is created by single table (simple view). If we want to Update view that are based on multiple tables using Joins, Update does not work. So we used "Instead of Triggers" for updating Complex view.

Answer Question  Select Best Answer

What is its purpose?How to use it?

Read Best Answer

Editorial / Best Answer

suji  

 Profile Answers by suji Questions by suji

• Member Since Sep-2005 | 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';

If you would like to see more details, Create your custom procedure

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

Here is another alternative way to get the same information

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 ]

Here is the usage :

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

Contributors for the editorial answer : Kewlshiva, srilakshmi.b, raaghav,kevaburg

Rooshi

• Oct 17th, 2014

Sp_help will give all the details about the table columns, indexes, partitions etc.

Saket Kale

• Oct 24th, 2012

Sp_help dbo.customers;

worked for me just fine.

Thanks to the OP, this runs perfectly on SQL 2008 R2, do not know about other versions

Improve Answer

mbhsye786

 Profile Answers by mbhsye786 Questions by mbhsye786

• Jan 13th, 2014

Your answer is incorrect
Sql Server runs only on windows platform and is hardware dependent
where as
Oracle runs in any platform and hardware independent such as can run on unix and windows.

Sivaram

• Dec 31st, 2006

Basic DifferenceSql Server:Runs only on windows platform,Hardware independentoracle:Runs in any platform and hardware dependent..

Answer Question  Select Best Answer

Read Best Answer

Editorial / Best Answer

Answered by: Hanif

• Apr 12th, 2006

---

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

nandu

• Apr 26th, 2012

SELECT YourColumn, COUNT(*) TotalCount
FROM YourTable
GROUP BY YourColumn
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC

Ramakant Sahoo

• Dec 24th, 2011

Code
SELECT EMAIL, COUNT(*)  "REPETED EMAIL" FROM EMP GROUP BY EMAIL; 

Improve Answer

Read Best Answer

Editorial / Best Answer

santanukd  

 Profile Answers by santanukd Questions by santanukd

• Member Since May-2008 | May 14th, 2009

---

Just replace "[your-schema-name-here]" in the following query with your schema name. Hope it comes handy to some of you out there.

Select OBJECT_TYPE,OBJECT_NAME
FROM
(
Select 'TABLE' as OBJECT_TYPE, TABLE_NAME as OBJECT_NAME, TABLE_SCHEMA as OBJECT_SCHEMA from information_schema.VIEWS
Union
Select 'VIEW' as OBJECT_TYPE, TABLE_NAME as OBJECT_NAME, TABLE_SCHEMA as OBJECT_SCHEMA from information_schema.VIEWS
Union
Select 'INDEX[Type:Name:Table]' as OBJECT_TYPE, concat(CONSTRAINT_TYPE,' : ',CONSTRAINT_NAME,' : ',TABLE_NAME) as OBJECT_NAME,TABLE_SCHEMA AS OBJECT_SCHEMA from information_schema.TABLE_CONSTRAINTS
Union
Select 'Procedure/Functions' as OBJECT_TYPE, ROUTINE_NAME as OBJECT_NAME, ROUTINE_SCHEMA as OBJECT_SCHEMA from information_schema.ROUTINES
) R

Where R.OBJECT_SCHEMA=[your-schema-name-here]

Keer

• Apr 25th, 2012

Select * from sys.tables where type = U

saikiran

• Jul 22nd, 2011

Select * from sys.tables

Improve Answer

interviewer ask.. can we replace where clause by group by clause

let say

select * from table_name where some_condition

so how we replace where clause by group by clause

Mark Haynes

 Profile Answers by Mark Haynes Questions by Mark Haynes

• Feb 16th, 2012

As a basic rule of query building: 1. The WHERE clause should be used to limit the number of rows returned by or affected by the a SQL statement. 2. The GROUP BY clause is used to group a selec...

Answer Question  Select Best Answer

Read Best Answer

Editorial / Best Answer

suji  

 Profile Answers by suji Questions by suji

• Member Since Sep-2005 | 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

suji

 Profile Answers by suji Questions by suji

• 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 fu...

Improve Answer

Read Best Answer

Editorial / Best Answer

suji  

 Profile Answers by suji Questions by suji

• Member Since Sep-2005 | 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

suji

 Profile Answers by suji Questions by suji

• 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 da...

krishnakumar

• Nov 3rd, 2006

The binary datatype is used for storing the images in the database.the non binary datatypes are integer,varchar etc

Improve Answer

Read Best Answer

Editorial / Best Answer

Answered by: Ankush Sharma

• Sep 14th, 2011

---

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

Suppose I want the total amount due from customers by state. In that case, I would need to use the GROUP BY clause to build an aggregate query.

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

Using Having

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

Ankush Sharma

• Sep 14th, 2011

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

Ankush Sharma

• Sep 14th, 2011

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

Improve Answer

Read Best Answer

Editorial / Best Answer

jayaprakash.raman  

 Profile Answers by jayaprakash.raman Questions by jayaprakash.raman

• Member Since Apr-2007 | Apr 24th, 2007

---

Please refer that here we are discussing about Sql Server only. There is no function named 'Translate' in Sql Server. 'Translate' function exists in Oracle for translating a single character to some other character.

Replace fucntion in Sql server is used to replace a string as well as character to another string or character. No need to have a seperate function like Transalate in Oracle:

Find below the examples to get the clear idea:

Ex 1: Sql Server replace function similar to Oracle Replace function

Declare @Str varchar(100)

set @Str='India is my country'

print @Str

print replace(@Str,'India','Pakistan')

Result

India is my country

Pakistan is my country

Ex 1: Sql Server replace function similar to Oracle Translate Replace function

Declare @Str varchar(100)

set @Str='Think You'

print @Str

print replace(@Str,'i','a')
Result

Think You

Thank You

Note: Please try to post Sql server questions only here. Ther is a seperate section available for Oracle. If we mix both, it will confuse the people who are specializing their skill in particular area. Thank you.

jayaprakash.raman

 Profile Answers by jayaprakash.raman Questions by jayaprakash.raman

• Apr 24th, 2007

Please refer that here we are discussing about Sql Server only. There is no function named 'Translate' in Sql Server. 'Translate' function exists in Oracle for translating a single cha...

aruna

• Apr 3rd, 2006

Translate is a character wise functionwhere as replace is a string wise

Improve Answer

Ritesh P. Medhe

• Sep 19th, 2006

Hi RegardsFill factor is the term associated with indexes actually with clustured indexesWhenever a clustured index is created sql server physically orders data in basis of the clustured column. As yo...

mvkr

• Aug 29th, 2006

When you create a clustered index, the data in the table is stored in the data pages of the database according to the order of the values in the indexed is known as fill factor

Answer Question  Select Best Answer