SQL Server Basic – IV

–Finding the number of rows to be fetched
Select COUNT(*) AS TOTAL_ROWS from Authors

–Finding the number of rows to be fetched, quantified on an attribute
Select state,COUNT(*) AS TOTAL_ROWS
from Authors
group by state

–Finding the number of rows to be fetched, quantified on an attribute,
–substituting any null attribute by some alias or field
Select ISNULL(state,’XX’) STATE,COUNT(*) NOFAUTHORS
from Authors
group by state

–Finding the number of rows to be fetched, quantified by more than one attribute
Select ISNULL(state,’XX’) STATE,Zip,COUNT(*) NOFAUTHORS
from Authors group by state,Zip

–Finding the number of rows, quantified on an attribute satisfying a particular criteria
select state, count(*)
from Authors
group by state
having state is not null

–Finding the number of rows, quantified on an attribute satisfying a particular criteria,
–ordered by the grouping (or a subset of grouping) fields
Select ISNULL(state,’XX’) STATE,Zip,COUNT(*) NOFAUTHORS from Authors
group by state,Zip
order by state,Zip ASC
— order by Zip DESC

–Finding the rows which appear more than once based on a selection criteria
select state, count(*)
from Authors
group by state
having count(*) > 1

–Finding the distinctive counts of an attribute in a table
select COUNT(*) NOF_ROWS,COUNT(state) NOF_VALIDSTATES,
COUNT(distinct state) NOF_DISTINCTSTATES
from Authors

Select COUNT(distinct state) states,  COUNT(distinct zip) zips from Authors

–Finding the resultset based on one restrictive condition
select * from Pubs..Authors
where state = ‘CA’

–Finding the resultset based on few restrictive conditions
select * from Pubs..Authors
where state = ‘CA’
or state = ‘UT’

–Finding the resultset based on few restrictive conditions
select * from Pubs..Authors where state in  ( ‘CA’ ,  ‘UT’  )
order by state desc

–Finding the resultset based bearing some restrictive textual pattern
select * from Northwind..Customers
where CompanyName like ‘B%’

–Finding the resultset based bearing some restrictive textual pattern
select * from Northwind..Customers
where Phone like ‘(5)%’

select * from Northwind..Customers
where substring(PostalCode,1,3) = ‘050’

select * from Northwind..Customers
where Right(Left(Phone,4),3) = ‘208’

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s