Please navigate to the bottom of the page for Table of Contents

Sunday, June 12, 2011

SQL Select Where Interview Questions

SQL is one of my favorite interview areas. If properly phrased, the interviewer can go in depth and look at a candidate’s ability to clearly define the logic and think on their feet. Most of the white boarding questions involve some flavor of SELECT statement and almost always include WHERE, GROUP BY, ORDER BY and JOINS. In this post we will explore the SELECT statement with WHERE clause. In addition, I will be focusing on Microsoft SQL Server 2008 version of the queries. We will explore Oracle, MySql, etc. in a later post.

SQL SELECT statement

A SELECT statement retrieves rows from the database and enables the selection of one or many rows or columns from one or many tables in SQL Server. In it’s fully complex form, a SELECT statement can be represented as:

<SELECT statement> ::=  

[WITH <common_table_expression> [,...n]]
[ ORDER BY { order_by_expression | column_position [ ASC | DESC ] }
[ ,...n ] ]
{ { AVG | COUNT | MAX | MIN | SUM } (expression )} [ ,...n ]
[ BY expression [ ,...n ] ]
[ <FOR Clause>]
[ OPTION ( <query_hint> [ ,...n ] ) ]

<query_expression> ::=
{ <query_specification> | ( <query_expression> ) }
<query_specification> | ( <query_expression> ) [...n ] ]

<query_specification> ::=
[TOP ( expression ) [PERCENT] [ WITH TIES ] ]

< select_list >
[ INTO new_table ]
[ FROM { <table_source> } [ ,...n ] ]
[ WHERE <search_condition> ]
[ <GROUP BY> ]
[ HAVING < search_condition > ]

The most basic form or a SELECT statement allows you to either retrieve all or selected columns from a given table. The only required condition is a FROM clause.




The FROM clause does not limit you to one table. In fact, you can specify multiple tables. In the query shown below, the  statement forms a virtual table that combines the data from the CUSTOMER table with the data from the INVOICE table. Each row in the CUSTOMER table combines with every row in the INVOICE table to form the new table. If the CUSTOMER table has 100 rows and the INVOICE table has 100, the new virtual table has 10,000 rows. This is a JOIN in it’s simplest form.


SQL WHERE clause

A WHERE clause allows you to specify the search condition for the rows returned by the SELECT statement. This clause is primarily used to limit the number of rows returned by or affected by the statement. The definition of the WHERE clause looks very simple.

[ WHERE <search_condition> ]

The <search_condition> defines the condition to be met for the rows to be returned. There is no limit to the number of predicates that can be included in a search condition. The condition in the WHERE clause may be simple or arbitrarily complex.You may join multiple conditions together by using the logical connectives AND, OR, and NOT. The comparison predicates (=, <, >, <>, <=, and >=) are the most common, but SQL offers several others that greatly increase your capability to distinguish, or filter out, a desired data item from others in the same column. The following list notes the predicates that give you that filtering capability: Comparison predicates (=, <, >, <>, <=, and >=), BETWEEN, IN [NOT IN], LIKE [NOT LIKE], SIMILAR, NULL, ALL, SOME, and ANY, EXISTS, UNIQUE, DISTINCT, OVERLAPS and MATCH.

Enough theory! Now let’s explore some simple interview questions that involve the SELECT and WHERE clauses. Many of the examples below also use some other SELECT constructs such as ORDER BY, etc.

Finding a row by using a simple equality

FROM Product
WHERE Name = 'Blade' ;

Finding rows that contain a value as a part of a string

SELECT ID, Name, Color
FROM Product
WHERE Name LIKE ('%Frame%');

Finding rows by using a comparison operator

FROM Product
WHERE ProductID <= 12 ;

Finding rows that meet any of three conditions

FROM Product
WHERE ProductID = 2
OR ProductID = 4
OR Name = 'Spokes' ;

Finding rows that must meet several conditions

SELECT ID, Name, Color
FROM Product
WHERE Name LIKE ('%Frame%')
AND Name LIKE ('HL%')
AND Color = 'Red' ;

Finding rows that are in a list of values

