SQL read text from a file

Consider the following scenario: Your database has a table called Customer (who doesn't these days) with millions of rows (lucky you). You production support team just called you in the middle of the night with a live site issue - Around 10,000 customers are getting a message that their logins have been disabled. And they send you a text file with 10,000 customer id's :-)

Great! Now you need to look these customers up in the database and enable their access again. 

So how can you load these 10K values from a text file into SQL? One way would be to edit the text file, replace each new line with a comma and then use the resulting string in the select clause as shown:

select * from Customer where Id in (1, 2, 6, 8, 10)   

Now this might prove to be too much non-techy way. You are a programmer, right? You don't hard-code stuff! You write code!!

So how can you load these disabled customer list in the database programatically?

Create a temporary table and call it DisabledCustomers with just one column (name it Id and make sure it allows nulls and is not primary key - just in case your list from support has duplicates).  

Run the following SQL to load the values from the file into this table.  
BULK INSERT dbo.DisabledCustomers
   FROM 'c:\temp\customerids.txt'
   WITH
      (
         ROWTERMINATOR ='\n'
      ) 

Now your select statement becomes quite simple: 
select * from Customer c
inner join DisabledCustomers dc on c.Id = dc.Id 

Many production systems have BULK INSERT disabled? As an exercise to you, my dear reader, I look forward to seeing some innovative solutions to that.

SQL self join or sub-query interview question (employee-manager salary)

