SQL queries examples

Note this an old article and requires a re-write to improve the quality.

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

Did you find this page useful?
+6  |  -0
(6 Votes)

KEBI Academy Membership

Unlock your potential and succeed

Silver Easy start

$8per month

  • All BI Courses
  • All BI Video Lessons
  • All BI Text Lessons
  • Live Chat
  • .

Gold Learn Smarter

$16per month

  • Silver +
  • Member Forum
  • Progress Tracking
  • 70-461 Practice Tests
  • .

Platinum Progress fast

$24per month

  • Gold +
  • Webinar Recordings
  • DIY Projects
  • Boot Camps
  • .

Diamond Unstoppable

$40per month

  • Skype Video Coaching
  • Remote Database (coming soon)
  • Practice SQL Online (coming soon)
  • .
  • .
On Purchase you get
7 Days Free Trial
Subscriptions only
View more
60 days
Money back guarantee
View more
24/7
Access
View more
Instant Activation
via Email
View more
Easy & Anytime
Cancellation
View more
Price
Freeze
View more
Friendly
& helpful support
View more
New content
for free
View more
How to progress your career?
Frequently Asked Questions
Subscription gives you 7 days free trial and gives you re-occuring billing (auto-renewal) which means that your card (or payment account) will be charges automatically based on your selected frequency (monthly, quarterly or yearly). This option is ideal for those who want to use our membership long term without having to worry to extend it with option to cancel it at anytime.
Access pass does not give free trial and it is a one-off payment that gives access for fixed amount of time and which auto-expiries, this is ideal for those who want time limited access without having to worry about cancelling the membership.
7 days trial applies to subscription only and you will be asked to provide payment details (show commitment) but no payment will be taken during your 7 days trial. After 7 days, payment will be taken unless you decide to cancel your subscription. Cancelling is easy and even if you forget to cancel and payment is taken you still get 60 days money back guarantee so simply ask for refund.
You can cancel your subscription (including free trial) at any point in time.
There are many ways to cancel:
1. You can use your email payment receipt and click link with order number that ends with S.
2. You can send us an email (main menu).
3. You can call us (main menu).
4. You can use live chat (main menu).
It will depend on your goal. Please review membership benefits and explore our website including preview articles and videos.
If you want us to help you find out if our membership is suitable for the goal you want to achieve then please get in touch (main menu)
Our membership options were designed to fit most goals and budgets. If you are unsure which one is best for you then please contact us (main menu).
Yes, You can downgrade and upgrade your membership at any time. For more information contact us (main menu).
Yes, for more information contact us (main menu).
If you are on quarterly or yearly subscription you can cancel it and get money back for unused months, however we will not take into consideration the loyalty discount we give (33% off for Quarterly subscription and 50% off for Yearly Subscriptions) which means that if used months without discounts exceeds what you paid then there is no money back, otherwise you will get the difference back.
Fastspring is our retailer and we only sell membership to them. It's like going to a shop and buying a product in the shop instead of directly from the company who produces the product.
In our case, this actually makes the membership more affordable, as we have lower operational costs.
That occasionally happens. Make sure your card or paypal account has sufficient funds to cover the payment, it's not suspended, the card has not expiried and address matches your card address. Sometimes you may have to call your card provider to tell them you are happy to make this purchase. It's usually easier to try another card or account but if you want to use a particular card then contact us (main menu) and we will try to help you out.
Contact us (main menu) and we will help you out.
This is very rare and typical reasons are:
1. Mispelled email address.
2. Email went to spam folder.
3. Email was blocked.

We check activations reguarly and if we cannot reach you we will issue a refund usually after 30 days.
We try to make it very clear but this happens occasionaly so don't worry and simply contact us (main menu) and we will issue a refund and cancel your subscription.
There are many options like email, telephone, live chat and even skype. Use main menu to contact us.
We are based in UK and generally provide support between 8am and 8pm local time (GMT +0), Monday to Friday (business days) and occasionally during the weekeneds.
We will happily asnwer it. Please contact us (main menu).