SQL Server Basic – I

These articles will help beginners to understand the Basic SQL queries…

--Selecting static non database text  
Select 'Hello World!' 

--Selecting the database and a dummy select
Use Pubs   
Select null

--Selecting every column content  
Use Pubs   
Select *  from authors  

--Selecting a mix of static text and every column content  
Select 'Row>',*  from authors  

--Seeing the definition of a table.
Sp_help Authors  

--Seeing the definition of a system table.
Sp_help SysObjects  

--Selecting a column content
Select au_fname from authors

--Converting a column content  to uppercase
Select UPPER(au_fname) from authors

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

--Selecting a column's First character  
Select LEFT(au_fname,1) from authors 

--Selecting a column's First 5 characters character  
Select LEFT(au_fname,5) from authors  


--Selecting a column's Last 5 characters character  

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

--Selecting a column's Length 
Select LEN(au_fname) from authors  
GO
Select len(727272727), datalength(727272727)
GO

--Parsing a US phone number string  
SELECT Phone, left(Phone,3)  AreaCode ,
left(right(Phone,8),3) Exchange ,
right(Phone,4) Connection from  Authors

--Finding initial characters of columns and adding them together
Select (upper(left(au_fname,1)))+ '.' 
+ upper(left(au_lname,1)) + '.' 
as Initials from Authors

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

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


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

--Ordering the results using column indexes.   
Select au_fname as AUTHOR_FIRSTNAME, LEN(au_fname) AS LENGTH_FIRSTNAME from authors   
Order by 2  

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

--Multiple Ordering, natural preferences  

Select au_fname as FirstName, state from Authors   
 Order by 1 ASC, 2 ASC  

--Multiple Ordering ,  Mixed Preferences  

Select au_fname as FirstName, state from Authors   
 Order by 1 DESC, 2 ASC  
  

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

--Derived Fields &  their Ordering  

Select au_fname + '  ' + au_lname as [AUTHOR'S FULL NAME]   
 FROM Authors  
 Order by [AUTHOR'S FULL NAME]  

--Derived Fields &  their Ordering continued  
Select  'Mr. ' + au_fname + ' - ' + au_lname as [AUTHOR'S FULL NAME]    
 FROM Authors   
 Order by [AUTHOR'S FULL NAME]   

--Selecting part of a field  
Select SUBSTRING('System Statistical Functions',7,12) STUFF

--Selecting part of a field  

Select SUBSTRING( au_fname, 2,5)   
 FROM Authors   
  
  
--Selecting part of a composite field  
Select SUBSTRING(au_fname ,1,1 )  + '  ' + au_lname FROM Authors

Advertisements

#basic