What are the general functions available in SQL?

aghiad

• Sep 11th, 2011

The General function are use in SQL 1-NVL (exp1,exp2) 2-NVL2 (exp1,exp2,exp3) 3-NULLIF (exp1,exp2) 4-COALESCE (exp1,exp2,exp3,......,expn) For example: "sql SELECT last_name...

ElavarasiGophannan

 Profile Answers by ElavarasiGophannan Questions by ElavarasiGophannan

• Feb 11th, 2009

Aggregate Functions: AVG() - Returns the average value  COUNT() - Returns the number of rows FIRST() - Returns the first value LAST() - Returns the last value MAX() - Returns the largest...

Answer Question  Select Best 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
span style="color: #ff0000;">'CA', 'LA'

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
 

Using Having

Code
 

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

suji

 Profile Answers by suji Questions by suji

• Nov 6th, 2011

Here is how you can use rollback in any of the SQL Server versions. works in both SQL Server 2000 and later versions. "sql Begin tran insert into table values or updates rollb...

Answer Question  Select Best 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

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

Lokesh M

 Profile Answers by Lokesh M Questions by Lokesh M

• Nov 28th, 2011

Steps to set / modify Global Setting for Query Studio: 1. Stop the Cognos 8|ReportNet server. 2. On the Cognos 8|ReportNet Server, go to the directory /c8|crn/templates/ps/async/ 3. Either create a n...

Answer Question  Select Best Answer

What is the difference between Subqueries and Joins? Which is better and Why? Give an example of each.

Hiren

• Nov 29th, 2011

Its true, I would like to add some more information about it, Subquery take separate temp table and checking condition .. Besides join is checks conditions first and then put it into tabl...

abdullah

• Sep 12th, 2011

I am software engineer and iam working on oracle mysql and sql so i have lot of experience on this ,basically joins are using there should be connection two or more than two tables ,joins are always o...

Answer Question  Select Best Answer

what is high water mark? Give an example where it can be useful ?

Lokesh M

 Profile Answers by Lokesh M Questions by Lokesh M

• Dec 12th, 2011

High Water Mark (HWM) represents total number of extent blocks used by a table. The value of High Water Mark could sometime be more then the actual size required/used by a table. This is because, some...

Answer Question  Select Best Answer

how do you run PL/SQL block from SQL Server

Lokesh M

 Profile Answers by Lokesh M Questions by Lokesh M

• Dec 12th, 2011

Try these and see...

Go To Options -> Run SQL
Set "Statement Delimiter" to None

Go To Options -> Technical Parameters
Tick "Use SQLExecDirect"

Answer Question  Select Best Answer

List down the advantage and disadvantages of Covering indexes.

Lokesh M

 Profile Answers by Lokesh M Questions by Lokesh M

• Dec 12th, 2011

Covering Indexes are useful for Input / Output intensive workloads.
Covering indexes are smaller in size
Covering Indexes are sorted by values
It is easier to cache date in Covering Indexes

Covering Indexes cannot be used to select all the columns / entire table.

Answer Question  Select Best Answer

How do represent objects in SQL Server? Is it possible? If yes, how?If not? Why?

Lokesh M

 Profile Answers by Lokesh M Questions by Lokesh M

• Dec 12th, 2011

Try using Server Management Objects (SMO) classes

Answer Question  Select Best Answer

1 How is the error handling in stored proc of T-SQL2 What is clustered index and non-clustered index? How many clustered indexes and non-clustered indexes can be created in one table? 3-what is disconnected mode?

Pradeep Kumar Sharma

• Dec 25th, 2011

Begin Try

--
--
End Try
Begin Catch

Declare @errMsg varchar(1000),@errSev int
select @errMsg=error_message(),@errSev=error_severity()
RaisError(@errMsg,@errSev,1)

End Catch

kirangiet

 Profile Answers by kirangiet Questions by kirangiet

• Aug 17th, 2010

1) CLR is integrated in SQL Server 2005. Hence we can use below code for Exception Handling. BEGIN TRY SELECT * FROM Employees; END TRY BEGIN CATCH SELECT ERROR_NUMBER() ...

Answer Question  Select Best Answer

What is fill factor?What should be the ideal value of fill factor for any index?Justify the answer.

raiyaans

 Profile Answers by raiyaans Questions by raiyaans

• Jan 12th, 2012

The fill factor simply says how large or how full to get our index pages
you can set it as 70% ,

If set to 100% it might me a bad thing if we had a lot of DML activity but if this is a read only table then
fill factor=100% is fine.

Answer Question  Select Best Answer

suji

 Profile Answers by suji Questions by suji

• Jan 15th, 2012

From your SQL Server Management Studio, you open Object Explorer, go to your database where you want to load the data into, right click, then pick Tasks > Import Data. Select the options based on the excel file

Answer Question  Select Best Answer

What is a key in SQL?

Mayank Tripathi

 Profile Answers by Mayank Tripathi Questions by Mayank Tripathi

• Jan 18th, 2012

Candidate Key, Alternate Key, Composite Key are also the parts of SQL Server: A candidate key is one that can identify each row of a table uniquely. Generally a candidate key becomes the primary ke...

psingla

 Profile Answers by psingla Questions by psingla

• Nov 8th, 2010

We have different types of keys in sql:primary key      identify record uniquely     max one per table     do not contain null values &...

Answer Question  Select Best 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

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

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
span style="color: #ff0000;">"REPETED EMAIL"

Improve Answer

Vijay

• May 10th, 2012

SELECT ID FROM TBLSAMPLE GROUP BY ID OR SELECT DISTINCT (ID) FROM TBLSAMPLE

Code
 

Mally

• Feb 13th, 2012

Good Code...however the code will delete all the duplicates... you may want to add:

delete top(n) clause in your delete statement to exactly delete the required number of records.

Answer Question  Select Best Answer

In which scenario I can use the unique key and primary key in SQL Server .
I mean in real life database how can I judge where I have to use the unique key and primary key.

sunil neha juhi singh

• Jun 28th, 2012

Primary key=not allow null values,create cluster index by default.
unique key=allow only one null values and by default it create non cluster index

kushgudseva

 Profile Answers by kushgudseva Questions by kushgudseva

• May 29th, 2012

A Unique ID and Primary Key Constrain perform the ability to allow unique data in the column but only the Unique constrain allows NULL values in the field along with the unique data. Primary Key constrain fields allow Unique and Not NULL values.

Answer Question  Select Best Answer