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.
SELECT GETDATE() TodaysDate
,CAST(GETDATE() AS float) TodaysDate
,CAST(GETDATE() AS INT) TodaysDate
--is string date
SELECT ISDATE('2011-01-01') CheckIfValueIsDate
,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
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
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
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.
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.