Selecting static non database text

Select 'Hello World ! '

--Selecting static text once

Selecting the database and a dummy select

Use Pubs

Select null

--Selecting nothing yields nothing. Using PUBS sets the database to be used for this entire session. Try also: Select 100,’ABC’

Selecting every column content

Use Pubs

Select * from authors

-- An * means everything

Selecting a mix of static text and every column content

Select 'Row>',* from authors

--'Row>' is a static text, which appears again & again in each rows.

Seeing the definition of a table.

Sp_help Authors

--Sp_help gives all the fields which make up any table. It knows about all the tables in a database. Proves very

handy for column names etc

Seeing the definition of a system table.

Sp_help SysObjects

--SysObjects is a system table which is very informative and is precreated in any database automatically. We will

revisit it later.

Selecting a column content

Select au_fname from authors

--Always refer to a correct field name spellings by referring table's definition executing the 'sp_help tablename' command..

Converting a column content to uppercase

Select UPPER(au_fname) from authors

--UPPER command converts every character in an expression or field to upper case. Here the expression is an english text..

Converting a column content to uppercase

Select LOWER('September 2002 | SQL Server Savvy | SQL Server Magazine A Set-Based Way to Find Specific Rows Solutions')

--LOWER command converts every character in an expression or field to lower case. Here the expression is an english text.

10.

Selecting a column's First character

Select LEFT(au_fname,1) from authors

--LEFT function shows the characters in a string, starting from the start.Here it will just show 1 character, the very first.

11.

Selecting a column's First 5 characters character

Select LEFT(au_fname,5) from authors

--LEFT function with a 5 argument will give first 5 characters from the LEFT end of the string. If there are fewer

charcters, it shows the entire string

12.

Selecting a column's Last 5 characters character

Select '%' + RIGHT(au_fname,5) from authors

--RIGHT function with a 5 argument will give last 5 characters from the RIGHT end of the string. If there are fewer

characters, it shows the entire string

13.

Selecting a column's Length

Select LEN(au_fname) from authors

Select len(727272727), datalength(727272727)

--LEN function gives the length of a column or an expression

--DataLength function gives the internal datatype length of a column or an expression. For integers it is 4 bytes.

--GO separates two SQL statements and run them one after the other showing the resultsets separately.

14.

Parsing a US phone number string

SELECT Phone, left(Phone,3) AreaCode ,left(right(Phone,8),3) Exchange ,right(Phone,4) Connection from Authors

--Combining Left & Right does lots of tokeninzation in the SQL industry

15.

Finding initial characters of columns and adding them together

Select (upper(left(au_fname,1)))+ '.' + upper(left(au_lname,1)) + '.' as Initials from Authors

--Finding initial characters, stuffing them together with some formatting.

16.

Selecting a column's content, its length and give new header aliases to each

Select au_fname as AUTHOR_FIRSTNAME, LEN(au_fname) AS LENGTH_FIRSTNAME from authors

--LEN function gives the length of a string

17.

Selecting a column's content, its length and giving new header aliases to each . Also Ordering the output of Names Alphabetically using field name

Select au_fname as AUTHOR_FIRSTNAME, LEN(au_fname) AS LENGTH_FIRSTNAME from authors

Order by au_fname

18.

Do as above using the Header Alias for ordering

Yields same results as above

Select au_fname as AUTHOR_FIRSTNAME, LEN(au_fname) AS LENGTH_FIRSTNAME from authors

Order by AUTHOR_FIRSTNAME

--We can also sort by the just given Alias Names

19.

Do as above using the Column Index for ordering

Yields same results as above

Select au_fname as AUTHOR_FIRSTNAME, LEN(au_fname) AS LENGTH_FIRSTNAME from authors

Order by 1

-- 1 stands for the very first field in the select list, viz., au_fname here.

20.

Ordering the results using column indexes.

Select au_fname as AUTHOR_FIRSTNAME, LEN(au_fname) AS LENGTH_FIRSTNAME from authors

Order by 2

-- Ordering with increasing length of Author Names

21.

Ordering the results using reverse alphabetical ordering

Select au_fname as AUTHOR_FIRSTNAME, LEN(au_fname) AS LENGTH_FIRSTNAME from authors

Order by 1 DESC

-- DESC means ascending

22.

Multiple Ordering, natural preferences

Select au_fname as FirstName, state from Authors

Order by 1 ASC, 2 ASC

-- ASC means ascending. ASCending is the default Order in a select

23.

Multiple Ordering , Mixed Preferences