One of my favorite interview questions that tips even seasoned SQL guys (maybe because it's too simple) is around querying data that involves a self join.

What is a self join?
A self join is a join in which a table is joined with itself, specially when the table has a foreign  key which references its own primary key. 

Question: Given an Employee table which has 3 fields - Id (Primary key), Salary and Manager Id, where manager id is the id of the employee that manages the current employee, find all employees that make more than their manager in terms of salary. Bonus: Write the table creation script.

SQL IF-ELSE and WHILE examples

As like the conditional processing constructs, SQL flow control statements are a critical part of your understanding of SQL. You may be asked a direct question; most likely it will be a part of a larger question.

Transact-SQL provides special words called control-of-flow language that control the flow of execution of Transact-SQL statements, statement blocks, user-defined functions, and stored procedures. These control-of-flow words are useful when you need to direct Transact-SQL to take some kind of action. For example, use a BEGIN...END pair of statements when including more than one Transact-SQL statement in a logical block. Use an IF...ELSE pair of statements when a certain statement or block of statements needs to be executed IF some condition is met, and another statement or block of statements should be executed if that condition is not met (the ELSE condition).

One interesting question that I have seen where even seasoned candidates’ trip is when asked to explain transaction rollbacks.

BEGIN TRANSACTION;
GO
IF @@TRANCOUNT = 0
BEGIN
    SELECT FirstName, MiddleName 
    FROM Person.Person WHERE LastName = 'Adams';
    ROLLBACK TRANSACTION;
    PRINT N'Rolling back the transaction two times would cause an error.';
END;
ROLLBACK TRANSACTION;
PRINT N'Rolled back the transaction.';
GO
/*
Rolled back the tranaction.
*/

In the above example, BEGIN and END define a series of Transact-SQL statements that execute together. If the BEGIN...END block were not included, both ROLLBACK TRANSACTION statements would execute and both PRINT messages would be returned.

Using IF…ELSE

IF...ELSE evaluates a Boolean expression, and if TRUE, executes a Transact-SQL statement or batch. The syntax is simple:

IF Boolean_expression { sql_statement | statement_block } 
    [ ELSE { sql_statement | statement_block } ] 

Boolean_expression  -  Is an expression that returns TRUE or FALSE. If the Boolean_expression contains a SELECT statement, the SELECT statement must be enclosed in parentheses.

{ sql_statement | statement_block } - Is any valid Transact-SQL statement or statement grouping as defined with a statement block. To define a statement block (batch), use the control-of-flow language keywords BEGIN and END. Although all Transact-SQL statements are valid within a BEGIN...END block, certain Transact-SQL statements should not be grouped together within the same batch (statement block).

Using a simple Boolean expression

The following example has a simple Boolean expression (1=1) that is true and, therefore, prints the first statement.

IF 1 = 1 PRINT 'Boolean_expression is true.'
ELSE PRINT 'Boolean_expression is false.' ;

Using a query as part of a Boolean expression

The following example executes a query as part of the Boolean expression.

IF 
    (SELECT COUNT(*) FROM Production.Product WHERE Name LIKE 'Touring-3000%' ) > 5
        PRINT 'There are more than 5 Touring-3000 bicycles.'
    ELSE 
        PRINT 'There are 5 or less Touring-3000 bicycles.' ;

Using nested IF...ELSE statements

The following example shows how an IF … ELSE statement can be nested inside another. Set the @Number variable to 5, 50, and 500 to test each statement.

DECLARE @Number int;
SET @Number = 50;
IF @Number > 100
   PRINT 'The number is large.';
ELSE 
   BEGIN
      IF @Number < 10
      PRINT 'The number is small.';
   ELSE
      PRINT 'The number is medium.';
   END ;
GO

Using WHILE statement

WHILE allows you to set a condition for the repeated execution of an SQL statement or statement block. The statements are executed repeatedly as long as the specified condition is true. The execution of statements in the WHILE loop can be controlled from inside the loop with the BREAK and CONTINUE keywords.

Using BREAK and CONTINUE with nested IF...ELSE and WHILE

In the following example, if the average list price of a product is less than $300, the WHILE loop doubles the prices and then selects the maximum price. If the maximum price is less than or equal to $500, the WHILE loop restarts and doubles the prices again. This loop continues doubling the prices until the maximum price is greater than $500, and then exits the WHILE loop and prints a message.

WHILE (SELECT AVG(ListPrice) FROM Production.Product) < $300
BEGIN
   UPDATE Production.Product
      SET ListPrice = ListPrice * 2
   SELECT MAX(ListPrice) FROM Production.Product
   IF (SELECT MAX(ListPrice) FROM Production.Product) > $500
      BREAK
   ELSE
      CONTINUE
END
PRINT 'Too much for the market to bear';

SQL CASE statement examples

It is quite difficult to write a stored procedure or a function if you do not know or understand the SQL conditional processing statements. From an interview perspective, you should familiarize yourself with the conditional processing as well as the flow control statements.

The CASE expression is used to evaluate several conditions and return a single value for each condition. For example, it allows an alternative value to be displayed depending on the value of a column. A common use of the CASE expression is to replace codes or abbreviations with more readable values. This is also known as a Simple CASE expression.

Question: For a given product and category abbreviation, show the full category name using a CASE expression.

SELECT   ProductNumber, Category =
      CASE ProductLine
         WHEN 'R' THEN 'Road'
         WHEN 'M' THEN 'Mountain'
         WHEN 'T' THEN 'Touring'
         WHEN 'S' THEN 'Other sale items'
         ELSE 'Not for sale'
      END,
   Name
FROM Product
ORDER BY ProductNumber;

Another use of CASE is to categorize data.

Question: Based on an item list price, show the price range for the item. The example below shows a searched CASE expression which evaluates a set of Boolean expressions to determine the result.

SELECT   ProductNumber, Name, 'Price Range' = 
      CASE 
         WHEN ListPrice =  0 THEN 'Mfg item - not for resale'
         WHEN ListPrice < 50 THEN 'Under $50'
         WHEN ListPrice >= 50 and ListPrice < 250 THEN 'Under $250'
         WHEN ListPrice >= 250 and ListPrice < 1000 THEN 'Under $1000'
         ELSE 'Over $1000'
      END
FROM Production.Product
ORDER BY ProductNumber ;

A CASE statement can also be used in a similar way as an Acess Iff:

Question: Show special instructions if they exist for a customer.

SELECT FirstName, LastName, TelephoneNumber, 'When to Contact' = 
     CASE
          WHEN TelephoneSpecialInstructions IS NULL THEN 'Any time'
          ELSE TelephoneSpecialInstructions
     END
FROM Person.vAdditionalContactInfo;

CASE Advanced scenarios

In addition to being used in a SELECT statement, a CASE expression can also be used in ORDER BY and HAVING clauses and in UPDATE and SET statements. Let’s review those examples.

 

Using CASE in an ORDER BY clause

Question: Sort of list of sales people by territory when they serve US otherwise sort by country.

The following example uses the CASE expression in an ORDER BY clause to determine the sort order of the rows based on a given column value. In the example below the result set is ordered by the column TerritoryName when the column CountryRegionName is equal to 'United States' and by CountryRegionName for all other rows.

SELECT BusinessEntityID, LastName, TerritoryName, CountryRegionName
FROM Sales.vSalesPerson
WHERE TerritoryName IS NOT NULL
ORDER BY CASE CountryRegionName WHEN 'United States' THEN TerritoryName
         ELSE CountryRegionName END;

Using CASE in a HAVING clause

Question: Find the maximum hourly rate for each job title and then restrict the result to those that are held by men with a maximum pay rate greater than 40 dollars or women with a maximum pay rate greater than 42 dollars.

The following example uses the CASE expression in a HAVING clause to restrict the rows returned by the SELECT statement. The statement returns the maximum hourly rate for each job title in the HumanResources.Employee table. The HAVING clause restricts the results further.

SELECT JobTitle, MAX(ph1.Rate)AS MaximumRate
FROM HumanResources.Employee AS e
JOIN HumanResources.EmployeePayHistory AS ph1 ON e.BusinessEntityID = ph1.BusinessEntityID
GROUP BY JobTitle
HAVING (MAX(CASE WHEN Gender = 'M' 
        THEN ph1.Rate 
        ELSE NULL END) > 40.00
     OR MAX(CASE WHEN Gender  = 'F' 
        THEN ph1.Rate  
        ELSE NULL END) > 42.00)
ORDER BY MaximumRate DESC;

Using CASE in an UPDATE statement

Question: Write an UPDATE statement to update the vacation hours for salaried employees. If the employee has less than 10 hours of vacation, give her 40 otherwise 20.

UPDATE HumanResources.Employee
SET VacationHours = 
    ( CASE
         WHEN ((VacationHours - 10.00) < 0) THEN VacationHours + 40
         ELSE (VacationHours + 20.00)
       END
    )
WHERE SalariedFlag = 0; 

SQL GROUP BY and HAVING clauses

In the previous post we explored the basics of SQL SELECT statement. In this post we will continue to look at some more complex questions and answers involving SELECT statement.

Using DISTINCT with SELECT

The following example uses DISTINCT to prevent the retrieval of duplicate titles.

SELECT DISTINCT JobTitle
FROM Employee
ORDER BY JobTitle;

GROUPing data using GROUP BY

The GROUP BY clause is used in a SELECT query to determine the groups that rows should be put in. GROUP BY follows the optional WHERE clause and is most often used when aggregate functions are referenced in the SELECT statement.

The following example finds the total of each sales order in the database. Because of the GROUP BY clause, only one row containing the sum of all sales is returned for each sales order.

SELECT SalesOrderID, SUM(LineTotal) AS SubTotal
FROM .SalesOrderDetail
GROUP BY SalesOrderID
ORDER BY SalesOrderID;

The following example finds the average price and the sum of year-to-date sales, grouped by product ID and special offer ID.

SELECT ProductID, SpecialOfferID, AVG(UnitPrice) AS 'Average Price', 
    SUM(LineTotal) AS SubTotal
FROM Sales.SalesOrderDetail
GROUP BY ProductID, SpecialOfferID
ORDER BY ProductID;

The following example groups by an expression. You can group by an expression if the expression does not include aggregate functions.

SELECT AVG(OrderQty) AS 'Average Quantity', 
NonDiscountSales = (OrderQty * UnitPrice)
FROM SalesOrderDetail
GROUP BY (OrderQty * UnitPrice)
ORDER BY (OrderQty * UnitPrice) DESC;

The following example finds the average price of each type of product and orders the results by average price.

SELECT ProductID, AVG(UnitPrice) AS 'Average Price'
FROM SalesOrderDetail
WHERE OrderQty > 10
GROUP BY ProductID
ORDER BY AVG(UnitPrice);

By adding the ALL keyword after GROUP BY, all row values are used in the grouping, even if they were
not qualified to appear via the WHERE clause as shown below.

SELECT OrderDate, SUM(TotalDue) TotalDueByOrderDate
FROM SalesOrderHeader
WHERE OrderDate BETWEEN '5/1/2011' AND '5/31/2011'
GROUP BY ALL OrderDate

Filter Grouped Data with HAVING clause

The HAVING clause is a filter that acts similar to a WHERE clause, but the filter acts on groups of rows rather than on individual rows. In other words, the HAVING clause is used to qualify the results after the GROUP BY has been applied. The WHERE clause, in contrast, is used to qualify the rows that are returned before the data is aggregated or
grouped. HAVING qualifies the aggregated data after the data has been grouped or aggregated.

The example below shows a HAVING clause with an aggregate function. It groups the rows in the SalesOrderDetail table by product ID and eliminates products whose average order quantities are five or less.

SELECT ProductID 
FROM SalesOrderDetail
GROUP BY ProductID
HAVING AVG(OrderQty) > 5
ORDER BY ProductID;

This example shows a HAVING clause without aggregate functions. This query uses the LIKE clause in the HAVING clause.

SELECT SalesOrderID, CarrierTrackingNumber 
FROM SalesOrderDetail
GROUP BY SalesOrderID, CarrierTrackingNumber
HAVING CarrierTrackingNumber LIKE '4BD%'
ORDER BY SalesOrderID ;

The following example shows using GROUP BY, HAVING, WHERE, and ORDER BY clauses in one SELECT statement. It produces groups and summary values but does so after eliminating the products with prices over $25 and average order quantities under 5. It also organizes the results by ID.

SELECT ProductID 
FROM Sales.SalesOrderDetail
WHERE UnitPrice < 25.00
GROUP BY ProductID
HAVING AVG(OrderQty) > 5
ORDER BY ProductID;

Aggregate functions such as SUM and AVG can also be using in the HAVING clause. For example, to see the products that have had total sales greater than $2000000.00, the following query would be needed.

SELECT ProductID, Total = SUM(LineTotal)
FROM SalesOrderDetail
GROUP BY ProductID
HAVING SUM(LineTotal) > $2000000.00;

If you want to make sure there are at least 1500 items involved in the calculations for each product, use HAVING COUNT(*) > 1500 to eliminate the products that return totals for fewer than 1500 items sold.

SELECT ProductID, SUM(LineTotal) AS Total
FROM SalesOrderDetail
GROUP BY ProductID
HAVING COUNT(*) > 1500;

Creating tables with SELECT INTO

The following example creates a temporary table named #Bicycles in tempdb.

USE tempdb;
GO
IF OBJECT_ID (N'#Bicycles',N'U') IS NOT NULL
DROP TABLE #Bicycles;
GO
SELECT * 
INTO #Bicycles
FROM AdventureWorks2008R2.Production.Product
WHERE ProductNumber LIKE 'BK%';

This second example creates the permanent table NewProducts.

IF OBJECT_ID ('dbo.NewProducts', 'U') IS NOT NULL
    DROP TABLE dbo.NewProducts;
GO

SELECT * INTO dbo.NewProducts
FROM Production.Product
WHERE ListPrice > $25 
AND ListPrice < $100;

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.