SELECT ID, Name, Color
FROM Product
WHERE Name IN ('Blade', 'Crown Race', 'Spokes');

Finding rows that have a value between two values

SELECT ID, Name, Color
FROM Production.Product
WHERE ProductID BETWEEN 725 AND 734;

Checking for NULL values

SELECT ProductID, Name, Weight
FROM Product

The SELECT statement can also change the column heading and perform calculations. The following examples return all rows from the Product table. The first example returns total sales and the discounts for each product. In the second example, the total revenue is calculated for each product.

Finding total sales and discounts for each product

SELECT p.Name AS ProductName, 
NonDiscountSales = (OrderQty * UnitPrice),
Discounts = ((OrderQty * UnitPrice) * UnitPriceDiscount)
FROM Product AS p
INNER JOIN SalesOrderDetail AS sod
ON p.ProductID = sod.ProductID
ORDER BY ProductName DESC;

Calculate total revenue for each product

SELECT 'Total income is', ((OrderQty * UnitPrice) * (1.0 - UnitPriceDiscount)), ' for ',
p.Name AS ProductName
FROM Product AS p
INNER JOIN SalesOrderDetail AS sod
ON p.ProductID = sod.ProductID
ORDER BY ProductName ASC;

This blog post barely touches the surface of the SELECT statement and the WHERE clause. In the next post we will explore the other clauses and keywords that are most popular in SQL interview questions.


  1. Replies
    1. Hi Nikhil,

      Great info! I recently came across your blog and have been reading along.
      I thought I would leave my first comment. I don’t know what to say except that I have
      I am searching information about video in java, do you know some framework?
      I need a little bit of orientation.
      By looking at the initial JVM configuration (start-up options) and nature of performance bottleneck, these configuration parameters can be set appropriately.
      By the way do you have any YouTube videos, would love to watch it. I would like to connect you on LinkedIn, great to have experts like you in my connection (In case, if you don’t have any issues).
      Please keep providing such valuable information.

      Thanks a heaps,

  2. Thank you so much..Most of my doubts got cleared..Thank you so much ...
    The sap hana training in dubai
    will provide u the best training...

  3. Hello, Thanks for sharing.
    These are good questions to refresh your knowledge before an interview. A technical interview would also consist of practical SQL Interview Questions

  4. Your good knowledge and kindness in playing with all the pieces were very useful. I don’t know what I would have done if I had not encountered such a step like thisselenium training in chennai

  5. The great service in this blog and the nice technology is visible in this blog. I am really very happy for the nice approach is visible in this blog and thank you very much for using the nice technology in this blog

    AWSTraining in Bangalore|

  6. This comment has been removed by the author.

  7. The great service in this blog and the nice technology is visible in this blog.It's very useful information. thanks for sharing..

    T-SQL Training


  8. Hello Buddy,

    What a brilliant post I have come across and believe me I have been searching out for this similar kind of post for past a week and hardly came
    across this.

    I am trying to generate a flat file using SSIS 2014 and want to use ý ascii(0253) instead of comma in the delimited csv file, please can you advise.

    Thank you very much and will look for more postings from you.

    Irene Hynes

  9. This comment has been removed by the author.

  10. Nice Post I learned a lot From the Post Thanks for sharing, learn the most ON-DEMAND software Training in Best Training Institutions
    Instructor-LED Salesforce Online Training
    Professional Salesforce CRM Training
    Salesforce Training online in India

  11. Nice set of interview questions and answers.i have gone through it very helpful one thanks for sharing...
    SQL Interview Questions & Answers

  12. The article is so informative. This is more helpful.
    software testing training courses
    selenium course
    Thanks for sharing

  13. I Got Job in my dream company with decent 12 Lacks Per Annum salary, I have learned this world most demanding course out there in the current IT Market from the Big Data Hadoop Training In Bangalore experts who helped me a lot to achieve my dreams comes true. Really worth trying.

  14. Although when you have become an expert you will be able to write many programs quickly, you must remember that many programs have taken whole teams of expert developers years to create.Why use Laravel

  15. Nice post I have been searching for a useful post like this on salesforce course details, it is highly helpful for me and I have a great expereince with this
    Salesforce Training  which is a best institute for career building program.

  16. This comment has been removed by the author.

  17. This comment has been removed by the author.

  18. This comment has been removed by the author.

  19. Thanks for sharing the information...
    Salesforce CRM Training in Marathahalli - Bangalore | Salesforce CRM Training Institutes | Salesforce CRM Course Fees and Content | Salesforce CRM Interview Questions - eCare Technologies located in Marathahalli - Bangalore, is one of the best Salesforce
    CRM Training institute with 100% Placement support. Salesforce CRM Training in Bangalore provided by Salesforce CRM Certified Experts and real-time Working Professionals with handful years of experience in real time Salesforce CRM Projects.

  20. Blockchain Course in Bangalore with 100% placement. We are the Best Blockchain Course Institute in Bangalore. Our Agile testing course and Certification courses are taught by working professionals who are experts in Blockchain.

    Blockchain Training in Bangalore

    Blockchain course in bangalore

    Blockchain in bangalore

    Blockchain classes in bangalore

    Blockchain course institute in bangalore

    Agile testing course and Certification course syllabus

    best Blockchain course

    Blockchain course centers

  21. It's very useful post and i had good experience with this salesforce training in Chennai who are offering good certificaiton assistance. I would say salesforce training is a best way to get certified on crm.

  22. The patent was conceded on August 17, 1966 and is by all accounts one of the principal software licenses.

  23. Pretty article! I found some useful information in your blog....

    so here we provide,

    We provide you with flexible services and complete hybrid network solutions. It can provide your organisation with exceptional data speeds, advanced external security protection, and high-resilience by leveraging the latest SD-WAN and networking technologies to monitor, manage and strengthening your organisation’s existing network devices.>

    #quadsel #network #security #technologies #managedservices #Infrastructure #Networking #OnsiteResources #ServiceDeskSupport #StorageServices #WarrantyAMCServices #datacentersolutions #DataCenterBuild #EWaste #InfraConsolidation #DisasterRecovery #NetworkingServices #ImagingServices #MPS #Consulting #WANOptimisation #enduserservices

  24. A backlink is a link created when one website links to another. Backlinks are important to SEO & impact for higher ranking. In my 7+ years seo Career i see, without backlinks a website doesn't rank higher on google SERP.

    Get Your 300+ High Quality DoFollow Backlinks Here!

    Order Now with Full Confidence & 100% satisfaction.

  25. With Brandsoo, it’s never been easier company brand names for sale or more convenient to shop high quality domain names and professional logos that’ll instantly give your brand a leg up and resonate with your audience! With many different domain sellers all competing for your business, you need to know where your business and brand will see.

  26. Wow it is really wonderful and awesome thus it is very much useful for me to understand many concepts and helped me a lot.

    SAP Online Training

    SAP Classes Online

    SAP Training Online

    Online SAP Course

    SAP Course Online

  27. Forex Signals, MT4 and MT5 Indicators, Strategies, Expert Advisors, Forex News, Technical Analysis and Trade Updates in the FOREX IN WORLD

    Forex Signals Forex Strategies Forex Indicators Forex News Forex World

  28. Situs judi bola online UFA88 Terpercaya, juga memiliki casino online seperti, Baccarat online, roulette, Judi slot online, sabung ayam dan dadu online.

  29. Adam the alien - Animated films A close encounter with a likable robot from another solar system leads to a star-crossed friendship Do aliens exist and an impossible intergalactic mission to save Extraterrestrial life the robot’s friends— despite overwhelming New cartoon movies odds.

  30. - Adalah Situs data hk Khusus yang menyediakan Daftar keluaran hk Result Nomor Togel dan Data hasil keluaran live draw hk seperti hk sgp sydney yang sudah terpercaya keluaran togel hk.

  31. Shield Security Solutions Provides Ontario Security Training, Security Guard License or Security License in Ontario. Get Started Today

  32. Website laten maken? Onze professionals bouwen een omzetverhogende, professionele én unieke website of webshop! Een website laten maken kan vandaag nog

    Website laten maken | webdesigner | website freelancer | webdeveloper | webdesigner