SQL queries examples

My Learning: Log in Learning Report [Log in]


Remove Ads or Log in
Remove Ads or Log in
+0  |  -0

Hi

In this blog I will post SQL queries examples as learning on examples usually is very effective sometimes better than any tutorial but this can also help with SQL Interview questions and answers. I will start from basic SQL queries and go to advanced and complex queries. I will use SQL Server 2008 R2 and I will try to remember to add comments for features that are new. The database I use is called AdventureWorksDW2008R2 and it is Microsoft training database that you can download from Microsoft site. I will be posting new samples for the next several weeks.

Basic SQL Queries

Date Related Examples
In this example I will show several date related functions.

--today's date
SELECT GETDATE() TodaysDate
,GETUTCDATE() UTCTodaysDate
,CAST(GETDATE() AS float) TodaysDate
,CAST(GETDATE() AS INT) TodaysDate

--is string date
SELECT ISDATE('2011-01-01') CheckIfValueIsDate
,ISDATE('20110101') ThisIsStillDate
,CAST('20110101' as int) as ChangeToInteger
,CAST(CAST('20110101' as Datetime) as in
t) as ChangeToDateThanInteger

-- date difference and add days
SELECT DATEDIFF(d,'2011-01-05','2011-01-15') as DayDifference
,DATEADD(d, -5, GETDATE()) as MinusFiveDays

Click image to enlarge
SQL Query date related examples

Extract website name from link
Text manipulation function are very common in SQL and in this example I will show how to extract website name from given link. I will store page link in a variable so it it easier to read it main part of the code that will perform all the work.

declare @PageLink as nvarchar(1000) = 'http://www.katieandemil.com/sql-server/interview-questions-and-answers/sql-interview-questions-and-answers-pdf-download'

select substring(replace(@PageLink,'http://www.',''),0,charindex('/',replace(@PageLink,'http://www.',''))) as WebsiteName

I have used replace function to remove http://www. Then I used substring to find / that is the first character after the website name that I am after and then I use substring finctuon to extract all characters up to the forward slash position.

Top 5 Employees - single table
The first example is very simple. I have writen SQL query to show TOP 5 Employees with the highest BaseRate and to do that I use OrderBy BaseRate DESC.

Click image to enlarge
SQL query show top employee base rate

 

 

 


 

 

 

Top 5 Customer - two tables
In the next query I want to show TOP 5 customers with the highest TotalSales Amount. To do that I join Customer table with InternetSales table on customerkey group by firstname and lastname (for large tables it would be worth adding DateOfBirth to avoid grouping different people together) and order by TotalSales which is SUM of SalesAmountField
SQL Query show top five customers

 


 

 

 

 

 

Departments with high level of female sick leave hours
In this SQL Query I want to show total female employees per department where sick leave hours is more than 40 but only departments where there are at least three females who meet the criteria. So first I filter Gender and SickLeaveHours then I group by department and then I filter the group and leave only department where total number of females that meet the criteria is greater than 3.
SQL Query show female high sick count per department

 


 

 

 

 

 

Advanced SQL Queries

 

 

Show field in every table that contains certain word
This SQL Query is one of my favourite ones. I use Information_schema.columns to find fields in every table (or view) of the selected database that contain word geo in field name.
SQL Query find field containing geo word

 


 

 

 

 

 

Take care
Emil


Remove Ads or Log in

Learn more about us

Who are we?

Where do we come from?

We want our visitors to know our story but we want our visitors to help us create it!

If you are interested in knowing a bit more about us than let us know and we will record a video with an answer (Webcam not just screen ;) and publish it on our website.

Submit your question

Visitor Voting

Did you know we have over 340+ FREE Videos?
(Mainly T-SQL, SSIS, SSRS)
Visitor Voting: Yes +12  |  No -5
Add comment (no email required).



Add Comment
Name: anil singh
Date Posted: 5/26/2012 9:12:26 PM
Comment:

artical is good but if you add  the output screenshot with last If ELSE used with SELECT then it will be much more good ...Remain is good..thanks for this artical.

 




Name: Emil
Date Posted: 5/27/2012 5:49:25 PM
Comment:

Hi Anil,

Thanks for your comment.

I updated SQL SELECT IF/Case and chose more suitable example with more explanation and result screenshot.

Please continue making suggestions it is much easier for us to update our tutorial this way.

Regards

Emil




Name: ken
Date Posted: 9/4/2012 2:02:13 PM
Comment:

i enjoy your explanation . pls how can i get sql server software to install on my pc.

      you guys r great.  nice job.

 




Name: ken
Date Posted: 9/4/2012 2:15:37 PM
Comment:

good job




Name: Nikash
Date Posted: 9/28/2012 5:00:39 AM
Comment:

Nice 




Name: king mhar
Date Posted: 12/3/2012 12:53:37 AM
Comment:

Very Nice Tutorial,

 

Can i ask a question if i can do update from 3 concatinated value and update it from other table.




Name: Venkat
Date Posted: 4/26/2013 3:31:29 PM
Comment:

Nice knowledge sharing...

Keep up the Good work. and Thanks a lot for sharing :)




Name: jag
Date Posted: 6/7/2013 10:28:28 AM
Comment:

Emil really very good work, explanation are very very clear idea...keep updating more things ..




Name: Andrew
Date Posted: 6/13/2014 1:37:05 AM
Comment: Your advanced query is nothing but a basic select statement with an alias.


Name: Emil
Date Posted: 7/18/2014 1:21:47 PM
Comment: Hi Andrew, The advanced part of the query is knowledge of information_schema.columns not the sql query itself. Take care Emil