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.