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]]
<query_expression>
[ ORDER BY { order_by_expression | column_position [ ASC | DESC ] }
[ ,...n ] ]
[ COMPUTE
{ { AVG | COUNT | MAX | MIN | SUM } (expression )} [ ,...n ]
[ BY expression [ ,...n ] ]
]
[ <FOR Clause>]
[ OPTION ( <query_hint> [ ,...n ] ) ]

<query_expression> ::=
{ <query_specification> | ( <query_expression> ) }
[ { UNION [ ALL ] | EXCEPT | INTERSECT }
<query_specification> | ( <query_expression> ) [...n ] ]

<query_specification> ::=
SELECT [ ALL | DISTINCT ]
[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.

 

SELECT * FROM CUSTOMER ;

SELECT FIRSTNAME, LASTNAME FROM CUSTOMER ;


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.


SELECT * FROM CUSTOMER, INVOICE ;


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



SELECT ID, Name
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



SELECT ID, Name
FROM Product
WHERE ProductID <= 12 ;


Finding rows that meet any of three conditions



SELECT ID, Name
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
WHERE Weight IS NULL


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.

13 comments:

  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,
      Lee

      Delete
  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...

    ReplyDelete
  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

    ReplyDelete
  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

    ReplyDelete
  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|

    ReplyDelete
  6. This comment has been removed by the author.

    ReplyDelete
  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

    ReplyDelete

  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.


    MuchasGracias,
    Irene Hynes

    ReplyDelete
  9. This comment has been removed by the author.

    ReplyDelete
  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

    ReplyDelete