Select au_fname as FirstName, state from Authors

Order by 1 DESC, 2 ASC

-- Here au_fname is arranged in Descending Alphabetic order and after that the state is arranged in Ascending Alphabetic

24.

Multiple Ordering , Order preference given to later fields in the select list

Select au_fname as FirstName, state from Authors

Order by 2 ASC, 1 ASC

-- Order priority given to state and then Author Names

25.

Derived Fields & their Ordering

Select au_fname + ' ' + au_lname as [AUTHOR'S FULL NAME]

FROM Authors

Order by [AUTHOR'S FULL NAME]

-- Joining two fields in a select list to form a new derived field. A rectangular brace [ ] enables you to put spaces &

apostrophes in a header alias

26.

Derived Fields & their Ordering continued

Select 'Mr. ' + au_fname + ' - ' + au_lname as [AUTHOR'S FULL NAME]

FROM Authors

Order by [AUTHOR'S FULL NAME]

-- Joining two fields in a select list to form a new derived field, together with some fillers.

27.

Selecting part of a field

Select SUBSTRING('System Statistical Functions',7,12) STUFF

--Selecting 12 characters, starting at the 7^th character in the argument expression Putting AS for aliasing is optional

as shown here.

28.

Selecting part of a field

Select SUBSTRING( au_fname, 2,5)

FROM Authors

--Selecting few characters from the first name.If the string doesn't have 5 characters, uptill the end, whatever.

29.

Selecting part of a composite field

Select SUBSTRING(au_fname ,1,1 ) + ' ' + au_lname FROM Authors

--Take first nam's initial and add to the second name. This is similar to saying LEFT(1) as above.

30.

Selecting part of a composite field

Select SUBSTRING('Mr. ' + au_fname ,1,5 ) + ' ' + au_lname FROM Authors

--First add Mr. To firstname then substring 5 characters from the resultant.

31.

A Combination of Upper, Lower, LEN and Substring

Select AllSmallFirstName = LOWER(au_fname),

InitCapFirstName = UPPER(LEFT(au_fname,1)) + LOWER(SUBSTRING(au_fname ,2,LEN(au_fname)))

FROM Authors

--"Alias = derivedfield" is similar in effect to "derivedfield AS Alias". Substring intends to start from the second

character end go till the end and so traversing the entire length of the string

32.

A Combination of Upper, Lower, Left and Substring

Select LEFT(UPPER(au_fname),1) +LOWER(SUBSTRING(au_fname ,2,LEN(au_fname))) + ' ' + UPPER(SUBSTRING(au_lname ,1,1 )) AS SHORTNAME FROM Authors

--Mixing several functions start from the innermost function to the outside while resolving

33.

Replacement of a part of a field or expression

SELECT REPLACE('United States has 50 states. Canada is north of the United States', 'United States', 'U.S.A.')

--Replacing a target with a pattern.

34.

Substituting a character in an expression

select REPLACE(city,'k','x') CITY from Authors

--Substituting a single character in a field

35.

Substituting a separator in an expression

SELECT REPLACE('flowers,bells,glasses,linen,confettii',',',' | ')

SELECT REPLACE('21.9.1999','.','/')

--Substituting a single character in a field

36.

Substituting a character in an expression

select Phone,REPLACE(REPLACE(phone,' ','.'),'-','.') NewPhone from Authors

--Substituting a single character in a field

37.

Repeatition of content of a field

SELECT REPLICATE(au_fname, 3) as STUFF

FROM authors

ORDER BY STUFF

--Replicating the same field one after the other, thrice.

38.

Repeatition of content of a field

SELECT REPLICATE('-', 100) AS LINE

--Replicating a pattern for very large number of times, gives a feel of drawing a line

39.

Repeatition of content of a field together with replacing few characters

SELECT REPLACE(REPLICATE('<XML>', 2),'><','></') AS PATTERN

--Replicating and replacing may some time be useful

40.

Padded money representation

Select 'Total Invoice Amount : ' + REPLICATE('*',(12 - LEN('638.99'))) + '638.99' + 'USD'

Select 'Total Invoice Amount : ' + REPLICATE('*',(12 - LEN('363638.99'))) + '363638.99' + 'USD'

--Padding '*' in an amount figure.

41.

Right Shift of Binary Zeroes

SELECT

REPLICATE('0',(8 - LEN('1'))) + '1'

,REPLICATE('0',(8 - LEN('11'))) + '11'

,REPLICATE('0',(8 - LEN('111'))) + '111'

,REPLICATE('0',(8 - LEN('1111'))) + '1111'

,REPLICATE('0',(8 - LEN('11111'))) + '11111'

,REPLICATE('0',(8 - LEN('111111'))) + '111111'

--Emulating Right Shift Operation using TSQL Replicate function.

42.

Reversing of content of a field

SELECT REVERSE(au_fname)

FROM authors

ORDER BY au_fname

--Reversing the content of a field

43.

Pallindroming a field

SELECT au_fname + ' ' + REVERSE(au_fname) PALLINDROME FROM authors ORDER BY au_fname

--Reversing and jutting together

44.

Blank spaces between fields

Select au_fname + SPACE(2) + au_lname From Authors

--Stuffing two blank spaces between first name & the last.

45.

Replacing a pattern with blanks

select REPLACE('New \t Light','\t',SPACE(10))

--A tab gets replaced with 10 spaces.

46.

Replacing a separator with spaces

select REPLACE('Tom|Jerry|McInTyre','|',SPACE(10))

--Separator pipes get replaced with ten spaces

47.

Embedding incremental spacing between characters in a literal

Select Replace ('abababa','b',SPACE(0)) away

Select Replace ('abababa','b',SPACE(1)) away

Select Replace ('abababa','b',SPACE(2)) away

Select Replace ('abababa','b',SPACE(3)) away

Select Replace ('abababa','b',SPACE(4)) away

--Incremental spacing between characters

48.

Replacing spaces with more spaces

Select Replace ('The Code of Nimrods 2',SPACE(1),SPACE(10))

Select Replace ('The Code of Nimrods 2',CHAR(32),SPACE(10))

--Space(1) can also be alternatively expressed as CHAR(32). Every printable & non printable characters have a unique ASCII code. A space is a 32.

49.

Replacing spaces with a pattern

Select Replace ('The wind is getting chilly',SPACE(1),CHAR(45)) Hyphoon ,

Replace ('The wind is getting chilly',SPACE(1),CHAR(47)) Slasher ,

Replace ('The wind is getting chilly',SPACE(1),CHAR(95)) LowerDraw ,

Replace ('The wind is getting chilly',SPACE(1),CHAR(124)) DigPipe,

Replace ('The wind is getting chilly',SPACE(1),' ') Html_Space

--Spaces amongst word can be stuffed with any patterns. In place of writing a hyphen (-), forward slash (/),underscore(_)

and a pipe(|) we can also refer them with their ascii codes 45,47,95 & 124 respectively

50.

Replacing spaces with a newline

SELECT Replace ('The wind is getting chilly',SPACE(1),CHAR(13)) Chinese

--Stacking the words in a line vertically using ASCII value of 13 for the new line. Use Ctrl+T or View Results in Text

before running this command to see the vertical stacking effect. After running this again do a Ctr+D or View Results in Grid.

51.

A report like formatting with single SQL

SELECT au_fname + CHAR(32) + Au_lname + CHAR(32) + Phone + CHAR(13) + Address + CHAR(13) + City + CHAR(32) + state + CHAR(32) + Zip + replicate(CHAR(13),3) from Authors

--Replicate(CHAR(13),3) introduces three blank lines between consecutive records. Use Ctrl+T or View Results in Text

before running this command to see the vertical stacking effect. After running this again do a Ctr+D or View Results in Grid

52.

Drawing decorative lines

select Replicate ('~o~'+SPACE(4),50)

--Composite patterns can be replicated to create a decorative line effect.

53.

Stripping of Leading Spaces

select LTRIM(' Five spaces are at the beginning of this string.')

--Five spaces put before the statement are taken away..

54.

Stripping of Leading Spaces

select '<' + ltrim(' middle ') + '>'

--Five spaces put before the statement are taken away..

55.

Drawing decorative lines

select replicate(space(9)+'---------',10)

select LTRIM (replicate(space(9)+'---------',10))

--The first pattern starts with nine blanks,the second not as it has been L-trimmed.

56.

Stripping of Trailing Spaces

select RTRIM('Five spaces are at the end of this string. ')

--Five spaces put after the statement are taken away..

57.

Stripping of Trailing Spaces

select '<'+LTRIM(RTrim(' middle ')) +'>'

--Five spaces put after the statement are taken away..

58.

A mix of Space trimming

select '<'+Type+'>' WITH_TRAILING_SPACES,'<'+LTRIM(RTrim(Type)) +'>' LACKING_SPACES from Titles

--Type is a char(12) field, so it would take12 places, whether or not the the data is less then 12 characters. Rtrim will take any left spaces after the data fill. Ltrim would take any leading spaces being put there as part of the data input.

59.

Finding the number of rows to be fetched

Select COUNT(*) AS TOTAL_ROWS from Authors

--Count(*) is the number of all the existences of the rows in a table.

60.

Finding the number of rows to be fetched, quantified on an attribute

Select state,COUNT(*) AS TOTAL_ROWS

from Authors

group by state

--field,Count(*) is the number of all the existences of the rows in a table, grouped by the field. All the distinct states will be listed from the table, along with there specific occurrence counts.

61.

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

--field,Count(*) is the number of all the existences of the rows in a table, grouped by the field. All the distinct states will be listed from the table, along with there specific occurrence counts. The Null States will be represented by ‘XX’.

62.

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

--field 1 field 2,Count(*) is the number of all the existences of the rows in a table, grouped by the two fields. All the distinct states will be listed from the table, next to them all the zipcodes will be listed along with there specific occurrence counts. The Null States will be represented by ‘XX’. The group by has to have same set of parameters, as are there in the select list before count(*), in any order. In place of two fields, we can have several grouping fields too.

63.

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

--A grouped result set filtered by a constraint. Having follows all the Where/And/Or syntax to be discussed later. Here the row with null state will be taken away

64.

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

--A grouped result set can be ordered by one, a few or all the grouping parameters in Ascending, Descending or any combinations

65.

Finding the rows which appear more than once based on a selection criteria

select state, count(*)

from Authors

group by state

having count(*) > 1

--This gives you the duplicate (or more) rows in a table, based on a criteria.

66.

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

--Selecting counts for the rows, columns and distinctive column contents.

67.

Finding the resultset based on one restrictive condition

select * from Pubs..Authors

where state = 'CA'

--Where field = value is the easiest of the where clause. Database..Table is a way to access other database tables by fully qualifying the path. This is used to get rid of USE Database construct.

68.

Finding the resultset based on few restrictive conditions

select * from Pubs..Authors

where state = 'CA'

or state = 'UT'

--The OR clause gives a facility to add more selection options in the where clause. OR usually applies for the same field iterated twice or more in the Where clause, as here state has been referred twice once for ‘CA’ & once for ‘UT’

69.

Finding the resultset based on few restrictive conditions

select * from Pubs..Authors where state in ( 'CA' , 'UT' )

order by state desc

--Where field in ( value1,value2,value3…..) is also one of the normal usage of the Where clause. This lets you select several constraining values for the attribute in one shot.

70.

Finding the resultset based bearing some restrictive textual pattern

select * from Northwind..Customers

where CompanyName like 'B%'

--Where field like text-pattern is one of the common way of querying. Try: like ‘%market%’

71.

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'

--The Phone & Postal code are alphanumeric characters in the table so they are enclosed in single quotes.

72.

Selecting columns based on a Where clause which is further constrained by an AND clause

SELECT * from Northwind..Customers

WHERE Phone like '(5)%'

AND RIGHT(Postalcode,2) = '33'

--The AND Clause further restricts the search results.

73.

Selecting columns based on a Where clause which is further constrained by an AND clause

SELECT * from Northwind..Customers

WHERE Phone like '(5)%'

AND RIGHT(Postalcode,2) = '33'

OR RIGHT(Postalcode,2) = '23'

--The AND Clause further restricts the search results. An OR clause adds additional conditions in the previously filtered resultset. In general AND reduces the number of rows in the resultset where as OR increases the number of resultsets.

--If you are copying and pasting the commands from here in a SQL editor, always put a Carriage Return (New Line) just before SELECT, WHERE, AND, OR, ORDER BY, GROUP BY, HAVING to increase readibility.

74.

Selecting columns based on a Where clause which is further constrained by a BETWEEN clause

SELECT Au_LName, YTD_SALES

from Pubs..titleView

where ytd_sales between 2000 and 4000

order by 2

--BETWEEN Clause works on the range of values lying between an intial and a final limit. This usually is number, but can be Strings too in which case there will be a dictionary like pattern search for the field value.

--TitleView is a View act like ‘frozen-queries’, which are much like a table in several respects. Much about these later.

75.

Finding the Grouped Count based on several filtering criteria

Use Pubs

SELECT STOR_ID, COUNT(*) count

FROM sales

WHERE title_id like 'PC%'

AND Stor_id between 7000 and 8000

OR ord_date between '1994-01-01' and '1995-01-01'