Sign In | Feedback | Donate | Search
Katie and Emil Logo
Vote Yes! I like it! [+0]  |  Vote no! Dislike? [-0]
Author: by Katie Glownia

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.sql-server-business-intelligence.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






 

 

PDF Free Download

 
You can print our this page using our SQL queries examples PDF Free Download

 

Take care
Emil

Upsss... Something went wrong and Google Ads on our website did not display.

Google ads sponsor FREE content on our website and without them it would not exist.

How to fix the problem?
  1. Check if the Google Search display any ads by searching the term Business Intelligence
    • If ads don't display, try different search terms and if there no ads then there might be a problem with your browser. If you made changes to your browser behaviour then undoing them will help.
    • If ads display then, refresh our page one more time, if the problem is still there than please contact us.
  2. Become 'No Ads Learner' Member or if you are already one Sign In.
Close
Take care
Katie & Emil
Well done! It seems Google Ads display properly now on our website.

Access our website's FREE content for FREE!